
## Sub-Queries

project a `raw` expression:

```ts
import { raw } from 'nukak/util';

this.querier.findMany(
  Item,
  {
    $group: ['companyId'],
  },
  {
    companyId: true,
    total: raw(({ escapedPrefix, dialect }) => `SUM(${escapedPrefix}${dialect.escapeId('salePrice')})`),
  }
);
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `companyId`, SUM(`salePrice`) `total` FROM `Item` GROUP BY `companyId`
```

&nbsp;

`$filter` by a `raw` expression:

```ts
import { raw } from 'nukak/util';

await this.querier.findMany(
  Item,
  {
    $filter: { $and: [{ companyId: 1 }, raw('SUM(salePrice) > 500')] },
    $group: ['creatorId'],
  },
  ['id']
);
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `id` FROM `Item` WHERE `companyId` = 1 AND SUM(salePrice) > 500 GROUP BY `creatorId`
```

&nbsp;

`$nexists` comparison operator:

```ts
import { raw } from 'nukak/util';

await this.querier.findMany(
  Item,
  {
    $filter: {
      $nexists: raw(({ escapedPrefix, dialect }) =>
        dialect.find(
          User,
          {
            $project: ['id'],
            $filter: { companyId: raw(escapedPrefix + dialect.escapeId(`companyId`)) },
          },
          { autoPrefix: true }
        )
      ),
    },
  },
  ['id']
);
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `id`
FROM `Item`
WHERE NOT EXISTS
    (SELECT `User`.`id` FROM `User` WHERE `User`.`companyId` = `Item`.`companyId`)
```


This tutorial explain how to use sub-queries with the nukak orm.

Querying Sub Queries


