Supabase Migrations Runbook

A step-by-step operational guide for adopting the Supabase CLI migration workflow on an existing remote project, with type-safe queries that provably match your remote schema and high confidence when running psql or supabase db push.

Verified against the current Supabase CLI and docs (June 2026).

0. The mental model (read this first)

Everything downstream depends on understanding two independent tracking systems:

1. Git tracks your migration files — the .sql files in supabase/migrations/.
2. Supabase tracks what has been applied to each database — a table called supabase_migrations.schema_migrations that exists separately in your local DB, each branch DB, and production.

supabase db push compares your local supabase/migrations/ folder against the target database’s history table and runs only the migrations not yet applied, in timestamp order. Drift happens when these two systems disagree (almost always because someone changed a database directly instead of through a migration file).

The shadow database is your safety net. When you run supabase db diff or supabase db push, the CLI spins up a throwaway “shadow” Postgres container, replays your migration files into it from scratch, and compares. This is how it validates that your migration set is internally consistent before touching anything real.

Why type safety becomes a guarantee, not a hope: your TypeScript types are generated from the same migration files that get pushed to production. If (a) local replays cleanly, (b) the committed types match what those migrations produce, and (c) a post-push diff against remote comes back empty — then your client types match your remote schema by construction. Sections 3, 4, 7, and 9 enforce each link in that chain.

The golden rule: once you are on migrations, never change a remote database directly (no DDL in the Dashboard SQL Editor or Table Editor against production). Every schema change — even a one-line column add — goes through a migration file. Direct changes bypass the history table and cause db push to fail with sync errors.

1. Phase 0 — Prerequisites & dependencies

1.1 Docker (required for the local stack)

The CLI runs the entire Supabase stack (Postgres, Auth, Storage, Realtime, Studio, a local SMTP server, and the diff tool) in Docker containers. You need Docker Desktop or any Docker-compatible runtime — OrbStack (lightest on macOS), Podman, Colima, or Rancher Desktop all work.

• Install Docker Desktop: https://www.docker.com/products/docker-desktop/
• Allocate at least 7 GB RAM to Docker (Docker Desktop → Settings → Resources). The stack will refuse health checks below this.
• Verify it’s running:

docker ps   # should return without error (an empty table is fine)

1.2 Supabase CLI

Do not npm install -g supabase — global install via npm is unsupported and will warn/fail. Use a package manager or npx.

macOS (recommended):

brew install supabase/tap/supabase

Windows:

scoop bucket add supabase https://github.com/supabase/scoop-bucket.git
scoop install supabase

Cross-platform / pin per project (good for teams + CI): add it as a dev dependency and call it through npx:

npm install --save-dev supabase
npx supabase --version

If you use the dev-dependency approach, prefix every command in this runbook with npx (e.g. npx supabase start). Pinning the version in package.json keeps everyone — and CI — on the same Studio/stack version.

Verify and keep current (the CLI version determines your local Studio version, so stay updated):

supabase --version
brew upgrade supabase   # or: scoop update supabase  /  npm i -D supabase@latest

1.3 Everything else

• Git — your migration files live in version control.
• Node.js 18+ — only needed for your app + supabase-js, not for the CLI itself if installed via brew/scoop.
• psql (optional, for Section 9) — installed with the libpq / postgresql-client package.
• A Supabase account plus your existing remote project, and your database password (Dashboard → Project Settings → Database → Reset password if you don’t have it).

2. Phase 1 — One-time project setup

Run these from the root of your application repo (e.g. your Next.js project).

2.1 Initialize

supabase init

This creates a supabase/ directory containing config.toml. It is safe — and expected — to commit this folder to git. It also offers to scaffold editor settings; accept if you want VS Code / Deno hints for Edge Functions.

2.2 Log in

supabase login

Opens a browser to generate a Personal Access Token, stored in your OS credential store. (In CI you skip this by setting the SUPABASE_ACCESS_TOKEN environment variable instead.)

2.3 Link to your remote project

supabase link --project-ref <your-project-ref>

