Source: Dev.to

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. Supabase project (local and production) Supabase CLI installed Next.js application Git for version control 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 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

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. 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 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 — 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);

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

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

— 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);

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

Breaking changes cause downtime. Deploy schema changes without breaking your running application. 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;

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;

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;

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;

Supabase doesn’t have automatic rollback. Plan ahead: 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;

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;

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 }

Reset database and apply all migrations

npx supabase db reset

Run your application tests

npm test

Push to staging

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

Run integration tests against staging

npm run test:integration

Apply migration

npx supabase db push

Apply rollback migration

npx supabase db push

❌ 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

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

Deploy in phases with backward compatibility. — ❌ 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 $$;

— ❌ 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 $$;

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 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] Originally published at https://www.iloveblogs.blog