İsmail Karslı

Posted on Nov 1

TypeSQL: type-safe plain SQL in TypeScript

#postgres #sql #typescript #webdev

I like writing SQL. But in TypeScript, raw SQL usually means giving up types: you get any[] back, or you write the row type by hand and hope it stays in sync with the query.

So I built TypeSQL. It checks your SQL strings with the TypeScript compiler and gives you exact types for rows and parameters.

const rows = await sql(
  `SELECT u.email, p.title
   FROM users u LEFT JOIN posts p ON p.author_id = u.id
   WHERE u.id = ${sql.param(userId)}`,
).execute();
// rows: { email: string; title: string | null }[]

title is string | null because of the LEFT JOIN. userId must be a number, because it's compared to u.id. Nobody wrote those types: the compiler worked them out from the SQL.

How it works

TypeSQL is a SQL parser written in TypeScript's type system. When you call sql("…") with a string literal, the compiler parses the SQL and looks up each table and column in your schema type. This happens in your editor and in tsc.

At runtime, sql() does very little. It builds { text, values }, and your driver sends the text as you wrote it.

Getting started

Install the core package and the adapter for your driver:

npm install @typesql/core @typesql/pg pg
npm install -D typescript@7.1.0-beta

Describe your tables with your database's type names:

import type { Col } from "@typesql/core/postgres";

export interface DB {
  users: {
    id: Col<"int4", { hasDefault: true }>;
    email: Col<"text">;
    display_name: Col<"text", { nullable: true }>;
    created_at: Col<"timestamptz", { hasDefault: true }>;
  };
  posts: {
    id: Col<"int8", { hasDefault: true }>;
    author_id: Col<"int4">;
    title: Col<"text">;
  };
}

Then create sql from your client:

import { Pool } from "pg";
import { createSql } from "@typesql/pg";

const sql = createSql<DB>(new Pool());

You don't have to write the schema by hand. You can generate it from a live database with typesql introspect, derive it from your migrations, or reuse an existing Kysely, Drizzle or Prisma schema.

Mistakes become compile errors

Errors show up on the SQL string, worded like the database's own errors:

sql("SELECT emial FROM users");
// column "emial" does not exist. Perhaps you meant the column "email"?

sql("SELECT * FROM post");
// relation "post" does not exist. Perhaps you meant the relation "posts"?

sql("SELECT id FROM users WHERE id = $1", "7");
// Argument of type 'string' is not assignable to parameter of type 'number'.

You find these in your editor, not in production.

Nullability that follows the SQL

This is the part I'm most happy with. TypeSQL works out nullability from the query itself:

Columns from the outer side of a LEFT JOIN become nullable.
COALESCE(x, 'default') makes them non-null again.
WHERE x IS NOT NULL narrows x to non-null.
Aggregates like max() are nullable, and count() is not.

Tools that ask a running database for types can't do this, because Postgres doesn't report nullability for result columns.

Parameters

Parameters take their type from where you use them. There are three ways to write them:

// Positional
sql("SELECT id FROM users WHERE id = $1", 7);

// Named
sql("SELECT id FROM users WHERE email = :email", { email: "a@b.c" });

// Inline
sql(`SELECT id FROM users WHERE id = ${sql.param(userId)}`);

Values are always sent as parameters, never pasted into the SQL text, so SQL injection isn't possible through them.

Writes are checked too

INSERT, UPDATE, DELETE and MERGE are checked against your schema:

sql("INSERT INTO users (display_name) VALUES ($1)", "Ada");
// null value in column "email" of relation "users" violates not-null constraint

Target columns must exist, values must fit their types, and every NOT NULL column without a default must be set. RETURNING gives the statement a row type.

Dynamic SQL

Sometimes a query has optional parts. Fragments let you build it from pieces, and the finished statement is still checked as a whole:

const recent = sql.fragment("u.id > $1", 10);

const q = sql(
  `SELECT u.email FROM users u WHERE ${recent} AND u.email <> $1`,
  "a@b.c",
);

Optional pieces are checked both with and without the fragment. There are also helpers for lists, identifiers, SET clauses and bulk VALUES. Most of the time, though, sql() is all you need.

Tested against PostgreSQL itself

A SQL parser in the type system sounds fragile, so I test it against PostgreSQL 18's own regression suite: about 50,000 statements. TypeSQL agrees with Postgres on all 49,999 statements it can check. CI fails if a single one disagrees.

TypeSQL also supports SQLite, MySQL and MariaDB, each with its own parser and type rules.

Editor support

Hover any query to see its row type. This works in any TypeScript editor. The VS Code extension and the Neovim language server also add table and column completions and SQL highlighting inside sql() strings.

Limits

TypeScript 7.1 is required. TypeSQL runs in the new native compiler. Older versions are too slow and aren't supported.
Only SQL known at compile time is checked. For SQL built at runtime, you give the row type yourself with sql.as<Row>().
Very large statements can hit the compiler's limits, for example around 42 UNION ALL branches. The docs list the exact numbers.
No tagged templates (sql`…`). TypeScript doesn't expose their text to the type system.

Try it

Try it online, with nothing to install: TypeScript Playground (it runs an older TypeScript, so treat it as a demo)
Docs: https://typesql.karsli.net
GitHub: https://github.com/ismailkarsli/TypeSQL

I'd love feedback, especially queries where TypeSQL gets a type wrong or gives up. Issues are welcome on GitHub.

DEV Community