Module: database/adapters/postgres-adapter

PostgreSQL adapter implementing the db-adapter interface.

Uses the pg package with pg-native for synchronous query execution. The existing repository layer calls db.prepare().run() synchronously (better-sqlite3 API), so this adapter provides the same blocking semantics.

Prerequisites

npm install pg pg-native

pg-native provides libpq C bindings with a querySync method. If pg-native is not installed, the adapter falls back to deasync (npm install deasync) to block the event loop on async pool queries.

SQL compatibility

Automatically translates common SQLite-isms to PostgreSQL:

  • @param named bindings → $N positional parameters
  • INTEGER PRIMARY KEY AUTOINCREMENT → SERIAL PRIMARY KEY
  • datetime('now') → NOW()
  • INSERT OR IGNORE → INSERT ... ON CONFLICT DO NOTHING
  • INSERT OR REPLACE → upsert via ON CONFLICT DO UPDATE SET
  • LIKE → ILIKE (case-insensitive matching)
  • PRAGMA table_info(t) → information_schema.columns query

Column name case mapping

PostgreSQL folds unquoted identifiers to lowercase (passwordHash → passwordhash). The adapter automatically remaps lowercase column names back to camelCase on every returned row so the application code works identically on both backends.

Source:

Members

(inner, constant) _COL_MAP

All camelCase column names used across the schema.

Source:

(inner) dialect :"postgres"

Type:
  • "postgres"
Source:

Methods

(static) createPostgresAdapter(optsopt) → {Object}

Create a PostgreSQL adapter instance.

Parameters:
Name Type Attributes Description
opts Object <optional>
Properties
Name Type Attributes Description
connectionString string <optional>

— PostgreSQL connection URL. Defaults to process.env.DATABASE_URL.
poolSize number <optional>

— Max pool connections (default 10).
Source:
Throws:

  • If DATABASE_URL is not set.

    Type
    Error

  • If neither pg-native nor deasync is installed.

    Type
    Error
Returns:

Adapter conforming to the db-adapter interface.

Type
Object

(static) translateSql(sql) → {string}

Convert SQLite-flavoured SQL to PostgreSQL.

Splits multi-statement SQL on semicolons (respecting string literals), translates each statement individually, and rejoins. This ensures that INSERT OR IGNORE / INSERT OR REPLACE clauses each receive their own ON CONFLICT suffix rather than only the last statement.

Parameters:
Name Type Description
sql string
Source:
Returns:

PostgreSQL-compatible SQL

Type
string

(inner) buildColumnMap(names) → {Object.<string, string>}

Build a lowercase → camelCase lookup from a list of camelCase names. Only entries where lowercase differs from the original are included.

Parameters:
Name Type Description
names Array.<string>
Source:
Returns:
Type
Object.<string, string>

(inner) isNamedParams(args) → {boolean}

Determine if args represent a named-params object (vs positional args).

Parameters:
Name Type Description
args Array.<any>
Source:
Returns:
Type
boolean

(inner) maskStringLiterals(sql) → {Object}

Mask single-quoted string literals in SQL so regex replacements don't corrupt values inside strings. Returns the masked SQL and a restore function that puts the original literals back.

Parameters:
Name Type Description
sql string
Source:
Returns:
Type
Object

(inner) namedToPositional(sql, namedParams) → {Object}

Convert @name named parameters to $N positional parameters.

String literals are masked before replacement so that email addresses or other values containing @ inside quoted strings are not treated as parameter placeholders (e.g. 'user@example.com' stays intact).

Parameters:
Name Type Description
sql string

— SQL with @name placeholders
namedParams Object

— { name: value, ... }
Source:
Returns:
Type
Object

(inner) questionToNumbered(sql) → {string}

Convert ? positional placeholders to $N numbered placeholders.

String literals are masked before replacement so that ? inside quoted strings (e.g. 'What?') is not treated as a placeholder.

Parameters:
Name Type Description
sql string
Source:
Returns:
Type
string

(inner) remapRow(row) → {Object}

Remap lowercase PostgreSQL column names to camelCase on a single row object. Keys that are already camelCase or not in the map are left unchanged.

Parameters:
Name Type Description
row Object
Source:
Returns:
Type
Object

(inner) remapRows(rows) → {Array.<Object>}

Remap all rows in an array.

Parameters:
Name Type Description
rows Array.<Object>
Source:
Returns:
Type
Array.<Object>

(inner) translateSingleStatement(stmt) → {string}

Translate a single SQL statement from SQLite dialect to PostgreSQL.

String literals are masked before replacements and restored afterward so that values like 'I LIKE cats' are never corrupted to 'I ILIKE cats'.

Parameters:
Name Type Description
stmt string

— a single SQL statement (no trailing semicolons expected)
Source:
Returns:

PostgreSQL-compatible SQL statement

Type
string