PostgREST Like Query String Parsing

- 5 mins

PostgREST is a standalone web server that turns any PostgreSQL database into a RESTful API. It uses your existing schema to serve REST endpoints. I ultimately decided against using PostgREST, however the query string parsing is top notch.

A query string is the part of a URL after a question mark (?). Often this is data used to filter, limit, paginate or order an API endpoint. For now I’m only worrying about using the query string as part of the WHERE clause in a SQL statement.

PostgREST’s calls this horizontal filtering. If we have a table called animals that looks like this:

genus species
Neotoma lepida
Neotoma cinerea
Dipodomys ordii
Dipodomys panamintinus
Peromyscus maniculatus

PostgREST will create this endpoint for GET requests /species which will return a JSON representation of the animals table. By default it will return all the records. If we wanted to filter the table and only return Neotoma species we would use /species?genus=Neotoma. PostgREST’s query string parsing has a cool feature that allows us to include the operator with the criteria. With PostgREST the endpoint will look like this /species?genus=eq.Neotoma. For a full rundown of all the PostgREST operators check the table below.

abbreviation meaning
eq equals
gte greater than or equal
gt greater than
lte less than or equal
lt less than
neq not equal
like LIKE operator (use * in place of %)
ilike ILIKE operator (use * in place of %)
in one of a list of values e.g. :code:?a=in.1,2,3
is checking for exact equality (null,true,false)
@@ full-text search using to_tsquery
@> contains e.g. :code:?tags=@>.{example, new}
<@ contained in e.g. :code:?values=<@{1,2,3}
not negates another operator, see below

These methods are extremely flexible for API query strings. Unfortunately I haven’t found a method like this that I can use with and Node and Express API that I’m building. So I’ve begun thinking about how I can port these techniques to work with Node.

Node Query String Parsing

Below is a quick implementation of how I am thinking about using PostgREST’s query string parsing for filtering.

First create an operator map object. This will match the text operator with the actual operator. Then create an example req.query object. Then initialize an empty array.

const opMap = {
  eq: '=',
  gte: '>=',
  gt: '>',
  lte: '<=',
  lt: '<',
  neq: '!='
}

const query = {
  genus: 'eq.neotoma',
  species: 'eq.lepida'
}

let s = []

Now iterate over the query to create objects with column, operator, criteria properties.

if (Object.keys(query).length !== 0) {
  for (let x in query) {
    s.push({
      column: x,
      operator: opMap[query[x].split('.')[0]],
      criteria: query[x].split('.')[1]
    })
  }
}

// result
s = [
  { column: "genus", operator: "=", criteria: "Neotoma" },
  { column: "species", operator: "=", criteria: "lepida" }
]

Now create an array of WHERE clause predicates.

let sStrings = []

for (let i = 0; i < s.length; i++) {
  sStrings.push(`${s[i].column} ${s[i].operator} ${s[i].criteria}`)
}

// result
sStrings = [
  "genus = Neotoma",
  "species = lepida"
]

Finally, reduce the array into the final WHERE clause predicate.

const where = sStrings.reduce((prev, curr) => prev + ' AND ' + curr)

// result
where = "genus = Neotoma AND species = lepida"

This final WHERE clause is can be sent to the database to return Neotoma lepida from the database.

Hopefully I can continue to work on this and make a fully functional query string parser.

rss facebook twitter github youtube mail spotify instagram linkedin google google-plus pinterest medium vimeo stackoverflow reddit quora