Status of this module
This module is deprecated. I'm happy to say that in the last 8 years, a lot of good libraries have been created that help with type safety for sql queries. There is no real need anymore for this library, so it's deprecated.
I recommend you to use one of these:
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 v2.2.0, TypeScript v4.7.4 and Node.js 14.x, 16.x, 18.x
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")
. Runnpx 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);
Helper
- getTableName
- getColumnName
- registerBeforeInsertTransform
- registerBeforeUpdateTransform
- validateTables
Querybuilder
General
Getting the results (Promises)
- findByPrimaryKey deprecated
- getFirstOrNull
- getFirstOrUndefined
- getFirst
- getSingleOrNull
- getSingleOrUndefined
- getSingle
- getMany
- getCount
- insertItem
- insertItemWithReturning
- insertItems
- insertSelect
- del
- delByPrimaryKey deprecated
- updateItem
- updateItemWithReturning
- updateItemByPrimaryKey deprecated
- updateItemsByPrimaryKey deprecated
- execute
Building the query
- select
- where
- andWhere
- orWhere
- whereNot
- whereColumn
- whereNull
- orWhereNull
- whereNotNull
- orWhereNotNull
- orderBy
- orderByRaw
- innerJoinColumn
- innerJoin
- innerJoinTableOnFunction
- leftOuterJoin
- 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
- distinctOn
- 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 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/guide/query-builder.html#select
typedKnex.query(User).select("id");
typedKnex.query(User).select("id", "name");
where
https://knexjs.org/guide/query-builder.html#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/guide/query-builder.html#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
deprecated
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");
);
getKnexQueryBuilder
Use getKnexQueryBuilder
to get to the underlying Knex.js query builder.
const query = typedKnex.query(User).select("name");
const knexQuery = query.getKnexQueryBuilder();
queryBuilder.where("somethingelse", "value");
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 |
getFirstOrUndefined |
undefined |
Item | First item |
getSingleOrUndefined |
undefined |
Item | Error |
const user = await typedKnex.query(User).where("name", "name").getFirstOrNull();
getFirstOrUndefined
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 |
getFirstOrUndefined |
undefined |
Item | First item |
getSingleOrUndefined |
undefined |
Item | Error |
const user = await typedKnex.query(User).where("name", "name").getFirstOrUndefined();
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 |
getFirstOrUndefined |
undefined |
Item | First item |
getSingleOrUndefined |
undefined |
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 |
getFirstOrUndefined |
undefined |
Item | First item |
getSingleOrUndefined |
undefined |
Item | Error |
const user = await typedKnex.query(User).where("name", "name").getSingleOrNull();
getSingleOrUndefined
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 |
getFirstOrUndefined |
undefined |
Item | First item |
getSingleOrUndefined |
undefined |
Item | Error |
const user = await typedKnex.query(User).where("name", "name").getSingleOrUndefined();
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 |
getFirstOrUndefined |
undefined |
Item | First item |
getSingleOrUndefined |
undefined |
Item | Error |
const user = await typedKnex.query(User).where("name", "name").getSingle();
getMany
const users = await typedKnex.query(User).whereNotNull("name").getMany();
getCount
Returns the row count of the query.
const count = await typedKnex.query(User).getCount();
The return type is Promise<number|string>
. This follows the Knex.js typing, see the count documentation.
The value of count will, by default, have type of string | number. This may be counter-intuitive but some connectors (eg. postgres) will automatically cast BigInt result to string when javascript's Number type is not large enough for the value.
The return type can be changed by overriding the ITypedQueryBuilder
interface.
Declare as number
:
declare module "@wwwouter/typed-knex" {
interface ITypedQueryBuilder<Model, SelectableModel, Row> {
getCount(): Promise<number>;
}
}
Declare as BigInt
:
declare module "@wwwouter/typed-knex" {
interface ITypedQueryBuilder<Model, SelectableModel, Row> {
getCount(): Promise<BigInt>;
}
}
When using Postgres, pg.types.setTypeParser
can be used to automatically convert the values.
To convert to integer
, use this code:
pg.types.setTypeParser(20, "text", parseInt);
To convert to bigint
, use this code:
pg.types.setTypeParser(20, "text", BigInt);
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
deprecated
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
deprecated
typedKnex.query(User);
updateItemsByPrimaryKey
deprecated
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
}
validateTables
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);
Add an array of table names to the validateTables
function to only validate those tables.
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, ["users"]);
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