Filters from query strings
A list endpoint usually takes its filters from the URL:
/users?status=active&age[gte]=18. This guide turns that into a
Condition[] you can hand to your driver, with only the columns you allow.
The filter shape
where() takes a plain object. A bare value means eq. An object of
operators applies each one to that column.
import { where } from '@rtorcato/db-common'
where({ status: 'active', age: { gte: 18, lt: 65 } })
// → [{ col: 'status', op: 'eq', val: 'active' },
// { col: 'age', op: 'gte', val: 18 },
// { col: 'age', op: 'lt', val: 65 }]
The operators are eq, ne, gt, gte, lt, lte, in, notIn, like,
ilike, isNull, isNotNull and between. in and notIn take an array,
between takes a [min, max] pair, and isNull / isNotNull take a boolean
(false inverts).
Parse the query string
That object is the shape bracket-style query strings already parse to. With
qs, which Express 4 uses for req.query:
import qs from 'qs'
qs.parse('status=active&age[gte]=18')
// → { status: 'active', age: { gte: '18' } }
URLSearchParams doesn't nest keys, so with it (or Express 5's default parser)
build the object yourself, one known column at a time.
Two things to know about parsed values:
- They are strings.
'18', not18. Postgres casts a bound'18'to the column's type, so comparisons usually work as-is. Coerce first if your driver or a type check needs a number. - Arrays need repeat syntax.
status[in]=a,bparses to the string'a,b'. Sendstatus[in][]=a&status[in][]=bfor an array, or split the string yourself.
Allowlist, then map
Column names can't be bound as parameters, so a column a client invents ends
up in your SQL. Run the conditions through allow() before you map them.
import { allow, where } from '@rtorcato/db-common'
import { toDrizzleWhere } from '@rtorcato/db-drizzle'
import qs from 'qs'
const cols = { status: users.status, age: users.age }
const conds = allow(where(qs.parse(url.search, { ignoreQueryPrefix: true })), ['status', 'age'])
const rows = await db.select().from(users).where(toDrizzleWhere(conds, cols))
allow() drops anything outside the list without throwing, so ?evil=1 just
filters nothing. See Allowlisting fields with allow()
for more.
Sorting from the same query string
orderBy() reads ?sort=name,-age (a leading - means descending) and returns
{ col, dir } items that allow() also accepts:
import { orderBy } from '@rtorcato/db-common'
import { toDrizzleOrderBy } from '@rtorcato/db-drizzle'
const sorts = allow(orderBy(params.get('sort') ?? ''), ['name', 'age'])
const rows = await db
.select()
.from(users)
.where(toDrizzleWhere(conds, cols))
.orderBy(...toDrizzleOrderBy(sorts, { ...cols, name: users.name }))
Things to watch
- Operators are open.
allow()checks columns, not operators. If a column shouldn't acceptlike(a leading%can force a full scan) orinwith a huge list, checkcond.opandcond.valbefore mapping. ilikeis Postgres-only. Uselikeon MySQL and SQLite.