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:

It also supports explicit source/target references now:

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:

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:

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:

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:

How to enable it#

Add this to your config.toml:

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:

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.