Back to Blog

Database migration strategies for production SaaS applications

Antoine DepuydtAntoine Depuydt
•
Prisma
Database
Migrations
Production
Best Practices
DevOps

Database migrations are critical for maintaining and evolving your SaaS application's data structure. This guide covers Prisma migration strategies, zero-downtime deployment patterns, rollback procedures, and best practices for safely managing schema changes in production environments.

Understanding Prisma migration commands

Prisma provides different commands for managing database schema changes, each with specific use cases:

prisma db push (Development only)

Directly syncs your schema to the database without creating migration files:

  • Fast and convenient for development
  • Automatically creates/updates tables
  • No migration history - changes are not tracked
  • Not suitable for production - can lose data if not careful

Use db:push only for early development, prototyping, or testing schema changes locally. Fastack uses this approach during initial development but switches to migrations before production.

prisma migrate dev (Development with history)

Creates migration files and applies them to your database:

  • Creates migration files in prisma/migrations/
  • Tracks all schema changes
  • Can be reviewed before applying
  • Safe to use in production (via migrate deploy)

Use migrate dev when you want to track changes, before deploying to production, or for team collaboration. Fastack uses this command to create all production migrations.

prisma migrate deploy (Production)

Applies pending migrations to production database:

  • Only applies migrations that haven't been run
  • Safe for production environments
  • Idempotent - can be run multiple times safely
  • Requires migrations to exist first (created with migrate dev)

Fastack's deployment scripts use migrate deploy to apply migrations in production, ensuring only new migrations are applied and existing ones are skipped.

Migration workflow for production

Follow this workflow to safely migrate your production database:

Step 1: Create migration in development

Create and test the migration locally:

Creating a migration
# 1. Update your Prisma schema
# packages/database/prisma/schema.prisma
model Purchase {
  id        String   @id @default(cuid())
  userId    String?
  amount    Int
  createdAt DateTime @default(now())
  
  user User? @relation(fields: [userId], references: [id])
}

# 2. Create migration
cd packages/database
npm run db:migrate

# Prisma will:
# - Detect schema changes
# - Generate migration SQL
# - Ask for migration name: "add_purchase_model"
# - Create migration file in prisma/migrations/
# - Apply migration to local database