[nukak](https://nukak.org) is the smartest [ORM](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping) for [TypeScript](http://www.typescriptlang.org), it is designed to be fast, safe, and easy to integrate into any application. It draws inspiration from [TypeORM](https://typeorm.io) and [Mongo driver](https://www.mongodb.com/docs/drivers/node/current/fundamentals/crud/query-document/).

[nukak](https://nukak.org) can run in Node.js, Browser, Cordova, PhoneGap, Ionic, React Native, NativeScript, Expo, and Electron platforms.

[nukak](https://nukak.org) has a consistent API for distinct databases, including PostgreSQL, MySQL, SQLite, and MongoDB.

&nbsp;

![Sample code](/code-s2.jpg?raw=true 'Same code')

&nbsp;

## Features

- Type-safe and Context-aware queries: squeeze the strength of `TypeScript` so it auto-completes, validates, and infers the appropriate operators on any level of the queries, [including the relations and their fields](https://www.nukak.org/docs/querying-relations).
- Serializable queries: its [syntax](https://nukak.org/docs/querying-logical-operators) is `100%` valid `JSON` allowing the queries to be transported across platforms with ease.
- Unified API across Databases: same query is transparently transformed according to the configured database.
- Combines the best elements of `OOP` (Object Oriented Programming) and `FP` (Functional Programming).
- [Declarative](https://nukak.org/docs/transactions-declarative) and [imperative](https://nukak.org/docs/transactions-imperative) `transactions` for flexibility, and `connection pooling` for scalability.
- Transparent support for [inheritance between entities](https://nukak.org/docs/entities-inheritance) for reusability and consistency.
- Modern [Pure ESM](https://gist.github.com/sindresorhus/a39789f98801d908bbc7ff3ecc99d99c) packages. `ESM` is natively supported by Node.js 12 and later.
- High performance: the [generated queries](https://www.nukak.org/docs/querying-logical-operators) are fast, safe, and human-readable.
- Supports the [Data Mapper](https://en.wikipedia.org/wiki/Data_mapper_pattern) pattern for maintainability.
- [soft-delete](https://nukak.org/docs/entities-soft-delete), [virtual fields](https://nukak.org/docs/entities-virtual-fields), [repositories](https://nukak.org/docs/querying-repository).

&nbsp;

## Install

1. Install the core package:

   ```sh
   npm install nukak --save
   ```

2. Install one of the specific adapters for your database:

| Database     | Driver           | Nukak Adapter    |
| ------------ | ---------------- | ---------------- |
| `PostgreSQL` | `pg`             | `nukak-postgres` |
| `SQLite`     | `sqlite sqlite3` | `nukak-sqlite`   |
| `MariaDB`    | `mariadb`        | `nukak-maria`    |
| `MongoDB`    | `mongodb`        | `nukak-mongo`    |
| `MySQL`      | `mysql2`         | `nukak-mysql`    |

For example, for `Postgres`:

```sh
npm install pg nukak-postgres --save
```

3. Additionally, your `tsconfig.json` may need the following flags:

   ```json
   "target": "es2020",
   "experimentalDecorators": true,
   "emitDecoratorMetadata": true
   ```

&nbsp;

---

&nbsp;

## Define the entities

Take any dump class (aka DTO) and annotate it with the decorators from `nukak/entity`.

```ts
import { v4 as uuidv4 } from 'uuid';
import { Entity, Id, Field } from 'nukak/entity';

/**
 * any class can be annotated with this decorator to make it works as
 * an entity.
 */
@Entity()
export class User {
  /**
   * an entity should specify an ID Field, its name and type are automatically detected.
   * the `onInsert` property can be used to specify a custom mechanism for
   * auto-generating the primary-key's value when inserting.
   */
  @Id({ onInsert: uuidv4 })
  id?: string;

  /**
   * the properties of the class can be annotated with this decorator so they
   * are interpreted as a column, its name and type are automatically detected.
   */
  @Field()
  name?: string;

  @Field()
  email?: string;

  @Field()
  password?: string;
}
```

&nbsp;

## Set up a default querier-pool

A default querier-pool can be set in any of the bootstrap files of your app (e.g. in the `server.ts`).

```ts
import { setQuerierPool } from 'nukak';
import { PgQuerierPool } from 'nukak-postgres';

export const querierPool = new PgQuerierPool(
  {
    host: 'localhost',
    user: 'theUser',
    password: 'thePassword',
    database: 'theDatabase',
  },
  // optionally, a logger can be passed to log the generated SQL queries
  { logger: console.log }
);

// the default querier pool that `nukak` will use
setQuerierPool(querierPool);
```

&nbsp;

## Manipulate the data

```ts
import { getQuerier } from 'nukak';
import { User } from './shared/models/index.js';

async function findFirstUsers(limit = 100) {
  const querier = await getQuerier();
  const users = await querier.findMany(
    User,
    {
      $sort: { createdAt: 1 },
      $limit: limit,
    },
    ['id', 'name', 'email']
  );
  await querier.release();
  return users;
}

async function createUser(body: User) {
  const querier = await getQuerier();
  const id = await querier.insertOne(User, body);
  await querier.release();
  return id;
}
```


This tutorial explain the features of the nukak orm and how to use get started with it.

Getting Started


## Basic entities

The following are the steps to define a basic `entity`:

1. Take any class and annotate it with the `@Entity` decorator.
2. Annotate one of its properties with the `@Id` decorator.
3. Annotate the rest of fields with the `@Field` decorator.

```ts
import { v4 as uuidv4 } from 'uuid';
import { Entity, Id, Field } from 'nukak/entity';

@Entity()
export class User {
  @Id({ onInsert: uuidv4 })
  id?: string;

  @Field()
  name?: string;

  @Field()
  email?: string;

  @Field()
  password?: string;
}
```


This tutorial explain how to define basic entities with the nukak orm.

Basic


## Relations between entities

Take any dump class (aka `DTO`) and annotate it with the decorators from `nukak/entity`.

```ts
import { v4 as uuidv4 } from 'uuid';
import { Field, ManyToOne, Id, OneToMany, Entity, OneToOne, ManyToMany } from 'nukak/entity';

@Entity()
export class Profile {
  /**
   * primary-key.
   * the `onInsert` callback can be used to specify a custom mechanism for auto-generating
   * the default value of a field when inserting a new record.
   */
  @Id({ onInsert: uuidv4 })
  id?: string;

  @Field()
  picture?: string;

  /**
   * foreign-keys are really simple to specify.
   */
  @Field({ reference: () => User })
  creatorId?: string;
}

@Entity()
export class User {
  @Id({ onInsert: uuidv4 })
  id?: string;

  @Field()
  name?: string;

  @Field()
  email?: string;

  @Field()
  password?: string;

  /**
   * `mappedBy` can be a callback or a string (callback is useful for auto-refactoring).
   */
  @OneToOne({ entity: () => Profile, mappedBy: (profile) => profile.creatorId, cascade: true })
  profile?: Profile;
}

@Entity()
export class MeasureUnitCategory {
  @Id({ onInsert: uuidv4 })
  id?: string;

  @Field()
  name?: string;

  @OneToMany({ entity: () => MeasureUnit, mappedBy: (measureUnit) => measureUnit.category })
  measureUnits?: MeasureUnit[];
}

@Entity()
export class MeasureUnit {
  @Id({ onInsert: uuidv4 })
  id?: string;

  @Field()
  name?: string;

  @Field({ reference: () => MeasureUnitCategory })
  categoryId?: string;

  @ManyToOne({ cascade: 'persist' })
  category?: MeasureUnitCategory;
}
```


This tutorial explain how to use relations in the entities with the nukak orm.

Relations


## Soft-Delete for entities

The `softDelete` property of the `@Entity` decorator can be used as below.

Note that it is also required to use the `onDelete` property in one of the fields to instruct `nukak` which field to use as the deletion mark when deleting the records.

```ts
/**
 * `softDelete: true` will make the entity "soft deletable".
 */
@Entity({ softDelete: true })
export class MeasureUnitCategory {
  @Id()
  id?: number;

  @Field()
  name?: string;
  
  /**
   * `onDelete` callback allows to specify which field will be used
   * when deleting/querying this entity.
   */
  @Field({ onDelete: Date.now })
  deletedAt?: number;
}
```

&nbsp;

If we delete a record for that entity, it will be soft-deleted, and won't be load by the `find` operations:

```ts
await querier.deleteOneById(MeasureUnitCategory, 1);
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
UPDATE `MeasureUnitCategory` SET `deletedAt` = 1627344820381 WHERE `id` 1
```

&nbsp;

And if we perform any `find` operation for that entity, the soft-deleted records won't be loaded (by default):

```ts
await querier.findMany(MeasureUnitCategory, { $limit: 100 }, ['id', 'name']);
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `id`, `name` FROM `MeasureUnitCategory` WHERE `deletedAt` IS NULL LIMIT 100
```

This tutorial explain how to use soft-delete in the entities with the nukak orm.

Soft Delete


## Virtual-Fields for entities

The `virtual` property of the `@Field` decorator can be used as below:

```ts
import { Entity, Id, Field, ManyToMany } from 'nukak/entity';
import { raw } from 'nukak/util';

@Entity()
export class Item {
  @Id()
  id?: number;

  @Field()
  name?: string;

  @ManyToMany({ entity: () => Tag, through: () => ItemTag, cascade: true })
  tags?: Tag[];

  @Field({
    /**
     * `virtual` property allows defining the value for a non-persistent field,
     * such value might be a scalar or a (`raw`) function. Virtual-fields can be
     * used in `$project`, `$filter` and `$having` as a common field whose value
     * is replaced at runtime.
     */
    virtual: raw(({ escapedPrefix, dialect }) => {
      const query = dialect.count(
        ItemTag,
        {
          $filter: {
            itemId: raw(`${escapedPrefix}${dialect.escapeId('id')}`),
          },
        },
        { autoPrefix: true }
      );
      return `(${query})`;
    }),
  })
  tagsCount?: number;
}

@Entity()
export class Tag {
  @Field()
  name?: string;

  @ManyToMany({ entity: () => Item, mappedBy: (item) => item.tags })
  items?: Item[];

  @Field({
    virtual: raw(({ escapedPrefix, dialect }) => {
      /**
       * `virtual` property allows defining the value for a non-persistent field,
       * such value might be a scalar or a (`raw`) function. Virtual-fields can be
       * used in `$project`, `$filter` and `$having` as a common field whose value
       * is replaced at runtime.
       */
      const query = dialect.count(
        ItemTag,
        {
          $filter: {
            tagId: raw(`${escapedPrefix}${dialect.escapeId('id')}`),
          },
        },
        { autoPrefix: true }
      );
      return `(${query})`;
    }),
  })
  itemsCount?: number;
}

@Entity()
export class ItemTag {
  @Id()
  id?: number;

  @Field({ reference: () => Item })
  itemId?: number;

  @Field({ reference: () => Tag })
  tagId?: number;
}
```

&nbsp;

If we project the `tagsCount` virtual-column:

```ts
await querier.findMany(Item, {}, ['id', 'tagsCount']);
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT
  `id`,
  (SELECT COUNT(*) FROM `ItemTag` WHERE `ItemTag`.`itemId` = `id`) `tagsCount`
FROM `Item`
```

&nbsp;

If we `$filter` by the `tagsCount` virtual-column:

```ts
await querier.findMany(
  Item,
  {
    $filter: {
      tagsCount: { $gte: 10 },
    },
  },
  ['id']
);
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `id` FROM `Item`
WHERE
  (SELECT COUNT(*) `count` FROM `ItemTag` WHERE `ItemTag`.`itemId` = `id`) >= 10
```


This tutorial explain how to use virtual-fields in the entities with the nukak orm.

Virtual Fields


## Inheritance between entities

The inline comments in the code below have concise descriptions of advanced use cases.

```ts
/**
 * an `abstract` class can (optionally) be used as the base "template" for the entities
 * (so common fields' declaration is easily reused).
 */
export abstract class BaseEntity {
  /**
   * auto-generated primary-key (when the `onInsert` property is omitted).
   */
  @Id()
  id?: number;

  /**
   * foreign-keys are really simple to specify with the `reference` property.
   */
  @Field({ reference: () => Company })
  companyId?: number;

  /**
   * The `Relation` wrapper type can be used in ESM projects for the relations to
   * avoid circular dependency issues.
   */
  @ManyToOne({ entity: () => Company })
  company?: Relation<Company>;

  @Field({ reference: () => User })
  creatorId?: number;

  @ManyToOne({ entity: () => User })
  creator?: Relation<User>;

  /**
   * 'onInsert' property can be used to specify a custom mechanism for
   * obtaining the value of a field when inserting:
   */
  @Field({ onInsert: Date.now })
  createdAt?: number;

  /**
   * 'onUpdate' property can be used to specify a custom mechanism for
   * obtaining the value of a field when updating:
   */
  @Field({ onUpdate: Date.now })
  updatedAt?: number;
}

/**
 * `Company` will inherit all the fields (including the `Id`) declared in `BaseEntity`.
 */
@Entity()
export class Company extends BaseEntity {
  @Field()
  name?: string;

  @Field()
  description?: string;
}

/**
 * and entity can specify the table name.
 */
@Entity({ name: 'user_profile' })
export class Profile extends BaseEntity {
  /**
   * an entity can specify its own ID Field and still inherit the others
   * columns/relations from its parent entity.
   */
  @Id()
  pk?: number;

  @Field({ name: 'image' })
  picture?: string;

  @OneToOne({ entity: () => User })
  declare creator?: Relation<User>;
}

@Entity()
export class User extends BaseEntity {
  @Field()
  name?: string;

  @Field()
  email?: string;

  @Field()
  password?: string;

  /**
   * `mappedBy` property can be a callback or a string (callback is useful for auto-refactoring).
   */
  @OneToOne({ entity: () => Profile, mappedBy: (profile) => profile.creator, cascade: true })
  profile?: Profile;

  @OneToMany({ entity: () => User, mappedBy: 'creator' })
  users?: User[];
}

@Entity()
export class LedgerAccount extends BaseEntity {
  @Field()
  name?: string;

  @Field()
  description?: string;

  @Field({ reference: () => LedgerAccount })
  parentLedgerId?: number;

  @ManyToOne()
  parentLedger?: LedgerAccount;
}

@Entity()
export class TaxCategory extends BaseEntity {
  /**
   * `idKey` symbol can be used to specify the name of the identifier property,
   * so the type of the identifier can always be type-safe
   * (the identifiers named as `id` or `_id` are auto-inferred).
   */
  [idKey]?: 'pk';

  /**
   * an entity can override the ID Field and still inherit the others
   * columns/relations from its parent entity.
   * 'onInsert' property can be used to specify a custom mechanism for
   * auto-generating the primary-key's value when inserting.
   */
  @Id({ onInsert: uuidv4 })
  pk?: string;

  @Field()
  name?: string;

  @Field()
  description?: string;
}

@Entity()
export class Tax extends BaseEntity {
  @Field()
  name?: string;

  @Field()
  percentage?: number;

  @Field({ reference: () => TaxCategory })
  categoryId?: string;

  @ManyToOne()
  category?: TaxCategory;

  @Field()
  description?: string;
}

/**
 * `softDelete` will make the entity "soft deletable".
 */
@Entity({ softDelete: true })
export class MeasureUnitCategory extends BaseEntity {
  @Field()
  name?: string;

  @OneToMany({ entity: () => MeasureUnit, mappedBy: (measureUnit) => measureUnit.categoryId })
  measureUnits?: MeasureUnit[];

  /**
   * `onDelete` callback allows to specify which field will be used when deleting/querying this entity.
   */
  @Field({ onDelete: Date.now })
  deletedAt?: number;
}

@Entity({ softDelete: true })
export class MeasureUnit extends BaseEntity {
  @Field()
  name?: string;

  @Field({ reference: () => MeasureUnitCategory })
  categoryId?: number;

  @ManyToOne({ cascade: 'persist' })
  category?: MeasureUnitCategory;

  @Field({ onDelete: Date.now })
  deletedAt?: number;
}

@Entity()
export class Storehouse extends BaseEntity {
  @Field()
  name?: string;

  @Field()
  address?: string;

  @Field()
  description?: string;
}

@Entity()
export class Item extends BaseEntity {
  @Field()
  name?: string;

  @Field()
  description?: string;

  @Field()
  code?: string;

  @Field({ reference: () => LedgerAccount })
  buyLedgerAccountId?: number;

  @ManyToOne()
  buyLedgerAccount?: LedgerAccount;

  @Field({ reference: () => LedgerAccount })
  saleLedgerAccountId?: number;

  @ManyToOne()
  saleLedgerAccount?: LedgerAccount;

  @Field({ reference: () => Tax })
  taxId?: number;

  @ManyToOne()
  tax?: Tax;

  @Field({ reference: () => MeasureUnit })
  measureUnitId?: number;

  @ManyToOne()
  measureUnit?: MeasureUnit;

  @Field()
  salePrice?: number;

  @Field()
  inventoryable?: boolean;

  @ManyToMany({ entity: () => Tag, through: () => ItemTag, cascade: true })
  tags?: Tag[];

  @Field({
    /**
     * `virtual` property allows defining the value for a non-persistent field,
     * such value might be a scalar or a (`raw`) function. Virtual-fields can be
     * used in `$project`, `$filter` and `$having` as a common field whose value
     * is replaced at runtime.
     */
    virtual: raw(({ escapedPrefix, dialect }) => {
      const query = dialect.count(
        ItemTag,
        {
          $filter: {
            itemId: raw(`${escapedPrefix}${dialect.escapeId('id')}`),
          },
        },
        { autoPrefix: true }
      );
      return `(${query})`;
    }),
  })
  tagsCount?: number;
}

@Entity()
export class Tag extends BaseEntity {
  @Field()
  name?: string;

  @ManyToMany({ entity: () => Item, mappedBy: (item) => item.tags })
  items?: Item[];

  @Field({
    virtual: raw(({ escapedPrefix, dialect }) => {
      /**
       * `virtual` property allows defining the value for a non-persistent field,
       * such value might be a scalar or a (`raw`) function. Virtual-fields can be
       * used in `$project`, `$filter` and `$having` as a common field whose value
       * is replaced at runtime.
       */
      const query = dialect.count(
        ItemTag,
        {
          $filter: {
            tagId: raw(`${escapedPrefix}${dialect.escapeId('id')}`),
          },
        },
        { autoPrefix: true }
      );
      return `(${query})`;
    }),
  })
  itemsCount?: number;
}

@Entity()
export class ItemTag {
  @Id()
  id?: number;

  @Field({ reference: () => Item })
  itemId?: number;

  @Field({ reference: () => Tag })
  tagId?: number;
}

@Entity()
export class InventoryAdjustment extends BaseEntity {
  @OneToMany({
    entity: () => ItemAdjustment,
    mappedBy: (rel) => rel.inventoryAdjustment,
    cascade: true,
  })
  itemAdjustments?: ItemAdjustment[];

  @Field()
  date?: Date;

  @Field()
  description?: string;
}

@Entity()
export class ItemAdjustment extends BaseEntity {
  @Field({ reference: () => Item })
  itemId?: number;

  @ManyToOne()
  item?: Item;

  @Field()
  number?: number;

  @Field()
  buyPrice?: number;

  @Field({ reference: () => Storehouse })
  storehouseId?: number;

  @ManyToOne()
  storehouse?: Storehouse;

  @Field({ reference: () => InventoryAdjustment })
  inventoryAdjustmentId?: number;

  @ManyToOne()
  inventoryAdjustment?: InventoryAdjustment;
}
```

This tutorial explain how to use inheritance between entities with the nukak orm.

Inheritance

Entities


## Logical Operators

| Name   | Description                                                                                    |
| ------ | ---------------------------------------------------------------------------------------------- |
| `$and` | joins query clauses with a logical `AND`, returns records that match all the clauses.          |
| `$or`  | joins query clauses with a logical `OR`, returns records that match any of the clauses.        |
| `$not` | joins query clauses with a logical `AND`, returns records that do not match all the clauses.   |
| `$nor` | joins query clauses with a logical `OR`, returns records that do not match any of the clauses. |

&nbsp;

Example usage for the `$and` logical operator (default operator if unspecified):

```ts
await querier.findMany(
  User,
  {
    $filter: { name: 'maku', creatorId: 1 },
  },
  ['id']
);
```

or the same query with an explicit `$and`

```ts
await querier.findMany(
  User,
  {
    $filter: { $and: [{ name: 'maku' }, { creatorId: 1 }] },
  },
  ['id']
);
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `id` FROM `User` WHERE `name` = 'maku' AND `creatorId` = 1
```

&nbsp;

Example usage for the `$or` logical operator:

```ts
await querier.findMany(
  User,
  {
    $filter: { $or: [{ name: 'maku' }, { creatorId: 1 }] },
  },
  ['id']
);
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `id` FROM `User` WHERE `name` = 'maku' OR `creatorId` = 1
```

&nbsp;

Example usage for the `$not` logical operator

```ts
await querier.findMany(
  User,
  {
    $filter: { $not: [{ name: 'maku' }, { creatorId: 1 }] },
  },
  ['id']
);
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `id` FROM `User` WHERE NOT (`name` = 'maku' AND `creatorId` = 1)
```

&nbsp;

Example usage for the `$nor` logical operator

```ts
await querier.findMany(
  User,
  {
    $filter: { $nor: [{ name: 'maku' }, { creatorId: 1 }] },
  },
  ['id']
);
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `id` FROM `User` WHERE NOT (`name` = 'maku' OR `creatorId` = 1)
```


This tutorial explain how to use logical operators with the nukak orm.

Logical Operators


## Comparison Operators

| Name           | Description                                                                        |
| -------------- | ---------------------------------------------------------------------------------- |
| `$eq`          | whether a value is equal to the given value.                                       |
| `$ne`          | whether a value is not equal to the given value.                                   |
| `$not`         | negates the given comparison.                                                      |
| `$lt`          | whether a value is less than the given value.                                      |
| `$lte`         | whether a value is less than or equal to the given value.                          |
| `$gt`          | whether a value is greater than the given value.                                   |
| `$gte`         | whether a value is greater than or equal to the given value.                       |
| `$startsWith`  | whether a string begins with the given string (case sensitive).                    |
| `$istartsWith` | whether a string begins with the given string (case insensitive).                  |
| `$endsWith`    | whether a string ends with the given string (case sensitive).                      |
| `$iendsWith`   | whether a string ends with the given string (case insensitive).                    |
| `$includes`    | whether a string is contained within the given string (case sensitive).            |
| `$iincludes`   | whether a string is contained within the given string (case insensitive).          |
| `$like`        | whether a string fulfills the given pattern (case sensitive).                      |
| `$ilike`       | whether a string fulfills the given pattern (case insensitive).                    |
| `$regex`       | whether a string matches the given regular expression.                             |
| `$in`          | whether a value matches any of the given values.                                   |
| `$nin`         | whether a value does not match any of the given values.                            |
| `$text`        | whether the specified fields match against a full-text search of the given string. |
| `$exists`      | whether the record exists in the given sub-query.                                  |
| `$nexists`     | whether the record does not exists in the given sub-query.                         |

&nbsp;

Example usage for the `$istartsWith` and `$ne` comparison operators:

```ts
await this.querier.findMany(
  User,
  {
    $filter: { name: { $istartsWith: 'Some', $ne: 'Something' } },
    $sort: { name: 1, id: -1 },
    $limit: 50,
  },
  ['id']
);
```

That &#9650; code will generate this &#9660; `SQL` for `Postgres`:

```sql
SELECT  "id" FROM "User"
WHERE ("name" ILIKE 'Some%' AND "name" <> 'Something')
ORDER BY "name", "id" DESC
LIMIT 50
```

And that same code above will generate this other &#9660; `SQL` for `MySQL`, `MariaDB` and `SQLite`:

```sql
SELECT  `id` FROM `User`
WHERE (LOWER(`name`) LIKE 'some%' AND `name` <> 'Something')
ORDER BY `name`, `id` DESC
LIMIT 50
```


This tutorial explain how to use comparison operators with the nukak orm.

Comparison Operators


## Querying relations

project a mandatory relation with `$required: true`:

```ts
@Transactional()
async function findLatestUserWithProfile(@InjectQuerier() querier?: Querier): Promise<User> {
  return querier.findOne(User,
    {
      $sort: { createdAt: -1 },
    },
    {
      id: true,
      name: true,
      profile: {
        $project: ['id', 'picture'],
        $required: true
      }
    }
  );
}
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `User`.`id`, `User`.`name`, `profile.id`, `profile.picture`
FROM `User`
INNER JOIN `Profile` `profile` ON `profile`.`creatorId` = `User`.`id`
ORDER BY `User.createdAt` DESC
```

&nbsp;

Simple query using `getQuerier()`:

```ts
import { getQuerier } from 'nukak';

async function findLatestUserWithProfile(): Promise<User> {
  const querier = await getQuerier();
  const user = querier.findOne(
    User,
    {
      $sort: { createdAt: -1 },
    },
    {
      id: true,
      name: true,
      profile: ['id', 'picture'],
    }
  );
  await querier.release();
  return user;
}
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `User`.`id`, `User`.`name`, `profile.id`, `profile.picture`
FROM `User`
LEFT JOIN `Profile` `profile` ON `profile`.`creatorId` = `User`.`id`
ORDER BY `User.createdAt` DESC
```

&nbsp;

More complex queries can be used, like the following:

```ts
import { Querier } from 'nukak';
import { Transactional, InjectQuerier } from 'nukak/querier';
import { Item } from './shared/models/index.js';

export class ItemService {
  @Transactional()
  async function findItems(@InjectQuerier() querier?: Querier): Promise<Item[]> {
    return querier.findMany(Item,
      {
        $filter: { salePrice: { $gte: 1000 }, name: { $istartsWith: 'A' } },
        $sort: { tax: { name: 1 }, measureUnit: { name: 1 }, createdAt: -1 },
        $limit: 100,
      },
      {
        id: true,
        name: true,
        measureUnit: {
          $project: ['id', 'name'],
          $filter: { name: { $ne: 'unidad' } },
          $required: true
        },
        tax: ['id', 'name'],
      }
    );
  }
}

export const itemService = new ItemService();
```

That &#9650; code will generate this &#9660; `SQL`:

```sql
SELECT `Item`.`id`, `Item`.`name`,
       `measureUnit`.`id` `measureUnit.id`, `measureUnit`.`name` `measureUnit.name`,
       `tax`.`id` `tax.id`, `tax`.`name` `tax.name`
FROM `Item`
INNER JOIN `MeasureUnit` `measureUnit` ON `measureUnit`.`id` = `Item`.`measureUnitId`
  AND `measureUnit`.`name` <> 'unidad' AND `measureUnit`.`deletedAt` IS NULL
LEFT JOIN `Tax` `tax` ON `tax`.`id` = `Item`.`taxId`
WHERE `Item`.`salePrice` >= 1000 AND LOWER(`Item`.`name`) LIKE 'a%'
ORDER BY `tax`.`name`, `measureUnit`.`name`, `Item`.`createdAt` DESC LIMIT 100
```


This tutorial explain how to use relations in the queries with the nukak orm.

Sub Queries


## Querier

A `querier` is the `nukak`'s abstraction over database drivers to dynamically generate the queries for _any_ given entity. It allows interaction with the different databases in a consistent way.

With a `querier` you can:

- Manipulate the data related to _any_ `entity`.
- Use [declarative](/docs/transactions-declarative) and [imperative](/docs/transactions-imperative) transactions.
- Obtain [repositories](/docs/querying-repository) for _specific_ `entities`.

```ts
import { getQuerier } from 'nukak';
import { User } from './shared/models/index.js';

const querier = await getQuerier();

const users = await querier.findMany(
  User,
  {
    $filter: { $or: [{ name: 'maku' }, { creatorId: 1 }] },
  },
  ['id']
);

await querier.release();
```

&nbsp;

### Querier API

```ts
/**
 * A `querier` allows to interact with the datasource to perform persistence operations on any entity.
 */
export interface Querier {
  /**
   * counts the number of records matching the given search parameters.
   * @param entity the target entity
   * @param qm the search options
   */
  count<E>(entity: Type<E>, qm?: QuerySearch<E>): Promise<number>;

  /**
   * obtains the record with the given primary key.
   * @param entity the target entity
   * @param id the primary key value
   * @param qm the criteria options
   */
  findOneById<E>(entity: Type<E>, id: IdValue<E>, qm?: QueryUnique<E>): Promise<E>;

  /**
   * obtains the first record matching the given search parameters.
   * @param entity the target entity
   * @param qm the criteria options
   */
  findOne<E>(entity: Type<E>, qm: QueryOne<E>): Promise<E>;

  /**
   * obtains the records matching the given search parameters.
   * @param entity the target entity
   * @param qm the criteria options
   */
  findMany<E>(entity: Type<E>, qm: Query<E>): Promise<E[]>;

  /**
   * obtains the records matching the given search parameters,
   * also counts the number of matches ignoring pagination.
   * @param entity the target entity
   * @param qm the criteria options
   */
  findManyAndCount<E>(entity: Type<E>, qm: Query<E>): Promise<[E[], number]>;

  /**
   * inserts a record.
   * @param entity the entity to persist on
   * @param payload the data to be persisted
   */
  insertOne<E>(entity: Type<E>, payload: E): Promise<IdValue<E>>;

  /**
   * inserts many records.
   * @param entity the entity to persist on
   * @param payload the data to be persisted
   */
  insertMany?<E>(entity: Type<E>, payload: E[]): Promise<IdValue<E>[]>;

  /**
   * updates a record partially.
   * @param entity the entity to persist on
   * @param id the primary key of the record to be updated
   * @param payload the data to be persisted
   */
  updateOneById<E>(entity: Type<E>, id: IdValue<E>, payload: E): Promise<number>;

  /**
   * updates many records partially.
   * @param entity the entity to persist on
   * @param qm the criteria to look for the records
   * @param payload the data to be persisted
   */
  updateMany?<E>(entity: Type<E>, qm: QueryCriteria<E>, payload: E): Promise<number>;

  /**
   * insert or update a record.
   * @param entity the entity to persist on
   * @param payload the data to be persisted
   */
  saveOne<E>(entity: Type<E>, payload: E): Promise<IdValue<E>>;

  /**
   * insert or update records.
   * @param entity the entity to persist on
   * @param payload the data to be persisted
   */
  saveMany?<E>(entity: Type<E>, payload: E[]): Promise<IdValue<E>[]>;

  /**
   * delete or SoftDelete a record.
   * @param entity the entity to persist on
   * @param id the primary key of the record
   */
  deleteOneById<E>(entity: Type<E>, id: IdValue<E>, opts?: QueryOptions): Promise<number>;

  /**
   * delete or SoftDelete records.
   * @param entity the entity to persist on
   * @param qm the criteria to look for the records
   */
  deleteMany<E>(entity: Type<E>, qm: QueryCriteria<E>, opts?: QueryOptions): Promise<number>;

  /**
   * get a repository for the given entity.
   * @param entity the entity to get the repository for
   */
  getRepository<E>(entity: Type<E>): Repository<E>;

  /**
   * whether this querier is in a transaction or not.
   */
  readonly hasOpenTransaction: boolean;

  /**
   * run the given callback inside a transaction in this querier.
   */
  transaction<T>(callback: (querier?: ThisType<Querier>) => Promise<T>): Promise<T>;

  /**
   * starts a new transaction in this querier.
   */
  beginTransaction(): Promise<void>;

  /**
   * commits the currently active transaction in this querier.
   */
  commitTransaction(): Promise<void>;

  /**
   * aborts the currently active transaction in this querier.
   */
  rollbackTransaction(): Promise<void>;

  /**
   * release the querier to the pool.
   */
  release(): Promise<void>;
}
```


This tutorial explain how to use a querier with the nukak orm.

Querier


## Repository

A `repository` allows running queries for a _specific_ `entity`, they can be used instead of [queriers](/docs/querying-querier) for this purpose. That way, it is unnecessary to provide the `entity` parameter, and only the `query` parameter is required. Every `repository` is also associated with a single [querier](/docs/querying-querier) instance (from where it was obtained).

With a `repository` you can:

- Manipulate the data related to the linked `entity`.
- Access the [querier](/docs/querying-querier) instance from where the `repository` was obtained.

```ts
import { getQuerier } from 'nukak';
import { User } from './shared/models/index.js';

const querier = await getQuerier();
const userRepository = querier.getRepository(User);

const users = await userRepository.findMany(
  {
    $filter: { $or: [{ name: 'maku' }, { creatorId: 1 }] },
  },
  ['id']
);

await querier.release();
```

&nbsp;

### Repository API

```ts
/**
 * A `repository` allows to interact with the datasource to perform persistence operations on a specific entity.
 */
export interface Repository<E> {
  /**
   * the `entity` class to which this `repository` is linked to.
   */
  readonly entity: Type<E>;

  /**
   * the `querier` instance to which this `repository` is linked to.
   */
  readonly querier: UniversalQuerier;

  /**
   * counts the number of records matching the given search parameters.
   * @param qm the search options
   */
  count(qm: QuerySearch<E>): Promise<number>;

  /**
   * obtains the record with the given primary key.
   * @param id the primary key value
   * @param qm the criteria options
   */
  findOneById(id: IdValue<E>, qm?: QueryUnique<E>): Promise<E>;

  /**
   * obtains the first record matching the given search parameters.
   * @param qm the criteria options
   */
  findOne(qm: QueryOne<E>): Promise<E>;

  /**
   * obtains the records matching the given search parameters.
   * @param qm the criteria options
   */
  findMany(qm: Query<E>): Promise<E[]>;

  /**
   * obtains the records matching the given search parameters,
   * also counts the number of matches ignoring pagination.
   * @param qm the criteria options
   */
  findManyAndCount(qm: Query<E>): Promise<[E[], number]>;

  /**
   * inserts a record.
   * @param payload the data to be persisted
   */
  insertOne(payload: E): Promise<IdValue<E>>;

  /**
   * inserts many records.
   * @param entity the entity to persist on
   * @param payload the data to be persisted
   */
  insertMany?(payload: E[]): Promise<IdValue<E>[]>;

  /**
   * updates a record partially.
   * @param id the primary key of the record to be updated
   * @param payload the data to be persisted
   */
  updateOneById(id: IdValue<E>, payload: E): Promise<number>;

  /**
   * updates many records partially.
   * @param qm the criteria to look for the records
   * @param payload the data to be persisted
   */
  updateMany?(qm: QueryCriteria<E>, payload: E): Promise<number>;

  /**
   * insert or update a record.
   * @param payload the data to be persisted
   */
  saveOne(payload: E): Promise<IdValue<E>>;

  /**
   * insert or update records.
   * @param payload the data to be persisted
   */
  saveMany?(payload: E[]): Promise<IdValue<E>[]>;

  /**
   * delete or SoftDelete a record.
   * @param id the primary key of the record
   */
  deleteOneById(id: IdValue<E>, opts?: QueryOptions): Promise<number>;

  /**
   * delete or SoftDelete records.
   * @param qm the criteria to look for the records
   */
  deleteMany(qm: QueryCriteria<E>, opts?: QueryOptions): Promise<number>;
}
```


This tutorial explain how to use repositories with the nukak orm.

Repository

Querying


## Declarative transactions

Both, [Declarative](/docs/transactions-declarative) and [Imperative](/docs/transactions-imperative) `transactions` are supported for flexibility, with the former you can just _describe_ the scope of your transactions, with the later you have more flexibility to programmatically specify the lifecycle of a transaction.

Use `Declarative` transactions as below:

1. take any service class, annotate the wanted function with the `@Transactional` decorator.
2. inject the querier instance by decorating one of the function's arguments with `@InjectQuerier`.

```ts
import { Querier } from 'nukak';
import { Transactional, InjectQuerier } from 'nukak/querier';
import { User, Confirmation } from './shared/models/index.js';

export class ConfirmationService {
  @Transactional()
  async confirm(confirmation: Confirmation, @InjectQuerier() querier?: Querier): Promise<void> {
    if (confirmation.type === 'register') {
      await querier.insertOne(User, {
        name: confirmation.name,
        email: confirmation.email,
        password: confirmation.password,
      });
    } else {
      await querier.updateOneById(User, confirmation.creatorId, {
        password: confirmation.password,
      });
    }
    await querier.updateOneById(Confirmation, confirmation.id, { status: 1 });
  }
}

export const confirmationService = new ConfirmationService();

/**
 * then you could just import the constant `confirmationService` in another file,
 * and when you call `confirmAction` function, all the operations there
 * will (automatically) run inside a single transaction
 */
await confirmationService.confirmAction(data);
```


This tutorial explain how to use declarative transactions with the nukak orm.

Declarative


## Imperative transactions

Both, [Declarative](/docs/transactions-declarative) and [Imperative](/docs/transactions-imperative) `transactions` are supported for flexibility, with the former you can just _describe_ the scope of your transactions, with the later you have more flexibility to programmatically specify the lifecycle of a transaction.

Use `Imperative` transactions as below:

1. get the `querier` instance with `await getQuerier()`.
2. run the transaction with `await querier.transaction(callback)`.
3. perform the different operations using the same `querier` (or `repositories`) inside your `callback` function.

```ts
import { getQuerier } from 'nukak';

async function confirm(confirmation: Confirmation): Promise<void> {
  const querier = await getQuerier();
  await querier.transaction(async () => {
    if (confirmation.action === 'signup') {
      await querier.insertOne(User, {
        name: confirmation.name,
        email: confirmation.email,
        password: confirmation.password,
      });
    } else {
      await querier.updateOneById(User, confirmation.creatorId, {
        password: confirmation.password,
      });
    }
    await querier.updateOneById(Confirmation, confirmation.id, { status: 1 });
  });
}
```

&nbsp;

That &#9650; code can also be implemented as this &#9660; (for more granular control):

```ts
async function confirm(confirmation: Confirmation): Promise<void> {
  const querier = await getQuerier();
  try {
    await querier.beginTransaction();
    if (confirmation.action === 'signup') {
      await querier.insertOne(User, {
        name: confirmation.name,
        email: confirmation.email,
        password: confirmation.password,
      });
    } else {
      await querier.updateOneById(User, confirmation.creatorId, {
        password: confirmation.password,
      });
    }
    await querier.updateOneById(Confirmation, confirmation.id, { status: 1 });
    await querier.commitTransaction();
  } catch (error) {
    await querier.rollbackTransaction();
    throw error;
  } finally {
    await querier.release();
  }
}
```


This tutorial explain how to use imperative transactions with the nukak orm.

Imperative

Transactions


## Express plugin

### Autogenerate REST APIs for the entities with Express

The `nukak-express` package automatically generates REST APIs for your entities.

1. Install `nukak-express` in your server project:

```sh
npm install nukak-express --save
```

or with _yarn_

```sh
yarn add nukak-express
```

2. Initialize the `querierMiddleware` middleware in your server code to generate REST APIs for your entities

```ts
import * as express from 'express';
import { querierMiddleware } from 'nukak-express';
import { User, Product, Category } from './shared/models/index.js';

const app = express();

app
  // ...
  .use(
    '/api',
    // this will generate REST APIs for the entities.
    querierMiddleware({
      /**
       * allow specify which entities will be used by the middleware
       * (all of them are used by default)
       */
      include: [User, Product, Category],
      /**
       * allow excluding some entities of being used by the middleware
       * (all of them are used by default)
       */
      exclude: [User],
      /**
       * Allow augment any kind of request before it runs
       */
      pre(req, meta) {
        console.log(`${req.method} ${req.url} ${meta.name}`);
        if (req.method === 'POST' && meta.entity === Product) {
          console.log(`A new product is going to be created!`);
        }
      },
      /**
       * Allow augment a saving request (POST, PATCH, PUT) before it runs
       */
      preSave(req, meta) {
        req.body.creatorId = req.identify.userId;
      },
      /**
       * Allow augment a filtering request (GET, DELETE) before it runs
       */
      preFilter(req, meta) {
        req.query.$filter.creatorId = req.identify.userId;
      },
    })
  );
```


This tutorial explain how to use the express plugin with the nukak orm.

Express


## Browser plugin

### Use the generated REST API similarly as if you were on the backend

The `nukak-browser` package allows to easily consume `nukak` REST APIs from the frontend.

1. Install `nukak-browser` in your frontend project:

```sh
npm install nukak-browser --save
```

or with _yarn_

```sh
yarn add nukak-browser
```

2. Use `nukak-browser` to call a `nukak` REST API

```ts
import { getRepository } from 'nukak-browser';
import { User } from './shared/models/index.js';

// 'User' is an entity class.
const userRepository = getRepository(User);

const users = await userRepository.findMany(
  {
    $filter: { email: { $endsWith: '@domain.com' } },
    $sort: { createdAt: -1 },
    $limit: 100,
  },
  { email: true, profile: ['picture'] }
);
```


This tutorial explain how to use the browser plugin with the nukak orm.