I have a question regarding SQL connection pools. My team is using the knex.js library in one of our node applications to make database query's. The application from time to time needs to switch databases. So my team created an initialization function that returns a knex object configured to the correct database. Then that object is used to do said query. To me this seems redundant and can cause bad performance, because we initiate a knex object every time need to do a query instead of reusing a single knex object. Which i could ignore if knex already does this when you which databases (and if anyone could shed light on this question as well that would be FANTASTIC !) . Moreover, (and this leads me to my question titled above) the connection pool properties are redefined. So does that mean we are creating new pools every time, or does the SQL ( SQL Sever in this case) reuse the connection pool you already defined ? The question might not be Knex specific, like if i used a library like knex for C#, and call that library a similar way, would SQL Server know not to make more connection pools?

Example code:

/** db.js
 * @param {any} database 
 * @returns db: Knex
 */
module.exports = ( database ) => {
  var knex = require('knex')({
    client: 'mssql',
    connection: {
        database: database,
        server:  '127.0.0.1',
        user: 'your_database_user',
        password: 'your_database_password'
    },
    pool: {
        min: 0,
        max: 10,
        idleTimeoutMillis: 5000,
        softIdleTimeoutMillis: 2000,
        evictionRunIntervalMillis: 500
    }
  });
  return knex; 
}; 


Index.js

var db = require('./db.js'); 
/**
 * @returns users:Array
 */
const getUsers = async() => {
    const users = await db('master')
            .select()
            .from('users_table')
            .orderBy('user_id'); 
    return users; 
}

I have a question regarding SQL connection pools. My team is using the knex.js library in one of our node applications to make database query's. The application from time to time needs to switch databases. So my team created an initialization function that returns a knex object configured to the correct database. Then that object is used to do said query. To me this seems redundant and can cause bad performance, because we initiate a knex object every time need to do a query instead of reusing a single knex object. Which i could ignore if knex already does this when you which databases (and if anyone could shed light on this question as well that would be FANTASTIC !) . Moreover, (and this leads me to my question titled above) the connection pool properties are redefined. So does that mean we are creating new pools every time, or does the SQL ( SQL Sever in this case) reuse the connection pool you already defined ? The question might not be Knex specific, like if i used a library like knex for C#, and call that library a similar way, would SQL Server know not to make more connection pools?

Example code:

/** db.js
 * @param {any} database 
 * @returns db: Knex
 */
module.exports = ( database ) => {
  var knex = require('knex')({
    client: 'mssql',
    connection: {
        database: database,
        server:  '127.0.0.1',
        user: 'your_database_user',
        password: 'your_database_password'
    },
    pool: {
        min: 0,
        max: 10,
        idleTimeoutMillis: 5000,
        softIdleTimeoutMillis: 2000,
        evictionRunIntervalMillis: 500
    }
  });
  return knex; 
}; 


Index.js

var db = require('./db.js'); 
/**
 * @returns users:Array
 */
const getUsers = async() => {
    const users = await db('master')
            .select()
            .from('users_table')
            .orderBy('user_id'); 
    return users; 
}

• javascript
• sql-server
• node.js
• knex.js

• 1 What the problem with .withSchema([schemaName])? – Fazal Rasel Commented Mar 31, 2018 at 19:23
• As mentioned above, just export one pool and use withSchema when making queries. You don't need a separate pool for each database. – user2652134 Commented Mar 31, 2018 at 20:39
• 1 The .withSchema is a great idea if you are using multiple schemas within a single db instance. But it won't work for accessing multiple database instances. – GaryL Commented Apr 10, 2018 at 23:47

1 Answer 1

Short answer: The 'singleton' nature of the node require() statement prevents reinitialization of multiple occurrences of knex. So the initially created pool continues to be used for the duration of your process, not recreated, as long as you don't discard the db. variable reference.

More discussion...

... my team created an initialization function that returns a knex object configured to the correct database. Then that object is used to do said query. To me this seems redundant and can cause bad performance, because we initiate a knex object every time need to do a query instead of reusing a single knex object. Which i could ignore if knex already does this when you switch databases...

    var db = require('./db.js');

The node.js require statement creates a singleton object. (You probably already know) this means that the first time the module is called by your program using the require statement, the module and it's data will be initialized, but successive identical require calls will just reuse the same module reference and will not reinitialize the module.

... the connection pool properties are redefined. So does that mean we are creating new pools every time, or does the SQL ( SQL Sever in this case) reuse the connection pool you already defined ?

So since the require()-ed module is not reinitialized, then the originally created pool will not be re-created. Unless you discard the db variable reference (discussed more below).

The question might not be Knex specific, like if i used a library like knex for C#, and call that library a similar way, would SQL Server know not to make more connection pools?

Generally speaking, you need to build or acquire connection some code to properly manage a pool of connections throughout the life of your process. Knex and most other database wrappers do this for us. (Under the covers Knex uses this library before v0.18.3 and this one on/after.)

Properly initializing and then using the singly initialized pooling code throughout the life of your application process acplishes this. Discarding the pool and recreating it within your process defeats the purpose of having pooling. Often pooling is setup as part of process initialization.

Also, this was probably just a misstatement within your question, but your Node.js module is making the connection pools, not the SQL Server.

... The application from time to time needs to switch databases. my team created an initialization function that returns a knex object configured to the correct database.

From that statement, I would expect to see code like the following:

var db = require('./db.js');
var dbOther = require('./dbOther.js');

... which each establishes a different database connection. If you are instead using:

var db = require('./db.js');
// ... do other stuff here in the same module ...
var db = require('./dbOther.js');

... then you are likely throwing away the original reference to your first database, and in that case, YES, you are discarding your DB connection and connection pool as you switch connections.

Or, you could do something like the following:

// initialize the 2 connection pools
const dbFirst = require('./db.js');
const dbOther = require('./dbOther.js');

// set the active connection
var db = dbFirst;
// change the active connection
db = dbOther;