Easily transform an url query into TypeORM query

TypeORM Server Query Builder

This library allows you to transfrom automatically url query into TypeORM findOptions queries.

Installation

npm install typeorm-server-query-builder

How it works?

You can use the frontend query builder to go faster without having to worry too much about the syntax.

Usage

Use QueryBuilder export from package and pass your req.query as an argument:

import { QueryBuilder } from 'typeorm-server-query-builder';
 
const builder = new QueryBuilder(req.query);
const builtQuery = builder.build();
// Now your query is built, pass it to your TypeORM repository
const results = await fooRepository.find(builtQuery);

Given the following url query string:

foo/?name__contains=foo&role__in=admin,common&age__gte=18&page=3&limit=10

It will be transformed into:

{
  where: {
    foo: Like('%foo%'),
    role: In(['admin', 'common']),
    age: MoreThanOrEqual(18)
  },
  skip: 20,
  take: 10
}

Different ways of retrieve data

GET, POST method by url query string

GET foo/?name__contains=foo&role__in=admin,common&age__gte=18&page=3&limit=10

POST foo/?name__contains=foo&role__in=admin,common&age__gte=18&page=3&limit=10

app.get('/foo', (req, res) => {
  const queryBuilder = new QueryBuilder(req.query); // => Parsed into req.query
  const built = queryBuilder.build();
})

POST method by body

POST foo/, body: {
  "name__contains": "foo",
  "role__in": "admin,common",
  "age__gte": 18,
  "page": 3,
  "limit": 10
}

app.post('/foo', (req, res) => {
  const queryBuilder = new QueryBuilder(req.body); // => Parsed into req.body
  const built = queryBuilder.build();
})

Available Lookups

Lookup	Behaviour	Example
(none)	Return entries that match with value	foo=raul
contains	Return entries that contains value	foo__contains=lopez
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

Notice

You can use negative logic prefixing lookup with __not. Example: foo__not__contains=value

Querying a column from an embedded entity. Example: user.name=value

OR condition union

// ?$or=name:juste|age__gte:15&$or=user.role:admin
{
  where: [
    { name: 'juste', age: MoreThanOrEqual('15') },
    { user: { role: 'admin' } }
  ]
}
 
// ?city=Dahomey&$or=name:juste|age__gte:15&$or=user.role:admin
{
  where: [
    { name: 'juste', city: 'Dahomey', age: MoreThanOrEqual('15') },
    { user: { role: 'admin' }, city: 'Dahomey' }
  ]
}

Options

Option	Default	Behaviour	Example
pagination	true	If true, paginate results. If false, disable pagination	pagination=false
page	1	Return entries for page page	page=2
limit	25	Return entries for page page paginated by size limit	limit=15
order	-	Order for fields: ^: Ascendant -: Descendant	order=^foo,-name,^surname
join	-	Set relations	join=posts,comments
select	-	Set fields selection	select=name,phoneNumber

Others methods

Remove precautionary fields from the query before building

removeField(field: string): QueryBuilder