Supabase Migrations: Schema Changes as Files

A schema change should be a file in the repository, not a sequence of clicks in a dashboard. In Supabase that file lives in supabase/migrations, and the same file is applied on a laptop, on a colleague's machine and on production. The database ends up with a history you can read, review and rebuild. That history is what makes a schema change reviewable at all, and it is the difference between a database you can reproduce and one you are afraid to touch.

What counts as a migration in Supabase?

A migration is a SQL file that creates, updates or deletes part of your database schema. Supabase's migrations guide describes migrations as the record of schema changes over time, kept in supabase/migrations. Each file follows the pattern <timestamp>_<name>.sql, for example 20230306095710_schema_test.sql, and the CLI applies them in timestamp order. Seed data is separate: it goes in supabase/seed.sql, not in a migration.

That separation is not decoration. A migration describes structure, and every environment should end up with the same structure. Seed data is a convenience for local work and tests. Put rows in a migration and every push carries them to production, including the ones you only wanted on your machine.

How do you create a migration?

When you know the SQL you want, create an empty migration and write it:

supabase migration new add_department_column

The CLI reference says the command creates the file locally, creates supabase/migrations if it does not exist, and prints the path, for example supabase/migrations/20230306095710_schema_test.sql. The file starts empty, so the SQL is yours to write and yours to review.

When the change happened by clicking in the local Dashboard, let the CLI write the SQL for you:

supabase db diff -f create_cities_table

The migrations guide gives that example. db diff compares the local database against a shadow database built by applying the migrations in a separate container, and -f saves the difference as a new migration file instead of printing it. This is the bridge between experimenting in a UI and keeping a file. The UI is a fine place to try a design; it is a bad place to leave one.

How is the local database brought up to date?

Local Supabase needs a container runtime. The local development guide lists Docker Desktop, OrbStack, Rancher Desktop and Podman, and says a Docker-compatible API is a prerequisite for supabase start. A fresh checkout is two commands, supabase init and supabase start, with the local dashboard at http://localhost:54323.

Then apply whatever the database has not seen:

supabase migration up --local

The CLI reference describes that flag as applying pending migrations to the local database. To see where you stand, run supabase migration list --local. Against a linked project, the same command compares local files with the remote history, which the reference says is stored in supabase_migrations.schema_migrations. Only timestamps are compared: not names, not contents. Keep that in mind, because it explains the failure in the next sections.

What is the difference between applying and resetting?

Applying runs the migrations the database has not seen yet and leaves existing data alone. Resetting throws the database away and rebuilds it. supabase db reset recreates the local Postgres container, applies every migration in supabase/migrations from the first one, and then runs supabase/seed.sql if it exists. The CLI reference adds the part people forget: any other data or schema change made during local development is discarded.

We reset often, on purpose. A reset is the cheap test of the claim that the migrations can rebuild the database from nothing. Applying tests the next step; resetting tests the whole history. If a reset fails on a clean machine, a new environment or a recovery will fail the same way, only at a worse moment.

Reset has a destructive form too. With --linked or --db-url, the command runs SQL that drops all user-created entities in that remote database. Treat it as a production command, and keep your resets local unless you have a backup and a reason.

How do migrations reach production?

Link the project once, then push:

supabase link --project-ref <ref>

supabase db push

db push pushes all local migrations to the remote database. The first run creates supabase_migrations.schema_migrations; later pushes skip the migrations already recorded there. --dry-run prints the migrations that would be applied without applying them, and --include-seed also seeds the remote database. The reference notes that push and pull require a linked project.

Why is a change made in Studio not a migration?

Because nothing was written to a file or to the history table. On a local stack you can change a table in the Dashboard and capture it afterwards with db diff. On a hosted project the migrations guide is blunt: changes made through the SQL editor or the Table Editor bypass the migration history and will cause db push to fail with sync errors. The guide's rule is that all schema changes to the remote database should go through migration files only.

The consequence is reproduction. A column added in the remote Table Editor exists in production and nowhere else. A fresh local stack built from the repository does not have it, a colleague cannot see it in a diff, and the next db reset produces a database that differs from production with no record of when the difference appeared. That is the situation migrations exist to prevent.

If the change already happened remotely, the documented rescue is supabase db pull. The reference says it pulls schema changes from a remote database and writes them as a new migration file in supabase/migrations, and that a new row can be inserted into the migration history table to reflect the remote state. Do it while the change is fresh and you still remember why it exists.

What goes wrong when you edit a migration that has already run?

The history table stores timestamps, not checksums. Once production has a row for a migration, db push considers it done and skips it. If you edit the file afterwards, production does not re-run it. A new local database, or a colleague who has never applied that version, does run the edited file. Two environments now have different schemas built from the same history, and no command complains.

This happens most often with a small correction. Someone applies a migration, notices a missing index or a wrong default, and edits the file instead of adding a second one, because editing feels tidier. The tidiness is local. The file that produced production is no longer the file in the repository, and the history no longer describes what actually ran.

The safer habit is to add a new migration that changes what the old one did:

supabase migration new fix_department_column_type

The history stays append-only, every environment applies the same sequence, and the correction is visible in review. When the only problem is bookkeeping, supabase migration repair can mark a version applied or reverted, but the reference says it updates the tracking table only. It does not run or revert any SQL, so it will not make a real schema difference go away.

There is also supabase migration squash, which combines local migrations into one file and is useful for removing repeated modifications from a long history. Read its page before using it: data manipulation statements such as insert, update or delete are omitted from the squashed migration, and cron jobs, storage buckets and vault secrets have to be re-added by hand. Squashing tidies a history; it does not repair a divergence.

Why read the generated SQL before it runs?

db diff writes to stdout by default and only creates a file when you pass -f. Either way, the SQL is the artifact. Read it the way you read code: does it drop a column, change a type, rename a table, add or remove a policy? A diff is a proposal, and some proposals lose data. A migration that runs cleanly can still be the wrong migration.

What we look for in a generated diff:

• Destructive statements such as drop column and drop table, and type changes that rewrite or truncate values.
• Defaults and nullability on columns that already hold rows, because adding a required column to a populated table can fail or backfill unpredictably depending on the statement.
• Row-level security policies and grants, which a schema diff can miss or represent differently from what you intended.
• Objects you did not expect, such as functions, triggers or indexes created by an extension.

Review is cheap because the migration is a text file in a pull request. A reviewer sees the exact statement that will run against production instead of a summary of it. Before the push, supabase db push --dry-run lists what will be applied. Two habits cover most of the risk: read the diff before committing it, and read the dry run before pushing it. Our article on git-based content makes the same argument for text; a database schema deserves the same treatment.

Where this fits in our work

If you run several local stacks, the CLI's default ports allow one local project per machine, as the local development guide notes. Supabase Local Manager is an independent project, in early access and not affiliated with Supabase, Inc., that runs many local Supabase stacks from one Node and Express service. Its migrations area lists, creates, diffs and applies migrations per project, which keeps the file-based workflow above intact.

Danube Labs builds and maintains systems where a schema outlives the feature that introduced it. If you want a second pair of eyes on how your database changes reach production, see our custom software work or book a free intro call.