Testing

Testing

PawQL provides a DummyAdapter for testing without a real database connection. This adapter records all executed SQL so you can assert against the generated queries.

Installation

DummyAdapter is available via a separate subpath import, kept out of production code:

// ✅ Correct — import from the testing subpath
import { DummyAdapter } from 'pawql/testing';

// ❌ Wrong — not available from the main import
import { DummyAdapter } from 'pawql'; // Error!

Why is it separate? So that DummyAdapter doesn't end up in your production bundle. See Philosophy below.

Basic Usage

import { test } from 'node:test';
import assert from 'node:assert';
import { createDB } from 'pawql';
import { DummyAdapter } from 'pawql/testing';

const schema = {
  users: {
    id: Number,
    name: String,
    email: String,
  }
};

test('should generate correct SELECT SQL', async () => {
  const adapter = new DummyAdapter(); // defaults to postgres dialect
  // For dialect-specific assertions: new DummyAdapter('mysql') or new DummyAdapter('sqlite')
  const db = createDB(schema, adapter);

  await db.query('users')
    .select('id', 'name')
    .where({ id: 1 })
    .execute();

  // Check the generated SQL
  const lastLog = adapter.logs[adapter.logs.length - 1]!;
  assert.strictEqual(lastLog.sql, 'SELECT "id", "name" FROM "users" WHERE "id" = $1');
  assert.deepStrictEqual(lastLog.params, [1]);
});

Dialect-aware: new DummyAdapter('postgres'|'mysql'|'sqlite') controls quoting ("id" vs `id`), RETURNING, ILIKE/ON CONFLICT/FULL JOIN guards, and DDL type mapping.


## DummyAdapter API

### `adapter.logs`

An array containing all executed queries:

