@molecule/api-database-postgresql

Auto-generated, AI-first package reference for the molecule.dev ecosystem. It is written to be read by coding agents as much as by people, and is generated from this package's source — edit src/index.ts JSDoc, not this file.

The PostgreSQL client.

Type

provider

Installation

npm install @molecule/api-database-postgresql @molecule/api-bond @molecule/api-database @molecule/api-secrets glob pg
npm install -D @types/pg

API

Functions

createMigrator(migrationsDir)

Returns a runMigrations() function bound to the given directory.

function createMigrator(migrationsDir: string): () => Promise<void>

• migrationsDir — Absolute path to the directory containing ordered *.sql migration files. Resolve via join(new URL('.', import.meta.url).pathname, '../../migrations') from the app's scripts/migrate.ts.

Returns: A no-arg runMigrations() that creates the database (if missing) and applies every migration file in lexical order.

createPool(config)

Creates a new pool with custom configuration.

Use this when you need a pool with different settings than the default.

function createPool(config?: DatabaseConfig): DatabasePool

• config — Database connection configuration (host, port, user, password, SSL, pool size).

Returns: A new DatabasePool backed by a fresh pg connection pool.

createStore(pool)

Creates a DataStore backed by a PostgreSQL DatabasePool.

function createStore(pool: DatabasePool): DataStore

• pool — The PostgreSQL DatabasePool to use for queries.

Returns: A DataStore that translates CRUD operations to SQL queries.

deriveSsl(databaseUrl)

Derive the ssl option for a pg connection from a database URL, secure by default. The three-way rule (identical everywhere a pg client/pool is created so the behaviour cannot drift):

1. Local / explicit no-SSL (isLocalUrl) → false (no TLS).
2. Private-CA managed provider (PGSSLROOTCERT set) → verify against that CA bundle ({ ca, rejectUnauthorized: true }). Verification stays ON.
3. Remote, no explicit opt-out → true: negotiate TLS and verify the server certificate against the system CA store. This is the default and closes the MITM hole that a blanket rejectUnauthorized: false opened.

Verification is relaxed to { rejectUnauthorized: false } only when the operator explicitly asks — DATABASE_SSL_REJECT_UNAUTHORIZED=false or sslmode=no-verify in the URL — and a loud warning is logged once, because that mode is vulnerable to man-in-the-middle interception of credentials and data. Operators behind a private CA should set PGSSLROOTCERT instead.

function deriveSsl(databaseUrl: string): boolean | ConnectionOptions | undefined

• databaseUrl — The Postgres connection URL.

Returns: The ssl value for pg.ClientConfig / pg.PoolConfig.

isLocalUrl(url)

Returns true when the connection URL points at a local / explicitly no-SSL Postgres, where TLS verification is neither possible nor meaningful.

