Migrations: write and apply

Introduction

Schema in Supabase (and in any serious Postgres setup) should be managed through migrations: versioned SQL files applied in order. This post covers how to write a migration, the folder and naming conventions, how to apply changes via the CLI or Studio, and basic rollback.

Goal: Learn how to create a new migration, run migrations against local/cloud safely, and understand the principle of "schema versioned, reproducible".

Features

What is a migration

• A migration is a one-off SQL file that typically creates/alters/drops tables, columns, indexes, functions, RLS. Each file has a unique name (often with a timestamp) so application order is defined.
• Supabase CLI manages migrations in supabase/migrations/; when you run supabase db push (or supabase migration up), the CLI compares with the supabase_migrations.schema_migrations table and only runs migrations that have not been applied.
• Reproducible: From a repo + migrations you can recreate the DB from scratch; dev and CI share the same schema.

Studio vs CLI

• Studio: Supabase Dashboard has a SQL Editor; you can run SQL directly. Good for quick experiments; do not use it as the source of truth for production schema (hard to version and reproduce).
• CLI: Migrations in the repo, applied by command; you get history and PR review. Use this for all formal schema changes.

Workflow / Process

1. Create the migration file: supabase migration new <descriptive_name> → creates the file supabase/migrations/<timestamp>_<name>.sql.
2. Write the SQL: Add only DDL (CREATE/ALTER/DROP) and, if needed, conditional data seeds (INSERT); avoid arbitrary DML (UPDATE/DELETE) unless the impact is well understood.
3. Run locally: supabase start (if not already running), then supabase db reset, or let migrations apply automatically on start; or supabase db push after linking the project.
4. Run against cloud: supabase link pointing at the right project (staging/prod) → supabase db push. Run against staging first, verify, then prod.
5. Rollback: Supabase does not roll back automatically; to "undo" a change you must write a new migration (ALTER/DROP) and push it. Keep migrations additive/modifying only, and avoid deleting data in a migration unless you have a backup.

Convention: one purpose per migration (e.g. add table X, add column Y); use a short descriptive file name (e.g. add_messages_content_index).

Sample code

Create a new migration

supabase migration new add_conversations_archived
# Creates file: supabase/migrations/20260317120000_add_conversations_archived.sql

Migration file contents

-- supabase/migrations/20260317120000_add_conversations_archived.sql
 
ALTER TABLE public.conversations
  ADD COLUMN IF NOT EXISTS archived BOOLEAN NOT NULL DEFAULT false;
 
CREATE INDEX IF NOT EXISTS idx_conversations_archived
  ON public.conversations(archived) WHERE archived = false;

Check status (after linking)

supabase migration list
# Lists applied / not-yet-applied migrations

Reset the local DB (drops data, re-runs all migrations)

supabase db reset

Applying it in your architecture

• Schema versioned: Migrations are the single source of truth for the DB structure; every schema change goes through a migration file and is reviewed in a PR. Avoid editing production by hand.
• Reproducible: Any environment (dev, CI, staging) can build the DB from empty by running all migrations; this reduces the "works on my machine but not on yours" problem.
• Environment separation: The same set of migrations, different projects (dev/staging/prod); deploy migrations to each environment following the process (staging first, then prod).

Conclusion

Migrations let you manage your Postgres schema as versioned files, applied consistently through the CLI. The next post covers Auth: JWT, sessions, and how to verify a JWT on the backend.

Next: 05 — Auth: JWT and backend integration

Further reading: Supabase — Database Migrations