typeorm-query-builder-wrapper

Easy Wrapper for TypeORM Query Builder

TypeORM Query Builder Wrapper

TypeORM is one of ORM that can run in NodeJS, Browser, Cordova, PhoneGap, Ionic, React Native, NativeScript, Expo, and Electron platforms and can be used with TypeScript and JavaScript. To use TypeORM to create connection, entities, etc, please visit this link. One of the great feature in TypeORM is Query Builder, that you can build query with builder pattern so you can write query with flexible and elegant syntax without to know more about SQL knowledge, to use Query Builder please visit this link. But, its Query Builder have a weakness that you have to write query with RAW that allow you to make a mistakes, typos, and of course reduce the elegancy of it. So, this library allows you to use TypeORM Query Builder in very easy, safe way, and of course more elegant. This library was inspired by TypeORM LINQ Repository

Features

• Currently it supports for SQL Database, especially PostgreSQL
• TypeScript and JavaScript support
• Produced code is performant, flexible, clean and maintainable

Installation

• Install the yarn package:

  yarn add typeorm typeorm-query-builder-wrapper --save

• You need to install reflect-metadata shim:

  npm install reflect-metadata --save

  and import it somewhere in the global place of your app (for example in app.ts):

  import "reflect-metadata";

• You may need to install node typings:

  npm install @types/node --save-dev

• Install a database driver:

  • for PostgreSQL or CockroachDB

    npm install pg --save

TypeScript configuration

Also, make sure you are using TypeScript version 3.3 or higher, and you have enabled the following settings in tsconfig.json:

"emitDecoratorMetadata": true,
"experimentalDecorators": true,

You may also need to enable es6 in the lib section of compiler options, or install es6-shim from @types.

TypeORM

Please visit this to setup what needs and how to use TypeORM.

Usage

• QueryBuilder
• Instantiate a QueryBuilder
• Adding Filter and Pagination
• Adding SELECT expression
• Adding WHERE expression
• Adding HAVING expression
• Adding DISTINCT ON expression
• Adding GROUP BY expression
• Joining relations
• Inner and left joins
• Getting the generated query
• Getting raw results
• Streaming result data
• Set locking
• Using subqueries

QueryBuilder

QueryBuilder is one of the most powerful features of TypeORM - it allows you to build SQL queries using elegant and convenient syntax, execute them and get automatically transformed entities.

Assume that you have User Entity like this:

import { BaseEntity, Column, Entity, PrimaryGeneratedColumn, UpdateDateColumn, CreateDateColumn, } from 'typeorm';

@Entity('users')
export class User extends BaseEntity {
    @PrimaryGeneratedColumn('uuid')
    id: string;

    @Column({
        type: 'boolean',
        name: 'is_deleted',
        default: false
    })
    isDeleted: boolean;

    @CreateDateColumn({
        type: 'timestamp',
        name: 'create_date_time',
        default: () => 'CURRENT_TIMESTAMP',
    })
    createDateTime: Date;

    @Column({ type: 'uuid', name: 'create_user_id' })
    createUserId: string;

    @Column('character varying', {
        nullable: false,
        length: 255,
        name: 'name',
    })
    name: string;

    @Column('character varying', {
        nullable: false,
        length: 255,
        name: 'username',
    })
    username: string;

    @Column('character varying', {
        nullable: false,
        length: 500,
        name: 'password',
        select: false,
    })
    password: string;
}

Simple example of QueryBuilder:

// Instantiate QueryBuilder
const qb = new QueryBuilder(User, {
      page: 1,
      limit: 10,
      order: '^name',
      name_contains: 'Roy',
      username: 'roygrindelwald',
      createDateTime__gte: '2020-11-15T00:00:00.000Z',
      createDateTime__lt: '2020-11-25T00:00:00.000Z',
    }, 't1');

// Mapping filter field (Whitelist).
qb.fieldResolverMap['name_contains'] = 't1.name';
qb.fieldResolverMap['username'] = 't1.username';
qb.fieldResolverMap['createDateTime__gte'] = 't1.create_date_time';
qb.fieldResolverMap['createDateTime__lt'] = 't1.create_date_time';

// Have to be after mapping.
qb.applyFilterPagination();

