Declarative database schemas

Overview#

Declarative schemas provide a developer-friendly way to maintain schema migrations.

Migrations are traditionally managed imperatively (you provide the instructions on how exactly to change the database). This can lead to related information being scattered over multiple migration files. With declarative schemas, you instead declare the state you want your database to be in, and the instructions are generated for you.

Because the schema files are the source of truth, make every change by editing them, not through Studio or the SQL editor. Migrations are generated from your schema files with supabase db schema declarative sync, which compares the files against your migration history, not the live database. Changes made directly to the database are not picked up.

Schema migrations#

Schema migrations are SQL statements written in Data Definition Language. They are versioned in your supabase/migrations directory to ensure schema consistency between local and remote environments.

Declaring your schema#

1
create table "employees" (
2
  "id" integer not null,
3
  "name" text
4
);

1
supabase db schema declarative sync -f create_employees_table

1
supabase start
2
supabase migration up

Updating your schema#

1
create table "employees" (
2
  "id" integer not null,
3
  "name" text,
4
  "age" smallint not null
5
);

1
supabase db schema declarative sync -f add_age

1
alter table "public"."employees" add column "age" smallint not null;

1
supabase migration up

Deploying your schema changes#

1
supabase login

1
supabase link

1
supabase db push

Managing dependencies#

As your database schema evolves, you will probably start using more advanced entities like views and functions. These entities are notoriously verbose to manage using plain migrations because the entire body must be recreated whenever there is a change. Using declarative schema, you can now edit them in-place so it’s much easier to review.

1
create table "employees" (
2
  "id" integer not null,
3
  "name" text,
4
  "age" smallint not null
5
);
6
7
create view "profiles" as
8
  select id, name from "employees";
9
10
create function "get_age"(employee_id integer) RETURNS smallint
11
  LANGUAGE "sql"
12
AS $$
13
  select age
14
  from employees
15
  where id = employee_id;
16
$$;

You don't need to order your schema files manually. When generating a migration, the engine analyzes the dependencies between your statements, such as foreign keys, views over tables, and functions used by triggers, and orders them automatically. File names and directory layout are for readability only. If your statements contain a dependency cycle, the command fails with a diagnostic instead of producing a broken migration.

This means you can organize supabase/schemas/ however you like. For example, one file per table:

1
.
2
└── supabase/
3
    ├── schemas/
4
    │   ├── employees.sql
5
    │   └── managers.sql
6
    └── migrations/
7
        ├── 20241004112233_create_employees_table.sql
8
        ├── 20241005112233_add_employee_age.sql
9
        └── 20241006112233_add_managers_table.sql

Or the per-schema layout that supabase db schema declarative generate produces, with one directory per database schema (such as supabase/schemas/public/tables/employees.sql) and cluster-level objects like roles under a reserved _cluster/ directory. You can also create a reserved _custom/ directory for hand-authored SQL covering objects the engine doesn't track. generate doesn't create it, and once it exists the export never writes to it or prunes it. See Known caveats.

Pulling in your production schema#

To set up declarative schemas on an existing project, export your production schema into declarative files:

1
supabase db schema declarative generate --linked

This writes per-object SQL files under supabase/schemas/, organized by database schema. In scripts, pass the target explicitly (--local, --linked, or --db-url) and add --overwrite to replace an existing tree without prompting. To later refresh the declarative tree from the remote database (for example, after a change was deployed outside your local workflow), run supabase db pull --declarative. It replaces the schema files without creating a migration or touching migration history.

generate writes schema files only. Because sync diffs your schema files against your migration history, that history must describe the same database before your first sync. If your project has no migrations yet, run supabase db pull first to create a baseline migration. Without a baseline, the first sync regenerates the entire schema as one migration. That migration can apply cleanly to an empty local database and still fail on db push, because the remote database already has those objects.

Rolling back a schema change#

During development, you may want to rollback a migration to keep your new schema changes in a single migration file. This can be done by resetting your local database to a previous version.

1
supabase db reset --version 20241005112233

After a reset, you can edit the schema and regenerate a new migration file. Note that you should not reset a version that's already deployed to production.

If you need to rollback a migration that's already deployed, you should first revert changes to the schema files. Then you can generate a new migration file containing the down migration. This ensures your production migrations are always rolling forward.

Known caveats#

Schema diffs are generated by pg-delta, which models most database entities, including tables, views, materialized views, functions, triggers, RLS policies, grants, comments, domains, partitions, and publications. There are still cases it cannot capture. Review every generated migration before committing.

Data manipulation language#

DML statements such as insert, update, and delete are never captured by a schema diff. This includes storage buckets, which are rows in the storage.buckets table rather than schema objects. A DML statement inside a declarative schema file is an error. Keep data changes in seed files or hand-written versioned migrations.

Default privileges on new objects#

