Skip to main content

SQL query builder vs raw SQL in TypeScript

Raw SQL in TypeScript is a whole statement written as SQL text: a template beside application code, or a .sql file compiled to a typed call. A SQL query builder composes the statement through an API; Kysely also checks each operation against the database types and infers the result.

A statement that never changes is where raw SQL is strongest, and the builder still checks it; every statement that does change is the builder's. A team that composes raw SQL is already maintaining a query builder, a bespoke and untyped one; a team that writes typed SQL files has done without one until the first dynamic query. This page starts with the fixed statement, then follows one asset query through optional filters, a shared count, an export, and nested tags, and tries to break it.

Both kinds of raw SQL write the statement whole​

SQL code generators

Compile whole statements in SQL files to typed application calls.

SQL clients

Write SQL beside application code, with clients that bind values.

SQL query builders

  • KyselyChecked composition and inferred results
  • KnexComposition through an API
  • ZapatosTyped helpers over SQL templates

Compose clauses and expressions through an API that checks them.

SQL checkers

Check whole statements against the database.

Grouped by where the statement is written, not ranked by capability. Each tool adds something a SQL console does not accept: interpolation and fragments in templates, parameter syntax or macros in SQL files. PgTyped also accepts SQL in TypeScript.

SQL templates such as Postgres.js and Slonik live beside application code, bind values, and compose through fragments; SafeQL checks the statements it can see whole. Typed SQL files are whole statements in .sql files that a SQL code generator, PgTyped, Prisma TypedSQL, or sqlc, turns into typed calls. Knex and Zapatos sit with the builders in the figure and are not compared here: Knex composes without checking references against a schema, and Zapatos types shortcut helpers over SQL templates.

A whole statement still gains checked references in the builder​

The statement to test the builder against is the one raw SQL is best at: complete, fixed, and readable on its own. A report of each owner's unfiled favorites is one statement with a CTE and two joins:

with favorites as (
  select asset_tag.asset_id
  from asset_tag
  inner join tag on tag.id = asset_tag.tag_id
  where tag.name = 'favorite'
)
select asset.id, asset.name, asset.owner_id
from asset
inner join favorites on favorites.asset_id = asset.id
where asset.folder_id is null
order by asset.owner_id, asset.name;

It runs unchanged in a SQL console, under EXPLAIN ANALYZE, and in a review by someone who does not write TypeScript. In application code it is a string: nothing checks asset.owner_id against the schema, the result type is whatever the caller annotates, and a renamed column is found by search. The same report in Kysely:

const unfiledFavorites = await db
  .with('favorites', (qb) =>
    qb
      .selectFrom('asset_tag')
      .innerJoin('tag', 'tag.id', 'asset_tag.tag_id')
      .select('asset_tag.asset_id')
      .where('tag.name', '=', 'favorite'),
  )
  .selectFrom('asset')
  .innerJoin('favorites', 'favorites.asset_id', 'asset.id')
  .select(['asset.id', 'asset.name', 'asset.owner_id'])
  .where('asset.folder_id', 'is', null)
  .orderBy('asset.owner_id')
  .orderBy('asset.name')
  .execute()
// Inferred: { id: string; name: string; owner_id: string }[]

Raw SQL's best case is a fixed statement, and the builder still checks it. Every reference is completed by the editor as it is typed and checked against the database declarations, the result type is inferred from the selection, and renaming asset.name turns the report into a compile error. The join on favorites.asset_id is checked against what the CTE selected, so a column dropped from the CTE fails in the clause that reads it, and the caller annotates nothing.

The builder version reads as an API, not as a second language: with, selectFrom, innerJoin, select, where, and orderBy are SQL's clauses under SQL's names, with the same column references, so the skill the builder asks for is SQL. Where the API stops looking like SQL, at fn and the JSON helpers below, .compile() shows the statement and its parameters for the console and the reviewer, and .explain() runs it under EXPLAIN ANALYZE. A statement tuned in a console or handed over by a DBA is translated once, clause for clause, and every later edit to it is checked.

