# Declarative database schemas

Manage your database schemas in one place and generate versioned migrations.

## Overview

Declarative schemas provide a developer-friendly way to maintain [schema migrations](#schema-migrations).

[Migrations](/docs/guides/deployment/database-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.

Note: Which diff engine your project uses depends on `config.toml`. If it contains an `[experimental.pgdelta]` section with `enabled = true`, your project uses `pg-delta`. If the section is missing or `enabled` is `false`, your project uses the legacy `migra` engine. See [Diff engines](/docs/guides/local-development/diff-engines) for how the two differ and how to switch.

This guide describes the workflow on `pg-delta`. The `db schema declarative` commands require it and won't run on the legacy engine. For a single run of `sync` or `generate` without changing `config.toml`, pass the `--experimental` flag. `db pull --declarative` runs regardless of the `[experimental.pgdelta]` setting, but only use it on `pg-delta`. On the legacy engine it leaves `db diff` unable to run. If your project is still on `migra`, follow [Declarative schemas on the legacy `migra` engine](#declarative-schemas-on-the-legacy-migra-engine) instead.

## 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 your first schema file**

Create a SQL file in `supabase/schemas` directory that defines an `employees` table.

```sql name=supabase/schemas/employees.sql
create table "employees" (
  "id" integer not null,
  "name" text
);
```

2. **Generate a migration file**

Generate a migration file by diffing against your declared schema. This walkthrough passes `--no-apply` so the command only writes the file and the next step applies it. Without that flag, the command offers to apply the migration to your local database. Both `--apply` and the global `--yes` flag apply it without prompting, and a non-interactive run writes the file and silently skips the apply step.

```bash name=Terminal
supabase db schema declarative sync -f create_employees_table --no-apply
```

3. **Start the local database and apply migrations**

Start the local database first. Then, apply the migration manually to see your schema changes in the local Dashboard.

```bash name=Terminal
supabase start
supabase migration up
```

### Updating your schema

Caution: With declarative schemas, the files in `supabase/schemas/` are the source of truth. `supabase db schema declarative sync` compares those files against your migrations. It does not read the live database. Changes you make directly (Studio, the SQL editor, `psql`) are invisible to the diff, which reports "No schema changes found" and silently drops the change. Always edit the schema files, then run `sync`.

Don't use `supabase db diff` here, because it diffs a live database against your migrations and never uses `supabase/schemas/` as its baseline. If you run `supabase db diff -f` while schema files exist, the CLI prints a note that it ignored them. When your migrations are empty or outdated, the migration it writes can include objects your schema files already declare.

1. **Add a new column**

Edit `supabase/schemas/employees.sql` file to add a new column to `employees` table.

```sql name=supabase/schemas/employees.sql
create table "employees" (
  "id" integer not null,
  "name" text,
  "age" smallint not null
);
```

Note: Some entities like views and enums expect columns to be declared in a specific order. To avoid messy diffs, always append new columns to the end of the table.

2. **Generate a new migration**

Diff existing migrations against your declared schema.

```bash name=Terminal
supabase db schema declarative sync -f add_age --no-apply
```

3. **Review the generated migration**

Verify that the generated migration contain a single incremental change.

```sql name=supabase/migrations/<timestamp>_add_age.sql
alter table "public"."employees" add column "age" smallint not null;
```

4. **Apply the pending migration**

Start the database locally and apply the pending migration.

```bash name=Terminal
supabase migration up
```

### Deploying your schema changes

1. **Sign in to the Supabase CLI**

[Sign in](/docs/reference/cli/supabase-login) via the Supabase CLI.

```bash name=Terminal
supabase login
```

2. **Link your remote project**

Follow the on-screen prompts to [link](/docs/reference/cli/supabase-link) your remote project.

```bash name=Terminal
supabase link
```

3. **Deploy database changes**

[Push](/docs/reference/cli/supabase-db-push) your changes to the remote database.

```bash name=Terminal
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.

```sql name=supabase/schemas/employees.sql
create table "employees" (
  "id" integer not null,
  "name" text,
  "age" smallint not null
);

create view "profiles" as
  select id, name from "employees";

create function "get_age"(employee_id integer) RETURNS smallint
  LANGUAGE "sql"
AS $$
  select age
  from employees
  where id = employee_id;
$$;
```

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:

```bash
.
└── supabase/
    ├── schemas/
    │   ├── employees.sql
    │   └── managers.sql
    └── migrations/
        ├── 20241004112233_create_employees_table.sql
        ├── 20241005112233_add_employee_age.sql
        └── 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](#known-caveats).

Note: Under the `pg-delta` engine, the `[db.migrations].schema_paths` setting from earlier CLI versions no longer controls declarative file ordering, because ordering is automatic. The CLI warns when the setting lists any paths. On the legacy `migra` engine, `schema_paths` still controls the order in which declarative files are applied. See [Ordering schema files](#ordering-schema-files-on-the-legacy-engine).

### Pulling in your production schema

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

```bash name=Terminal
supabase db schema declarative generate --linked
```

This writes per-object SQL files under `supabase/schemas/` (or the directory set by `experimental.pgdelta.declarative_schema_path`), 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.

Note: CLI versions before 2.115.0 wrote the declarative tree to `supabase/database`. Version 2.115.0 and later read `supabase/schemas` and warn when `supabase/database` holds schema files, `supabase/schemas` is empty, and `declarative_schema_path` is unset. To clear the warning, do one of the following:

- Move the tree to `supabase/schemas/`.
- Keep the tree in place and set `declarative_schema_path = "./database"` under `[experimental.pgdelta]`.

`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.

```bash name=Terminal
supabase db reset --version 20241005112233
```

After a reset, you can [edit the schema](#updating-your-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.

Danger: SQL statements generated in a down migration are usually destructive. You must review them carefully to avoid unintentional data loss.

## 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](/docs/guides/local-development/seeding-your-database) or hand-written [versioned migrations](/docs/guides/deployment/database-migrations). Supported [extension configuration](#extension-managed-objects), such as named `pg_cron` jobs, is tracked separately through extension functions.

### 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.

`pg-delta` tracks named `pg_cron` jobs as extension configuration, even though they are stored as rows in `cron.job`. Generated migrations create jobs with `select cron.schedule_in_database(...)` and remove them with `select cron.unschedule(...)`. For a changed job, the migration calls `cron.unschedule(...)` followed by `cron.schedule_in_database(...)`.

With `pg_cron` enabled, declare each job in a SQL file under `supabase/schemas/` using `select cron.schedule('<name>', ...)`. Give each job a unique name. The engine skips jobs with a missing or duplicate `jobname` and reports a warning. To change or remove a job, edit or remove its declaration, then [generate and apply a migration](#updating-your-schema) with `supabase db schema declarative sync`.

### 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` engine \[#declarative-schemas-on-the-legacy-migra-engine]

Projects that haven't enabled `pg-delta` 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](/docs/guides/local-development/diff-engines) for how to check which engine you're on and how the engines compare.

Caution: On the legacy engine, don't run `supabase db pull --declarative`. The command runs regardless of the `[experimental.pgdelta]` setting and writes a per-object tree meant for the `db schema declarative` commands. It also points `[db.migrations].schema_paths` in `config.toml` at that tree. The next local `db diff` applies the files in lexicographic order without resolving dependencies between them, so it fails when a file sorts before an object it depends on.

If you've already run it, [switch to `pg-delta`](#switching-to-pg-delta), or undo both changes:

1. Delete the files the export wrote to the declarative directory (`supabase/schemas/` by default). Files that were already there are left in place.
2. Restore `[db.migrations].schema_paths` in `config.toml` to its previous value, or remove it if the export added it.

### 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:

   ```bash
   supabase stop
   supabase db diff -f add_age
   ```

3. Start the stack and apply the migration:

   ```bash
   supabase start
   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 processes the entries in the order you list them: it expands each pattern, sorts that pattern's matches lexicographically, and skips any file an earlier entry already matched. This example always runs `employees.sql` first:

```toml name=supabase/config.toml
[db.migrations]
schema_paths = [
  "./schemas/employees.sql",
  "./schemas/*.sql",
]
```

### 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:

```bash
supabase db dump > supabase/schemas/prod.sql
```

### Limitations of the legacy engine \[#limitations-of-the-legacy-engine]

In addition to the caveats above, the legacy [`migra`](https://github.com/djrobstep/migra) engine has these limitations: [view owner and grants](https://github.com/djrobstep/migra/issues/160#issuecomment-1702983833), [security invoker on views](https://github.com/djrobstep/migra/issues/234) (the setting is silently dropped from the view definition), [indexes on a materialized view aren't restored when the view is recreated](https://github.com/djrobstep/migra/issues/194), [alter policy statements](https://github.com/djrobstep/schemainspect/blob/master/schemainspect/pg/obj.py#L228), [column privileges](https://github.com/djrobstep/schemainspect/pull/67), [comments](https://github.com/djrobstep/migra/issues/69), [roles](https://github.com/djrobstep/migra/issues/240), [`alter publication ... add table`](https://github.com/supabase/cli/issues/883), and [`create domain` statements](https://github.com/supabase/cli/issues/2137). Add those entities through versioned migrations instead.

### Switching to `pg-delta` \[#switching-to-pg-delta]

Follow [Switch an existing project to `pg-delta`](/docs/guides/local-development/diff-engines#switch-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.