Both engines treat permissions as part of the schema state. When you create a new object, generated migrations can include GRANT/REVOKE statements you didn't write, reflecting default privileges. If you haven't customized permissions, these lines are safe to remove.

Object kinds that aren't tracked#

Some object kinds are not tracked by the engine: casts, operators, operator classes and families, text search configurations, dictionaries, parsers, and templates, statistics objects, languages, transforms, and parameter ACLs. They are never silently dropped. The engine reports them as warnings, and you can pass --strict-coverage to turn those warnings into hard failures (useful in CI).

To use these objects with declarative schemas, create the reserved supabase/schemas/_custom/ directory if it doesn't exist and put their SQL there so dependent objects still resolve. generate never creates, overwrites, or prunes that directory. Deliver the change itself through a versioned migration, and make sure that migration sorts before the generated migration that depends on the object. Otherwise db reset and every later sync fail. Parameter ACLs are the exception because they live in a catalog shared by every database in the cluster. Keep them out of _custom/ and manage them through versioned migrations only.

Supabase-managed schemas#

Under the pg-delta engine, the diff excludes platform objects in Supabase-managed schemas such as auth and storage. It captures your customizations on top of them, such as triggers on managed tables, RLS policies on any table in auth, and RLS policies on storage.objects, storage.buckets, and realtime.messages. A trigger counts as yours only when its function lives outside the managed schemas (for example, a trigger on auth.users calling a function in public). A trigger whose function lives inside auth or storage is excluded, even if you created the function. The diff also skips other objects you create inside these schemas, such as your own functions or indexes.

Manage those objects through versioned migrations.

Extension-managed objects#

Objects that belong to an extension are recognized as the extension's, not yours. Anything an extension owns is excluded from diffs. Objects extensions create as they run, such as partitions maintained by pg_partman and queue tables created by pgmq, are never emitted as raw create table or drop table statements. The diff expresses changes to them through the extension's own API instead, such as select pgmq.drop_queue('q'); or a delete from partman.part_config row, and the CLI flags those statements as destructive. Create and change these objects through the extension's own functions, and review any generated API calls before committing.

Adopting an existing schema tree#

Two checks gate sync on a schema tree the CLI didn't generate:

• If your migrations call pg_net (for example, database webhooks), add [experimental.webhooks] with enabled = true to config.toml first. The section isn't part of the default init template.
• Declare the extensions your schema files depend on. The check trips for pg_net and for extensions your own migrations create. When it does, interactive sync offers to add the missing declaration and re-plan, or to stage a fresh export into a sibling -next directory and print the commands to adopt it. A non-interactive run only prints those commands. Extensions the local stack already ships, such as pgcrypto and uuid-ossp, don't trip the check even when undeclared, so declare them yourself.

Declarative schemas on the legacy migra#

Projects that opted out of pg-delta with [experimental.pgdelta] enabled = false can still use declarative schemas. The workflow has the same shape, but the commands differ, and the db schema declarative commands aren't available. See Diff engines for how to check which engine you're on and how the engines compare.

Generating migrations on the legacy engine#

On the legacy engine, supabase db diff reads your declarative files. When supabase/schemas/ contains files, it compares them against your migrations instead of reading the live database. Generate migrations with the local stack stopped.

1. Edit the files in supabase/schemas/.

2. Stop the local stack and generate a migration:

   1
   supabase stop
   2
   supabase db diff -f add_age

3. Start the stack and apply the migration:

   1
   supabase start
   2
   supabase migration up

As on pg-delta, changes made directly through Studio, the SQL editor, or psql are not picked up. Always edit the schema files, then diff.

Ordering schema files on the legacy engine#

The legacy engine applies your schema files in lexicographic order. The order matters when tables reference each other, because the parent table must be created first. To control the order, list files or glob patterns under [db.migrations].schema_paths in config.toml. The CLI expands the patterns, removes duplicates, and sorts the result lexicographically. This example always runs employees.sql first:

1
[db.migrations]
2
schema_paths = [
3
  "./schemas/employees.sql",
4
  "./schemas/*.sql",
5
]

Starting from an existing database on the legacy engine#

supabase db schema declarative generate isn't available on the legacy engine. Instead, dump your production schema into a single file and split it into smaller files over time:

1
supabase db dump > supabase/schemas/prod.sql

Limitations of the legacy engine #

In addition to the caveats above, the legacy migra engine has these limitations: view owner and grants, security invoker on views (the setting is silently dropped from the view definition), indexes on a materialized view aren't restored when the view is recreated, alter policy statements, column privileges, comments, roles, alter publication ... add table, and create domain statements. Add those entities through versioned migrations instead.

Moving back to pg-delta#

Remove enabled = false from [experimental.pgdelta] in config.toml, or set it to true, then follow Upgrade an existing project to pg-delta. For a declarative project, the steps that change your day-to-day work are removing schema_paths, replacing db diff with db schema declarative sync in scripts and CI, and dropping the supabase stop step.