Drizzle ORM in Production: Transactions, Relational Queries vs Joins, Migrations and Pooling

Beyond Basic Drizzle

This guide assumes you already know Drizzle basics — schema definition, simple select/insert/update calls, and a first pass at db.transaction(). If you haven’t set up a project yet, start with the Drizzle ORM Basics, which covers installation, connecting to Postgres/MySQL/SQLite, and a straightforward CRUD workflow. This article picks up where that one stops: it does not re-explain how to define a table or run your first query. Instead it focuses on the decisions that only show up once an application has real traffic — which transaction isolation level protects a given invariant, when the relational query API’s automatic joins start costing more than they save, how a migration that looks harmless in a local database can lock a production table for minutes, and how connection pooling has to change depending on whether the code runs in a long-lived Node process or a serverless function that spins up and tears down per request.

None of this is exotic. It is the set of things that tends to surface in incident reviews after a Drizzle-backed service has been running for a few months: a transaction that silently used the wrong isolation level and let two requests double-spend a balance, a migration that added a NOT NULL column without a default and stalled every write for the duration of a full-table rewrite, or a connection pool sized for a single server that exhausted Postgres’s connection limit the moment the app was deployed behind an autoscaler. The goal here is to walk through the reasoning behind each pattern, not just the code, so that when a similar situation comes up in a real schema you can reason about the trade-off instead of copying a snippet that happened to work somewhere else.

---

Schema Design Patterns

// db/schema.ts
import {
  pgTable, serial, text, timestamp, boolean, integer,
  uuid, jsonb, pgEnum, index, uniqueIndex
} from 'drizzle-orm/pg-core'
import { relations } from 'drizzle-orm'

// Enum type
export const userRoleEnum = pgEnum('user_role', ['admin', 'moderator', 'user'])
export const postStatusEnum = pgEnum('post_status', ['draft', 'published', 'archived'])

// Base columns (reusable)
const timestamps = {
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}

// Users table
export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  role: userRoleEnum('role').default('user').notNull(),
  passwordHash: text('password_hash').notNull(),
  emailVerified: boolean('email_verified').default(false).notNull(),
  deletedAt: timestamp('deleted_at'),  // soft delete
  ...timestamps,
}, (t) => ({
  emailIdx: index('users_email_idx').on(t.email),
  roleIdx: index('users_role_idx').on(t.role),
}))

// Posts table
export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  authorId: uuid('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  title: text('title').notNull(),
  slug: text('slug').notNull(),
  content: text('content').notNull(),
  status: postStatusEnum('status').default('draft').notNull(),
  metadata: jsonb('metadata').$type<{ views: number; tags: string[] }>(),
  publishedAt: timestamp('published_at'),
  ...timestamps,
}, (t) => ({
  slugIdx: uniqueIndex('posts_slug_idx').on(t.slug),
  authorIdx: index('posts_author_idx').on(t.authorId),
  statusIdx: index('posts_status_idx').on(t.status),
}))

// Tags (many-to-many)
export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: text('name').notNull().unique(),
})

