Using Higher-Order Functions to Build Queries in Knex.js

My team has been using Knex.js to build database queries for our latest project. To create increasingly complex queries, we developed a pattern to generate SQL queries using higher-order functions. I’d like to share how we leverage these higher-order functions to make our query builders modular, concise, and very easy to understand.

A Simple Example

For this example, I have a simple data model consisting of books and authors. First, let’s build a higher-order function that helps us find a book by its title. Here’s the general pattern we’ll follow:

1. Define our higher-order functions.
2. Create a simple base query.
3. Create our query builders using the higher-order functions.
4. Apply the query builders to the base query.


// Step 1
const filterByTitle = (title) => {
  return (query) => query.where('title', title);
};

// Step 2
const baseQuery = knex('books');

// Step 3
const applyTitleFilter = filterByTitle('Coding With Javascript');

// Step 4
const completedQuery = applyTitleFilter(baseQuery);

Here’s the generated SQL query:


SELECT *
FROM "books"
WHERE "title" = 'Coding With Javascript'

The filterByTitle() function is a higher-order function which returns a query builder. The query builder is also a function that takes a single argument (the query) and returns it with some modifications.

Now, this is a very basic example that doesn’t need higher-order queries to make it clearer, but it demonstrates the approach we’ll be using for our other examples.

Creating Specific Builders Using Generic Function

Next, let’s try to find books that were published on or after the year 2015. To do this, we can implement a generic date-filter function:


const filterAfterDate = (field, date) => {
  return (query) => {
    return query.where(field, '>', date);
  };
};

This function is fairly flexible. It allows us to specify a field and a value to compare. With this function, we can construct a query builder for our particular problem:


const baseQuery = knex('books');

const publishedAfter2015 = filterAfterDate('publishDate', '2014-12-31');

const completedQuery = publishedAfter2015(baseQuery);

Result SQL:


SELECT *
FROM "books"
WHERE "publishDate" > '2014-12-31'

The key part of this example is the name of our query builder. By naming it “publishedAfter2015,” we can communicate our intended purpose for the filter. Other developers can see the purpose of this query by looking at the last line of code. This becomes extremely helpful once we start combining multiple query builders.

Combining Multiple Query Builders

Let’s add on to our previous example, filtering by author name as well. We’ll need to create another higher-order function for filtering by author:


const filterByAuthor = (name) => {
  return (query) => {
    return query
      .join('authors', 'authors.id', 'books.authorId')
      .where('authors.name', name);
  };
};

Using a function like this has the extra benefit of hiding some implementation details. By using this function to build queries instead of the raw Knex functions, we don’t have to waste any brain power figuring out join clauses.

Now, we can create two query builders using both higher-order functions:


const baseQuery = knex('books');

const booksWrittenByAndy = filterByAuthor('Andy');
const publishedAfter2015 = filterAfterDate('publishDate', '2014-12-31');

const completedQuery = booksWrittenByAndy(publishedAfter2015(baseQuery));

Result SQL (in PostgreSQL):


SELECT *
FROM "books"
INNER JOIN "authors" ON "authors"."id" = "books"."authorId"
WHERE "publishDate" > '2014-12-31' AND "authors"."name" = 'Andy'

The nested function calls makes it easy to read our intent. We are looking for books written by Andy and published after 2015. If we were to just use Knex.js without higher-order functions, it would look like this:


knex('books')
.join('authors', 'authors.id', 'books.authorId')
.where('authors.name', 'Andy')
.andWhere('publishDate' > '2014-12-31');

While it’s not all that hard to understand the raw Knex function calls, wrapping these same calls in higher-order functions cleans up the code quite a bit and allows for easy reading.

Next time you build queries with Knex.js, try applying higher-order functions. By using this pattern, you can separate concerns while generating queries and clarify the intent of your queries when you come back to maintain them.