Vitali Haradkou

Posted on Apr 13

TypeScript knows your SQL is broken before your tests even run

#typescript #playwright #testing #sql

You're writing a Playwright test. You need to seed some rows first. You write:

const result = await db.query('SELECT * WHERE id = ?', [42]);

TypeScript: 🤷 looks fine to me.

Your test runner: also fine.

Your database at 2 AM: ERROR: syntax error at or near "WHERE"

What if TypeScript could catch that? Not at runtime. Not in the test. At the type level, before anything runs.

Spoiler: it can.

The trick: SQL grammar as TypeScript types

TypeScript's template literal types let you match string patterns recursively. You can model a finite-state machine entirely within the type system.

Here is a tiny slice of what that looks like:

type Head<S extends string> =
  S extends `${infer H} ${infer _}` ? H : S;

type Tail<S extends string> =
  S extends `${infer _} ${infer T}` ? T : "";

type StateAfterSELECT<S extends string, Full extends string> =
  S extends ""
    ? never                                       // no FROM → invalid
    : Uppercase<Head<S>> extends "FROM"
      ? StateAfterFROM<Tail<S>, Full>             // found FROM → next state
      : StateAfterSELECT<Tail<S>, Full>;          // consume token, recurse

Walk the SQL token by token. Each state knows what tokens are valid next. If you reach an accepting state, return the parameter tuple. If not, return never.

What you actually get

import type { SQLParams } from '@playwright-labs/sql-core';

type A = SQLParams<'SELECT * FROM users WHERE id = ?'>;
//   ^-- [unknown]  ✅

type B = SQLParams<'SELECT * WHERE id = ?'>;
//   ^-- never  ❌ missing FROM

type C = SQLParams<'UPDATE users WHERE id = ?'>;
//   ^-- never  ❌ missing SET

type D = SQLParams<'SELECT * FROM t WHERE id = $3'>;
//   ^-- never  ❌ $1 and $2 are missing (sequential validation!)

That last one is my favourite. If you jump from $1 straight to $3, the type system notices.

How it connects to real queries

The sql function wraps this type system into something usable:

import { sql } from '@playwright-labs/sql-core';

const q1 = sql("SELECT * FROM users WHERE id = ?");
// typeof q1 → SqlStatement<[unknown]>

const q2 = sql("SELECT * WHERE id = ?");
// typeof q2 → never  ❌ compile error right here

SqlStatement<P> is a branded string that carries the parameter count. The SqlClient interface uses it to enforce the params array:

await client.query(q1, [42]);    // ✅
await client.query(q1);          // ❌ params required
await client.query(q1, [1, 2]);  // ❌ too many params

This is not a runtime check. TypeScript catches all three at compile time.

The Playwright fixture

The type safety story is nice. But in practice, the bigger pain with database tests is the connection boilerplate:

// without fixture-sql — every. single. test. file.
let db: Client;
beforeEach(async () => { db = await pgConnect(); });
afterEach(async () => { await db.end(); });

With @playwright-labs/fixture-sql:

import { test, expect } from '@playwright-labs/fixture-sql';
import { sqliteAdapter } from '@playwright-labs/fixture-sql/sqlite';

test.use({ sqlAdapter: sqliteAdapter(':memory:') });

test('insert and read', async ({ sql: db }) => {
  await db.execute(sql`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`);
  await db.execute(sql`INSERT INTO users VALUES (1, 'Alice')`);

  const { rows } = await db.query<{ name: string }>(
    sql("SELECT name FROM users WHERE id = ?"),
    [1],
  );
  expect(rows[0]!.name).toBe('Alice');
});

One test.use() call. Connection opens before the test, closes after it — even if the test throws. Each test gets a fresh in-memory SQLite database.

The language server plugin

There's also @playwright-labs/ts-plugin-sql: a TypeScript language service plugin that gives you:

Autocomplete for table names and column names inside sql templates
Diagnostics as squiggly underlines in your editor (before you even run a type check)
Hover info showing column types from your actual schema

Configure it in tsconfig.json:

{
  "compilerOptions": {
    "plugins": [{
      "name": "@playwright-labs/ts-plugin-sql",
      "schemaFile": "./src/db-types.ts"
    }]
  }
}

Generate db-types.ts from a live database:

pnpm playwright-labs-fixture-sql pull --adapter sqlite --url ./dev.db --out ./src/db-types.ts

Then

sql`SELECT ...` // rest

Your editor will offer id, name, email from the real schema.

Quick recap

Problem	Solution
SQL typos caught at runtime	`SQLParams<S>` returns `never` for invalid SQL
Wrong param count	`SqlStatement<P>` brand enforces the array
Missing `FROM`, `SET`, etc.	TypeScript FSM validates structure
`$3` without `$1`/`$2`	Sequential validation via `MinusOne<N>` type
Connection boilerplate	`fixture-sql` handles lifecycle
No editor SQL support	`ts-plugin-sql` language server plugin

Try it

pnpm add -D @playwright-labs/fixture-sql @playwright-labs/ts-plugin-sql
pnpm add -D better-sqlite3

The source is at github.com/vitalics/playwright-labs.

My question for you: what's the weirdest SQL bug you've shipped to production that a type system could have caught? Drop it in the comments 👇

DEV Community