@ts-awesome/orm

TypeScript-friendly, minimalistic object relational mapping library.

Key features:

• strong object mapping with @ts-awesome/model-reader
• no relation navigation (intentional)
• heavy use of type checks and lambdas
• supports a common subset of SQL
• built-in unit testing driver

No relation navigation is intentional: relationships are expressed in queries so you always control joins and payload shape.

Install

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

Quick start

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

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

  @dbField
  public name!: string;
}

const compiler = new PgCompiler();
const driver: IQueryExecutor<ISqlQuery> = /* your driver instance */;

const query: IBuildableQuery = Select(User).where({name: 'Alice'}).limit(1);
const compiled: ISqlQuery = compiler.compile(query);
const results: User[] = await driver.execute(compiled, User);

Supported drivers

• PostgreSQL: @ts-awesome/orm-pg
• SQLite: @ts-awesome/orm-sqlite
• Firebird: @ts-awesome/orm-firebird
• Other drivers may be available on request

Model declaration

Model metadata is defined with dbTable and dbField decorators.

import {dbField, dbTable, Branded} from '@ts-awesome/orm';
import {DB_JSON} from '@ts-awesome/orm-pg'; // or other driver

type FirstModelId = Branded<number, 'FirstModelId'>;
type AuthorId = Branded<number, 'AuthorId'>;

class SubDocumentModel {
  public title!: string;
}

const enum UserStatus {
  Active = 1,
  Inactive = 0,
}

enum UserRole {
  Admin = 'admin',
  User = 'user',
}

@dbTable('first_table')
class FirstModel {
  // numeric autoincrement primary key
  @dbField({primaryKey: true, autoIncrement: true})
  public id!: FirstModelId;

  // just another field
  @dbField
  public title!: string;

  // let's map prop to different field
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;

  // nullable field requires explicit model and nullable
  // these are direct matches to @ts-awesome/model-reader
  @dbField({
    model: String,
    nullable: true,
  })
  public description!: string | null;

  // JSON column with model conversion
  @dbField({
    kind: DB_JSON, // data will be stored as JSON
    model: SubDocumentModel, // and will be converted to instance of SubDocumentModel
    nullable: true,
  })
  public document!: SubDocumentModel | null;

  // numeric enum support
  @dbField({model: UserStatus})
  public status!: UserStatus;

  // string enum support
  @dbField({model: UserRole})
  public role!: UserRole;

  // readonly field with database default
  @dbField({name: 'created_at', readonly: true})
  public createdAt!: Date;
}

Common decorator options

• primaryKey: marks a primary key column
• autoIncrement: enables auto-increment on numeric keys
• name: maps a property to a different column name
• readonly: marks a column as DB-managed (insert/update ignored)
• nullable: allows null in the model type
• model: overrides the inferred model type (enums, custom classes)
• kind: custom read/write hooks for DB-specific types
• sensitive: hides field values unless explicitly requested by reader/driver
• default: default DB value (used for metadata and inserts)

Table indexes for upsert

@dbTable can declare unique indexes used by Upsert().conflict().

import {dbField, dbTable} from '@ts-awesome/orm';

@dbTable('users', [
  {name: 'users_email_unique', fields: ['email'], default: true},
])
class User {
  @dbField({primaryKey: true})
  public id!: number;

  @dbField
  public email!: string;
}

Custom field kinds

Use kind for custom read/write transformations and query wrapping.

import {dbField, dbTable} from '@ts-awesome/orm';

const BoolAsNumber = {
  reader: (raw: unknown) => raw === 1,
  writer: (value: boolean) => (value ? 1 : 0),
};

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

  @dbField({kind: BoolAsNumber})
  public enabled!: boolean;
}

Derived fields

@dbFilterField lets you expose subquery-based fields without relation navigation. It requires a single-column primary key on the source table.

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

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

  @dbField
  public userId!: number;

  @dbField
  public name!: string;
}

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

  @dbFilterField((id, _table) => Select(Role)
    .columns(({name}) => [name])
    .where(({userId}) => userId.eq(id))
    .limit(1))
  public roleName!: string;
}

@dbManyField exists but is deprecated; use @dbFilterField instead.

Sensitive fields

Fields marked as sensitive are omitted unless you pass true to the reader/driver.

import {dbField, dbTable, Select} from '@ts-awesome/orm';
import {TestCompiler, TestDriver} from '@ts-awesome/orm/test-driver';

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

  @dbField({sensitive: true})
  public passwordHash!: string;
}

const driver = new TestDriver();
const compiler = new TestCompiler();
const results = await driver.execute(compiler.compile(Select(User)), User, true);

Vanilla select

import {Branded, dbField, dbTable, IBuildableQuery, IQueryExecutor, Select} from '@ts-awesome/orm';
import {ISqlQuery, PgCompiler} from '@ts-awesome/orm-pg'; // or other driver

