@playwright-labs/fixture-sql

SQL connection fixture for Playwright — driver-agnostic, auto-cleanup, zero boilerplate.

Why this package exists

End-to-end tests that touch a database share a painful pattern: you open a connection before each test, run queries, and have to remember to close the connection and clean up state afterwards. If a test fails midway the cleanup code never runs and the next test starts with stale data or a leaked connection.

@playwright-labs/fixture-sql wires a live SQL connection into Playwright's fixture system. The connection is opened automatically before each test and closed after it — even on failure. Every test gets its own isolated client so tests cannot interfere with each other through shared connection state.

Problems it solves

| Without this package | With this package | |---|---| | Manual beforeEach / afterEach for every test file | One test.use() line per file | | Leaked connections on test failure | Automatic close guaranteed by Playwright teardown | | Copy-pasted adapter boilerplate for pg / mysql / sqlite | Three ready-made adapters, one API | | No type safety on row shapes | SQLType compile-time SQL validation and typed row interfaces | | Stale data leaking between tests | Each test gets a fresh connection (or :memory: DB for SQLite) |

Package ecosystem

This package is the test-layer entry point into a three-package system:

┌─────────────────────────────────────────────────────────────────┐
│  @playwright-labs/sql-core                                      │
│  ─────────────────────────────────────────────────────────────  │
│  • sql("…") / sql`…` / sql(["…"]) tag function                  │
│  • SqlClient / SqlAdapter interfaces                            │
│  • SQLType.ts — compile-time FSM that validates SQL grammar     │
│    and infers parameter tuples from ? / $N placeholders         │
│  • Adapter implementations: sqlite / pg / mysql                 │
└──────────────┬──────────────────────────┬───────────────────────┘
               │                          │
               ▼                          ▼
┌──────────────────────────┐  ┌──────────────────────────────────┐
│  @playwright-labs/       │  │  @playwright-labs/ts-plugin-sql  │
│  fixture-sql             │  │  ──────────────────────────────  │
│  ──────────────────────  │  │  TypeScript language service     │
│  • test fixture with     │  │  plugin — runs inside tsserver:  │
│    auto-open/close       │  │  • SQL keyword autocomplete      │
│  • useSql() for extra    │  │  • Table / column completions    │
│    connections           │  │    (from your schema file)       │
│  • Custom matchers       │  │  • Structural diagnostics        │
│  • pull CLI for schema   │  │  • Hover info for tables/columns │
│    generation            │  │                                  │
└──────────────────────────┘  └──────────────────────────────────┘

How the three packages connect

1. sql-core is the foundation — it exports the sql function, SqlClient interface, and the compile-time type system. Both other packages depend on it.

2. fixture-sql (this package) adds Playwright integration on top of sql-core: it manages connection lifecycle, exposes the sql fixture in tests, and re-exports all sql-core types so you only need one import.

3. ts-plugin-sql adds editor intelligence. It reads your database schema (generated by this package's pull CLI) and provides autocomplete, diagnostics, and hover info inside sql templates. It also re-exports the sql function from sql-core.

Typical workflow

                    ┌── pnpm pull ──────────────────────────────────┐
                    │   introspects live DB, writes db-types.ts      │
                    ▼                                                 │
live DB  ──►  db-types.ts  ──►  ts-plugin-sql tsconfig  ──►  editor IDE features
                    │
                    └──►  import type { UsersRow }  ──►  typed query results

// 1. Generate schema types once (or add to CI):
//    pnpm fixture-sql pull --adapter sqlite --url ./dev.db --out ./src/db-types.ts

// 2. Configure the language service plugin in tsconfig.json:
//    { "plugins": [{ "name": "@playwright-labs/ts-plugin-sql", "schemaFile": "./src/db-types.ts" }] }

// 3. Write tests with full type safety + editor support:
import { test, expect } from '@playwright-labs/fixture-sql';
import { sqliteAdapter } from '@playwright-labs/fixture-sql/sqlite';
import { sql } from '@playwright-labs/ts-plugin-sql'; // re-exports sql from sql-core
import type { UsersRow } from './db-types.js';

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

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

The sql function and type safety

sql-core exports a sql function with three calling forms:

| Form | Return type | Compile-time validation | |---|---|---| | sql`SELECT …` | string | None (TypeScript limitation) | | sql("SELECT …") | SqlStatement<P> | Full — validates SQL structure, infers param count | | sql(["SELECT …"]) | SqlStatement<P> | Full — same as plain string |

Use sql("…") or sql(["…"]) when you want the compiler to catch missing FROM clauses, wrong UPDATE syntax, or calls with the wrong number of params. The tagged template form is useful for editor syntax highlighting.

Features

• Driver-agnostic fixture — works with PostgreSQL (pg), MySQL (mysql2), and SQLite (better-sqlite3) via optional peer dependencies; bring your own driver
• Auto-cleanup — every connection opened through sql or useSql is closed after the test, even when the test throws
• Test isolation — each test gets its own SqlClient; share nothing between tests by default
• Multiple connections per test — useSql(adapter) opens additional connections on demand, all tracked for teardown
• Compile-time SQL validation — SQLType.ts models SQL grammar as a TypeScript FSM; SQLParams<S> resolves to the correct parameter tuple and is never for structurally invalid SQL
• Sequential $N validation — $3 without $1 and $2 is a compile-time error
• ? and $N parameter styles — both MySQL/SQLite (?) and PostgreSQL ($1, $2, …) are inferred correctly
• pull CLI — introspects a live schema and generates TypeScript row-type interfaces so your tests never go out of sync with the DB

Installation

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

Quick start

1. Replace the base test import

// tests/my.spec.ts
import { test, expect } from '@playwright-labs/fixture-sql';
import { sqliteAdapter } from '@playwright-labs/fixture-sql/sqlite';
import { sql } from '@playwright-labs/fixture-sql'; // re-exported from sql-core

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

test('inserts and reads a row', 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');
});

