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?

score 14 · Accepted Answer · answered Jan 31 '18 at 16:17

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 '18 at 16:17

Veve

6,643
5
39
58

3

How can I switch the database in a connection pool? – Hello World May 14 '19 at 06:03
@HelloWorld see this [answer](https://stackoverflow.com/a/56146932/4074148) 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. – Veve Apr 11 '21 at 09:37

score 6 · Answer 2 · answered Sep 22 '17 at 18:36

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

score 1 · Answer 3 · answered May 12 '21 at 23:47

1

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 '21 at 23:47

user956584

5,316
3
40
50

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

3 Answers3