Type-safe SQL in Playwright tests: a compile-time FSM, a branded string, and zero leaked connections

Playwright is excellent at browser automation. But a substantial portion of interesting tests also need a database — seeding rows before a test, verifying mutations after one, or testing stored procedures directly.

The conventional approach is either to mock the database (fast but hollow) or to manage connections manually in beforeEach / afterEach hooks (correct but tedious, and leak-prone when a test throws).

This post documents how we built a three-package system that:

Validates SQL structure at compile time via a TypeScript finite-state-machine
Infers the exact parameter tuple from ? / $N placeholders as a phantom type
Wires a real database connection into Playwright's fixture system with automatic lifecycle management
Provides editor autocomplete, diagnostics, and hover inside sql template literals

All three packages are MIT-licensed and live in the playwright-labs monorepo.

The problem space

Consider this test:

test("fetch user by email", async () => {
  const conn = await pg.connect(process.env.DATABASE_URL!);
  try {
    const result = await conn.query(
      "SELECT * FROM usres WHERE email = $1", // typo: usres
      ["alice@example.com"],
    );
    expect(result.rows).toHaveLength(1);
  } finally {
    await conn.end();
  }
});

Three problems:

usres is a typo. TypeScript has no idea — it is just a string.
If conn.query throws, the finally block runs but the test framework may not await it cleanly depending on how errors propagate.
This boilerplate repeats in every test file.

We wanted a solution where the typo is caught before the test runner starts, where connection cleanup is guaranteed, and where the per-test setup is a single test.use() call.

Part 1 — The type system (`sql-core`)

Template literal types as an FSM

TypeScript's template literal types support string pattern matching and recursive conditional types. Together they are expressive enough to model a grammar.

We encode a simplified SQL grammar as a finite-state machine where each State* type is a node and conditional branches are transitions:

type ParseSQL<S extends string> =
  Uppercase<Head<S>> extends "SELECT"
    ? StateAfterSELECT<Tail<S>, S>
    : Uppercase<Head<S>> extends "UPDATE"
      ? StateAfterUPDATE<Tail<S>, S>
      : Uppercase<Head<S>> extends "DELETE"
        ? StateAfterDELETE<Tail<S>, S>
        : Uppercase<Head<S>> extends "INSERT"
          ? StateAfterINSERT<Tail<S>, S>
          : Uppercase<Head<S>> extends "CREATE"
            ? StateAfterCREATE<Tail<S>, S>
            : never;

Head<S> extracts the first whitespace-delimited token. Tail<S> returns everything after it. The FSM walks the token stream and either reaches an accepting state (returning the parameter tuple) or never.

type StateAfterSELECT<S extends string, Full extends string> = S extends ""
  ? never // SELECT with no FROM
  : Uppercase<Head<S>> extends "FROM"
    ? StateAfterFROM<Tail<S>, Full> // transition to FROM state
    : StateAfterSELECT<Tail<S>, Full>; // consume column, recurse

Parameter extraction

Once the FSM reaches a valid accepting state it counts parameters. We support both styles:

? positional (MySQL/SQLite):

type CountParams<
  S extends string,
  Acc extends unknown[] = [],
> = S extends `${string}?${infer Rest}`
  ? CountParams<Rest, [...Acc, unknown]>
  : Acc;

$N numbered (PostgreSQL):

We walk candidates "1" through "20", find the highest present index, convert it to a tuple length, and validate that all lower indices are also present — no gaps allowed.

type ValidateDollarSequential<S extends string, N extends number> = N extends 0
  ? true
  : HasDollarParam<S, `${N}`> extends true
    ? ValidateDollarSequential<S, MinusOne<N>>
    : false;

MinusOne<N> is a separate utility type that decrements a numeric literal using digit-by-digit arithmetic in the type system.

The public API

export type SQLParams<S extends string> =
  ParseSQL<Trim<S>> extends never
    ? never
    : HasAnyDollarParam<Trim<S>> extends true
      ? ValidateDollarSequential<
          Trim<S>,
          StringToNumber<MaxDollarIndex<Trim<S>>>
        > extends true
        ? CountDollarParams<Trim<S>>
        : never
      : CountParams<Trim<S>>;

