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:

-- 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:

npx supabase init

This creates:

supabase/
  config.toml
  seed.sql
  migrations/

Link to your remote project:

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

Creating Your First Migration#

Create a new migration:

npx supabase migration new create_posts_table

This creates:

supabase/migrations/20260314120000_create_posts_table.sql

Write your schema changes:

-- 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:

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:

npx supabase db diff -f migration_name

This creates a migration file with your changes.

3. Review the generated SQL
4. Apply to local database:

npx supabase db reset

5. Test your application
6. Commit migration to git

Production Deployment#

1. Test migration in staging:

npx supabase db push --db-url postgresql://staging-url

2. Verify application works with new schema
3. Deploy to production:

npx supabase db push

4. Deploy application code
5. Monitor for errors

Common Migration Patterns#

Adding a Column#

-- 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#

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

Renaming a Column#

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

Adding a Foreign Key#

-- 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#

-- 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

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

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

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

Phase 2: Backfill data

-- 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

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

Phase 4: Remove old column

-- 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

ALTER TABLE posts
ADD COLUMN body TEXT;

Phase 2: Copy data

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

ALTER TABLE posts
DROP COLUMN content;

Pattern 3: Table Rename#

Use views for backward compatibility:

-- 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:

DROP VIEW posts;

Handling Data Migrations#

Migrate data alongside schema changes:

-- 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:

-- 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:

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:

// 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#

# Reset database and apply all migrations
npx supabase db reset

# Run your application tests
npm test

Test in Staging#

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

# Run integration tests against staging
npm run test:integration

Test Rollback#

# Apply migration
npx supabase db push

# Apply rollback migration
npx supabase db push

Common Pitfalls#

1. Not Testing Migrations#

# ❌ 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#

-- ❌ 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#

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

Batch large updates:

-- ✅ 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#

-- ❌ 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]