• Find <your-project-ref> in your project URL: https://supabase.com/dashboard/project/<project-ref>, or just run supabase link with no flag and pick from the interactive list.
• It will prompt for your database password. Linking is what lets db push, db pull, and db dump know which remote to talk to.

2.4 Confirm .gitignore protects secrets

supabase init adds sensible ignores, but confirm your repo ignores environment files and CLI temp state:

# Supabase
supabase/.temp/
supabase/.branches/

# Local env — never commit
.env
.env.local
.env*.local

Commit: git add supabase/ .gitignore && git commit -m "chore: initialize Supabase CLI"

3. Phase 2 — Baseline your existing remote schema (your real “first migration”)

This is the single most important step for an existing project, and the linchpin of “local matches remote.” Because production already has tables, your first migration is not a new table — it’s a snapshot of what’s already live.

3.1 Pull the public schema

supabase db pull

This connects to your linked remote, captures the current schema, and writes supabase/migrations/<timestamp>_remote_schema.sql. It also records the baseline in the remote history table so future pushes know where they’re starting from.

3.2 Pull auth & storage (only if you have custom objects there)

db pull excludes the auth and storage schemas by default, and — importantly — when the migrations/ folder is still empty, the first pull ignores the --schema flag entirely. So pull those after 3.1, as a second step, if you have custom triggers, RLS policies, or buckets defined there:

supabase db pull --schema auth,storage

3.3 Review and commit

Open the generated file(s) and skim them. The auto-generated DDL is more verbose than hand-written SQL (it spells out identity sequences, default privileges, etc.) — that’s expected. The diff tool can occasionally miss exotic objects, so a quick read pays off.

git add supabase/migrations
git commit -m "migration: baseline existing remote schema"

After this, your local migration history and your remote are in sync, both anchored to the same baseline, and both tracked in git.

4. Phase 3 — Start the local stack & verify parity

4.1 Start

supabase start

First run downloads the Docker images (a few minutes). When it finishes it prints your local endpoints and keys — keep this handy:

It also prints local anon / service_role (and publishable/secret) keys. These are well-known, non-secret demo values used only by the local stack — they are not your production keys.

You can re-print these any time (handy for scripting .env.local):

supabase status            # human-readable
supabase status -o env     # as KEY=VALUE pairs (ANON_KEY, SERVICE_ROLE_KEY, etc.)

4.2 Prove the migration set replays cleanly

supabase db reset

This destroys and rebuilds your local database, replays every migration in supabase/migrations/ from scratch in order, then runs supabase/seed.sql if present. If this succeeds, your migration set is internally consistent — which is exactly what will happen on a fresh branch and what db push validates against remote. Run db reset liberally; it’s free confidence and only touches local.

Open Studio at http://127.0.0.1:54323 and confirm your tables look like production (minus data).

4.3 Two .env files — don’t confuse them

There are two distinct environment files in play:

1. Your app’s .env.local (e.g. Next.js) — the keys your application uses to reach Supabase. Point it at local while developing:

# .env.local  — LOCAL development
NEXT_PUBLIC_SUPABASE_URL=http://127.0.0.1:54321
NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY=<local publishable/anon key from `supabase status`>
SUPABASE_SECRET_KEY=<local service_role/secret key — server-side only>

For production/preview builds you swap these for the remote project’s URL and its publishable key (client) and secret key (server) from Dashboard → Project Settings → API Keys. (More on the new key model in Section 11.)

2. The CLI’s .env — used by supabase itself to substitute env(...) references inside config.toml (e.g. OAuth provider secrets for local Auth). The CLI auto-reads a .env in your project root. Changes to config.toml require supabase stop && supabase start to take effect.

Rule of thumb: .env.local configures your app; .env configures the CLI. Neither is ever committed.

5. Phase 4 — End-to-end type safety

5.1 Generate types from your local schema

mkdir -p src/types
supabase gen types typescript --local > src/types/database.types.ts

--local reads the running local stack — which is your migration files made real, so the generated types reflect exactly what will ship. (You can also generate from the linked project with --linked, or any project by ref with --project-id <ref>; use those for verification, but treat --local as your authoring source of truth.)

5.2 Wire the types into supabase-js