Input	`SQLParams<S>`
`'SELECT * FROM users WHERE id = ?'`	`[unknown]`
`'UPDATE t SET a = $1, b = $2 WHERE id = $3'`	`[unknown, unknown, unknown]`
`'SELECT *'`	`never` (missing FROM)
`'SELECT * FROM t WHERE id = $3'`	`never` ( $1,$ 2 missing)

`SqlStatement<P>` — the phantom brand

export type SqlStatement<P extends readonly unknown[] = readonly unknown[]> =
  string & { readonly __sqlBrand: P };

A string-keyed property rather than a unique symbol so the brand survives tsup's type bundling across .d.ts chunks.

The SqlClient interface uses _PlainOrEmpty as the fallback overload parameter to prevent SqlStatement<[unknown]> from silently falling through when params are missing:

type _PlainOrEmpty = string & { readonly __sqlBrand?: readonly [] };
 
interface SqlClient {
  query<T = Row, P extends readonly [unknown, ...unknown[]]>(
    sql: SqlStatement<P>,
    params: readonly [...P],
  ): Promise<QueryResult<T>>;
 
  query<T = Row>(
    sql: _PlainOrEmpty,
    params?: unknown[],
  ): Promise<QueryResult<T>>;
  // ...
}

SqlStatement<[unknown]> is NOT assignable to _PlainOrEmpty because __sqlBrand: [unknown] is not compatible with __sqlBrand?: []. So the first overload is selected, which requires params.

The `sql` function

Tagged template literal strings arguments are always widened to TemplateStringsArray by TypeScript, which prevents literal-type inference. We expose three calling forms:

@type {<const T extends TemplateStringsArray | string | string[]>(strings: T, ...values) =>
  T extends TemplateStringsArray
    ? string
    : T extends string
      ? (SQLParams<T> extends never ? never : SqlStatement<SQLParams<T>>)
      : T extends readonly [infer S extends string]
        ? (SQLParams<S> extends never ? never : SqlStatement<SQLParams<S>>)
        : string
}

Form	Return type
sql`SELECT …`	`string`
`sql("SELECT …")`	`SqlStatement<P>`
`sql(["SELECT …"])`	`SqlStatement<P>`

Use sql("…") or sql(["…"]) when you want compile-time validation. Use the tagged form for editor syntax highlighting (the language plugin handles structural diagnostics separately).

`AsyncDisposable`

SqlClient implements the TC39 explicit resource management protocol:

interface SqlClient {
  close(): Promise<void>;
  [Symbol.asyncDispose](): Promise<void>;
}

await using client = await sqliteAdapter(":memory:").create();
// automatically closed when the block exits

Part 2 — The Playwright fixture (`fixture-sql`)

Design

The fixture creates one SqlClient per test via the configured SqlAdapter, registers it for teardown, and exposes it as the sql fixture:

// simplified from fixture.ts
const test = base.extend<Fixtures>({
  sql: async ({ sqlAdapter }, use) => {
    const client = await sqlAdapter.create();
    await use(client);
    await client.close();
  },
  useSql: async ({}, use) => {
    const clients: SqlClient[] = [];
    await use(async (adapter) => {
      const client = await adapter.create();
      clients.push(client);
      return client;
    });
    await Promise.all(clients.map((c) => c.close()));
  },
  sqlAdapter: [
    async ({}, use) => {
      throw new Error(
        "sqlAdapter fixture not configured — call test.use({ sqlAdapter })",
      );
      await use(null!);
    },
    { option: true },
  ],
});

The finally-equivalent is Playwright's own teardown phase, which runs even when a test throws.

Usage

import { test, expect } from "@playwright-labs/fixture-sql";
import { sqliteAdapter } from "@playwright-labs/fixture-sql/sqlite";
import { sql } from "@playwright-labs/fixture-sql";
 
test.use({ sqlAdapter: sqliteAdapter(":memory:") });
 
