Database Migration Strategies for Next.js and Supabase Production Apps
Developer Guide

Database Migration Strategies for Next.js and Supabase Production Apps

Complete guide to database migrations in Supabase. Learn zero-downtime deployments, schema versioning, rollback strategies, and production patterns.

2026-03-14
17 min read
Database Migration Strategies for Next.js and Supabase Production Apps

Database Migration Strategies for Next.js and Supabase Production Apps#

You've built your Next.js app with Supabase. It works perfectly in development. Now you need to deploy to production and realize: how do I safely change the database schema without breaking everything?

Database migrations are how you version control your schema and deploy changes safely. This guide covers everything from basic migrations to zero-downtime production deployments.

Prerequisites#

  • Supabase project (local and production)
  • Supabase CLI installed
  • Next.js application
  • Git for version control

Understanding Migrations#

A migration is a SQL file that changes your database schema:

sql
-- supabase/migrations/20260314120000_add_posts_table.sql
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  content TEXT,
  user_id UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

Migrations are:

  • Versioned: Timestamped filenames ensure order
  • Tracked: Supabase knows which migrations have run
  • Repeatable: Same migrations produce same result
  • Reversible: You can write rollback logic

Setting Up Migrations#

Initialize Supabase locally:

bash
npx supabase init

This creates:

supabase/
  config.toml
  seed.sql
  migrations/

Link to your remote project:

bash
npx supabase link --project-ref your-project-ref

Creating Your First Migration#

Create a new migration:

bash
npx supabase migration new create_posts_table

This creates:

supabase/migrations/20260314120000_create_posts_table.sql

Write your schema changes:

sql
-- Create posts table
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  published BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- RLS policies
CREATE POLICY "Anyone can view published posts"
  ON posts FOR SELECT
  USING (published = TRUE);

CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own posts"
  ON posts FOR UPDATE
  USING (auth.uid() = user_id);

-- Indexes
CREATE INDEX posts_user_id_idx ON posts(user_id);
CREATE INDEX posts_slug_idx ON posts(slug);
CREATE INDEX posts_published_created_at_idx ON posts(published, created_at DESC);

-- Updated at trigger
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

Apply locally:

bash
npx supabase db reset

This drops your local database and applies all migrations from scratch.

Migration Workflow#

Development Workflow#

  1. Make schema changes in Supabase Studio (local)
  2. Generate migration from changes:
bash
npx supabase db diff -f migration_name

This creates a migration file with your changes.

  1. Review the generated SQL
  2. Apply to local database:
bash
npx supabase db reset
  1. Test your application
  2. Commit migration to git

Production Deployment#

  1. Test migration in staging:
bash
npx supabase db push --db-url postgresql://staging-url
  1. Verify application works with new schema
  2. Deploy to production:
bash
npx supabase db push
  1. Deploy application code
  2. Monitor for errors

Common Migration Patterns#

Adding a Column#

sql
-- supabase/migrations/20260314130000_add_view_count.sql
ALTER TABLE posts
ADD COLUMN view_count INTEGER DEFAULT 0 NOT NULL;

-- Create index if needed
CREATE INDEX posts_view_count_idx ON posts(view_count DESC);

Modifying a Column#

sql
-- supabase/migrations/20260314140000_make_content_optional.sql
ALTER TABLE posts
ALTER COLUMN content DROP NOT NULL;

Renaming a Column#

sql
-- supabase/migrations/20260314150000_rename_content_to_body.sql
ALTER TABLE posts
RENAME COLUMN content TO body;

Adding a Foreign Key#

sql
-- supabase/migrations/20260314160000_add_category_fk.sql
ALTER TABLE posts
ADD COLUMN category_id UUID REFERENCES categories(id);

CREATE INDEX posts_category_id_idx ON posts(category_id);

Creating an Index#

sql
-- supabase/migrations/20260314170000_add_search_index.sql
CREATE INDEX posts_title_search_idx ON posts
USING GIN (to_tsvector('english', title));

Zero-Downtime Migrations#

Breaking changes cause downtime. Deploy schema changes without breaking your running application.

Pattern 1: Additive Changes#

Add new columns/tables without removing old ones:

Phase 1: Add new column

sql
-- Migration 1: Add new column
ALTER TABLE posts
ADD COLUMN published_at TIMESTAMPTZ;

Deploy application code that writes to both old and new columns:

typescript
await supabase.from('posts').insert({
  published: true,
  published_at: new Date().toISOString() // Write to new column
})

Phase 2: Backfill data

sql
-- Migration 2: Backfill existing data
UPDATE posts
SET published_at = created_at
WHERE published = TRUE AND published_at IS NULL;

Phase 3: Make new column required

sql
-- Migration 3: Add NOT NULL constraint
ALTER TABLE posts
ALTER COLUMN published_at SET NOT NULL;

Phase 4: Remove old column

sql
-- Migration 4: Drop old column
ALTER TABLE posts
DROP COLUMN published;

Pattern 2: Column Rename#

Don't rename directly. Add new column, migrate data, remove old:

Phase 1: Add new column

sql
ALTER TABLE posts
ADD COLUMN body TEXT;

Phase 2: Copy data

sql
UPDATE posts
SET body = content
WHERE body IS NULL;

Phase 3: Update application to use new column

Deploy code that reads/writes body instead of content.

Phase 4: Remove old column

sql
ALTER TABLE posts
DROP COLUMN content;

Pattern 3: Table Rename#

Use views for backward compatibility:

sql
-- Rename table
ALTER TABLE posts RENAME TO articles;

-- Create view with old name
CREATE VIEW posts AS SELECT * FROM articles;

Update application gradually, then drop view:

sql
DROP VIEW posts;

Handling Data Migrations#

Migrate data alongside schema changes:

sql
-- supabase/migrations/20260314180000_normalize_tags.sql

-- Create new tags table
CREATE TABLE tags (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT UNIQUE NOT NULL
);

-- Create junction table
CREATE TABLE post_tags (
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

-- Migrate data from old tags column (text array)
INSERT INTO tags (name)
SELECT DISTINCT unnest(tags) AS name
FROM posts
WHERE tags IS NOT NULL;

-- Populate junction table
INSERT INTO post_tags (post_id, tag_id)
SELECT p.id, t.id
FROM posts p
CROSS JOIN LATERAL unnest(p.tags) AS tag_name
JOIN tags t ON t.name = tag_name;

-- Drop old column
ALTER TABLE posts DROP COLUMN tags;

Rollback Strategies#

Supabase doesn't have automatic rollback. Plan ahead:

Strategy 1: Write Reverse Migrations#

For every migration, write the reverse:

sql
-- supabase/migrations/20260314190000_add_featured_flag.sql
ALTER TABLE posts
ADD COLUMN featured BOOLEAN DEFAULT FALSE;

-- supabase/migrations/20260314190001_rollback_featured_flag.sql
ALTER TABLE posts
DROP COLUMN featured;

Strategy 2: Use Transactions#

Wrap migrations in transactions:

sql
BEGIN;

-- Your changes
ALTER TABLE posts ADD COLUMN featured BOOLEAN;

-- Test the change
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'posts' AND column_name = 'featured'
  ) THEN
    RAISE EXCEPTION 'Migration failed';
  END IF;
END $$;

COMMIT;

Strategy 3: Feature Flags#

Decouple code and schema changes:

typescript
// Deploy schema change first
// Then enable feature with flag
const useNewSchema = await getFeatureFlag('use_new_posts_schema')

if (useNewSchema) {
  // Use new schema
} else {
  // Use old schema
}

Testing Migrations#

Test Locally#

bash
# Reset database and apply all migrations
npx supabase db reset

# Run your application tests
npm test

