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:
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
| Variable | Required | Description |
|---|---|---|
PW_DB_TYPE | Yes | Database engine: postgresql, mysql, mssql, or sqlite |
PW_DB_HOST | No | Database server host (not needed for SQLite) |
PW_DB_PORT | No | Database server port |
PW_DB_NAME | Yes | Database name (or file path for SQLite) |
PW_DB_USERNAME | No | Database username |
PW_DB_PASSWORD | No | Database password |
Configuration via environments.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
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
| Option | Type | Default | Description |
|---|---|---|---|
type | 'postgresql' | 'mysql' | 'mssql' | 'sqlite' | — | Database engine type |
host | string | — | Database server host |
port | number | — | Database server port |
database | string | — | Database name (or file path for SQLite) |
username | string | — | Database username |
password | string | — | Database password |
connectionTimeout | number | 10000 | Connection timeout in ms |
queryTimeout | number | 30000 | Query execution timeout in ms |
encrypt | boolean | true | Encrypt connection (MSSQL only) |
trustServerCertificate | boolean | false | Trust server certificate (MSSQL, useful for local dev) |
Usage
Querying Data
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)
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,$2natively - MySQL — Converts to
?placeholders - MSSQL — Converts to
@p1,@p2named 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.
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 placeTable and column names must be plain identifiers (optionally schema-qualified). Values are always parameters. See Test Data and Automatic Cleanup.
Lifecycle
- Setup — Creates a connection pool, verifies connectivity with a ping query
- Use — Provides the
databaseClientto the test - Teardown — Drains the pool and closes all connections
Error Handling
| Error | When |
|---|---|
FixtureInitError | Connection failure (includes host, port, timeout) |
FixtureOperationError | Query execution failure (includes SQL statement) |
// 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 existSupported Drivers
| Type | Library | Notes |
|---|---|---|
postgresql | pg | Connection pooling via pg.Pool |
mysql | mysql2/promise | Connection pooling with waitForConnections |
mssql | mssql | Connection pooling with encrypt/trust options |
sqlite | better-sqlite3 | Synchronous driver, file-based or in-memory |