import { createClient } from '@supabase/supabase-js'
import type { Database } from '@/types/database.types'

export const supabase = createClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY!,
)

Passing <Database> gives you autocomplete and compile-time errors on table names, column names, return shapes, inserts, and updates — across client, server actions, and Edge Functions.

5.3 Add npm scripts so this is one command

{
  "scripts": {
    "db:start": "supabase start",
    "db:stop": "supabase stop",
    "db:reset": "supabase db reset",
    "db:new": "supabase migration new",
    "db:diff": "supabase db diff -f",
    "db:types": "supabase gen types typescript --local > src/types/database.types.ts",
    "db:lint": "supabase db lint",
    "db:push": "supabase db push"
  }
}

Regenerate types every time you change the schema: migrate → npm run db:reset → npm run db:types → commit both.

5.4 Make drift a build failure (CI guard)

This is what guarantees committed types never silently fall out of step with the schema your migrations produce. Add .github/workflows/generate-types.yml:

name: generate-types
on:
  pull_request:
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: supabase/setup-cli@v1
        with:
          version: latest
      - run: supabase init
      - run: supabase db start
      - name: Verify generated types match the schema
        run: |
          supabase gen types typescript --local > src/types/database.types.ts
          if ! git diff --ignore-space-at-eol --exit-code --quiet src/types/database.types.ts; then
            echo "::error::Committed types are out of date. Run 'npm run db:types' and commit."
            git diff
            exit 1
          fi

If a PR changes the schema but forgets to regenerate types, CI fails. That closes the loop.

6. Phase 5 — The everyday local migration loop

Two equally valid styles. Pick per change.

Style A — SQL-first (recommended once comfortable)

# 1. Create an empty, timestamped migration
supabase migration new add_invoices_table

# 2. Write the DDL in the new file:
#    supabase/migrations/<timestamp>_add_invoices_table.sql

create table if not exists public.invoices (
  id          bigint primary key generated always as identity,
  org_id      uuid not null references public.organizations (id),
  amount      numeric(12,2) not null,
  status      text not null default 'draft',
  created_at  timestamptz not null default now()
);

-- RLS-first: enable and write policies in the same migration
alter table public.invoices enable row level security;

create policy "members read their org invoices"
  on public.invoices for select
  to authenticated
  using ( org_id = (select auth.jwt() ->> 'org_id')::uuid );

# 3. Apply just the new pending migration to local
supabase migration up
#    (or `supabase db reset` to replay the entire set from scratch — best before pushing)

# 4. Regenerate types + sanity-check
npm run db:types
supabase db lint          # plpgsql_check for schema errors

# 5. Commit migration + regenerated types together
git add supabase/migrations src/types/database.types.ts
git commit -m "feat(db): add invoices table with org-scoped RLS"

Style B — Studio-first, then capture the diff (LOCAL only)

Use this when you’d rather click through the local Studio UI. Only ever do this against the local database, then capture it:

# 1. Make your changes in local Studio (http://127.0.0.1:54323)

# 2. Diff local against the shadow DB and write a migration file
supabase db diff -f add_invoices_table

# 3. Review the generated SQL (migra can miss some object types — always read it)

# 4. Verify it replays cleanly, then regenerate types
supabase db reset
npm run db:types

# 5. Commit
git add supabase/migrations src/types/database.types.ts
git commit -m "feat(db): add invoices table"

Either way: never make these changes in the remote Studio. That’s the golden-rule violation that breaks db push.

Optional: database tests (pgTAP)

supabase test new invoices_rls   # stubs a pgTAP test
supabase test db                 # runs all tests via pg_prove

7. Phase 6 — The branch (staging / preview) tier

This is the middle environment that catches problems before production. You have two paths depending on whether you want managed Supabase Branching.

Path A — Supabase Branching + GitHub (recommended)

Branching gives every pull request its own isolated Supabase instance with a full copy of your schema (deliberately no production data), so you can validate migrations and test the app end-to-end before merging.

• Preview branches are ephemeral — created per PR, paused after inactivity, deleted when the PR merges/closes.
• Persistent branches are long-lived — use one for staging / QA.
• Branching (preview environments) requires the Pro plan. GitHub-based deploys and CLI deploys work on all plans; only the per-PR preview environments need Pro.

