Solution to multiple connections with knex while using Next.js

The Issue

Using next.js has it's own advantages and not going to go through them in this
post but one major blockage while building
TillWhen was the number of Database
connection each api request was creating. Initially I thought it was just
because of the constant restarts of the server I was making that lead to the 30+
connections but I remember setting PG to disregard idle connections after a
minute.

Anyway, soon It was obvious that the knex connections I created weren't getting
destroyed and there was a new connection every time I made a request.

Now even though thins could be easily solved for mysql using
serverless-mysql which manages the connections based of serverless
environments, and I could even use the pg version of the above,
serverless-pg but, we already had the whole apps built with knex.js and I
didn't wanna rewrite every query again so had to find a better way.

I had 2 solutions at this point.

Solution #1 - Memoize

Now, I assume that you have one file that you maintain the knex instance in, if
not, then you are going to have to do a lot of refactoring.

Let's get to creating a knex instance but with a simple variable that will store
the connection instance so on the next request, the same is sent back to the
handler using the db instance.

utils/db-injector.js

const dbConfig = require("knexfile");
const knex = require("knex");

let cachedConnection;

export const getDatabaseConnector = () => {
  if (cachedConnection) {
    console.log("Cached Connection");
    return cachedConnection;
  }
  const configByEnvironment = dbConfig[process.env.NODE_ENV || "development"];

  if (!configByEnvironment) {
    throw new Error(
      `Failed to get knex configuration for env:${process.env.NODE_ENV}`
    );
  }
  console.log("New Connection");
  const connection = knex(configByEnvironment);
  cachedConnection = connection;
  return connection;
};

We now have a variable cachedConnection that either has an instance, if not, a
new one is created and is referred to by it. Now let's see how you would use
this in the request handlers.

controllers/user.js

const db = require("utils/db-injector");

controller.fetchUser = async (req, res) => {
  try {
    const data = db()("users").where();
    return res.status(200).send(data[0]);
  } catch (err) {
    console.error(err);
    throw err;
  }
};

At this point you are almost always getting a cached connection, I say almost
always because the actual utils/db-injector.js might get reinit by next.js and
you will have a connection that still hanging out with knex for longer than
intented. This isn't much of an issue but if you are like me who doesn't want
this to exist either, let's get to the second solution.

Solution #2 - Destroy!

Yeah, we mercilessly destroy the connection with the database after each request
to make sure that there's always only one connection per request, the peak of
optimization! Which should've been handled by knex but let's not blame knex!

Anyway, the 2nd solution required a simple higher-order function that would

we start by modifying the db-injector to create a new instance everytime
instead of caching because the cached instance won't exist anymore and will give
you a unusable knex connection or no connection at all. Let's do that first.

utils/db-injector.js

const dbConfig = require("knexfile");
const knex = require("knex");

let connection;

export const getDatabaseConnector = () => {
  return () => {
    const configByEnvironment = dbConfig[process.env.NODE_ENV || "development"];
    if (!configByEnvironment) {
      throw new Error(
        `Failed to get knex configuration for env:${process.env.NODE_ENV}`
      );
    }
    connection = knex(configByEnvironment);
    return connection;
  };
};

We now have a new connection on every request, let's write the higher-order
function so it can destroy the connection and let the DB of the connection
misery.

The higher-order function as said, is going to be very simple, it's just taking
in the handler , waiting for it to complete the request and then we destroy it.

connection-handler.js

import { getDatabaseConnector } from "utils/db-injector";
const connector = getDatabaseConnector();

export default (...args) => {
  return (fn) => async (req, res) => {
    req.db = connector();
    await fn(req, res);
    await req.db.destroy();
  };
};

Why do I pass in req.db?, reason being that if the handler keeps importing the
db , the higher-order function has no way to destroy the exact instance, and
hence we init the db instance and destroy the instance here. It's a simple form
of self-cleaning.

pages/api/user/index.js

import connectionHandler from "connection-handler";

const handler = async (req, res) => {
  try {
    if (req.method === "GET") {
      const { currentUser } = req;
      const data = await req
        .db("users")
        .leftJoin("profiles as profile", "users.id", "profile.user_id")
        .where("users.id", currentUser.id)
        .select(
          "profile.name as profileName",
          "profile.id as profileId",
          "users.id ",
          "users.email"
        );
      return Response(200, data[0], res);
    } else {
      return res.status(404).end();
    }
  } catch (err) {
    return res.status(500).send({ error: "Oops! Something went wrong!" });
  }
};

export default connectionHandler()(handler);

And finally, I'm showing a generic Next.js handler here instead of the full
fledged controller like in the example above, since the higher-order function is
going to be added in here and not in the controllers. So the only modification
you'll have to do to all the route handlers is , instead of exporting the
handlers directly, export a version wrapped in a higher-order function.