NestJS query string filters for Drizzle

To turn NestJS query string filters into Drizzle queries, call service.parseQuery(query) in the controller. It reads a query such as ?population[gte]=1000000&region[name]=Asia&sortBy=-name&page=2, coerces each value by its Drizzle column type, and returns the filters, pagination and options that findAll, findByCursor and count take. Unknown columns, operators and relations are a 400 before any SQL runs.

@Get()
findAll(@Query() query: Record<string, unknown>) {
  const q = this.countries.parseQuery(query);
  return q.isCursor
    ? this.countries.findByCursor(q.filters, q.pagination, q.options)
    : this.countries.findAll(q.filters, q.pagination, q.options);
}

What does the query string look like?Copy Anchor Link

Each filter is a key: a bare value for an exact match, or brackets for an operator. A relation name filters by a related column.

GET /countries?status=active                 exact match
GET /countries?population[gte]=1000000       operator
GET /countries?region_id[in]=1,3             list operand, comma separated
GET /countries?code=IN&code=NP               repeated key: IN (...)
GET /countries?region[name]=Asia             belongs-to column (region.name=Asia works too)
GET /countries?states[name]=Kerala           has-many or many-to-many column
GET /cities?state[country][code]=IN          a path through relations (state.country.code=IN too)
GET /countries?states[none][name]=Kerala     some / none / every on a collection
GET /countries?states[some]=                 has at least one state
GET /countries?or[0][code]=IN&or[1][code]=NP OR group
GET /countries?not[region][name]=Asia        NOT group
GET /countries?sortBy=region.name,-name&page=2&limit=20
GET /countries?fields=id,name,region.name&with=region
GET /countries?search=nep
GET /countries?cursor=                       first keyset page
GET /countries?withCount=false               skip the COUNT(*)
GET /countries?counts=states,tags            _count on each row

A relation filter can go on through the related table's own relations, and a collection takes some, none or every as the next key. filterableColumns is checked against the full dotted path (state.country.code). See filtering by relation.

How do operators and lists work in brackets?Copy Anchor Link

All 22 filter operators work in brackets, and each one expects a particular shape of value:

Use a repeated key (name[in]=a&name[in]=b) or name[]=a&name[]=b when a value contains a comma. between needs exactly two values, and a single-value operator given twice is a 400.

The parser reads both Express 5's default flat keys (age[gte]) and the nested objects of the qs parser (app.set('query parser', 'extended')), so it works whichever one your app uses.

Which parameters are not filters?Copy Anchor Link

These keys are read by the parser itself. Every other key is a filter.

The flags (count, withCount, withDeleted, onlyDeleted) accept true, 1 or an empty value for true, and false or 0 for false.

How are values coerced?Copy Anchor Link

By the Drizzle column, so the service gets the type it expects:

What is a 400?Copy Anchor Link

The same things the service rejects, caught before any SQL runs:

• an unknown column, relation, operator, sort column, fields entry, with path or counts name
• a value the column cannot hold
• a malformed key such as a[b, or two keys that conflict (age=1&age[gt]=2)
• a key given twice where one value is expected (page=1&page=2)
• anything outside an allowlist

Keys containing __proto__, constructor or prototype are refused outright.

Which options does parseQuery take?Copy Anchor Link

Pass them as the second argument:

const q = this.tags.parseQuery(query, {
  allowTrash: true, // accept withDeleted / onlyDeleted
  searchColumns: ["name"], // what ?search= looks in
  ignore: ["format"], // a parameter the controller reads itself
});

parseQuery copies the entity's relations, primary key and five read allowlists into these options, then spreads yours on top, so an option you pass replaces the entity value for that call. parseCrudQuery(query, table, options) takes the same options and fills in nothing.

What does it return?Copy Anchor Link

A ParsedCrudQuery, with the three findAll arguments ready to pass and the parts split out for your own use:

interface ParsedCrudQuery {
  filters: CrudFilter;
  pagination: {
    page?: number;
    limit?: number;
    sortBy?: SortColumn[];
    cursor?: string | null;
    count?: boolean;
    withCount?: boolean;
  };
  sort: SortColumn[];
  select?: string[];
  relations?: string[];
  counts?: string[];
  search?: SqlSearchOptions;
  options: SqlOperationOptions; // select, relations, counts, search, withDeleted, onlyDeleted
  isCursor: boolean; // true when cursor or after was sent
}

Type the service's fourth generic as CrudFilter so the parsed filters fit without a cast:

export class CountriesService extends SqlBaseCrudService<
  Country,
  CreateCountryDto,
  UpdateCountryDto,
  CrudFilter
> {}

Common questionsCopy Anchor Link

Next stepsCopy Anchor Link