# @ts-awesome/orm-pg

> TypeScript ORM PostgreSQL driver

Latest version **2.0.0-alpha.0** (published 2026-02-04) · MIT license · 0 weekly downloads

## Install

```sh
npm install @ts-awesome/orm-pg
pnpm add @ts-awesome/orm-pg
yarn add @ts-awesome/orm-pg
bun add @ts-awesome/orm-pg
```

## Health

**Score 55/100 (C)** — status: stable.

Positive: has types; no vulnerabilities; high quality score.

Warnings: low downloads; no esm support.

## Facts

| | |
|---|---|
| Version | 2.0.0-alpha.0 |
| Published | 2026-02-04 |
| First published | 2020-12-16 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | bundled |
| Module format | CommonJS |
| Dependencies | 3 |
| Unpacked size | 143.1 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 2 |
| Author | Volodymyr Iatsyshyn |
| Maintainers | ts-awesome-bot, viatsyshyn |

## Links

- npm: https://www.npmjs.com/package/@ts-awesome/orm-pg
- Repository: https://github.com/ts-awesome/orm-pg
- Homepage: https://github.com/ts-awesome/orm-pg#readme
- Issues: https://github.com/ts-awesome/orm-pg/issues
- npm.io page: https://npm.io/package/@ts-awesome/orm-pg

## Dependencies (3)

- [yesql](https://npm.io/package/yesql.md) ^6.1.0
- [@types/pg](https://npm.io/package/@types/pg.md) ^8.10.1
- [reflect-metadata](https://npm.io/package/reflect-metadata.md) ^0.2.2

## Recent versions

- 2.0.0-alpha.0 (latest) — 2026-02-04
- 1.9.0-rc.3 — 2026-02-02
- 1.9.0-rc.2 — 2026-01-30
- 1.9.0-rc.1 — 2026-01-29
- 1.9.0-rc.0 — 2026-01-25
- 1.7.0 — 2024-12-04
- 1.7.0-rc.0 — 2024-12-04
- 1.6.1 — 2024-11-08
- 1.6.0 — 2024-08-08
- 1.6.0-rc6 — 2024-07-12
- 1.6.0-rc5 — 2024-07-02
- 1.6.0-rc4 — 2024-07-02
- 1.6.0-rc3 — 2024-05-17
- 1.6.0-rc2 — 2024-05-17
- 1.6.0-rc1 — 2024-04-11
- … 41 more at https://npm.io/package/@ts-awesome/orm-pg/versions

## README

# @ts-awesome/orm-pg

TypeScript ORM PostgreSQL driver for [@ts-awesome/orm](https://github.com/ts-awesome/orm)

Key features:

* uses [yesql](https://github.com/pihvi/yesql#readme) 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<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](https://dba.stackexchange.com/questions/68266/what-is-the-best-way-to-store-an-email-address-in-postgresql)

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-]{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.

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

### Example

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

### 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.

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

```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
May be freely distributed under the [MIT license](https://opensource.org/licenses/MIT).

Copyright (c) 2022 Volodymyr Iatsyshyn and other contributors
## Quickstart

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

---
_Source: https://npm.io/package/@ts-awesome/orm-pg · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._
