Part 2 · 2 chapters · ~12 min

Query Building

From fluent API to AST to SQL, dialect compilers and where abstractions leak, parameter binding and injection, relation loading and hydration (the expensive part nobody profiles), type mapping for dates, decimals, JSON, enums and arrays, and raw SQL escape hatches.

The pipeline

code

// Kysely: the AST is visible and the SQL is predictable
const q = db.selectFrom('transfers').innerJoin('accounts', 'accounts.id', 'transfers.account_id')
  .select(['transfers.id', 'transfers.amount_kobo', 'accounts.currency'])
  .where('accounts.owner_id', '=', ownerId).orderBy('transfers.created_at', 'desc').limit(50);
q.compile();   // { sql: 'select "transfers"."id", ... where "accounts"."owner_id" = $1 ... limit $2', parameters: [7, 50] }

// injection through an escape hatch: never interpolate user input into raw SQL
await prisma.$queryRawUnsafe(`SELECT * FROM accounts WHERE email = '${email}'`);   // vulnerable
await prisma.$queryRaw`SELECT * FROM accounts WHERE email = ${email}`;             // tagged template: parameterised

FROM A FLUENT CALL TO SQL

every ORM and query builder follows this pipeline

swipe the figure sideways, or tap expand for full screen

1/4

the AST

Fluent calls build an abstract syntax tree of the query, not a string. That is what lets builders compose, inspect and type-check queries.

calls build a tree, not a stringcomposable and checkable

Type mapping

type	pitfall	practice
bigint	JS drivers return strings (or lose precision as Number)	map to BigInt or string; never Number for ids above 2^53
numeric / decimal	returned as string or float	a decimal library; minor units in bigint for money
timestamp vs timestamptz	time zone shifts on read	timestamptz, UTC everywhere
JSON / JSONB	untyped blobs	validate on read (zod, Pydantic)
enums	database enums are hard to change	text with a check constraint, or lookup tables