# pgsql-client

PostgreSQL client utilities with query helpers, RLS context management, and database administration. ## Overview `pgsql-client` provides a set of utilities for working with PostgreSQL databases: - **DbAdmin**: Database administration operations (create, drop, templates, extensions, grants) - **PgClient**: Query helpers with RLS context management - **Role utilities**: Role name mapping for anonymous, authenticated, and administrator roles - **Context utilities**: Generate SQL for setting PostgreSQL session context variables - **Stream utilities**: Stream SQL to psql process ## Installation ```bash npm install pgsql-client ``` ## Usage ### DbAdmin ```typescript import { DbAdmin } from 'pgsql-client'; const admin = new DbAdmin({ host: 'localhost', port: 5432, user: 'postgres', password: 'password', database: 'mydb' }); // Create a database admin.create('mydb'); // Install extensions admin.installExtensions(['uuid-ossp', 'pgcrypto'], 'mydb'); // Drop a database admin.drop('mydb'); ``` ### PgClient ```typescript import { PgClient } from 'pgsql-client'; const client = new PgClient({ host: 'localhost', port: 5432, user: 'app_user', password: 'password', database: 'mydb' }); // Query helpers const users = await client.any('SELECT * FROM users'); const user = await client.one('SELECT * FROM users WHERE id = $1', [userId]); const maybeUser = await client.oneOrNone('SELECT * FROM users WHERE email = $1', [email]); // Set RLS context client.setContext({ role: 'authenticated', 'jwt.claims.user_id': userId }); // Or use the auth helper client.auth({ role: 'authenticated', userId: userId }); // Close the connection await client.close(); ``` ## API ### DbAdmin - `create(dbName?)` - Create a database - `drop(dbName?)` - Drop a database - `createFromTemplate(template, dbName?)` - Create database from template - `installExtensions(extensions, dbName?)` - Install PostgreSQL extensions - `connectionString(dbName?)` - Generate connection string - `createTemplateFromBase(base, template)` - Create template database - `cleanupTemplate(template)` - Clean up template database - `grantRole(role, user, dbName?)` - Grant role to user - `grantConnect(role, dbName?)` - Grant connect privilege - `createUserRole(user, password, dbName)` - Create user with roles - `loadSql(file, dbName)` - Load SQL file - `streamSql(sql, dbName)` - Stream SQL to database ### PgClient - `query(sql, values?)` - Execute query with context - `any(sql, values?)` - Return all rows - `one(sql, values?)` - Return exactly one row (throws if not exactly one) - `oneOrNone(sql, values?)` - Return one row or null - `many(sql, values?)` - Return many rows (throws if none) - `manyOrNone(sql, values?)` - Return rows or empty array - `none(sql, values?)` - Execute without returning rows - `result(sql, values?)` - Return full QueryResult - `begin()` - Begin transaction - `commit()` - Commit transaction - `savepoint(name?)` - Create savepoint - `rollback(name?)` - Rollback to savepoint - `setContext(ctx)` - Set session context variables - `auth(options?)` - Set authentication context - `clearContext()` - Clear context and reset to anonymous - `close()` - Close connection ### Ephemeral Databases Create temporary databases for testing, code generation, or other ephemeral use cases: ```typescript import { createEphemeralDb } from 'pgsql-client'; // Create a temporary database with a unique UUID-based name const { name, config, admin, teardown } = createEphemeralDb(); // Use the database const pool = new Pool(config); await pool.query('SELECT 1'); await pool.end(); // Clean up when done teardown(); // Or keep for debugging teardown({ keepDb: true }); ``` #### Options ```typescript const { config, teardown } = createEphemeralDb({ prefix: 'test_', // Database name prefix (default: 'ephemeral_') extensions: ['uuid-ossp'], // PostgreSQL extensions to install baseConfig: { // Override connection settings host: 'localhost', port: 5432, user: 'postgres', password: 'password' }, verbose: true // Enable logging }); ``` #### Return Value - `name` - The generated database name (e.g., `ephemeral_a1b2c3d4_...`) - `config` - Full PostgreSQL configuration for connecting - `admin` - DbAdmin instance for additional operations - `teardown(opts?)` - Function to drop the database (pass `{ keepDb: true }` to preserve) #### Use Cases Ephemeral databases are useful for: - **Code generation**: Generate types from a temporary schema - **Integration tests**: Isolated database per test suite - **CI pipelines**: Clean database state for each run - **Local development**: Experiment without affecting shared databases