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:
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.
|gte||greater than or equal|
|lte||less than or 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: |
|is||checking for exact equality (null,true,false)|
|@@||full-text search using to_tsquery|
|@>||contains e.g. :code: |
|<@||contained in e.g. :code: |
|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.
Now iterate over the query to create objects with column, operator, criteria properties.
Now create an array of WHERE clause predicates.
Finally, reduce the array into the final WHERE clause predicate.
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.