{"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.","language":"javascript","status":"active","last_verified":"Sun Apr 19","install":{"commands":["npm install pgsql-test"],"cli":null},"imports":["import { getConnections } from 'pgsql-test';","import { seed } from 'pgsql-test';","import { setContext } from 'pgsql-test';"],"auth":{"required":false,"env_vars":[]},"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.","tag":null,"tag_description":null,"last_tested":null,"results":[]},"compatibility":null}