Tables & Schema

You can model your schema three ways — the console Table Editor, the CLI (anvilbase schema …), or the Schema API (/api/v1/projects/{id}/schema/…). All three drive the same validated DDL path. For anything the structured API doesn’t cover (complex constraints, functions, triggers), use the SQL Editor.

Creating a table

Console

Project → Tables → New Table. Add columns, pick types, set a primary key, toggle RLS (on by default). Save.

CLI

The CLI reads a JSON spec:

todos.json

{
  "columns": [
    {"name":"id","type":"uuid","primary_key":true,"default":"gen_random_uuid()"},
    {"name":"user_id","type":"uuid","nullable":false},
    {"name":"task","type":"text","nullable":false},
    {"name":"done","type":"boolean","default":"false"},
    {"name":"created_at","type":"timestamptz","default":"now()"}
  ],
  "enable_rls": true
}

anvilbase schema table create --project <id> --name todos --from ./todos.json

Schema API

curl -X POST http://localhost:39001/api/v1/projects/<id>/schema/tables \
  -H "Authorization: Bearer $ANVILBASE_TOKEN" -H "Content-Type: application/json" \
  -d '{
    "name":"orders",
    "enable_rls":true,
    "columns":[
      {"name":"id","type":"uuid","primary_key":true,"default":"gen_random_uuid()"},
      {"name":"total","type":"numeric(10,2)","nullable":false},
      {"name":"user_id","type":"uuid","foreign_key":{"table":"users","column":"id"}}
    ]
  }'

enable_rls defaults to true. At least one column is required. Returns 201.

Note: RLS is enabled automatically on every new table in public regardless of creation path (see Row Level Security). Setting enable_rls: false here issues an explicit ALTER TABLE … DISABLE ROW LEVEL SECURITY — it is the per-table escape hatch, not merely “skip the enable”.

Column specification

Each column object supports:

Field	Type	Notes
name	string	must match ^[A-Za-z_][A-Za-z0-9_]*$
type	string	from the allowlist (below)
nullable	bool	default true
default	string	a SQL expression, e.g. now(), gen_random_uuid(), false
primary_key	bool	marks the PK
foreign_key	object	{ "table": "...", "column": "..." }

Allowed column types

Text & identity: text, varchar(n), uuid, boolean Numbers: int2/int4/int8 (smallint/integer/bigint), serial, numeric(p,s), real, double precision Time: timestamptz, timestamp, date, time Structured: jsonb, json, arrays (text[], int4[], …) Vectors: vector / vector(n) — see Vector Search

Precision/scale and [] array suffixes are accepted. Types outside the allowlist are rejected — use the SQL Editor for exotic types.

Adding and dropping columns

# Add a column
anvilbase schema column add --project <id> --table todos \
  --name notes --type text --nullable true

anvilbase schema column add --project <id> --table todos \
  --name updated_at --type timestamptz --default "now()"

# Drop a column — confirmation must match the name exactly
anvilbase schema column drop --project <id> --table todos --name notes --confirm notes

Schema API equivalents:

# Add
curl -X POST .../schema/tables/todos/columns -d '{"name":"notes","type":"text"}' ...
# Drop (echo the name in ?confirm=)
curl -X DELETE ".../schema/tables/todos/columns/notes?confirm=notes" ...

Dropping a table

Destructive operations require you to echo the object name in ?confirm= (or --confirm on the CLI). A mismatch is rejected 400 before the database is touched.

anvilbase schema table drop --project <id> --name todos --confirm todos

curl -X DELETE "http://localhost:39001/api/v1/projects/<id>/schema/tables/todos?confirm=todos" \
  -H "Authorization: Bearer $ANVILBASE_TOKEN"

Inspecting the schema

# List tables with columns, PKs, FKs, indexes, and a row estimate
anvilbase schema tables --project <id>

curl http://localhost:39001/api/v1/projects/<id>/schema/tables \
  -H "Authorization: Bearer $ANVILBASE_TOKEN" | jq .

Auto-generated REST docs for your tables

AnvilBase generates a per-project OpenAPI 3.0 document describing the REST surface for your tables (one path per table, with GET/POST/PATCH/DELETE, PostgREST-style params, and column-derived schemas):

curl http://localhost:39001/api/v1/projects/<id>/docs \
  -H "Authorization: Bearer $ANVILBASE_TOKEN" | jq '.paths | keys'

Point a Swagger UI / Redoc at it, or feed it to a client generator.

Generating typed clients

The CLI generates typed client code from your schema:

# Supabase-compatible TypeScript Database interface
anvilbase gen types typescript --project <id> > database.types.ts

# Drizzle ORM schema
anvilbase gen types drizzle --project <id> > src/db/schema.ts

The TypeScript output gives you Database['public']['Tables']['todos']['Row' | 'Insert' | 'Update'], where Insert makes defaulted/nullable columns optional and Update makes everything optional — matching the Supabase typing convention.

Where RLS comes in

Every table created here has RLS enabled but no policies, so it’s locked until you add one. Go to Row Level Security next, or use the console’s visual policy builder.

Next: SQL Editor & Raw SQL.