export const postTags = pgTable('post_tags', {
  postId: uuid('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  tagId: integer('tag_id').notNull().references(() => tags.id, { onDelete: 'cascade' }),
}, (t) => ({
  pk: { columns: [t.postId, t.tagId] },
}))

A few of these choices are worth calling out explicitly, because they are the ones that get skipped when a schema is copied from a tutorial and then left untouched as an app grows.

The deletedAt column on users is a nullable timestamp, not a boolean isDeleted flag. That distinction matters later: a boolean can only answer “is this deleted,” while a timestamp also answers “when was it deleted,” which you will eventually need for compliance exports, support tickets (“we deleted your account on March 3rd”), or a scheduled hard-delete job that only purges rows older than a retention window. Adding the timestamp now costs nothing; retrofitting it after the boolean is already in production means a backfill migration and a period where you don’t actually know when historical deletions happened.

The indexes on email, role, slug, authorId, and status are not decorative. Every one of them exists because a specific query in this schema filters or joins on that column — emailIdx supports login lookups, authorIdx supports “all posts by this user,” statusIdx supports “all published posts.” A common mistake is to index every column defensively; each extra index adds write overhead (every INSERT/UPDATE has to maintain it) and consumes disk and cache space, so the right default is to add an index when you can point to the query it serves, and remove indexes that pg_stat_user_indexes shows are never scanned. The uniqueIndex on slug does double duty: it enforces the uniqueness constraint at the database level (never trust application-level uniqueness checks alone — they race under concurrent requests) and gives Postgres a fast path for slug lookups on the public-facing post page.

The postTags junction table’s composite primary key deserves a second look before you copy it: pk: { columns: [t.postId, t.tagId] } is written as a plain object here, but current Drizzle releases expect a primaryKey({ columns: [...] }) call imported from drizzle-orm/pg-core — check the exact shape against the version you have installed, since a schema-definition mismatch like this fails at drizzle-kit generate time rather than silently, which is at least easy to catch in CI. The reason a composite key is preferable to a synthetic serial id on this table is that it makes “this post already has this tag” a database-level constraint instead of an application-level check — a duplicate insert simply fails, rather than silently creating a second row that a naive SELECT DISTINCT would need to paper over.

---

Relations — Typed Associations

// Define relations for the relational query API
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}))

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
  postTags: many(postTags),
}))

export const postTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, { fields: [postTags.postId], references: [posts.id] }),
  tag: one(tags, { fields: [postTags.tagId], references: [tags.id] }),
}))

export const tagsRelations = relations(tags, ({ many }) => ({
  postTags: many(postTags),
}))

It’s worth being clear about what relations() actually does, because it’s easy to assume it changes the generated SQL schema — it doesn’t. relations() is purely a client-side declaration that Drizzle’s query builder reads to know how tables connect; it produces no ALTER TABLE, no foreign key constraint, and no migration output. The actual referential integrity still comes from .references(() => users.id) in the column definition inside pgTable. This split matters in practice: if you forget the relations() block, db.query.users.findFirst({ with: { posts: true } }) will fail at compile time (TypeScript won’t know posts is a valid relation) or at runtime with a clear error, but the foreign key constraint in the database is unaffected either way. Conversely, if you define a relations() block for two tables that don’t actually share a foreign key, Drizzle will happily build a query using whatever fields/references mapping you hand-typed — it does not verify the relationship exists in the database schema. Keep the two in sync deliberately; nothing enforces it for you.

---

Relational Queries

import { db } from './db'
import { users, posts } from './schema'

// Fetch user with all their posts
const userWithPosts = await db.query.users.findFirst({
  where: (users, { eq }) => eq(users.id, userId),
  with: {
    posts: {
      where: (posts, { eq }) => eq(posts.status, 'published'),
      orderBy: (posts, { desc }) => [desc(posts.publishedAt)],
      limit: 10,
      columns: {
        content: false,  // exclude large field
      },
      with: {
        postTags: {
          with: { tag: true },
        },
      },
    },
  },
})

// Result is fully typed:
// userWithPosts.posts[0].postTags[0].tag.name

// Fetch all published posts with author info
const publishedPosts = await db.query.posts.findMany({
  where: (posts, { eq, isNull }) =>
    and(eq(posts.status, 'published'), isNull(posts.deletedAt)),
  orderBy: (posts, { desc }) => [desc(posts.publishedAt)],
  with: {
    author: {
      columns: { id: true, name: true, email: true },
    },
    postTags: {
      with: { tag: true },
    },
  },
})

with (relational queries) vs. manual leftJoin — when each one is the right call

Both db.query.*.findMany({ with: {...} }) and db.select().from().leftJoin() end up talking to the same database, but they generate very different SQL and it’s worth understanding the difference before defaulting to one everywhere.

The relational query API (db.query) issues one query per relation level under the hood on most drivers, not a single flat join — Drizzle fetches the top-level rows, then issues follow-up queries (often using WHERE id IN (...)) for each nested with, and stitches the results back into the typed, nested object you get back. This is why userWithPosts.posts[0].postTags[0].tag.name comes back correctly typed and de-duplicated even though a naive SQL join of users → posts → postTags → tags would produce a flattened, repeated row for every tag on every post. For the common case — “give me this user and their recent posts and each post’s tags” — this is exactly what you want: no manual grouping logic, no accidentally duplicated user.name values from a join fan-out, and full type inference derived from your schema.

The trade-off is query count and the loss of fine-grained control over the generated SQL. A deeply nested with (three or four levels, each filtered and limited) can turn into four or five round-trips to the database instead of one. For most CRUD-shaped API endpoints that’s a non-issue — the round-trips are cheap relative to network latency to the client — but it stops being free once you’re building a report that aggregates across tens of thousands of rows, or a query where you need a single GROUP BY with a computed aggregate (a post’s tag count, a rolling sum) that the relational API has no vocabulary for.

That’s the case for reaching for db.select().from(posts).leftJoin(postTags, eq(postTags.postId, posts.id)).groupBy(posts.id) instead: full control over exactly one SQL statement, access to groupBy, having, window functions via sql\…`, and predictable single-round-trip cost — at the price of writing the row-flattening/grouping logic yourself and losing the automatic nested typing. A reasonable rule of thumb: default to db.query` for read paths that map directly to a UI view (a user’s dashboard, a post detail page with its tags and author), and drop to the query builder the moment you need an aggregate, a computed column, or you’ve measured that the extra round-trips are showing up in a latency budget.

---

Transactions

A transaction’s job is to make a group of statements behave as a single atomic unit, but atomicity is only half the story — the other half is isolation: what a transaction is allowed to see of other transactions running concurrently against the same rows. Drizzle doesn’t invent its own isolation semantics; it passes the level straight through to the underlying database, so the behavior you get is whatever Postgres (or MySQL/SQLite) implements for that level. It’s worth knowing the four ANSI SQL levels concretely, because picking the wrong one is a bug that only shows up under concurrent load, which means it rarely reproduces in local development or in a test suite that runs requests sequentially.

Isolation level	Dirty read	Non-repeatable read	Phantom read	Typical use
read uncommitted	Possible (MySQL only; Postgres treats this as read committed)	Possible	Possible	Rarely used deliberately
read committed (Postgres default)	Prevented	Possible	Possible	General CRUD — each statement sees a fresh snapshot
repeatable read	Prevented	Prevented	Prevented in Postgres (MVCC), possible in some other engines	Reports, multi-step reads that must stay consistent
serializable	Prevented	Prevented	Prevented	Financial balances, inventory counts, anything where two concurrent transactions must not both “win”

For most CRUD endpoints, the Postgres default of read committed is the right choice and you don’t need to think about it further — each statement inside the transaction sees the latest committed data, which matches how most application code is written. The case where you need to override it is when a transaction reads a value, makes a decision based on it, and writes a result back, and two concurrent transactions doing this could both read the same starting value and produce an inconsistent result — the classic example is decrementing inventory or a wallet balance. serializable (or, at minimum, an explicit row lock with SELECT ... FOR UPDATE) forces the database to detect that conflict and abort one of the two transactions with a serialization failure, which your code then has to catch and retry. Drizzle exposes this through the transaction options:

await db.transaction(
  async (tx) => {
    const [account] = await tx.select().from(accounts).where(eq(accounts.id, accountId))
    if (account.balance < amount) throw new Error('Insufficient balance')
    await tx.update(accounts).set({ balance: account.balance - amount }).where(eq(accounts.id, accountId))
  },
  { isolationLevel: 'serializable' }
)
// On a Postgres serialization failure (error code 40001), retry the whole
// transaction from the top — the transaction itself did not partially apply.