Set up once:

1. Dashboard → your project → Branches → enable Branching, connect your GitHub repo, set the production branch (usually main) and the directory (supabase/).
2. (Optional) Add a seed file so branches start with reference data — see Configuration → seeding.

Then your loop becomes:

1. git checkout -b feat/invoices → commit your migration + types → push → open a PR.
2. Supabase spins up a preview branch, runs the deployment workflow (clone → pull migrations from main → health → configure → migrate → seed → deploy functions). If your migration has a bug, the Migrate step fails on the branch — never on production.
3. The generate-types CI guard (Section 5.4) runs in the same PR.
4. Review the branch’s own Studio/API, test the app against it.
5. Merge to main → Supabase automatically runs the same deployment workflow against production. No manual db push needed.

Path B — A second project as staging (no Pro / no GitHub integration)

If you’re not using Branching, mirror production with a separate Supabase project for staging and deploy via CLI/CI:

• Create a brand-new staging project (don’t reuse one whose schema was hand-edited — the CLI would try to reapply changes).
• In CI, set SUPABASE_ACCESS_TOKEN, SUPABASE_DB_PASSWORD, and the staging SUPABASE_PROJECT_ID as encrypted secrets.
• Wire two GitHub Actions: on merge to a develop branch → supabase db push to staging; on merge to main → supabase db push to production. Switch the target via the PROJECT_ID secret per workflow.

8. Phase 7 — Push to production with confidence

Whether you push manually or your CI does it, this is the safe sequence.

8.1 See exactly what will change

supabase migration list

Shows, side by side, which migrations are applied locally vs on remote and where they diverge. The rows that are local-only are precisely what a push will apply.

8.2 Dry-run the push

supabase db push --dry-run

Prints the migrations that would be applied without applying them. (Note: dry-run lists the pending files; it does not fully execute the SQL — your real correctness checks are the clean local db reset in 4.2 and the branch Migrate step in Phase 6. Treat dry-run as “confirm the set,” not “validate the SQL.”)

8.3 Take a backup before anything destructive

For schema changes that drop or rewrite columns, snapshot first:

supabase db dump --linked -f backups/pre-deploy-$(date +%Y%m%d).sql            # schema
supabase db dump --linked --data-only -f backups/pre-deploy-data-$(date +%Y%m%d).sql  # data

(Production projects also have Point-in-Time Recovery on paid plans — know your recovery window before a big migration.)

8.4 Push

supabase db push

On its first run against a database, this creates the supabase_migrations.schema_migrations history table; thereafter it applies only the not-yet-applied migrations, in order, and records each. Seed data is not pushed unless you explicitly pass --include-seed (you usually don’t want to seed production).

Coordinate so only one person/pipeline pushes at a time. Concurrent pushes from different machines can collide because migrations apply in timestamp order.

8.5 The final confidence check — prove zero drift

supabase db diff --linked --schema public

After a clean deploy this should print nothing. An empty diff means your local migration state and the live remote schema are identical — and therefore your generated types match production. Then re-verify types against the live schema:

supabase gen types typescript --linked > /tmp/remote.types.ts
diff src/types/database.types.ts /tmp/remote.types.ts   # expect no differences

8.6 Re-run the security advisor

Since you run RLS-first, confirm the deploy didn’t expose anything: Dashboard → Advisors → Security Advisor, and review every finding before dismissing. (The Supabase MCP get_advisors tool can pull these programmatically.)

9. Phase 8 — Using psql against remote, safely

9.1 Get the right connection string

In the Dashboard, click Connect (top bar). You’ll see three options:

• Direct connection (db.<ref>.supabase.co:5432) — a real Postgres connection, IPv6-only unless you have the IPv4 add-on. Best for long-lived sessions from an IPv6-capable network.
• Session pooler (aws-0-<region>.pooler.supabase.com:5432) — IPv4-friendly, persistent sessions. Use this for interactive psql if you’re not sure about IPv6.
• Transaction pooler (port 6543) — for serverless/edge; not ideal for an interactive shell.

