trying to use node.js and mysql to connect to different database per user - preferably using a pool

Question

I'm fairly new to nodejs, knex, bookshelf etc and am currently developing a web app that would connect to a different mysql database based on which user is logged in.

The first connection works fine, I then log out and in the sign out code I do knex.destroy(). When I go to log back in I get an Unhandled rejection Error: There is no pool defined on the current client

It seems that knex doesn't recreate a pool once it has been destroyed even if it gets reinitialized.

Has anyone tried this and know how to do it?

I have tried initialising without a database and adding one when the user logs in but the connection doesn't seem to then connect to the database. I've tried instantiating a new connection with the database without destroying the previous, which results in the user using the first user's database. Destroying the connection removes the MySQL connections and even establishes a new connection with the right database but apparently no pool.

From reading it looks like knex wasn't designed to do this but surely there must be a way to instantiate a new connection with a new pool?

I'm using passport-local code snippets follow

passport.use(new LocalStrategy({
    usernameField: 'email',
    passwordField: 'password'
  },
  function(email, password, done) {
    new userModel.User({email: email}).fetch().then(function(data) {
      var user = data;
      if(user === null) {
         return done(null, false, {message: 'Invalid username or password'});
      } else {
         user = data.toJSON();
         if(!bcrypt.compareSync(password, user.password)) {
            return done(null, false, {message: 'Invalid username or password'});
         } else {
             ctrl = new DB();
             ctrl.connect(user.db);
             dbModel = require('../server/models/dbModel');
             return done(null, user);
         }
      }
   });
}));

DB.js

function DB(){

}


DB.prototype.connect = function (db){   
    if (db !== "crm" && db !== null){
        db = "crm-" + db;
    }

    DB.knex = require('knex')({
        client: 'mysql',
        connection:{
            host: 'localhost',  // your host
            user: MYSQL_USR, // your database user
            password: MYSQL_PWD, // your database password
            database: db,
            charset: 'UTF8_GENERAL_CI' //,
    //      debug : true
        }
    });

    DB.bookshelf = require('bookshelf')(DB.knex);
};

DB.prototype.destroy = function (){
    DB.knex.destroy().then(console.log("destroyed"));
};

module.exports = DB;

please help! let me know what other code you may need.

Has anyone ever done this and if so how? Please, I have not had or found any answers.

Answer 1

I'm not sure if this will help or not, but I got this stripped down version to work. I'm not really familiar with knex, but I created two very simple databases and I was able to connect to both and get simple output.

index.js

var DB = require('./DB');

function userConnect(db_name){
    var db = new DB();
    var knex = db.connect(db_name);

    knex.select('color').from('test').then(function(result){
        console.log(result);
        knex.destroy();
    });
}

userConnect('db_one');
userConnect('db_two');

DB.js

function DB(){

}


DB.prototype.connect = function (db){   
    return require('knex')({
        client: 'mysql',
        connection:{
            host: MYSQL_HOST,  // your host
            user: MYSQL_USER, // your database user
            password: MYSQL_PASSWORD, // your database password
            database: db
        }
    });
};

module.exports = DB;