```typescript
type LogEntry = {
  sql: string;     // The SQL string that was executed
  params: any[];   // Parameter values
};

adapter.logs; // LogEntry[]

Inspecting Logs

const adapter = new DummyAdapter();
const db = createDB(schema, adapter);

await db.query('users').insert({ id: 1, name: 'Alice' }).execute();
await db.query('users').where({ id: 1 }).execute();

console.log(adapter.logs);
// [
//   { sql: 'INSERT INTO "users" ...', params: [1, 'Alice'] },
//   { sql: 'SELECT * FROM "users" WHERE "id" = $1', params: [1] }
// ]

// Access the last log entry
const lastQuery = adapter.logs[adapter.logs.length - 1]!;

Shared Logs (Transactions)

When testing transactions, DummyAdapter automatically shares logs across transaction adapters:

test('transaction logs BEGIN/COMMIT', async () => {
  const adapter = new DummyAdapter();
  const db = createDB(schema, adapter);

  await db.transaction(async (tx) => {
    await tx.query('users').insert({ id: 1 }).execute();
  });

  assert.strictEqual(adapter.logs[0]!.sql, 'BEGIN');
  assert.ok(adapter.logs[1]!.sql.includes('INSERT'));
  assert.strictEqual(adapter.logs[2]!.sql, 'COMMIT');
});

Testing Patterns

Pattern 1: Verify SQL Generation

test('generates correct SQL', async () => {
  const adapter = new DummyAdapter();
  const db = createDB(schema, adapter);

  await db.query('users')
    .where({ age: { gt: 18 } })
    .orderBy('name', 'ASC')
    .limit(10)
    .execute();

  const log = adapter.logs[0]!;
  assert.strictEqual(
    log.sql,
    'SELECT * FROM "users" WHERE "age" > $1 ORDER BY "name" ASC LIMIT 10'
  );
  assert.deepStrictEqual(log.params, [18]);
});

Pattern 2: Verify Parameters

test('passes correct parameters', async () => {
  const adapter = new DummyAdapter();
  const db = createDB(schema, adapter);

  await db.query('users')
    .insert({ id: 1, name: 'Alice', email: 'alice@test.com' })
    .execute();

  const log = adapter.logs[0]!;
  assert.strictEqual(log.params.length, 3);
  assert.strictEqual(log.params[0], 1);
  assert.strictEqual(log.params[1], 'Alice');
  assert.strictEqual(log.params[2], 'alice@test.com');
});

Pattern 3: Fresh Adapter per Test

// ✅ Each test gets a fresh adapter — logs don't leak between tests
test('test 1', async () => {
  const adapter = new DummyAdapter();
  const db = createDB(schema, adapter);
  // ...
});

test('test 2', async () => {
  const adapter = new DummyAdapter();
  const db = createDB(schema, adapter);
  // ... adapter.logs starts empty
});

Pattern 4: DDL Testing (dialect-aware)

test('creates correct table DDL', async () => {
  const adapter = new DummyAdapter('postgres');
  const db = createDB({
    products: {
      id: { type: Number, primaryKey: true },
      name: String,
      price: Number,
      meta: json<{a:string}>(),
      uid: uuid,
    }
  }, adapter);

  await db.createTables();

  const sql = adapter.logs[0]!.sql;
  assert.ok(sql.includes('CREATE TABLE IF NOT EXISTS "products"'));
  assert.ok(sql.includes('"id" INTEGER PRIMARY KEY'));
  assert.ok(sql.includes('"name" TEXT NOT NULL'));
  assert.ok(sql.includes('"price" INTEGER NOT NULL'));
  assert.ok(sql.includes('"meta" JSONB NOT NULL')); // MySQL→JSON, SQLite→TEXT
  assert.ok(sql.includes('"uid" UUID NOT NULL')); // MySQL→VARCHAR(36), SQLite→TEXT
});

test('arrayType only on postgres', async () => {
  const pg = new DummyAdapter('postgres');
  const mysql = new DummyAdapter('mysql');
  assert.doesNotThrow(async () => {
    const r = createMigrationRunner(pg);
    await r.createTable('t', { tags: arrayType(String) });
  });
  await assert.rejects(async () => {
    const r = createMigrationRunner(mysql);
    await r.createTable('t', { tags: arrayType(String) });
  }, /ArrayType is only supported on PostgreSQL/);
});


## Compatible Test Runners

PawQL tests use the `node:test` module, which is compatible with:

- **Node.js** 18+ (`npx tsx --test test/*.test.ts`)
- **Bun** (`bun test`)

```json
// package.json
{
  "scripts": {
    "test": "npx tsx --test test/*.test.ts",
    "test:bun": "bun test"
  }
}

Philosophy

DummyAdapter is separated from production exports because:

  1. Bundle size — Test utilities shouldn't end up in production builds
  2. Clean API — Users only see what they need in autocomplete
  3. Separation of concerns — Production code vs test utilities
  4. Industry standard — Similar to @angular/core/testing, react/test-utils

Integration Tests (Docker)

PawQL also includes comprehensive integration tests that run against a real PostgreSQL database via Docker. These tests verify every feature end-to-end.

Prerequisites

  • Docker and Docker Compose installed

Running Integration Tests

# 1. Start PostgreSQL container
npm run test:docker:up

# 2. Run integration tests
npm run test:integration

# 3. Stop the container when done
npm run test:docker:down

What's Tested

The integration test suite covers:

  • DDL: Table creation, column types, constraints
  • CRUD: INSERT (single/batch), SELECT, UPDATE, DELETE
  • Filtering: All WHERE operators (gt, lt, gte, lte, in, notIn, like, ilike, between, null)
  • ORDER BY: ASC/DESC, multiple columns
  • LIMIT / OFFSET: Pagination
  • JOINs: INNER, LEFT, with filtering
  • Transactions: Commit, rollback, multiple operations
  • GROUP BY + HAVING: Aggregation queries
  • Upsert: ON CONFLICT DO NOTHING / DO UPDATE
  • Raw SQL: Direct SQL execution
  • Soft Delete: softDelete(), restore(), withTrashed(), onlyTrashed()
  • Advanced Types: JSONB, arrays, enums, booleans, nullable columns
  • Pool Management: Pool statistics

Docker Compose Configuration

The test database uses port 5433 (not the default 5432) to avoid conflicts with local PostgreSQL:

# docker-compose.test.yml
services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: pawql_test
      POSTGRES_PASSWORD: pawql_test_pass
      POSTGRES_DB: pawql_test
    ports:
      - "5433:5432"
    tmpfs:
      - /var/lib/postgresql/data  # RAM-based for speed

Available Scripts

{
  "test": "Unit tests only (no Docker needed)",
  "test:unit": "Unit tests only",
  "test:integration": "Integration tests (requires Docker)",
  "test:all": "All tests (unit + integration)",
  "test:docker:up": "Start test database",
  "test:docker:down": "Stop test database"
}

Next Steps