The retry is not optional decoration — under serializable, Postgres is expected to abort transactions that would otherwise violate serializability, and the correct response to that specific error code is to re-run the transaction function, not to treat it as a real failure. A simple exponential-backoff retry wrapper around db.transaction() for error code 40001 (or 40P01 for a plain deadlock) handles this in most apps without needing a dedicated retry library.

import { db } from './db'
import { users, posts } from './schema'

// Basic transaction
async function createPostWithTags(
  authorId: string,
  postData: { title: string; content: string; slug: string },
  tagIds: number[]
) {
  return await db.transaction(async (tx) => {
    // Create post
    const [post] = await tx.insert(posts)
      .values({
        authorId,
        title: postData.title,
        content: postData.content,
        slug: postData.slug,
      })
      .returning()

    // Insert tag associations
    if (tagIds.length > 0) {
      await tx.insert(postTags)
        .values(tagIds.map(tagId => ({ postId: post.id, tagId })))
    }

    // Update user's post count in metadata
    await tx.execute(sql`
      UPDATE users
      SET metadata = jsonb_set(
        COALESCE(metadata, '{}'),
        '{postCount}',
        (COALESCE((metadata->>'postCount')::int, 0) + 1)::text::jsonb
      )
      WHERE id = ${authorId}
    `)

    return post
  })
  // If any step throws, all changes are rolled back
}

// Nested transactions (savepoints)
async function complexOperation() {
  return await db.transaction(async (tx) => {
    await tx.insert(users).values({ email: '[email protected]', name: 'Alice', passwordHash: '...' })

    // Nested transaction uses savepoint
    await tx.transaction(async (innerTx) => {
      try {
        await innerTx.insert(posts).values({ /* ... */ })
      } catch {
        // Only rolls back the inner transaction (savepoint)
        // Outer transaction continues
      }
    })

    return 'done'
  })
}

Two things about the code above are easy to skim past and both matter in production. First, tx.transaction() inside an already-open transaction doesn’t open a second, independent database transaction — Postgres doesn’t support truly nested transactions — it issues a SAVEPOINT and, on error, a ROLLBACK TO SAVEPOINT. That’s why the comment says “only rolls back the inner transaction”: the outer transaction’s connection and snapshot are untouched, so alice’s insert from the outer scope survives even if the inner post insert fails. This is genuinely useful for “best-effort” sub-steps — send a welcome email record, log an audit row — where you want the main operation to succeed even if a secondary write fails.

Second, and this is the actual pitfall: an empty catch {} block like the one shown silently discards the error. That’s fine for a true best-effort side effect, but it is a common source of “the data looks wrong and there’s no error in the logs” bugs when someone copies this pattern for a step that was supposed to be required. Before catching an error at a savepoint boundary, be explicit about whether failure there is genuinely recoverable; if it isn’t, let it propagate so the outer transaction rolls back too, and at minimum log what was swallowed (catch (err) { logger.warn('non-critical write failed', err) }) rather than catching bare.

One more practical note: db.transaction() holds a database connection for its entire duration, including the time your application code spends between statements (an await fetch() to a third-party API, for example). A transaction that does I/O unrelated to the database — calling an external payment API, waiting on a queue — will hold that connection open the whole time, which under load can exhaust your pool far faster than the query volume alone would suggest. Keep transactions to database work only; do the external call before or after, not inside.

---

Prepared Statements

import { db } from './db'
import { users, posts } from './schema'
import { eq, and, isNull, placeholder } from 'drizzle-orm'

// Prepare once, execute many times
const getPublishedPostById = db
  .select()
  .from(posts)
  .where(and(
    eq(posts.id, placeholder('id')),
    eq(posts.status, 'published')
  ))
  .prepare('get_published_post')

// Execute (no parse/plan overhead)
const post = await getPublishedPostById.execute({ id: 'some-uuid' })

// Prepared insert
const insertUser = db
  .insert(users)
  .values({
    email: placeholder('email'),
    name: placeholder('name'),
    passwordHash: placeholder('passwordHash'),
  })
  .returning({ id: users.id, email: users.email })
  .prepare('insert_user')