// Select fields.
qb.selectRaw(
    ['t1.id', 'id'],
    ['t1.name', 'fullName'],
    ['t1.username', 'username'],
    ['t1.email', 'email'],
);

qb.andWhere(
    e => e.isDeleted,
    w => w.isFalse(),
);

// Execute the query
const data = await qb.exec();

It builds the following SQL query:

SELECT 
    t1.id AS id,
    t1.name AS fullName,
    t1.username AS username,
    t1.email AS email
FROM users t1 
WHERE 
    t1.name = 'Roy'
    AND t1.username = 'roygrindelwald'
    AND t1.create_date_time >= '2020-11-15T00:00:00.000Z'
    AND t1.create_date_time < '2020-11-25T00:00:00.000Z'
    AND t1.is_deleted = 'false'
ORDER BY t1.name ASC 
LIMIT 10

and returns you a list instance of User:

[
    User {
        id: "8cca9676-32a4-11eb-adc1-0242ac120002",
        fullName: "Roy Grindelwald",
        username: "roygrindelwald",
        email: "[e-mail]"
    }
]

Instantiate a QueryBuilder

import { QueryBuilder } from 'typeorm-query-builder-wrapper';
import { User } from './model/user.entity';

const qb = new QueryBuilder(User, {
      page: 1,
      limit: 10,
      order: '^name',
      name_contains: 'Roy',
      username: 'roygrindelwald',
      createDateTime__gte: '2020-11-15T00:00:00.000Z',
      createDateTime__lt: '2020-11-25T00:00:00.000Z',
    }, 't1');

There are 3 arguments of QueryBuilder constructor:

• Entity - used to apply entity to QueryBuilder.

• QueryObject - used to construct filter and pagination in QueryBuilder. Example:

  {
    page: 1,
    limit: 10,
    order: '^name',
    name_contains: 'Roy',
    username: 'roygrindelwald',
    createDateTime__gte: '2020-11-15T00:00:00.000Z',
    createDateTime__lt: '2020-11-25T00:00:00.000Z',
  }
  

  It is contains these options:

  Option	Default	Behaviour	Example
  page	1	Return entries for page page	page: 2
  limit	10	Return entries for page page paginated by size limit	limit: 25
  order	-	Order for fields: ^: Ascendant -: Descendant	order: '^foo,-name,^surname'

  And for the field of entity, it follows these rules:

  Lookup	Behaviour	Example
  matches	Return entries that match with value	foo: 'raul'
  contains	Return entries that contains value	foo__contains: 'ryo'
  startswith	Return entries that starts with value	foo__startswith: 'r'
  endswith	Return entries that ends with value	foo__endswith: 'dev'
  isnull	Return entries with null value	foo__isnull
  lt	Return entries with value less than or equal to provided	foo__lt: 18
  lte	Return entries with value less than provided	foo__lte: 18
  gt	Returns entries with value greater than provided	foo__gt: 18
  gte	Return entries with value greater than or equal to provided	foo__gte: 18
  in	Return entries that match with values in list	foo__in: 'admin,common'
  between	Return entries in range	foo__between: [1, 27]

• Alias - used to define alias for main table.

Adding Filter and Pagination

Most of the time when you develop an application, you need pagination functionality. This is used if you have pagination, page slider, or infinite scroll components in your application.

To do this, it is very simple, you only need define page and limit in QueryObject when you instantiate QueryBuilder, for example:

qb.applyFilterPagination();

it will add ORDER and LIMIT query into SQL raw query like this:

ORDER BY t1.name ASC 
LIMIT 10

If you have filters for your list, you have to mapping the fields to make a whitelist for the filter and also to map field of entity to field of QueryObject, for example:

qb.fieldResolverMap['name_contains'] = 't1.name';
qb.fieldResolverMap['username'] = 't1.username';
qb.fieldResolverMap['createDateTime__gte'] = 't1.create_date_time';
qb.fieldResolverMap['createDateTime__lt'] = 't1.create_date_time';

it will add WHERE query into SQL raw query like this:

WHERE 
    t1.name = 'Roy'
    AND t1.username = 'roygrindelwald'
    AND t1.create_date_time >= '2020-11-15T00:00:00.000Z'
    AND t1.create_date_time < '2020-11-25T00:00:00.000Z'

WARNING !! YOU HAVE TO MAPPING BEFORE APPLY TO FILTER PAGINATION

