SQL migrations

A migration is a SQL file that changes your schema: creates a table, adds a column, writes a policy. Keeping every change as a numbered file, applied in order, means you can rebuild the schema anywhere, review changes before they ship, and see exactly what ran in production and when.

How history is tracked

Applied migrations are recorded in the project's database, in a table the Supabase CLI also uses:

-- supabase_migrations.schema_migrations
--   version     text primary key   -- UTC timestamp, YYYYMMDDHHMMSS
--   statements  text[]             -- the SQL that ran
--   name        text               -- e.g. add_invoice_due_date

select version, name
from supabase_migrations.schema_migrations
order by version;

The version sorts in the order migrations were written, and a version that's already in the table is never applied twice. The MCP server's list_migrations tool reads this same table, so migrations applied by an AI agent, by the Supabase CLI or by hand (if you record them) all show up in one list.

Write a migration

An invoice tool needs a due date and a way to find overdue invoices. A good migration is small, does one thing, and is safe to run on a live table:

-- 20261111093000_add_invoice_due_date.sql
set lock_timeout = '5s';

alter table public.invoices
  add column due_on date;

update public.invoices
  set due_on = issued_on + 30
  where due_on is null;

create index invoices_due_on_unpaid_idx
  on public.invoices (due_on)
  where status <> 'paid';

Adding a nullable column is instant in Postgres, even on a large table. lock_timeout makes the migration fail quickly rather than wait behind long-running queries and block everyone else. The backfill update is fine for thousands of rows; for millions, run it in batches outside the migration.

Apply with the MCP server

An AI coding agent connected to the SupaHost MCP server can call apply_migration with a snake_case name (1 to 80 characters) and the SQL. The server:

1. creates supabase_migrations.schema_migrations if it doesn't exist yet,
2. runs your SQL and inserts the history row in one transaction, with a version of the current UTC time.

If any statement fails, nothing is applied and nothing is recorded. Because it's one transaction, statements that can't run in a transaction, such as create index concurrently, fail here; run those separately (see Indexes).

You can ask your agent in plain words, for example "Add a nullable due_on date column to invoices, backfill it as issued_on + 30, and index unpaid invoices by due date. Apply it as a migration." Writes made over MCP appear in Dashboard → Activity as MCP (<token name>). A read-only token can't apply migrations at all. See MCP tools reference.

Apply in Studio's SQL editor

Open Dashboard → your project → Database tab → Open Studio (owner only), go to the SQL Editor, paste the migration and run it. Statements sent in one run execute together.

Studio doesn't record history. To keep the table accurate, add the row yourself in the same run:

-- ...the migration statements above, then:
insert into supabase_migrations.schema_migrations (version, name, statements)
values ('20261111093000', 'add_invoice_due_date', array['see repository file']);

If the schema doesn't exist yet in a new project, create it once first, matching the columns shown above:

create schema if not exists supabase_migrations;
create table if not exists supabase_migrations.schema_migrations (
  version text primary key,
  statements text[],
  name text
);

Apply with psql over SSH (Business)

On Business and Enterprise you can run migration files with psql, either through the SSH tunnel or with the restricted SSH command. Both connect as postgres. See SSH access and direct Postgres for setup.

# Through the tunnel (ssh -N -L 5432:127.0.0.1:5432 [email protected])
psql "$DATABASE_URL" --single-transaction -v ON_ERROR_STOP=1 \
  -f supabase/migrations/20261111093000_add_invoice_due_date.sql

# Or without a tunnel
ssh [email protected] sudo supahost-psql app-one \
  < supabase/migrations/20261111093000_add_invoice_due_date.sql

--single-transaction and ON_ERROR_STOP give you the same all-or-nothing behaviour as apply_migration. Record the history row as shown in the Studio section, or use the Supabase CLI over the tunnel, which records it for you: see Migrations workflow: local to SupaHost.

Test on a branch first (Pro and above)

On Pro, Business and Enterprise, try risky migrations on a copy of the project before production:

1. Dashboard → your project → Branches tab → create a branch. Keep "Copy data, users and files" on to test against real data, or turn it off for schema only.
2. Point your agent or Studio at the branch and apply the migration there.
3. Test the app against the branch's URL and keys. A branch is a separate project with its own keys.
4. Apply the same migration to the main project. Branches have no merge.
5. Delete the branch. It counts as a project while it exists.

A branch copies the supabase_migrations history, so list_migrations on the branch shows the same starting point as its parent. See Branching.

Undoing a migration

There are no automatic "down" migrations. To reverse a change, write a new migration that undoes it:

-- 20261112080000_drop_invoice_due_date.sql
drop index if exists public.invoices_due_on_unpaid_idx;
alter table public.invoices drop column if exists due_on;

For destructive changes such as dropping a column or table, export or copy the data first. On Pro and Business, point-in-time recovery can also restore the project's database to a moment before a bad migration: see Point-in-time recovery.

Habits that keep migrations safe

• Prefer additive changes. Add new columns and tables, move the app over, then remove the old ones in a later migration.
• Rename in steps. Add the new column, write to both, backfill, switch reads, then drop the old one. A direct rename breaks clients still using the old name.
• Keep files in your repository under supabase/migrations/ so the schema can be rebuilt locally and reviewed in pull requests.
• Reload the Data API cache if a new table or function doesn't appear: notify pgrst, 'reload schema';
• Enable RLS in the same migration that creates a table in public.

Related

• Migrations workflow: local to SupaHost
• Partial migrations from transaction mistakes
• Freezing writes during a migration
• ALTER TABLE takes forever on a large table