@ochorocho/playwright-db-connector

Playwright fixtures for database connectivity. Verify that your CRUD application actually writes to the database.

Supports PostgreSQL, MySQL, MariaDB, and SQLite.

Install

This package is hosted on GitHub Packages. Configure the @ochorocho scope to use the GitHub registry by adding this to your project's .npmrc:

@ochorocho:registry=https://npm.pkg.github.com

Then install the package:

npm install @ochorocho/playwright-db-connector

Install the driver for your database:

# PostgreSQL
npm install pg

# MySQL or MariaDB
npm install mysql2

# SQLite
npm install better-sqlite3

Quick Start

1. Configure your database in playwright.config.ts

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

export default defineConfig({
  use: {
    dbConfig: {
      client: 'pg',
      connection: {
        host: 'localhost',
        port: 5432,
        user: 'test',
        password: 'test',
        database: 'myapp_test',
      },
      seedFiles: ['./tests/fixtures/schema.sql'],
    },
  },
});

2. Use db in your tests

import { test, expect } from '@ochorocho/playwright-db-connector';

test('user is persisted after registration', async ({ db, page }) => {
  await page.goto('/register');
  await page.fill('[name=email]', '[email protected]');
  await page.fill('[name=password]', 'secret123');
  await page.click('button[type=submit]');

  // Verify the user was stored in the database
  await expect(db).toHaveRecord('users', { email: '[email protected]' });
});

Each test runs inside a transaction that is automatically rolled back, giving you perfect isolation with zero cleanup.

Configuration

use: {
  dbConfig: {
    // Required: database driver
    client: 'pg',             // 'pg' | 'mysql2' | 'better-sqlite3' | 'sqlite3'

    // Required: connection details (object or connection string)
    connection: {
      host: 'localhost',
      port: 5432,
      user: 'test',
      password: 'test',
      database: 'myapp_test',
    },
    // OR: connection: process.env.DATABASE_URL,
    // OR: connection: { filename: ':memory:' },  // SQLite

    // Optional: cleanup strategy (default: 'transaction')
    cleanupStrategy: 'transaction',  // 'transaction' | 'delete' | 'truncate' | 'none'

    // Optional: SQL files to run once per worker on startup
    seedFiles: ['./schema.sql'],

    // Optional: CSV files (TYPO3 format) to import once per worker on startup
    seedCsvFiles: ['./seed.csv'],

    // Optional: connection pool (ignored for SQLite)
    pool: { min: 0, max: 5 },

    // Optional: pass-through to knex
    knexConfig: {},
  },
}

Cleanup Strategies

| Strategy | How it works | Speed | Use when | |---|---|---|---| | transaction (default) | Wraps each test in a transaction, rolls back after | Fastest | Most cases | | delete | Tracks haveInDatabase inserts, deletes them after | Medium | DDL in tests (CREATE TABLE) | | truncate | Truncates all touched tables after each test | Slower | Need a completely clean slate | | none | No cleanup | - | You manage state yourself |

Multi-Project (multiple databases)

export default defineConfig({
  projects: [
    {
      name: 'postgres',
      use: {
        dbConfig: { client: 'pg', connection: { ... } },
      },
    },
    {
      name: 'sqlite',
      use: {
        dbConfig: { client: 'better-sqlite3', connection: { filename: ':memory:' } },
      },
    },
  ],
});

API

Assertions (Playwright-style matchers)

await expect(db).toHaveRecord('users', { email: '[email protected]' });
await expect(db).not.toHaveRecord('users', { email: '[email protected]' });
await expect(db).toHaveRecordCount(5, 'users');
await expect(db).toHaveRecordCount(2, 'users', { active: true });

Assertions (Codeception-style methods)

await db.seeInDatabase('users', { email: '[email protected]' });
await db.dontSeeInDatabase('users', { email: '[email protected]' });
await db.seeNumRecords(5, 'users');
await db.seeNumRecords(2, 'users', { active: true });

Retrieval

const email = await db.grabFromDatabase('users', 'email', { id: 1 });
const names = await db.grabColumnFromDatabase('users', 'name', { active: true });
const entries = await db.grabEntriesFromDatabase('users', { role: 'admin' });
const count = await db.grabNumRecords('users');

Manipulation

// Insert (tracked for auto-cleanup, returns primary key)
const userId = await db.haveInDatabase('users', {
  name: 'Alice',
  email: '[email protected]',
});

// Update (returns number of affected rows)
await db.updateInDatabase('users', { active: false }, { id: userId });

// Delete (returns number of deleted rows)
await db.deleteFromDatabase('users', { id: userId });

Raw SQL

// Use ? for parameter bindings (knex normalizes across all databases)
const result = await db.query('SELECT * FROM users WHERE id = ?', [1]);

Transactions

await db.transaction(async (trx) => {
  const userId = await trx.haveInDatabase('users', { name: 'Alice', email: '[email protected]' });
  await trx.haveInDatabase('posts', { user_id: userId, title: 'First Post' });
});

Seed from SQL file

await db.loadSqlFile('./fixtures/extra-data.sql');

CSV datasets (TYPO3-style)

Seed and assert using CSV files in the TYPO3 testing framework format. Each table section starts with the table name in the first column, followed by column headers. Data rows begin with an empty first field. Multiple tables can be in one file.

"users","uid","name","email","active"
,1,"Alice","[email protected]",1
,2,"Bob","[email protected]",1
"posts","uid","user_id","title"
,1,1,"Hello World"

Special values: \NULL is converted to SQL NULL.

Import CSV data:

await db.importCsvFile('./fixtures/seed.csv');

Assert database matches CSV:

// Throws with a detailed diff if any row doesn't match
await db.assertCsvDataSet('./fixtures/expected.csv');

Auto-seed on startup (in playwright.config.ts):

dbConfig: {
  seedFiles: ['./schema.sql'],       // SQL schema first
  seedCsvFiles: ['./seed.csv'],      // Then CSV data
}

Escape hatch (raw knex)

const knex = db.knex;
await knex.schema.createTable('temp', (t) => {
  t.increments('id');
  t.string('name');
});

Composing with existing fixtures

If you already have custom Playwright fixtures, merge them:

import { test as dbTest, expect as dbExpect } from '@ochorocho/playwright-db-connector';
import { test as base, mergeTests, mergeExpects } from '@playwright/test';

const myTest = base.extend({
  myFixture: async ({}, use) => { await use('hello'); },
});

export const test = mergeTests(myTest, dbTest);
export const expect = mergeExpects(dbExpect);

Development

# Install dependencies
mise install   # Installs Node.js version from .mise.toml
npm install

# Build
npm run build

# Run unit tests
npm run test:unit

# Run SQLite integration tests
npm run test:integration

# Run E2E Playwright tests (uses the plugin itself)
npm run test:e2e

# Start Docker databases for multi-DB integration tests
docker compose -f tests/docker-compose.yml up -d
TEST_PG=1 TEST_MYSQL=1 npm run test:integration

License

MIT