@akylas/kiss-orm

Kiss-ORM

• Introduction
• Compatibility
• Basics
• Getting started
• Events
• Cascade
• Scoping
• Soft delete
• Migrations
• Relationships
  • one-to-one
  • one-to-many
  • many-to-many
• Eager loading for relationships
• Autoloading relationships
• Advanced typings
• 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:

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: ['[e-mail]', '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:

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:

    npm install kiss-orm --save

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: '[e-mail]',
    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:

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.

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

Soft delete

Soft-delete can be implemented with the scoping feature

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:

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

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

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

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

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

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.

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:

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

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.