So.. SQL Queries.

Recently, I started to build a simple Medium clone in Node.js and PostgreSQL, just for fun. After I implemented the basic Medium clone, I wanted to add a “draft post” feature:

If a post is marked as “Draft”, only its creator can see it exists.

Yeah, it isn’t the smartest authorization layer, but that was enough for the MVP.

So I started to add WHERE is_published IS true OR user_id = $1 to almost every SQL query. After I got it to work, I have noticed I was repeating LOTS of code: pagination and authorization were all the same across most of my queries. yikes.

So I thought of the following solutions:

This code works, and the SQL is readable so it’s totally cool to keep it this way.

I can move code from my DB layer into my Node.js codebase. This will hurt pagination performance and force me to use Cursors for simple queries.

Find a better way of making SQL DRY: I don’t want any redundant duplications of code in my JavaScript, so why should I allow it in my SQL?

The Solution: A Composable SQL Query Functor

Kinda the best of both worlds

The idea itself isn’t that hardcore. A paginated query can be expressed as a paginated sub-query using subselect. A filtered post list can filter a sub query. So let’s think how to get it to work.

We can just wrap our Queries with a map function to alter the query into a different one. By making the query modifiers composable we can benefit from lots of boilerplate and keep our SQL DRY.

A reference for a Query functor — help your SQL codebase be DRY.

As you can see, it’s just a simple Query class that has a value which contains the query string and array of parameters, that just gonna help us give names to the query parameters, that represented as numbers in node-pg. A small demo for a simple query from the “posts” table can look like:

This simple query doesn’t give us any good perspective about how great composing SQL can feel like. This is just a simple SELECT query: getting the raw data out of the database. So, if we execute this query and provide our userId, we will receive all of our posts. Not filtered and not paginated.

As an app that can hold a lot of data, we might want to paginate this query. What if we could implement a simple function that take the query data and returns a new query data of paginated query?

Pagination, when used as a mapping function, can be pretty easy:

a pagination map function

The new query should be a paginate query that subselects the query we had before (using LIMIT and OFFSET).

The parameters we send to the query are the same parameters we had, only we introduce “limit” and “offset”.

To use this function, we only need to map a Query instance:

voilà! we have a paginated query!

And it doesn’t stop there

We can use the query functor in many ways: