npm.io
2.0.0-alpha.0 • Published 9 months ago

@ts-awesome/orm-pg

Licence
MIT
Version
2.0.0-alpha.0
Deps
3
Size
143 kB
Vulns
0
Weekly
0
Stars
2

@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

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__

__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

Quickstart

__CODE_BLOCK_13__