typed-knex

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 @wwwouter/typed-knex

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

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 typed-knex -u string-parameters to automatically switch over to the string syntax.

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 typed-knex -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 typed-knex -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 { TypedKnex } from "@wwwouter/typed-knex";

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

async function example() {
    const typedKnex = new TypedKnex(knex);

    const query = typedKnex.query(User).innerJoin("category", UserCategory, "id", "=", "categoryId").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 "@wwwouter/typed-knex";

@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 { TypedKnex } from "@wwwouter/typed-knex";

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

const typedKnex = new TypedKnex(knex);

Querybuilder

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 typedKnex.query(Type) to create a query for the table referenced by Type

const query = typedKnex.query(User);

getColumnAlias

Use getColumnAlias to get the underlying alias of a column, to use in a raw function.

const query = typedKnex.query(UserCategory);
query.selectRaw("hash", String, `hashFunction(${query.getColumnAlias("name")})`).select("id");

// select (hashFunction("userCategories"."name")) as "hash", "userCategories"."id" as "id" from "userCategories"

select

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

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

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

where

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

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

Or with operator

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

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

andWhere

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

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

orWhere

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

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

whereNot

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

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

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

Use getColumn when nesting

query.whereExists(User, (subQuery1) => {
        subQuery1.whereColumn('status', '=', 'status'); // Compares subQuery1 with its parent (query).  

        subQuery1.whereExists(User, (subQuery2) => {
            subQuery2.whereColumn(subQuery2.getColumn('status'), '=', query.getColumn('status')); // Compares subQuery2 with the first parent (query)

            subQuery2.whereExists(User, (subQuery3) => {
                subQuery3.whereColumn(subQuery3.getColumn('status'), '=', subQuery1.getColumn('status')); // Compares subQuery3 with the second parent (subQuery1)
            });
        });
    });

whereNull

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

orWhereNull

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

whereNotNull

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

orWhereNotNull

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

orderBy

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

orderByRaw

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

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

innerJoin

typedKnex.query(User).innerJoin("category", UserCategory, "id", "=", "categoryId");

// select * from "users" inner join "userCategories" as "category" on "category"."id" = "users"."categoryId"

innerJoinColumn

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

innerJoinTableOnFunction

typedKnex.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");
});

leftOuterJoin

typedKnex.query(User).leftOuterJoin("category", UserCategory, "id", "=", "categoryId");

// select * from "users" left outer join "userCategories" as "category" on "category"."id" = "users"."categoryId"

leftOuterJoinColumn

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

leftOuterJoinTableOnFunction

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

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

selectQuery

typedKnex
    .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 typedKnex.query(User).findByPrimaryKey("id", "d", "name");

whereIn

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

whereNotIn

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

orWhereIn

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

orWhereNotIn

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

whereBetween

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

whereNotBetween

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

orWhereBetween

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

orWhereNotBetween

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

whereExists

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

orWhereExists

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

whereNotExists

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

orWhereNotExists

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

whereParentheses

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

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

having

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

havingNull

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

havingNotNull

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

havingIn

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

havingNotIn

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

havingExists

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

havingNotExists

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

havingBetween

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

havingNotBetween

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

union

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

unionAll

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

min

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

count

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

countDistinct

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

max

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

sum

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

sumDistinct

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

avg

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

avgDistinct

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

clearSelect

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

clearWhere

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

clearOrder

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

limit

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

offset

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

useKnexQueryBuilder

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

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

keepFlat

Use keepFlat to prevent unflattening of the result.

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

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

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

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

toQuery

const query = typedKnex.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 typedKnex.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 typedKnex.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 typedKnex.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 typedKnex.query(User).where("name", "name").getSingle();

getMany

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

getCount

typedKnex.query(User);

insertItem

typedKnex.query(User);

insertItemWithReturning

query.insertItemWithReturning({ id: "newId" });

// insert into "users" ("id") values ('newId') returning *

query.insertItemWithReturning({ id: "newId" }, ["id"]);

// insert into "users" ("id") values ('newId') returning "users"."id"

insertItems

typedKnex.query(User);

insertSelect

await typedKnex.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

typedKnex.query(User);

delByPrimaryKey

typedKnex.query(User);

updateItem

typedKnex.query(User);

updateItemWithReturning

query.updateItemWithReturning({ id: "newId" });

// update "users" set "id" = 'newId' returning *

query.updateItemWithReturning({ id: "newId" }, ["id"]);

// update "users" set "id" = 'newId' returning "users"."id"

updateItemByPrimaryKey

typedKnex.query(User);

updateItemsByPrimaryKey

typedKnex.query(User);

execute

typedKnex.query(User);

whereRaw

typedKnex.query(User);

havingRaw

typedKnex.query(User);

transacting

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

truncate

typedKnex.query(User);

distinct

typedKnex.query(User);

distinctOn

typedKnex.query(UserCategory).select("id").distinctOn(["name"]);

// select distinct on ("userCategories"."name") "userCategories"."id" as "id" from "userCategories"

clone

typedKnex.query(User);

groupByRaw

typedKnex.query(User);

Transactions

const typedKnex = new TypedKnex(database);
const transaction = await typedKnex.beginTransaction();
try {
    await typedKnex.query(User).transacting(transaction).insertItem(user1);
    await typedKnex.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 "@wwwouter/typed-knex";

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

Name		Name	Last commit message	Last commit date
Latest commit History 293 Commits
.github/workflows		.github/workflows
.vscode		.vscode
redirect		redirect
src		src
test		test
upgradeTestProjects/v2-v3-stringParameters		upgradeTestProjects/v2-v3-stringParameters
.gitignore		.gitignore
.npmignore		.npmignore
.travis.yml		.travis.yml
CHANGELOG.md		CHANGELOG.md
CONTRIBUTING.md		CONTRIBUTING.md
ISSUE_TEMPLATE.md		ISSUE_TEMPLATE.md
LICENSE		LICENSE
README.md		README.md
package.json		package.json
tsconfig.json		tsconfig.json
tslint.json		tslint.json

License

bgilman-nyk/typed-knex

Folders and files

Latest commit

History

Repository files navigation