Skip to content

sql-core

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

sql-core

README.md

@playwright-labs/sql-core

Core SQL primitives for TypeScript — typed sql tag, driver-agnostic adapters, and a compile-time SQL type system.

What this package provides

@playwright-labs/sql-core is the shared foundation used by @playwright-labs/fixture-sql. It can also be used standalone in any TypeScript project that needs:

  • A sql function that validates SQL structure and infers parameter counts at compile time
  • A SqlClient / SqlAdapter interface that works with PostgreSQL, MySQL, and SQLite
  • Ready-made adapter implementations for all three databases
  • Type utilities (SQLParams, ValidSQL) that catch SQL mistakes before tests run

Features

  • Typed sql function — plain string and array forms return SqlStatement<P>, which encodes the parameter count as a phantom brand; the compiler enforces the correct params array at every call site
  • Compile-time SQL validation — a TypeScript finite-state-machine models SQL grammar; structurally invalid SQL (missing FROM, no SET in UPDATE, gaps in $N sequences, …) produces never at compile time
  • Driver-agnostic SqlClient — one interface for PostgreSQL, MySQL, and SQLite with typed overloads for parameterised queries
  • Three ready-made adapters — sqliteAdapter, pgAdapter, mysqlAdapter via optional peer dependencies
  • ? and $N parameter styles — both MySQL/SQLite (?) and PostgreSQL ($1, $2, …) are supported; $N parameters must be sequential (gap like $3 without $2 → never)
  • ESM + CJS dual build — works in both module systems

Installation

pnpm add @playwright-labs/sql-core
# install the driver you actually use
pnpm add -D better-sqlite3  # SQLite
pnpm add -D pg              # PostgreSQL
pnpm add -D mysql2          # MySQL / MariaDB

Quick start

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

const client = await sqliteAdapter(':memory:').create();

await client.execute(sql`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)`);
await client.execute(sql`INSERT INTO users VALUES (?, ?)`, [1, 'Alice']);

const { rows } = await client.query<{ name: string }>(
  sql`SELECT name FROM users WHERE id = ?`,
  [1],
);

console.log(rows[0]!.name); // 'Alice'

await client.close();

The sql function

The sql export works in three calling forms with different type inference behaviour:

Form TypeScript infers Return type
sql`SELECT …` TemplateStringsArray string
sql("SELECT …") literal string S SqlStatement<P>
sql(["SELECT …"]) literal tuple [S] SqlStatement<P>
sql`SELECT ${val}` multi-part template string

TypeScript always infers TemplateStringsArray for tagged template strings arguments, which prevents literal-type inference. Use the plain string or array form when compile-time SQL validation is needed.

Plain string / array forms (type-safe)

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

// Plain string — captures literal type, validates SQL, infers params
const q1 = sql("SELECT * FROM users WHERE id = ?");
// typeof q1 → SqlStatement<[unknown]>

// Array form — identical inference via mutable literal tuple
const q2 = sql(["SELECT * FROM users WHERE id = ?"]);
// typeof q2 → SqlStatement<[unknown]>

// TypeScript enforces the params array:
await client.query(q1, [42]);   // ✅ one param — ok
await client.query(q1);         // ❌ compile error — params required
await client.query(q1, [1, 2]); // ❌ compile error — too many params

// Invalid SQL → never (compile error at the call site)
const bad = sql("SELECT * WHERE id = ?");
//    ^^^  never — missing FROM

Tagged template form (syntax highlighting)

// Returns string — no compile-time param checking
const q3 = sql`SELECT * FROM users WHERE id = ?`;
// typeof q3 → string

// Interpolations return string too
const table = 'users';
const q4 = sql`SELECT * FROM ${table} LIMIT 10`;
// typeof q4 → string  (runtime concatenation)

Use the tagged template form for syntax highlighting in editors that support it. For type safety, use sql("…") or sql(["…"]).

Type system

SQLParams<S>

Resolves to a tuple of unknown whose length equals the number of parameters in S. Returns never if S is structurally invalid SQL or if $N parameters are not sequential.

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

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

type B = SQLParams<'UPDATE t SET x = $1, y = $2 WHERE id = $3'>;
//   ^-- [unknown, unknown, unknown]

type C = SQLParams<'SELECT *'>;
//   ^-- never  (missing FROM)

type D = SQLParams<'SELECT * FROM t WHERE id = $3'>;
//   ^-- never  ($1 and $2 are missing — gap in sequence)

Validated statement forms:

Statement Required clauses
SELECT FROM
UPDATE table name, SET
DELETE FROM, table name
INSERT INTO, VALUES or sub-SELECT
CREATE TABLE, table name

Optional clauses (JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET) are accepted after the required ones.

ValidSQL<S>

S if the SQL is structurally valid, never otherwise.

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

function runQuery<S extends string>(sql: ValidSQL<S>, params: SQLParams<S>) { … }

runQuery('SELECT * FROM users', []);  // ✅
runQuery('SELECT *', []);             // ❌ compile error — missing FROM

SqlStatement<P>

A branded string type that carries the parameter tuple P as a phantom type. Produced by sql("…") and sql(["…"]); consumed by the typed SqlClient overloads.

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

