[Public Alpha] Declarative Schema Management with pg-delta

[avallete]

Experimental: Declarative Schema Management with pg-delta

We've been working on a new way to handle database schema changes in the Supabase CLI, and it's now at a point where we want people to try it and tell us what they think.

TL;DR

The CLI now ships with pg-delta, our own Postgres schema diffing engine, and an experimental declarative schema workflow on top of it. Basically: you describe your schema as SQL files, run a command, and the CLI generates the migration for you. No more writing migrations by hand if you don't want to.

Available today behind an experimental flag. This is still very much alpha, things will break, coverage is not complete yet. We want your feedbacks.

Why we built our own

Schema diffing in the CLI has been relying on third-party tools (migra, pgAdmin) and each one comes with its own set of issues, missing coverage, and maintenance problems. On top of that, the whole migration workflow has always been imperative: you write the migration yourself, or you diff against a live database and hope it catches everything.

We looked at existing diffing tools but none of them really fit what we needed. Most of them carry a lot of legacy support for older Postgres versions, and we didn't want that tech debt. pg-delta only targets Postgres 15+, which lets us start clean and take advantage of newer catalog features without workarounds. More importantly, we want a tight integration with all of Supabase's features long term: all the extensions we ship, auth/storage schemas, RLS policies, the whole platform. Building on top of someone else's diffing engine means we'd always be fighting upstream to get Supabase-specific stuff supported. Owning the diffing layer gives us full control over that.

A lot of developers, especially those coming from ORMs or tools like Terraform, expect something more declarative: you describe what the schema should look like, and the tooling figures out the diff. We wanted to offer that.

What's new

pg-delta as a diffing backend

@supabase/pg-delta is our own schema diff engine, written in TypeScript from scratch. It lives in the supabase/pg-toolbelt monorepo alongside pg-topo (topological sorting for DDL statements). You can already use it as the diffing backend for supabase db diff:

supabase db diff --use-pg-delta

It also supports explicit source/target references now:

supabase db diff --from migrations --to local --use-pg-delta

Declarative schema workflow

Everything goes through a single command: supabase db schema declarative sync.

One thing we cared about: we wanted to stay pure SQL. No custom DSL, no YAML, no config files describing your tables. Just .sql files. But we also wanted the freedom you get with code based declaration, so you don't need to worry about the order of statements in your declarative schema. You can split and organize your files however makes sense to you, pg-delta will figure out the right execution order. If your users table references a type defined in another file, that's fine. Just write your schema the way you want it organized.

If you don't have a declarative schema yet, the CLI will guide you through the setup. It asks where to generate from (local database or a custom URL), exports your schema into SQL files under supabase/database/, and you're ready to go:

$ supabase db schema declarative sync

No declarative schema found. Generate a new one ? [Y/n] 

    Generate declarative schema from:                 
                                                      
  >  1. Local database [generate from local Postgres] 
     2. Custom database URL [enter a connection string]

Reset local database to match migrations first? (local data will be lost) [y/N] y
Creating shadow database...
Applying declarative schemas via pg-delta...
Applied 21 statements in 1 round(s).
Declarative schema written to supabase/database
Creating shadow database...
Initialising schema...
Seeding globals from roles.sql...
Applying migration 20260320160742_init.sql...
Applying migration 20260410150534_disable_pg_net.sql...
No schema changes found

From there, the SQL files in supabase/database/ are your source of truth. You edit them directly, for example adding a column to a table, and run sync again:

$ supabase db schema declarative sync

Creating shadow database...
Applying declarative schemas via pg-delta...
Applied 21 statements in 1 round(s).
Generated migration SQL:
ALTER TABLE public.projects ADD COLUMN deleted_at timestamp without time zone;
                                                                              