const newUser = await insertUser.execute({
  email: '[email protected]',
  name: 'Bob',
  passwordHash: await hashPassword('secret'),
})

Prepared statements skip query planning on repeat executions by asking the database to parse and plan the SQL once, then bind fresh parameter values on each subsequent call. The saving is real but usually small in absolute terms — a few hundred microseconds to a couple of milliseconds per call, depending on query complexity — so it’s not something to reach for by default. It matters on paths that run the same shape of query at very high frequency: an API endpoint doing thousands of requests per second against a single-row lookup, or a batch job re-running the same query in a tight loop.

The gotcha that catches teams by surprise in production is connection pooling middleware. Prepared statements are tied to a specific database connection/session — PREPARE on connection A is not visible on connection B. Many hosted Postgres setups put PgBouncer (or an equivalent pooler) in front of the database in transaction pooling mode, which hands out a physical connection per transaction and can reuse that same physical connection for a completely different client’s next transaction. Under transaction pooling, a prepared statement created on one logical connection may not exist — or worse, may collide with a statement of the same name prepared by a different client — on the physical connection the pooler hands back. The practical fix is to either avoid server-side prepared statements when running behind PgBouncer in transaction mode (session mode doesn’t have this problem), or use a driver/pooler combination that’s explicitly documented as compatible — the serverless Postgres providers (Neon, Supabase’s pooler) each document their own guidance here, and it’s worth checking before assuming .prepare() behaves the same way it does against a direct connection.

---

Soft Deletes

import { isNull, and } from 'drizzle-orm'

// Soft delete
async function deleteUser(userId: string) {
  await db.update(users)
    .set({ deletedAt: new Date() })
    .where(eq(users.id, userId))
}

// Always filter soft-deleted records
const activeUsers = db.$with('active_users').as(
  db.select().from(users).where(isNull(users.deletedAt))
)

// Use in queries
const result = await db.with(activeUsers)
  .select()
  .from(activeUsers)
  .where(eq(activeUsers.role, 'admin'))

// Helper to always exclude soft-deleted
function withoutDeleted<T extends typeof users>(table: T) {
  return db.select().from(table).where(isNull(table.deletedAt))
}

const aliveUsers = await withoutDeleted(users)

// Restore
async function restoreUser(userId: string) {
  await db.update(users)
    .set({ deletedAt: null })
    .where(eq(users.id, userId))
}

