|$ curl https://forge-ai.dev/api/markdown?path=docs/databases/migrations

$cat docs/database-migrations.md

updated Recently·45 min read·published

Database Migrations

◆Migrations◆DevOps◆Databases◆Beginner to Advanced🎯Free Tools

Introduction

Database migrations are version-controlled scripts that modify your database schema. They track every change to your tables, columns, indexes, and constraints over time, ensuring that every developer and environment shares the same database structure.

Without migrations, schema changes are manual, error-prone, and uncoordinated. One developer's local change breaks another developer's setup. Production deployments become nerve-wracking guessing games. Migrations solve this by making schema changes explicit, repeatable, and reversible.

This guide covers migration tools for Prisma, Drizzle, and raw SQL, along with rollback strategies, seed data management, and zero-downtime migration patterns.

📝

note

A migration is a single, reversible step in your database's evolution. Think of it like a Git commit for your schema — each migration captures one logical change and can be undone if something goes wrong.

Prisma Migrations

Prisma provides a built-in migration system that compares your schema file with the current database state and generates SQL migration files. It tracks which migrations have been applied in a_prisma_migrations table.

prisma-migrations.sh

Bash

1	# Create a new migration after schema changes
2	npx prisma migrate dev --name add_user_role
3
4	# This:
5	# 1. Compares schema.prisma with database
6	# 2. Generates SQL in prisma/migrations/
7	# 3. Applies the migration to dev database
8	# 4. Regenerates Prisma Client
9
10	# Apply pending migrations in production
11	npx prisma migrate deploy
12
13	# Check migration status
14	npx prisma migrate status
15
16	# Reset database (development only!)
17	npx prisma migrate reset
18
19	# Create a migration without applying it
20	npx prisma migrate dev --create-only
21
22	# Review the generated SQL
23	cat prisma/migrations/20250115_add_user_role/migration.sql

migration-sql.sql

SQL

1	-- Example: Prisma-generated migration SQL
2	-- 20250115_add_user_role/migration.sql
3
4	-- CreateTable
5	CREATE TABLE "users" (
6	"id" TEXT NOT NULL,
7	"email" TEXT NOT NULL,
8	"name" TEXT,
9	"role" TEXT NOT NULL DEFAULT 'USER',
10	"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
11	"updated_at" TIMESTAMP(3) NOT NULL,
12
13	CONSTRAINT "users_pkey" PRIMARY KEY ("id")
14	);
15
16	-- CreateIndex
17	CREATE UNIQUE INDEX "users_email_key" ON "users"("email");
18
19	-- Example: Manual migration for complex changes
20	-- 20250120_add_indexes/migration.sql
21
22	-- CreateIndex
23	CREATE INDEX "idx_orders_user_status" ON "orders"("user_id", "status");
24
25	-- CreateIndex
26	CREATE INDEX "idx_orders_created_at" ON "orders"("created_at" DESC);
27
28	-- Raw SQL migration (when Prisma's schema doesn't cover your needs)
29	-- 20250125_add_jsonb_index/migration.sql
30
31	-- Raw SQL
32	CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
33
34	-- Raw SQL
35	ALTER TABLE products ADD COLUMN search_vector tsvector
36	GENERATED ALWAYS AS (
37	setweight(to_tsvector('english', coalesce(name, '')), 'A')
38	) STORED;

ℹ

info

Use npx prisma migrate dev in development and npx prisma migrate deploy in production. The dev command regenerates the client and resets the database if needed; deploy only applies pending migrations.

Drizzle Migrations

Drizzle uses drizzle-kit for migrations. It generates SQL files from your schema definition and supports push (dev) and migrate (production) workflows.

drizzle-migrations.sh

Bash

1	# Generate migration SQL
2	npx drizzle-kit generate
3
4	# Push schema directly to database (development)
5	npx drizzle-kit push
6
7	# Run pending migrations
8	npx drizzle-kit migrate
9
10	# Open Drizzle Studio (visual database browser)
11	npx drizzle-kit studio
12
13	# Check migration status
14	npx drizzle-kit check
15
16	# Pull existing database schema into Drizzle schema
17	npx drizzle-kit pull

drizzle-config.ts

TypeScript

1	// drizzle.config.ts
2	import type { Config } from "drizzle-kit";
3
4	export default {
5	schema: "./src/schema/*",
6	out: "./drizzle/migrations",
7	dialect: "postgresql",
8	dbCredentials: {
9	url: process.env.DATABASE_URL!
10	},
11	verbose: true,
12	strict: true
13	} satisfies Config;
14
15	// Programmatic migrations (for CI/CD)
16	import { migrate } from "drizzle-orm/postgres-js/migrator";
17	import { db } from "./db";
18
19	async function runMigrations() {
20	console.log("Running migrations...");
21	await migrate(db, { migrationsFolder: "./drizzle/migrations" });
22	console.log("Migrations complete.");
23	}
24
25	runMigrations().catch(console.error);

