{"id":13768,"library":"pgsql-test","title":"pgsql-test: Isolated PostgreSQL Testing Environments","description":"pgsql-test is a Node.js and TypeScript library, currently at version 4.9.1, that provides instant, isolated, and role-aware PostgreSQL databases for integration testing. It differentiates itself by ensuring each test runs within its own transaction or savepoint, which offers complete isolation, automatic rollbacks, and clean state management without polluting external database environments. Key features include support for testing Row-Level Security (RLS) via `setContext()`, flexible data seeding options (including SQL files, programmatic seeds, and integration with `pgpm` modules), and automatic teardown to prevent resource leaks. The library is actively maintained within the `constructive-io` ecosystem and is designed to be compatible with popular asynchronous test runners like Jest and Mocha, offering a reliable solution for fast and realistic database integration tests.","status":"active","version":"4.9.1","language":"javascript","source_language":"en","source_url":"https://github.com/constructive-io/constructive","tags":["javascript","postgres","postgresql","testing","integration-tests","database-testing","pg","rls","role-based-access","typescript"],"install":[{"cmd":"npm install pgsql-test","lang":"bash","label":"npm"},{"cmd":"yarn add pgsql-test","lang":"bash","label":"yarn"},{"cmd":"pnpm add pgsql-test","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"While pgsql-test provides database connections, applications typically interact with PostgreSQL using the 'pg' client library. This is a common peer dependency for application logic.","package":"pg","optional":false}],"imports":[{"note":"This is the primary named export to obtain database connections and a teardown function. It's an async function.","wrong":"const { getConnections } = require('pgsql-test');","symbol":"getConnections","correct":"import { getConnections } from 'pgsql-test';"},{"note":"Provides utilities for flexible database seeding, often used with .sql files or pgpm modules. It's a named export.","wrong":"import seed from 'pgsql-test/seed';","symbol":"seed","correct":"import { seed } from 'pgsql-test';"},{"note":"Used for simulating different user roles and JWT claims, essential for testing Row-Level Security (RLS) policies.","wrong":"import { setContext } from 'pgsql-test/context';","symbol":"setContext","correct":"import { setContext } from 'pgsql-test';"}],"quickstart":{"code":"import { getConnections } from 'pgsql-test';\nimport { Client } from 'pg';\n\ndescribe('User Service Integration', () => {\n  let db: Client;\n  let teardown: () => Promise<void>;\n\n  // Before all tests, set up a new isolated test database\n  beforeAll(async () => {\n    // getConnections creates a new UUID-named database, applies migrations\n    // (if pgpm modules are configured), and returns a pg client and teardown function.\n    ({ db, teardown } = await getConnections({\n      database: 'my_app_test',\n      connectionString: process.env.DATABASE_URL ?? 'postgres://user:password@localhost:5432/postgres'\n    }));\n\n    // Example: Create a simple table and insert some initial data\n    await db.query(`\n      CREATE TABLE IF NOT EXISTS users (\n        id SERIAL PRIMARY KEY,\n        name VARCHAR(255) NOT NULL,\n        email VARCHAR(255) UNIQUE NOT NULL\n      );\n    `);\n    await db.query(`\n      INSERT INTO users (name, email) VALUES\n      ('Alice', 'alice@example.com'),\n      ('Bob', 'bob@example.com');\n    `);\n  });\n\n  // After all tests in this suite, clean up the test database\n  afterAll(async () => {\n    await teardown();\n  });\n\n  // Each test runs within its own transaction for further isolation\n  beforeEach(async () => {\n    await db.query('BEGIN;');\n  });\n\n  afterEach(async () => {\n    await db.query('ROLLBACK;'); // Rollback all changes made in the test\n  });\n\n  test('should retrieve all users', async () => {\n    const res = await db.query('SELECT * FROM users ORDER BY id;');\n    expect(res.rows).toHaveLength(2);\n    expect(res.rows[0].name).toBe('Alice');\n  });\n\n  test('should add a new user', async () => {\n    await db.query(\"INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');\");\n    const res = await db.query('SELECT * FROM users;');\n    expect(res.rows).toHaveLength(3);\n    expect(res.rows.some(u => u.name === 'Charlie')).toBe(true);\n  });\n\n  test('should not allow duplicate emails', async () => {\n    await db.query(\"INSERT INTO users (name, email) VALUES ('David', 'david@example.com');\");\n    await expect(db.query(\"INSERT INTO users (name, email) VALUES ('Eve', 'david@example.com');\")).rejects.toThrow(/duplicate key value violates unique constraint/);\n  });\n});","lang":"typescript","description":"This quickstart demonstrates setting up an isolated PostgreSQL database for a Jest/Mocha test suite, performing per-test transaction rollbacks, and running basic CRUD operations. It uses `getConnections` to manage the database lifecycle and a `pg` client for interactions."},"warnings":[{"fix":"Review the package's GitHub releases or changelog for detailed migration instructions before upgrading major versions.","message":"Major version updates (e.g., from v3 to v4) in pgsql-test or its underlying `constructive-io` dependencies may introduce breaking API changes. Always consult the release notes and migration guides for the specific version you are upgrading to.","severity":"breaking","affected_versions":">=3.0.0"},{"fix":"Ensure `await teardown();` is called in an `afterAll` or `after` hook to properly clean up the test database.","message":"Failing to call the `teardown()` function returned by `getConnections()` can leave test databases active after tests complete. This leads to resource consumption and potential conflicts in subsequent test runs, especially in CI/CD environments.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Verify your PostgreSQL server is running and accessible from where your tests are executed. Ensure connection details (host, port, user, password) are correctly provided, often via environment variables.","message":"pgsql-test relies on an accessible PostgreSQL server (e.g., via Docker or a local instance) to create and manage test databases. If the PostgreSQL server is not running or misconfigured, `getConnections()` will fail.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Optimize seeding by loading only necessary data, using smaller datasets for unit-level integration tests, or leveraging `pgpm` for efficient, incremental migrations where applicable.","message":"Extensive or complex seeding operations (e.g., loading large datasets, running many SQL migration files) can significantly increase test setup time, particularly when using per-test isolation or frequent database recreation.","severity":"gotcha","affected_versions":">=1.0.0"}],"env_vars":null,"search_vec":"'4.9.1':20 'access':149 'activ':100 'asynchron':116 'automat':52,91 'awar':28 'base':148 'clean':55 'compat':113 'complet':50 'construct':105 'constructive-io':104 'current':17 'data':77 'databas':30,61,131,142 'database-test':141 'design':110 'differenti':35 'ecosystem':107 'ensur':38 'environ':7,62 'extern':60 'fast':128 'featur':64 'file':82 'flexibl':76 'includ':65,80 'instant':23 'integr':32,86,132,139 'integration-test':138 'io':106 'isol':4,24,51 'javascript':134 'jest':120 'key':63 'leak':96 'level':71 'librari':16,98 'like':119 'maintain':101 'manag':57 'mocha':122 'modul':89 'node.js':13 'offer':49,123 'option':79 'pg':144 'pgpm':88 'pgsql':2,9 'pgsql-test':1,8 'pollut':59 'popular':115 'postgr':135 'postgresql':5,29,136 'prevent':94 'programmat':83 'provid':22 'realist':130 'reliabl':125 'resourc':95 'rls':73,145 'role':27,147 'role-awar':26 'role-based-access':146 'rollback':53 'row':70 'row-level':69 'run':41 'runner':118 'savepoint':47 'secur':72 'seed':78,84 'setcontext':75 'solut':126 'sql':81 'state':56 'support':66 'teardown':92 'test':3,6,10,33,40,68,117,133,137,140,143 'transact':45 'typescript':15,150 'version':19 'via':74 'within':42,102 'without':58","created_at":"2026-04-20T01:56:12.341134+00:00","updated_at":"2026-04-20T01:56:12.341134+00:00","problems":[{"fix":"Double-check the username and password in your connection string (e.g., `DATABASE_URL` environment variable) and ensure the PostgreSQL user has access to create/manage databases.","cause":"The PostgreSQL connection string provided has incorrect credentials for the specified user.","error":"error: password authentication failed for user \"testuser\""},{"fix":"Ensure your PostgreSQL server is started and listening on the correct host/port. If using Docker, verify the container is running and ports are mapped correctly.","cause":"The PostgreSQL server is not running, or it's not accessible at the specified host and port.","error":"psql: error: could not connect to server: Connection refused"},{"fix":"Install the `pg` package as a dependency: `npm install pg` or `yarn add pg`.","cause":"Although pgsql-test manages connections, the `db` client returned by `getConnections()` is an instance of `pg.Client`. If your application code uses the `pg` client directly, it must be installed.","error":"Error: Cannot find module 'pg'"},{"fix":"Ensure `teardown()` is reliably called in `afterAll`. If the issue persists with parallel tests, review your test runner's concurrency settings or pgsql-test's configuration for unique database naming.","cause":"A previous test run failed to clean up the temporary database, or multiple parallel tests are attempting to create the same UUID-named database due to a bug or misconfiguration.","error":"ERROR: database \"uuid-xyz-test\" already exists"}],"ecosystem":"npm","meta_description":null,"install_score":null,"quickstart_score":null,"quickstart_tag":null,"pypi_latest":"0.2.0","cli_name":"","cli_version":null,"type":"library","homepage":"https://constructive-io.github.io/pgsql-test","github":"https://github.com/constructive-io/constructive","docs":null,"changelog":null,"pypi":null,"npm":"https://www.npmjs.com/package/pgsql-test","openapi_spec":null,"status_page":null,"smithery":null,"categories":["testing","database"],"base_url":null,"auth_type":null,"provenance":{"verified_status":null,"verified_at":null,"last_verified":"2026-06-17","next_check":"2026-07-18","install_tag":null}}