A fixed write is checked the same way. An insert that omits a required column is a valid statement until it runs: PostgreSQL prepares it without objection and rejects the row at execution. Kysely's values() accepts the table's columns only and requires each one the declaration marks neither generated nor nullable:

await db
  .insertInto('asset')
  .values({ id: 'asset-7', name: 'IMG_0001.jpg' }) // TypeScript error: owner_id is required
  .execute()

set() on an update holds the same line, and returning() infers its result like a selection. A SQL checker stops where PostgreSQL does, at a statement that prepares; the builder also requires the columns the row needs. A typed SQL file misses the same insert: the statement is valid, so the generator types it, and the row still fails.

The builder checks the fixed statement in the same API the next section composes with, so a fixed query and a dynamic one share one toolchain, one type source, and no generation step between a query edit and its type check. The declarations are generated when the schema changes. A typed SQL file checks the report too, with the database, and derives its type, though not the nullability a left join adds, as the export below shows. That check is a generation run after every query edit, against a live database for PgTyped and TypedSQL. Whole raw statements keep their place in migrations, bootstrap, and one-off scripts, where there is no composition to check. The three forms part at the first filter that applies only sometimes.

Every variation is a fragment, a file, or a checked operation​

An asset list starts as an owner's assets, sorted by name and ID and limited to 50; any of the three forms writes that in a few lines. Then the list needs an optional album filter, which adds a join and a predicate, and an optional name prefix.

Database types and request options for the following examples
interface Database {
  asset: {
    id: string
    name: string
    owner_id: string
    folder_id: string | null
  }
  folder: { id: string; name: string }
  album_asset: { album_id: string; asset_id: string }
  asset_tag: { asset_id: string; tag_id: string }
  tag: { id: string; name: string }
}
type AssetOptions = {
  ownerId: string
  albumId?: string
  includeTags: boolean
  namePrefix?: string
}

Assume a Kysely<Database> instance named db, a configured Postgres.js client named sql, one row per album/asset pair, and PostgreSQL. The prefix filter uses SQL LIKE-pattern semantics: % and _ in the input remain wildcards.

Add the optional join and filters​

Put the shared query in a function. Kysely's $call hands the builder to a function and returns its result, so each addition is a plain if; the join is applied inside the branch that needs it, and the references in that branch are checked against the joined table:

function matchingAssets(options: AssetOptions) {
  return db
    .selectFrom('asset')
    .where('asset.owner_id', '=', options.ownerId)
    .$call((qb) => {
      if (options.albumId !== undefined) {
        qb = qb
          .innerJoin('album_asset', 'album_asset.asset_id', 'asset.id')
          .where('album_asset.album_id', '=', options.albumId)
      }
      if (options.namePrefix !== undefined) {
        qb = qb.where('asset.name', 'like', options.namePrefix + '%')
      }
      return qb
    })
}

With raw SQL, the same rules live in fragment helpers. Postgres.js numbers the parameters; a pool.query string built by concatenation renumbers its $n placeholders by hand each time a filter moves:

const albumJoin = (options: AssetOptions) =>
  options.albumId !== undefined
    ? sql`inner join album_asset on album_asset.asset_id = asset.id`
    : sql``
const albumFilter = (options: AssetOptions) =>
  options.albumId !== undefined
    ? sql`and album_asset.album_id = ${options.albumId}`
    : sql``
const nameFilter = (options: AssetOptions) =>
  options.namePrefix !== undefined
    ? sql`and asset.name like ${options.namePrefix + '%'}`
    : sql``

function matchingAssetSql(options: AssetOptions) {
  return sql`
    from asset ${albumJoin(options)}
    where asset.owner_id = ${options.ownerId}
      ${albumFilter(options)} ${nameFilter(options)}
  `
}
const assets = await sql<{ id: string; name: string }[]>`
  select asset.id, asset.name ${matchingAssetSql(options)}
  order by asset.name, asset.id limit 50
`

The album join and its predicate are one branch in Kysely and two fragments that must agree here, because the join lands before where and the predicate after it, and one fragment cannot carry both. Three helpers that each decide when their text applies, and a function that places them in order, are a query builder: one the team maintains through tests, since no type records which fragment is present. $call is the typed form of the ternary and the empty template, the part of the raw version that was never SQL either.

