Drizzle or raw SQL: picking a data layer

The argument gets framed as types versus control, which is why it never resolves. Both sides are describing real experiences of different parts of the same codebase.

Here is the framing I find more useful. A data layer has two populations of queries, and they want opposite things.

Two populations

The first is large and dull. Fetch a row by id. Insert a record. Update three fields. Paginate a list with two filters. These are numerous, nearly identical, and they change shape every time somebody adds a column.

The second is small and sharp. The dashboard query with three CTEs and a window function. The bulk upsert with conflict handling. The report that needs an index hint and an EXPLAIN plan read twice before anyone trusts it.

A query builder is excellent at the first and merely adequate at the second. Raw SQL is the reverse. Teams that pick one for everything spend the rest of the project paying for the half it fits badly, and they usually blame the tool.

The case for a builder

With Drizzle the schema is TypeScript and the types fall out of it.

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

export const lead = pgTable("lead", {
  id:        bigserial("id", { mode: "number" }).primaryKey(),
  source:    varchar("source", { length: 32 }).notNull(),
  sourceRef: varchar("source_ref", { length: 64 }).notNull(),
  payload:   jsonb("payload").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

export type Lead = typeof lead.$inferSelect;
export type NewLead = typeof lead.$inferInsert;

const rows = await db
  .select({ id: lead.id, source: lead.source })
  .from(lead)
  .where(eq(lead.source, "web"))
  .limit(20);
// rows: { id: number; source: string }[] — inferred, not declared

Three things there are worth more than they look.

Renaming a column breaks the build, rather than producing a 500 at 2am from one forgotten string in a file nobody has opened this year. That is the strongest argument for a typed layer and it gets stronger with every person who joins the team.

The selected shape is the returned type. Select two columns and the type has two fields, with no hand-written interface drifting quietly away from the table it claims to describe.

And there is no string building, which matters because conditional filters are both the classic injection surface and the classic source of a stray AND. Composed predicates make that whole category structural instead of a thing you have to be careful about.

The other side of it

A second dialect to know. You still need SQL, plus how this particular library expresses SQL, and when the generated query is wrong you are debugging in two languages at once.

Awkwardness at the edges. Window functions, recursive CTEs, DISTINCT ON, full-text ranking. All expressible, and the builder version is usually longer and harder to read than the SQL it generates.

And an API that makes N+1 comfortable. Fetching related rows in a loop reads perfectly naturally in TypeScript and is a disaster against the database. The builder did not cause that, it just declined to make it look as expensive as it is.

When to write the SQL

Drop down when the query needs the database's own vocabulary. Keep it parameterised and give it a name.

export async function leadsByDay(from: Date, to: Date) {
  const { rows } = await db.execute<{ day: string; total: number; running: number }>(sql`
    SELECT date_trunc('day', created_at)::date AS day,
           count(*)                            AS total,
           sum(count(*)) OVER (ORDER BY date_trunc('day', created_at)) AS running
      FROM lead
     WHERE created_at >= ${from} AND created_at < ${to}
     GROUP BY 1
     ORDER BY 1
  `);
  return rows;
}

That function is honest about three things at once. It is SQL. The parameters are bound rather than interpolated. And the result type is stated explicitly, because nothing can infer it.

It also lives in the data layer beside the builder queries, not scattered through route handlers, which is the part people skip.

The rule I actually use: if I expect to read an EXPLAIN plan for it, I write it as SQL. Tuning a query you did not write is guesswork, and the plan is the only thing that settles it.

Migrations are the part that bites

Whatever you choose for querying, generated migrations deserve more suspicion than they get.

Generate the SQL, then read it before it runs. A schema change that looks like a rename in TypeScript can come out the other side as a drop and an add, which is a column of production data gone and present in nobody's test database.

Check the generated file into the repository, review it in the pull request like any other code, and apply it with the same ceremony as a deploy. That a tool wrote it is not a reason to trust it more than code a person wrote.

Pick by population

The builder for the hundred boring queries. SQL for the handful that need the database to actually be a database.

That hybrid is not indecision, though it gets called that. It is the arrangement that gives you compile-time safety exactly where schema drift hurts, and full expressiveness exactly where the alternative is fighting an abstraction to produce a query you could have typed in a minute.