PostgreSQL Mocking Library

Common errors

• Error: Query not found.

  cause The executed query string (after normalization) does not match any queries previously added with `pg.add()`.
  fix Verify that the query string in `client.query()` exactly matches (after considering normalization) one of the queries added via `pg.add()`. Check for typos or unexpected parameter binding syntax.

• Error: Invalid number of values provided. Expected X, got Y.

  cause The number of values provided in the second argument of `client.query()` does not match the number of validation rules (second argument) provided during `pg.add()`.
  fix Ensure the array of values passed to `client.query()` has the same length as the array of validation rules provided to `pg.add()` for that specific query.

• Error: Value at index X failed validation.

  cause A value passed to `client.query()` did not pass the validation rule (type string or custom function) defined for its corresponding index in `pg.add()`.
  fix Review the validation rule for the specified index in `pg.add()` and confirm the value provided in `client.query()` meets that criteria (e.g., correct `typeof` or the custom validation function returns `true`).

Warnings

• gotcha pgmock2 normalizes SQL queries internally by disregarding whitespace and making them case-insensitive. This means the query string provided to `add` and `query` does not need to be an exact match, but users expecting strict string comparisons might encounter unexpected matches or mismatches.
  Fix: Be aware of the normalization behavior; ensure your `add` calls define queries that match the expected normalized form, or rely on the normalization for flexibility. Avoid overly specific whitespace/casing in your test query strings.
• gotcha The third parameter to the `add` method, which defines the query response, MUST strictly adhere to the `pg.QueryResponse` interface (i.e., contain `rowCount` and `rows` properties). Missing or incorrectly typed properties will lead to runtime errors when the mock query is executed.
  Fix: Always provide a response object with at least `rowCount: number` and `rows: any[]`. For example: `{ rowCount: 0, rows: [] }` for an empty result.
• gotcha Value validation in `add` uses either `typeof` strings (e.g., 'number', 'string') or custom functions. Mismatches between the validation rules defined in `add` and the actual values passed to `query` will result in an error being thrown.
  Fix: Ensure the values provided in the `query` method's second parameter precisely match the validation rules defined when adding the query. For custom functions, verify the function returns `true` for valid inputs.

Install

• npm install pgmock2 npm
• yarn add pgmock2 yarn
• pnpm add pgmock2 pnpm

Imports

• PgMock2
  wrong:

  const PgMock2 = require('pgmock2');

  correct:

  import PgMock2 from 'pgmock2';

  While `require('pgmock2').default` works, the direct `import` is preferred for ESM contexts. For CJS, `require('pgmock2')` directly returns the default export.
• PgMock2Client

  import PgMock2, { PgMock2Client } from 'pgmock2';
  // Or for the instance:
  const client = new PgMock2();

  PgMock2Client is the type returned by `pg.connect()`. The primary export is the PgMock2 class itself.
• QueryConfig
  wrong:

  import { QueryConfig } from 'pgmock2';

  correct:

  import type { QueryConfig } from 'pgmock2';

  Use `import type` when only importing types to ensure it's removed during transpilation and doesn't affect runtime.

Quickstart

This quickstart demonstrates how to set up mock queries with `pgmock2`, including parameter validation, and then execute those queries against a mock client.

import PgMock2 from 'pgmock2';

const pg = new PgMock2();

// Add a query with a number validation for the first parameter ($1)
pg.add('SELECT * FROM employees WHERE id=$1', ['number'], {
    rowCount: 1,
    rows: [
        { id: 1, name: 'John Smith', position: 'application developer' }
    ]
});

// Add a query without parameters
pg.add('SELECT * FROM products', [], {
    rowCount: 2,
    rows: [
        { id: 101, name: 'Laptop', price: 1200 },
        { id: 102, name: 'Mouse', price: 25 }
    ]
});

(async function() {
    try {
        // Connect to the mock database
        const client = await pg.connect();

        // Query with a valid parameter
        const employeeData = await client.query('SELECT * FROM employees WHERE id=$1;', [1]);
        console.log('Employee Query Result:', employeeData.rows);

        // Query without parameters
        const productData = await client.query('SELECT * FROM products;');
        console.log('Product Query Result:', productData.rows);

        // Attempt a query with an invalid parameter type (will likely throw)
        await client.query('SELECT * FROM employees WHERE id=$1;', ['invalid']);
    } catch (err: any) {
        console.error('Error during quickstart:', err.message);
    }
})();