The first conditional in a shared helper takes every statement that uses it out of the checker's coverage. SafeQL's Postgres.js integration lists conditional fragment selection as unsupported, so every statement assembled from these helpers is outside what it checks. Its maintainer explains the limit as combinatorial: each condition doubles the forms to check. Kysely checks the album branch once, inside its if.

A typed SQL file cannot add or remove clauses, so each optional filter becomes a nullable parameter guarded by is null or, and album membership becomes exists. With Prisma TypedSQL, parameters are typed by comments and the rest is plain SQL:

findAssets.sql
-- @param {String} $1:ownerId
-- @param {String} $2:albumId?
-- @param {String} $3:namePrefix?
select asset.id, asset.name
from asset
where asset.owner_id = $1
  and ($2::text is null or exists (
    select 1 from album_asset
    where album_asset.asset_id = asset.id
      and album_asset.album_id = $2
  ))
  and ($3::text is null or asset.name like $3 || '%')
order by asset.name, asset.id
limit 50;

PgTyped and sqlc write the same statement with :albumId and sqlc.narg(album_id), syntax that fails in a console. A SQL file with tool syntax is the input to a tool that produces a statement, which is what a builder is too. PgTyped's documentation states the design: "pgTyped doesn't support query composition or concatenation", and "forces you to move the dynamic logic into the SQL layer".

The nullable parameter, with the casts PostgreSQL requires on a reused parameter, is the mild form of that move. The rest is catalogued in sqlc's dynamic queries discussion, open since 2020: CASE WHEN switches that defeat indexes, placeholders filled by application code, and a query builder beside the generator. The composition did not disappear; it moved into the statement, where the planner pays for every variation, or back into application code, where nothing checks it.

Reuse the filters for a list and a count​

The list chooses its fields and pagination after calling matchingAssets; the total reuses the filters without inheriting the limit:

const assets = await matchingAssets(options)
  .select(['asset.id', 'asset.name'])
  .orderBy('asset.name')
  .orderBy('asset.id')
  .limit(50)
  .execute()
// Inferred: { id: string; name: string }[]

const total = await matchingAssets(options)
  .select((eb) => eb.cast<number>(eb.fn.countAll(), 'integer').as('count'))
  .executeTakeFirstOrThrow()
// Inferred: { count: number }

Each consumer keeps its own selections and pagination; each inherits the shared query's scope and result type. Kysely's builders are immutable, so extending one does not change another, and a new shared rule goes into matchingAssets once. Fragments give Postgres.js the same reuse of the predicates and none of the result: each template declares its shape again by hand, so every consumer is one more annotation that nothing checks against its SQL. A generated call returns one fixed shape, so the count is a second statement that repeats the predicates. Checking both statements does not establish that their filtering rules agree.

Add a folder name through a left join​

An export needs each asset's folder name, and unfiled assets have none:

const exportedAssets = await matchingAssets(options)
  .leftJoin('folder', 'folder.id', 'asset.folder_id')
  .select(['asset.id', 'asset.name', 'folder.name as folder_name'])
  .orderBy('asset.name')
  .orderBy('asset.id')
  .execute()
// Inferred: { id: string; name: string; folder_name: string | null }[]

folder_name is nullable because the left join made it nullable. The Postgres.js caller annotates the result itself, and nothing objects to folder_name: string. A generator has to infer nullability, because PostgreSQL's RowDescription reports a column's type and source table and nothing about whether it can be null. Put this export in a .sql file and Prisma TypedSQL and PgTyped generate folder_name: string: the source column is NOT NULL, the left join is not consulted, and folder_name.toUpperCase() compiles and throws on the first unfiled asset. sqlc tracks the join and emits string | null. Nullability is where two of the three generators type the column wrong, and it is what Kysely tracks through the join.

Add optional nested tags​

The list needs tags when requested. Add a correlated JSON subquery with jsonArrayFrom from kysely/helpers/postgres:

const assets = await matchingAssets(options)
  .select(['asset.id', 'asset.name'])
  .$if(options.includeTags, (qb) =>
    qb.select((eb) =>
      jsonArrayFrom(
        eb
          .selectFrom('asset_tag')
          .innerJoin('tag', 'tag.id', 'asset_tag.tag_id')
          .select(['tag.id', 'tag.name'])
          .whereRef('asset_tag.asset_id', '=', 'asset.id')
          .orderBy('tag.name'),
      ).as('tags'),
    ),
  )
  .orderBy('asset.name')
  .orderBy('asset.id')
  .limit(50)
  .execute()
// Inferred: { id: string; name: string; tags?: { id: string; name: string }[] }[]

Requested tags use [] when none exist; unrequested tags omit the key and the subquery. The inner references are checked, including the one to the outer asset. A fixed statement cannot remove a column, so a SQL file is a second statement chosen in TypeScript, and the database describes its tags column as json: TypedSQL and PgTyped generate JsonValue | null and Json | null, and sqlc string | null. A JSON column's shape is where generated types stop; the nested shape is known only to the code that wrote the subquery, and Kysely wrote it. See conditional selects and nested relations.

What changed as the requirements grew?​

RequirementKyselySQL templatesTyped SQL files
Add a conditional join and predicateExtend the builder; references checked in the branch.Compose fragments; the statements leave SafeQL's coverage.Nullable parameters and exists; one statement, and one plan, for every combination.
Share filters across list, count, exportOne unfinished query; each caller selects its fields.Share fragments; each caller declares its own annotation.A statement per shape; repeat the predicates, or use a view.
Left join an optional folder nameThe column arrives as string | null.The annotation says whatever the caller wrote.A new statement; string from TypedSQL and PgTyped, string | null from sqlc.
Optional nested tagsA conditional selection; the nested shape is inferred.A conditional fragment; the annotation declares the shape.A second statement; the column is JSON, or a string.

Each requirement cost the builder one checked operation; it cost the fragments a helper and an annotation, and the SQL files a statement.

Try to break the shared query​

The test of a composition layer is what happens when someone changes it without reading all of it.

A consumer cannot reference a table the shared query only sometimes joins​

A per-album export needs each row's album ID. With fragments, the caller adds the column:

const albumExport = await sql<{ id: string; name: string; album_id: string }[]>`
  select asset.id, asset.name, album_asset.album_id ${matchingAssetSql(options)}
  order by asset.name, asset.id
`

This compiles and runs for requests that supply an album. For the rest, the helper omits the join and PostgreSQL rejects album_asset.album_id; the fragment records nothing about when album_asset is present. The same caller does not compile with Kysely:

const albumExport = await matchingAssets(options)
  .select(['asset.id', 'asset.name', 'album_asset.album_id']) // TypeScript error
  .execute()

album_asset is in scope only inside the if that joins it; the shared query's type is the builder before the join, so it does not offer the table. The error names the decision: require the album and join it unconditionally, or select the column inside the branch that has it. A fragment carries SQL text and bound values; a Kysely builder also carries the tables in scope and the result selected so far. Whether a join applies the right rule or preserves row counts still needs review and tests.

With SQL files the export is a new statement with its own exists. A generated call cannot be extended by its caller, which prevents the mistake and also prevents the extension. TypedSQL documents its dynamic-column limit as a hand-off to unsafe raw queries.

A rename reaches every caller through the compiler​

Suppose a migration renames asset.name to asset.label, and an agent must update the application. With Kysely, regenerate the declarations once and type-check:

interface Database {
  asset: {
    id: string
    label: string
    owner_id: string
    folder_id: string | null
  }
  // ...
}

function matchingAssets(options: AssetOptions) {
  return (
    db
      .selectFrom('asset')
      // ...
      .$call((qb) => {
        // ...
        if (options.namePrefix !== undefined) {
          qb = qb.where('asset.name', 'like', options.namePrefix + '%') // TypeScript error
        }
        return qb
      })
  )
}

const assets = await matchingAssets(options)
  .select(['asset.id', 'asset.name']) // TypeScript error
  .orderBy('asset.id')

