supabase/apps/docs/content/guides/deployment/database-migrations.mdx

---
id: 'database-migrations'
title: 'Database Migrations'
description: 'How to manage schema migrations for your Supabase project.'
subtitle: 'How to manage schema migrations for your Supabase project.'
video: 'https://www.youtube-nocookie.com/v/Kx5nHBmIxyQ'
tocVideo: 'Kx5nHBmIxyQ'
---

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](/docs/guides/local-development#quickstart) the Supabase CLI and start the local development stack.

<Admonition type="note">

If a lock timeout error occurs, in your migration file, consider increasing your [`lock_timeout`](https://postgresqlco.nf/doc/en/param/lock_timeout/) setting.

</Admonition>

<StepHikeCompact>

  <StepHikeCompact.Step step={1}>
    <StepHikeCompact.Details title="Create your first migration file">
      To get started, generate a [new migration](/docs/reference/cli/supabase-migration-new) to store the SQL needed to create our `employees` table.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase migration new create_employees_table
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={2}>
    <StepHikeCompact.Details title="Add the SQL to your migration file">
      This creates a new migration file in supabase/migrations directory.

      To that file, add the SQL to create this `employees` table.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```sql name=supabase/migrations/<timestamp>_create_employees_table.sql
create table if not exists employees (
  id bigint primary key generated always as identity,
  name text not null,
  email text,
  created_at timestamptz default now()
);
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={3}>
    <StepHikeCompact.Details title="Apply your first migration">
      Run this migration to create the `employees` table.

      Now you can visit your new `employees` table in the local Dashboard.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase migration up
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={4}>
    <StepHikeCompact.Details title="Modify your employees table">
      Next, modify your `employees` table by adding a column for `department`.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase migration new add_department_column
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={5}>
    <StepHikeCompact.Details title="Add a new column to your table">
      To that new migration file, add the SQL to create a new `department` column.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```sql name=supabase/migrations/<timestamp>_add_department_column.sql
alter table if exists public.employees
add department text default 'Hooli';
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={6}>
    <StepHikeCompact.Details title="Apply your second migration">
      Run this migration to update your existing `employees` table.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase migration up
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

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

<Admonition type="note">

View the [complete code](https://github.com/supabase/supabase/tree/master/examples/database/employees) for this example on GitHub.

</Admonition>

### 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.

<StepHikeCompact>

  <StepHikeCompact.Step step={1}>
    <StepHikeCompact.Details title="Populate your table">
      Create a seed script in supabase/seed.sql.

      To that file, add the SQL to insert data into your `employees` table.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```sql name=supabase/seed.sql
insert into public.employees
  (name)
values
  ('Erlich Bachman'),
  ('Richard Hendricks'),
  ('Monica Hall');
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={2}>
    <StepHikeCompact.Details title="Reset your database">
      Reset your database to reapply migrations and populate with seed data.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase db reset
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

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.

The SQL that `db diff` generates depends on your project's diff engine.

<$Partial path="diff_engine_check.mdx" />

<Admonition type="note">

If you'd rather declare the state you want your database to be in and have migrations generated for you, manage your schema declaratively instead. On `pg-delta`, edit files in `supabase/schemas/` and generate migrations with `supabase db schema declarative sync`. Don't use `db diff` to generate migrations from declarative files on that engine, because it never uses `supabase/schemas/` as its baseline. On the legacy `migra` engine, `db schema declarative sync` won't run and `db diff` does read `supabase/schemas/`, so follow [Declarative schemas on the legacy `migra` engine](/docs/guides/local-development/declarative-database-schemas#declarative-schemas-on-the-legacy-migra-engine) instead. See [Declarative database schemas](/docs/guides/local-development/declarative-database-schemas).

</Admonition>

<Admonition type="caution">

Only use the Dashboard to make schema changes on your **local** database, then capture them with `supabase db diff`. Making schema changes directly on your **remote** database (via the SQL editor or Table Editor) bypasses the migration history and will cause `db push` to fail with sync errors. Once you're using migrations, all schema changes to your remote database should go through migration files only.

</Admonition>

<StepHikeCompact>

  <StepHikeCompact.Step step={1}>
    <StepHikeCompact.Details title="Create your table from the Dashboard">
      Create a new table called `cities`, with columns `id`, `name` and `population`.

      Then generate a [schema diff](/docs/reference/cli/supabase-db-diff).
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase db diff -f create_cities_table
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={2}>
    <StepHikeCompact.Details title="Add schema diff as a migration">
      A new migration file is created for you.

      Alternately, you can copy the table definitions directly from the Table Editor.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```sql name=supabase/migrations/<timestamp>_create_cities_table.sql
create table "public"."cities" (
  "id" bigint primary key generated always as identity,
  "name" text,
  "population" bigint
);
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={3}>
    <StepHikeCompact.Details title="Test your migration">
      Test your new migration file by resetting your local database.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase db reset
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

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](/dashboard) and create a new project to deploy to.

<StepHikeCompact>

  <StepHikeCompact.Step step={1}>
    <StepHikeCompact.Details title="Sign in to the Supabase CLI">
      [Sign in](/docs/reference/cli/supabase-login) to the Supabase CLI using an auto-generated Personal Access Token.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase login
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={2}>
    <StepHikeCompact.Details title="Link your project">
      [Link](/docs/reference/cli/supabase-link) to your remote project by selecting from the on-screen prompt.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase link
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={3}>
    <StepHikeCompact.Details title="Deploy database migrations">
      [Push](/docs/reference/cli/supabase-db-push) your migrations to the remote database.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase db push
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={4}>
    <StepHikeCompact.Details title="Deploy database seed data (optional)">
      [Push](/docs/reference/cli/supabase-db-push) your migrations and seed the remote database.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase db push --include-seed
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>
</StepHikeCompact>

Visiting your live project on [Supabase](/dashboard/project/_), 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:**

<StepHikeCompact>

  <StepHikeCompact.Step step={1}>
    <StepHikeCompact.Details title="Create a migration locally">
      Each developer creates migration files on their own branch, never touching the remote database directly.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase migration new your_change_description
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>

</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={2}>
    <StepHikeCompact.Details title="Test and commit">
      Reset your local database to apply the migration, then commit the migration file to git.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase db reset
git add supabase/migrations
git commit -m "add migration: your_change_description"
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>

</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={3}>
    <StepHikeCompact.Details title="Pull and reset when a teammate merges a migration">
      After pulling new migration files from git, reset your local database to apply them.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
git pull
supabase db reset
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>

</StepHikeCompact>

<StepHikeCompact>

  <StepHikeCompact.Step step={4}>
    <StepHikeCompact.Details title="One person deploys to remote">
      Coordinate so only one person runs `db push` at a time. Migration files are applied in timestamp order, so concurrent pushes from different machines can cause conflicts.
    </StepHikeCompact.Details>

    <StepHikeCompact.Code>

```bash name=Terminal
supabase db push
```

    </StepHikeCompact.Code>

  </StepHikeCompact.Step>

</StepHikeCompact>

<Admonition type="note">

For a more automated deployment approach, consider using [Supabase Branching](/docs/guides/deployment/branching) or a CI/CD pipeline that runs `supabase db push` on merge to your main branch.

</Admonition>

## 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:

```bash name=Terminal
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:

```bash name=Terminal
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.

What `db pull` captures in managed schemas depends on your project's diff engine.

<$Partial path="diff_engine_check.mdx" />

On `pg-delta`, a plain `supabase db pull` captures your customizations automatically, such as a trigger on `auth.users` that calls a function in `public`, or RLS policies on `storage.objects`. A trigger whose function lives inside `auth` or `storage` is excluded even if you created it, so deliver it through a versioned migration. On the legacy `migra` engine, the initial pull's `pg_dump` skips those schemas, but the diff pass that follows appends your triggers and RLS policies there to the same migration, and later pulls diff them too. Older CLI versions excluded `auth` and `storage` entirely and printed a message saying so. If you see that message, capture existing customizations once with explicit pulls:

```bash name=Terminal
supabase db pull --schema auth
supabase db pull --schema storage
```

See [Diff engines](/docs/guides/local-development/diff-engines) for what each engine captures in managed schemas.

### 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 already there (for example, it was applied manually), you can mark it as applied without re-running it:

```bash name=Terminal
supabase migration repair --status applied <migration-timestamp>
```

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

```bash name=Terminal
supabase migration repair --status reverted <migration-timestamp>
```

<Admonition type="caution">

`migration repair` updates the tracking table only — it does not apply or revert any SQL. Use it to correct the history record when you know the actual database state is correct.

</Admonition>