PostgreSQL

PostgreSQL is a relational DBMS focused on correctness and extensibility.

Setup

Install

npm install @primate/postgresql

You need a running PostgreSQL server and a database/user with access.

Configure

// config/db.ts
import postgresql from "@primate/postgresql";

export default postgresql({
  database: "app",
  // host: "localhost",
  // port: 5432,
  // username: "postgres",
  // password: "secret",
  // pool: { max: 10, connectTimeout: 30_000, idleTimeout: 0 },
});

Options

option	type	default	description
database	`string`	—	Database name
host	`string`	`"localhost"`	PostgreSQL host
port	`number`	`5432`	PostgreSQL port
username	`string`	—	Username
password	`string`	—	Password
pool	`object`	driver defaults	Connection pool controls

Pool options

option	type	postgres.js default	description
max	`number`	`10`	Maximum connections; integer from 1 to 65,535
connectTimeout	`number`	`30000`	Milliseconds allowed to establish and authenticate a new connection; integer from 1 to 2,147,483,647
idleTimeout	`number`	`0`	Milliseconds before an idle connection closes; integer from 0 to 2,147,483,647, with `0` disabling idle closure

These controls do not impose a timeout on requests waiting for pool capacity. Omitting an option preserves the postgres.js default.

Independent pools

Create a separately owned PostgreSQL instance without repeating credentials:

const locks = db.fork({
  pool: { max: 4, connectTimeout: 5_000, idleTimeout: 1_000 },
});

try {
  await locks.client.begin(async sql => {
    await sql`SELECT pg_advisory_xact_lock(10, 20)`;
    // transaction-scoped work
  });
} finally {
  await locks.close();
}

The fork inherits the parent's endpoint, credentials, and unspecified pool settings, but owns another pool. Its client, typed sql() queries, Store operations, transactions, and close() are independent from the parent.

JSONB values

Store fields declared with p.json() retain their native PostgreSQL JSONB shape. Objects remain objects, arrays remain arrays, and string, number and boolean values remain their corresponding JSON scalars. PostgreSQL constraints, operators and indexes can therefore inspect the stored structure directly.

SELECT jsonb_typeof(detail) FROM record;

For example, a stored object returns object, rather than string. Primate passes the JavaScript value to the PostgreSQL driver's JSON serializer exactly once.

If a transaction connection is lost, client.begin() rejects, including after an advisory lock was acquired. Rejection reports the loss but cannot cancel application work already running in the callback. Use application-level cancellation or fencing when work must stop after lock loss.

Oracle

SQLite