Then the complete of filter and pagination is:

qb.fieldResolverMap['name_contains'] = 't1.name';
qb.fieldResolverMap['username'] = 't1.username';
qb.fieldResolverMap['createDateTime__gte'] = 't1.create_date_time';
qb.fieldResolverMap['createDateTime__lt'] = 't1.create_date_time';

qb.applyFilterPagination();

And the complete result:

WHERE 
    t1.name = 'Roy'
    AND t1.username = 'roygrindelwald'
    AND t1.create_date_time >= '2020-11-15T00:00:00.000Z'
    AND t1.create_date_time < '2020-11-25T00:00:00.000Z'
ORDER BY t1.name ASC 
LIMIT 10

Adding SELECT expression

To select fields in table, you can use SELECT query. In QueryBuilder, you can use selectRaw method. It has one argument is a list of fields with its alias. Example:

qb.selectRaw(
    ['t1.id', 'id'],
    ['t1.name', 'name'],
    ['t1.username', 'username'],
    ['t1.email', 'email'],
);

Which will result in the following sql query:

SELECT
    t1.id AS id,
    t1.name AS fullName,
    t1.username AS username,
    t1.email AS email
FROM users t1

In this SQL query, users is the table name, and t1 is an alias we assign to this table.

Adding WHERE expression

Adding a WHERE expression is as easy as:

qb.andWhere(
    e => e.isDeleted,
    w => w.isFalse(),
);

Which will produce:

WHERE t1.is_deleted = 'false'

If WHERE expression it reach for the first time, then AND or OR will be removed to avoid error. So the result will not be like this:

WHERE AND t1.is_deleted = 'false'

You can add AND into an existing WHERE expression:

qb.andWhere(
    e => e.isDeleted,
    w => w.isFalse(),
);
qb.andWhere(
    e => e.name,
    w => w.equals('Roy Grindelwald'),
);

Which will produce the following SQL query:

WHERE 
    t1.is_deleted = 'false'
    AND t1.name = 'Roy Grindelwald'

You can add OR into an existing WHERE expression:

qb.andWhere(
    e => e.isDeleted,
    w => w.isFalse(),
);
qb.orWhere(
    e => e.username,
    w => w.equals('roygrindelwald'),
);

Which will produce the following SQL query:

WHERE 
    t1.is_deleted = 'false'
    OR t1.username = 'roygrindelwald'

They have 2 arguments.

First argument is called Property Selector, which return the selected property or field, example e.username, e is entity of main table which is User, user has username field, then e.username will give you an output of field username from User entity. it also can select from User relation, assume User has relation to Product with entity name product. NOTE! you have to mapping Product entity first in User entity class, or vice versa, depends on your need. So, if you want select one of field of Product entity, you can do like this:

qb.andWhere(
    e => e.product.productCode,
    w => w.equals('2ABC5'),
);

Second argument is called Operator, which return the operator used in WHERE expression like =, !=, IN, IS NULL, etc.

The list of oprator:

Operator	Behaviour	Example
equals	Return entries that match with value	w.equals('foo')
notEquals	Return entries that not match with value	w.notEquals('foo')
contains	Return entries that contains value	w.contains('foo')
beginsWith	Return entries that starts with value	w.beginsWith('foo')
endsWith	Return entries that ends with value	w.endsWith('foo')
isNotNull	Return entries with not null value	w.isNotNull()
isNull	Return entries with null value	w.isNull()
greaterThan	Returns entries with value greater than provided	w.greaterThan(100)
greaterThanOrEqual	Return entries with value greater than or equal to provided	w.greaterThanOrEqual(100)
lessThan	Return entries with value less than or equal to provided	w.lessThan(100)
lessThanOrEqual	Return entries with value less than provided	w.lessThanOrEqual(100)
in	Return entries that match with values in list	w.in(['foo', 'bar'])
notIn	Return entries that not match with values in list	w.notIn(['foo', 'bar'])

You can add a complex WHERE expression into an existing WHERE like Brackets in TypeORM

qb.andWhere(
    e => e.isDeleted,
    w => w.isFalse(),
);
qb.andWhereIsolated(q =>
    q.andWhere(
        e => e.product.productCode,
        w => w.isNotNull(),
    ).orWhere(
        e => e.name,
        w => w.equals('Roy'),
    ),
);

