DEV Community

Cover image for Tagged Template Literals That Type-Check SQL: A 60-Line DSL
Gabriel Anhaia
Gabriel Anhaia

Posted on

Tagged Template Literals That Type-Check SQL: A 60-Line DSL

#typescript #tutorial #javascript #programming

The query was wrong for two weeks before anyone noticed. A column got renamed from created to created_at in a migration, the report kept running, the report kept returning rows, the rows had null where created used to be. Nobody caught it because string is a type that does not know what a column name is.

Drizzle and Kysely solve this. They are the right answer for a real codebase. But there is a small DSL you can build in about sixty lines that gets you most of the typing benefit when the surface is small enough that pulling in a query builder is the wrong shape: internal scripts, a one-table audit log, the migration tooling for your own product. The interesting part is what falls out of tagged template literals plus template-literal types when you put them together.

This is that DSL, end to end, with the failure modes the compiler will catch.

The schema is a type

Every column-name check you want at compile time has to start from a single source of truth. A type literal that lists tables and their columns is enough.

type Schema = {
  users: {
    id: number;
    email: string;
    created_at: string;
  };
  orders: {
    id: number;
    user_id: number;
    total: number;
  };
};
Enter fullscreen mode Exit fullscreen mode

That object is the only thing the rest of the DSL reads from. No runtime mirror, no codegen.

A tagged template that captures column names

A tagged template gets two things: an array of string parts and a list of interpolated values. The runtime side is unremarkable. The type side is where the work happens.

You want the call site to look like this:

const q = sql<Schema>`
  SELECT ${"id"}, ${"email"} FROM ${"users"}
`;
Enter fullscreen mode Exit fullscreen mode

The interpolated slots are column or table names. The compiler should reject ${"emial"} immediately because the schema does not have it. And q should know it is selecting id and email from users, so the row type comes back as { id: number; email: string }.

To get there, the tag function takes two generics: the schema and a tuple of literal strings the call site is interpolating. TypeScript's as const inference on a tuple parameter does the heavy lifting.

type ColOf<S, T extends keyof S> = keyof S[T] & string;
type AnyName<S> = (keyof S & string) | ColOf<S, keyof S>;

function sql<S>() {
  return function tag<P extends readonly AnyName<S>[]>(
    parts: TemplateStringsArray,
    ...names: P
  ): { text: string; names: P } {
    let text = parts[0];
    for (let i = 0; i < names.length; i++) {
      text += `"${names[i]}"` + parts[i + 1];
    }
    return { text, names };
  };
}
Enter fullscreen mode Exit fullscreen mode

Two things to read here. AnyName<S> is the union of every legal name in the schema: every table key, plus every column key under every table. Anything else fails at the call site. The empty sql<Schema>() call is the awkward bit. TypeScript cannot partial-apply a generic, so the schema lands on a curried call. You write it once at the top of the file and forget it.

const q = sql<Schema>()`
  SELECT ${"id"}, ${"email"} FROM ${"users"}
`;
// q.text:  SELECT "id", "email" FROM "users"
// q.names: readonly ["id", "email", "users"]

const broken = sql<Schema>()`
  SELECT ${"emial"} FROM ${"users"}
`;
// error: '"emial"' is not assignable to '"id" | "email" |
//   "created_at" | "user_id" | "total" | "users" | "orders"'
Enter fullscreen mode Exit fullscreen mode

That is half the win. The other half is figuring out the row shape from the names you captured.

Inferring the row shape

The tuple P keeps the order of the interpolated names. The convention you enforce in the SQL is columns first, then the table at the end. That gives the compiler something to pattern-match on.

A small recursive conditional type splits the tuple into "all but last" and "last", uses the last as the table name, and looks up each leading element on that table.

type SplitLast<T extends readonly any[]> =
  T extends readonly [...infer Init, infer Last]
    ? { init: Init; last: Last }
    : never;

