npm package: @ochorocho/playwright-db-connector
Description: Playwright fixtures for database connectivity with PostgreSQL, MariaDB, MySQL, and SQLite
Latest version: 0.0.2

# @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](https://docs.github.com/en/packages/working-with-a-github-packages-registry/working-with-the-npm-registry). 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:

```bash
npm install @ochorocho/playwright-db-connector
```

Install the driver for your database:

```bash
# 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`

```typescript
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

```typescript
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]', 'alice@example.com');
  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: 'alice@example.com' });
});
```

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

## Configuration

```typescript
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)

```typescript
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)

```typescript
await expect(db).toHaveRecord('users', { email: 'alice@example.com' });
await expect(db).not.toHaveRecord('users', { email: 'deleted@example.com' });
await expect(db).toHaveRecordCount(5, 'users');
await expect(db).toHaveRecordCount(2, 'users', { active: true });
```

### Assertions (Codeception-style methods)

```typescript
await db.seeInDatabase('users', { email: 'alice@example.com' });
await db.dontSeeInDatabase('users', { email: 'deleted@example.com' });
await db.seeNumRecords(5, 'users');
await db.seeNumRecords(2, 'users', { active: true });
```

### Retrieval

```typescript
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

```typescript
// Insert (tracked for auto-cleanup, returns primary key)
const userId = await db.haveInDatabase('users', {
  name: 'Alice',
  email: 'alice@example.com',
});

// 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

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

### Transactions

```typescript
await db.transaction(async (trx) => {
  const userId = await trx.haveInDatabase('users', { name: 'Alice', email: 'a@test.com' });
  await trx.haveInDatabase('posts', { user_id: userId, title: 'First Post' });
});
```

### Seed from SQL file

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

### CSV datasets (TYPO3-style)

Seed and assert using CSV files in the [TYPO3 testing framework format](https://docs.typo3.org/m/typo3/reference-coreapi/main/en-us/Testing/FunctionalTesting/Index.html). 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.

```csv
"users","uid","name","email","active"
,1,"Alice","alice@example.com",1
,2,"Bob","bob@example.com",1
"posts","uid","user_id","title"
,1,1,"Hello World"
```

Special values: `\NULL` is converted to SQL NULL.

**Import CSV data:**
```typescript
await db.importCsvFile('./fixtures/seed.csv');
```

**Assert database matches CSV:**
```typescript
// 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`):
```typescript
dbConfig: {
  seedFiles: ['./schema.sql'],       // SQL schema first
  seedCsvFiles: ['./seed.csv'],      // Then CSV data
}
```

### Escape hatch (raw knex)

```typescript
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:

```typescript
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

```bash
# 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