README

Easily transform an url query into TypeORM FindOptions

TOFO - TypeORM FindOptions Builder

TOFO stand for TypeORM FindOptions. this library allows you to transform automatically url query into TypeORM FindOptions queries.

Installation

npm install tofo

How it works?

Usage

Quick Start
Using Allowed Fields
Using POST method to retrive data

Quick Start

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

Given the following url query string:

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

Then build the query into FindOptions

import { FindOptionBuilder } from 'tofo';

const builder = new FindOptionBuilder(req.query);
const builtQuery = builder.build();

It will be transformed into:

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

Now your query is builted, pass it to your TypeORM repository.

const results = await fooRepository.find(builtQuery);

For another available syntax can be found in here:

Allowed Fields

By default, tofo accepting all fields passed in req.query. If you need to filtering for only some fields you can use setAllowedFields() function.

// Let's say this query got from req.query
const query = { name: "foo", status: "active", location: "Sukowati", limit: 5 };

const builder = new FindOptionBuilder(query);
// Set allowed fields
builder.setAllowedFields(['limit']);
builder.setAllowedFields(['name', 'status']);

// Build query
const builtQuery = builder.build();

It will be transformed into:

{
  skip: 0,
  take: 5,
  where: {
    name: 'foo',
    status: 'active'
  },
}

WARNING: you also need to set allowed builtin query options keys if needed, otherwise the keys will be removed before build.

Using POST method to retrieve data

Instead using req.query from GET method, you also can send data using POST method and retrieve the data from req.body.

curl --location --request POST 'http://localhost/api/foo' \
--header 'Content-Type: application/json' \
--data-raw '{
    "name": "foo",
    "status": "active"
}'

And handle data like

app.post('/api/foo', (req, res) => {
  const builder = new FindOptionBuilder(req.body); // => Process data from req.body
  const builtQuery = builder.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' }
  ]
}

Query Option Keys

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`

NOTE: If you using AllowedFields you also need to include these option keys also.

Available Methods

Remove precautionary fields from the query before building

removeField(field: string): FindOptionBuilder

Set Allowed Fields from the query before building

setAllowedFields(['allowed_field1', 'allowed_field2']): FindOptionBuilder

Others

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

Usage no npm install needed!