What Are Database Migrations? Evolving Your Schema Safely

Why Migrations Exist: The Problem They Solve

In development, it's easy to change your schema: drop the table, recreate it with the new structure, re-seed your test data. No real data exists, so nothing is lost.

In production with real users:

• You can't drop and recreate tables — real user data would be gone
• You need to alter existing tables while preserving existing data
• Multiple developers working on the same project need to know what schema changes have been applied
• You need to reproduce the exact same schema on new environments
• If a bad migration breaks things, you need to roll it back

Migrations solve all of this by treating schema changes as code: versioned, committed to git, applied in order, and tracked in the database itself.

Real Scenario: Adding a Column to Production

What AI Generated

// Step 1: Edit your schema.prisma to add the new field
// Tested with Prisma 5.x

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  bio       String?  // ← Add this: optional (?) means nullable in SQL
  createdAt DateTime @default(now())
}

// Step 2: Generate and apply the migration
// Run in your terminal:
// npx prisma migrate dev --name add-bio-to-users

// This command:
// 1. Compares schema.prisma to your current database
// 2. Generates a migration SQL file
// 3. Applies it to your development database
// 4. Records it in _prisma_migrations table

// The generated migration file looks like:
// prisma/migrations/20260317_add_bio_to_users/migration.sql

-- prisma/migrations/20260317153042_add_bio_to_users/migration.sql
-- This file is auto-generated by Prisma. Commit it to git.

ALTER TABLE "users" ADD COLUMN "bio" TEXT;
-- TEXT (no NOT NULL) because bio is optional — existing rows get NULL, which is fine

// Step 3: Deploy to production
// Run this on your production server (or in your CI/CD pipeline):
// npx prisma migrate deploy

// migrate deploy:
// - Checks which migrations have already run (via _prisma_migrations table)
// - Applies only the new ones
// - Safe for production: never touches data, only schema
// - Fails loudly if a migration fails (doesn't silently skip)

Understanding the Migration Workflow

The Three Commands

What the Migration History Table Looks Like

-- _prisma_migrations table (Prisma manages this automatically)
SELECT migration_name, applied_steps_count, finished_at
FROM _prisma_migrations
ORDER BY started_at;

-- migration_name                                    | applied | finished_at
-- 20260314_init                                     |    1    | 2026-03-14 10:00:00
-- 20260315_add_posts_table                          |    1    | 2026-03-15 08:30:00
-- 20260317_add_bio_to_users                         |    1    | 2026-03-17 15:30:00

-- When you run 'migrate deploy', it checks this table
-- and only runs migrations not yet in the list

Handling a Migration That Adds a Required Column

Adding a nullable column (String?) is safe — existing rows get NULL. Adding a required column (String, no ?) without a default fails, because existing rows have no value to put there.

// Option 1: Make it optional (nullable)
bio  String?   // Existing rows get NULL — safe

// Option 2: Give it a default value
role String @default("user")   // Existing rows get "user" — safe

// Option 3: Two-step migration for required field without default
// Migration 1: Add as nullable
ALTER TABLE users ADD COLUMN display_name TEXT;

// Fill existing rows with data
UPDATE users SET display_name = name WHERE display_name IS NULL;

// Migration 2: Make it required
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;

Raw SQL Migrations (Without Prisma)

-- Many projects use simple numbered SQL files tracked in git
-- migrations/
-- ├── 001_init.sql
-- ├── 002_add_posts.sql
-- └── 003_add_bio_to_users.sql

-- 003_add_bio_to_users.sql:
BEGIN;

ALTER TABLE users ADD COLUMN bio TEXT;

-- Record this migration ran (in your custom tracking table)
INSERT INTO schema_migrations (version) VALUES ('003_add_bio_to_users');

COMMIT;

Dangerous Migration Patterns to Avoid

1. Dropping a Column with Live Traffic

If your app code still references a column you just dropped, every query touching that column will fail instantly. Safe approach: deploy code that no longer references the column → wait → then drop the column in a follow-up migration.

2. Long-Running ALTER TABLE on Large Tables

On PostgreSQL, some schema changes on very large tables can temporarily slow down or block your app while the database applies the change. The bigger the table, the longer it takes. For most vibe coding projects this won't be an issue — but if your app grows to millions of rows, ask your AI to use the "add nullable first, fill data, then add constraint" pattern:

-- RISKY on large tables (can block other queries while running):
ALTER TABLE events ADD COLUMN processed BOOLEAN NOT NULL DEFAULT false;

-- SAFER approach for big tables:
ALTER TABLE events ADD COLUMN processed BOOLEAN;
-- Then fill data in batches (background job)
-- Then add NOT NULL constraint when all rows are filled

3. Using db push in Production

prisma db push bypasses migration history entirely — changes the database directly without creating migration files. Your team loses track of what changed, when, and why. Never use it outside of local development/prototyping. Store your database URL in an environment variable and make sure production credentials aren't exposed in your code.

How to Debug Migration Errors with AI

What to Learn Next

Frequently Asked Questions