Database Migrations in the Next.js Supabase Starter Kit

Create and manage database migrations using Supabase's declarative schema and diffing tools to evolve your PostgreSQL schema safely.

Database migrations in Makerkit use Supabase's declarative schema approach. Define your schema in SQL files at apps/web/supabase/schemas/, then generate migration files that track changes over time. This keeps your schema version-controlled and deployable across environments.

Database Migration Workflow

Create and apply schema changes safely

Edit the declarative schema

Generate a migration file

Test locally

Push to production

Why Declarative Schema?

Makerkit uses declarative schema files instead of incremental migrations for several reasons:

Readable: See your entire schema in one place
Mergeable: Schema changes are easier to review in PRs
Recoverable: Always know the intended state of your database
Automated: Supabase generates migration diffs for you

Avoid Supabase Studio for Schema Changes

Don't use the hosted Supabase Studio to modify your schema. Changes made there won't be tracked in your codebase. Use your local Supabase instance and generate migrations from schema files.

Schema File Organization

Schema files live in apps/web/supabase/schemas/:

apps/web/supabase/
├── config.toml           # Supabase configuration
├── seed.sql              # Seed data for development
├── schemas/              # Declarative schema files
│   ├── 00-extensions.sql
│   ├── 01-enums.sql
│   ├── 02-accounts.sql
│   ├── 03-roles.sql
│   ├── 04-memberships.sql
│   ├── 05-subscriptions.sql
│   └── your-feature.sql  # Your custom schema
└── migrations/           # Generated migration files
    ├── 20240101000000_initial.sql
    └── 20240115000000_add_projects.sql

Files are loaded alphabetically, so prefix with numbers to control order.

Editing the Declarative Schema

Adding a New Table

Create a schema file for your feature:

apps/web/supabase/schemas/20-projects.sql

-- Projects table for team workspaces
create table if not exists public.projects (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references public.accounts(id) on delete cascade,
  name text not null,
  description text,
  status text not null default 'active' check (status in ('active', 'archived')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
-- Enable RLS
alter table public.projects enable row level security;
-- RLS policies
create policy "Users can view their account's projects"
  on public.projects
  for select
  using (
    account_id in (
      select account_id from public.accounts_memberships
      where user_id = auth.uid()
    )
  );
create policy "Users with write permission can insert projects"
  on public.projects
  for insert
  with check (
    public.has_permission(auth.uid(), account_id, 'projects.write'::app_permissions)
  );
-- Updated at trigger
create trigger set_projects_updated_at
  before update on public.projects
  for each row execute function public.set_updated_at();

Modifying an Existing Table

Edit the schema file directly. For example, to add a column:

apps/web/supabase/schemas/20-projects.sql

create table if not exists public.projects (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references public.accounts(id) on delete cascade,
  name text not null,
  description text,
  status text not null default 'active' check (status in ('active', 'archived')),
  priority integer not null default 0,  -- New column
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Adding Indexes

Add indexes for frequently queried columns:

-- Add to your schema file
create index if not exists projects_account_id_idx
  on public.projects(account_id);
create index if not exists projects_status_idx
  on public.projects(status)
  where status = 'active';

Generating a Migration File

After editing schema files, generate a migration that captures the diff:

# Generate migration from schema changes
pnpm --filter web supabase:db:diff -f add_projects

This creates a timestamped migration file in apps/web/supabase/migrations/:

apps/web/supabase/migrations/20260119000000_add_projects.sql

-- Generated by Supabase CLI
create table public.projects (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references public.accounts(id) on delete cascade,
  name text not null,
  description text,
  status text not null default 'active' check (status in ('active', 'archived')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
alter table public.projects enable row level security;
-- ... policies and triggers

Always Review Generated Migrations

The diffing tool has known caveats. Always review generated migrations before applying them. Check for:

Destructive operations (DROP statements)
Missing or incorrect constraints
Order of operations issues

Testing Locally

Apply and test your migration locally before pushing to production:

# Stop Supabase if running
pnpm run supabase:web:stop
# Start with fresh database
pnpm run supabase:web:start
# Or reset to apply all migrations
pnpm run supabase:web:reset

Verify the Schema

Check that your changes applied correctly:

# Open local Supabase Studio
open http://localhost:54323

Navigate to Table Editor and verify your table exists with the correct columns.

Run Database Tests

If you have pgTAP tests, run them to verify RLS policies:

pnpm --filter web supabase:test

See Database Tests for writing tests.

Pushing to Production

After testing locally, push migrations to your remote Supabase instance:

# Link to your Supabase project (first time only)
pnpm --filter web supabase link --project-ref your-project-ref
# Push migrations
pnpm --filter web supabase db push

Migration Commands Reference

Command	Description
`pnpm run supabase:web:start`	Start local Supabase
`pnpm run supabase:web:stop`	Stop local Supabase
`pnpm run supabase:web:reset`	Reset and apply all migrations
`pnpm --filter web supabase:db:diff -f <name>`	Generate migration from schema diff
`pnpm --filter web supabase db push`	Push migrations to remote
`pnpm --filter web supabase:typegen`	Regenerate TypeScript types

Regenerating TypeScript Types

After schema changes, regenerate the TypeScript types:

pnpm --filter web supabase:typegen

This updates packages/supabase/src/database.types.ts with your new tables and columns. Import types in your code:

import type { Database } from '@kit/supabase/database';
type Project = Database['public']['Tables']['projects']['Row'];
type NewProject = Database['public']['Tables']['projects']['Insert'];

Common Patterns

Adding a Lookup Table

-- Status enum as lookup table
create table if not exists public.project_statuses (
  id text primary key,
  label text not null,
  sort_order integer not null default 0
);
insert into public.project_statuses (id, label, sort_order) values
  ('active', 'Active', 1),
  ('archived', 'Archived', 2),
  ('deleted', 'Deleted', 3)
on conflict (id) do nothing;

Adding a Junction Table

-- Many-to-many relationship
create table if not exists public.project_members (
  project_id uuid not null references public.projects(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  role text not null default 'member',
  created_at timestamptz not null default now(),
  primary key (project_id, user_id)
);

Data Migration

For data transformations, use a separate migration:

apps/web/supabase/migrations/20260120000000_backfill_priority.sql

-- Backfill priority based on status
update public.projects
set priority = case
  when status = 'active' then 1
  when status = 'archived' then 0
  else 0
end
where priority is null;

Troubleshooting

Diff shows no changes: Ensure your schema file is being loaded. Check file naming (alphabetical order matters).

Migration fails on production: The diff tool may generate invalid SQL. Review and manually fix the migration file.

Type mismatch after migration: Regenerate types with pnpm --filter web supabase:typegen.

RLS policy errors: Check that your policies reference valid columns and functions. Test with database tests.

Database Schema for detailed schema patterns
Database Architecture for understanding the data model
Database Functions for built-in SQL functions
Database Tests for testing migrations