Composable SQL In JavaScript

Using SQL queries in conjunction with Functors to create reusable querying systems ⚡️

tl;dr: This article covers how to build a simple, composable SQL query system in JS. The reference implementation is in this GitHub repo.

It has been a while since I have read Brian Lonsdorf’s Prof. Frisby’s Mostly Adequate Guide to Functional Programming (in JS). Most of its parts were intuitive and I felt I could drop them in my code ASAP — composing functions, currying functions, etc.

One thing I haven’t really got to use in a real app is Functors (Containers), which Chapter 8 in the book covers. In general? A functor/container is a class that provides the following interface:

Functor / Container basic implementation with some flowtype annotations.

I have used Facebook’s Flow to annotate the types. So basically, it is a class with a map function that receives a function and returns a new instance of the class after (maybe) running the function it got as a parameter. The book reveals the Maybe functor, which along with Array, is one of the most commonly used and talked Functors:

Never worry about nulls again!

The Array#map function executes the provided function to each element in the array, the Maybe#map executes the function only if the value is not null or undefined. This means that the Maybe functor helps you to stop doing null checks, which is pretty awesome. However, all this mapping functions just to pass data around.. it wasn’t my type of thing. So, I haven’t got to use it ¯\_(ツ)_/¯

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:

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

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:

Reference Implementation

So, I’m glad to introduce cuery, the composable query class for PostgreSQL. I hope it will help you write simple SQL queries to simplify your development environment and make your apps more predictable.

Yet cuery itself isn’t the purpose of this article: it’s really opinionated around my own usage. Each app can be implemented with a different query data: It might contain a name so you can profile queries by name, it might miss the parameters array if you use named parameters. Hell, you can even use plain JSONs and functions instead of using a class. It’s up to you how you want to get it going.

Any suggestions? I’d like to hear your feedback!

Written by

Frontend at Wix. I like weird stuff and rock music

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store