It has 1 argument that has type of QueryBuilder like Sub Query.

Which will produce the following SQL query:

WHERE 
    t1.is_deleted = 'false'
    AND (
        t2.product_code = '2ABC'
        OR t1.name = 'Roy Grindelwald'
    )

You can combine as many AND and OR expressions as you need.

Note: be careful with orWhere - if you use complex expressions with both AND and OR expressions, keep in mind that they are stacked without any pretences. Sometimes you'll need to create a where string instead, and avoid using orWhere.

Adding HAVING expression

Adding a HAVING expression is easy as:

qb.andHaving(
    e => e.isDeleted,
    w => w.isTrue(),
);

Which will produce following SQL query:

HAVING t1.is_deleted = 'true'

The rules of play is same as WHERE expression. But it is not support yet for complex having.

Adding DISTINCT ON expression

When using both distinct-on with an order-by expression, the distinct-on expression must match the leftmost order-by. The distinct-on expressions are interpreted using the same rules as order-by. Please note that, using distinct-on without an order-by expression means that the first row of each set is unpredictable.

Adding a DISTINCT ON expression is easy as:

qb.setDistinctOn(
    e => e.id,
    e => e.name,
);

Which will produce:

SELECT DISTINCT ON (t1.id, t1.name) ... FROM users t1 ORDER BY t1.id

Adding GROUP BY expression

Adding a GROUP BY expression is easy as:

qb.groupBy(
    e => e.id,
    e => e.name,
);

Which will produce the following SQL query:

GROUP BY t1.id, t1.name

If you use .groupBy more than once you'll override all previous GROUP BY expressions.

Joining relations

Let's say you have the following entities:

import {Entity, PrimaryGeneratedColumn, Column, OneToMany} from "typeorm";
import {Photo} from "./Photo";

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @OneToMany(type => Photo, photo => photo.user)
    photos: Photo[];
}

import {Entity, PrimaryGeneratedColumn, Column, ManyToOne} from "typeorm";
import {User} from "./User";

@Entity()
export class Photo {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    url: string;

    @ManyToOne(type => User, user => user.photos)
    user: User;
}

Now let's say you want to load user "Timber" with all of his photos:

const qb = new QueryBuilder(User, {}, 't1');
qb.selectRaw(
    ['t1.id', 'id'],
    ['t1.name', 'name'],
    ['t2.url', 'url'],
);
qb.leftJoin(
    e => e.photos,
    't2',
    j =>
        j.andWhere(
            e => e.isDeleted,
            w => w.isFalse(),
        ),
);
qb.andWhere(
      e => e.name,
      w => w.equals('Timber'),
);

This will generate following sql query:

SELECT 
    t1.id AS id
    t1.name AS name
    t2.url AS url
FROM users t1
LEFT JOIN photos t2 ON t2.user = t1.id AND t2.is_deleted = 'false'
WHERE t1.name = 'Timber'

You'll get the following result:

[
    {
        id: 1,
        name: "Timber",
        url: "me-with-chakram.jpg",
    },
    {
        id: 1,
        name: "Timber",
        url: "me-with-trees.jpg",
    }
]

Inner and left joins

If you want to use INNER JOIN instead of LEFT JOIN just use .innerJoin instead:

qb.innerJoin(
    e => e.photos,
    't2',
    j =>
        j.andWhere(
            e => e.isDeleted,
            w => w.isFalse(),
        ),
);

This will generate:

SELECT 
    t1.id AS id
    t1.name AS name
    t2.url AS url
FROM users t1
INNER JOIN photos t2 ON t2.user = t1.id AND t2.is_deleted = 'false'
WHERE t1.name = 'Timber'

The difference between LEFT JOIN and INNER JOIN is that INNER JOIN won't return a user if it does not have any photos. LEFT JOIN will return you the user even if it doesn't have photos. To learn more about different join types, refer to the SQL documentation.

Getting the generated query

Sometimes you may want to get the SQL query generated by QueryBuilder. To do so, use getQuery or getSql:

const sql = qb.selectRaw(
    ['t1.id', 'id'],
    ['t1.name', 'name'],
    ['t1.username', 'username'],
    ['t1.email', 'email'],
).andWhere(
    e => e.isDeleted,
    w => w.isFalse(),
).getQuery();

Getting raw results