type RowOf<S, P extends readonly string[]> =
  SplitLast<P> extends { init: infer Cols; last: infer T }
    ? T extends keyof S
      ? Cols extends readonly (keyof S[T])[]
        ? { [K in Cols[number] & keyof S[T]]: S[T][K] }
        : never
      : never
    : never;
Enter fullscreen mode Exit fullscreen mode

Now thread RowOf through the return type of the tag.

function sql<S>() {
  return function tag<P extends readonly AnyName<S>[]>(
    parts: TemplateStringsArray,
    ...names: P
  ): { text: string; names: P; row: RowOf<S, P> } {
    let text = parts[0];
    for (let i = 0; i < names.length; i++) {
      text += `"${names[i]}"` + parts[i + 1];
    }
    return { text, names, row: undefined as unknown as RowOf<S, P> };
  };
}

const q = sql<Schema>()`
  SELECT ${"id"}, ${"email"} FROM ${"users"}
`;
type Row = typeof q.row;
// { id: number; email: string }
Enter fullscreen mode Exit fullscreen mode

The row property is a phantom: undefined at runtime, with the type as the only payload. You hand it to the layer that turns the SQL string into actual rows (pg, better-sqlite3, whatever) and use Row as the return type of the function.

What fails, and where

The interesting part of any DSL is the error surface. The 60-line version above catches three classes of mistake at the call site.

Misspelled column. Already covered. The interpolated slot is constrained to the union of names, the compiler refuses anything else.

Column from the wrong table. A column that does exist in the schema but not on the table you named.

const wrongTable = sql<Schema>()`
  SELECT ${"total"} FROM ${"users"}
`;
type WrongRow = typeof wrongTable.row;
// never
Enter fullscreen mode Exit fullscreen mode

The conditional type collapses to never because "total" is not a key of S["users"]. The error is not screaming. WrongRow is just never, which means nothing downstream that consumes a row will accept it. You catch the failure when you try to use the row, not at the SQL line. That is a real cost.

Empty selection. If you forget the columns and only interpolate the table, RowOf returns never for the same reason. A SELECT FROM users with no columns is not a query.

What the 60-line version does not catch:

  • A WHERE clause comparing a column to the wrong type. The string between interpolations is opaque to the compiler. You can lift WHERE into typed slots (${eq("id", 7)} returns a typed predicate), but that is another thirty lines.
  • A column that exists on a different table you happen to be joining. Joins move the column-name space from one table to a union of tables, and the tag has to thread the join shape through. Doable, but the recursion gets fiddly and the error messages get noisy.
  • SQL injection in a literal section. The string between ${...} is concatenated as-is. If you put user input outside an interpolation slot, you wrote a SQL injection. The DSL types names, not values; a separate parameter-binding API is the right place for values.

Where the 60 lines stop earning their cost

The DSL is a bet on a specific shape. You have a small surface: a handful of tables, queries you write by hand, no joins or aggregations more elaborate than a WHERE. In that shape, the type-level guarantee on column names plus the row inference is worth the price of one curried generic at the top of the file.

The bet stops paying when the surface grows. Three cliffs to watch for.

Parser complexity is the first cliff. Every feature you want to type (JOIN, GROUP BY, aggregates, expressions in the SELECT list) is a new conditional type that recursively walks the tuple of interpolated names and produces a row shape. Each one is tractable on its own. Stacked, they slow tsc and produce errors that point at a 40-line conditional instead of the SQL line.

