@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();
);
Encrypted kinds
__INLINE_CODE_9__, __INLINE_CODE_10__, and __INLINE_CODE_11__ rely on pgcrypto and a shared key.
__CODE_BLOCK_7__Function wrappers
Function wrappers live under __INLINE_CODE_12__ and are exported from the package entry (__INLINE_CODE_13__). See __INLINE_CODE_14__ for the full list.
Example
__CODE_BLOCK_8__JSON / JSONB
__INLINE_CODE_15__, __INLINE_CODE_16__, __INLINE_CODE_17__, __INLINE_CODE_18__, __INLINE_CODE_19__, __INLINE_CODE_20__
Full-text search
__INLINE_CODE_21__, __INLINE_CODE_22__, __INLINE_CODE_23__, __INLINE_CODE_24__, __INLINE_CODE_25__, __INLINE_CODE_26__
Date/time
__INLINE_CODE_27__, __INLINE_CODE_28__, __INLINE_CODE_29__
Math/percentiles
__INLINE_CODE_30__, __INLINE_CODE_31__, __INLINE_CODE_32__
pgcrypto
__INLINE_CODE_33__, __INLINE_CODE_34__, __INLINE_CODE_35__, __INLINE_CODE_36__, __INLINE_CODE_37__, __INLINE_CODE_38__, __INLINE_CODE_39__, __INLINE_CODE_40__, __INLINE_CODE_41__, __INLINE_CODE_42__, __INLINE_CODE_43__, __INLINE_CODE_44__, __INLINE_CODE_45__
Requires __INLINE_CODE_46__ extension.
__CODE_BLOCK_9__PostGIS
__INLINE_CODE_47__, __INLINE_CODE_48__, __INLINE_CODE_49__, __INLINE_CODE_50__, __INLINE_CODE_51__, __INLINE_CODE_52__, __INLINE_CODE_53__, __INLINE_CODE_54__, __INLINE_CODE_55__, __INLINE_CODE_56__, __INLINE_CODE_57__, __INLINE_CODE_58__, __INLINE_CODE_59__, __INLINE_CODE_60__
Requires __INLINE_CODE_61__ extension.
__CODE_BLOCK_10__Transactions
__CODE_BLOCK_11__Supported isolation levels: __INLINE_CODE_62__, __INLINE_CODE_63__, __INLINE_CODE_64__.
Error mapping
__INLINE_CODE_65__ maps common PostgreSQL errors to typed exceptions:
- __INLINE_CODE_66__ (__INLINE_CODE_67__)
- __INLINE_CODE_68__ (__INLINE_CODE_69__)
- __INLINE_CODE_70__ for other __INLINE_CODE_71__ values
Named parameters
Use __INLINE_CODE_72__ to share parameter values between queries or drivers.
__CODE_BLOCK_12__Timestamp parsing
__INLINE_CODE_73__ registers a TIMESTAMP (OID 1114) parser to return UTC __INLINE_CODE_74__ values.
License
May be freely distributed under the MIT license.
Copyright (c) 2022 Volodymyr Iatsyshyn and other contributors