Database Migrations

Manage your database schema with version-controlled migration files

Database migrations are SQL statements that create, update, or delete database schemas. They provide a structured way to track and version your database changes over time, making it easy to share schema updates across your team and deploy to production.

Why Use Migrations?

Migrations provide several key benefits:

• Version control: Track database schema in Git alongside your code
• Reproducibility: Recreate your database schema at any point in time
• Team collaboration: Share schema changes easily across developers
• Safe deployments: Apply tested changes to production systematically
• Rollback capability: Revert changes if issues arise
• Environment parity: Keep development, staging, and production in sync

Migration Workflow

Prerequisites

Ensure you have:

• Supabase CLI installed
• A project initialized with supabase init
• Local services running with supabase start

Creating Your First Migration

Let’s create a simple employees table to demonstrate the migration workflow.

1. Generate a migration file

   Create a new migration with a descriptive name:

   supabase migration new create_employees_table
   

   This creates a timestamped file in supabase/migrations/:

   supabase/migrations/20240304120000_create_employees_table.sql
   
2. Add SQL to your migration

   Open the migration file and add your SQL:

   supabase/migrations/20240304120000_create_employees_table.sql

   -- Create employees table
   CREATE TABLE IF NOT EXISTS public.employees (
     id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
     name TEXT NOT NULL,
     email TEXT,
     created_at TIMESTAMPTZ DEFAULT NOW()
   );
   
   -- Enable Row Level Security
   ALTER TABLE public.employees ENABLE ROW LEVEL SECURITY;
   
   -- Create policy for authenticated users
   CREATE POLICY "Employees are viewable by authenticated users"
     ON public.employees
     FOR SELECT
     TO authenticated
     USING (true);
   
3. Apply the migration

   Run the migration against your local database:

   supabase migration up
   

   You can now view the employees table in Studio at http://localhost:54323.

Modifying Tables

Now let’s add a department column to demonstrate schema evolution:

1. Create a new migration

   supabase migration new add_department_column
   
2. Add ALTER TABLE statement

   supabase/migrations/20240304130000_add_department_column.sql

   ALTER TABLE IF EXISTS public.employees
   ADD COLUMN department TEXT DEFAULT 'Engineering';
   
3. Apply the migration

   supabase migration up
   

Migration files are applied in chronological order based on their timestamp prefix.

Schema Diffing

If you prefer using the Dashboard or SQL editor to make changes, you can generate migrations from schema differences.

Create Changes in Dashboard

1. Open Studio at http://localhost:54323
2. Create a new table called cities with columns:
   • id (bigint, primary key, identity)
   • name (text)
   • population (bigint)

Generate Migration from Diff

Generate a migration file from your changes:

supabase db diff -f create_cities_table

This creates a new migration file with the SQL needed to recreate your changes:

supabase/migrations/20240304140000_create_cities_table.sql

CREATE TABLE "public"."cities" (
  "id" BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  "name" TEXT,
  "population" BIGINT
);

Test Your Migration

Reset your database to test the migration:

supabase db reset

This will:

1. Drop the database
2. Reapply all migrations in order
3. Run your seed file (if it exists)

supabase db reset destroys all local data. Always save important changes first:

supabase db dump --local --data-only > backup.sql

Migration Best Practices

1. Keep Migrations Atomic

Each migration should focus on a single logical change:

Good: Single purpose

-- Migration: add_user_roles.sql
CREATE TYPE user_role AS ENUM ('admin', 'member', 'guest');

ALTER TABLE users ADD COLUMN role user_role DEFAULT 'member';

2. Use Idempotent SQL

Make migrations safe to run multiple times:

-- Use IF NOT EXISTS
CREATE TABLE IF NOT EXISTS products (...);

-- Check before altering
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_name = 'products' AND column_name = 'price') THEN
    ALTER TABLE products ADD COLUMN price DECIMAL(10,2);
  END IF;
END $$;

3. Handle Lock Timeouts

For large tables, set lock timeout to prevent blocking:

-- At the top of your migration
SET lock_timeout = '2s';

ALTER TABLE large_table ADD COLUMN new_col TEXT;

4. Always Enable RLS

Enable Row Level Security on new tables:

CREATE TABLE sensitive_data (...);

-- Never forget this!
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

5. Use Descriptive Names

Name migrations clearly:

Good

supabase migration new add_email_verification_to_users
supabase migration new create_posts_table
supabase migration new add_index_on_posts_created_at

