*** Important upgrade notice ***

Because TypeScript 4.1 supports template literal string types, the function syntax is no longer necessary. You can now use strings while maintaining type-safety. The function syntax is removed since version 4.

To help with the upgrade, you can run npx ew-query-repository -u string-parameters to automatically switch over to the string syntax.

ew-query-repository

Standing on the shoulders of Knex.js, but now everything is typed!

Goals:

• Be useful for 80% of the use cases, for the other 20% easily switch to lower-level Knex.js.
• Be as concise a possible.
• Mirror Knex.js as much a possible, with these exceptions:
  • Don't use this.
  • Be selective on what returns a Promise and what not.
  • Less overloading, which makes typings easier and code completion better.
• Get the most of the benefits TypeScript provides: type-checking of parameters, typed results, rename refactorings.

Install:

npm install @expresswebjs/ew-query-repository

Make sure experimentalDecorators and emitDecoratorMetadata are turned on in your tsconfig.json:

{
    "compilerOptions": {
        "experimentalDecorators": true,
        "emitDecoratorMetadata": true,
        ...
    },
    ...
}

Tested with Knex.js v0.95.0, TypeScript v4.2.3 and Node.js v14.11.0

Breaking changes in v4

• Because TypeScript 4.1 supports template literal string types, the function syntax is no longer necessary. You can now use strings while maintaining type-safety. The function syntax is removed. Run npx ew-query-repository -u string-parameters to automatically upgrade.
• .onColumn() is deprecated. Use .on(). Remember that the columns switched eg .onColumns(i=>i.prop1, '=' j=>j.prop2) should become .on("prop2", '=', "prop1"). Run npx ew-query-repository -u join-on-columns-to-on to automatically upgrade.
• The use of optional columns (@Column() public nickName?: string;) is deprecated. This was used to signal a nullable column. The correct way to do this is @Column() public nickName: string | null;.

Documentation

Quick example

To reference a column, use the name. Like this .select("name") or this .where("name", "Hejlsberg")

import * as Knex from "knex";
import { ModelRepository } from "@expresswebjs/ew-query-repository";

const knex = Knex({
    client: "pg",
    connection: "postgres://user:pass@localhost:5432/dbname",
});

async function example() {
    const model = new ModelRepository(knex);

    const query = model
        .query(User)
        .innerJoin("category")
        .where("name", "Hejlsberg")
        .select("id", "category.name");

    const oneUser = await query.getSingle();

    console.log(oneUser.id); // Ok
    console.log(oneUser.category.name); // Ok
    console.log(oneUser.name); // Compilation error
}

Define tables

Use the Table decorator to reference a table and use the Column decorator to reference a column.

Use @Column({ primary: true }) for primary key columns.

Use @Column({ name: '[column name]' }) on property with the type of another Table to reference another table.

import { Column, Table } from "@expresswebjs/ew-query-repository";

@Table("userCategories")
export class UserCategory {
    @Column({ primary: true })
    public id: string;
    @Column()
    public name: string;
    @Column()
    public year: number;
}

@Table("users")
export class User {
    @Column({ primary: true })
    public id: string;
    @Column()
    public name: string;
    @Column()
    public categoryId: string;
    @Column({ name: "categoryId" })
    public category: UserCategory;
    @Column()
    public someNullableValue: string | null;
}

Create instance

import * as Knex from "knex";
import { ModelRepository } from "@expresswebjs/ew-query-repository";

const knex = Knex({
    client: "pg",
    connection: "postgres://user:pass@localhost:5432/dbname",
});

const model = new ModelRepository(knex);

Helper

• getTableName
• getColumnName
• registerBeforeInsertTransform
• registerBeforeUpdateTransform

Querybuilder

General

• query
• transacting
• toQuery
• useKnexQueryBuilder
• keepFlat

Getting the results (Promises)

• findByPrimaryKey
• getFirstOrNull
• getFirst
• getSingleOrNull
• getSingle
• getMany
• getCount
• insertItem
• insertItems
• insertSelect
• del
• delByPrimaryKey
• updateItem
• updateItemByPrimaryKey
• updateItemsByPrimaryKey
• execute

Building the query

