Drizzle migrations and seeds

The Plain CRUD with Drizzle recipe shows how to wire drizzleProvider so handlers can use the real Drizzle API. What it deliberately skips is the migration story — how you author SQL changes, run them at the right point in the lifecycle, and seed enough data to actually develop against. This recipe fills that gap.

What you'll set up

• A drizzle.config.ts at the repo root pointing at your schema and a db/migrations/ folder.
• npm scripts for db:generate, db:migrate, and db:seed — three verbs, three responsibilities.
• A boot-time policy that aligns those scripts with drizzleProvider: either the provider runs migrate() before any handler can serve traffic (small apps), or migrations are gated behind a separate CI step and the provider's check() asserts the schema is at the expected version (production).
• A pglite-backed reset helper so tests start from a known state in milliseconds.

Project layout

The convention mirrors the rest of the Drizzle ecosystem. Everything Drizzle-shaped lives under db/ next to your app code:

src/
  db/
    schema.ts          # Drizzle table definitions
    index.ts           # exports db + pool
    migrate.ts         # programmatic migrate() entry point
    seed.ts            # idempotent seed script
    migrations/        # generated by drizzle-kit — committed
      0000_init.sql
      0001_add_users_email_index.sql
      meta/
    fixtures/          # JSON seeds, hand-authored
      users.json
      tenants.json
  app.ts
  users/
    ...
drizzle.config.ts

db/migrations/ is generated, but commit it. The folder is the contract between developers and production — Drizzle Kit hashes every file in meta/_journal.json, and out-of-band edits will be rejected.

Authoring a migration

1. Edit the schema

// src/db/schema.ts
import { pgTable, serial, text, timestamp, index } from "drizzle-orm/pg-core";

export const users = pgTable(
  "users",
  {
    id:        serial("id").primaryKey(),
    email:     text("email").notNull().unique(),
    name:      text("name").notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (t) => ({
    emailIdx: index("users_email_idx").on(t.email),
  }),
);

2. Configure drizzle-kit

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema:  "./src/db/schema.ts",
  out:     "./src/db/migrations",
  dialect: "postgresql",
  dbCredentials: { url: process.env.DATABASE_URL! },
  // Generate without prompts in CI; review SQL in PRs instead.
  strict:    true,
  verbose:   true,
});

3. Add scripts

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate":  "tsx src/db/migrate.ts",
    "db:seed":     "tsx src/db/seed.ts",
    "db:studio":   "drizzle-kit studio"
  }
}

4. Generate

pnpm db:generate

This writes src/db/migrations/0001_add_users_email_index.sql. Open the file. Read the SQL. If it looks wrong (a renamed column you expected to be ALTER ... RENAME showing up as DROP + ADD), abort and adjust your schema or add an explicit migration — Drizzle's diffing is not telepathic.

-- 0001_add_users_email_index.sql
CREATE INDEX IF NOT EXISTS "users_email_idx" ON "users" USING btree ("email");

Running migrations on boot vs on deploy

The two policies are real, they coexist in most repos, and the right answer depends on how scared you are of the next deploy. Pick deliberately.

Boot-time (small apps, dev, ephemeral environments)

Run migrate() inside a plugin that boots before drizzleProvider is used by any handler. The framework's deterministic boot order makes this safe:

// src/db/migrate.ts
import { drizzle }     from "drizzle-orm/node-postgres";
import { migrate }     from "drizzle-orm/node-postgres/migrator";
import { Pool }        from "pg";

export async function runMigrations(): Promise<void> {
  const pool = new Pool({ connectionString: process.env.DATABASE_URL });
  const db   = drizzle(pool);
  await migrate(db, { migrationsFolder: "./src/db/migrations" });
  await pool.end();
}

if (import.meta.url === `file://${process.argv[1]}`) {
  runMigrations().catch((err) => {
    console.error(err);
    process.exit(1);
  });
}

Wire it into the provider as a boot step:

// src/app.ts
import { createApp, definePlugin } from "@nwire/app";
import { drizzleProvider } from "@nwire/drizzle";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { sql } from "drizzle-orm";
import { db, pool } from "./db/index.js";