Raw SQL Migrations

For maximum control, you can write migrations as raw SQL files and manage them with a migration runner. This approach is database-agnostic and works with any ORM or raw SQL codebase.

migration-runner.ts

TypeScript

1	// migrations/runner.ts
2	import { readFileSync, readdirSync } from "fs";
3	import { join } from "path";
4	import { Pool } from "pg";
5
6	const MIGRATIONS_DIR = join(__dirname, "../sql");
7
8	interface Migration {
9	id: string;
10	name: string;
11	filename: string;
12	sql: string;
13	appliedAt?: Date;
14	}
15
16	async function getAppliedMigrations(pool: Pool): Promise<Set<string>> {
17	const result = await pool.query(
18	"SELECT id FROM schema_migrations ORDER BY applied_at"
19	);
20	return new Set(result.rows.map((r) => r.id));
21	}
22
23	async function getAvailableMigrations(): Promise<Migration[]> {
24	const files = readdirSync(MIGRATIONS_DIR)
25	.filter((f) => f.endsWith(".sql"))
26	.sort();
27
28	return files.map((filename) => ({
29	id: filename.split("_")[0],
30	name: filename.replace(".sql", ""),
31	filename,
32	sql: readFileSync(join(MIGRATIONS_DIR, filename), "utf-8")
33	}));
34	}
35
36	async function runMigrations(pool: Pool) {
37	const applied = await getAppliedMigrations(pool);
38	const available = await getAvailableMigrations();
39	const pending = available.filter((m) => !applied.has(m.id));
40
41	if (pending.length === 0) {
42	console.log("No pending migrations.");
43	return;
44	}
45
46	for (const migration of pending) {
47	console.log(`Applying: ${migration.name}`);
48
49	try {
50	await pool.query("BEGIN");
51	await pool.query(migration.sql);
52	await pool.query(
53	"INSERT INTO schema_migrations (id, name, applied_at) VALUES ($1, $2, NOW())",
54	[migration.id, migration.name]
55	);
56	await pool.query("COMMIT");
57	console.log(`Applied: ${migration.name}`);
58	} catch (error) {
59	await pool.query("ROLLBACK");
60	console.error(`Failed: ${migration.name}`, error);
61	throw error;
62	}
63	}
64	}
65
66	// sql/001_create_users.sql
67	// CREATE TABLE users (
68	// id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
69	// email VARCHAR(255) UNIQUE NOT NULL,
70	// name VARCHAR(255),
71	// created_at TIMESTAMPTZ DEFAULT NOW()
72	// );
73	//
74	// CREATE TABLE schema_migrations (
75	// id VARCHAR(255) PRIMARY KEY,
76	// name VARCHAR(255) NOT NULL,
77	// applied_at TIMESTAMPTZ NOT NULL
78	// );

Rollback Strategies

Rollback is the ability to reverse a migration if it causes problems. Not all migration systems support automatic rollbacks, so planning for them is essential.

rollback.sql

SQL

1	-- Forward migration: Add a column
2	-- migrations/005_add_user_phone.sql
3	ALTER TABLE users ADD COLUMN phone VARCHAR(20);
4	CREATE INDEX idx_users_phone ON users (phone);
5
6	-- Rollback migration: Undo the changes
7	-- migrations/005_rollback_add_user_phone.sql
8	DROP INDEX IF EXISTS idx_users_phone;
9	ALTER TABLE users DROP COLUMN IF EXISTS phone;
10
11	-- Forward: Create a table
12	-- migrations/006_create_audit_log.sql
13	CREATE TABLE audit_log (
14	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
15	user_id UUID NOT NULL REFERENCES users(id),
16	action VARCHAR(50) NOT NULL,
17	entity_type VARCHAR(50) NOT NULL,
18	entity_id UUID NOT NULL,
19	old_data JSONB,
20	new_data JSONB,
21	created_at TIMESTAMPTZ DEFAULT NOW()
22	);
23
24	CREATE INDEX idx_audit_log_user ON audit_log (user_id, created_at DESC);
25	CREATE INDEX idx_audit_log_entity ON audit_log (entity_type, entity_id);
26
27	-- Rollback: Drop the table
28	-- migrations/006_rollback_create_audit_log.sql
29	DROP TABLE IF EXISTS audit_log CASCADE;
30
31	-- Rename column (safe rollback)
32	-- Forward
33	ALTER TABLE users RENAME COLUMN name TO full_name;
34	-- Rollback
35	ALTER TABLE users RENAME COLUMN full_name TO name;
36
37	-- Data migration (irreversible — back up first!)
38	-- Forward
39	UPDATE users SET full_name = CONCAT(first_name, ' ', last_name);
40	-- Rollback (not possible without backup)
41	-- This is why you ALWAYS back up before data migrations