• select
• where
• andWhere
• orWhere
• whereNot
• whereColumn
• whereNull
• orWhereNull
• whereNotNull
• orWhereNotNull
• orderBy
• orderByRaw
• innerJoinColumn
• innerJoinTableOnFunction
• leftOuterJoinColumn
• leftOuterJoinTableOnFunction
• selectRaw
• selectQuery
• whereIn
• whereNotIn
• orWhereIn
• orWhereNotIn
• whereBetween
• whereNotBetween
• orWhereBetween
• orWhereNotBetween
• whereExists
• orWhereExists
• whereNotExists
• orWhereNotExists
• whereParentheses
• groupBy
• having
• havingNull
• havingNotNull
• havingIn
• havingNotIn
• havingExists
• havingNotExists
• havingBetween
• havingNotBetween
• union
• unionAll
• min
• count
• countDistinct
• max
• sum
• sumDistinct
• avg
• avgDistinct
• clearSelect
• clearWhere
• clearOrder
• limit
• offset
• whereRaw
• havingRaw
• truncate
• distinct
• clone
• groupByRaw

getTableName

const tableName = getTableName(User);

// tableName = 'users'

getColumnName

const columnName = getColumnName(User, "id");

// columnName = 'id'

registerBeforeInsertTransform

Hook that is run before doing an insert. Execute this function as soon as possible. For example at the top of index.ts or server.ts.

registerBeforeInsertTransform((item: any, typedQueryBuilder: ITypedQueryBuilder<{}, {}, {}>) => {
    if (typedQueryBuilder.columns.find(column => column.name === 'created_at') && !item.hasOwnProperty('created_at')) {
        item.created_at = new Date();
    }
    if (typedQueryBuilder.columns.find(column => column.name === 'updated_at') && !item.hasOwnProperty('updated_at')) {
        item.updated_at = new Date();
    }
    if (typedQueryBuilder.columns.find(column => column.name === 'id') && !item.hasOwnProperty('id')) {
        item.id = guid();
    }
    return item;
});

registerBeforeUpdateTransform

Hook that is run before doing an update. Execute this function as soon as possible. For example at the top of index.ts or server.ts.

registerBeforeUpdateTransform((item: any, typedQueryBuilder: ITypedQueryBuilder<{}, {}, {}>) => {
    if (typedQueryBuilder.columns.find("name" === 'updated_at') && !item.hasOwnProperty('updated_at')) {
        item.updated_at = new Date();
    }
    return item;
});

query

Use model.query(Type) to create a query for the table referenced by Type

const query = model.query(User);

select

https://knexjs.org/#Builder-select

model.query(User).select("id");

model.query(User).select("id","name");

where

https://knexjs.org/#Builder-where

model.query(User).where("name", "name");

Or with operator

model.query(User).where("name", "like", "%user%");

// select * from "users" where "users"."name" like '%user%'

andWhere

model
    .query(User)
    .where("name", "name")
    .andWhere("name", "name");

model
    .query(User)
    .where("name", "name")
    .andWhere("name", "like", "%na%");

orWhere

model
    .query(User)
    .where("name", "name")
    .orWhere("name", "name");

model
    .query(User)
    .where("name", "name")
    .orWhere("name", "like", "%na%");

whereNot

https://knexjs.org/#Builder-whereNot

model.query(User).whereNot("name", "name");

whereColumn

To use in subqueries. First parameter is for sub query columns and the third parameter is for columns from the parent query.

model.query(User).whereNotExists(UserSetting, (subQuery) => {
    subQuery.whereColumn("userId", "=", "id");
});

whereNull

model.query(User).whereNull("name");

orWhereNull

model
    .query(User)
    .whereNull("name")
    .orWhereNull("name");

whereNotNull

model.query(User).whereNotNull("name");

orWhereNotNull

model
    .query(User)
    .whereNotNull("name")
    .orWhereNotNull("name");

orderBy

model.query(User).orderBy("id");

orderByRaw

await model.query(User).orderByRaw("SUM(??) DESC", "users.year");

//  select * from "users" order by SUM("users"."year") DESC

innerJoinColumn

model.query(User).innerJoinColumn("category");

innerJoinTableOnFunction

model.query(User).innerJoinTableOnFunction("evilTwin", User, (join) => {
    join.on(
        "id",
        "=",
        "id"
    )
        .andOn(
            "name",
            "=",
            "id"
        )
        .orOn(
            "someValue",
            "=",
            "id"
        )
        .onVal("name", "=", "1")
        .andOnVal("name", "=", "2")
        .orOnVal("name", "=", "3")
        .onNull("name");
});

leftOuterJoinColumn

model.query(User).leftOuterJoinColumn("category");

leftOuterJoinTableOnFunction

model.query(User).leftOuterJoinTableOnFunction("evilTwin", User, (join) => {
    join.on(
        "id",
        "=",
        "id"
    )
        .andOn(
            "name",
            "=",
            "id"
        )
        .orOn(
            "someValue",
            "=",
            "id"
        )
        .onVal("name", "=", "1")
        .andOnVal("name", "=", "2")
        .orOnVal("name", "=", "3")
        .onNull("name");
});