declare const stmt: SqlStatement<[unknown, unknown]>;
// assignable to string, but plain string is NOT assignable to SqlStatement

InferSQLParams<S>

Alias for SQLParams<S>.

Adapters

SQLite

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

const client = await sqliteAdapter(':memory:').create();            // in-memory
const client2 = await sqliteAdapter('./path/to/db.sqlite').create(); // file

Peer dependency: better-sqlite3 >=9.0.0

PostgreSQL

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

const client = await pgAdapter('postgresql://user:pass@localhost:5432/mydb').create();
const client2 = await pgAdapter({ host: 'localhost', database: 'mydb' }).create();

Peer dependency: pg >=8.0.0

MySQL / MariaDB

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

const client = await mysqlAdapter('mysql://user:pass@localhost:3306/mydb').create();
const client2 = await mysqlAdapter({ host: 'localhost', database: 'mydb' }).create();

Peer dependency: mysql2 >=3.0.0

Adapter table

Import path Driver Peer dependency
@playwright-labs/sql-core/sqlite SQLite better-sqlite3 >=9.0.0
@playwright-labs/sql-core/pg PostgreSQL pg >=8.0.0
@playwright-labs/sql-core/mysql MySQL / MariaDB mysql2 >=3.0.0

All peer dependencies are optional — install only the driver you use.

CLI

The package ships a pull CLI that introspects a live database and generates TypeScript row-type interfaces — the same db-types.ts file that @playwright-labs/ts-plugin-sql reads via its schemaFile option.

The CLI is exposed under two bin names: @playwright-labs/sql-core and playwright-labs-sql-core.

pnpm add -D @playwright-labs/sql-core

Install it as a direct devDependency — pnpm only links bins of direct dependencies into node_modules/.bin. npm users can run npx playwright-labs-sql-core pull ... without an explicit install.

pnpm sql-core pull --adapter <sqlite|pg|mysql> --url <url> [--out <file>]

Flags: -a, --adapter, -u, --url, -o, --out. Without --out the types are printed to stdout.

pnpm sql-core pull --adapter sqlite --url ./dev.db --out ./src/db-types.ts
pnpm sql-core pull --adapter pg     --url postgresql://user:pass@localhost/mydb --out ./src/db-types.ts
pnpm sql-core pull --adapter mysql  --url mysql://user:pass@localhost/mydb --out ./src/db-types.ts

The generated file starts with a header and declares one interface per table:

// Auto-generated by @playwright-labs/sql-core
// Adapter : sqlite
// Source  : ./dev.db
// Generated: 2026-01-01T00:00:00.000Z
// Do not edit this file manually — re-run `@playwright-labs/sql-core pull` to refresh.

/** Row type for the `users` table */
export interface UsersRow {
  id: number;
  name: string;
  email: string | null;
}

/** All table row types, keyed by table name. */
export type Tables = {
  users: UsersRow;
};

API reference

sql

// Tagged template — always returns string (no compile-time validation)
sql`SELECT * FROM users WHERE id = ?`  // → string

// Plain string — validates SQL, infers param tuple
sql("SELECT * FROM users WHERE id = ?")  // → SqlStatement<[unknown]>

// Array form — identical to plain string, useful when SQL is stored separately
sql(["SELECT * FROM users WHERE id = ?"])  // → SqlStatement<[unknown]>
sql(["SELECT * FROM ", " WHERE id = ?"])   // → string (multi-element = dynamic)

SqlClient

interface SqlClient {
  // Typed overloads — enforce params array via SqlStatement brand
  query<T = Row, P extends readonly [unknown, ...unknown[]]>(
    sql: SqlStatement<P>,
    params: readonly [...P],
  ): Promise<QueryResult<T>>;

  // Fallback — plain string or no-param statement
  query<T = Row>(sql: string, params?: unknown[]): Promise<QueryResult<T>>;

  execute<P extends readonly [unknown, ...unknown[]]>(
    sql: SqlStatement<P>,
    params: readonly [...P],
  ): Promise<void>;

  execute(sql: string, params?: unknown[]): Promise<void>;

  close(): Promise<void>;
}

QueryResult<T>

interface QueryResult<T> {
  rows: T[];
  rowCount: number;
  command?: string; // e.g. "SELECT", "INSERT 0 1"
}

SqlAdapter

interface SqlAdapter {
  create(): Promise<SqlClient>;
}

Custom adapter

Implement SqlAdapter to add support for any other database driver:

import type { SqlAdapter, SqlClient, QueryResult, Row } from '@playwright-labs/sql-core';

export function myAdapter(url: string): SqlAdapter {
  return {
    async create(): Promise<SqlClient> {
      const conn = await MyDriver.connect(url);
      return {
        async query<T = Row>(sql: string, params?: unknown[]): Promise<QueryResult<T>> {
          const result = await conn.query(sql, params);
          return { rows: result.rows as T[], rowCount: result.rowCount };
        },
        async execute(sql: string, params?: unknown[]): Promise<void> {
          await conn.query(sql, params);
        },
        async close(): Promise<void> {
          await conn.end();
        },
      } as SqlClient;
    },
  };
}

License

MIT