where to destroy knex connection

Question

I'm using knex with pg.

I have a project similar as following.

dbClient.js

const dbClient = require('knex')({
  client: 'pg',
  connection: {
    host: '127.0.0.1',
    user: 'user',
    password: 'password',
    database: 'staging',
    port: '5431'
  }
})

module.exports = dbClient

libs.js

const knex = require('./dbClient.js')

async function doThis(email) {
  const last = await knex('users').where({email}).first('last_name').then(res => res.last_name)
  // knex.destroy()
  return last
}

async function doThat(email) {
  const first = await knex('users').where({email}).first('first_name').then(res => res.first_name)
  // knex.destroy()
  return first
}

module.exports = {
  doThat,
  doThis
}

test01.js

const {doThis, doThat} = require('./libs.js');

(async () => {
  try {
    const res1 = await doThis('[e-mail]')
    console.log(res1)
    const res2 = await doThat('[e-mail]')
    console.log(res2)
  } catch (err) {
    console.log(err)
  }
})()

When knex.destroy() was removed from libs.js as shown above. node test01 could output res1 and res2. But the issue is the connection hangs indefinitely and CMD never return.

But if I uncomment knex.destroy() from libs.js, then doThis will execute, CMD will hangs at doThat as there's no connection anymore which has been closed in doThis.

My question is :

What is the best location for knex.destroy()? Or there's other ways to do it?

Thanks for your time!

Answer 1

Knex destroy() seems to be a one time operation. After destroying a connection, one might require a brand new connection pool for the next operation.

The db client module you export is cached into node module cache and a new connection pool is not created every time you require it.

This is intended usage, the pool is supposed to be destroyed when app exits or all the tests are done. If you have reasons to create/destroy connections for every operation ( like in serverless environment) you should not reuse the destroyed client, rather create a new instance every time.

Otherwise, it defeats the purpose of connection pooling.

---

Update about lambda/server-less environments:

Technically a function and its resources are to be released after the lambda function has run, this includes any connections it might have opened. This is necessary for truly stateless functions. Therefore it is advisable to close connection when function is done. However, a lot of functions opening/closing a lot of connections may eventually make the DB server run out of connections (see this discussion for example). One solution might be to use an intermediate pool like PgBouncer that negotiates connections between DB server and Lambda functions.

The other way is for the platform provider (AWS) to add special pooling capabilities to lambda environment and let them share long-lived resources.

Answer 2

You probably don't usually need to explicitly call knex.destroy() – this is implied by the documentation itself saying (emphasis mine):

If you ever need to explicitly teardown the connection pool, you may use knex.destroy([callback]).