# @probitas/client-sql-postgres

> Version: 0.5.0

PostgreSQL client for [Probitas](https://github.com/probitas-test/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

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

## Quick Start

```ts
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

```ts
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

```ts
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

```ts
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

| Package | Description |
|---------|-------------|
| [`@probitas/client-sql`](https://jsr.io/@probitas/client-sql) | Common SQL types and utilities |
| [`@probitas/client-sql-mysql`](https://jsr.io/@probitas/client-sql-mysql) | MySQL client |
| [`@probitas/client-sql-sqlite`](https://jsr.io/@probitas/client-sql-sqlite) | SQLite client |
| [`@probitas/client-sql-duckdb`](https://jsr.io/@probitas/client-sql-duckdb) | DuckDB client |

## Links

- [GitHub Repository](https://github.com/probitas-test/probitas-client)
- [Probitas Framework](https://github.com/probitas-test/probitas)
- [PostgreSQL](https://www.postgresql.org/)


## Classes

### `PostgresTransaction`

```typescript
class PostgresTransaction implements SqlTransaction
```

PostgreSQL transaction implementation.

Wraps a postgres.js reserved connection to provide transaction semantics.

**Constructor:**

```typescript
new PostgresTransaction(sql: postgres.ReservedSql)
```

**Methods:**

```typescript
query(): unknown
```

Execute a query within the transaction.


```typescript
queryOne(): unknown
```

Execute a query and return the first row or undefined.


```typescript
commit(): unknown
```

Commit the transaction.

```typescript
rollback(): unknown
```

Rollback the transaction.

---

### `SqlError`

```typescript
class SqlError extends ClientError
```

Base error class for SQL-specific errors.
Extends ClientError with SQL-specific properties.

**Constructor:**

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

**Properties:**

- [readonly] `name`: `string`
- [readonly] `kind`: `SqlErrorKind`
- [readonly] `sqlState`: `string | null`

---

### `QuerySyntaxError`

```typescript
class QuerySyntaxError extends SqlError
```

Error thrown when a SQL query has syntax errors.

**Constructor:**

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

**Properties:**

- [readonly] `name`: `string`
- [readonly] `kind`: `"query"`

---

### `ConstraintError`

```typescript
class ConstraintError extends SqlError
```

Error thrown when a constraint violation occurs.

**Constructor:**

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

**Properties:**

- [readonly] `name`: `string`
- [readonly] `kind`: `"constraint"`
- [readonly] `constraint`: `string`

---

### `DeadlockError`

```typescript
class DeadlockError extends SqlError
```

Error thrown when a deadlock is detected.

**Constructor:**

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

**Properties:**

- [readonly] `name`: `string`
- [readonly] `kind`: `"deadlock"`

---

### `SqlConnectionError`

```typescript
class SqlConnectionError extends SqlError
```

Error thrown when a connection or network-level error occurs.

This includes:
- Connection refused (server not running)
- Authentication failure
- Connection timeout
- Pool exhaustion
- TLS handshake failure
- DNS resolution failure

**Constructor:**

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

**Properties:**

- [readonly] `name`: `string`
- [readonly] `kind`: `"connection"`

---

## Interfaces

### `PostgresClient`

```typescript
interface PostgresClient extends AsyncDisposable
```

PostgreSQL client interface.

**Properties:**

- [readonly] `config`: `PostgresClientConfig` — The client configuration.
- [readonly] `dialect`: `"postgres"` — The database dialect.

**Methods:**

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

Execute a SQL query.


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

Execute a query and return the first row or undefined.


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

Execute a function within a transaction.

The transaction is automatically committed if the function completes successfully,
or rolled back if the function throws an error.


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

Copy data from an iterable into a table using PostgreSQL COPY protocol.


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

Copy data from a query result using PostgreSQL COPY protocol.


```typescript
listen(channel: string): AsyncIterable<PostgresNotification>
```

Listen for notifications on a channel.


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

Send a notification on a channel.


```typescript
close(): Promise<void>
```

Close the client and release all connections.

---

### `PostgresSslConfig`

```typescript
interface PostgresSslConfig
```

SSL/TLS configuration for PostgreSQL connection.

**Properties:**

- [readonly] `rejectUnauthorized?`: `boolean` — Whether to reject unauthorized certificates.
- [readonly] `ca?`: `string` — CA certificate(s) for verification.
- [readonly] `cert?`: `string` — Client certificate for mutual TLS.
- [readonly] `key?`: `string` — Client private key for mutual TLS.

---

### `PostgresConnectionConfig`

```typescript
interface PostgresConnectionConfig extends CommonConnectionConfig
```

PostgreSQL connection configuration.

Extends CommonConnectionConfig with PostgreSQL-specific options.

**Properties:**

- [readonly] `database?`: `string` — Database name to connect to.
- [readonly] `ssl?`: `boolean | PostgresSslConfig` — SSL/TLS configuration.

---

### `PostgresPoolConfig`

```typescript
interface PostgresPoolConfig
```

Pool configuration for PostgreSQL.

**Properties:**

- [readonly] `max?`: `number` — Maximum number of connections in the pool
- [readonly] `idleTimeout?`: `number` — Idle timeout in milliseconds before closing unused connections
- [readonly] `connectTimeout?`: `number` — Connection timeout in milliseconds

---

### `PostgresClientConfig`

```typescript
interface PostgresClientConfig extends CommonOptions
```

Configuration for creating a PostgreSQL client.

**Properties:**

- [readonly] `url`: `string | PostgresConnectionConfig` — Connection URL string or configuration object.

- [readonly] `pool?`: `PostgresPoolConfig` — Pool configuration
- [readonly] `applicationName?`: `string` — Application name for PostgreSQL connection
- [readonly] `throwOnError?`: `boolean` — Whether to throw an error for query failures.
When false, failures are returned as SqlQueryResultError or SqlQueryResultFailure.
Can be overridden per-query via SqlQueryOptions.

---

### `PostgresNotification`

```typescript
interface PostgresNotification
```

PostgreSQL LISTEN/NOTIFY notification.

**Properties:**

- [readonly] `channel`: `string` — Channel name
- [readonly] `payload`: `string` — Notification payload
- [readonly] `processId`: `number` — Process ID of the notifying backend

---

### `PostgresErrorLike`

```typescript
interface PostgresErrorLike
```

PostgreSQL error structure from the driver.

**Properties:**

- [readonly] `message`: `string`
- [readonly] `code?`: `string`
- [readonly] `constraint?`: `string`

---

### `SqlQueryOptions`

```typescript
interface SqlQueryOptions extends CommonOptions
```

Options for individual SQL queries.

**Properties:**

- [readonly] `throwOnError?`: `boolean` — Whether to throw an error for query failures.
When false, failures are returned as SqlQueryResultError or SqlQueryResultFailure.

---

### `SqlTransactionOptions`

```typescript
interface SqlTransactionOptions
```

Options for starting a transaction.

**Properties:**

- [readonly] `isolationLevel?`: `SqlIsolationLevel` — Isolation level for the transaction

---

### `SqlTransaction`

```typescript
interface SqlTransaction
```

SQL transaction interface.
Implementations should provide actual database-specific transaction handling.

**Methods:**

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

Execute a query within the transaction.

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

Execute a query and return the first row or undefined.

```typescript
commit(): Promise<void>
```

Commit the transaction.

```typescript
rollback(): Promise<void>
```

Rollback the transaction.

---

### `SqlErrorOptions`

```typescript
interface SqlErrorOptions extends ErrorOptions
```

Options for SqlError constructor.

**Properties:**

- [readonly] `sqlState?`: `string` — SQL State code (e.g., "23505" for unique violation)

---

### `SqlQueryResultSuccess`

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

SQL query result for successful queries.

The query was executed successfully and returned results.

**Properties:**

- [readonly] `processed`: `true` — Server processed the query.
- [readonly] `ok`: `true` — Query succeeded.
- [readonly] `error`: `null` — No error for successful queries.
- [readonly] `rows`: `readonly T[]` — Query result rows.
- [readonly] `rowCount`: `number` — Number of affected rows.
- [readonly] `lastInsertId`: `bigint | string | null` — Last inserted ID (for INSERT statements).
- [readonly] `warnings`: `unknown | null` — Warning messages from the database.

---

### `SqlQueryResultError`

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

SQL query result for query errors (syntax errors, constraint violations, etc.).

Server received and processed the query, but it failed due to a SQL error.

**Properties:**

- [readonly] `processed`: `true` — Server processed the query.
- [readonly] `ok`: `false` — Query failed.
- [readonly] `error`: `SqlError` — Error describing the SQL error.
- [readonly] `rows`: `readonly never[]` — Empty rows for failed queries.
- [readonly] `rowCount`: `0` — Zero affected rows for failed queries.
- [readonly] `lastInsertId`: `null` — No lastInsertId for failed queries.
- [readonly] `warnings`: `null` — No warnings for failed queries.

---

### `SqlQueryResultFailure`

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

SQL query result for connection failures (network errors, timeouts, etc.).

Query could not be processed by the server (connection refused, timeout,
pool exhausted, authentication failure, etc.).

**Properties:**

- [readonly] `processed`: `false` — Server did not process the query.
- [readonly] `ok`: `false` — Query failed.
- [readonly] `error`: `SqlFailureError` — Error describing the failure.
- [readonly] `rows`: `null` — No rows (query didn't reach server).
- [readonly] `rowCount`: `null` — No row count (query didn't reach server).
- [readonly] `lastInsertId`: `null` — No lastInsertId (query didn't reach server).
- [readonly] `warnings`: `null` — No warnings (query didn't reach server).

---

### `SqlQueryResultSuccessParams`

```typescript
interface SqlQueryResultSuccessParams<T = any>
```

Parameters for creating a SqlQueryResultSuccess.

**Properties:**

- [readonly] `rows`: `readonly T[]` — The result rows
- [readonly] `rowCount`: `number` — Number of affected rows (for INSERT/UPDATE/DELETE)
- [readonly] `duration`: `number` — Query execution duration in milliseconds
- [readonly] `lastInsertId?`: `bigint | string` — Last inserted ID (for INSERT statements)
- [readonly] `warnings?`: `readonly string[]` — Warning messages from the database

---

## Functions

### `createPostgresClient`

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

Create a new PostgreSQL client instance.

The client provides connection pooling, parameterized queries, transaction support,
and PostgreSQL-specific features like COPY and LISTEN/NOTIFY.


**Parameters:**

- `config`: `PostgresClientConfig` — - PostgreSQL client configuration

**Returns:** `Promise<PostgresClient>`

A promise resolving to a new PostgreSQL client instance


**Example:**

Using URL string
```ts
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();
```

Using connection config object
```ts
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();
```

Transaction with auto-commit/rollback
```ts
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();
```

LISTEN/NOTIFY for real-time events
```ts
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();
```

Using `await using` for automatic cleanup
```ts
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
```

---

### `isConnectionError`

```typescript
function isConnectionError(error: PostgresErrorLike): boolean
```

Check if an error is a connection-level error based on SQLSTATE or error characteristics.

**Parameters:**

- `error`: `PostgresErrorLike`

**Returns:** `boolean`

---

### `mapPostgresError`

```typescript
function mapPostgresError(error: PostgresErrorLike): SqlError
```

Maps a PostgreSQL error to the appropriate SqlError subclass.


**Parameters:**

- `error`: `PostgresErrorLike` — - PostgreSQL error from the driver

**Returns:** `SqlError`

Mapped SqlError or subclass

---

## Types

### `SqlIsolationLevel`

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

Transaction isolation level.

---

### `SqlErrorKind`

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

SQL-specific error kinds.

---

### `SqlOperationError`

```typescript
type SqlOperationError = QuerySyntaxError | ConstraintError | DeadlockError | SqlError
```

Error types that indicate an operation was processed by the server.
These errors occur after the query reaches the SQL server.

---

### `SqlFailureError`

```typescript
type SqlFailureError = SqlConnectionError | AbortError | TimeoutError
```

Error types that indicate the operation was not processed.
These are errors that occur before the query reaches the SQL server.

---

### `SqlQueryResult`

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

SQL query result union type representing all possible result states.

- **Success**: `processed: true, ok: true, error: null`
- **Error**: `processed: true, ok: false, error: SqlError`
- **Failure**: `processed: false, ok: false, error: SqlConnectionError`


---

## Related Links

### This Package

- [`ConstraintError`](https://probitas-test.github.io/documents/api/client-sql-postgres#ConstraintError)
- [`DeadlockError`](https://probitas-test.github.io/documents/api/client-sql-postgres#DeadlockError)
- [`PostgresClient`](https://probitas-test.github.io/documents/api/client-sql-postgres#PostgresClient)
- [`PostgresClientConfig`](https://probitas-test.github.io/documents/api/client-sql-postgres#PostgresClientConfig)
- [`PostgresConnectionConfig`](https://probitas-test.github.io/documents/api/client-sql-postgres#PostgresConnectionConfig)
- [`PostgresErrorLike`](https://probitas-test.github.io/documents/api/client-sql-postgres#PostgresErrorLike)
- [`PostgresNotification`](https://probitas-test.github.io/documents/api/client-sql-postgres#PostgresNotification)
- [`PostgresPoolConfig`](https://probitas-test.github.io/documents/api/client-sql-postgres#PostgresPoolConfig)
- [`PostgresSslConfig`](https://probitas-test.github.io/documents/api/client-sql-postgres#PostgresSslConfig)
- [`QuerySyntaxError`](https://probitas-test.github.io/documents/api/client-sql-postgres#QuerySyntaxError)
- [`SqlConnectionError`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlConnectionError)
- [`SqlError`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlError)
- [`SqlErrorKind`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlErrorKind)
- [`SqlErrorOptions`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlErrorOptions)
- [`SqlFailureError`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlFailureError)
- [`SqlIsolationLevel`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlIsolationLevel)
- [`SqlQueryOptions`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlQueryOptions)
- [`SqlQueryResult`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlQueryResult)
- [`SqlQueryResultError`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlQueryResultError)
- [`SqlQueryResultFailure`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlQueryResultFailure)
- [`SqlQueryResultSuccess`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlQueryResultSuccess)
- [`SqlTransaction`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlTransaction)
- [`SqlTransactionOptions`](https://probitas-test.github.io/documents/api/client-sql-postgres#SqlTransactionOptions)

### Other Packages

- [`AbortError`](https://probitas-test.github.io/documents/api/client#AbortError) (@probitas/client)
- [`CommonConnectionConfig`](https://probitas-test.github.io/documents/api/client#CommonConnectionConfig) (@probitas/client)
- [`CommonOptions`](https://probitas-test.github.io/documents/api/client#CommonOptions) (@probitas/client)
- [`TimeoutError`](https://probitas-test.github.io/documents/api/client#TimeoutError) (@probitas/client)

### Built-in Types

- [`AsyncIterable`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Iteration_protocols#the_async_iterator_and_async_iterable_protocols)
- [`Promise`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise)
- [`Record`](https://www.typescriptlang.org/docs/handbook/utility-types.html#recordkeys-type)

---

*Last updated: 2026-01-12*