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

const conditions = sql`"role" = ${'admin'} AND "blocked" = ${false}`;
database.query(sql`SELECT * FROM "Users" WHERE ${conditions};`);

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: '[email protected]',
    isBlocked: false,
    emailVerificationCode: null,
});

await repository.delete(user);

await db.disconnect();

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

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

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

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

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

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

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`

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

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;
    }
}

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

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

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