test.beforeEach(async ({ sql: db }) => {
  await db.execute(sql`
    CREATE TABLE IF NOT EXISTS users (
      id    INTEGER PRIMARY KEY AUTOINCREMENT,
      name  TEXT    NOT NULL,
      email TEXT    NOT NULL UNIQUE
    )
  `);
});
 
test("parameterised query", async ({ sql: db }) => {
  await db.execute(sql("INSERT INTO users (name, email) VALUES (?, ?)"), [
    "Alice",
    "alice@example.com",
  ]);
 
  const { rows } = await db.query<{ name: string }>(
    sql("SELECT name FROM users WHERE email = ?"),
    ["alice@example.com"],
  );
 
  expect(rows[0]!.name).toBe("Alice");
});

Multiple connections

test("primary → replica sync", async ({ useSql }) => {
  const primary = await useSql(pgAdapter(process.env.PRIMARY_URL!));
  const replica = await useSql(pgAdapter(process.env.REPLICA_URL!));
 
  await primary.execute(sql("INSERT INTO events (type) VALUES ($1)"), [
    "login",
  ]);
 
  // wait for replication…
  const { rows } = await replica.query(
    sql("SELECT * FROM events WHERE type = $1"),
    ["login"],
  );
  expect(rows).toHaveLength(1);
});
// both connections closed automatically

Adapters

Package export	Driver	Peer dep
`@playwright-labs/fixture-sql/sqlite`	`better-sqlite3`	`>=9.0.0`
`@playwright-labs/fixture-sql/pg`	`pg`	`>=8.0.0`
`@playwright-labs/fixture-sql/mysql`	`mysql2`	`>=3.0.0`

Part 3 — The language server plugin (`ts-plugin-sql`)

The plugin runs inside tsserver and intercepts three language service methods.

Configuration

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

Schema loading

The schemaFile is a TypeScript file generated by the pull CLI:

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

// db-types.ts — auto-generated
export interface UsersRow {
  id: number;
  name: string;
  email: string | null;
}
export interface PostsRow {
  id: number;
  user_id: number;
  title: string;
}
 
export type Tables = { users: UsersRow; posts: PostsRow };

The plugin parses this file with the TypeScript AST, extracts table and column names, and uses them for completions and hover.

Completions

Context-aware based on the preceding SQL token:

After FROM, JOIN, UPDATE, INTO → table names
After SELECT, WHERE, SET, AND, OR → column names (filtered to tables in scope)
Elsewhere → SQL keywords

Diagnostics

The plugin runs a plain JS SQL validator on every sql\...`` template in the file and reports structural errors as TypeScript diagnostics. They appear as squiggly underlines in VS Code without any build step.

Hover

const q = sql`SELECT id, name FROM users WHERE id = ?`;
//                              ^^^^^
// Hover: users — id: number, name: string, email: string | null

Putting it all together

live DB ──► pnpm fixture-sql pull ──► db-types.ts ──► tsconfig schemaFile
                                           │
                              import type { UsersRow } in tests

import { test, expect } from "@playwright-labs/fixture-sql";
import { pgAdapter } from "@playwright-labs/fixture-sql/pg";
import { sql } from "@playwright-labs/ts-plugin-sql";
import type { UsersRow } from "./db-types.js";
 
test.use({ sqlAdapter: pgAdapter(process.env.DATABASE_URL!) });
 
test("typed end-to-end", async ({ sql: db }) => {
  const { rows } = await db.query<UsersRow>(
    sql("SELECT id, name FROM users WHERE id = $1"),
    [1],
  );
  expect(rows[0]!.name).toBe("Alice");
});

The sql("…") call validates the query at compile time
The plugin provides completions and hover in the editor
The fixture manages the connection lifecycle
The row result is typed as UsersRow

Installation

pnpm add -D @playwright-labs/fixture-sql @playwright-labs/ts-plugin-sql
pnpm add -D better-sqlite3  # or pg / mysql2

All three packages are MIT-licensed. Source is at github.com/vitalics/playwright-labs.