⚠

warning

Data migrations (INSERT, UPDATE, DELETE) are generally irreversible. Always back up your database before running data migrations. For large tables, run data migrations in batches to avoid locking the table for extended periods.

Seed Data

Seed data populates your database with initial or test data. It is essential for development environments, testing, and production bootstrap data.

prisma-seed.ts

TypeScript

1	// prisma/seed.ts (Prisma)
2	import { PrismaClient } from "@prisma/client";
3	import { hash } from "bcryptjs";
4
5	const prisma = new PrismaClient();
6
7	async function main() {
8	// Create admin user
9	const admin = await prisma.user.upsert({
10	where: { email: "admin@example.com" },
11	update: {},
12	create: {
13	email: "admin@example.com",
14	name: "Admin User",
15	role: "ADMIN",
16	password: await hash("admin123", 12),
17	profile: {
18	create: { bio: "System administrator" }
19	}
20	}
21	});
22
23	// Create test users
24	const users = await Promise.all(
25	Array.from({ length: 10 }, (_, i) =>
26	prisma.user.upsert({
27	where: { email: `user${i + 1}@example.com` },
28	update: {},
29	create: {
30	email: `user${i + 1}@example.com`,
31	name: `Test User ${i + 1}`,
32	role: "USER"
33	}
34	})
35	)
36	);
37
38	// Create sample posts
39	for (const user of users) {
40	await prisma.post.createMany({
41	data: Array.from({ length: 5 }, (_, i) => ({
42	title: `Post ${i + 1} by ${user.name}`,
43	content: `This is the content of post ${i + 1}.`,
44	authorId: user.id,
45	published: i % 2 === 0
46	})),
47	skipDuplicates: true
48	});
49	}
50
51	console.log("Seed data created successfully.");
52	}
53
54	main()
55	.catch(console.error)
56	.finally(() => prisma.$disconnect());

drizzle-seed.ts

TypeScript

1	// drizzle/seed.ts (Drizzle)
2	import { db } from "../src/db";
3	import { users, posts } from "../src/schema";
4
5	async function seed() {
6	// Insert users
7	const insertedUsers = await db.insert(users).values([
8	{ email: "admin@example.com", name: "Admin", role: "ADMIN" },
9	{ email: "alice@example.com", name: "Alice", role: "USER" },
10	{ email: "bob@example.com", name: "Bob", role: "USER" }
11	]).onConflictDoNothing().returning();
12
13	// Insert posts
14	await db.insert(posts).values(
15	insertedUsers.flatMap((user) =>
16	Array.from({ length: 3 }, (_, i) => ({
17	title: `Post ${i + 1}`,
18	content: `Content for post ${i + 1}`,
19	authorId: user.id,
20	published: true
21	}))
22	)
23	);
24
25	console.log("Seed complete.");
26	}
27
28	seed();

Zero-Downtime Migrations

In production, schema changes must not disrupt running applications. Zero-downtime migration strategies ensure that both old and new application versions can work with the database during the migration window.

zero-downtime.sql

SQL

1	-- SAFE: Adding a column with a default (PostgreSQL 11+)
2	-- Old code still works — it just doesn't see the new column
3	ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL;
4
5	-- SAFE: Adding a NOT NULL column (PostgreSQL 11+)
6	-- Default is stored in catalog, not written to every row
7	ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';
8
9	-- DANGEROUS: Renaming a column
10	-- Step 1: Add new column
11	ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
12
13	-- Step 2: Backfill data (in batches!)
14	UPDATE users SET full_name = name WHERE full_name IS NULL
15	AND id IN (SELECT id FROM users WHERE full_name IS NULL LIMIT 10000);
16
17	-- Step 3: Deploy app code that reads from both columns
18	-- Step 4: Deploy app code that writes to new column only
19	-- Step 5: Drop old column (after confirming no reads)
20	ALTER TABLE users DROP COLUMN name;
21
22	-- SAFE: Adding an index (non-blocking)
23	CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
24	-- CONCURRENTLY prevents table locks but takes longer
25
26	-- SAFE: Removing an index
27	DROP INDEX CONCURRENTLY IF EXISTS idx_old_index;
28
29	-- SAFE: Changing column type (expand and contract)
30	-- Step 1: Add new column
31	ALTER TABLE users ADD COLUMN email_new VARCHAR(320);
32	-- Step 2: Copy data
33	UPDATE users SET email_new = email WHERE email_new IS NULL
34	AND id IN (SELECT id FROM users WHERE email_new IS NULL LIMIT 10000);
35	-- Step 3: Swap column names (atomic)
36	ALTER TABLE users RENAME COLUMN email TO email_old;
37	ALTER TABLE users RENAME COLUMN email_new TO email;
38	-- Step 4: Drop old column after verification
39	ALTER TABLE users DROP COLUMN email_old;

