TypeScript friendly minimalistic Object Relation Mapping library
Key features:
- strong object mapping with @ts-awesome/model-reader
- no relation navigation - intentional
- heavy use of type checks and lambdas
- support common subset of SQL
Each model metadata is defined with dbTable
and dbField
decorators
import {dbField, dbField} from "@ts-awesome/orm";
import {DB_JSON} from "@ts-awesome/orm-pg"; // or other driver
@dbTable('first_table')
class FirstModel {
// numeric autoincrement primary key
@dbField({primaryKey: true, autoIncrement: true})
public id!: number;
// just another field
@dbField
public title!: string;
// lets map prop to different field
@dbField({name: 'author_id'})
public authorId!: number;
// nullable field requires explicit model and nullable
// these are direct match to @ts-awesome/model-reader
@dbField({
model: String,
nullable: true,
})
public description!: string | null;
// advanced use case
@dbModel({
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
// readonly field with database default
@dbField({name: 'created_at', readonly: true})
public createdAt!: Date;
}
import {IBuildableQuery, IQueryExecutor, Select} from "@ts-awesome/orm";
import {ISqlQuery, PgCompiler} from "@ts-awesome/orm-pg"; // or other driver
const compiler = new PgCompiler();
const driver: IQueryExecutor<ISqlQuery>;
const query: IBuildableQuery = Select(FirstModel).where({authorId: 5}).limit(10);
const compiled: ISqlQuery = compiler.compile(query);
const results: FirstModel[] = await driver.execute(compiled, FirstModel);
For more streamlined use please check @ts-awesome/entity
ORM provides a way to use model declaration to your advantage: TypeScript will check is fields exists. And TypeScript will check operands for compatible types.
const query = Select(FirstModel)
// authorId = 5;
.where({authorId: '5'}) // gives error, it can be number only
.limit(10);
For more complex logic ORM provides WhereBuilder
const query = Select(FirstModel)
// authorId = 5;
.where(({authorId}) => authorId.eq(5))
.limit(10);
const query = Select(FirstModel)
// authorId in (5, 6)
.where(({authorId, description}) => authorId.in([5, 6]))
.limit(10);
const query = Select(FirstModel)
// authorId = 5 AND description LIKE 'some%';
.where(({authorId, description}) => and(authorId.eq(5), description.like('some%')))
.limit(10);
- Generic comparable:
- left.
eq
(right) equivalent to left=
right or leftIS NULL
if right === null - left.
neq
(right) equivalent to left<>
right or leftIS 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 left BETWEEN (a, b)
- left.
- Strings
- left.
like
(right) equivalent to leftLIKE
right
- left.
- Arrays
- left.
in
(right) equivalent to leftIN
right - left.
has
(right) equivalent to rightIN
left
- 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
- left.
- Binary logic
- left.
and
(right) equivalent to left&
right - left.
or
(right) equivalent to left|
right - left.
xor
(right) equivalent to left^
right
- left.
- Logic
-
and
(op1, op2, op3) equivalent to op1AND
op2AND
op3 -
or
(op1, op2, op3) equivalent to op1OR
op2OR
op3 -
not
(op) equivalent toNOT
op
-
- Subqueries
-
all
(query) equivalent toALL
(compiled query) -
any
(query) equivalent toANY
(compiled query) -
exists
(query) equivalent toEXISTS
(compiled query)
-
- Aggregation functions
-
avg
(expr) equivalent toAVG
(expr) -
max
(expr) equivalent toMAX
(expr) -
min
(expr) equivalent toMIN
(expr) -
sum
(expr) equivalent toSUM
(expr) -
count
(expr) equivalent tocount
(expr)
-
Sometimes you may need to perform some joins for filtering
import {dbTable, dbField} from "@ts-awesome/orm";
@dbTable('second_table')
class SecondModel {
@dbField({primatyKey: true, autoIncrement: true})
public id!: number;
@dbField
public name!: string;
}
const query = Select(FirstModel)
// lets join SecondModel by FK
.join(SecondModel, (root, other) => root.authorId.eq(other.id))
// lets filter by author name
.where(() => of(SecondModel, 'name').like('John%'))
.limit(10)
In some cases TableRef
might be handy, especially of need to join same table multiple times
import {dbTable, dbField} from "@ts-awesome/orm";
@dbTable('second_table')
class SecondModel {
@dbField({primatyKey: true, autoIncrement: true})
public id!: number;
@dbField
public name!: string;
}
@dbTable('third_table')
class ThirdModel {
@dbField({primatyKey: true, autoIncrement: true})
public id!: number;
@dbField
public createdBy!: number;
@dbField
public ownedBy!: number;
}
const ownerRef = new TableRef(SecondModel);
const creatorRef = new TableRef(SecondModel);
const query = Select(ThirdModel)
// lets join SecondModel by FK
.join(SecondModel, ownerRef, (root, other) => root.ownedBy.eq(other.id))
// lets join SecondModel by FK
.join(SecondModel, creatorRef, (root, other) => root.createdBy.eq(other.id))
// lets filter by owner or creator name
.where(() => or(
of(ownerRef, 'name').like('John%'),
of(creatorRef, 'name').like('John%'),
))
.limit(10)
import {Select, min, count, alias} from '@ts-awesome/orm'
const ts: Date; // some timestamp in 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')])
import {Select, desc} from '@ts-awesome/orm'
const query = Select(FirstModel)
// lets join SecondModel by FK
.join(SecondModel, (root, other) => root.authorId.eq(other.id))
// lets sort by author and title reverse
.orderby(({title}) => [of(SecondModel, 'name'), desc(title)])
.limit(10)
ORM provides Insert
, Update
, Upset
and Delete
builders
import {Insert} from '@ts-awesome/orm';
const query = Insert(FirstModel)
.values({
title: 'New book'
})
import {Update} from '@ts-awesome/orm';
const query = Update(FirstModel)
.values({
title: 'New book'
})
.where(({id}) => id.eq(2))
import {Upsert} from '@ts-awesome/orm';
const query = Upsert(FirstModel)
.values({
title: 'New book'
})
.where(({id}) => id.eq(2))
// conflict resolution index is defined in @dbTable decorator
.conflict('index_name')
import {Delete} from '@ts-awesome/orm';
const query = Delete(FirstModel)
.where(({authorId}) => authorId.eq(2))
May be freely distributed under the MIT license.
Copyright (c) 2022 Volodymyr Iatsyshyn and other contributors