const migrations = definePlugin("db-migrations", ({ boot }) => {
  boot(async () => {
    await migrate(db, { migrationsFolder: "./src/db/migrations" });
  });
});

export const app = createApp({
  appName: "my-app",
  // Order matters: drizzleProvider runs first (db registered), then
  // migrations runs in its boot() hook, then the rest of the app comes up.
  plugins: [
    drizzleProvider({
      db,
      close: () => pool.end(),
      check: (d) => d.execute(sql`SELECT 1`),
    }),
    migrations,
  ],
});

Good for: solo apps, demos, CI test runners, preview environments. Bad for: any deploy where two app pods boot in parallel against the same database. Drizzle's migrator does take an advisory lock, but you're still putting schema changes on the critical path of every cold start. Don't.

Out-of-band (production)

Run migrations as a separate CI step, before the new container image is rolled out. Use drizzleProvider's check() to assert the schema version the running code expects, so a mismatch fails the readiness probe instead of corrupting data.

# .github/workflows/deploy.yml (excerpt)
- name: Migrate database
  run: pnpm db:migrate
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

- name: Deploy app
  run: kubectl set image deployment/my-app my-app=${{ env.IMAGE }}

// src/app.ts — check() guards against forgotten migrations
import { sql } from "drizzle-orm";

drizzleProvider({
  db,
  close: () => pool.end(),
  check: async (d) => {
    // Assert the latest expected migration is present. The hash comes from
    // src/db/migrations/meta/_journal.json — generate-time you commit it.
    const expected = "0001_add_users_email_index";
    const [row] = await d.execute<{ hash: string }>(sql`
      SELECT hash FROM "drizzle"."__drizzle_migrations"
      ORDER BY created_at DESC LIMIT 1
    `);
    if (row?.hash !== expected) {
      throw new Error(`schema drift: expected ${expected}, got ${row?.hash}`);
    }
  },
});

Good for: anything you'd page someone over. Bad for: dev loops, where having to remember a separate command is friction. Keep the boot-time variant in your dev script and the out-of-band variant in CI.

Seeding

Seeds are not migrations. Seeds are idempotent data inserts you can re-run on every dev startup. Author them as a script that imports the same db your app uses and reads from db/fixtures/:

// src/db/seed.ts
import { readFile } from "node:fs/promises";
import { fileURLToPath } from "node:url";
import { dirname, join } from "node:path";
import { sql }   from "drizzle-orm";
import { db, pool } from "./index.js";
import { users } from "./schema.js";

const here = dirname(fileURLToPath(import.meta.url));

async function loadJson<T>(name: string): Promise<T> {
  const buf = await readFile(join(here, "fixtures", name), "utf-8");
  return JSON.parse(buf) as T;
}

export async function seed(): Promise<void> {
  const rows = await loadJson<Array<{ email: string; name: string }>>("users.json");

  // Idempotent insert — re-running the seed shouldn't double rows.
  await db
    .insert(users)
    .values(rows)
    .onConflictDoNothing({ target: users.email });

  // eslint-disable-next-line no-console
  console.log(`seeded ${rows.length} users`);
}

if (import.meta.url === `file://${process.argv[1]}`) {
  seed()
    .then(() => pool.end())
    .catch(async (err) => {
      console.error(err);
      await pool.end();
      process.exit(1);
    });
}

// src/db/fixtures/users.json
[
  { "email": "marta@example.com", "name": "Marta" },
  { "email": "dina@example.com",  "name": "Dina"  }
]

Run it directly:

pnpm db:seed

