
## 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`, and `$filter` 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`, and `$filter` 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, { $project: ['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,
  {
    $project: ['id'],
    $filter: {
      tagsCount: { $gte: 10 },
    },
  }
);
```

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.

Entities Virtual Fields


[nukak](https://nukak.org) is the [smartest ORM](https://medium.com/@rogerpadillac/in-search-of-the-perfect-orm-e01fcc9bce3d) for TypeScript, it is designed to be fast, safe, and easy to integrate into any application. It takes inspiration from [mongo queries](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, Electron, Bun and Deno.

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

&nbsp;

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

&nbsp;

## Why nukak?

See [this article](https://medium.com/@rogerpadillac/in-search-of-the-perfect-orm-e01fcc9bce3d) in medium.com.

&nbsp;

## Features

- **Type-safe and Context-aware queries**: squeeze the powers of `TypeScript` so it auto-completes and validates, 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) can be `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.
- **FP + OOP**: Combines the best elements of `FP` (Functional Programming) and `OOP` (Object Oriented 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): `ESM` is natively supported by Node.js 16 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;

## 1. 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": "es2022",
   "experimentalDecorators": true,
   "emitDecoratorMetadata": true
   ```

&nbsp;

---

&nbsp;

## 2. 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 { Id, Field, Entity } 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;

## 3. Setup 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 when calling `getQuerier()` and
// in the `@Transactional` decorator (by default).
setQuerierPool(querierPool);
```

&nbsp;

## 4. Manipulate the data

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

async function findLastUsers(limit = 100) {
  const querier = await getQuerier();
  const users = await querier.findMany(
    User,
    {
      $project: { id: true, name: true, email: true },
      $sort: { createdAt: 'desc' },
      $limit: limit,
    }    
  );
  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, { $project: ['id', 'name'], $limit: 100 });
```

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


## 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`, and `$filter` 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`, and `$filter` 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,
  {
    $project: ['id'],
    $filter: { name: 'maku', creatorId: 1 },
  }
);
```

or the same query with an explicit `$and`

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

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,
  {
    $project: ['id'],
    $filter: { $or: [{ name: 'maku' }, { creatorId: 1 }] },
  }
);
```

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,
  {
    $project: ['id'],
    $filter: { $not: [{ name: 'maku' }, { creatorId: 1 }] },
  }
);
```

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,
  {
    $project: ['id'],
    $filter: { $nor: [{ name: 'maku' }, { creatorId: 1 }] },
  }
);
```

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,
  {
    $project: ['id'],
    $filter: { name: { $istartsWith: 'Some', $ne: 'Something' } },
    $sort: { name: 1, id: -1 },
    $limit: 50,
  }
);
```

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,
    {
      $project: {
        id: true,
        name: true,
        profile: {
          $project: ['id', 'picture'],
          $required: true
        }
      },
      $sort: { createdAt: -1 },
    }
  );
}
```

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,
    {
      $project: {
        id: true,
        name: true,
        profile: ['id', 'picture'],
      },
      $sort: { createdAt: -1 },
    }    
  );
  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,
      {
        $project: {
          id: true,
          name: true,
          measureUnit: {
            $project: ['id', 'name'],
            $filter: { name: { $ne: 'unidad' } },
            $required: true
          },
          tax: ['id', 'name'],
        },
        $filter: { salePrice: { $gte: 1000 }, name: { $istartsWith: 'A' } },
        $sort: { tax: { name: 1 }, measureUnit: { name: 1 }, createdAt: -1 },
        $limit: 100,
      }      
    );
  }
}

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

`$filter` by a `raw` expression:

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

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

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

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

&nbsp;

`$nexists` comparison operator:

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

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

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.

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,
  {
    $project: ['id'],
    $filter: { $or: [{ name: 'maku' }, { creatorId: 1 }] },
  }
);

await querier.release();
```



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(
  {
    $project: ['id'],
    $filter: { $or: [{ name: 'maku' }, { creatorId: 1 }] },
  }
);

await querier.release();
```


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.

**Note**: Pre-requisite step, define a querier pool.

```ts
// in file querierPool.ts
import { setQuerierPool } from 'nukak';
import { PgQuerierPool } from 'nukak-postgres';

export const querierPool = new PgQuerierPool(
  {
    host: process.env.DB_HOST,
    port: +process.env.DB_PORT,
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
  },
  { logger: console.log }
);

setQuerierPool(querierPool);
```

---

### Method 1: independent functions for more granular control `querier.*`.

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

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();
  }
}
```

---

### Method 2: shorthand `querier.transaction(...)`:

```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 });
  });
  await querier.release()
}
```

---

### Method 3: shorthand `querierPool.transaction(...)`.

```ts
import { querierPool } from './querierPool.js';

async function confirm(confirmation: Confirmation): Promise<void> {
  await querierPool.transaction(async (querier) => {
    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 });
  });
}
```

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(
  {
    $project: { email: true, profile: ['picture'] },
    $filter: { email: { $endsWith: '@domain.com' } },
    $sort: { createdAt: -1 },
    $limit: 100,
  }
);
```


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