Agentuity Documentation

The @agentuity/postgres package provides a PostgreSQL client built for serverless environments with automatic reconnection.

Installation

bun add @agentuity/postgres

Basic Usage

import { postgres } from '@agentuity/postgres';
 
// Create client (uses DATABASE_URL by default)
const sql = postgres();
 
// Queries automatically retry on connection errors
const users = await sql`SELECT * FROM users WHERE active = ${true}`;

Transactions

Transactions ensure multiple operations succeed or fail together:

const tx = await sql.begin();
try {
  await tx`UPDATE accounts SET balance = balance - ${100} WHERE name = ${'Alice'}`;
  await tx`UPDATE accounts SET balance = balance + ${100} WHERE name = ${'Bob'}`;
  await tx.commit();
} catch (error) {
  await tx.rollback();
  throw error;
}

Transaction Options

const tx = await sql.begin({
  isolationLevel: 'serializable',  // 'read uncommitted' | 'read committed' | 'repeatable read' | 'serializable'
  readOnly: true,                   // Read-only transaction
  deferrable: true,                 // Deferrable transaction (only with serializable + readOnly)
});

Savepoints

Use savepoints for partial rollbacks within a transaction:

const tx = await sql.begin();
try {
  await tx`INSERT INTO users (name) VALUES (${'Alice'})`;
 
  const savepoint = await tx.savepoint();
  try {
    await tx`INSERT INTO users (name) VALUES (${'Bob'})`;
    // Something goes wrong
    throw new Error('Oops');
  } catch {
    await savepoint.rollback();  // Only rolls back Bob's insert
  }
 
  await tx.commit();  // Alice's insert is committed
} catch (error) {
  await tx.rollback();
  throw error;
}

Configuration

const sql = postgres({
  url: 'postgres://user:pass@localhost:5432/mydb',
  max: 10,
  prepare: false,
  reconnect: {
    enabled: true,
    maxAttempts: 10,
    initialDelayMs: 100,
    maxDelayMs: 30000,
    multiplier: 2,
    jitterMs: 1000,
  },
  onclose: (error) => console.log('Connection closed', error),
  onreconnect: (attempt) => console.log('Reconnecting', { attempt }),
  onreconnected: () => console.log('Reconnected!'),
});

Common options from PostgresConfig:

Option	Type	Default	Description
`url`	`string`	`DATABASE_URL`	PostgreSQL connection string
`hostname`, `port`, `username`, `password`, `database`	connection fields	-	Individual connection fields when not using `url`
`max`	`number`	`10`	Maximum connections in the pool
`preconnect`	`boolean`	`false`	Run a test query during client creation instead of connecting lazily
`prepare`	`boolean`	`false`	Use named prepared statements. Leave this `false` for connection poolers
`bigint`	`boolean`	`false`	Return large integers as `BigInt` instead of strings
`maxLifetime`	`number`	`0`	Maximum connection lifetime in seconds. `0` disables the limit
`reconnect.enabled`	`boolean`	`true`	Enable automatic reconnection
`reconnect.maxAttempts`	`number`	`10`	Maximum reconnection attempts
`reconnect.initialDelayMs`	`number`	`100`	Initial delay before first retry
`reconnect.maxDelayMs`	`number`	`30000`	Maximum delay between retries
`reconnect.multiplier`	`number`	`2`	Exponential backoff multiplier
`reconnect.jitterMs`	`number`	`1000`	Maximum random jitter added to delays
`onclose`	`(error) => void`	-	Callback when connection closes
`onreconnect`	`(attempt) => void`	-	Callback when a reconnection attempt starts
`onreconnected`	`() => void`	-	Callback when reconnection succeeds
`onreconnectfailed`	`(error) => void`	-	Callback when reconnection fails permanently

Connection Stats

Monitor connection health with detailed statistics:

const sql = postgres();
 
// After some queries
console.log(sql.stats);

Property	Type	Description
`connected`	`boolean`	Whether currently connected
`reconnecting`	`boolean`	Whether currently reconnecting
`totalConnections`	`number`	Total connections established
`reconnectAttempts`	`number`	Current reconnection attempt count
`failedReconnects`	`number`	Total failed reconnection attempts
`lastConnectedAt`	`Date \| null`	When last connected
`lastDisconnectedAt`	`Date \| null`	When last disconnected
`lastReconnectAttemptAt`	`Date \| null`	When last reconnection was attempted

Additional Methods

Wait for Connection

Block until a connection is established:

// Wait up to 5 seconds for connection
await sql.waitForConnection(5000);

Graceful Shutdown

Signal shutdown to prevent reconnection attempts, then close the client when in-flight work has drained:

sql.shutdown();
await sql.close();

Unsafe Queries

Execute raw SQL strings. Use unsafeQuery when you still want retry behavior around a trusted raw SQL string:

// Only use for trusted queries, not user input
const result = await sql.unsafeQuery('SELECT version()');

Access Raw Client

Access the underlying Bun.SQL instance:

const bunSql = sql.raw;

Key Features

Automatic reconnection with exponential backoff and jitter
Query retry on retryable errors (connection lost, etc.)
Graceful shutdown on SIGTERM/SIGINT
Transaction support with isolation levels and savepoints
Connection stats for monitoring and debugging

Next Steps

Database Overview: Bun SQL basics and provisioning
Drizzle ORM: Type-safe queries with Drizzle