@probitas/client-sql-postgres

PostgreSQL client for Probitas scenario testing framework.

This package provides a PostgreSQL client designed for integration testing, with transaction support and LISTEN/NOTIFY capabilities.

Features

• Query Execution: Parameterized queries with type-safe results
• Transactions: Full transaction support with isolation levels
• LISTEN/NOTIFY: Real-time notifications for async testing
• COPY Protocol: High-performance bulk data loading
• Connection Pooling: Configurable pool with idle timeout
• Resource Management: Implements AsyncDisposable for proper cleanup

Installation

deno add jsr:@probitas/client-sql-postgres

Quick Start

import { createPostgresClient } from "@probitas/client-sql-postgres";

const client = await createPostgresClient({
  url: "postgres://user:pass@localhost:5432/mydb",
});

// Query with parameters
const result = await client.query<{ id: number; name: string }>(
  "SELECT id, name FROM users WHERE active = $1",
  [true]
);
console.log(result.rows);

// Get first row
const user = await client.queryOne<{ id: number; name: string }>(
  "SELECT * FROM users WHERE id = $1",
  [1]
);

await client.close();

Transactions

await client.transaction(async (tx) => {
  await tx.query("INSERT INTO accounts (id, balance) VALUES ($1, $2)", [1, 100]);
  await tx.query("INSERT INTO accounts (id, balance) VALUES ($1, $2)", [2, 200]);
  // Automatically committed if no error, rolled back on exception
}, { isolationLevel: "serializable" });

LISTEN/NOTIFY

// Listen for notifications
const listener = await client.listen("events");
for await (const notification of listener) {
  console.log("Received:", notification.payload);
  break;
}

// Send notification
await client.notify("events", JSON.stringify({ type: "created", id: 123 }));

Using with using Statement

await using client = await createPostgresClient({
  url: "postgres://localhost:5432/testdb",
});

const result = await client.query("SELECT 1 as n");
// Client automatically closed when block exits

Related Packages

Package	Description
@probitas/client-sql	Common SQL types and utilities
@probitas/client-sql-mysql	MySQL client
@probitas/client-sql-sqlite	SQLite client
@probitas/client-sql-duckdb	DuckDB client

Links

• GitHub Repository
• Probitas Framework
• PostgreSQL

deno add jsr:@probitas/client-sql-postgres

class ConstraintError extends SqlError

new ConstraintError(message: string, constraint: string, options?: SqlErrorOptions)

class DeadlockError extends SqlError

new DeadlockError(message: string, options?: SqlErrorOptions)

class PostgresTransaction implements SqlTransaction

new PostgresTransaction(sql: postgres.ReservedSql)

query(): unknown

queryOne(): unknown

commit(): unknown

rollback(): unknown

class QuerySyntaxError extends SqlError

new QuerySyntaxError(message: string, options?: SqlErrorOptions)

class SqlError extends ClientError

new SqlError(message: string, kind: SqlErrorKind, options?: SqlErrorOptions)

class SqlQueryResult<T = Record<string, any>>

new SqlQueryResult(init: SqlQueryResultInit<T>)

map(): unknown

as(): unknown

interface PostgresClient extends AsyncDisposable

query<T = Record<string, any>>(sql: string, params?: unknown[]): Promise<SqlQueryResult<T>>

queryOne<T = Record<string, any>>(sql: string, params?: unknown[]): Promise<T | undefined>

transaction<T>(fn: (tx: SqlTransaction) => unknown, options?: SqlTransactionOptions): Promise<T>

copyFrom(table: string, data: AsyncIterable<unknown[]>): Promise<number>

copyTo(query: string): AsyncIterable<unknown[]>

listen(channel: string): AsyncIterable<PostgresNotification>

notify(channel: string, payload?: string): Promise<void>

close(): Promise<void>

interface PostgresClientConfig extends CommonOptions

interface PostgresConnectionConfig extends CommonConnectionConfig

interface PostgresErrorLike

interface PostgresNotification

interface PostgresPoolConfig

interface PostgresSslConfig

interface SqlErrorOptions extends ErrorOptions

interface SqlQueryResultInit<T>

interface SqlQueryResultMetadata

interface SqlTransaction

query<T = Record<string, any>>(sql: string, params?: unknown[]): Promise<SqlQueryResult<T>>

queryOne<T = Record<string, any>>(sql: string, params?: unknown[]): Promise<T | undefined>

commit(): Promise<void>

rollback(): Promise<void>

interface SqlTransactionOptions

async function createPostgresClient(config: PostgresClientConfig): Promise<PostgresClient>

const client = await createPostgresClient({
  url: "postgres://user:pass@localhost:5432/mydb",
});

const result = await client.query("SELECT * FROM users WHERE id = $1", [1]);
console.log(result.rows.first());

await client.close();

const client = await createPostgresClient({
  url: {
    host: "localhost",
    port: 5432,
    database: "mydb",
    username: "user",
    password: "pass",
  },
  pool: { max: 10 },
  applicationName: "my-app",
});

const user = await client.transaction(async (tx) => {
  await tx.query("INSERT INTO users (name) VALUES ($1)", ["John"]);
  return await tx.queryOne("SELECT * FROM users WHERE name = $1", ["John"]);
});

// Listen for notifications
for await (const notification of client.listen("user_events")) {
  console.log("Received:", notification.payload);
}

// In another session
await client.notify("user_events", JSON.stringify({ userId: 123 }));

await using client = await createPostgresClient({
  url: "postgres://localhost:5432/mydb",
});

const result = await client.query("SELECT 1");
// Client automatically closed when scope exits

function mapPostgresError(error: PostgresErrorLike): SqlError

type SqlErrorKind = "query" | "constraint" | "deadlock" | "unknown"

type SqlIsolationLevel = "read_uncommitted" | "read_committed" | "repeatable_read" | "serializable"