Error messages are the second cliff. When the conditional type fails for a non-obvious reason (a missing column on a joined table, or a tuple shape that does not match the parser's pattern), the compiler shows you the broken type, not the broken SQL. You spend time decoding the type system instead of the query.

And then there is the existence of Drizzle, Kysely, and pg-typed. Those projects solved the same problem with years of edge cases baked in. The 60-line DSL is right when you are inside a small package, the schema is yours, and pulling in a 200kB query builder for one audit log function is the wrong cost. It is wrong when you are building the data layer of an actual product.

A practical rule. If your RowOf definition starts to grow a third recursive case, you have outgrown the DSL. Switch to a real query builder before the recursion grows a fourth.

The whole thing

Sixty lines, end to end, with the schema and a working call. Paste it into a TS playground at strict settings and the call sites compile and the misspelled name fails.

type ColOf<S, T extends keyof S> = keyof S[T] & string;
type AnyName<S> = (keyof S & string) | ColOf<S, keyof S>;

type SplitLast<T extends readonly any[]> =
  T extends readonly [...infer Init, infer Last]
    ? { init: Init; last: Last }
    : never;

type RowOf<S, P extends readonly string[]> =
  SplitLast<P> extends { init: infer Cols; last: infer T }
    ? T extends keyof S
      ? Cols extends readonly (keyof S[T])[]
        ? { [K in Cols[number] & keyof S[T]]: S[T][K] }
        : never
      : never
    : never;

function sql<S>() {
  return function tag<P extends readonly AnyName<S>[]>(
    parts: TemplateStringsArray,
    ...names: P
  ): { text: string; names: P; row: RowOf<S, P> } {
    let text = parts[0];
    for (let i = 0; i < names.length; i++) {
      text += `"${names[i]}"` + parts[i + 1];
    }
    return {
      text,
      names,
      row: undefined as unknown as RowOf<S, P>,
    };
  };
}

type Schema = {
  users: {
    id: number;
    email: string;
    created_at: string;
  };
  orders: {
    id: number;
    user_id: number;
    total: number;
  };
};

const q = sql<Schema>()`
  SELECT ${"id"}, ${"email"} FROM ${"users"}
`;
type Row = typeof q.row;
// { id: number; email: string }
Enter fullscreen mode Exit fullscreen mode

The tagged-template-plus-template-literal-types pattern is in the same family as Zod's schema-to-type inference and Hono's route typing. The shape is always the same: a runtime value whose generic parameters are a structured representation of what you wrote, threaded through conditional types until the call site sees a type that mirrors the input.

Sixty lines is the smallest version of that pattern that does anything useful. It is also the version that fits in your head while you are reading it, which is the only reason to write it instead of installing the library.

If this was useful

Tagged templates and template-literal types are two of the building blocks in The TypeScript Type System. The book covers how the DSL-level patterns above generalize: how Zod parses a schema literal into a type, how Drizzle's column inference is a stack of conditional types, how the same machinery shows up in ts-pattern and Hono's route typing. If the 60-line DSL above made you want to write your own typed mini-language, that is the chapter to read next.

If you are coming from JVM languages, Kotlin and Java to TypeScript covers the same bridge with a focus on variance, sealed-to-union translations, and where the type systems disagree. From PHP 8+, PHP to TypeScript covers the sync-to-async and generics jump from the other side. If you are shipping TS at work, TypeScript in Production covers the build, monorepo, and dual-publish concerns the type system itself does not touch.

The five-book set:

  • TypeScript Essentials — From Working Developer to Confident TS, Across Node, Bun, Deno, and the Browser — entry point: amazon.com/dp/B0GZB7QRW3
  • The TypeScript Type System — From Generics to DSL-Level Types — deep dive: amazon.com/dp/B0GZB86QYW
  • Kotlin and Java to TypeScript — A Bridge for JVM Developers — bridge for JVM devs: amazon.com/dp/B0GZB2333H
  • PHP to TypeScript — A Bridge for Modern PHP 8+ Developers — bridge for PHP devs: amazon.com/dp/B0GZBD5HMF
  • TypeScript in Production — Tooling, Build, and Library Authoring Across Runtimes — production layer: amazon.com/dp/B0GZB7F471

All five books ship in ebook, paperback, and hardcover.

The TypeScript Library — the 5-book collection

Top comments (0)