PostgreSQL Dialect

PostgreSQL Dialect Overview PostgreSQL is the ORM's most fully-featured dialect. Unlike the compatibility-layer dialects (TiDB, CockroachDB, etc.), the PostgresDialect is a first-class, hand-written implementation on top of the official node-postgres (pg) driver — it does not extend any other dialect. Key facts (from src/dialects/postgres/index.ts): Dialect name is 'postgres'; the driver library is 'pg'. Connections are pooled (pg.Pool), with a separate dedicated pg.Client opened lazily only for LISTEN/NOTIFY (notifications are delivered to the specific backend that issued LISTEN, so a pooled connection can't be used). Identifiers are double-quoted: escapeId('mycol') → "mycol", and embedded double-quotes are doubled (" → ""). Parameters use PostgreSQL's native numbered $1, $2, … placeholders, not ?. The build methods generate these and return a matching values array. RETURNING is supported on INSERT / UPDATE / DELETE / UPSERT / MERGE — a genuine PostgreSQL capability, not emulated. Connection Notes: The top-level Prorm option is username; internally it is mapped to the pg driver's user field. Constructor defaults are applied when omitted: host: 'localhost', port: 5432, max: 20 (pool size), idleTimeoutMillis: 30000, connectionTimeoutMillis: 2000. Driver extras via dialectOptions Postgres-specific fields that aren't part of the base connection shape are passed through the top-level dialectOptions object, which Prorm spreads onto the dialect config (src/prorm.ts). These are forwarded to pg.Pool: The ssl value is passed verbatim to pg (ssl: this.config.ssl), so it accepts anything node-postgres accepts — true, false, or a TLS options object with ca/cert/key/rejectUnauthorized. Schemas / searchpath There is no schema or searchPath connection option on the Postgres dialect — the config interface (PostgresDialectOptions) does not declare one, so the connection uses the server's default searchpath (normally public). Schema qualification is done per operation instead: quoteTable(table, schema) prefixes a non-public schema: quoteTable('users', 'billing') → "billing"."users". A schema of 'public' (or omitted) is left unqualified. View, materialized-view, trigger, and stored-procedure builders accept an options.schema and qualify the object name the same way. createSchema(name) / dropSchema(name, { ifExists, cascade }) and listSchemas() manage schemas directly. To pin a searchpath for a session, run it as a raw statement: Data types getDataTypeSql maps the ORM's cross-dialect type descriptors to PostgreSQL types. A raw string is passed through unchanged (with one normalization: the cross-dialect pseudo-type DATETIME, emitted by the model-sync layer for createdAt/updatedAt/deletedAt, is rewritten to TIMESTAMP, preserving any precision — DATETIME(6) → TIMESTAMP(6)). ORM type descriptor | Generated SQL | --------------------------------------- | ------------------------- | { key: 'STRING' } | VARCHAR(255) | { key: 'STRING', length: 100 } | VARCHAR(100) | { key: 'CHAR' } | CHAR(1) | { key: 'TEXT' } | TEXT | { key: 'INTEGER' } | INTEGER | { key: 'INTEGER', autoIncrement } | SERIAL | { key: 'BIGINT' } | BIGINT | { key: 'BIGINT', autoIncrement } | BIGSERIAL | { key: 'FLOAT' } | REAL | { key: 'DOUBLE' } | DOUBLE PRECISION | { key: 'DECIMAL', precision, scale } | DECIMAL(precision,scale)| { key: 'BOOLEAN' } | BOOLEAN | { key: 'DATE' } | TIMESTAMP | { key: 'DATEONLY' } | DATE | { key: 'TIME' } | TIME | { key: 'BLOB' } | BYTEA | { key: 'JSON' } | JSON | { key: 'JSONB' } | JSONB | { key: 'UUID' } | UUID | { key: 'ENUM', values: ['a','b'] } | ENUM('a','b') | { key: 'ARRAY', type: INTEGER } | INTEGER[] | { key: 'INET' } / CIDR / MACADDR | INET / CIDR / MACADDR | { key: 'HSTORE' } | HSTORE | { key: 'VECTOR', dimensions: 3 } | VECTOR(3) | Notes on honest limitations: SERIAL/BIGSERIAL are emitted only for auto-increment INTEGER/BIGINT columns; the identity-column form (GENERATED … AS IDENTITY) is not generated by the type mapper. ENUM is emitted inline as ENUM('a','b'), which is not native PostgreSQL syntax — PostgreSQL enums are named types created with CREATE TYPE. Use the dialect's createEnumType() helper and reference the type by name for real enum columns. VECTOR requires the pgvector extension (CREATE EXTENSION IF NOT EXISTS vector — see createExtension()). GEOMETRY/GEOGRAPHY require PostGIS. An unknown descriptor falls back to VARCHAR(255). Placeholders: $n numbering Every DML builder walks the input left-to-right and emits $1, $2, … in order, pushing the corresponding JS value onto a values array that lines up with the placeholders. A Literal (raw SQL) value is inlined verbatim and does not consume a placeholder number — so numbering stays contiguous across a mix of bound values and literals. For UPDATE, the SET placeholders are numbered first, then the WHERE values are appended to the same values array (in order) so the caller passes one flat array to the driver. RETURNING INSERT, UPDATE, DELETE, UPSERT, and MERGE all accept a returning option: returning: true → appends RETURNING . returning: ['id', 'createdat'] → appends RETURNING "id", "createdat" (each column identifier-quoted). The query() method surfaces RETURNING rows: for a non-SELECT statement that returns rows, the result's rows array is populated and rowCount is set to the number of returned rows. ON CONFLICT upsert PostgreSQL upserts use INSERT … ON CONFLICT (cols) DO UPDATE SET … with the special excluded pseudo-row referencing the values that would have been inserted. Two builders produce this: buildInsertQuery(table, values, { upsert: true, conflictFields, … }) buildUpsertQuery(table, values, { conflictFields, updateOnDuplicate?, … }) conflictFields is required. PostgreSQL's ON CONFLICT must name the columns of an existing unique or exclusion constraint — it cannot be inferred from the inserted columns — so both builders throw if conflictFields is missing or empty. updateOnDuplicate (on buildUpsertQuery) restricts which columns the DO UPDATE SET touches; otherwise every inserted column is updated: For multi-branch merge logic (update on one condition, delete on another, insert otherwise), buildMergeQuery emits a PostgreSQL 15+ MERGE … WHEN [NOT] MATCHED statement instead. Its RETURNING support requires PostgreSQL 17+. Arrays escapeArray produces a PostgreSQL ARRAY[...] literal (strings are double-quoted, non-scalar elements are JSON-encoded and quoted). PostgreSQL infers the element type from the target column, so untyped literals work for both text[] and numeric arrays: Array operator helpers build predicate fragments: Helper | Emitted SQL | -------------------------------------- | ------------------------------------ | buildArrayContains('tags', ['a','b']) | "tags" @> ARRAY["a", "b"] | buildArrayContainedBy('tags', ['a']) | "tags" <@ ARRAY["a"] | buildArrayOverlaps('tags', ['a','b']) | "tags" && ARRAY["a", "b"] | buildArrayAny('tags', 'a') | 'a' = ANY("tags") | buildArrayAll('scores', 10, '>') | 10 > ALL("scores") | JSON / JSONB Both JSON and JSONB column types are mapped directly. buildJsonPathQuery constructs path predicates and containment/existence checks: Operator handling (real PostgreSQL semantics, verified in the dialect source): Containment @> / <@ — emitted as raw operators with a ::jsonb cast on the argument: "data" @> $1::jsonb. (PostgreSQL has no function form for containment, only the operators.) Key existence ? — emitted via the jsonbexists(col, $1::text) function. Any-key existence ?| — emitted via jsonbexistsany(col, $1::text[]), which correctly takes a text[] of candidate keys (distinct from the single-key ?). Comparison operators (=, !=, >, <, >=, <=, ~, ~) with a path use the #> path-extraction operator; without a path they compare the column directly. Beyond CRUD The dialect also implements genuine PostgreSQL features backing higher ORM layers, each grounded in the driver: full-text search (buildFullTextSearchQuery, wrapping totsvector/plaintotsquery with an optional tsrank/tsrankcd ranking), LISTEN/NOTIFY over a dedicated non-pooled client (listen/notify/unlisten, with notify using SELECT pgnotify($1, $2)), session-level advisory locks (pgAdvisoryLock/pgTryAdvisoryLock, each holding a dedicated pooled connection until release()), server-side cursor streaming (queryStream), and range/list/hash partitioning helpers. Caveats / honest limitations build methods generate SQL; they do not execute. They return { sql, values }; you (or the ORM's higher layers) pass them to query(). conflictFields is mandatory for upserts — both upsert builders throw without it (see the ON CONFLICT section). Inline ENUM('a','b') is not native PostgreSQL syntax. Prefer named enum types via createEnumType(). SET LIMIT on UPDATE/DELETE is emitted when options.limit is given, but standard PostgreSQL does not support LIMIT on UPDATE/DELETE; it will error on a real server unless an extension permits it. Use a subquery on the primary key instead. No global schema/searchPath config. Qualify per operation, or SET searchpath via a raw statement. escape() inlines values; the parameterized build path ($n + values) is preferred for anything derived from user input. </invoke> Related reading Running postgres in Docker — get one going locally All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL