Class PostgresAdapter

constructor

• new PostgresAdapter(options): PostgresAdapter

• Parameters

  • options: string | PostgresAdapterOptions

  Returns PostgresAdapter

Readonlykind

Readonlycapabilities

getPool

• getPool(): null | PostgresPoolLike

• The pool the adapter runs on: the pool it was given, or the pool it made while it is open. Null while the adapter is closed.

  Returns null | PostgresPoolLike

open

• open(openOptions?): Promise<void>

• Opens the adapter: makes its pool and checks that a connection can be made (and with a segment, creates the segment's schema when it is missing and checks that the segment is the connection's current schema), or takes up the pool it was given. A failure ends the pool it made, so a later open() starts again. Two calls at once open once.

  Parameters

  • OptionalopenOptions: StorageOpenOptions

  Returns Promise<void>

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}`);
  Copy

get

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

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

  Type Parameters

  • T

  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]);
  Copy

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

  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]);
  Copy

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);
  `);
  Copy

transaction

• transaction<T>(fn, options?): Promise<T>

• Runs fn in a transaction on one pooled connection: COMMIT when it resolves, ROLLBACK when it or COMMIT throws, and that error is rethrown. A COMMIT that PostgreSQL answers with ROLLBACK, because a statement in the transaction failed and fn went on, throws too. options sets the isolation level and the read-only and deferrable modes; an unknown isolation level is refused before a connection is checked out.

  When ROLLBACK fails too (the connection broke), the error thrown is still the transaction's own, with the ROLLBACK error on it as rollbackError, and the connection is closed instead of returned to the pool. The handle fn receives runs on the pool once the transaction has ended.

  Type Parameters

  • T

  Parameters

  • fn: ((trx: StorageAdapter) => Promise<T>)
    • • (trx): Promise<T>

      • Parameters

        • trx: StorageAdapter

        Returns Promise<T>

  • Optionaloptions: PostgresTransactionOptions

  Returns Promise<T>

withConnection

• withConnection<T>(fn): Promise<T>

• Holds one pooled connection for fn, for work that needs one session: SET and other session settings, session advisory locks (pg_advisory_lock), temporary tables, or statements that must not run inside a transaction. Its statements run outside a transaction unless fn opens one with connection.transaction() or BEGIN.

  When fn resolves, the connection goes back to the pool with its session state, so undo what fn set (or use SET LOCAL inside a transaction). When fn throws, the connection is closed instead, which ends its session state and any transaction it left open, and fn's error is rethrown. When fn resolves inside a transaction it began and did not end, the connection is closed, which rolls the transaction back, and the call rejects (pg 8.21 and later report the transaction status). The connection refuses statements once fn has ended. A connection that broke while fn held it (the server ended the session) is closed, not returned. On a segmented adapter the connection starts in the segment, as every connection of its pool does.

  Type Parameters

  • T

  Parameters

  • fn: ((connection: PostgresConnection) => Promise<T>)
    • • (connection): Promise<T>

      • Parameters

        • connection: PostgresConnection

        Returns Promise<T>

  Returns Promise<T>

batch

• batch(operations): Promise<BatchResult>

• Runs the operations in one transaction, going on past one that fails, as the better-sqlite3 adapter's batch does. Each operation runs under a savepoint, since a failed statement aborts a PostgreSQL transaction; a failure rolls back to it and is reported with its index in errors. The operations that succeed commit together. When the transaction itself fails, nothing commits and the error is reported at index -1.

  Parameters

  • operations: BatchOperation[]

  Returns Promise<BatchResult>

listen

• listen(channel, handler): Promise<(() => Promise<void>)>

• Calls handler with each notification sent on channel (PostgreSQL LISTEN) from any session: notify(), pg_notify(), or the trigger the dialect's changeNotificationSql installs. The channel name is exact, case included, as pg_notify() takes it.

  One connection of the pool is held while any channel has a handler. When the server ends it, the adapter listens again on a new one after 1 second, then 2, up to 30, and logs it; notifications sent meanwhile are lost (https://www.postgresql.org/docs/current/sql-notify.html). close() stops every channel. Resolves, once listening, to a function that stops this handler.

  Parameters

  • channel: string
  • handler: PostgresNotificationHandler

  Returns Promise<(() => Promise<void>)>

  Example

  const stop = await db.listen('notes_changes', (payload) => refresh(JSON.parse(payload)));
  Copy

notify

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

• Sends payload on channel (pg_notify) to every session listening there. PostgreSQL delivers it when the sending transaction commits, and refuses a payload of 8000 bytes or more.

  Parameters

  • channel: string
  • payload: string = ''

  Returns Promise<void>

getPoolStats

• getPoolStats(): null | PostgresPoolStats

• The pool's counts, or null when the adapter is not open. A pool of the caller's that does not count its connections, as pg's Pool does, reads 0.

  Returns null | PostgresPoolStats

close

• close(): Promise<void>

• Closes the adapter. A pool the adapter made or owns is ended, once; a pool of its caller's that it does not own is left open, with the adapter's listener taken off it. A call made while a close is running waits for it.

  Returns Promise<void>