Or wrap it as a CLI action so it shows up alongside the rest of your operational verbs (nwire please ... is the framework's CLI entry):

// src/db/seed.action.ts
import { defineAction } from "@nwire/forge";
import { z } from "zod";
import { seed } from "./seed.js";

export const seedDev = defineAction({
  name: "db.seed-dev",
  description: "Refresh the dev database with fixture data.",
  input: z.object({}),
  handler: async () => {
    await seed();
    return { ok: true } as const;
  },
});

nwire please db:seed-dev

Reset for tests

Tests should never touch your dev database. With pglite, they don't have to — every test file gets its own in-process Postgres. Pair it with a truncate everything helper for between-test isolation:

// src/db/__tests__/reset.ts
import { sql } from "drizzle-orm";
import type { PgDatabase } from "drizzle-orm/pg-core";

/**
 * Truncate every user table — fast (`RESTART IDENTITY CASCADE`),
 * keeps schema intact, no `DROP` / re-migrate dance.
 */
export async function resetDb(db: PgDatabase<any, any, any>): Promise<void> {
  const rows = await db.execute<{ tablename: string }>(sql`
    SELECT tablename FROM pg_tables
    WHERE schemaname = 'public' AND tablename NOT LIKE 'drizzle_%'
  `);
  if (rows.length === 0) return;
  const names = rows.map((r) => `"${r.tablename}"`).join(", ");
  await db.execute(sql.raw(`TRUNCATE ${names} RESTART IDENTITY CASCADE;`));
}

// src/users/__tests__/register.test.ts
import { beforeEach, describe, it, expect } from "vitest";
import { PGlite }  from "@electric-sql/pglite";
import { drizzle } from "drizzle-orm/pglite";
import { migrate } from "drizzle-orm/pglite/migrator";
import { resetDb } from "../../db/__tests__/reset.js";
import { users }   from "../../db/schema.js";

const client = new PGlite();
const db = drizzle({ client });

beforeAll(async () => {
  await migrate(db, { migrationsFolder: "./src/db/migrations" });
});

beforeEach(async () => {
  await resetDb(db);
});

describe("users.register", () => {
  it("creates a row", async () => {
    await db.insert(users).values({ email: "a@b.com", name: "A" });
    expect(await db.select().from(users)).toHaveLength(1);
  });
});

One pglite per test file, one migrate() per beforeAll, one resetDb() per beforeEach. No Docker, no port conflicts, ~50ms cold start.

Production checklist

Don't deploy until each item has a real answer, not "we'll figure it out".

• Backups. Take an automated snapshot immediately before every production migration. Most managed Postgres providers support a one-line pg_dump to S3 or equivalent. Wire it into your deploy workflow as the step right before pnpm db:migrate.
• Rollbacks (read carefully). Drizzle Kit does not generate down migrations by default. This is a deliberate choice — automatic down-migrations are dangerous because they assume the rollback is the inverse of the apply, which is rarely true for data. The honest workaround:
  1. For destructive changes (DROP COLUMN, DROP TABLE), author a manual rollback migration as the next migration (a forward fix), not a down. Roll forward, not back.
  2. Critical schema migrations stay restorable from the pre-migration snapshot. Practice the restore in staging at least once before relying on it.
  3. If you genuinely need symmetric up/down for a project, run drizzle-kit generate --custom and write the inverse SQL by hand. Treat the down-migration as code that will only ever run once, under stress, at 3am.
• Zero-downtime column adds. Adding a NOT NULL column with no default while the old app is still running will break the old app's INSERTs. The safe order is always:
  1. Migration 1: add column as NULL (or with a server-side default). Deploy.
  2. Backfill data with a one-off script.
  3. Migration 2: tighten to NOT NULL once every row has a value. Deploy.
• Renames. Drizzle Kit will see a rename as DROP + ADD by default. For non-empty tables this means data loss. Edit the generated SQL by hand to use ALTER ... RENAME, or add it as a --custom migration. Always.
• Migration locking. Production Postgres migrations should run with lock_timeout and statement_timeout set short (a few seconds) — a runaway ALTER TABLE blocking writes is worse than a failed deploy you can retry.

See also

• Plain CRUD with Drizzle — wiring drizzleProvider itself.
• Graceful shutdown + K8s probes — how check() plugs into /ready and what your deploy needs to look like to use it.
• Testing — broader pglite + test-kit patterns.
• Drizzle Kit docs — upstream reference for the generate / migrate / studio commands.
• Drizzle migrations docs — upstream reference for the migration journal format.