Uql-browser NPM

`{*}` uql = Universal Query Language

uql is a plug & play ORM, with a declarative JSON syntax to query/update different data-sources. Basically, just declare what you want from your datasource, and then uql runs efficient (and safe) SQL or Mongo queries.

:star2: Features

supports on-demand populate (at multiple levels), projection of fields/columns (at multiple levels), complex filtering (at multiple levels), grouping, and pagination.
declarative and imperative transactions
generic and custom repositories
relations between entities
supports entities inheritance patterns
connection pooling
supports Postgres, MySQL, MariaDB, SQLite, MongoDB
code is readable, short, performant and flexible
plugins for frameworks: express, more soon...

:battery: Installation

Install the npm package:
npm install uql --save or yarn add uql
Make sure to enable the following properties in the tsconfig.json file of your TypeScript project: experimentalDecorators and emitDecoratorMetadata
Install a database driver according to your database:
- for MySQL or MariaDB
  npm install mysql2 --save (alternatively, mysql or mariadb driver can be used)
- for PostgreSQL or CockroachDB
  npm install pg --save
- for SQLite
  npm install sqlite3 --save
- for MongoDB
  npm install mongodb --save

:egg: Entities

Notice that the inheritance between entities is optional

import { v4 as uuidv4 } from 'uuid';
import { Id, Property, OneToMany, OneToOne, ManyToOne, Entity } from 'uql/entity/decorator';

/**
 * an abstract class can (optionally) be used as a template for the entities
 * (so boilerplate code is reduced)
 */
export abstract class BaseEntity {
  @Id()
  id?: string;

  /**
   * different relations between entities are supported
   */
  @ManyToOne({ type: () => Company })
  company?: string | Company;

  @ManyToOne({ type: () => User })
  user?: string | User;

  /**
   * 'onInsert' callback can be used to specify a custom mechanism for
   * obtaining the value of a property when inserting:
   */
  @Property({ onInsert: () => Date.now() })
  createdAt?: number;

  /**
   * 'onUpdate' callback can be used to specify a custom mechanism for
   * obtaining the value of a property when updating:
   */
  @Property({ onUpdate: () => Date.now() })
  updatedAt?: number;

  @Property()
  status?: number;
}

@Entity()
export class Company extends BaseEntity {
  @Property()
  name?: string;

  @Property()
  description?: string;
}

/**
 * a custom name can be specified for the corresponding table/document
 */
@Entity({ name: 'user_profile' })
export class Profile extends BaseEntity {
  /**
   * a custom name can be (optionally) specified for every property (this also overrides parent's class ID declaration)
   */
  @Id({ name: 'pk' })
  id?: string;

  @Property({ name: 'image' })
  picture?: string;
}

@Entity()
export class User extends BaseEntity {
  @Property()
  name?: string;

  @Property()
  email?: string;

  @Property()
  password?: string;

  @OneToOne({ mappedBy: 'user' })
  profile?: Profile;
}

@Entity()
export class TaxCategory extends BaseEntity {
  /**
   * Any entity can specify its own ID Property and still inherit the others
   * columns/relations from its parent entity.
   * 'onInsert' callback can be used to specify a custom mechanism for
   * auto-generating the primary-key's value when inserting
   */
  @Id({ onInsert: () => uuidv4() })
  pk?: string;

  @Property()
  name?: string;

  @Property()
  description?: string;
}

@Entity()
export class Tax extends BaseEntity {
  @Property()
  name?: string;

  @Property()
  percentage?: number;

  @ManyToOne()
  category?: TaxCategory;

  @Property()
  description?: string;
}

:gear: Configuration

import { setOptions } from 'uql';
import { GenericRepository } from 'uql/repository';

setOptions({
  datasource: {
    driver: 'pg',
    host: 'localhost',
    user: 'theUser',
    password: 'thePassword',
    database: 'theDatabaseName',
  },
  defaultRepositoryClass: GenericRepository,
  debug: true,
});

:speaking_head: Declarative Transactions

import { Querier } from 'uql/type';
import { Transactional, InjectQuerier } from 'uql/querier/decorator';
import { getRepository } from 'uql/repository';