Test in Staging#

bash
# Push to staging
npx supabase db push --db-url postgresql://staging-url

# Run integration tests against staging
npm run test:integration

Test Rollback#

bash
# Apply migration
npx supabase db push

# Apply rollback migration
npx supabase db push

Common Pitfalls#

1. Not Testing Migrations#

bash
# ❌ Deploying untested migrations
npx supabase db push

# ✅ Test first
npx supabase db reset
npm test
npx supabase db push --db-url staging
npm run test:integration
npx supabase db push

2. Breaking Changes Without Coordination#

sql
-- ❌ Removing column while app still uses it
ALTER TABLE posts DROP COLUMN content;

Deploy in phases with backward compatibility.

3. Large Data Migrations in Single Transaction#

sql
-- ❌ Locks table for too long
UPDATE posts SET slug = generate_slug(title);

Batch large updates:

sql
-- ✅ Process in batches
DO $$
DECLARE
  batch_size INTEGER := 1000;
  processed INTEGER := 0;
BEGIN
  LOOP
    UPDATE posts
    SET slug = generate_slug(title)
    WHERE id IN (
      SELECT id FROM posts
      WHERE slug IS NULL
      LIMIT batch_size
    );
    
    GET DIAGNOSTICS processed = ROW_COUNT;
    EXIT WHEN processed = 0;
    
    COMMIT;
  END LOOP;
END $$;

4. Not Handling Migration Failures#

sql
-- ❌ No error handling
ALTER TABLE posts ADD COLUMN featured BOOLEAN;
UPDATE posts SET featured = TRUE WHERE view_count > 1000;

-- ✅ With error handling
DO $$
BEGIN
  ALTER TABLE posts ADD COLUMN featured BOOLEAN;
  UPDATE posts SET featured = TRUE WHERE view_count > 1000;
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Migration failed: %', SQLERRM;
    RAISE;
END $$;

Production Checklist#

Before deploying migrations to production:

  • [ ] Test migration in local environment
  • [ ] Test migration in staging environment
  • [ ] Verify application works with new schema
  • [ ] Write rollback migration
  • [ ] Plan deployment during low-traffic period
  • [ ] Backup database before migration
  • [ ] Monitor application after deployment
  • [ ] Have rollback plan ready
  • [ ] Document breaking changes
  • [ ] Notify team of deployment

Summary#

Database migrations in Supabase enable safe, version-controlled schema changes:

  • Use Supabase CLI for migration management
  • Test migrations thoroughly before production
  • Deploy additive changes for zero downtime
  • Write rollback migrations for critical changes
  • Coordinate schema and code deployments
  • Monitor applications after migration

Start with simple migrations and gradually adopt zero-downtime patterns as your application grows.

[INTERNAL LINK: deploying-nextjs-supabase-production] [INTERNAL LINK: nextjs-supabase-database-design-optimization] [INTERNAL LINK: nextjs-supabase-security-best-practices]

Frequently Asked Questions

|

Have more questions? Contact us

Back to All Guides

Related Guides

Deploying Next.js + Supabase to Production

Deploying Next.js + Supabase to Production

Complete guide to deploying Next.js and Supabase applications to production. Learn Vercel deployment, environment configuration, database migrations, CI/CD...

Developer Guide
Database Design and Optimization for Next.js and Supabase Applications

Database Design and Optimization for Next.js and Supabase Applications

Master PostgreSQL database design, indexing strategies, query optimization, and scaling patterns for high-performance Next.js and Supabase applications. Learn schema design, performance tuning, and production optimization.

Developer Guide
The Complete Next.js + Supabase Production Launch Checklist (47 Items)

The Complete Next.js + Supabase Production Launch Checklist (47 Items)

The definitive pre-launch audit for Next.js 15 + Supabase applications. 47 concrete checks across security, RLS, performance, observability, auth, and deployment — every item caused a production incident somewhere.

Developer Guide