Interface PostgresConnection

One pooled connection, held for a withConnection() callback. Its statements run outside a transaction unless the callback opens one, and session state (SET, session advisory locks, temporary tables) lasts until the connection closes.

interface PostgresConnection {
    transaction<T>(fn: ((trx: StorageAdapter) => Promise<T>), options?: PostgresTransactionOptions): Promise<T>;
    kind: string;
    capabilities: ReadonlySet<StorageCapability>;
    open(options?: StorageOpenOptions): Promise<void>;
    run(statement: string, parameters?: StorageParameters): Promise<StorageRunResult>;
    get<T>(statement: string, parameters?: StorageParameters): Promise<null | T>;
    all<T>(statement: string, parameters?: StorageParameters): Promise<T[]>;
    exec(script: string): Promise<void>;
    close(): Promise<void>;
    batch?(operations: BatchOperation[]): Promise<BatchResult>;
    prepare?<T>(statement: string): PreparedStatement<T>;
    registerFunction?(name: string, fn: SqlFunction, options?: SqlFunctionOptions): void;
}

Hierarchy (view full)

Properties

kind capabilities

Methods

transaction open run get all exec close batch? prepare? registerFunction?

Properties

Readonlykind

kind: string

Identifier for logging/diagnostics (e.g., 'better-sqlite3', 'postgres').

Readonlycapabilities

capabilities: ReadonlySet<StorageCapability>

Capability flags indicating supported features.

Methods

transaction

open

  • open(options?): Promise<void>

  • Opens the underlying connection or initializes the backing store.

    Parameters

    Returns Promise<void>

    Throws

    If connection cannot be established

    Example

    await adapter.open({ filePath: '/path/to/db.sqlite3' });

run

  • run(statement, parameters?): Promise<StorageRunResult>

  • Executes a mutation statement (INSERT, UPDATE, DELETE).

    Parameters

    • statement: string

      SQL statement to execute

    • Optionalparameters: StorageParameters

      Optional parameters for the statement

    Returns Promise<StorageRunResult>

    Metadata about affected rows

    Throws

    If statement execution fails

    Example

    const result = await adapter.run('INSERT INTO users (name) VALUES (?)', ['John']);
    console.log(`Inserted with ID: ${result.lastInsertRowid}`);

get

  • get<T>(statement, parameters?): Promise<null | T>

  • Retrieves a single row (or null if none found).

    Type Parameters

    • T = unknown

    Parameters

    • statement: string

      SQL SELECT statement

    • Optionalparameters: StorageParameters

      Optional parameters for the statement

    Returns Promise<null | T>

    First row or null if no results

    Throws

    If query execution fails

    Example

    const user = await adapter.get<User>('SELECT * FROM users WHERE id = ?', [123]);

all

  • all<T>(statement, parameters?): Promise<T[]>

  • Retrieves all rows returned by the statement.

    WARNING: For large result sets, this loads everything into memory. Consider using streaming (if supported) for large queries.

    Type Parameters

    • T = unknown

    Parameters

    • statement: string

      SQL SELECT statement

    • Optionalparameters: StorageParameters

      Optional parameters for the statement

    Returns Promise<T[]>

    Array of all matching rows (empty array if none)

    Throws

    If query execution fails

    Example

    const users = await adapter.all<User>('SELECT * FROM users WHERE age > ?', [18]);

exec

  • exec(script): Promise<void>

  • Executes a script containing multiple SQL statements.

    Statements are typically delimited by semicolons. This is useful for running migrations or initialization scripts. No results are returned.

    Parameters

    • script: string

      SQL script with multiple statements

    Returns Promise<void>

    Throws

    If any statement in the script fails

    Example

    await adapter.exec(`
      CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
      CREATE INDEX idx_users_name ON users(name);
    `);

close

  • close(): Promise<void>

  • Closes the underlying connection and releases resources.

    After calling close(), the adapter should not be used again. Always close adapters when done to prevent resource leaks.

    Returns Promise<void>

    Example

    try {
      await adapter.open();
      // ... use adapter ...
    } finally {
      await adapter.close();
    }

Optionalbatch

  • batch(operations): Promise<BatchResult>

  • Executes multiple operations in a batch (if supported).

    This is more efficient than executing operations individually, especially for bulk inserts. Check if 'batch' capability is present.

    Parameters

    Returns Promise<BatchResult>

    Batch execution results

    Throws

    If adapter doesn't support batch operations

    Example

    if (adapter.capabilities.has('batch')) {
      const result = await adapter.batch([
        { statement: 'INSERT INTO users (name) VALUES (?)', parameters: ['Alice'] },
        { statement: 'INSERT INTO users (name) VALUES (?)', parameters: ['Bob'] }
      ]);
    }

Optionalprepare

  • prepare<T>(statement): PreparedStatement<T>

  • Returns a prepared statement for repeated execution (if supported).

    Prepared statements improve performance for frequently executed queries and provide better protection against SQL injection. Check if 'prepared' capability is present.

    Type Parameters

    • T = unknown

    Parameters

    • statement: string

      SQL statement to prepare

    Returns PreparedStatement<T>

    Prepared statement handle

    Throws

    If adapter doesn't support prepared statements

OptionalregisterFunction

  • registerFunction(name, fn, options?): void

  • Makes a JavaScript function callable from SQL under name (if supported: the better-sqlite3, sql.js and IndexedDB adapters). The function takes as many arguments as it declares (fn.length).

    Parameters

    • name: string

      An identifier: letters, digits and underscores

    • fn: SqlFunction

      The function; SQL values arrive as its arguments

    • Optionaloptions: SqlFunctionOptions

      deterministic (better-sqlite3)

    Returns void

    Throws

    If the name is not an identifier

    Example

    db.registerFunction?.('slugify', (title) => String(title).toLowerCase(), { deterministic: true });
    const rows = await db.all('SELECT slugify(title) AS slug FROM posts');

Settings

Member Visibility

On This Page

Properties
Methods