Package Exports

@uql/mysql

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (@uql/mysql) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

🌞 Getting Started

uql is a plug & play ORM, with declarative JSON syntax to query/update multiple data sources. Just declare what you want from the data source, and then uql will run fast and safe SQL (or Mongo) queries.

Given it is just a small library with serializable JSON syntax, the queries can be written in the client (web/mobile) and send to the backend, or just use uql directly in the backend, or even use it in a mobile app with an embedded database.

Features

JSON (serializable) syntax for all the queries.
uses the power of TypeScript to get (smart) type-safety everywhere.
generated queries are fast, safe, and human-readable.
criteria like $project, $filter, $sort, $limit works at multiple levels (including deep relations and their fields).
declarative and programmatic transactions.
soft-delete.
virtual fields.
entity repositories.
different kinds of relations between entities.
transparent support for inheritance patterns between entities.
connection pooling.
supports Postgres, MySQL, MariaDB, SQLite, MongoDB (beta).
plugins for frameworks: express (more coming).

Installation
Configuration
Entities
Declarative Transactions
Programmatic Transactions
Generate REST APIs with Express
Consume REST APIs from the Frontend
FAQs

🔋 Installation

Install the core package:

npm install @uql/core --save

yarn add @uql/core

Install one of database packages according to your database:

for MySQL (or MariaDB)

npm install @uql/mysql --save

or with yarn

yarn add @uql/mysql

for PostgreSQL

npm install @uql/postgres --save

or with yarn

yarn add @uql/postgres

for SQLite

npm install @uql/sqlite --save

or with yarn

yarn add @uql/sqlite

for MongoDB

npm install @uql/mongo --save

or with yarn

yarn add @uql/mongo

Additionally, your tsconfig.json needs the following flags:

"target": "es6", // or a more recent ecmascript version
"experimentalDecorators": true,
"emitDecoratorMetadata": true

⚙️ Configuration

Initialization should be done once (e.g. in one of the bootstrap files of your app).

import { setOptions } from '@uql/core';
import { PgQuerierPool } from '@uql/postgres';

setOptions({
  querierPool: new PgQuerierPool({
    host: 'localhost',
    user: 'theUser',
    password: 'thePassword',
    database: 'theDatabase',
  }),
  logging: true,
  logger: console.log,
});

🥚 Entities

Take any dump class (aka DTO) and annotate it with the decorators from '@uql/core/entity'.

import { Field, ManyToOne, Id, OneToMany, Entity, OneToOne, ManyToMany } from '@uql/core/entity';

@Entity()
export class Profile {
  /**
   * primary key
   */
  @Id()
  id?: number;
  @Field()
  picture?: string;
  /**
   * foreign-keys are really simple to specify
   */
  @Field({ reference: () => User })
  creatorId?: number;
}

@Entity()
export class User {
  @Id()
  id?: number;
  @Field()
  name?: string;
  @Field()
  email?: string;
  @Field()
  password?: string;
  /**
   * `mappedBy` can be a callback or a string (callback is useful for auto-refactoring)
   */
  @OneToOne({ entity: () => Profile, mappedBy: (profile) => profile.creatorId, cascade: true })
  profile?: Profile;
}

@Entity()
export class MeasureUnitCategory {
  @Id()
  id?: number;
  @Field()
  name?: string;
  @OneToMany({ entity: () => MeasureUnit, mappedBy: (measureUnit) => measureUnit.category })
  measureUnits?: MeasureUnit[];
}

@Entity()
export class MeasureUnit {
  @Id()
  id?: number;
  @Field()
  name?: string;
  @Field({ reference: () => MeasureUnitCategory })
  categoryId?: number;
  @ManyToOne({ cascade: 'persist' })
  category?: MeasureUnitCategory;
}

@Entity()
export class Item {
  @Id()
  id?: number;
  @Field()
  name?: string;
  @Field()
  description?: string;
  @Field()
  code?: string;
  @ManyToMany({ entity: () => Tag, through: () => ItemTag, cascade: true })
  tags?: Tag[];
}

@Entity()
export class Tag {
  @Id()
  id?: number;
  @Field()
  name?: string;
  @ManyToMany({ entity: () => Item, mappedBy: (item) => item.tags })
  items?: Item[];
}

@Entity()
export class ItemTag {
  @Id()
  id?: number;
  @Field({ reference: () => Item })
  itemId?: number;
  @Field({ reference: () => Tag })
  tagId?: number;
}

