Where and when to use knex.destroy?

Question

I'm confused on where to use knex.destroy() in my Node API.

If I don't use knex.destroy() after I open the connection to make a call, the connection pool fills up over time, leading to error:

Unhandled rejection TimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

If I close the connection, which makes sense to me, when I'm done with it,

router.get('/users', function(req, res, next) {
    var select = knex.select('*').from('users');
    select.then((result) => {
        res.send(result);
    }).catch((error) => {
        res.send(error);
    }).finally(function() {
        knex.destroy(); // close it when I'm done
    });
});

The connection is closed for separate API calls:

Unhandled rejection Error: Unable to acquire a connection at Client_PG.acquireConnection (/var/app/current/node_modules/knex/lib/client.js:331:40)

So where and when do I actually destroy the connection? Again, this Node application simply serves as an API. Each API call should open, then close, the connection, but knex doesn't seem to like this.

Router files that require knex: (I do this for each router file)

const knexService = require('../knexService');
const bookshelf = knexService.bookshelf;
const knex = knexService.knex;
let User = require('../models/User');

module.exports = function(app, router) {
   router.get('/users', function(req, res, next) {
       var select = knex.select('*').from('users');
       select.then((result) => {
           res.send(result);
       }).catch((error) => {
           res.send(error);
       }).finally(function() {
           knex.destroy(); // close it when I'm done
       });
   });
   ...

UserModel file

const knexService = require('../knexService');
const bookshelf = knexService.bookshelf;
var BaseModel = require('./BaseModel');
var addressModel = require('./Address').Address;

var User = BaseModel.extend({
    tableName: 'users',
    hasTimestamps: true,
    addresses: function() {
        return this.hasMany(addressModel);
    }
});

KnexService.js

const knexfile = require('./knexfile');
const knex = require('knex')(knexfile.production);
const bookshelf = require('bookshelf')(knex);

module.exports.knex = knex;
module.exports.bookshelf = bookshelf;

KnexFile.js

module.exports = {

    development: {
        client: 'pg',
        version: '7.2',
        connection: {
            ...

Knex internally manages a pool, when you run a query like this it allocates a connection from the pool, and releases it when its resolved.... You should not need that destroy call at all, something here is not as it appears. — Catalyst, Apr 22 '19 at 15:22
Where do you open the connection? You should only need to do that once when it starts up — Catalyst, Apr 22 '19 at 15:22
@Catalyst I agree. Why sometimes does my pool fill up leading to the error above? — user3871, Apr 22 '19 at 17:04
hard to debug, but this can happen if you're issuing more simultaneous requests to your server than the database is serving. Like if the pool only handles 8 connections, and those `select *` statements take 50ms, and you issue 16 requests in 25ms — Catalyst, Apr 23 '19 at 02:09

score 8 · Accepted Answer · answered Apr 24 '19 at 20:44

knex.destroy() should be called when you want to knex to throw away all connections from pool and stop all timers etc. so that application can end gracefully.

So basically that should be called only on application exit unless you are doing some more complex stuff for example with connecting to multiple databases with multiple knex instances.

If you are running out of connections and pool fills up it means that you have problems in your code. Possible reasons could be:

making too many long lasting queries concurrently
creating transactions and never committing / rolling back so those connections are never returned to the pool
bug in knex / bookshelf / some middleware

You should try to pinpoint which parts of your app causes pool to fill up, remove all the extra code like bookshelf related stuff and find the minimal setup which you can use to replicate your problem (also remove all transactions to start with).

Are you really using postgresql 7.2 or are you connecting some custom postgresql compatible DB? That could cause some issues, but I don't think those would reveal themselves in that way, but by having broken connections to be left in pool.

How can I locate where a node application officially exits? I'll add it to wherever that happens — user3871, Apr 24 '19 at 20:54
@Growler You should know when and where you like to stop your app... for example simple shell program probably should exit in the end of main function. There is no `official` location to do that. However you can catch event when process is about to exit https://stackoverflow.com/questions/14031763/doing-a-cleanup-action-just-before-node-js-exits — Mikael Lepistö, Apr 24 '19 at 21:03

Where and when to use knex.destroy?

1 Answers1