Database Migrations

How to manage schema migrations for your Supabase project.

Database migrations are SQL statements that create, update, or delete your existing database schemas. They are a common way of tracking changes to your database over time.

Schema migrations#

For this guide, we'll create a table called employees and see how we can make changes to it.

You will need to install the Supabase CLI and start the local development stack.

1
supabase migration new create_employees_table

1
create table if not exists employees (
2
  id bigint primary key generated always as identity,
3
  name text not null,
4
  email text,
5
  created_at timestamptz default now()
6
);

1
supabase migration up

1
supabase migration new add_department_column

1
alter table if exists public.employees
2
add department text default 'Hooli';

1
supabase migration up

Finally, you should see the department column added to your employees table in the local Dashboard.

Seeding data#

Now that you are managing your database with migrations, it would be great have some seed data to use every time you reset the database.

1
insert into public.employees
2
  (name)
3
values
4
  ('Erlich Bachman'),
5
  ('Richard Hendricks'),
6
  ('Monica Hall');

1
supabase db reset

You should now see the employees table, along with your seed data in the Dashboard! All of your database changes are captured in code, and you can reset to a known state at any time, complete with seed data.

Diffing changes#

This workflow is great if you know SQL and are comfortable creating tables and columns. If not, you can still use the Dashboard to create tables and columns, and then use the CLI to diff your changes and create migrations.

1
supabase db diff -f create_cities_table

1
create table "public"."cities" (
2
  "id" bigint primary key generated always as identity,
3
  "name" text,
4
  "population" bigint
5
);

1
supabase db reset

The last step is deploying these changes to a live Supabase project.

Deploy your project#

You've been developing your project locally, making changes to your tables via migrations. It's time to deploy your project to the Supabase Platform and start scaling up to millions of users!

Head over to Supabase and create a new project to deploy to.

1
supabase login

1
supabase link

1
supabase db push

1
supabase db push --include-seed

Visiting your live project on Supabase, you'll see a new employees table, complete with the department column you added in the second migration above.

Working with a team#

When multiple developers share a Supabase project, a few rules keep migrations from getting out of sync.

The golden rule: never change the remote database directly. Once you're using migrations, all schema changes — even small ones — should go through migration files. Using the Dashboard's SQL editor or Table Editor on your remote database bypasses the migration history, and db push will start failing with sync errors.

The team workflow:

1
supabase migration new your_change_description

1
supabase db reset
2
git add supabase/migrations
3
git commit -m "add migration: your_change_description"

1
git pull
2
supabase db reset

1
supabase db push

Diagnosing and fixing sync errors#

If db push fails with errors suggesting you run supabase migration repair, your local migration files and the remote database's migration history are out of sync. Here's how to diagnose and fix it.

How migration tracking works#

Supabase tracks which migrations have been applied on each database in a table called supabase_migrations.schema_migrations. When you run supabase db push, it compares your local supabase/migrations folder against that table and runs only the ones not yet applied, in order.

Git tracks your migration files. Supabase tracks what's been applied to each database. These are two separate systems that need to stay in sync.

Step 1: Check what's out of sync#

Start by listing the migration status across local and remote:

1
supabase migration list

This shows which migrations are applied locally, which are applied on the remote, and where they diverge.

Step 2: If you made changes on the remote database directly#

Pull the current remote state into a migration file to get back in sync:

1
supabase db pull

This creates a new migration file capturing the current remote schema. Commit it to git, then follow the standard workflow going forward.

Step 3: If the migration history table is wrong#

If a migration shows as missing in the remote history table but the schema change is actually already there (for example, it was applied manually), you can mark it as applied without re-running it:

1
supabase migration repair --status applied <migration-timestamp>

Or if a migration is recorded as applied but was never actually run:

1
supabase migration repair --status reverted <migration-timestamp>