knex connections aren't directly freed after a route has finished (and connection object goes out of scope)

Question

In my application I am creating the knex connection object only during route handling, not "on script loading", since the configuration is loaded elsewhere into a global object (Sails).

The code hence looks a bit like:

const knexBuilder = require('knex');
function db() {
  if (!!sails && !!sails.config && !!sails.config.datastores && !!sails.config.datastores.default) {
    return knexBuilder({
      client: 'pg',
      connection: sails.config.datastores.default,
    })
  } else {
    throw new Error('sails not initialized, can\'t load db');
  }
}
module.exports = {
  getEventDates: async function(req: RequestTy, res: ResponseTy) {
    const sess = req.session;
    try {   
      const dbObject = db();
      const dbPromise = dbObject
        .select(`ed.${EventDate.schema.event.columnName}`, `ed.${EventDate.schema.start_date.columnName}`)
        .from(`public.${EventDate.tableName} as ed`);
      const data = await dbPromise;

      return res.json(data);
    } catch(err) {
      console.log(err);
      return res.status(500).send('problems');
    }
  },
}

Now I notice that the database has a constant increase in connections. With slowly connections being dropped over time, but in our application this quickly leads to an error where there are too many connections at once. Even wth only a few people (above function is called every few seconds by each user who has the frontend page open).

So how can I force knex to behave nicely and clean up after itself and drop the db connections as soon as the object goes out of scope?

Answer 1

You are creating new connection pool every time when you call your db() function which creates new knex instance. That also forces knex to create new connections to the database on every request, because it cannot use old open connections from those old abandoned knex instances.

In your db() function you should first check if you have already created a knex instance and if you have then return the one already created.

Basically you should never create multiple knex instances in your application unless you are connecting multiple databases.