# 3. Review the generated SQL
cat prisma/migrations/*/migration.sql

# 4. Regenerate Prisma Client
npm run db:generate

Always review the generated SQL before committing the migration. The migration file will be created in prisma/migrations/ with a timestamp and your migration name.

Step 2: Test in staging

Test the migration on a staging database that mirrors production:

Testing migration in staging
# Apply migration to staging database
DATABASE_URL="postgresql://staging-db-url"

npx prisma migrate deploy   --schema=./packages/database/prisma/schema.prisma

# Verify migration
# - Check that tables/columns were created correctly
# - Test application functionality
# - Verify data integrity
# - Check performance impact

# Test rollback procedure (if needed)
# Restore from backup and verify recovery works

Staging testing helps identify potential issues before they reach production. Test both the migration forward and any rollback procedures.

Step 3: Backup production database

Always backup production before applying migrations:

Backing up production database
# Backup production database
pg_dump $DATABASE_URL >   production_backup_$(date +%Y%m%d_%H%M%S).sql

# Verify backup
ls -lh production_backup_*.sql

# Store backup securely (S3, backup service, etc.)
# Keep multiple backups for different time points

Fastack's migration scripts include backup functionality, but manual backups provide an additional safety layer. Store backups in a separate location from your production database.

Step 4: Deploy to production

Apply migrations to production using migrate deploy:

Deploying migrations in production
# Using Fastack's migration script
./deploy/scripts/migrate.sh --app fastack-boilerplate

# Or manually in Docker
docker exec fastack-boilerplate-app   prisma migrate deploy   --schema=./packages/database/prisma/schema.prisma

# The script will:
# 1. Backup the database (if enabled)
# 2. Check for pending migrations
# 3. Apply only new migrations
# 4. Handle baseline scenarios (if database exists without migration history)
# 5. Verify migration success

Fastack's migration script includes error handling for common scenarios, including baseline support for databases that exist without migration history.

Zero-downtime migration strategies

For production SaaS applications, zero-downtime migrations are essential. Use these patterns:

Adding new columns (backward compatible)

Add new columns as nullable or with default values:

Safe column addition
// Step 1: Add column as nullable
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  phone     String?  // New nullable column
  createdAt DateTime @default(now())
}

// Migration SQL (generated automatically)
ALTER TABLE "User" ADD COLUMN "phone" TEXT;

// Step 2: Deploy application code that handles both old and new data
// Step 3: Backfill data (if needed)
UPDATE "User" SET "phone" = 'default-value' WHERE "phone" IS NULL;

// Step 4: Make column required (optional, in separate migration)
ALTER TABLE "User" ALTER COLUMN "phone" SET NOT NULL;

This approach allows the application to work with both old and new database schemas during deployment, ensuring zero downtime.

Renaming columns (multi-step)

Rename columns safely using a multi-step process:

Safe column rename
// Step 1: Add new column alongside old one
ALTER TABLE "User" ADD COLUMN "fullName" TEXT;

// Step 2: Copy data from old column to new column
UPDATE "User" SET "fullName" = "name";

// Step 3: Deploy application code that uses new column
// (Application reads from "fullName", writes to both)

// Step 4: Stop writing to old column, verify all reads use new column

// Step 5: Remove old column (separate migration)
ALTER TABLE "User" DROP COLUMN "name";

This multi-step approach ensures the application continues working throughout the migration process.

Adding indexes

Add indexes using CONCURRENTLY in PostgreSQL:

Concurrent index creation
// For large tables, use CONCURRENTLY to avoid locking
CREATE INDEX CONCURRENTLY "User_email_idx" ON "User"("email");

// Note: Prisma doesn't generate CONCURRENTLY by default
// You may need to customize the migration SQL for large tables

// Or add index in Prisma schema and customize migration
model User {
  id    String @id @default(cuid())
  email String @unique
  
  @@index([email]) // Prisma will generate index
}

// Then edit migration.sql to add CONCURRENTLY:
// CREATE INDEX CONCURRENTLY "User_email_idx" ON "User"("email");

Concurrent index creation prevents table locking, allowing reads and writes to continue during index creation.

Rollback strategies

Plan for rollback scenarios before deploying migrations:

Restore from backup

The safest rollback method is restoring from a pre-migration backup:

Restoring from backup
# Restore production database from backup
psql $DATABASE_URL < production_backup_20251031_120000.sql

# Verify restoration
# - Check table structure
# - Verify data integrity
# - Test application functionality

# Note: This will lose any data created after the backup
# Consider this when planning migration timing

Backup restoration is the most reliable rollback method but may result in data loss if the backup is not recent. Always create backups immediately before migrations.

Reverse migration

Create a reverse migration to undo changes:

Creating reverse migrations
# For simple changes, create a reverse migration
# Example: Removing a column that was just added

# 1. Create new migration to reverse the change
npm run db:migrate
# Name: remove_purchase_table

# 2. Edit the migration SQL to drop the table
# packages/database/prisma/migrations/.../migration.sql
DROP TABLE IF EXISTS "Purchase" CASCADE;

# 3. Apply reverse migration
prisma migrate deploy

# Note: This only works for reversible changes
# Data loss may occur if the original migration deleted data

Reverse migrations work well for additive changes (adding tables, columns) but may not fully restore deleted data. Always test reverse migrations in staging first.

Best practices

Follow these practices for safe production migrations:

  • Always backup before migrations - Especially in production
  • Use migrations in production - Never use db:push in production
  • Review migration SQL - Check generated SQL before applying to production
  • Test in staging - Always test migrations in a staging environment first
  • Keep migration files in version control - Commit all migration files to Git
  • Never edit applied migrations - Create new migrations instead of modifying existing ones
  • Plan for rollback - Always have a rollback plan before deploying
  • Monitor migration performance - Track migration duration and database load

Migration patterns in Fastack

Fastack implements several migration best practices:

  • All migrations are tracked in packages/database/prisma/migrations/
  • Deployment scripts include automatic backup functionality
  • Baseline support for existing databases without migration history
  • Error handling for common migration scenarios
  • Docker integration for running migrations in containerized environments

Fastack's migration workflow ensures safe, repeatable database changes that can be version-controlled and applied consistently across development, staging, and production environments.

Conclusion

Database migrations are a critical part of maintaining production SaaS applications. By using Prisma migrations, following zero-downtime patterns, and implementing proper backup and rollback procedures, you can safely evolve your database schema without disrupting your users.

Fastack's migration strategy demonstrates how to structure migrations in a monorepo, integrate them into deployment pipelines, and handle edge cases like baseline scenarios. Always test migrations in staging, backup production before applying changes, and have a rollback plan ready.

Ready to build your SaaS faster?

Get our scalable, production-ready boilerplate to save endless hours of development and setup

Get Fastack
Database Migration Strategies for Production SaaS Applications - Fastack