type AuthorId = Branded<number, 'AuthorId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;
}

const compiler = new PgCompiler();
const driver: IQueryExecutor<ISqlQuery>;

const query: IBuildableQuery = Select(FirstModel).where({authorId: 5 as AuthorId}).limit(10);
const compiled: ISqlQuery = compiler.compile(query);
const results: FirstModel[] = await driver.execute(compiled, FirstModel);

For more streamlined use, please check @ts-awesome/model-reader.

Select builder

ORM provides a way to use model declaration to your advantage: TypeScript will check if fields exist, and will check operands for compatible types.

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

type AuthorId = Branded<number, 'AuthorId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;
}

const query = Select(FirstModel)
  // authorId = 5;
  .where({authorId: '5'}) // gives error, it can be number (branded AuthorId) only
  .limit(10);

For more complex logic, ORM provides a WhereBuilder.

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

type AuthorId = Branded<number, 'AuthorId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;
}

const query = Select(FirstModel)
  // authorId = 5;
  .where(({authorId}) => authorId.eq(5 as AuthorId))
  .limit(10);

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

type AuthorId = Branded<number, 'AuthorId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;

  @dbField({model: String, nullable: true})
  public description!: string | null;
}

const query = Select(FirstModel)
  // authorId in (5, 6)
  .where(({authorId, description}) => authorId.in([5 as AuthorId, 6 as AuthorId]))
  .limit(10);

import {and, Branded, dbField, dbTable, Select} from '@ts-awesome/orm';

type AuthorId = Branded<number, 'AuthorId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;

  @dbField({model: String, nullable: true})
  public description!: string | null;
}

const query = Select(FirstModel)
  // authorId = 5 AND description LIKE 'some%';
  .where(({authorId, description}) => and(authorId.eq(5 as AuthorId), description.like('some%')))
  .limit(10);

Overview of operators and functions:

• Generic comparable:
  • left.eq(right) equivalent to left = right or left IS NULL if right === null
  • left.neq(right) equivalent to left <> right or left IS NOT NULL if right === null
  • left.gt(right) equivalent to left > right
  • left.gte(right) equivalent to left >= right
  • left.lt(right) equivalent to left < right
  • left.lte(right) equivalent to left <= right
  • left.between(a, b) equivalent to left BETWEEN (a, b)
• Strings
  • left.like(right) equivalent to left LIKE right
• Arrays
  • left.in(right) equivalent to left IN right
  • left.has(right) equivalent to right IN left
• Math
  • left.add(right) equivalent to left + right
  • left.sub(right) equivalent to left - right
  • left.mul(right) equivalent to left * right
  • left.div(right) equivalent to left / right
  • left.mod(right) equivalent to left % right
• Binary logic
  • left.and(right) equivalent to left & right
  • left.or(right) equivalent to left | right
  • left.xor(right) equivalent to left ^ right
• Logic
  • and(op1, op2, op3) equivalent to op1 AND op2 AND op3
  • or(op1, op2, op3) equivalent to op1 OR op2 OR op3
  • not(op) equivalent to NOT op
• Subqueries
  • all(query) equivalent to ALL (compiled query)
  • any(query) equivalent to ANY (compiled query)
  • exists(query) equivalent to EXISTS (compiled query)
• Aggregation functions
  • avg(expr) equivalent to AVG (expr)
  • max(expr) equivalent to MAX (expr)
  • min(expr) equivalent to MIN (expr)
  • sum(expr) equivalent to SUM (expr)
  • count(expr) equivalent to COUNT (expr)
  • count(expr, true) equivalent to COUNT(DISTINCT expr)
  • stddev_pop, stddev_samp, var_pop, var_samp
• Date & Time
  • now(), current_date(), current_timestamp()
  • extract(field, source) equivalent to EXTRACT(field FROM source)
  • date_trunc(part, source) equivalent to DATE_TRUNC(part, source)
• String
  • concat(s1, s2, ...), lower(s), upper(s), length(s)
  • trim(s), ltrim(s), rtrim(s)
  • substring(s, start, len), position(sub in str), replace(str, from, to)
  • lpad(s, len, pad), rpad(s, len, pad), repeat(s, n)
  • left(s, n), right(s, n), reverse(s)
• Math
  • abs(x), ceil(x), floor(x), round(x, d)
  • power(b, e), sqrt(x), mod(x, y)
  • exp(x), ln(x), log(x, base), trunc(x, d), pi(), sign(x), random()
• Conditional
  • coalesce(v1, v2, ...), nullif(v1, v2)
  • greatest(v1, v2, ...), least(v1, v2, ...)
  • case_({when: cond, then: val}, ..., {else: val})
• Casting
  • cast(expr, type) equivalent to CAST(expr AS type)