Enter a name for this migration (press Enter to keep 'declarative_sync'): add_deleted_at_to_projects
Created new migration at supabase/migrations/20260413181801_add_deleted_at_to_projects.sql
Apply this migration to local database? [Y/n] 
Connecting to local database...
Applying migration 20260413181801_add_deleted_at_to_projects.sql...
Migration applied successfully.

The full workflow is: edit your schema files and run sync. The CLI will create a migration and ask you to apply it.

If your changes include destructive statements, the CLI will warn you before applying:

$ supabase db schema declarative sync

Creating shadow database...
Initialising schema...
Seeding globals from roles.sql...
Applying migration 20260320160742_init.sql...
Applying migration 20260410150534_disable_pg_net.sql...
Applying migration 20260413181801_add_deleted_at_to_projects.sql...
Generated migration SQL:
ALTER TABLE public.projects DROP COLUMN deleted_at;
                                                   
Enter a name for this migration (press Enter to keep 'declarative_sync'): drop_deleted_at
Created new migration at supabase/migrations/20260413182237_drop_deleted_at.sql
Found drop statements in schema diff. Please double check if these are expected:
ALTER TABLE public.projects DROP COLUMN deleted_at
Apply this migration to local database? [Y/n] 
Connecting to local database...
Applying migration 20260413182237_drop_deleted_at.sql...
Migration applied successfully.

It also uses catalog caching so subsequent runs don't redo all the shadow-database work.

You can also skip all the interactive prompts with flags, which is useful for CI or agentic use:

supabase db schema declarative sync --apply --name add_back_deleted_at

How to enable it

Add this to your config.toml:

[experimental.pgdelta]
enabled = true

Or just pass --experimental on any of the declarative commands.

The declarative schema path defaults to database/ in your project root but you can change it:

[experimental.pgdelta]
enabled = true
declarative_schema_path = "my-schema/"

What we want to know

This is experimental and we want to shape it based on real usage. Some things we're curious about:

• Does the edit > sync loop work for you? Or do you need something different?
• What's missing? Any Postgres objects or patterns that pg-delta doesn't handle well?
• How does it compare to whatever diffing tools you've been using (migra, pgAdmin, Atlas, pgroll, etc.)?
• Debugging: when something goes wrong, the CLI generates a debug bundle you can attach to issues. Is that helpful? What else would make debugging easier?

What's next

To be clear: this is alpha. Even if pg-delta cover a lot of Postgres object type it isn't battle tested yet, and you will probably run into cases where the diff is wrong or incomplete. That's expected at this stage and exactly why we're putting it out now, we need real world usage to find the gaps.

We're actively improving pg-delta coverage and the declarative workflow. Goal is to make this the default diffing engine and eventually move the declarative commands out of experimental. What you tell us here directly affects what we work on next.

Links

• pg-toolbelt repo (pg-delta + pg-topo)
• CLI PR: Declarative sync command
• CLI PR: Default folder rename to database/

---

Give it a try and let us know what you think. File issues on pg-toolbelt for diffing bugs and on cli for workflow stuff. Or just drop your thoughts here.

[tmerse]

Nice to hear that this is finally taking shape.

Two questions: How closely does the default database structure need to be followed, or can it be completely free-form?

With pg-delta the default structure, created via supabase db schema declarative sync

Looks like

├── cluster
│   └── extensions
│       ├── hypopg.sql
│       ├── index_advisor.sql
│       ├── pg_cron.sql
│       └── pg_trgm.sql
└── schemas
    └── public
        ├── functions
        ├── sequences
        ├── tables
        ├── types
        └── views

With the existing migra, we cut the layout by feature, e.g.

- 00_shared
   - 01_shared_enums
- 01_users
   - 01_users_enums
   - 02_users_tables
   - 03_users_functions
- 02_other_feature
   - 01_other_feature_enums
   - 02....
- 99_cross_features
   - 01_...

So my first question is: How far can the structure for pg-delta be altered?

And second: Is there any roadmap regarding missing features/things that are still wip? How mature do you think this is already, are there obvious caveats/things still missing/lacking?

