Manage database migrations

The decision determines everything that follows

• Prisma Migrate — you describe the schema in schema.prisma; Prisma diffs against the DB and generates the SQL migration. Best when you're already using Prisma as your ORM. Strong type generation; tight coupling between schema, types, and DB.
• Drizzle Kit — you define the schema as TypeScript code (table definitions in .ts files); Drizzle generates SQL migration files. Less coupling than Prisma — the migrations are plain SQL you can read, edit, and run elsewhere. Pairs with Drizzle ORM but works standalone too.
• Raw SQL + a runner (node-pg-migrate, knex, sqitch, Flyway) — you write the SQL by hand; the runner tracks what's applied. No coupling to any ORM. Most portable; the schema lives only in the SQL files and the DB itself, not in any other source of truth.

Quick rule of thumb: using Prisma already? Prisma Migrate. Want type-safe DB code but plain-SQL migrations? Drizzle Kit. Want maximum portability or working in a non-TypeScript stack? Raw SQL.

You can switch later but it's painful. Pick deliberately.

For projects already on Prisma

Edit prisma/schema.prisma to describe the desired schema. Adding a new table:

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  body      String
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
}

Generate and apply a migration:

npx prisma migrate dev --name add_post_table

This creates prisma/migrations/<timestamp>_add_post_table/migration.sql — readable SQL Prisma derived from the diff — runs it against your dev DB, and regenerates the TypeScript types. Open the file and read what it generated. Prisma's diffs are usually correct but it has done surprising things (dropped + recreated columns instead of renaming, generated indexes you didn't expect) — catch them in review, not in production.

Commit the migration folder. On deploy: npx prisma migrate deploy applies all unrun migrations against the target DB. Never run migrate dev against production — it can prompt for resets.

SQL migrations from TypeScript schema definitions

Define the schema as TypeScript in db/schema.ts:

import { pgTable, serial, varchar, integer, timestamp } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  createdAt: timestamp("created_at").defaultNow(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  body: varchar("body").notNull(),
  authorId: integer("author_id").references(() => users.id).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
});

Configure Drizzle in drizzle.config.ts:

import { defineConfig } from "drizzle-kit";
export default defineConfig({
  schema: "./db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: { url: process.env.DATABASE_URL! },
});

Generate the migration SQL:

npx drizzle-kit generate
# Creates drizzle/0001_add_posts.sql with the actual SQL.
# Open it. Read it. Commit it.

# Apply it:
npx drizzle-kit migrate

Drizzle's appeal: the generated SQL is plain. If you don't like the diff, hand-edit the .sql before committing. The migrate command tracks applied migrations in a __drizzle_migrations table.

SQL you wrote, runner that tracks it

For Node, node-pg-migrate is a small, well-maintained runner. Other ecosystems have equivalents: knex migrations, Flyway (JVM and standalone), sqitch (language-agnostic), Django/Rails/Laravel built-in migrations.

npm i node-pg-migrate
npx node-pg-migrate create add-posts-table
# Creates migrations/<timestamp>_add-posts-table.js with up() and down() exports.

Write the SQL:

// migrations/1715000000000_add-posts-table.js
exports.up = pgm => {
  pgm.createTable("posts", {
    id: "id",  // shorthand for serial primary key
    title: { type: "varchar(255)", notNull: true },
    body: { type: "text", notNull: true },
    author_id: { type: "integer", notNull: true, references: "users" },
    created_at: { type: "timestamp", default: pgm.func("now()") },
  });
};

exports.down = pgm => {
  pgm.dropTable("posts");
};

Apply / revert:

npx node-pg-migrate up          # apply all unrun
npx node-pg-migrate down        # revert most recent

The migrations are tracked in a pgmigrations table. For more advanced needs (raw SQL only, no DSL), use pgm.sql("CREATE TABLE ...") inside up(). The runner doesn't care; it just executes whatever you put there.

Three non-negotiables

• Never edit a deployed migration. Once a migration has run on any shared environment (staging, production), it is frozen. The next change is a new migration. Editing a deployed one means the file no longer matches what's already applied — every environment now has a different schema and the runner can't reconcile.
• Migrations are always additive on the way up. Add columns as nullable. Add tables. Add indexes. Reversing a deploy by reverting the migration usually doesn't work in production (data has been written that depends on the new schema). Plan for forward-only.
• Test the migration end-to-end before deploying. Spin up a clean DB, replay all migrations, verify the schema matches expectations. This is what CI does for you — see CI/CD with GitHub Actions Step 6 for the service-container pattern that runs migrations in CI.

What works on 1,000 rows breaks on 100 million

Most migrations are instant on small tables and slow-or-locking on big ones. The patterns to know:

• Adding a NOT NULL column with a default value — on Postgres 11+, fast (metadata-only). On older versions, full table rewrite. Always check your Postgres version.
• Adding a foreign key constraint — locks both tables while validating. On big tables: add the constraint as NOT VALID first (fast, no validation), then VALIDATE CONSTRAINT in a separate migration (slow but doesn't block writes).
• Creating an index — locks writes on the table by default. Use CREATE INDEX CONCURRENTLY on Postgres for an online build. Cannot run inside a transaction — disable the wrapping transaction for that migration (each tool has a flag).
• Backfilling data — never put a "UPDATE big_table SET col = ..." in a migration. It locks the table for hours. Backfill in batches from application code or a separate script; the migration only adds the column.

For really big tables (50M+ rows on Postgres), look at pg_repack and online-schema-change tools. The migration runner can't help you avoid locks; the SQL itself has to be written carefully.

When you can, when you can't, what to do instead

The down migration is mostly useful in dev — applying a migration, realizing it's wrong, reverting, regenerating. In production, "roll back the migration" is usually the wrong answer:

• Data has been written that uses the new schema. Reverting drops that data.
• Long-running migrations that you canceled mid-flight may have left the DB in a partial state. The down migration doesn't know which steps completed.
• Rolling back a deployed migration on prod creates a divergence: prod is now on schema vN-1 while staging is on vN. Your next migration written against vN won't apply on prod.

The production pattern instead: roll forward, not back. If a migration deployed and you regret it, write a new migration that fixes the regret. Drop the column you shouldn't have added; revert the constraint; whatever. This keeps the migration sequence linear and every environment converges to the same head.

Keep it out of migrations

"Seed data" — fake users for dev, lookup tables, demo content — is tempting to put in migrations because it lives near the schema. Don't. Migrations should be schema-only; data belongs in a separate script.

// scripts/seed.ts
import { db } from "./db";

await db.users.create({ email: "admin@local", role: "admin" });
await db.users.createMany([
  { email: "alice@local" },
  { email: "bob@local" },
]);

Run npm run seed after a fresh DB setup. The seed script is idempotent (use upsert or check-before-insert); it can run multiple times safely. This separation is what lets your migrations be pure schema and your seed data live next to the test fixtures it represents.