Column references without a model

import {of, Select} from '@ts-awesome/orm';

const query = Select(FirstModel)
  .orderBy(() => [of(null, 'score')]);

Joining

Sometimes you may need to perform joins for filtering.

import {Branded, dbField, dbTable, of, Select} from '@ts-awesome/orm';

type AuthorId = Branded<number, 'AuthorId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;
}

@dbTable('second_table')
class SecondModel {
  @dbField({primaryKey: true, autoIncrement: true})
  public id!: AuthorId;

  @dbField
  public name!: string;
}

const query = Select(FirstModel)
  // let's join SecondModel by FK
  .join(SecondModel, (root, other) => root.authorId.eq(other.id))
  // let's filter by author name
  .where(() => of(SecondModel, 'name').like('John%'))
  .limit(10);

In some cases TableRef might be handy, especially if you need to join the same table multiple times.

import {dbField, dbTable, Branded, of, or, Select, TableRef} from '@ts-awesome/orm';

type AuthorId = Branded<number, 'AuthorId'>;
type ThirdModelId = Branded<number, 'ThirdModelId'>;

@dbTable('second_table')
class SecondModel {
  @dbField({primaryKey: true, autoIncrement: true})
  public id!: AuthorId;

  @dbField
  public name!: string;
}

@dbTable('third_table')
class ThirdModel {
  @dbField({primaryKey: true, autoIncrement: true})
  public id!: ThirdModelId;

  @dbField
  public createdBy!: AuthorId;

  @dbField
  public ownedBy!: AuthorId;
}

const ownerRef = new TableRef(SecondModel);
const creatorRef = new TableRef(SecondModel);
const query = Select(ThirdModel)
  // let's join SecondModel by FK
  .join(SecondModel, ownerRef, (root, other) => root.ownedBy.eq(other.id))
  // let's join SecondModel by FK
  .join(SecondModel, creatorRef, (root, other) => root.createdBy.eq(other.id))
  // let's filter by owner or creator name
  .where(() => or(
    of(ownerRef, 'name').like('John%'),
    of(creatorRef, 'name').like('John%'),
  ))
  .limit(10);

Grouping

import {alias, Branded, count, dbField, dbTable, min, Select} from '@ts-awesome/orm';

type AuthorId = Branded<number, 'AuthorId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;

  @dbField
  public title!: string;

  @dbField({name: 'created_at'})
  public createdAt!: Date;
}

const ts: Date; // some timestamp in the past
const query = Select(FirstModel)
  // we need titles to contain `key`
  .where(({title}) => title.like('%key%'))
  // group by authors
  .groupBy(['authorId'])
  // filter to have first publication not before ts
  .having(({createdAt}) => min(createdAt).gte(ts))
  // result should have 2 columns: authorId and count
  .columns(({authorId}) => [authorId, alias(count(), 'count')]);

Ordering

import {Branded, dbField, dbTable, desc, of, Select} from '@ts-awesome/orm';

type AuthorId = Branded<number, 'AuthorId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;

  @dbField
  public title!: string;
}

@dbTable('second_table')
class SecondModel {
  @dbField({primaryKey: true})
  public id!: AuthorId;

  @dbField
  public name!: string;
}

const query = Select(FirstModel)
  // let's join SecondModel by FK
  .join(SecondModel, (root, other) => root.authorId.eq(other.id))
  // let's sort by author and title reverse
  .orderBy(({title}) => [of(SecondModel, 'name'), desc(title)])
  .limit(10);

Pagination

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

type AuthorId = Branded<number, 'AuthorId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;
}

const query = Select(FirstModel)
  .where(({authorId}) => authorId.eq(5 as AuthorId))
  .limit(10)
  .offset(20);

Distinct and FOR UPDATE

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

type FirstModelId = Branded<number, 'FirstModelId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({primaryKey: true})
  public id!: FirstModelId;

  @dbField({name: 'author_id'})
  public authorId!: number;
}

const distinctQuery = Select(FirstModel, true)
  .columns(({authorId}) => [authorId]);

const lockedQuery = Select(FirstModel, 'UPDATE')
  .where(({id}) => id.eq(1 as FirstModelId));

Supported FOR modes: 'UPDATE' | 'NO KEY UPDATE' | 'SHARE' | 'KEY SHARE'.

Set operations

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

enum UserStatus {
  Active = 1,
  Inactive = 0,
}

@dbTable('first_table')
class FirstModel {
  @dbField({model: UserStatus})
  public status!: UserStatus;
}

const query = Select(FirstModel)
  .where(({status}) => status.eq(UserStatus.Active))
  .union(true, Select(FirstModel).where(({status}) => status.eq(UserStatus.Inactive)));

Set operations require compatible column lists; the boolean flag toggles DISTINCT.