2. PostgreSQL

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

test.use({
  sqlAdapter: pgAdapter('postgresql://user:pass@localhost:5432/testdb'),
});

test('users table exists', async ({ sql: db }) => {
  const { rows } = await db.query<{ count: string }>(
    'SELECT COUNT(*) AS count FROM users',
  );
  expect(Number(rows[0]!.count)).toBeGreaterThanOrEqual(0);
});

3. MySQL

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

test.use({
  sqlAdapter: mysqlAdapter('mysql://user:pass@localhost:3306/testdb'),
});

test('products exist', async ({ sql: db }) => {
  const { rows } = await db.query('SELECT id FROM products LIMIT 1');
  expect(rows.length).toBeGreaterThan(0);
});

Adapters

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

All three are optional — install only the driver you use.

API reference

test (extended Playwright test)

Drop-in replacement for @playwright/test's test. Adds three fixtures:

sql: SqlClient

A ready-to-use connection for the current test. Auto-opened and auto-closed.

test('example', async ({ sql: db }) => {
  const { rows, rowCount } = await db.query('SELECT * FROM orders');
  await db.execute('DELETE FROM sessions WHERE expired = 1');
});

Requires sqlAdapter to be configured first via test.use().

useSql(adapter): Promise<SqlClient>

Opens an additional connection on demand and registers it for automatic teardown.

test('two databases', async ({ useSql }) => {
  const primary = await useSql(pgAdapter(primaryUrl));
  const replica = await useSql(pgAdapter(replicaUrl));

  await primary.execute("INSERT INTO events VALUES (1, $1)", ['login']);
  const { rows } = await replica.query('SELECT * FROM events');
  expect(rows).toHaveLength(1);
});

sqlAdapter: SqlAdapter (option)

Configuration option. Set once per file or describe block:

// applies to all tests in this file
test.use({ sqlAdapter: pgAdapter(process.env.DATABASE_URL!) });

// override for one describe block
test.describe('read-only suite', () => {
  test.use({ sqlAdapter: pgAdapter(process.env.REPLICA_URL!) });
});

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>;
}

Compile-time SQL validation

SQLType.ts implements a finite-state-machine parser over TypeScript template-literal types. Import the utility types when you want the compiler to catch SQL mistakes before tests even run.

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

SQLParams<S>

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

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)

Supports both ? (SQLite / MySQL) and $N (PostgreSQL) parameter styles. Validated statements include:

• SELECT … FROM … [JOIN] [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT] [OFFSET]
• UPDATE … SET … [WHERE]
• DELETE FROM … [WHERE]
• INSERT INTO … VALUES (…)
• CREATE TABLE … (…)

ValidSQL<S>

S if the SQL is structurally valid, never otherwise.

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

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

InferSQLParams<S>

Alias for SQLParams<S>.

pull CLI — schema-to-types generator

Introspects a live database and emits TypeScript interface declarations for every table so your tests can use typed row shapes without writing them by hand.

# print to stdout
pnpm pull --adapter sqlite --url ./dev.db
pnpm pull --adapter pg     --url postgresql://user:pass@localhost/mydb
pnpm pull --adapter mysql  --url mysql://user:pass@localhost/mydb

# write to a file
pnpm pull --adapter sqlite --url ./dev.db --out ./src/db-types.ts

Example output:

// Auto-generated by @playwright-labs/fixture-sql/bin/pull

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

/** Row type for the `posts` table */
export interface PostsRow {
  id: number;
  user_id: number;
  title: string;
  body: string | null;
}

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

Use the generated types in your tests:

import type { UsersRow } from './db-types.js';

test('typed rows', async ({ sql: db }) => {
  const { rows } = await db.query<UsersRow>('SELECT * FROM users');
  expect(rows[0]!.email).toBeNull(); // type-safe
});

The same file can be passed to @playwright-labs/ts-plugin-sql via schemaFile to enable schema-aware autocomplete and hover in your editor.

Custom adapter

Implement SqlAdapter to connect any database:

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

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

Test isolation patterns

SQLite in-memory (fastest)

Each create() call opens a brand-new :memory: database — zero cleanup required.

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

Shared file database

Use a fixed file path when tests need to read data seeded before the suite.

test.use({ sqlAdapter: sqliteAdapter('./fixtures/seed.db') });

PostgreSQL with per-test schema

For full isolation with a shared Postgres instance, create a unique schema per test:

import { randomUUID } from 'node:crypto';

test.use({ sqlAdapter: pgAdapter(process.env.DATABASE_URL!) });

test.beforeEach(async ({ sql: db }) => {
  const schema = `test_${randomUUID().replace(/-/g, '')}`;
  await db.execute(`CREATE SCHEMA ${schema}`);
  await db.execute(`SET search_path TO ${schema}`);
});

License

MIT