PostgreSQL client utilities with query helpers, RLS context management, and database administration.

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

npm install pgsql-client

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');

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();

• 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

• 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

Create temporary databases for testing, code generation, or other ephemeral use cases:

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 });

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
});

• 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)

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

MIT