Skip to content

Database Fixture ​

The databaseClient fixture provides a multi-dialect database client supporting PostgreSQL, MySQL, MSSQL, and SQLite. It exposes a unified interface with query() and execute() methods, handling connection pooling and lifecycle automatically.


What It Does ​

  • Connects to PostgreSQL, MySQL, MSSQL, or SQLite databases
  • Manages connection pools with configurable timeouts
  • Verifies connectivity on setup with a ping query
  • Provides parameterized query execution (prevents SQL injection)
  • Automatically normalizes parameter placeholders across dialects
  • Drains connection pools on teardown

Setup ​

Install ​

Install the driver for your database. Each one is an optional peer dependency, so you install only the ones you use:

bash
npm install --save-dev pg               # PostgreSQL
npm install --save-dev mysql2           # MySQL
npm install --save-dev mssql            # MSSQL
npm install --save-dev better-sqlite3   # SQLite (a native module)

Run it in a container ​

No database to connect to? Set PW_DB_CONTAINER=true (or container: true in config) and the fixture starts one with Testcontainers for the run, then removes it. Needs Docker and npm install --save-dev testcontainers. See Testcontainers.

Environment Variables ​

VariableRequiredDescription
PW_DB_TYPEYesDatabase engine: postgresql, mysql, mssql, or sqlite
PW_DB_HOSTNoDatabase server host (not needed for SQLite)
PW_DB_PORTNoDatabase server port
PW_DB_NAMEYesDatabase name (or file path for SQLite)
PW_DB_USERNAMENoDatabase username
PW_DB_PASSWORDNoDatabase password

Configuration via environments.json ​

json
{
  "environments": {
    "dev": {
      "database": {
        "type": "postgresql",
        "host": "localhost",
        "port": 5432,
        "database": "myapp_test",
        "username": "postgres",
        "password": "postgres",
        "connectionTimeout": 10000,
        "queryTimeout": 30000
      }
    }
  }
}

Configuration via playwright.config.ts ​

typescript
import { defineConfig } from '@playwright/test';

export default defineConfig({
  projects: [
    {
      name: 'integration',
      testMatch: '**/integration/**/*.spec.ts',
      use: {
        database: {
          type: 'postgresql',
          host: 'localhost',
          port: 5432,
          database: 'myapp_test',
          username: 'postgres',
          password: 'postgres',
        },
      },
    },
  ],
});

Configuration Options ​

OptionTypeDefaultDescription
type'postgresql' | 'mysql' | 'mssql' | 'sqlite'—Database engine type
hoststring—Database server host
portnumber—Database server port
databasestring—Database name (or file path for SQLite)
usernamestring—Database username
passwordstring—Database password
connectionTimeoutnumber10000Connection timeout in ms
queryTimeoutnumber30000Query execution timeout in ms
encryptbooleantrueEncrypt connection (MSSQL only)
trustServerCertificatebooleanfalseTrust server certificate (MSSQL, useful for local dev)

Usage ​

Querying Data ​

typescript
import { test, expect } from '@inluxc/propmaster';

test('query users', async ({ databaseClient }) => {
  const rows = await databaseClient.query<{ id: number; name: string }>(
    'SELECT id, name FROM users WHERE active = $1',
    [true]
  );

  expect(rows.length).toBeGreaterThan(0);
  expect(rows[0]).toHaveProperty('name');
});

Executing Statements (INSERT/UPDATE/DELETE) ​

typescript
test('insert a record', async ({ databaseClient }) => {
  const result = await databaseClient.execute(
    'INSERT INTO users (name, email) VALUES ($1, $2)',
    ['Bob', '[email protected]']
  );

  expect(result.affectedRows).toBe(1);
});

Parameter Placeholders ​

Always use PostgreSQL-style positional parameters ($1, $2, ...) regardless of the underlying database. The fixture automatically normalizes them:

  • PostgreSQL — Uses $1, $2 natively
  • MySQL — Converts to ? placeholders
  • MSSQL — Converts to @p1, @p2 named parameters
  • SQLite — Converts to ? placeholders

Automatic Cleanup ​

insert() writes rows and deletes them again when the test ends, pass or fail. The row is matched on key, else id when every row has one, else every inserted column. Raw SQL through execute() isn't tracked.

typescript
const user = testData.build(userFactory);                      // see Test Data
await databaseClient.insert('users', user);                     // one row, or an array
await databaseClient.insert('audit_log', rows, { key: 'ref' }); // match the cleanup on one column
await databaseClient.insert('countries', seed, { keep: true }); // leave it in place

Table and column names must be plain identifiers (optionally schema-qualified). Values are always parameters. See Test Data and Automatic Cleanup.


Lifecycle ​

  1. Setup — Creates a connection pool, verifies connectivity with a ping query
  2. Use — Provides the databaseClient to the test
  3. Teardown — Drains the pool and closes all connections

Error Handling ​

ErrorWhen
FixtureInitErrorConnection failure (includes host, port, timeout)
FixtureOperationErrorQuery execution failure (includes SQL statement)
typescript
// Connection failure example:
// FixtureInitError: database/connect — host: localhost, port: 5432, reason: Connection refused

// Query failure example:
// FixtureOperationError: database/query — sql: SELECT ..., reason: relation "users" does not exist

Supported Drivers ​

TypeLibraryNotes
postgresqlpgConnection pooling via pg.Pool
mysqlmysql2/promiseConnection pooling with waitForConnections
mssqlmssqlConnection pooling with encrypt/trust options
sqlitebetter-sqlite3Synchronous driver, file-based or in-memory

Released under the MIT License.