Composable SQL In JavaScript

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:

type ContainerMapFunction = (containerData: ContainerData) => ContainerData;

class Container {
  value: ContainerData;

  static of(value: ContainerData) {
    return new Container(value);
  }

  map(f: ContainerMapFunction): Container {
    // ... maybe invoke f ...
    // always return Container
  }
}

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:

class Maybe {
  value: ?any;

  static of(value: ?any) {
    return new Maybe(value);
  }

  isNothing() {
    return this.value == null;
  }

  map(f: Function): Maybe {
    return this.isNothing() ? new Maybe(null) : new Maybe(f(this.value));
  }
}

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 ¯_(ツ)_/¯

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 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.

export type QueryData = {
  query: string,
  params: Array<string>,
};

export default class Query {
  static of(queryData: QueryData) {
    return new Query(queryData);
  }

  queryData: QueryData;

  constructor(queryData: QueryData) {
    this.queryData = queryData;
  }

  map(f: (queryData: QueryData) => QueryData) {
    return Query.of(f(this.queryData));
  }
}

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:

const getPostsForUser = Query.of({
  query: `
    SELECT users.name as user_name, users.username as user_username, posts.*
    FROM posts, users WHERE posts.user_id = users.id
    AND users.id = $1
    ORDER BY created_at DESC
  `,
  params: ["userId"],
});

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:

import type { QueryData } from "./Query";

export default function paginate(queryData: QueryData): QueryData {
  const limit = queryData.params.length + 1;
  const offset = limit + 1;
  const randomName = `paginated_${Math.floor(Math.random() * 100000)}`;

  return {
    ...queryData,
    query: `SELECT "${randomName}".* FROM (${queryData.query}) "${randomName}" LIMIT $${limit} OFFSET $${offset}`,
    params: [...queryData.params, "limit", "offset"],
  };
}

• 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:

const getPostsForUserPaginated = getPostsForUser.map(paginate);

We can use the query functor in many ways:

• I have mentioned I use named parameters, that work great due to the execute function I have implemented. I also integrated a simple “undefined check” that warns when a query got undefined as a parameter.
• Pagination, data filtering, aggregation (COUNT, etc).
• We can use functions to map our queries to return only the columns we need (so we won’t expose the password hashes 🔑).
• All the functions passed to map are unit-testable.

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!