Soft deletes look simple until they interact with the rest of the schema, and the two problems that come up most often are unique constraints and cascades. A plain unique() index on email doesn’t know about deletedAt — once a user soft-deletes their account, no one else can ever register with that email again, because the row (and its unique constraint) is still there. The usual fix is a partial unique index that only applies to non-deleted rows: in Postgres, uniqueIndex('users_email_idx').on(t.email).where(sql\deleted_at IS NULL`)`. That lets a new user reuse an email address that a soft-deleted account previously held, while still preventing two active accounts from sharing one.

The second problem is that onDelete: 'cascade' in the posts table’s foreign key only fires on a real DELETE statement — a soft delete is an UPDATE, so cascading behavior has to be reimplemented in application code (or a trigger) if you want “soft-deleting a user also soft-deletes their posts.” This is easy to forget, and the failure mode is quiet: the user disappears from user-facing lists, but their posts stay fully visible and queryable until someone notices. If soft-delete cascades matter for a given relationship, write and test the cascade explicitly rather than assuming the database’s ON DELETE behavior covers it.

The withoutDeleted() helper and the $with() CTE shown above both exist to solve the same underlying risk: it is very easy to write a new query somewhere in a large codebase that forgets the isNull(deletedAt) filter, and the bug that results — a soft-deleted user showing up in search results, a deleted post appearing in an RSS feed — is the kind that only gets noticed by an end user, not by a test. Centralizing the filter in one helper (or, in Postgres, a view: CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL) turns “did the author of this query remember to filter” into “did the author use the helper,” which is much easier to enforce in code review.

---

Custom Column Types

import { customType } from 'drizzle-orm/pg-core'

// Custom type for encrypted values
const encryptedText = customType<{
  data: string
  driverData: string
}>({
  dataType() { return 'text' },
  toDriver(value: string): string {
    return encrypt(value)
  },
  fromDriver(value: string): string {
    return decrypt(value)
  },
})

// Custom type for money (avoid float precision issues)
const money = customType<{ data: number; driverData: string }>({
  dataType() { return 'numeric(12,2)' },
  toDriver(value: number): string { return value.toString() },
  fromDriver(value: string): number { return parseFloat(value) },
})

// Use in schema
export const payments = pgTable('payments', {
  id: uuid('id').defaultRandom().primaryKey(),
  amount: money('amount').notNull(),
  cardNumber: encryptedText('card_number').notNull(),  // stored encrypted
})

customType is Drizzle’s escape hatch for columns where the wire format and the application format need to diverge, and the two examples above illustrate two different reasons to use it. The money type exists because JavaScript’s number is an IEEE-754 double, which cannot represent most decimal fractions exactly — 0.1 + 0.2 famously doesn’t equal 0.3. Storing currency as a float and doing arithmetic on it in application code eventually produces off-by-a-cent totals that are hard to trace back to their source. Mapping to Postgres’s numeric(12,2) and doing arithmetic with an arbitrary-precision decimal library (or keeping the value as an integer count of cents, which sidesteps the issue entirely) avoids the class of bug altogether; the customType here just handles the string-to-number conversion at the boundary.

The encryptedText type is a different trade-off, and it’s worth being deliberate about it rather than reaching for it on every sensitive-looking column. Encrypting at the ORM layer means the plaintext never touches application logs or a SELECT * in a database GUI by accident, which is a real security win — but it also means the database can no longer index, sort, filter, or do a case-insensitive LIKE search on that column, because all it sees is ciphertext. WHERE card_number = $1 still works if you encrypt the search parameter identically before comparing (assuming a deterministic cipher mode), but WHERE name ILIKE '%smith%' does not, and can’t, without either decrypting every row to compare in application code (defeats the purpose at any scale) or using specialized searchable-encryption schemes that are well outside what a customType wrapper provides. Reserve this pattern for genuinely sensitive fields you don’t need to query flexibly — card numbers, SSNs, API secrets — and keep it off columns like name or email where search and sort are part of normal product functionality.

---

Migrations

# Generate migration from schema changes
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push schema directly (dev only — no migration file)
npx drizzle-kit push

# View current schema state
npx drizzle-kit studio

drizzle-kit generate diffs your TypeScript schema against the last known migration state and writes the resulting SQL to a new file under ./drizzle. It is a diffing tool, not a magician — it cannot infer intent, only structural difference — so every generated file needs a human read-through before it ships, not just a glance at the filename. Two situations in particular deserve extra attention because generate will happily produce SQL that is syntactically correct but operationally dangerous on a table with real traffic:

• A NOT NULL column added without a default. On Postgres, ALTER TABLE posts ADD COLUMN featured boolean NOT NULL on a table with existing rows requires the database to go back and validate every existing row against the new constraint. Depending on Postgres version and table size, this can take an ACCESS EXCLUSIVE lock for the duration of that scan — which blocks every read and write against the table until it finishes. On a table with a few hundred rows this is invisible; on a table with tens of millions it can stall production traffic for minutes. The safe pattern is the classic “expand/contract”: add the column as nullable first, backfill it in batches from application code or a background job, then add the NOT NULL constraint as a separate migration once every row is confirmed populated (Postgres 12+ can validate a NOT NULL via a CHECK constraint added NOT VALID and then VALIDATE CONSTRAINT’d separately, which avoids the long lock).
• Adding an index on a large table. A plain CREATE INDEX takes a lock that blocks writes to the table for as long as the index build takes. CREATE INDEX CONCURRENTLY avoids that lock at the cost of taking longer and not being usable inside a transaction block — which means it has to be run outside of drizzle-kit migrate’s normal transactional wrapper. If a generated migration includes a new index on a table you know is large in production, pull that statement into its own migration file and run it with CONCURRENTLY by hand rather than letting the default tooling wrap it in a transaction.

A smaller but common trap: renaming a column shows up to drizzle-kit generate as a DROP COLUMN followed by an ADD COLUMN, not a rename — because the tool is comparing schema shape, not tracking identity across edits. Applied naively, that migration drops real data. drizzle-kit generate will prompt interactively when it detects a rename is more likely than a drop when run against an editable terminal, but in a non-interactive CI pipeline that prompt can’t be answered and the tool may fall back to the destructive interpretation — always run generate locally first, review the SQL file it produces, and commit the reviewed file rather than regenerating in CI.

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

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,  // require confirmation for destructive migrations
})