# Interactive psql via the session pooler (IPv4-safe)
psql "postgresql://postgres.<project-ref>:<db-password>@aws-0-<region>.pooler.supabase.com:5432/postgres"

Percent-encode special characters in the password if you inline it. Better: leave the password out and let it prompt, or use a ~/.pgpass file.

9.2 Rules for psql on production

• ✅ Reads, inspection, data fixes are fine. For ad-hoc reads, wrap them so you can’t accidentally write:

  begin; set transaction read only;
  select count(*) from public.invoices where status = 'overdue';
  rollback;

• 🚫 Never run schema DDL (create table, alter table, drop ..., create policy, etc.) directly on production via psql or the SQL Editor. That bypasses the migration history and re-breaks the local↔remote sync you worked to establish. All DDL goes through a migration file → branch → push.

10. Drift detection & recovery cheat-sheet

migration repair only edits the history table — it never applies or reverts SQL. Use it to make the record match a database state you’ve already verified.

11. Reference — the new API key model (2025+)

Supabase replaced the legacy JWT-based anon / service_role keys with a new format. Get both from Dashboard → Project Settings → API Keys.

Notes that matter for you:

• The secret key must never reach a browser — the gateway actively rejects it based on the User-Agent. Keep it in server-side env only.
• Legacy anon/service_role keys still work during the migration window but are slated for removal in late 2026; new projects created after Nov 2025 don’t get them at all.
• The publishable key carries the same low privileges as the old anon key, so your RLS policies behave identically — no policy changes needed when you switch.
• Send these keys on the apikey header (not as a bearer token); user-session JWTs from Supabase Auth still go in Authorization: Bearer ….

12. Your “full confidence” checklist

Before you trust a deploy, all of these should be true:

• supabase db reset replays the entire migration set locally with no errors (Section 4.2)
• Every schema change since the last deploy exists as a committed migration file — nothing was hand-edited on remote (golden rule)
• src/types/database.types.ts was regenerated and committed alongside the migration (Section 5)
• The PR’s type-drift CI guard passed, and the branch Migrate step succeeded on a fresh DB (Sections 5.4, 7)
• supabase migration list shows the expected pending migrations and nothing surprising (Section 8.1)
• A backup was taken before any destructive change (Section 8.3)
• Post-push, supabase db diff --linked --schema public is empty and remote types match committed types (Section 8.5)
• Security Advisor reviewed after deploy (Section 8.6)

When every box is checked, “my type-safe queries match the actual remote schema” is a fact you’ve verified end to end — not an assumption.

Command quick reference

# Setup (once)
supabase init
supabase login
supabase link --project-ref <ref>

# Baseline an existing project (once)
supabase db pull
supabase db pull --schema auth,storage     # only if you have custom objects there

# Local stack
supabase start
supabase status -o env
supabase stop                               # add --no-backup to wipe local data

# Daily loop
supabase migration new <name>
supabase migration up                       # apply pending to local
supabase db reset                           # replay ALL migrations + seed (local)
supabase db diff -f <name>                  # capture local Studio changes as a migration
supabase gen types typescript --local > src/types/database.types.ts
supabase db lint
supabase test db

# Inspect & deploy
supabase migration list
supabase db push --dry-run
supabase db dump --linked -f backup.sql
supabase db push
supabase db diff --linked --schema public   # expect empty after deploy

# Recovery
supabase db pull                            # recapture drifted remote
supabase migration repair --status applied|reverted <timestamp>

Sources (official docs)

• Local development overview — https://supabase.com/docs/guides/local-development/overview
• CLI getting started (Docker) — https://supabase.com/docs/guides/local-development/cli/getting-started
• Database migrations (workflow, golden rule, repair) — https://supabase.com/docs/guides/deployment/database-migrations
• Managing environments (CI, staging) — https://supabase.com/docs/guides/deployment/managing-environments
• Branching — https://supabase.com/docs/guides/deployment/branching
• Generate types — https://supabase.com/docs/guides/deployment/ci/generating-types
• CLI reference — https://supabase.com/docs/reference/cli/introduction
• New API keys — https://supabase.com/docs/guides/api/api-keys