PanaORM

PanaORM is an ORM that is focused on letting you to write SQL queries in a type-safe way by using the Typescript or Javascript arrow functions. PanaORM are inspired highly by LINQ and ORMs in .NET ecosystem, such as EF Core and Linq2Db.

PanaORM goal is to provide a intuitive and type-safe way to write simple or even complex SQL queries. Its designed to eliminate the inconsistencies and confusion that usually presents in other ORMs when you switch from its query object to query builder or raw SQL.

Features

Features that PanaORM provides:

• Entity mapping using decorators or fluent API.
• Intuitive & type-safe way to write SQL queries using arrow functions.
• Relation mapping that is focused on lazy loading, but no worry you can still eager load it.
• Relation loading strategies with single or split queries.
• Write your complex queries in expressive way.
• Robust raw SQL support with DSL-like syntax.
• Support for multiple databases such as PostgreSQL, MySQL, SQLite, etc.
• Support for transactions and savepoints.
• UnitOfWork pattern with EF-like DataContext.
• Minimal or full-pledged Change Tracking.
• Multiple migration strategies.
• Query caching.

Upcoming features:

• UnmanagedQueryBuilder just like usual ORM query builder.
• Inheritance strategies.
• Compiled query.

Quick Start

Installation

npm install panaorm

Creating a DataContext

DataContext is a class that is used to interact with the database. It is responsible for managing the connection and transaction. Its also acts as a unit of work.

import { DataContext } from 'panaorm';

class MyDataContext extends DataContext {
    constructor() {
        super({
            type: 'sqlite',
            database: 'mydb.sqlite',
        });
    }
}

Creating an Entity

Entity is a class that represents a table in the database. You can use decorators or fluent API to map the entity to the table.

import { Entity, Column, Key } from 'panaorm';

@Entity()
class User {
    @Key({ autoIncrement: true })
    id: number;

    @Column()
    name: string;

    @Column()
    email: string;
}

If you are not a fan of decorators, you can use fluent API to map the entity.

import { Entity, Column, Key } from 'panaorm';

class User {
    id: number;
    name: string;
    email: string;
}

Entity(User)
    .key('id', { autoIncrement: true })
    .column('name', { type: 'string' })
    .column('email', { type: 'string' });

Querying Data

After creating the DataContext and Entity, you can start querying the data. You can use the fromSet method to query the data.

const dataContext = new MyDataContext();

const users = await dataContext
    .fromSet(User)
    .where(u => u.name === 'John')
    .first();

This will generate the following SQL query:

SELECT * FROM "User" WHERE "name" = 'John'

If you want to select a specific column, you can use the select method.

const users = await dataContext
    .fromSet(User)
    .where(u => u.name === 'John')
    .select(u => {
        return {
            id: u.id,
            fullname: u.name,
        };
    })
    .first();

This will generate the following SQL query:

SELECT "id", "name" AS "fullname" FROM "User" WHERE "name" = 'John'

Selecting Multiple Data

Easiest way to fetch multiple data is using the toArray method.

const users = await dataContext
    .fromSet(User)
    .where(u => u.name === 'John')
    .toArray();

Using toArray will directly fetch the data from the database. However since PanaORM is using lazy loading, you can use for-await-of to fetch the data lazily.

const users = dataContext
    .fromSet(User)
    .where(u => u.name === 'John');

for await (const user of users) {
    console.log(`Hi ${user.name}`);
}

If you have a large data, you can use take and skip method to limit the data.

const users = await dataContext
    .fromSet(User)
    .where(u => u.name === 'John')
    .skip(10)
    .take(10)
    .toArray();

Otherwise, you can use toStreamable to create a query stream that will not fetch all the data at once. However, streamable query is depend on the database driver support.

const users = dataContext
    .fromSet(User)
    .where(u => u.name === 'John')

for await (const user of users.toStreamable()) {
    console.log(`Hi ${user.name}`);
}

Inserting Data

You can use the insert method to insert the data.

const user = new User();
user.name = 'John';
user.email = '[email protected]';

await dataContext.insert(user);

Updating Data

You can use the update method to update the data.

const user = await dataContext
    .fromSet(User)
    .where(u => u.name === 'John')
    .first();

user.email = '[email protected]';

await dataContext.update(user);

Updating many records at once

To update many records at once, you can use the updateMany method with the where method.

await dataContext
    .fromSet(User)
    .where(u => u.includes('bot'))
    .updateMany()
    .set(x => x.isInactive = true)
    .execute();

Deleting Data

You can use the delete method to delete the data.

await dataContext
    .fromSet(User)
    .where(u => u.name === 'John')
    .delete();

Relation Mapping

To define relation, you can use @HasOne or @HasMany decorator. For example we will change our User entity to have a profile.

import { Entity, Column, Key, HasOne } from 'panaorm';

@Entity()
class User {
    @Key({ autoIncrement: true })
    id: number;

    @Column()
    name: string;

    @Column()
    email: string;

    @HasOne(() => Profile)
    profile: Profile;
}

@Entity()
class Profile {
    @Key({ autoIncrement: true })
    id: number;

    @Column()
    bio: string;

    @HasMany(() => User)
    users: User[];
}

Now you can query the data with relation.

const users = await dataContext
    .fromSet(User)
    .where(u => u.name === 'John')
    .include(u => u.profile)
    .select();

This will generate the following SQL query:

SELECT * FROM "User" WHERE "name" = 'John'
SELECT * FROM "Profile" WHERE "userId" IN (1, 2, 3)

Working lazily with relation

Using an array to work with relation can be tricky, especially with lazy approach. Often you only need some data from the relation, not all of it. Its hard to do that with array.

Thats why PanaORM provides a RelationSet to work with relation.