Articles

Drizzle ORM Interview Questions: SQL Boundaries, Transactions, and Migrations

Prepare Drizzle ORM interview answers on SQL parameters, runtime decoding, transaction handles, savepoints, and reviewed migration deployment plans.

Strong Drizzle ORM interview answers show the generated SQL, identify which values become parameters, and trace the connection executing each statement. TypeScript types make a query easier to reason about, but they do not validate incoming request data, convert every driver result, or make a schema change safe to deploy.

Evidence boundary: linked Drizzle documentation establishes official API behavior. SQL traces, rollback ledgers, and rollout recommendations below are original interview exercises and inference. They are not candidate reports or claims about an employer's question frequency. Examples use PostgreSQL-style SQL; driver and transport support must be checked separately.

Where is the SQL boundary in a typed query?

Question: “If Drizzle accepts this TypeScript expression, can a request value change the SQL structure?”

Official fact: Drizzle's SQL template documentation explains parameterization of interpolated values and distinguishes that behavior from raw SQL construction. Its generic type annotations do not perform runtime casts.

Original exercise: assume a users schema containing id, email, and createdAt is already defined, and the request email has passed application validation.

const rows = await db
  .select({ id: users.id })
  .from(users)
  .where(eq(users.email, requestedEmail));

The structural shape is a select from the users table with an email equality predicate. The email should be represented as a bound value, not pasted into a SQL string.

An illustrative PostgreSQL form is:

SELECT "id" FROM "users" WHERE "users"."email" = $1;
-- parameters: [requestedEmail]

Exact quoting, qualification, and placeholders depend on the generated query and dialect. Inspect actual output rather than treating this illustrative string as a byte-for-byte Drizzle guarantee.

Inference: test with an email containing quotes and SQL-looking punctuation. The important assertion is that the statement structure stays the same and the input appears in the parameters. A test that only checks “the endpoint returns 200” does not establish that boundary.

Parameterization does not authorize access to the matching row. Tenant scope and permission predicates are separate requirements. A perfectly parameterized query can still expose another customer's data when it omits tenant_id.

Can a parameter stand in for a column name?

Question: “The client sends a sort field. Should we bind it as $1?”

A parameter represents a data value, not an arbitrary SQL identifier or ordering direction. Binding the string email is not equivalent to choosing the schema's email column for sorting.

Original safer pattern: translate a small accepted vocabulary into known schema objects.

const sortColumns = {
  email: users.email,
  created: users.createdAt,
};

if (!(requestedSort in sortColumns)) {
  throw new Error("unsupported sort field");
}
const column = sortColumns[requestedSort as keyof typeof sortColumns];
const rows = await db.select().from(users).orderBy(asc(column));

Inference: validate direction separately if the endpoint supports ascending and descending order. Do not pass a request fragment directly to sql.raw. Raw construction deliberately crosses a different trust boundary from bound values.

The question also has a semantic edge: what happens when many rows share the selected sort value? Add a stable tie-breaker such as a unique ID when pagination requires deterministic ordering. Escaping an identifier prevents one class of structural problem; it does not define a stable page or make the field appropriate for this endpoint.

Does `sql<number>` return a JavaScript number?

Question: “The selected count is typed as number, but production returned a string. Is that a compiler bug?”

No. A compile-time assertion and a runtime conversion are different operations. Official fact: the SQL documentation describes type hints separately from .mapWith(), which supplies runtime result mapping.

Original example:

const rows = await db.select({
  total: sql<number>`count(*)`.mapWith(Number),
}).from(users);

The generic informs TypeScript about the intended type. The mapper requests a runtime conversion. Whether that conversion is safe depends on the database result and the precision required.

Inference: Number("9007199254740993") cannot represent that integer exactly as a JavaScript safe integer. A conversion that removes the string type can still lose information. For potentially large counts or identifiers, choose a representation deliberately and test boundary values.

Likewise, a date type in a schema does not validate an arbitrary JSON string arriving at an HTTP endpoint. Validate request payloads before query construction and validate domain-sensitive returned values where necessary. Keep three questions separate: what the compiler believes, what the driver returns, and what the business operation permits.

What actually participates in `db.transaction`?

Official fact: Drizzle's transaction guide supplies a transaction handle for the callback and documents nested savepoint APIs. Dialect-specific configuration options are part of the contract.

Original code-review exercise:

await db.transaction(async (tx) => {
  await tx.insert(orders).values({ id: 17, status: "pending" });
  await db.insert(auditEvents).values({ orderId: 17, kind: "created" });
  throw new Error("abort");
});

The order insert uses tx; the audit insert uses the outer db. The second statement is not automatically captured because it appears between the callback's braces.

