How to Convert Supabase Project to Declarative Schemas

Matt Brown July 9, 2026 Supabase
Watch an example migration

Supabase migrations can quickly get complicated. Early in a project the database structure rapidly evolves, often resulting in a complex array of migration files. It can be difficult to find the most recent table, view or enum when there are many different versions of it in the change history.

This slows down the development process because you’re constantly looking for the correct file. It also reduces clarity around the database architecture, making it more difficult to understand the codebase.

This article will show how to convert an existing Supabase project to a Declarative Schema pattern, bringing clarity and simplicity to your project.

Here’s the GitHub Repo with the conversion scripts.

TLDR Process Steps

supabase start
supabase db reset
./supabase-inventory/scripts/supabase_db_dump.sh
python3 ./supabase-inventory/scripts/split_schema.py
cp ./supabase-inventory/templates/grants.sql supabase/migrations/$(date +%Y%m%d%H%M%S)_grants.sql
supabase db reset
supabase db diff
supabase db diff --linked

What is declarative schema?

The simplest way to demonstrate is with tables. Rather than creating a new migration each time you add a field, you have a schemas folder that specifies the table structure, and the migrations are generated from there.

Here’s a quick example. With classic migrations, tables and views get redefined again and again across the history:

supabase/migrations/
  20240103091412_create_projects.sql
  20240117103055_create_reports_view.sql
  20240215142207_add_project_status.sql
  20240302091733_create_teams.sql
  20240402113045_update_reports_view.sql
  20240518084921_add_project_due_date.sql
  20240607120310_add_teams_billing_fields.sql
  20240701174502_update_reports_view_again.sql

Which file has the current definition of the reports view? What columns does projects actually have today? You have to replay the history in your head — and real projects have dozens more of these.

Declarative schemas bring structure. Now you just need to update the files below and new migrations are automatically generated by running supabase db diff -f name_of_change.

The existing migrations don’t change. You can squash them if you want but it is not necessary.

supabase/
  schemas/
    00_extensions.sql
    01_types.sql
    02_policy_helpers.sql
    tables/
       001_tablename.sql
       002_tablename.sql
       ...
    functions.sql
    grants.sql
    views.sql
    manual.sql        (auth triggers, storage policies — edit by hand)

Requirements

  • Python 3.11+
  • Supabase CLI 2.108+
  • Docker

Process steps

Copy supabase-inventory/ folder from GitHub into the root of your project.

supabase/
   migrations/
   functions/
supabase-inventory/

Step 1: Start Supabase and reset the database

supabase start
supabase db reset

Step 2: Create schema file that holds the database structure

This can be based on your local Supabase instance.

./supabase-inventory/scripts/supabase_db_dump.sh

Or you can add a project ref and it will be based on that remote instance. The project ref is the ~20-character id in your dashboard URL: supabase.com/dashboard/project/<project-ref>.

PROJECT_REF="abcdefghijklmnopqrst" ./supabase-inventory/scripts/supabase_db_dump.sh

This outputs supabase-inventory/output/schema.sql, which has all of the sql used to define the database.

Step 3: Split schema.sql into separate schema files

python3 ./supabase-inventory/scripts/split_schema.py

split_schema.py organizes sql around tables. So for a particular table, the schema, RLS, constraints, keys, grants are all in the same file.

Safe to rerun any time. Each run:

  • Deletes and regenerates all generated files, including tables/*.sql — renamed or reordered tables never leave stale copies behind. Never edit generated files by hand; edit via rerunning, or use manual.sql.
  • Never touches schemas/manual.sql.
  • Updates schema_paths in supabase/config.toml automatically. This specifies the order the schema files are applied when building the shadow database. Anything that’s depended on must be listed first.
  • Moves functions referenced by policies or triggers into 02_policy_helpers.sql, which loads before the table files. That file starts with SET check_function_bodies = off; so function bodies can reference tables that don’t exist yet — bodies are only validated on first call. Functions are otherwise kept exactly as dumped, so schema files always match what migrations built.
  • Anything it can’t classify goes to output/extracted/review.sql with a warning — inspect it if the warning appears.

Each file will look something like this:

create table public.projects (...);

alter table public.projects
  add constraint ...;

create index ...;

create trigger ...;

alter table public.projects
  enable row level security;

create policy ...;

grant ...;

create function ...;

create or replace trigger ...;

Other supporting sql (extensions, types, functions, views, grants) is stored in the relevant files shown in the folder structure above.

Step 4: Add the grants migration

cp ./supabase-inventory/templates/grants.sql \
   supabase/migrations/$(date +%Y%m%d%H%M%S)_grants.sql

Then run supabase db reset to apply it.

Why is this needed? The grants that let anon, authenticated, and service_role touch your tables come from default privileges, not from your migration files. Recent CLI versions seed the same default privileges locally, so a fresh supabase db reset usually gets the grants for free — but depending on your CLI version and migration history (e.g. a REVOKE in an old migration, or objects created under a different role), a rebuilt database can be missing grants, and supabase db diff then shows grant drift that no schema edit can fix. This migration guarantees the grants exist either way; if they already do, every statement is a no-op:

  1. GRANT ... ON ALL TABLES IN SCHEMA public covers every existing table, sequence, and function in one statement each. Postgres resolves “all tables” against the live database when the migration runs, so there’s nothing to generate or keep in sync.
  2. ALTER DEFAULT PRIVILEGES makes tables created by future migrations get the same grants automatically, so the drift never comes back.

Copy it in once during initial setup — after that it’s a plain migration file like any other.

Step 5: Compare declarative schema with database

supabase db diff validates your schema files: 1. Spins up a shadow database and applies your schemas/ files to it 2. Compares that shadow DB against your local DB (built from migrations) 3. Outputs SQL needed to reconcile the two

A shadow build failure means a syntax error or ordering problem in your schema files.

Locally:

supabase db diff

With linked remote (compares local against prod):

supabase db diff --linked

If there are differences, reconcile by either adding to manual.sql (below) or creating a new migration with supabase db diff -f my_new_migration_name.

Auth triggers, storage policies, etc.

supabase db dump --local only exports the public schema. Auth-schema triggers and storage policies are never in the dump, so split_schema.py cannot auto-place them. Whenever the diff shows an unexpected drop trigger on auth.* or drop policy on storage.objects, copy the CREATE statement from the relevant migration into schemas/manual.sql — that file is never overwritten by split_schema.py.

Once supabase db diff is empty and supabase db diff --linked shows only platform-managed items like pg_cron, then you are done. This shows that there is parity between the new declarative schema and the current db.

How to update schema

Never write a migration by hand for structural changes. Edit the schema file, run one diff, commit both. One db diff covers all changes regardless of how many schema files you touched.

  1. Edit the relevant file in supabase/schemas/ — e.g. add a column to tables/007_teams.sql

  2. Generate the migration:

    supabase db diff -f describe_your_change

    This compares ALL schema files against the local DB in one pass and writes one migration file.

  3. Commit the edited schema file and the generated migration together.

  4. Run supabase db reset to verify the full history still builds cleanly.

That’s it. Your project is now set up to use declarative schemas. You shouldn’t need to write migrations by hand in the future. It’s still helpful to look at the generated migration file after each supabase db diff -f to confirm it does what’s intended.

← Back to all posts