Recognizes loopback hosts, unix-socket URLs, and an explicit sslmode=disable — the standard libpq opt-out. The latter lets a caller reach a no-SSL Postgres over a private/non-localhost address (e.g. a sandbox reaching the host DB via the docker bridge gateway, or the sandbox Postgres at 172.17.0.1 which doesn't speak SSL) without us having to guess from the host. Production URLs without it still default to verified SSL.

function isLocalUrl(url: string): boolean

• url — The PostgreSQL connection URL.

Returns: true if the URL points to a local or explicitly no-SSL database.

Constants

databasePostgresqlSecretDefinitions

Secret definitions required by the PostgreSQL database bond.

const databasePostgresqlSecretDefinitions: SecretDefinition[]

pool

The PostgreSQL connection pool instance.

Example usage:

import * as Database from '@molecule/api-database-postgresql'

const queryDB = async () => {
  const result = await Database.pool.query(`SELECT * FROM "table"`)

  // ...

  return result?.rows
}

const pool: DatabasePool

store

Lazily-initialized default DataStore backed by the default pool.

const store: DataStore

Namespaces

setup

Members:

• setup.replacements — const: The default SQL files contain placeholder values which should be replaced.
• setup.runSQL — function: Executes the SQL contained within some file, replacing placeholder values as necessary.
• setup.setup — function: Sets up the database by executing all SQL files.

Core Interface

Implements @molecule/api-database interface.

Bond Wiring

Setup function to register this provider with the core interface:

import { setPool, setStore } from '@molecule/api-database'
import { pool, store } from '@molecule/api-database-postgresql'

export function setupDatabasePostgresql(): void {
  setPool(pool)
  setStore(store)
}

Injection Notes

Requirements

Peer dependencies:

• @molecule/api-bond ^1.0.1
• @molecule/api-database ^1.0.1
• @molecule/api-secrets ^1.0.1

Environment Variables

• DATABASE_URL (required) — PostgreSQL connection URL — default: postgres://molecule:molecule@127.0.0.1:5432/myapp
  • Provisioned automatically in molecule.dev sandboxes — manual setup only needed outside the platform.
  • Setup: Postgres connection string; locally, use the Docker Compose default.
  • Example: postgres://user:pass@localhost:5432/myapp

Runtime Dependencies

• @molecule/api-bond
• @molecule/api-database
• @molecule/api-secrets
• glob
• pg

Bond this as the DataStore (setStore(store)); app code then uses the abstract @molecule/api-database functions (findMany/create/…), never raw pg. The connection comes from the DATABASE_URL env var (server-side) — don't hardcode credentials.

• SSL is verify-by-default (derived from the URL): a MANAGED database (Supabase, Neon, RDS, Heroku) requires SSL and works out of the box; local Postgres needs none. Do NOT disable certificate verification to silence a cert error — that opens a MITM on your DB traffic. Fix the URL / CA instead (e.g. ?sslmode=require).
• Tables are created by timestamped .sql files in migrations/ (the runner applies them on boot) — never CREATE TABLE at runtime; ids are UUID strings (see @molecule/api-database).
• Pool max defaults to 10 (not the server's max_connections) — tune with DATABASE_POOL_MAX. A migration file with a genuine error (not an idempotent "already exists") now FAILS the boot with every broken file named, instead of warn-logging and booting with a partial schema.
• like is case-insensitive (emits ILIKE) and does NOT escape the value — the caller's own %/_ are honored as wildcards, identical to the sqlite/mysql bonds. For human-typed search input, use ilike instead (escapes + auto-wraps %…%) — see WhereCondition['operator'] in @molecule/api-database.
• pool.transaction() is implemented (parity with the sqlite/mysql bonds, so transactional code ports across the bonds unchanged): it acquires a dedicated client, issues BEGIN, and returns a DatabaseTransaction whose query() runs on that client and whose commit()/rollback() run the matching SQL and release the client back to the pool. Call commit() on success and rollback() on a thrown error; either one (or a bare release()) returns the client exactly once, so wrap in try/catch/finally and never leak it.
• The pool fails fast when DATABASE_URL is unset: first use throws an actionable "DATABASE_URL is not set" error (via @molecule/api-secrets) instead of silently connecting to the pg driver defaults (localhost:5432, OS user) and failing later with a raw ECONNREFUSED/auth error far from the cause. An explicit createPool(config) is the caller's own choice and is not second-guessed. (The one-shot migration runner still defaults its URL but prints the DATABASE_URL to check on a connection failure.)
• Objects/arrays written to json/jsonb columns are JSON-serialized FOR you on create/updateById/updateMany (the column set is introspected + cached per table, and only object/array values trigger it — scalar writes pay no extra round-trip). Pass the JS value as-is; do NOT JSON.stringify it yourself (that double-encodes), and do not rely on node-pg's default object serialization (jsonb rejects it with 22P02). Reads come back already parsed (the pg driver deserializes json/jsonb), so the round-trip is object-in → object-out.
• One-off bootstrap SQL (grants, extensions, seed data) goes in .sql files under a __setup__ directory (run via the exported setup namespace); versioned schema belongs in migrations only.