Thanks again. Love seeing this moving forward.

[avallete]

Hi there,

For the first question: pg-delta declarative apply is meant to be fairly free-form. It parses the SQL statements across all *.sql files in the declarative folder, then tries to statically reorder them as much as possible, and, when needed, applies them in multiple rounds to resolve dependencies. So the layout does not need to strictly follow the default generated structure. In other words, you are not forced into a fixed folder hierarchy like schemas/public/tables/... if you prefer organizing things by feature instead.

In your case, you would just move your current declarative schema under the right folder, run supabase db schema declarative sync. It should apply your declarative schema, compare it against the migrations, and either determine that there are no changes or produce the migration needed to reconcile the two. The shape in your case could look like this:

./supabase/database/
├── 00_shared
│   └── 01_shared_enums.sql
├── 01_users
│   ├── 01_users_enums.sql
│   ├── 02_users_tables.sql
│   └── 03_users_functions.sql
├── 02_other_feature
│   ├── 01_other_feature_enums.sql
│   └── ...
└── 99_cross_features
    └── 01_...

That said, pg-delta does not care about your file naming convention for apply order. The only thing that matters is the set of statements inside the declarative folder, so you could also drop the 01/02/... prefixes if you want.

This is still alpha software, so while the goal is to support arbitrary organization as long as the dependency graph is solvable, there may still be edge cases where unusual layouts or patterns expose bugs.

On maturity: we have compared it against other diffing tools like migra and pgschema, and it is already broadly in similar range. We have also been dogfooding it on our own repos and apps. The main thing it still needs is more real-world coverage, edge cases, and bug reports so we can keep hardening it. One other important point is that even though pg-delta is still maturing, it does not lock you into a proprietary format or workflow. At the end of the day, it just produces plain SQL. That means the adoption cost is lower: you can try it, use the generated SQL in the rest of your existing tooling, and keep your workflow fairly flexible. It also reduces the churn risk if the experience does not end up fitting your needs, since you are not tied to some opaque intermediate format or tool-specific migration artifact.

One area we already know is still lacking is extension-specific diffing. Some extensions blur the line between schema and data. A good example is storage, where rows like buckets are technically data, but they also drive platform behavior and infrastructure. Longer term, we want diffs to understand and handle cases like that properly, as well as similar patterns in other extensions such as pgmq, pg_cron, vector, etc. That is not there yet.

Another area we want to improve is migration guidance and safety analysis. For example, detecting risky changes, warning about potential data loss, and suggesting safer alternatives for destructive or expensive operations. That part is also still early and not fully built out yet.

[star26bsd]

I am so hyped by this! This is a biiiig step forward. Will start testing it and report back.

[star26bsd]

Hi!

One thought that comes to mind after playing around a bit is that I would love to have supabase db schema declarative sync --name test not go in interactive mode by default OR have a switch to turn it off. Current behaviour (as of 2.95.4):

...
Created new migration at supabase/migrations/20260428093833_test.sql
Apply this migration to local database? [Y/n] n
...

The CLI currently provides no option to neither apply using --apply nor go in interactive mode, which is the opposite of what I am looking for.

Reasoning

I see benefits in keeping the two-step process on applying a migration file. That way, an agent can check the generated migration file against certain specs and apply it in a dedicated step using our beloved supabase migration up

Or will that vanish and that is why the --apply has been introduced? Is my thought process flawed? :)

Thanks!
Stephan

[avallete]

Hey there !

Thank's for the feedback, I think thats a good ask, indeed there is no simple bail out for non interactive, no --apply migration. We'll see into fixing this.

Meanwhile, note that if your terminal in a non-tty environment, then the behavior your mention is the one that occurs (migration created, not applied). That can be emulated by closing stdin by sending in to a pipe like so:

true | npx supabase@latest db schema declarative sync --experimental --name add_published_at 2>&1 | cat

Will result in:

Generated migration SQL:
ALTER TABLE public.posts ADD COLUMN published_at timestamp with time zone DEFAULT now() NOT NULL;

Created new migration at supabase/migrations/20260429122310_add_published_at.sql

[alexb-tellmira]

Hi! Is it (or will it be) possible to create a schema from declarative files skipping versioned migrations completely? I see that pgdelta declarative apply exists, and I think it would make sense to have a command like supabase db schema declarative apply for local database resets or for spinning up new instances in CI.

[avallete]

Hey there ! Good question.

In theory yes, but there are two slightly different concepts here:

• A declarative schema describes the state your database should be in
• Migrations describe how your database evolves to reach that state

Tools like pgdelta’s declarative apply are great for taking a (mostly empty) database and bringing it directly to a desired state so something like supabase db schema declarative apply for local resets or CI bootstrapping makes sense conceptually.

That said, we don’t see it as a full replacement for migrations.

Migrations are important because they capture intent, which a diff-based approach can’t reliably infer. For example, if a column disappears and another appears, a diff can’t know if you meant to drop it, rename it, or transform data along the way.

Also, in practice, it’s usually safer (and often faster) to apply migrations in order than to rely on a declarative apply:

• Migrations are deterministic and ordered
• They are applied step-by-step with explicit control
• They don’t depend on heuristics to “figure out” what changed

Whereas declarative apply is more of a “do its best” approach:

• No guaranteed ordering of operations
• No strong correctness guarantees around intent
• More room for unexpected diffs or destructive changes

---

So the mental model is more:

• State = your database (or a declarative snapshot of it)
• Transitions = your migrations

In practice:

• Declarative apply → great for bootstrapping / CI / fast resets
• Migrations → necessary for controlled, safe evolution over time

A common pattern is also to generate a “base” schema (or squashed migration) from the current state, and then continue with migrations from there.

Curious how you’d want to use this in CI, just for faster setup ? or as a replacement for migrations entirely?

[alexb-tellmira]

Mostly for faster setup. We have a long tail of versioned migrations and are a bit hesitant to squash them yet. I’m curious to see if applying declarative SQL files would be a cleaner and faster way to get the environment ready.

[mntzrr]

I've been testing it out. One quick thing I noticed: once you add [experimental.pgdelta] enabled = true to config.toml, it kicks in automatically without needing to pass --use-pg-delta (with or without --experimental) flags every time.

I did have one question though: is it intentional that pg-delta ignores triggers on the auth schema? I ran both of these:

bun supabase db diff --schema auth,storage -f temp
bun supabase db diff -f temp

In both cases, a trigger I attached to auth.users was completely missing from the generated migration. For context, switching the engine to --use-migra picks the trigger up just fine, so it seems specific to pg-delta rather than a general CLI issue.

Is this intentional for the alpha stage, or just a missing feature on the roadmap?

Aside from this, it's working superb. It actually caught domains that migra missed entirely, and the security_invoker support has saved me from having to manually edit migrations. Really excellent step forward!

[avallete]

Hi there!

Yep hat behavior is intentional (for now). We currently exclude “Supabase-controlled schemas” (like auth, storage, etc.) from diffing/declarative apply so we can focus on user-owned database objects and avoid accidentally generating changes against managed internals.

That said, your case highlights a real gap: user-defined objects inside those schemas (like your trigger on auth.users) are getting filtered out as well, which is probably too aggressive. We’ll likely need to refine this so we still ignore Supabase-managed objects, but keep anything user-defined.

Would you mind opening an issue on GitHub here: https://github.com/supabase/pg-toolbelt
A minimal reproducible example would really help us validate and fix this properly.

Glad to hear the rest is working well especially the domains + security_invoker support, that’s exactly the kind of improvements we were aiming for 🙂

[deejiw]

It would be great if we can toggle between destructive and less destructive mode ie migration files first drop a specific enum column then add it back instead of recreating the whole table.