Inference: with a driver and pool capable of using another connection, an independently committed audit row could remain after the order rolls back. Constraints, locking, or a single-connection driver can instead make that statement wait or fail. The general defect is incorrect enrollment, not one guaranteed final state for every driver.

A repair uses tx for both statements and passes that handle into helpers. Do not let a helper accept tx while internally importing a global db. Read the actual query path, not just the function signature.

Also await the work before the callback returns. A fire-and-forget promise can outlive the transaction. Sending an external notification from the callback does not make the notification reversible if the database later rolls back. An outbox pattern can represent the durable obligation, with delivery and duplicate handling outside the transaction.

Walk through a nested rollback

Question: “If the inner transaction fails, must everything roll back?”

Original savepoint exercise: assume the chosen driver supports the documented nested transaction behavior. Start with an existing account. The outer transaction changes its display label. The inner scope attempts an optional preference update and throws. The application catches that inner failure in the outer scope and then writes a recovery audit row through the outer handle.

Operation	Scope	State if outer transaction commits
Change account label	Outer transaction	Retained
Change optional preference	Inner savepoint	Reverted after inner failure
Write recovery audit	Outer transaction	Retained

If the error instead propagates out of the outer callback, the outer transaction also fails and its changes roll back. The location of the catch changes the intended behavior. Catching an error is therefore a domain decision: is the preference genuinely optional, or is the whole operation invalid without it?

Inference: savepoint release is not an independent durable commit. If the outer transaction later fails, previously successful inner work does not survive merely because the inner callback finished.

Do not catch every database error and continue blindly. Some errors can leave an enclosing transaction unusable unless the relevant savepoint rollback and driver behavior are handled correctly. Verify the chosen driver's behavior with a real integration fixture rather than substituting a mocked promise rejection.

Are transactions identical across serverless drivers?

Question: “This works through a local PostgreSQL connection. Can we keep the same interactive callback on every HTTP database adapter?”

Official fact: Drizzle's Neon connection guide distinguishes HTTP and WebSocket-based connection paths. Transport capabilities affect interactive transaction support.

Inference: a stateless HTTP query endpoint, a batched transaction API, and an interactive connection with read-dependent branching are different interfaces. A batch of predefined statements does not necessarily support reading a result and then choosing a new statement inside the same transaction.

Before porting code, identify the adapter, SDK version, deployment runtime, pooling behavior, and supported transaction API. If the adapter cannot provide the required interaction, change the connection approach or redesign the operation within supported semantics.

A test should force an error after the first mutation and verify final database state. Merely proving two successful statements ran says little about atomic rollback. If the test uses a different adapter from production, document that gap rather than claiming driver equivalence.

How do generate, migrate, and push differ?

Official fact: Drizzle's migration overview describes multiple workflows. generate produces migration artifacts from schema changes; migrate applies pending migrations. push compares the declared schema with the database and applies changes directly.

Original interview contrast: a local prototype may favor quick schema iteration. A production rollout with review, multiple application versions, and recovery requirements benefits from versioned, inspected migration artifacts. This is a recommendation about operational reviewability, not a claim that one command is universally forbidden.

Generated SQL is evidence of what the tool inferred, not proof of what the developer intended. A renamed field can be mistaken for a removal and addition depending on the change and choices made during generation. Read the artifact and its data consequences before execution.

The migration history answers which recorded steps ran. It does not prove that nobody manually altered the schema afterward. When investigating drift, compare the actual schema, the declared schema, and the applied migration history rather than trusting any one alone.

Design a migration that old writers can survive

Original exercise: introduce a required normalized_email field on an actively used table. Immediately adding a non-null column with no compatible population path can break existing writers or historical rows.

A reviewable plan has distinct phases:

Add the field in a form old code can tolerate.
Define how concurrent old and new writers populate or maintain it.
Backfill existing rows in bounded, resumable batches.
Reconcile nulls, duplicates, and normalization collisions.
Add the required constraint after the data and writer paths support it.
Remove compatibility behavior only after old writers are retired.

Inference: lowercasing is an example transformation, not a universal email identity rule. Before creating a unique constraint, decide the application's normalization semantics and how collisions are resolved. Two distinct stored addresses might map to the same new value.

The backfill needs a checkpoint and a rule for records changed during processing. A job that blindly copies an old snapshot over a newer write can finish with every row populated and still be wrong. Verify counts and content reconciliation, not only that the worker reached its final ID.

Finally, inspect database locking and index-build restrictions. A migration transaction does not make every DDL operation nonblocking or reversible. Rehearse the actual statement on realistic data and preserve an application rollback path while the expanded schema remains compatible.