export class ConfirmationService {
  // declarate a transaction
  @Transactional()
  async confirmAction(body: Confirmation, @InjectQuerier() querier?: Querier): Promise<void> {
    const userRepository = getRepository(User);
    const confirmationRepository = getRepository(Confirmation);

    if (body.type === 'register') {
      const newUser: User = {
        name: body.name,
        email: body.email,
        password: body.password,
      };
      await userRepository.insertOne(newUser, querier);
    } else {
      const userId = body.user as string;
      await userRepository.updateOneById(userId, { password: body.password }, querier);
    }

    await confirmationRepository.updateOneById(body.id, { status: CONFIRM_STATUS_VERIFIED }, querier);
  }
}

:hammer_and_wrench: Programmatic Transactions

import { getQuerier } from 'uql/querier';

// ...then inside any of your functions

const querier = await getQuerier();

try {
  // programmatically start a transaction
  await querier.beginTransaction();

  // create one user
  const insertedId: string = await querier.insertOne(User, {
    name: 'Some Name',
    email1: { picture: 'abc1@example.com' },
    profile: { picture: 'abc1' },
  });

  // create multiple users in a batch
  const insertedIds: string[] = await querier.insert(User, [
    {
      name: 'Another Name',
      email: { picture: 'abc2@example.com' },
      profile: { picture: 'abc2' },
      company: 123,
    },
    {
      name: 'One More Name',
      email: { picture: 'abc3@example.com' },
      profile: { picture: 'abc3' },
      company: 123,
    },
  ]);

  // find users
  const users: User[] = await querier.find(User, {
    project: {
      id: true,
      email: true,
      password: true,
    },
    populate: {
      profile: {
        project: {
          id: true,
          picture: true,
        },
      },
    },
    filter: { company: 123 },
    limit: 100,
  });

  // update users
  const updatedRows: number = await querier.updateOneById(User, generatedId, { company: 123 });

  // removed users
  const updatedRows: number = await querier.removeOneById(User, generatedId);

  // count users
  const count: number = await querier.count(User, { company: 3 });

  await querier.commitTransaction();
} catch (error) {
  await querier.rollbackTransaction();
} finally {
  await querier.release();
}

:zap: Generate REST APIs from Express

uql provides a express plugin to automatically generate REST APIs for your entities.

Install express plugin in your server project npm install uql-express --save or yarn add uql-express
Initialize the express middleware in your server code to generate CRUD REST APIs for your entities

import { entitiesMiddleware } from 'uql-express';

const app = express();

app
  // ...other routes may go before and/or after (as usual)
  .use(
    '/api',
    // this will generate CRUD REST APIs for the entities.
    // all entities will be automatically exposed unless
    // 'include' or 'exclude' options are provided
    entitiesMiddleware({
      exclude: [Confirmation, User],
    })
  );

:globe_with_meridians: Consume REST APIs from Frontend

uql provides a browser plugin to consume the REST APIs.

Install browser plugin in your frontend project npm install uql-browser --save or yarn add uql-browser
Initialize uql in your frontend code

import { setOptions, GenericRepository, getRepository } from 'uql-browser';

setOptions({
  defaultRepositoryClass: GenericRepository,
  debug: true,
});

// 'Item' is an entity class
const lastItems = await getRepository(Item).find({ sort: { createdAt: -1 }, limit: 100 });

:book: Frequently Asked Questions

Why uql if there already are GraphQL, TypeORM, Mikro-ORM, Sequelize?

GraphQL requires additional servers and also learning a new language; uql should allow same this, but without need to configure and maintaining additional components.

On the other hand, existing ORMs like TypeORM, Mikro-ORM, and Sequelize; are in one way or another, coupled to databases; uql uses a declarative JSON syntax (agnostic from the datasource) which can easily be serialized and send as messages (e.g. through the network) between different components of a system (e.g. micro-services), and then each one has the flexibility to decide how to process these messages.

At last but not at least, uql helps with the communication between the different tiers of your system, e.g. it allows the frontend to send dynamic requests to the backend (like GraphQL).