CODEWITHSUNDEEP
javascriptmysqlnode.jsexpressconnection-pooling
Hello World
Hello World

Reputation: 2907

Switch database in a connection pool in nodejs MySQL

How can I switch the database after connecting to MySQL in nodejs using connection pool?

I used to use the normal connection with MySQL since it has some issue now I would like to use the connection pooling. But how can I change the database after creating a connection with MySQL?

Here is how I change the database: 

conn.changeUser({
    database: req.session.dbname
}, function (err) {
    if (err) {
        console.log(err);
    } else {
        next();
    }
});

But now it shows conn.changeUser is not a function

Here is the method to connect with mysql: 

const conn = mysql.createPool({
    connectionLimit: 10,
    host: config.host,
    user: config.user,
    password: config.password,
    database: 'shaw_elc_gst_13032019'
});

This is the result when I console.log(conn):

Pool {
  _events:
   [Object: null prototype] {
     connection: [Function],
     acquire: [Function],
     enqueue: [Function],
     release: [Function],
     error: [Function] },
  _eventsCount: 5,
  _maxListeners: undefined,
  config:
   PoolConfig {
     acquireTimeout: 10000,
     connectionConfig:
      ConnectionConfig {
        host: 'localhost',
        port: 3306,
        localAddress: undefined,
        socketPath: undefined,
        user: 'root',
        password: '****',
        database: 'shaw_elc_gst_13032019',
        connectTimeout: 10000,
        insecureAuth: false,
        supportBigNumbers: false,
        bigNumberStrings: false,
        dateStrings: false,
        debug: undefined,
        trace: true,
        stringifyObjects: false,
        timezone: 'local',
        flags: '',
        queryFormat: undefined,
        pool: [Circular],
        ssl: false,
        multipleStatements: false,
        typeCast: true,
        maxPacketSize: 0,
        charsetNumber: 33,
        clientFlags: 455631 },
     waitForConnections: true,
     connectionLimit: 10,
     queueLimit: 0 },
  _acquiringConnections: [],
  _allConnections:
   [ PoolConnection {
       _events: [Object],
       _eventsCount: 2,
       _maxListeners: undefined,
       config: [ConnectionConfig],
       _socket: [Socket],
       _protocol: [Protocol],
       _connectCalled: true,
       state: 'authenticated',
       threadId: 11069,
       _pool: [Circular] },
     PoolConnection {
       _events: [Object],
       _eventsCount: 2,
       _maxListeners: undefined,
       config: [ConnectionConfig],
       _socket: [Socket],
       _protocol: [Protocol],
       _connectCalled: true,
       state: 'authenticated',
       threadId: 11067,
       _pool: [Circular] },
     PoolConnection {
       _events: [Object],
       _eventsCount: 2,
       _maxListeners: undefined,
       config: [ConnectionConfig],
       _socket: [Socket],
       _protocol: [Protocol],
       _connectCalled: true,
       state: 'authenticated',
       threadId: 11070,
       _pool: [Circular] },
     PoolConnection {
       _events: [Object],
       _eventsCount: 2,
       _maxListeners: undefined,
       config: [ConnectionConfig],
       _socket: [Socket],
       _protocol: [Protocol],
       _connectCalled: true,
       state: 'authenticated',
       threadId: 11068,
       _pool: [Circular] },
     PoolConnection {
       _events: [Object],
       _eventsCount: 2,
       _maxListeners: undefined,
       config: [ConnectionConfig],
       _socket: [Socket],
       _protocol: [Protocol],
       _connectCalled: true,
       state: 'authenticated',
       threadId: 11071,
       _pool: [Circular] },
     PoolConnection {
       _events: [Object],
       _eventsCount: 2,
       _maxListeners: undefined,
       config: [ConnectionConfig],
       _socket: [Socket],
       _protocol: [Protocol],
       _connectCalled: true,
       state: 'authenticated',
       threadId: 11072,
       _pool: [Circular] },
     PoolConnection {
       _events: [Object],
       _eventsCount: 2,
       _maxListeners: undefined,
       config: [ConnectionConfig],
       _socket: [Socket],
       _protocol: [Protocol],
       _connectCalled: true,
       state: 'authenticated',
       threadId: 11073,
       _pool: [Circular] },
     PoolConnection {
       _events: [Object],
       _eventsCount: 2,
       _maxListeners: undefined,
       config: [ConnectionConfig],
       _socket: [Socket],
       _protocol: [Protocol],
       _connectCalled: true,
       state: 'authenticated',
       threadId: 11074,
       _pool: [Circular] },
     PoolConnection {
       _events: [Object],
       _eventsCount: 2,
       _maxListeners: undefined,
       config: [ConnectionConfig],
       _socket: [Socket],
       _protocol: [Protocol],
       _connectCalled: true,
       state: 'authenticated',
       threadId: 11075,
       _pool: [Circular] },
     PoolConnection {
       _events: [Object],
       _eventsCount: 2,
       _maxListeners: undefined,
       config: [ConnectionConfig],
       _socket: [Socket],
       _protocol: [Protocol],
       _connectCalled: true,
       state: 'authenticated',
       threadId: 11076,
       _pool: [Circular] } ],
  _freeConnections: [],
  _connectionQueue:
   [ [Function],
     [Function],
     [Function],
     [Function],
     [Function],
     [Function],
     [Function],
     [Function] ],
  _closed: false }

Upvotes: 4

Views: 4878

Answers (1)

T.J. Crowder
T.J. Crowder

Reputation: 1075735

I suggest giving the pooling documentation a thorough read.

You've said you're using conn.changeUser(/*...*/), but then you've said you're using const conn = mysql.createPool(/*...*/); to initialize that conn constant. That means conn is a pool, not a connection; it's not surprising that it doesn't have a changeUser method.

If you want to change database, you need to do it on the connection, not the pool. Instead of using the shorthand pool.query form, you'd do pool.getConnection/conn.changeUser/conn.query/conn.release. First, call your variable pool, not conn:

const pool = mysql.createPool({

then

pool.getConnection(function(err, conn) {
    if (err) {
        // handle/report error
        return;
    }
    conn.changeUser({
        database: req.session.dbname
    }, function (err) {
        if (err) {
            // handle/report error
            return;
        }
        // Use the updated connection here, eventually
        // release it:
        conn.release();
    });
});

That said, if it were me, I'd be more comfortable having a connection pool per database, rather than a common pool where you change the database. That could be pure paranoia on my part, but it's what I'd do. But if you don't use separate pools, I suggest always do the changeUser so you're sure what database you're using, or test thoroughly to see what the mysql module does to handle this (and repeat that testing on every dot release of the module, unless the maintainer documents the behavior).

Upvotes: 12

Related Questions