selectRaw

model.query(User).selectRaw("otherId", Number, "select other.id from other");

selectQuery

model
    .query(UserCategory)
    .select("id")
    .selectQuery("total", Number, User, (subQuery) => {
        subQuery.count("id", "total").whereColumn("categoryId", "=", "id");
    });

select "userCategories"."id" as "id", (select count("users"."id") as "total" from "users" where "users"."categoryId" = "userCategories"."id") as "total" from "userCategories"

findByPrimaryKey

const user = await model.query(User).findByPrimaryKey("id", "d","name");

whereIn

model.query(User).whereIn("name", ["user1", "user2"]);

whereNotIn

model.query(User).whereNotIn("name", ["user1", "user2"]);

orWhereIn

model
    .query(User)
    .whereIn("name", ["user1", "user2"])
    .orWhereIn("name", ["user3", "user4"]);

orWhereNotIn

model
    .query(User)
    .whereIn("name", ["user1", "user2"])
    .orWhereNotIn("name", ["user3", "user4"]);

whereBetween

model.query(UserCategory).whereBetween("year", [1, 2037]);

whereNotBetween

model.query(User).whereNotBetween("year", [1, 2037]);

orWhereBetween

model
    .query(User)
    .whereBetween("year", [1, 10])
    .orWhereBetween("year", [100, 1000]);

orWhereNotBetween

model
    .query(User)
    .whereBetween("year", [1, 10])
    .orWhereNotBetween("year", [100, 1000]);

whereExists

model.query(User).whereExists(UserSetting, (subQuery) => {
    subQuery.whereColumn("userId", "=", "id");
});

orWhereExists

model.query(User).orWhereExists(UserSetting, (subQuery) => {
    subQuery.whereColumn("userId", "=", "id");
});

whereNotExists

model.query(User).whereNotExists(UserSetting, (subQuery) => {
    subQuery.whereColumn("userId", "=", "id");
});

orWhereNotExists

model.query(User).orWhereNotExists(UserSetting, (subQuery) => {
    subQuery.whereColumn("userId", "=", "id");
});

whereParentheses

model
    .query(User)
    .whereParentheses((sub) => sub.where("id", "1").orWhere("id", "2"))
    .orWhere("name", "Tester");

const queryString = query.toQuery();
console.log(queryString);

Outputs:

select * from "users" where ("users"."id" = '1' or "users"."id" = '2') or "users"."name" = 'Tester'

groupBy

model
    .query(User)
    .select("someValue")
    .selectRaw("total", Number, 'SUM("numericValue")')
    .groupBy("someValue");

having

model.query(User).having("numericValue", ">", 10);

havingNull

model.query(User).havingNull("numericValue");

havingNotNull

model.query(User).havingNotNull("numericValue");

havingIn

model.query(User).havingIn("name", ["user1", "user2"]);

havingNotIn

model.query(User).havingNotIn("name", ["user1", "user2"]);

havingExists

model.query(User).havingExists(UserSetting, (subQuery) => {
    subQuery.whereColumn("userId", "=", "id");
});

havingNotExists

model.query(User).havingNotExists(UserSetting, (subQuery) => {
    subQuery.whereColumn("userId", "=", "id");
});

havingBetween

model.query(User).havingBetween("numericValue", [1, 10]);

havingNotBetween

model.query(User).havingNotBetween("numericValue", [1, 10]);

union

model.query(User).union(User, (subQuery) => {
    subQuery.select("id").where("numericValue", 12);
});

unionAll

model
    .query(User)
    .select("id")
    .unionAll(User, (subQuery) => {
        subQuery.select("id").where("numericValue", 12);
    });

min

model.query(User).min("numericValue", "minNumericValue");

count

model.query(User).count("numericValue", "countNumericValue");

countDistinct

model.query(User).countDistinct("numericValue", "countDistinctNumericValue");

max

model.query(User).max("numericValue", "maxNumericValue");

sum

model.query(User).sum("numericValue", "sumNumericValue");

sumDistinct

model.query(User).sumDistinct("numericValue", "sumDistinctNumericValue");

avg

model.query(User).avg("numericValue", "avgNumericValue");

avgDistinct

model.query(User).avgDistinct("numericValue", "avgDistinctNumericValue");

clearSelect

model
    .query(User)
    .select("id")
    .clearSelect()
    .select("name");

clearWhere