💬 Declarative Transactions

Both, declarative and programmatic transactions are supported, with the former you can just describe the scope of your transactions, with the later you have more flexibility (hence more responsibility).

To use Declarative Transactions (using the @Transactional decorator):

take any service class, annotate the wanted function with the @Transactional decorator.
inject the querier instance by decorating one of the function's arguments with @InjectQuerier.

import { Querier } from '@uql/core/type';
import { Transactional, InjectQuerier } from '@uql/core/querier/decorator';

class ConfirmationService {
  @Transactional()
  async confirmAction(confirmation: Confirmation, @InjectQuerier() querier?: Querier) {
    if (confirmation.type === 'register') {
      await querier.insertOne(User, {
        name: confirmation.name,
        email: confirmation.email,
        password: confirmation.password,
      });
    } else {
      await querier.updateOneById(User, confirmation.creatorId, { password: confirmation.password });
    }
    await querier.updateOneById(Confirmation, confirmation.id, { status: CONFIRM_STATUS_VERIFIED });
  }
}

export const confirmationService = new ConfirmationService();

/**
 * then you could just import the constant `confirmationService` in another file,
 * and when you call `confirmAction` function, all the operations there
 * will (automatically) run inside a single transaction
 */
await confirmationService.confirmAction(data);

👐 Programmatic Transactions

uql supports both, declarative and programmatic transactions, with the former you can just describe the scope of your transactions, with the later you have more flexibility (hence more responsibility).

To use Programmatic Transactions:

obtain the querier object with await getQuerier().
open a try/catch/finally block.
start the transaction with await querier.beginTransaction().
perform the different operations using the querier or repositories.
commit the transaction with await querier.commitTransaction().
in the catch block, add await querier.rollbackTransaction().
release the querier back to the pool with await querier.release() in the finally block.

import { getQuerier } from '@uql/core';

async function confirmAction(confirmation: Confirmation) {
  const querier = await getQuerier();
  try {
    await querier.beginTransaction();
    if (confirmation.action === 'signup') {
      await querier.insertOne(User, {
        name: confirmation.name,
        email: confirmation.email,
        password: confirmation.password,
      });
    } else {
      await querier.updateOneById(User, confirmation.creatorId, { password: confirmation.password });
    }
    await querier.updateOneById(Confirmation, confirmation.id, { status: CONFIRM_STATUS_VERIFIED });
    await querier.commitTransaction();
  } catch (error) {
    await querier.rollbackTransaction();
    throw error;
  } finally {
    await querier.release();
  }
}

⚡ Autogenerate REST APIs with Express

A express plugin is provided to automatically generate REST APIs for your entities.

Install express plugin in your server project:

npm install @uql/express --save

or with yarn

yarn add @uql/express

Initialize the express middleware in your server code to generate REST APIs for your entities

import * as express from 'express';
import { querierMiddleware } from '@uql/express';

const app = express();

app
  // ...
  .use(
    '/api',

    // this will generate REST APIs for the entities.
    querierMiddleware({
      // all entities will be automatically exposed unless
      // 'include' or 'exclude' options are provided
      exclude: [Confirmation],

      // `query` callback allows to extend all then queries that are requested to the API,
      // so it is a good place to add additional filters to the queries (like for multi tenant apps)
      query<E>(entity: Type<E>, qm: Query<E>, req: Request): Query<E> {
        qm.$filter = {
          ...qm.$filter,
          // ensure the user can only see the data that belongs to his company
          companyId: req.identity.companyId,
        };
        return qm;
      },
    })
  );

☁️ Easily call the generated REST APIs from the Client

A client plugin (for browser/mobile) is provided to easily consume the REST APIs from the frontend.

Install client plugin in your frontend project:

npm install @uql/client --save

or with yarn

yarn add @uql/client

Use the client to call the uql CRUD API

import { getRepository } from '@uql/client';

// 'User' is an entity class
const userRepository = getRepository(User);

const users = await userRepository.findMany({
  $project: { email: true, profile: ['picture'] },
  $filter: { email: { $endsWith: '@domain.com' } },
  $sort: { createdAt: -1 },
  $limit: 100,
});

@uql/mysql

Package Exports

Readme

🌞 Getting Started

Features

Table of Contents

🔋 Installation

⚙️ Configuration

🥚 Entities

💬 Declarative Transactions

👐 Programmatic Transactions

⚡ Autogenerate REST APIs with Express

☁️ Easily call the generated REST APIs from the Client