To get raw data, you use exec. Examples:

const data = await qb.selectRaw(
    ['t1.id', 'id'],
    ['t1.name', 'name'],
    ['t1.username', 'username'],
    ['t1.email', 'email'],
).andWhere(
    e => e.isDeleted,
    w => w.isFalse(),
).exec();

Streaming result data

You can use stream which returns you a stream. Streaming returns you raw data and you must handle entity transformation manually:

const fileName = `User_${new Date().getTime()}.csv`;

// response is writeable output
response.setHeader(
    'Content-disposition',
    `attachment; filename=${fileName}`,
);
response.writeHead(200, { 'Content-Type': 'text/csv' });
response.flushHeaders();

// To set header of CSV
response.write(`User ID, Name, URL\n`);

const qb = new QueryBuilder(User, {}, 't1');

qb.selectRaw(
    ['t1.id', 'id'],
    ['t1.name', 'name'],
    ['t2.url', 'url'],
);
qb.leftJoin(
    e => e.photos,
    't2',
    j =>
        j.andWhere(
            e => e.isDeleted,
            w => w.isFalse(),
        ),
);
qb.andWhere(
      e => e.name,
      w => w.equals('Timber'),
);

await qb.stream(response, this.transformer);

The transformer looks like this:

transformer(doc) {
    const values = [
        doc.id,
        doc.name,
        doc.url,
    ];

    return `${values.join(',')} \n`;
}

It will stream raw results into csv format (for this example) in your user's browser.

Set locking

QueryBuilder supports both optimistic and pessimistic locking. To use pessimistic read locking use the following method:

qb.setLock("pessimistic_read");

To use pessimistic write locking use the following method:

qb.setLock("pessimistic_write");

To use dirty read locking use the following method:

qb.setLock("dirty_read");

To use optimistic locking use the following method:

qb.setLock("optimistic", version);

Optimistic locking works in conjunction with both @Version and @UpdatedDate decorators from TypeORM.

Using subqueries

You can easily create subqueries. Subqueries are supported in SELECT and FROM expressions. Next will be supported in WHERE and JOIN expressions. Example:

qb.selectSubQuery(User, 'user', subQuery =>
    subQuery.selectRaw(
        ['user.name', 'name'],
        ['user.username', 'username'],
    )
    .andWhere(
        'user.is_deleted',
        w => w.isFalse()
    )
);

example for FROM:

qb.from(User, 'user', subQuery => {
    return subQuery.selectRaw(
        ['user.name', 'name'],
        ['user.username', 'username'],
    )
    .andWhere(
        'user.is_deleted',
        w => w.isFalse()
    );
});

example for WHERE:

qb.from(User, 'user', subQuery => {
    return subQuery.selectRaw(
      ['t1.id', 'id'],
      ['t1.username', 'username'],
    ).andWhere(
      e => e.username,
      (w, subQuery) => {
        w.in(
          subQuery
            .selectRaw(['user.username', 'username'])
            .from(User, 'user')
            .andWhere(
              e => e.isDeleted,
              w => w.isFalse(),
            )
            .getQuery()
        )
      },
    ).orWhere(
      e => e.username,
      (w, subQuery) => {
        w.notIn(
          subQuery
            .selectRaw(['user.username', 'username'])
            .from(User, 'user')
            .andWhere(
              e => e.isDeleted,
              w => w.isFalse(),
            )
            .getQuery()
        )
      },
    );
});

example for JOIN:

qb.from(User, 'user', subQuery => {
    return subQuery.selectRaw(
      ['t1.id', 'id'],
      ['t1.name', 'name'],
      ['t2.branchName', 'branch'],
    )
    .innerJoinSubQuery(
      subQuery => {
        return subQuery
          .selectRaw(
            ['branch.user_id', 'userId'],
            ['branch.branch_name', 'branchName'],
          )
          .from(Branch, 'branch')
          .andWhere(
            e => e.isDeleted,
            w => w.isFalse(),
          )
      },
      't2',
      j => j.andWhere(
        't2.userId',
        w => w.equalsWithField('t1.id'),
      )
    );
});

TODOS:

• Support for MongoDB and other DB
• Support for GraphQL

Samples

Take a look at the samples in sample for examples of usage.

Contributing

Learn about contribution here and how to setup your development environment here.