Next.js boilerplate with Drizzle ORM

src/db/tables.ts (re-exported, with every other battery's tables, from src/db/schema.ts) is a description of the database, not a change to it. A schema edit that is not accompanied by a generated migration is a change that exists only on the machine it was typed on.

The loop, every time

1. Edit src/db/tables.ts.
2. bun run db:generate: writes drizzle/NNNN_*.sql plus the drizzle/meta snapshot.
3. Read the generated SQL. Every time. It is the only place a rename shows up as DROP COLUMN + ADD COLUMN.
4. bun run db:migrate to apply it locally.
5. Commit src/db/tables.ts and the whole drizzle/ folder in the same commit. Never one without the other.

If you edited the schema and the diff contains no new file under drizzle/, the change is not finished.

Never edit a migration that has been applied

Drizzle stores the hash of each applied migration in drizzle.__drizzle_migrations. Editing an already-applied .sql file makes the file and the recorded hash disagree: environments that ran the old version skip your edit forever, and fresh environments get a schema nobody else has. The result is a fleet where no two databases match.

Fix forward instead: write the correction in src/db/tables.ts and generate a new migration.

The single exception: a migration you generated in this working tree, have not applied anywhere but your own machine, and have not pushed. That is the window for hand-editing: adding a backfill UPDATE, splitting an ALTER into safe steps, or swapping CREATE INDEX for CREATE INDEX CONCURRENTLY. If you take it, re-run bun run db:migrate against a freshly reset local database to prove the file still applies from zero.

db:push is for scratch databases only

bun run db:push diffs the schema straight against a live database and applies the difference with no file and no record. It is useful while shaping a table on a private branch database. It is never how a change reaches a database someone else uses, because there is nothing to review, nothing to replay, and nothing to roll forward from.

Before opening a pull request, drop the pushed database or reset it, then run bun run db:generate so the change exists as SQL.

No raw DDL in application code

CREATE TABLE, ALTER TABLE, DROP, CREATE INDEX, CREATE EXTENSION and GRANT belong in drizzle/. Not in a route handler, not in a server action, not in a seed script, not behind an if (!exists) guard at boot. Application code that mutates the schema races every other instance and makes the migration history a lie.

Postgres features Drizzle cannot express in the schema builder (a trigger, a materialised view, a partial unique index with a complex predicate, an extension) go in a hand-written SQL file created with bunx drizzle-kit generate --custom, which is versioned like any other migration.

Migrations run at deploy time, not request time

Use bun run db:migrate:deploy from the build or a release step. Do not call the migrator from instrumentation.ts, middleware or a route: serverless instances start concurrently and would fight over the migration lock on every cold start.

Migrations use the direct connection, never the pooler. directConnectionUrl() in src/db/direct-url.ts resolves it: it prefers the unpooled string your database battery declares in .env.local and falls back to rewriting the pooled host. Call it rather than hardcoding a second URL, and do not read DATABASE_URL for a migration path directly.

Index every foreign key you filter on

Postgres indexes the referenced side of a foreign key automatically, because it is a primary key or unique. It indexes the referencing side never. A posts.author_id with no index turns every "posts by this author" query and every ON DELETE CASCADE into a sequential scan.

export const posts = pgTable(
  "posts",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    authorId: text("author_id").notNull(),
    publishedAt: timestamp("published_at", { withTimezone: true }),
    ...timestamps,
  },
  (table) => [
    index("posts_author_id_idx").on(table.authorId),
    index("posts_published_at_idx").on(table.publishedAt),
  ],
);

Order the columns of a composite index the way you filter: equality columns first, then the range or sort column. on(table.tenantId, table.createdAt) serves where tenant = ? order by created_at desc; the reverse does not.

On a table that already holds production rows, hand-edit the generated migration to CREATE INDEX CONCURRENTLY and delete the transaction wrapper: CONCURRENTLY cannot run inside a transaction.

Column conventions

• Pass the SQL name explicitly: text("display_name"). The property is camelCase, the column is snake_case, and nothing depends on a global casing setting that a future config change could flip.
• Timestamps are timestamp("...", { withTimezone: true }). A naive timestamp is a bug waiting for a deploy in another region.
• Money is an integer count of minor units (integer("amount_cents")), never a float and never numeric. A currency's exponent is not always 2 (JPY and KRW have none, KWD has three) so store minor units and format with Intl.NumberFormat, which knows the exponent.
• Enumerated values: prefer text plus a $type<"a" | "b">() annotation and a check constraint over pgEnum, unless the set genuinely never changes. Adding a value to a Postgres enum type is a migration; adding one to a union type is not.
• Nullable means "this can legitimately be absent". If it cannot, mark it .notNull() and give it a default, so the next migration on a populated table does not fail.

Queries

• Select the columns you need. db.select({ id: posts.id, title: posts.title }) narrows the return type and the row width at the same time.
• Never build a sql template from concatenated user input. Use a placeholder: sql`where slug = ${slug}` parameterises; string concatenation into a sql.raw does not.
• db.query.<table>.findMany({ with: … }) for tree-shaped reads, db.select().from().innerJoin() for flat rows and aggregates. Do not mix the two styles in one function.
• Never await a query inside a loop over rows. Collect the ids and use inArray, or express it as one join.
• db.transaction() requires a driver that can hold a session, and db is not always one: on Neon's HTTP driver the call type-checks and throws at runtime. Use dbSession from @/db for that code path, or fold the work into a single statement with a CTE. See docs/solutions/drizzle/transactions-in-serverless.md.

Ownership

src/db/schema.ts is shared ground: several batteries contributed tables to it. Keep each battery's tables in their own block, do not reorder blocks casually (the generated migration diff gets noisier), and do not rename a table another battery's code queries by name without grepping for it first.

src/db/client.ts is owned by the database battery: it exports the connection primitives (getSql, sql, pools, health checks), not the ORM handle. Import db (and dbSession, and recordAudit) from @/db, which is the one surface that means the same thing under either ORM. Never construct a second connection in application code: src/db/driver.ts wraps the client that battery already made, and every extra one is another pooled slot your serverless functions will exhaust.