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.