Database Migration Strategies for Next.js and Supabase Pr...

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

Make schema changes in Supabase Studio (local)
Generate migration from changes:

npx supabase db diff -f migration_name

This creates a migration file with your changes.

Review the generated SQL
Apply to local database:

npx supabase db reset

Test your application
Commit migration to git

Production Deployment

Test migration in staging:

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

Verify application works with new schema
Deploy to production:

npx supabase db push

Deploy application code
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]

Originally published at https://www.iloveblogs.blog

推荐订阅源

DEV Community