@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

``bash
npm install @ts-awesome/orm-pg pg
`

Extensions

Some features require PostgreSQL extensions:

`sql
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.

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

Standalone

`ts
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

`ts
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

`ts
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>(SqlQueryDriverSymbol)
.toDynamicValue(() => new PgDriver(pool))

container.bind>(SqlQueryBuildableQueryCompilerSymbol)
.to(PgCompiler)
`

Kinds

This package provides ORM kinds for:

$3

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

$3

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

$3

JSONB variant with read/write casting to jsonb

$3

UUID casting for uuid fields

$3

INET casting for IP address fields

$3

TIMESTAMPTZ read/write casting with JS Date

$3

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

$3

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

$3

MONEY casting with number conversion (locale/format dependent)

$3

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

$3

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

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

$3

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

`ts
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.

$3

`ts
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')
)
]);
`

$3

jsonb_set, jsonb_insert, jsonb_path_query, jsonb_path_query_array, json_agg, jsonb_agg

$3

to_tsvector, to_tsquery, plainto_tsquery, ts_rank, ts_rank_cd, setweight

$3

date_part, age, timezone

$3

width_bucket, percentile_cont, percentile_disc

$3

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.

`sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;
`

$3

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.

`sql
CREATE EXTENSION IF NOT EXISTS postgis;
`

Transactions

`ts
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.

`ts
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

`ts
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();
``