Programmatic migrations (for testing)

import { migrate } from 'drizzle-orm/node-postgres/migrator'
import { db } from './db'

// Run migrations programmatically
await migrate(db, { migrationsFolder: './drizzle' })

The strict: true option in drizzle.config.ts is worth keeping on deliberately rather than turning off when it becomes annoying: it makes drizzle-kit pause for confirmation before generating a migration it classifies as destructive (a dropped column, a dropped table, a type change that could lose precision). In an automated pipeline that confirmation has nowhere to go, so strict mode is really a signal that a migration needs a human to run generate locally, read the diff, and commit the file — not something CI should be generating and applying in the same step. A reasonable production setup separates these two steps entirely: drizzle-kit generate runs locally (or in a PR-triggered job that fails if the committed migrations directory would change), and drizzle-kit migrate — applying already-reviewed, already-committed SQL files — runs as a deploy step, ideally before the new application code that depends on the schema change goes live. Running migrate against a database that’s still serving the previous version of the app is the reason expand/contract matters: the previous app version needs to keep working against the new schema for the brief window where old and new instances are both running during a rolling deploy, which is why “add nullable column, deploy code that writes it, backfill, add constraint, deploy code that requires it” is four separate steps instead of one.

---

Performance Patterns

// Select only needed columns
const names = await db.select({ id: users.id, name: users.name }).from(users)

// Batch inserts (single round-trip)
await db.insert(posts).values([post1, post2, post3, post4])

// Upsert
await db.insert(users)
  .values({ email: '[email protected]', name: 'Alice', passwordHash: '...' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Alice Updated', updatedAt: new Date() },
  })

// Count without fetching all rows
const [{ count }] = await db
  .select({ count: sql<number>`count(*)::int` })
  .from(posts)
  .where(eq(posts.status, 'published'))

// Use indexes — ensure your WHERE columns are indexed
// Check with: EXPLAIN ANALYZE SELECT ...

// Pagination (cursor-based is faster than offset for large tables)
const cursor = lastPost?.createdAt ?? new Date()
const nextPage = await db.select().from(posts)
  .where(lt(posts.createdAt, cursor))
  .orderBy(desc(posts.createdAt))
  .limit(20)

Offset-based pagination (LIMIT 20 OFFSET 4000) forces Postgres to scan and discard every row before the offset on each request, so page 200 gets progressively slower even though it returns the same number of rows as page 1. Cursor-based pagination avoids that by turning “skip N rows” into “rows after this indexed value,” which an index on createdAt can satisfy directly regardless of how deep into the result set the cursor is — the query cost stays roughly constant from page 1 to page 2000. The trade-off is that cursor pagination doesn’t support jumping to an arbitrary page number (“go to page 47”) the way offset pagination does, since there’s no cheap way to compute the cursor value for a page you haven’t scrolled to yet — it’s the right fit for infinite-scroll and “next page” UIs, not numbered pagination widgets.

Connection pooling and driver adapters

Drizzle itself doesn’t manage connections — it wraps whichever driver you hand it (node-postgres’s Pool, postgres.js, the Neon serverless driver, mysql2, and others), and the pooling behavior you get is entirely the driver’s. This is the detail that trips up a lot of Drizzle deployments moving from a traditional server to a serverless or edge target, because the two environments need opposite pooling strategies.

// Traditional long-lived server (node-postgres)
import { Pool } from 'pg'
import { drizzle } from 'drizzle-orm/node-postgres'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,          // total connections this process will hold open
  idleTimeoutMillis: 30_000,
})
export const db = drizzle(pool)
// One Pool, reused for the lifetime of the process — size `max` against
// (Postgres max_connections) / (number of app instances you run).

// Serverless / edge (Neon HTTP driver — no persistent socket)
import { neon } from '@neondatabase/serverless'
import { drizzle } from 'drizzle-orm/neon-http'

const sql = neon(process.env.DATABASE_URL!)
export const db = drizzle(sql)
// Each query is its own HTTP request; there is no long-lived pool to size,
// which is exactly what avoids exhausting Postgres connections when
// hundreds of function instances can spin up concurrently.

A node-postgres Pool is built for a process that stays alive: it opens a handful of real TCP connections once and reuses them across requests, so max should be sized against how many application instances will run concurrently, not against expected request volume — max: 10 across 20 autoscaled instances is 200 real Postgres connections, which is enough to hit the default max_connections limit (100) on a modestly sized managed Postgres instance by itself, before counting any other service talking to the same database. That’s the scenario that produces the classic “works fine in staging, falls over the moment traffic scales in production” incident: connection counts scale with instance count, not with the pool size setting alone, and nothing in the Pool constructor warns you when the aggregate is approaching the server’s limit.

Serverless functions invert the problem: each invocation can be a fresh process with no memory of a previous pool, so opening a traditional TCP-based pool per invocation and never reusing it defeats the purpose of pooling and can exhaust connections even faster, since a burst of concurrent invocations each opens its own connections simultaneously. This is why serverless-targeted drivers (Neon’s HTTP/WebSocket driver, PlanetScale’s HTTP driver for MySQL) exist as separate Drizzle adapters (drizzle-orm/neon-http, drizzle-orm/neon-serverless) rather than reusing node-postgres — they either issue each query as a stateless HTTP call or multiplex over a shared edge-side connection pool that the provider manages, so a spike of function invocations doesn’t translate 1:1 into a spike of Postgres backend connections. If a project is deployed to Cloudflare Workers, Vercel Edge Functions, or similar, sizing a pg.Pool is close to always the wrong approach — reach for the provider’s dedicated serverless/edge driver instead, and reserve node-postgres/postgres.js pools for deployments where the process genuinely stays alive between requests (a traditional Node server, a long-running container, a worker process).

For deployments that sit in between — a containerized Node app behind a managed pooler like PgBouncer — the earlier prepared-statement caveat and the pool-sizing math both apply simultaneously: size the app’s own Pool conservatively (PgBouncer is already doing connection multiplexing on the database side), and confirm the pooler’s mode before assuming .prepare() behaves as documented.

---

Frequently Asked Questions (FAQ)

Q. Should I default to serializable isolation for every transaction to be safe?

A. No — serializable forces Postgres to detect conflicting concurrent transactions and abort one of them, which means your application code has to be ready to retry, and higher-contention tables will see more of those retries under load. Use read committed (the Postgres default) for ordinary CRUD, and reach for serializable (or an explicit SELECT ... FOR UPDATE row lock) specifically for the read-modify-write sequences where two concurrent transactions reading the same starting value would produce an inconsistent result — balances, inventory counts, and similar invariants.

Q. Where can I verify the exact behavior described here?

A. Drizzle’s own documentation (orm.drizzle.team) and your database’s documentation are the sources of truth for driver- and version-specific behavior — isolation level names, customType signatures, and pooling driver options have changed across Drizzle releases, so cross-check against the version pinned in your package.json before relying on a specific API shape.

---

Related Articles

• Drizzle ORM Basics
• Prisma ORM in TypeScript
• What TypeScript 5 Changed

---