pgsql-test

import { getConnections } from 'pgsql-test'; import { Client } from 'pg'; describe('User Service Integration', () => { let db: Client; let teardown: () => Promise<void>; // Before all tests, set up a new isolated test database beforeAll(async () => { // getConnections creates a new UUID-named database, applies migrations // (if pgpm modules are configured), and returns a pg client and teardown function. ({ db, teardown } = await getConnections({ database: 'my_app_test', connectionString: process.env.DATABASE_URL ?? 'postgres://user:password@localhost:5432/postgres' })); // Example: Create a simple table and insert some initial data await db.query(` CREATE TABLE IF NOT EXISTS users ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL ); `); await db.query(` INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com'); `); }); // After all tests in this suite, clean up the test database afterAll(async () => { await teardown(); }); // Each test runs within its own transaction for further isolation beforeEach(async () => { await db.query('BEGIN;'); }); afterEach(async () => { await db.query('ROLLBACK;'); // Rollback all changes made in the test }); test('should retrieve all users', async () => { const res = await db.query('SELECT * FROM users ORDER BY id;'); expect(res.rows).toHaveLength(2); expect(res.rows[0].name).toBe('Alice'); }); test('should add a new user', async () => { await db.query("INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');"); const res = await db.query('SELECT * FROM users;'); expect(res.rows).toHaveLength(3); expect(res.rows.some(u => u.name === 'Charlie')).toBe(true); }); test('should not allow duplicate emails', async () => { await db.query("INSERT INTO users (name, email) VALUES ('David', 'david@example.com');"); await expect(db.query("INSERT INTO users (name, email) VALUES ('Eve', 'david@example.com');")).rejects.toThrow(/duplicate key value violates unique constraint/); }); });