model
    .query(User)
    .where("id", "name")
    .clearWhere()
    .where(("name", "name");

clearOrder

model
    .query(User)
    .orderBy("id")
    .clearOrder()
    .orderBy(("name");

limit

model.query(User).limit(10);

offset

model.query(User).offset(10);

useKnexQueryBuilder

Use useKnexQueryBuilder to get to the underlying Knex.js query builder.

const query = model.query(User)
    .useKnexQueryBuilder(queryBuilder => queryBuilder.where('somethingelse', 'value')
    .select("name");
);

keepFlat

Use keepFlat to prevent unflattening of the result.

const item = await model
    .query(User)
    .where("name", 'name')
    .innerJoinColumn("category");
    .select("name", "category.name")
    .getFirst();

// returns { name: 'user name', category: { name: 'category name' }}

const item = await model
    .query(User)
    .where("name", 'name')
    .innerJoinColumn("category");
    .select("name", "category.name")
    .keepFlat()
    .getFirst();

// returns { name: 'user name', category.name: 'category name' }

toQuery

const query = model.query(User);

console.log(query.toQuery()); // select * from "users"

getFirstOrNull

| Result | No item | One item | Many items | | ----------------- | ------- | -------- | ---------- | | getFirst | Error | Item | First item | | getSingle | Error | Item | Error | | getFirstOrNull | null | Item | First item | | getSingleOrNull | null | Item | Error |

const user = await model
    .query(User)
    .where("name", "name")
    .getFirstOrNull();

getFirst

| Result | No item | One item | Many items | | ----------------- | ------- | -------- | ---------- | | getFirst | Error | Item | First item | | getSingle | Error | Item | Error | | getFirstOrNull | null | Item | First item | | getSingleOrNull | null | Item | Error |

const user = await model
    .query(User)
    .where("name", "name")
    .getFirst();

getSingleOrNull

| Result | No item | One item | Many items | | ----------------- | ------- | -------- | ---------- | | getFirst | Error | Item | First item | | getSingle | Error | Item | Error | | getFirstOrNull | null | Item | First item | | getSingleOrNull | null | Item | Error |

const user = await model
    .query(User)
    .where("name", "name")
    .getSingleOrNull();

getSingle

| Result | No item | One item | Many items | | ----------------- | ------- | -------- | ---------- | | getFirst | Error | Item | First item | | getSingle | Error | Item | Error | | getFirstOrNull | null | Item | First item | | getSingleOrNull | null | Item | Error |

const user = await model
    .query(User)
    .where("name", "name")
    .getSingle();

getMany

const users = await model
    .query(User)
    .whereNotNull("name")
    .getMany();

getCount

model.query(User);

insertItem

model.query(User);

insertItems

model.query(User);

insertSelect

await model.query(User);
    .selectRaw('f', String, '\'fixedValue\'')
    .select("name")
    .distinct()
    .whereNotNull("name")
    .insertSelect(UserSetting, "id", "initialValue");

// insert into "userSettings" ("userSettings"."id","userSettings"."initialValue") select distinct ('fixedValue') as "f", "users"."name" as "name" from "users" where "users"."name" is not null

del

model.query(User);

delByPrimaryKey

model.query(User);

updateItem

model.query(User);

updateItemByPrimaryKey

model.query(User);

updateItemsByPrimaryKey

model.query(User);

execute

model.query(User);

whereRaw

model.query(User);

havingRaw

model.query(User);

transacting

const model = new ModelRepository(database);
const transaction = await model.beginTransaction();
try {
    await model.query(User).transacting(transaction).insertItem(user1);
    await model.query(User).transacting(transaction).insertItem(user2);
    await transaction.commit();
} catch (error) {
    await transaction.rollback();
    // handle error
}

truncate

model.query(User);

distinct

model.query(User);

clone

model.query(User);

groupByRaw

model.query(User);

Transactions

const model = new ModelRepository(database);
const transaction = await model.beginTransaction();
try {
    await model.query(User).transacting(transaction).insertItem(user1);
    await model.query(User).transacting(transaction).insertItem(user2);
    await transaction.commit();
} catch (error) {
    await transaction.rollback();
    // handle error
}

Validate tables

Use the validateTables function to make sure that the Table's and Column's in TypeScript exist in the database.

import * as Knex from "knex";
import { validateTables } from "@expresswebjs/ew-query-repository";

const knex = Knex({
    client: "pg",
    connection: "postgres://user:pass@localhost:5432/dbname",
});

await validateTables(knex);

Test

npm test

Update version

npm version major|minor|patch
update CHANGELOG.md
git commit --amend
npm publish --access=public --otp=CODE
git push

for beta

update version to x.x.x-beta.x
npm publish --access public --tag beta --otp=CODE