@uql/sqlite

uql ·

Learn how to use uql in your own project.

Getting Started

uql is a flexible and efficient ORM, with declarative JSON syntax and smart type-safety.

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.
• the generated queries are efficient, safe, and human-readable.
• $project, $filter, $sort, $limit works at multiple levels (including deep relations and their fields).
• declarative and programmatic transactions.
• soft-delete, virtual fields, repositories, connection pooling.
• different kinds of relations between entities.
• transparent support for inheritance patterns between entities.
• supports Postgres, MySQL, MariaDB, SQLite, MongoDB (beta).
• plugins for frameworks: express (more coming).

Table of Contents

1. Installation
2. Configuration
3. Entities
4. Declarative Transactions
5. Programmatic Transactions
6. Generate REST APIs with Express
7. Consume REST APIs from the Frontend
8. FAQs

Installation

1. Install the core package:

   npm install @uql/core --save
   

   or

   yarn add @uql/core
   

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

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

1. take any service class, annotate the wanted function with the @Transactional decorator.
2. 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';

class ConfirmationService {
  @Transactional()
  async confirmAction(confirmation: Confirmation, @InjectQuerier() querier?: Querier): Promise<void> {
    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: 1 });
  }
}

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:

1. obtain the querier object with await getQuerier().
2. open a try/catch/finally block.
3. start the transaction with await querier.beginTransaction().
4. perform the different operations using the querier or repositories.
5. commit the transaction with await querier.commitTransaction().
6. in the catch block, add await querier.rollbackTransaction().
7. 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): Promise<void> {
  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: 1 });
    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.

1. Install express plugin in your server project:

npm install @uql/express --save

or with yarn

yarn add @uql/express

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

1. Install client plugin in your frontend project:

npm install @uql/client --save

or with yarn

yarn add @uql/client

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