danger

Never rename or drop columns in a single migration step in production. Use the expand-and-contract pattern: add the new column, migrate data, update application code, then drop the old column — all as separate deployments.

Migration Best Practices

Following migration best practices prevents data loss, downtime, and team coordination issues. These practices are distilled from years of production experience.

best-practices.sh

Bash

1	# Best practices for migration management:
2
3	# 1. One logical change per migration
4	# BAD: 003_add_users_and_orders.sql (two unrelated changes)
5	# GOOD: 003_add_users.sql + 004_create_orders.sql
6
7	# 2. Always review generated SQL before applying
8	npx prisma migrate dev --name add_phone
9	cat prisma/migrations/*/migration.sql # review!
10
11	# 3. Never modify applied migrations
12	# Create a new migration instead
13	npx prisma migrate dev --name fix_phone_default
14
15	# 4. Test migrations on a copy of production data
16	pg_dump production_db \| psql test_db
17
18	# 5. Use CI/CD to validate migrations
19	# .github/workflows/migrate.yml
20	# - Run on pull request
21	# - Apply to test database
22	# - Verify schema matches expected state
23
24	# 6. Back up before production migrations
25	pg_dump -Fc production_db > backup_$(date +%Y%m%d_%H%M%S).dump
26
27	# 7. Use advisory locks to prevent concurrent migrations
28	# PostgreSQL: SELECT pg_advisory_lock(12345);
29
30	# 8. Separate schema and data migrations
31	# Schema migrations: DDL (CREATE, ALTER, DROP)
32	# Data migrations: DML (INSERT, UPDATE, DELETE)
33	# Data migrations need more care (batching, verification)

✓

best practice

Name migrations descriptively with a sequence number prefix. Use the formatNNN_description.sql (e.g.,005_add_user_phone.sql). The number ensures ordering; the description documents intent.

Testing Migrations

Migrations should be tested in CI to catch errors before they reach production. The key tests are: applying cleanly, rolling back, and idempotent re-application.

migration-tests.ts

TypeScript

1	// migrations/__tests__/migrations.test.ts
2	import { describe, it, expect, beforeAll, afterAll } from "vitest";
3	import { Pool } from "pg";
4
5	describe("Database Migrations", () => {
6	let pool: Pool;
7
8	beforeAll(async () => {
9	pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });
10	// Start fresh
11	await pool.query("DROP SCHEMA public CASCADE; CREATE SCHEMA public");
12	});
13
14	afterAll(async () => {
15	await pool.end();
16	});
17
18	it("should apply all migrations successfully", async () => {
19	// Run migrations
20	const { runMigrations } = await import("../runner");
21	await expect(runMigrations(pool)).resolves.not.toThrow();
22
23	// Verify tables exist
24	const tables = await pool.query(`
25	SELECT table_name FROM information_schema.tables
26	WHERE table_schema = 'public'
27	ORDER BY table_name
28	`);
29
30	expect(tables.rows.map(r => r.table_name)).toContain("users");
31	expect(tables.rows.map(r => r.table_name)).toContain("posts");
32	});
33
34	it("should be idempotent (safe to run twice)", async () => {
35	const { runMigrations } = await import("../runner");
36	await expect(runMigrations(pool)).resolves.not.toThrow();
37	});
38
39	it("should have correct column types", async () => {
40	const columns = await pool.query(`
41	SELECT column_name, data_type
42	FROM information_schema.columns
43	WHERE table_name = 'users'
44	ORDER BY ordinal_position
45	`);
46
47	const emailCol = columns.rows.find(r => r.column_name === "email");
48	expect(emailCol).toBeDefined();
49	expect(emailCol.data_type).toBe("character varying");
50	});
51
52	it("should have required indexes", async () => {
53	const indexes = await pool.query(`
54	SELECT indexname FROM pg_indexes
55	WHERE tablename = 'users'
56	`);
57
58	expect(indexes.rows.map(r => r.indexname))
59	.toContain("users_email_key");
60	});
61	});

X GitHub YouTube Instagram Facebook Discord Telegram Buy me a coffee Sponsor