A declarative way to cache PostgreSQL queries using Node.js
Tags: postgres, javascript, node
Published: 2019-11-02 18:12:08

---
title: A declarative way to cache PostgreSQL queries using Node.js
published: true
description: Implementing PostgreSQL cache using interceptors.
tags: postgres, javascript, node
cover_image: https://thepracticaldev.s3.amazonaws.com/i/pc9bly14e6t8nhdo7kul.png
---

There can be many reasons to make you want to cache SQL queries. Some of them are valid, e.g. reducing the number of round-trips (esp. when dealing with high-latency). Others might be micro-optimizations that are just not worth it. Regardless of your reasons for wanting to cache SQL queries, implementing them can be cumbersome.

## Subject

I am going to use [Slonik](https://github.com/gajus/slonik) (PostgreSQL client for Node.js) and [node-cache](https://www.npmjs.com/package/node-cache) to demonstrate the usual way to implement cache and a declarative way to add cache to your existing codebase.

Let's assume a simple query-method to get `country` PK value using another unique identifier:

```js
const getCountryIdByCodeAlpha2 = (
  connection: DatabaseConnectionType,
  countryCode: string
): Promise<DatabaseRecordIdType> => {
  return connection.maybeOneFirst(sql`
    SELECT id
    FROM country
    WHERE code_alpha_2 = ${countryCode}
  `);
};

```

This type of query is particularly common when ingesting data from external inputs (e.g. user submitted input or data that has been collected using scraping).

## Measuring the problem

In the particular case that prompted me to explore caching, this query was called 7k+ times/ minute. Aside from this query, there were a dozen of other similar queries that collectively were executed well over 50k+ times/ minute. None of them affect my database server performance (PostgreSQL is already good at caching), but they:

* generate unnecessary logs
* increase the overall time needed to complete the task

The time it takes for PostgreSQL to _execute_ such a query is minuscule, e.g.

```sql
EXPLAIN ANALYZE
SELECT id
FROM country
WHERE code_alpha_2 = 'gb';

Index Only Scan using country_code_alpha_2_id_idx on country  (cost=0.14..1.16 rows=1 width=4) (actual time=0.425..0.426 rows=1 loops=1)
  Index Cond: (code_alpha_2 = 'gb'::citext)
  Heap Fetches: 0
Planning Time: 0.069 ms
Execution Time: 0.439 ms

```

However, we have to also add the network time. In my case, the latency between the worker agent and the database is ~3ms.

```
ping ***.aivencloud.com                                                     17:31:54
PING ***.aivencloud.com (34.90.***.***): 56 data bytes
64 bytes from 34.90.***.***: icmp_seq=0 ttl=53 time=3.166 ms
64 bytes from 34.90.***.***: icmp_seq=1 ttl=53 time=2.627 ms
64 bytes from 34.90.***.***: icmp_seq=2 ttl=53 time=2.873 ms

```

That means that executing a query and getting the result takes at least 7.5ms (0.5ms query execution time + 2 trips). Put it another way, every 60 seconds, we waste ~350 seconds of computing time (spread across many servers). Overtime, this adds up to a lot (70 hours over month).

## Implementing cache

All you need to implement cache is some storage service with a mechanism to limit how long and how many items can be stored. [`node-cache`](https://www.npmjs.com/package/node-cache) is such an abstraction for synchronously storing/ retrieving objects in memory. Using `node-cache`, you use `set` method to store cache and `get` method to retrieve cache; `node-cache` handles invalidation and storage limits behind the scenes. This is how  `getCountryIdByCodeAlpha2` would look like if it used `node-cache`:

```js
const cache = new NodeCache({
  checkperiod: 60,
  maxKeys: 10000,
  stdTTL: 60,
  useClones: false,
});

const getCountryIdByCodeAlpha2 = async (
  cache: NodeCache,
  connection: DatabaseConnectionType,
  countryCode: string
): Promise<DatabaseRecordIdType> => {
  const maybeCountryId = cache.get(countryCode);

  if (maybeCountryId) {
    return maybeCountryId;
  }

  const maybeResult = await connection.maybeOneFirst(sql`
    SELECT id
    FROM country
    WHERE code_alpha_2 = ${countryCode}
  `);

  cache.set(maybeResult, maybeResult);

  return maybeResult;
};

```

However, this way of adding cache has a few disadvantages:

* It introduces a lot of boilerplate around every query.
* It introduces an additional dependency (`NodeCache` instance) that needs to be passed around throughout your codebase along with the database connection handle.

If you had to go this way, 9/10 I would say it is not worth it. Luckily, there is a better way.

## Declarative cache

Slonik has a concept of [interceptors](https://github.com/gajus/slonik#slonik-interceptors) (middlewares) that can be used to capture and modify SQL request and response. This makes them perfect for implementing cache. Such interceptor already exists: [`slonik-interceptor-query-cache`](https://github.com/gajus/slonik-interceptor-query-cache).

`slonik-interceptor-query-cache` uses SQL comments to recognize which queries should be cached and for how long. Specifically, it searches for comment `@cache-ttl`. `@cache-ttl` comment indicates for how long the query should be cached. Queries without `@cache-ttl` are not cached at all, i.e. In order to cache the result of the earlier query for 60 seconds, the only change we need to make is to add a `@cache-ttl` comment to our query:

```js
const getCountryIdByCodeAlpha2 = (
  connection: DatabaseConnectionType,
  countryCode: string
): Promise<DatabaseRecordIdType> => {
  return connection.maybeOneFirst(sql`
    -- @cache-ttl 60
    SELECT id
    FROM country
    WHERE code_alpha_2 = ${countryCode}
  `);
};

```

Now this query will be cache result for each unique `countryCode` for 60 seconds.

`slonik-interceptor-query-cache` does not implement storage, though. You can use [`node-cache`](https://www.npmjs.com/package/node-cache), [`lru-cache`](https://github.com/isaacs/node-lru-cache), Redis, or any other storage engine. To use them, you simply need to abstract their interface using `get` and `set` methods, and provide them to `slonik-interceptor-query-cache`. Continuing with the `node-cache` example, this is how you would initiate Slonik with the query cache interceptor using `node-cache` as a storage engine:

```js
import NodeCache from 'node-cache';
import {
  createPool
} from 'slonik';
import {
  createQueryCacheInterceptor
} from 'slonik-interceptor-query-cache';

const nodeCache = new NodeCache({
  checkperiod: 60,
  stdTTL: 60,
  useClones: false,
});

const hashQuery = (query: QueryType): string => {
  return JSON.stringify(query);
};

const pool = createPool('postgres://', {
  interceptors: [
    createQueryCacheInterceptor({
      storage: {
        get: (query) => {
          return cache.get(hashQuery(query)) || null;
        },
        set: (query, cacheAttributes, queryResult) => {
          cache.set(hashQuery(query), queryResult, cacheAttributes.ttl);
        },
      },
    }),
  ]
});

```

and that is it: with minimal code changes, now you can cache any query just by adding a comment to SQL. Among other benefits, this:

* allows you to quickly test the impact of caching a specific query
* allows you to quickly enable/ disable query caching (by simply adding/ removing the query cache interceptor)
* does not affect how you write test cases
* does not add boilerplate code to every query
* does not require to passthrough an additional dependency to every query invocation