# SQL migrations

> Change a SupaHost project's schema with versioned SQL migrations: how history is tracked, applying them over MCP, in Studio or with psql on Business, and testing on a branch first.

Source: https://supahost.app/docs/database/migrations · Updated 2026-11-11

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:

```sql
-- 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:

```sql
-- 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](/docs/ai-tools/mcp) 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](/docs/database/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](/docs/ai-tools/mcp-tools).

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

```sql
-- ...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:

```sql
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](/docs/platform/ssh) for setup.

```bash
# Through the tunnel (ssh -N -L 5432:127.0.0.1:5432 supahost@acme.supahost.space)
psql "$DATABASE_URL" --single-transaction -v ON_ERROR_STOP=1 \
  -f supabase/migrations/20261111093000_add_invoice_due_date.sql

# Or without a tunnel
ssh supahost@acme.supahost.space 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](/docs/local-development/migrations-workflow).

## 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](/docs/platform/branching).

## Undoing a migration

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

```sql
-- 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](/docs/platform/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](/docs/local-development/migrations-workflow)
- [Partial migrations from transaction mistakes](/kb/partial-migrations)
- [Freezing writes during a migration](/kb/read-only-mode)
- [ALTER TABLE takes forever on a large table](/kb/slow-alter-table)