PostgreSQL SQL Test Runner

Common errors

• Error: Environment variable DB is not set

  cause The required PostgreSQL connection URL was not provided via the `DB` environment variable.
  fix Set the `DB` environment variable with a valid PostgreSQL connection string before running `pg-test`. Example: `export DB=postgres://user:pass@host:port/dbname`.

• Error: connect ECONNREFUSED 127.0.0.1:5432

  cause The `pg-test` utility could not establish a connection to the PostgreSQL server, likely due to the server not running, incorrect host/port, or firewall issues.
  fix Verify that your PostgreSQL server is running, the specified host and port in the `DB` environment variable are correct, and no firewall is blocking the connection. Check PostgreSQL logs for details.

• syntax error at or near "SELECT"

  cause One of your `.sql` test files contains invalid SQL syntax.
  fix Review the SQL file where the error occurred. Correct the syntax. The error message usually provides clues about the line number or near where the error happened.

Warnings

• gotcha The `DB` environment variable is mandatory for `pg-test` to connect to PostgreSQL. If not set, the tool will likely fail with a connection error or use an unexpected default (like `travis` on Travis-CI).
  Fix: Ensure `export DB=postgres://user:password@host:port/database` (Linux/macOS) or `$env:DB='...'` (Windows PowerShell) is run before `pg-test`.
• breaking pg-test is a CLI-first tool. There is no documented or intended programmatic API for `import`ing functions or classes directly from the package. Attempting to do so will result in import errors or undefined behavior.
  Fix: Interact with `pg-test` via `child_process.spawn` or `exec` methods in Node.js, or by running the `pg-test` command directly in your shell or CI/CD pipeline.
• gotcha The runner stops on the first SQL file failure. This means it's not designed to report all failures in a suite but rather to immediately halt execution, which can be less informative for large test suites.
  Fix: Consider structuring tests into smaller, independent files or using other test runners if comprehensive failure reporting is required.
• gotcha pg-test executes raw SQL files. This limits its capabilities compared to full-fledged JavaScript/TypeScript test frameworks (e.g., dynamic test generation, complex assertions, mocking).
  Fix: Use pg-test for simple, direct SQL validation. For more complex testing scenarios involving application logic, integrate with a JS/TS test runner and a PostgreSQL client library (e.g., `node-postgres`).

Install

• npm install pg-test npm
• yarn add pg-test yarn
• pnpm add pg-test pnpm

Imports

• pg-test
  wrong:

  import { run } from 'pg-test';

  correct:

  import { spawn } from 'child_process'; spawn('pg-test', ['path/to/tests'], { stdio: 'inherit' });

  pg-test is primarily a CLI tool. There are no documented direct programmatic exports for `import { ... } from 'pg-test'`. To execute it from a Node.js script, use `child_process` methods. Do not attempt to import symbols as if it were a library.
• PgTestRunner
  wrong:

  import PgTestRunner from 'pg-test';

  correct:

  // No direct programmatic API documented for import.

  This package is a CLI application. It does not export a `PgTestRunner` class or similar for programmatic instantiation. Its functionality is accessed via the command line.
• Configuration
  wrong:

  import { configure } from 'pg-test';

  correct:

  // Configuration is via environment variables or CLI arguments.

  Configuration for pg-test is handled through the `DB` environment variable for database connection and CLI arguments for the test path. There is no `configure` function or similar API to import.

Quickstart

Demonstrates how to run `pg-test` programmatically using `child_process` after setting the required `DB` environment variable, including creating a temporary SQL test file.

import { spawnSync } from 'child_process';
import path from 'path';
import fs from 'fs';

// Create a dummy test file
const testFilePath = path.join(process.cwd(), 'temp_test.sql');
fs.writeFileSync(testFilePath, 'SELECT 1 = 1; -- Should pass');

// Set the DB environment variable (replace with your actual DB URL)
// For local PostgreSQL without password: 'postgres://postgres@localhost/your_db_name'
// Make sure 'your_db_name' exists and is accessible.
process.env.DB = process.env.DB ?? 'postgres://postgres@localhost/testdb';

console.log(`Running pg-test against: ${process.env.DB}`);

const result = spawnSync('pg-test', [path.dirname(testFilePath)], {
  stdio: 'inherit',
  env: process.env
});

if (result.status !== 0) {
  console.error('pg-test failed!');
} else {
  console.log('pg-test completed successfully.');
}

// Clean up the dummy test file
fs.unlinkSync(testFilePath);