@ts-awesome/orm-pg

TypeScript ORM PostgreSQL driver for @ts-awesome/orm

Key features:

• uses yesql for named params
• can run raw SQL or compile IBuildableQuery
• provides PostgreSQL-specific kinds and function wrappers (pgcrypto, PostGIS, JSONB, full-text search)
• supports context-aware sessions, error mapping, and isolation-level transactions

Install

npm install @ts-awesome/orm-pg pg

Extensions

Some features require PostgreSQL extensions:

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS postgis;

Context

PgDriver.withContext() sets session variables for the connection used by a query. Session context is reset after each request in pooled connections.

const driver = new PgDriver(pgPool).withContext({
  'TIME ZONE': 'UTC',
  application_name: 'orm-pg'
});

Standalone

import {Pool, PoolConfig} from 'pg';
import {IBuildableQuery, IQueryExecutor, Select} from "@ts-awesome/orm";
import {ISqlQuery, PgCompiler, PgDriver} from "@ts-awesome/orm-pg"; 

const config: PoolConfig;
const pgPool = new Pool(config);

const driver = new PgDriver(pgPool);

const compiled: ISqlQuery = {
  // driver uses yesql
  sql: 'SELECT :value',
  params: {
    value: 1
  }
};
const results = await driver.execute(compiled);

Vanilla use with ORM

import {Pool, PoolConfig} from 'pg';
import {IBuildableQuery, IQueryExecutor, Select} from "@ts-awesome/orm";
import {ISqlQuery, PgCompiler, PgDriver} from "@ts-awesome/orm-pg"; 

const config: PoolConfig;
const pgPool = new Pool(config);

const compiler = new PgCompiler();
const driver = new PgDriver(pgPool);

const query: IBuildableQuery;
const compiled: ISqlQuery = compiler.compile(query);
const results = await driver.execute(compiled);

Use with IoC container

import {Pool, PoolConfig} from 'pg';
import {IBuildableQuery, IQueryExecutor, Select} from "@ts-awesome/orm";
import {ISqlQuery, PgCompiler, PgDriver} from "@ts-awesome/orm-pg"; 

const config: PoolConfig;
const pgPool = new Pool(config);

const compiler = new PgCompiler();
const driver = new PgDriver(pgPool);

const container: Container;

container.bind<IQueryDriver<ISqlQuery>>(SqlQueryDriverSymbol)
  .toDynamicValue(() => new PgDriver(pool))

container.bind<IBuildableQueryCompiler<ISqlQuery>>(SqlQueryBuildableQueryCompilerSymbol)
  .to(PgCompiler)

Kinds

This package provides ORM kinds for:

DB_UID

This kind is a dummy, but other drivers may have different behaviour

DB_JSON

This kind stringifies before write and parses raw value from DB. Malformed JSON will throw on read. DB type should be TEXT or equivalent

DB_JSONB

JSONB variant with read/write casting to jsonb

DB_UUID

UUID casting for uuid fields

DB_INET

INET casting for IP address fields

DB_TIMESTAMPTZ

TIMESTAMPTZ read/write casting with JS Date

DB_DATE

DATE read/write casting with JS Date (date-only semantics; timezone differences may affect display)

DB_NUMERIC

NUMERIC casting with number conversion (may lose precision for large values)

DB_MONEY

MONEY casting with number conversion (locale/format dependent)

DB_UUID_ARRAY / DB_INT_ARRAY / DB_TEXT_ARRAY

Array casting helpers for uuid[], integer[], and text[]

DB_EMAIL

This kind ensures DB fields is case-insensitive, also makes value lowercase on read/write For more details check here

Depends on citext extension.

Please run following initialization code on your DB

CREATE EXTENSION citext;
CREATE DOMAIN Email AS citext CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );

Encrypted kinds

DB_ENCRYPTED_TEXT, DB_ENCRYPTED_JSON, and DB_ENCRYPTED_EMAIL rely on pgcrypto and a shared key.

import {PgDriver, namedSharedKey} from '@ts-awesome/orm-pg';

const driver = new PgDriver(pgPool);
driver.setNamedParameter(namedSharedKey, 'your-shared-key');

Function wrappers

Function wrappers live under src/functions/* and are exported from the package entry (@ts-awesome/orm-pg). See docs/functions.md for the full list.

Example

import {Select, of} from '@ts-awesome/orm';
import {to_tsquery, to_tsvector, ts_rank} from '@ts-awesome/orm-pg';

const query = Select(Article)
  .columns(() => [
    ts_rank(
      to_tsvector(of(Article, 'content'), 'english'),
      to_tsquery('search', 'english')
    )
  ]);

JSON / JSONB

jsonb_set, jsonb_insert, jsonb_path_query, jsonb_path_query_array, json_agg, jsonb_agg

Full-text search

to_tsvector, to_tsquery, plainto_tsquery, ts_rank, ts_rank_cd, setweight

Date/time

date_part, age, timezone

Math/percentiles

width_bucket, percentile_cont, percentile_disc

pgcrypto

digest, hmac, gen_random_uuid, crypt, gen_salt, pgp_sym_encrypt, pgp_sym_decrypt, pgp_sym_encrypt_bytea, pgp_sym_decrypt_bytea, pgp_pub_encrypt, pgp_pub_decrypt, pgp_pub_encrypt_bytea, pgp_pub_decrypt_bytea

Requires pgcrypto extension.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

PostGIS

st_point, st_geom_from_text, st_as_text, st_distance, st_intersects, st_geog_from_text, st_distance_sphere, st_dwithin, st_as_geojson, st_geom_from_geojson, st_transform, st_buffer, st_area, st_length

Requires postgis extension.

CREATE EXTENSION IF NOT EXISTS postgis;

Transactions

import {IsolationLevel} from '@ts-awesome/orm';

const tx = await driver.begin(IsolationLevel.Serializable);
try {
  await tx.execute(compiledQuery);
  await tx.commit();
} catch (error) {
  await tx.rollback();
  throw error;
}

Supported isolation levels: Serializable, RepeatableRead, ReadCommitted.

Error mapping

PgExecutor maps common PostgreSQL errors to typed exceptions:

• DuplicateValueDbError (23505)
• FkViolatedDbError (23503)
• DbError for other err.code values

Named parameters

Use NamedParameter to share parameter values between queries or drivers.

import {NamedParameter} from '@ts-awesome/orm';

const shared = new NamedParameter('shared');
driver.setNamedParameter(shared, 'value');

Timestamp parsing

PgExecutor registers a TIMESTAMP (OID 1114) parser to return UTC Date values.

License

May be freely distributed under the MIT license.

Copyright (c) 2022 Volodymyr Iatsyshyn and other contributors

Quickstart

import {dbField, dbTable, Select} from '@ts-awesome/orm';
import {createPgDriver, PgCompiler} from '@ts-awesome/orm-pg';

@dbTable('users')
class User {
  @dbField({primaryKey: true})
  public id!: number;

  @dbField
  public name!: string;
}

const {pool, driver} = createPgDriver({connectionString: process.env.DATABASE_URL});
const compiler = new PgCompiler();

const compiled = compiler.compile(Select(User).where(({name}) => name.like('A%')).limit(10));
const users = await driver.execute(compiled, User);

await pool.end();