The compiler lists every stale reference: the shared filter and each consumer's selection, in the list, the count, the export, and the tags listing, and the editor completes asset.label where asset.name used to be. The same edit in the raw SQL forms is a text search. A SQL template's declared annotation keeps claiming name exists until a runtime value is undefined. A generated type catches the drift at the next generation run, and only in the statements it generated. For PgTyped and TypedSQL that run needs a database at the new schema; for sqlc it needs the schema files and the generator; for all three it produces regenerated output to review before the compiler has anything to compare. A type check is a verification step an agent can run on every edit, with nothing but the repository. The database enters Kysely's loop when the schema changes, not when a query does; a query edit, such as selecting asset.name as label, changes the consumer's inferred type and its callers' errors with no generation step.

SQL checkers verify a statement; Kysely verifies a composition​

A SQL checker verifies a statement it can see whole; Kysely verifies each operation that application code adds to a query. SafeQL and the generators ask PostgreSQL to describe a statement, so its grammar, functions, and result columns are checked against the database. Kysely's grammar is library output, tested per dialect across its API, so a misplaced clause is not a mistake application code can make; a reference, a result shape, or a composition is, and those are what its types check.

What the builder leaves to the first run is a short list; raw SQL outside a checker's coverage leaves the whole statement there. Kysely's coverage ends where a statement's meaning depends on the database rather than the declarations: an unqualified id after a join of two tables that both have one compiles and PostgreSQL rejects it as ambiguous, a selected column missing from GROUP BY compiles, fn('lower', ['asset.id']) compiles whatever the column's type, and a sql expression is typed as declared. The declarations are also a snapshot: a migration applied without regenerating them is drift the compiler cannot see and a checker reading the live database can, so regeneration belongs in the migration step or in CI. The checker and the builder are not exclusive: SafeQL's Kysely plugin, experimental, inlines the sql expressions in a Kysely query and checks them against the database, so the one input the builder takes on trust can have a checker too. A checker with the database catches each of those gaps in a fixed statement; the builder sends them to the first run with the references and the composition already checked.

Every mistake a builder prevents is one that unchecked SQL finds at runtime, one round trip at a time. Outside a checker's coverage, which ends at the first conditional, a misspelled column, a parameter numbered wrong, or a comparison against the wrong type is found by running the statement and reading the database's message, and PostgreSQL stops at the first error: one per attempt. With Kysely those are compile errors, reported together, before a connection is opened. For a coding agent that is an execution loop per mistake, against a database it may not have.

Can raw SQL be type-safe?​

Yes, one statement at a time. A declared result type checks callers against the annotation, not the SQL; a runtime schema, such as Slonik's with a parser interceptor, rejects an incompatible row when the query runs; SafeQL and the generators check the statements they see whole. None of them establish conditional composition. Kysely does not validate returned rows, so runtime validation still has a place at boundaries.

Performance belongs to the statement, not the authoring tool​

The database receives a parameterized statement either way, and cannot tell which tool wrote it. Compiling a builder to SQL is work in application memory before the round trip; the comparison that matters is between the statements and their plans. For a request with only an owner filter, .compile() on the list query returns the parameters ['owner-1', 50] and this SQL:

compiled.sql (formatted)
select "asset"."id", "asset"."name"
from "asset"
where "asset"."owner_id" = $1
order by "asset"."name", "asset"."id"
limit $2

A request without an album sends no album_asset reference, so each shape the application composes is planned as the statement it is, with that statement's indexes. The fixed ($2::text is null or ...) statement serves every combination with one text: after five executions of a prepared statement PostgreSQL may keep a generic plan, chosen without the parameter values, and PgTyped's documentation warns that "highly dynamic SQL queries can lead to worse DB execution times". The fixed statement asks the planner to serve every combination from one plan; the composed one asks the application to own the combinations and gives each its own plan.

Kysely's escape hatch is a typed expression inside the builder​

Raw SQL has a place inside Kysely: as an expression inside a composed query, not a statement beside it. Kysely does not cover all of SQL; it covers what applications write most, extends that coverage release by release, and types the escape hatch for the rest. A sql template inside a query is an expression like any other: a reference passed through eb.ref is checked against the tables in scope, values bind as parameters, and the declared result type flows into the query. The builder's like operator has no ESCAPE clause; a reusable helper supplies it:

import { Expression, sql } from 'kysely'

function like(column: Expression<string>, pattern: string, escape = '\\') {
  return sql<boolean>`${column} like ${pattern} escape ${escape}`
}
const matches = await matchingAssets(options)
  .where((eb) => like(eb.ref('asset.name'), pattern))
  .select(['asset.id', 'asset.name'])
  .limit(50)
  .execute()
// Inferred: { id: string; name: string }[]

A whole statement through the sql tag gives up every check on this page; keep that form for migrations and scripts, and sql.raw for trusted syntax such as an identifier. See raw expressions and the SQL template API.

Production code draws the same line​

OpenClaw's contributor guide, written for coding agents, requires Kysely for ordinary SQLite access and limits raw SQL to schema, migrations, bootstrap, and justified primitives. Schema, migrations, and bootstrap are written as SQL; the queries application code runs go through the builder. The same composition appears in larger codebases:

  • Immich composes optional relation selections and a lateral subquery for stacked assets.
  • Open Collective conditionally adds currency conversion through a correlated exchange-rate lookup.
  • Docmost composes recursive ancestors for page restrictions with the same builder interface as its simpler queries.

Other practical considerations​

SQL injection

A query builder does not prevent SQL injection on its own, and raw SQL does not invite it on its own. Every client here binds values as parameters; injection enters through request text placed in SQL syntax, which Postgres.js sql.unsafe and Kysely's sql.raw both send as SQL. Column names and sort directions are syntax, so every workflow allow-lists them; pass an allow-listed column to orderBy and a misspelled entry is a type error, while Kysely's dynamic references leave the allow-list to you. Safe parameterization is not the reason to adopt a builder; raw SQL has it. The checked composition API is.

Type-checking cost

Raw SQL costs nothing at the type level. Kysely's checks are computed by the compiler, and a schema with hundreds of tables or a deeply composed query can slow type-checking and the editor. Generated declarations keep that cost tied to schema size, not query count, and conditional branches and helpers typed with Expression keep individual queries shallow.

Incremental adoption

Kysely's PostgreSQL dialect takes an existing node-postgres Pool, so adoption runs one helper at a time while the remaining pool.query calls keep running.

Engines, drivers, and portability

Raw SQL is written in one engine's grammar, with its placeholder style, identifier quoting, and functions. PgTyped is PostgreSQL only. Prisma TypedSQL supports PostgreSQL, MySQL, and SQLite, with argument types declared manually on SQLite. sqlc supports the same three; its TypeScript plugin targets pg, Postgres.js, mysql2, and better-sqlite3, and its README states that it is "still in early access". Kysely ships dialects for PostgreSQL, MySQL, SQLite, and Microsoft SQL Server, compiles the same operations to each engine's syntax, and exposes engine-specific features such as PostgreSQL conflict handling and MySQL duplicate-key updates.

Most applications never change engines, so portability is a weak reason on its own to adopt a builder; it matters for libraries, multi-engine products, and teams that test on a lighter engine.

Choosing raw SQL or Kysely​

Why use a query builder instead of raw SQL?​

Because application queries change, and a query builder is the only form here that checks the change itself, not only the statement it produces. Raw SQL binds the same parameters and sends the same statement; what it leaves to review, tests, and search, Kysely's types report before a connection is opened.

When does raw SQL belong in a TypeScript application?​

Where there is no composition to check: migrations and DDL, bootstrap, one-off scripts, and the expression a builder cannot express.

Try one endpoint whose filters are repeated across a list and count. Add a shared rule, change a selected alias, and let a consumer reference a conditional join. Compare the edits, the steps before anything can tell you the edit was valid, and the statements the database receives.

Write application queries in Kysely, fixed or composed: references checked, results inferred, and the composition layer checked rather than maintained.

Get started with Kysely, explore query examples, or learn how to generate database types.