Join types

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

type AuthorId = Branded<number, 'AuthorId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;
}

@dbTable('second_table')
class SecondModel {
  @dbField({primaryKey: true})
  public id!: AuthorId;
}

const query = Select(FirstModel)
  .joinLeft(SecondModel, (root, other) => root.authorId.eq(other.id));

Join variants include joinLeft, joinRight, and joinFull.

Scalar subqueries

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

const subquery = Select(FirstModel)
  .columns(({authorId}) => [authorId])
  .where(({id}) => id.eq(1 as FirstModelId))
  .asScalar();

Other builders

ORM provides Insert, Update, Upsert and Delete builders.

Insert

import {dbField, dbTable, Insert} from '@ts-awesome/orm';

@dbTable('first_table')
class FirstModel {
  @dbField
  public title!: string;
}

const query = Insert(FirstModel)
  .values({
    title: 'New book'
  });

Update

import {Branded, dbField, dbTable, Update} from '@ts-awesome/orm';

type FirstModelId = Branded<number, 'FirstModelId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({primaryKey: true})
  public id!: FirstModelId;

  @dbField
  public title!: string;
}

const query = Update(FirstModel)
  .values({
    title: 'New book'
  })
  .where(({id}) => id.eq(2 as FirstModelId));

Upsert

import {Branded, dbField, dbTable, Upsert} from '@ts-awesome/orm';

type FirstModelId = Branded<number, 'FirstModelId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({primaryKey: true})
  public id!: FirstModelId;

  @dbField
  public title!: string;
}

const query = Upsert(FirstModel)
  .values({
    title: 'New book'
  })
  .where(({id}) => id.eq(2 as FirstModelId))
  // conflict resolution index is defined in @dbTable decorator
  .conflict('index_name');

Delete

import {Branded, dbField, dbTable, Delete} from '@ts-awesome/orm';

type AuthorId = Branded<number, 'AuthorId'>;

@dbTable('first_table')
class FirstModel {
  @dbField({name: 'author_id'})
  public authorId!: AuthorId;
}

const query = Delete(FirstModel)
  .where(({authorId}) => authorId.eq(2 as AuthorId));

Window Functions

The ORM supports standard SQL window functions using the Window builder.

import {alias, dbField, dbTable, desc, row_number, Select, Window} from '@ts-awesome/orm';

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

  @dbField({name: 'author_id'})
  public authorId!: number;

  @dbField({name: 'created_at'})
  public createdAt!: Date;
}

// Define the window
const w = new Window(FirstModel)
  .partitionBy(['authorId'])
  .orderBy(desc('createdAt'));

const query = Select(FirstModel)
  .columns(({id, authorId}) => [
    id,
    authorId,
    // Use the window definition
    alias(row_number(w), 'row_num')
  ]);

Supported functions: row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value, nth_value.

Window framing is supported via range(), rows(), groups(), and start()/end()/exclusion().

const framed = new Window(FirstModel)
  .partitionBy(['authorId'])
  .orderBy(desc('createdAt'))
  .rows()
  .start(1, 'PRECEDING')
  .end('CURRENT ROW');

Branded Types

To improve type safety for IDs, you can use Branded<T, Brand>.

import {Branded, dbField, dbTable} from '@ts-awesome/orm';

// Branded ID types (best practice)
type UserId = Branded<number, 'UserId'>;

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

// Now you can't accidentally pass a plain number or a different ID type
// const user: User = ...;
// const otherId: OrderId = 5;
// user.id = otherId; // Error
// user.id = 5 as UserId; // OK

// For string-based UIDs (UUID, NanoID, etc.)
type OrderUid = Branded<string, 'OrderUid'>;

@dbTable('orders')
class Order {
  @dbField({primaryKey: true})
  public uid!: OrderUid;
}

// const orderUid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' as OrderUid;

Array models are supported using model: [Class].

@dbTable('groups')
class Group {
  @dbField({model: [String]})
  public tags!: string[];
}

Test Driver

The @ts-awesome/orm/test-driver module provides a powerful mechanism to unit test your services without a real database. It allows you to mock query results and inspect executed queries.

import {dbField, dbTable, Select} from '@ts-awesome/orm';
import {TestCompiler, TestDriver} from '@ts-awesome/orm/test-driver';

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

const driver = new TestDriver();
const compiler = new TestCompiler();

// Mock results
driver.whenSelect('users').return([{ id: 1, name: 'Alice' }]);

// Assertions
expect(driver.executedQueries[0].tableName).toBe('users');

// Include sensitive fields when reading
const results = await driver.execute(compiler.compile(Select(User)), User, true);

For full documentation, please refer to the Test Driver README.

License

May be freely distributed under the MIT license.

Copyright (c) 2022 Volodymyr Iatsyshyn and other contributors