Recommended way to change database in existing connecction with node-mysql

Question

I am trying to change database in an existing connection in node. The connection may or may not have database name in createConnection call. Here's the code:

var mysql = require('mysql');


connection = mysql.createConnection( {
    host               : 'localhost',
    user               : 'me',
    password           : 'secret',
    port               : 3306,
    /* database           : 'test' // optional */
});

I am using node-mysql lib. Currently I'm closing the connection and re-connecting.

Is there any better way to do that? Similar to mysql_select_db in php?

Veve · Accepted Answer · 2018-01-31 16:17:32Z

14

You can change some of the connection parameters* with the changeUser method:

connection.changeUser({database : 'my_database'}, function(err) {
  if (err) throw err;
});

*List of these parameters:

user: The name of the new user (defaults to the previous one).
password: The password of the new user (defaults to the previous one).
charset: The new charset (defaults to the previous one).
database: The new database (defaults to the previous one).

answered Jan 31, 2018 at 16:17

Veve

6,7785 gold badges44 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hello World Over a year ago

How can I switch the database in a connection pool?

Veve Over a year ago

@HelloWorld see this answer to switch the database in a connection pool. Note that I agree with the end note avise about having a connection pool per database, rather than a common pool where you change the database.

ryanmc · Accepted Answer · 2017-09-22 18:36:43Z

6

The only way I have found is to use the 'USE' command

in your example it would look like this

let database = 'my_database';
connection.query(`USE ${database}`, (error, results, fields) => {
    ...
  });

after that all queries using this connection would be sent to the new database

answered Sep 22, 2017 at 18:36

ryanmc

8142 gold badges10 silver badges22 bronze badges

Comments

user956584 · Accepted Answer · 2021-05-12 23:47:24Z

2

Or just use database name in query

conn.query('SELECT H FROM other.GeoDB WHERE H = \'r1r0f\' LIMIT 1  ', function (err, res, f)
conn.query('SELECT * FROM data.users WHERE U = \'123\' LIMIT 1  ', function (err, res, f)

answered May 12, 2021 at 23:47

user956584

5,5875 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

Recommended way to change database in existing connecction with node-mysql

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related