# *** 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](https://knexjs.org/), 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:

```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")`

```ts
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.

```ts
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

```ts
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](#getTableName)
-   [getColumnName](#getColumnName)
-   [registerBeforeInsertTransform](#registerBeforeInsertTransform)
-   [registerBeforeUpdateTransform](#registerBeforeUpdateTransform)

## Querybuilder

### General

-   [query](#query)
-   [transacting](#transacting)
-   [toQuery](#toQuery)
-   [useKnexQueryBuilder](#useKnexQueryBuilder)
-   [keepFlat](#keepFlat)

### Getting the results (Promises)

-   [findByPrimaryKey](#findByPrimaryKey)
-   [getFirstOrNull](#getFirstOrNull)
-   [getFirst](#getFirst)
-   [getSingleOrNull](#getSingleOrNull)
-   [getSingle](#getSingle)
-   [getMany](#getMany)
-   [getCount](#getCount)
-   [insertItem](#insertItem)
-   [insertItems](#insertItems)
-   [insertSelect](#insertSelect)
-   [del](#del)
-   [delByPrimaryKey](#delByPrimaryKey)
-   [updateItem](#updateItem)
-   [updateItemByPrimaryKey](#updateItemByPrimaryKey)
-   [updateItemsByPrimaryKey](#updateItemsByPrimaryKey)
-   [execute](#execute)

### Building the query

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

### getTableName

```ts
const tableName = getTableName(User);

// tableName = 'users'
```

### getColumnName

```ts
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`

```ts
const query = model.query(User);
```

### select

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

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

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

### where

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

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

Or with operator

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

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

### andWhere

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

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

### orWhere

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

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

### whereNot

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

```ts
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.

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

### whereNull

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

### orWhereNull

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

### whereNotNull

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

### orWhereNotNull

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

### orderBy

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

### orderByRaw

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

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

### innerJoinColumn

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

### innerJoinTableOnFunction

```ts
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

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

### leftOuterJoinTableOnFunction

```ts
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

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

### selectQuery

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

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

### findByPrimaryKey

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

### whereIn

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

### whereNotIn

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

### orWhereIn

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

### orWhereNotIn

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

### whereBetween

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

### whereNotBetween

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

### orWhereBetween

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

### orWhereNotBetween

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

### whereExists

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

### orWhereExists

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

### whereNotExists

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

### orWhereNotExists

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

### whereParentheses

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

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

Outputs:

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

### groupBy

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

### having

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

### havingNull

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

### havingNotNull

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

### havingIn

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

### havingNotIn

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

### havingExists

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

### havingNotExists

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

### havingBetween

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

### havingNotBetween

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

### union

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

### unionAll

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

### min

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

### count

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

### countDistinct

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

### max

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

### sum

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

### sumDistinct

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

### avg

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

### avgDistinct

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

### clearSelect

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

### clearWhere

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

### clearOrder

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

### limit

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

### offset

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

### useKnexQueryBuilder

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

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

### keepFlat

Use `keepFlat` to prevent unflattening of the result.

```ts
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

```ts
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`    |

```ts
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`    |

```ts
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`    |

```ts
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`    |

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

### getMany

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

### getCount

```ts
model.query(User);
```

### insertItem

```ts
model.query(User);
```

### insertItems

```ts
model.query(User);
```

### insertSelect

```ts
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

```ts
model.query(User);
```

### delByPrimaryKey

```ts
model.query(User);
```

### updateItem

```ts
model.query(User);
```

### updateItemByPrimaryKey

```ts
model.query(User);
```

### updateItemsByPrimaryKey

```ts
model.query(User);
```

### execute

```ts
model.query(User);
```

### whereRaw

```ts
model.query(User);
```

### havingRaw

```ts
model.query(User);
```

### transacting

```ts
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

```ts
model.query(User);
```

### distinct

```ts
model.query(User);
```

### clone

```ts
model.query(User);
```

### groupByRaw

```ts
model.query(User);
```

## Transactions

```ts
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.

```ts
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