@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

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

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

await client.transaction(async (tx: SqlTransaction) => {
  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" });

await client.close();

LISTEN/NOTIFY

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

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

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

await client.close();

Using with using Statement

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

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

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 SqlConnectionError extends SqlError

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

class SqlError extends ClientError

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

interface PostgresClient extends AsyncDisposable

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

queryOne<T = Record<string, any>>(
  sql: string,
  params?: unknown[],
  options?: SqlQueryOptions,
): 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 SqlQueryOptions extends CommonOptions

interface SqlQueryResultError<T = any> extends SqlQueryResultBase<T>

interface SqlQueryResultFailure<T = any> extends SqlQueryResultBase<T>

interface SqlQueryResultSuccess<T = any> extends SqlQueryResultBase<T>

interface SqlQueryResultSuccessParams<T = any>

interface SqlTransaction

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

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

commit(): Promise<void>

rollback(): Promise<void>

interface SqlTransactionOptions

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

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

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

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

await client.close();

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

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

await client.close();

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

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

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

await client.close();

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

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

// 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 client.close();

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

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

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

function isConnectionError(error: PostgresErrorLike): boolean

function mapPostgresError(error: PostgresErrorLike): SqlError

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

type SqlFailureError = SqlConnectionError | AbortError | TimeoutError

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

type SqlOperationError = QuerySyntaxError | ConstraintError | DeadlockError | SqlError

type SqlQueryResult<T = any> = SqlQueryResultSuccess<T> | SqlQueryResultError<T> | SqlQueryResultFailure<T>