Common Migration Patterns

Adding Indexes

-- Add index for better query performance
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_posts_user_id 
  ON posts(user_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_posts_created_at 
  ON posts(created_at DESC);

Use CONCURRENTLY to avoid locking the table during index creation.

Creating Triggers

-- Auto-update timestamp trigger
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_posts_modtime
  BEFORE UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION update_modified_column();

Adding Foreign Keys

-- Add foreign key with proper naming
ALTER TABLE posts
  ADD CONSTRAINT fk_posts_user_id
  FOREIGN KEY (user_id)
  REFERENCES auth.users(id)
  ON DELETE CASCADE;

Creating Functions

-- Create a helper function
CREATE OR REPLACE FUNCTION get_user_posts(user_uuid UUID)
RETURNS SETOF posts
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT * FROM posts WHERE user_id = user_uuid;
$$;

Renaming Columns Safely

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name TEXT;

-- Step 2: Copy data
UPDATE users SET full_name = name;

-- Step 3: Drop old column (in a separate migration after deployment)
ALTER TABLE users DROP COLUMN name;

Seeding Data

Use supabase/seed.sql for development data:

supabase/seed.sql

-- Insert test users
INSERT INTO public.employees (name, email, department)
VALUES
  ('Alice Johnson', '[e-mail]', 'Engineering'),
  ('Bob Smith', '[e-mail]', 'Marketing'),
  ('Carol White', '[e-mail]', 'Sales');

-- Insert test cities
INSERT INTO public.cities (name, population)
VALUES
  ('New York', 8336817),
  ('Los Angeles', 3979576),
  ('Chicago', 2693976);

Apply seed data:

supabase db reset  # Reapplies migrations and seed data

Seed files are for development only. Don’t use them for production data.

Deploying Migrations

Link to Remote Project

First, authenticate and link to your Supabase project:

# Login to Supabase
supabase login

# Link to your remote project
supabase link

Select your project from the interactive prompt.

Push Migrations to Production

Deploy your migrations:

supabase db push

This applies all pending migrations to your remote database.

Push with Seed Data (Optional)

For staging environments, you can include seed data:

supabase db push --include-seed

Never use --include-seed in production as it may overwrite real data.

Migration Commands Reference

Command	Description
supabase migration new <name>	Create a new migration file
supabase migration up	Apply pending migrations
supabase migration list	List all migrations
supabase migration repair <version>	Mark a migration as applied
supabase db diff	Show schema differences
supabase db diff -f <name>	Create migration from diff
supabase db reset	Reset database and reapply migrations
supabase db push	Push migrations to remote database

Troubleshooting

Migration fails with syntax error

Error: syntax error at or near...

Solution:

• Validate SQL syntax in a SQL editor
• Check for missing semicolons
• Ensure proper quoting of identifiers
• Test migration locally first: supabase db reset

Table already exists

Error: relation "table_name" already exists

Solution:

• Use CREATE TABLE IF NOT EXISTS
• Check if migration was already applied
• Review migration history: supabase migration list

Lock timeout exceeded

Error: canceling statement due to lock timeout

Solution:

• Increase lock timeout in migration:
  SET lock_timeout = '5s';
  
• Run during low-traffic period
• Use CONCURRENTLY for index creation

Permission denied

Error: permission denied for schema public

Solution:

• Ensure you’re using the correct schema
• Check RLS policies aren’t blocking operations
• Verify migration uses proper privileges

Advanced Topics

Multi-Stage Deployments

For zero-downtime deployments of breaking changes:

1. Migration 1: Add new column

   ALTER TABLE users ADD COLUMN email_new TEXT;
   
2. Deploy application update

   Update app to write to both columns

3. Migration 2: Backfill data

   UPDATE users SET email_new = email WHERE email_new IS NULL;
   
4. Migration 3: Switch over

   ALTER TABLE users DROP COLUMN email;
   ALTER TABLE users RENAME COLUMN email_new TO email;
   

Managing Multiple Environments

Use different projects for each environment:

# Link to staging
supabase link --project-ref staging-ref
supabase db push

# Link to production
supabase link --project-ref production-ref
supabase db push

Next Steps

Deploy to Production

Learn how to deploy your project to Supabase Platform

Database Testing

Set up automated testing for your database

CI/CD Integration

Automate migrations with GitHub Actions

CLI Reference

Complete CLI command reference