npm package: @akylas/kiss-orm
Description: An extremely simple and explicit ORM for TypeScript
Author: Sébastien Caparros
Latest version: 2.1.14

# Kiss-ORM

* [Introduction](#introduction)
* [Compatibility](#compatibility)
* [Basics](#basics)
* [Getting started](#getting-started)
* [Events](#events)
* [Cascade](#cascade)
* [Scoping](#scoping)
* [Soft delete](#soft-delete)
* [Migrations](#migrations)
* [Relationships](#relationships)
    - [one-to-one](#one-to-one)
    - [one-to-many](#one-to-many)
    - [many-to-many](#many-to-many)
* [Eager loading for relationships](#eager-loading-for-relationships)
* [Autoloading relationships](#autoloading-relationships)
* [Advanced typings](#advanced-typings)
* [Transactions](#transactions)

## Introduction

Kiss-ORM is a new, very opinionated ORM for TypeScript. Here is a description of it's design philosophy:
- No query builder (you can use the full power and expressiveness of SQL)
- Security: Kiss-ORM allows you to write and concatenate SQL queries without worrying about SQL injections
- Fully tested
- Sane dependency-injection (and dependencies!)
- Data-mapper pattern rather than active-record
- Immutability of the objects
- No magic. Everything is explicit. No database operation is done unless explicitly requested.
- Proper separation of concerns for your repositories
- Simplicity: the architecture is ridiculously simple. If you need complex operations, you have the freedom to write it without worries.
- No mappings: Kiss-ORM always assumes that the column and JS properties have the same name.

## Compatibility

Kiss-ORM is compatible with the following databases:
- PostgreSQL, via the `pg`, `pg-format` and `pg-pool` packages that you need to install
- MySQL, via the `mysql` package that you need to install (experimental support)
- SQLite, via the `sqlite` package that you need to install (experimental support)

## Basics

Kiss-ORM uses the template-strings tagging feature to secure all the queries.

Here is the basic query syntax:

```typescript
database.query(sql`
    SELECT *
    FROM "Users"
    WHERE "email" = ${unsafeInputs.email}
    AND "password" = CRYPT(${unsafeInputs.password})
`);
```

Did you notice the `sql` tag? This internally transforms the query safely into something like this:

```
{
    query: 'SELECT * FROM "Users" WHERE "email" = $1 AND "password" = CRYPT($2)',
    params: ['bob@example.com', '123456'],
}
```

For security reasons, the query method does not accept raw strings, so you cannot forget to use the `sql` tag.

You can also safely include and concatenate queries:
```typescript
const conditions = sql`"role" = ${'admin'} AND "blocked" = ${false}`;
database.query(sql`SELECT * FROM "Users" WHERE ${conditions};`);
```

Result:
```
{
    query: 'SELECT * FROM "Users" WHERE "role" = $1 AND "blocked" = $2',
    params: ['admin', false],
}
```

## Getting started

Installation:
```bash
    npm install kiss-orm --save
```

```typescript
import {
    sql,
    PgSqlDatabase,
    CrudRepository,
} from 'kiss-orm';

class UserModel {
    public readonly id!: number;
    public readonly email!: string;
    public readonly isBlocked!: boolean;

    // Nullable fields are converted to `null`, not `undefined`.
    public readonly emailVerificationCode!: string|null;
}

class UserRepository extends CrudRepository<UserModel> {
    constructor(database: PgSqlDatabase) {
        super({
            database,
            table: 'Users',
            primaryKey: 'id',
            model: UserModel,
        });
    }
}

// [...]

const db = new PgSqlDatabase({
    // https://node-postgres.com/api/client#new-clientconfig-object
    // https://node-postgres.com/api/pool#new-poolconfig-object
});

// Note: You can alternatively inject a Pool object to the `PgSqlDatabase` constructor if you need.

const repository = new UserRepository(db);

const user = await repository.get(2);
const blockedUsers: User[] = await repository.search(
    sql`"isBlocked" OR "email" LIKE ${'%@' + bannedDomain}`,
    sql`"email" ASC`,
);

const updatedUser = await repository.update(user, {
    isBlocked: false,
});

const newUser = await repository.create({
    email: 'alice@example.com',
    isBlocked: false,
    emailVerificationCode: null,
});

await repository.delete(user);

await db.disconnect();
```

## Events

There is no specific feature for the events, because the repositories allows you to do it in an explicit way:

```typescript
class UsersRepository extends CrudRepository<UserModel> {
    public async create(attributes: any): Promise<UserModel> {
        doSomeThingBeforeInsert();
        const user = await super.create(attributes);
        doSomeThingAfterInsert();
        return user;
    }

    public async update(user: UserModel, attributes: any): Promise<UserModel> {
        doSomeThingBeforeUpdate();
        const newUser = await super.update(user, attributes);
        doSomeThingAfterUpdate();
        return newUser;
    }

    public async delete(user: UserModel) {
        doSomeThingBeforeDelete();
        await super.delete(user);
        doSomeThingAfterDelete();
    }
}
```

## Cascade

Cascade operations are not supported by Kiss-ORM, but your database engine does it pretty well already :) .

If you have more complex or specific needs, you will have to specifically implement it with the proper transactions.

## Scoping

Scoping allows you to apply a global filter to all SELECT queries.

```typescript
class AdminUsersRepository extends CrudRepository<UserModel> {
    constructor(database: PgSqlDatabase) {
        super({
            // [...]
            scope: sql`"role" = 'admin'`,
        });
    }
}
```

## Soft delete

Soft-delete can be implemented with the scoping feature

```typescript
class UsersRepository extends CrudRepository<UserModel> {
    constructor(database: PgSqlDatabase) {
        super({
            // [...]
            scope: sql`NOT("deletedFlag")`,
        });
    }
}
```

When you do this, the `delete` method will still trigger a database `DELETE` operation.
If you want to change this behaviour, you can override it:

```typescript
class UsersRepository extends CrudRepository<UserModel> {
    public async delete(user: UserModel) {
        await this.update(user, { deletedFlag: true });
    }
}
```

## Migrations

Kiss-ORM comes with a simple migration system.
You can execute this whenever you want (when your server starts for example).
Since this is a Javascript object, you can choose to organize your migrations
however you want (for example import it for a big unique file, or split it).

```typescript
await db.migrate({
    'createUserTable': sql`
        CREATE TABLE "User" (
            "id" UUID PRIMARY KEY NOT NULL,
            "email" TEXT NOT NULL
        );
    `,
    'addUserEmailIndex': sql`
        CREATE UNIQUE INDEX "User_email_index" ON "User"("email");
    `,
});
```

## Relationships

Relationships are defined in the repositories and must be explicitly loaded

### one-to-one

```typescript
class RoleModel {
    // [...]
}

class UserModel {
    // [...]
    public readonly roleId!: number;

    public role?: RoleModel;
}

class RoleRepository extends CrudRepository<RoleModel> {
    // [...]
}

class UserRepository extends CrudRepository<UserModel> {
    // [...]
    async loadRoleRelationship(user: UserModel): Promise<UserModel> {
        return this.createModelFromAttributes({
            ...user,
            role: await (new RoleRepository(this.database)).get(user.roleId),
        });
    }
}

const repository = new UserRepository(database);
let user = await repository.get(1);
// Currently. user.role is `undefined`. You explicitly need to load it
user = await repository.loadRoleRelationship(user);
// `user.role` is now populated with a `RoleModel`.
```

### one-to-many

```typescript
class RoleModel {
    // [...]
    public users?: ReadonlyArray<UserModel>;
}

class RoleRepository extends CrudRepository<RoleModel> {
    // [...]
    async loadUsersRelationship(role: RoleModel): Promise<RoleModel> {
        return this.createModelFromAttributes({
            ...role,
            users: await (new UserRepository(this.database)).search(sql`"roleId" = ${role.id}`),
        });
    }
}

const repository = new RoleRepository(database);
let role = await repository.get(1);
role = await repository.loadUsersRelationship(role);
// role.users is now populated with an array of `UserModel`
```

### many-to-many

```typescript
class ArticleModel {
    // [...]
    public readonly authors?: ReadonlyArray<UserModel>;
}

class UserModel {
    // [...]
    public readonly articles?: ReadonlyArray<ArticleModel>;
}

class ArticleRepository extends CrudRepository<ArticleModel> {
    // [...]
    async loadAuthorsRelationship(article: ArticleModel): Promise<ArticleModel> {
        return this.createModelFromAttributes({
            ...article,
            authors: await (new UserRepository(this.database)).search(sql`
                "id" IN (
                    SELECT "userId"
                    FROM "ArticleAuthors"
                    WHERE "articleId" = ${article.id}
                )
            `),
        });
    }
}

class UserRepository extends CrudRepository<UserModel> {
    // [...]
    async loadArticlesRelationship(user: UserModel): Promise<UserModel> {
        return this.createModelFromAttributes({
            ...user,
            articles: await (new AuthorRepository(this.database)).search(sql`
                "id" IN (
                    SELECT "articleId"
                    FROM "ArticleAuthors"
                    WHERE "userId" = ${user.id}
                )
            `),
        });
    }
}

const repository = new UserRepository(database);
let user = await repository.get(1);
user = await repository.loadArticlesRelationship(user);
// `user.articles` is now populated with an array of `ArticleModel`.

const repository = new ArticleRepository(database);
let article = await repository.get(1);
article = await repository.loadAuthorsRelationship(article);
// `user.authors` is now populated with an array of `UserModel`.
```

## Eager loading for relationships

Kiss-ORM only supports lazy-loading (on-demand).
If you need something more complex, you should implement the queries specifically.

## Autoloading relationships

```typescript
class UserRepository extends CrudRepository<UserModel> {
    // [...]

    // This function is called everytime an object is created by Kiss-ORM
    // I don't recommend to do this because it will result in a lot of unnecessary queries...
    protected async createModelFromAttributes(attributes: any): Promise<UserModel> {
        const user = super.createModelFromAttributes(attributes);
        await this.loadRoleRelationship(user);
        await this.loadArticlesRelationship(user);
        return user;
    }
}
```

## Advanced typings

By default, the type of the primary key (for the `get` method) and the parameters
of the `create` and `update` methods is `any`, but you can specify it.

When you are using a `serial` / `auto-increment` id, you should not specify the id in the properties list.

```typescript
class UserRepository extends CrudRepository<
    UserModel, // Object returned by the methods
    { email: string, isBlocked: boolean },
    number, // Type of the primary key (id)
> {
    // [...]
}
```

Handling uuids or automatically filled columns is a bit more tricky, but possible:

```typescript
type UserParams = {
    uuid: SqlQuery,
    email: string,
    // [...]
    createdAt: Date,
};

type AllowedUserParams = Omit<UserParams, 'id' | 'createdAt'>;

export default class User extends CrudRepository<UserModel, UserParams, string> {
    // [...]

    async create (attributes: AllowedUserParams): Promise<UserModel> {
        return super.create({
            ...attributes,
            uuid: sql`gen_random_uuid()`,
            createdAt: new Date(),
        });
    }
}
```

## Transactions

The `sequence` method can be used to run transactions. It ensures that all the queries
are done on the same connection and that the connection is dedicated
(no other async process can execute a query on this connection during the sequence).

```typescript
await database.sequence(async sequenceDb => {
    await sequenceDb.query(sql`BEGIN;`);
    // [...]
    await sequenceDb.query(sql`COMMIT;`);
});
```

Note: On SQLite, the `sequence` function does not give the same guarantees. Since SQLite is local and runs with a single process without a pool of connections, this function is just a wrapper for `serialize`.