I have one MySQL instance with one database for each user.
I want to build an API that should connect to the right database depending on the user calling it.
For this setup what is the best way to manage database connections?
In the documentation of the mysql node package I found the following two options:
- Create one pool for the whole instance, then use changeUser to connect to the database,
for example like this:
const pool = createPool({
connectionLimit: 10,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
port: 3306, // default
host: process.env.SQL_INSTANCE_HOST,
})
const connection = await pool.getConnection();
connection.changeUser({ database: userDatabase }, (err) => {
throw new Error(`Could not connect to database: ${err}`);
});
// use connection, release it after usage
- Use PoolCluster to create one pool for each database, then use the call the pool corresponding to the user:
const poolCluster = mysql.createPoolCluster();
poolCluster.add('USER1', config1);
poolCluster.add('USER2', config2);
poolCluster.add('USER3', config3);
const pool = poolCluster.of('USER1');
pool.query(function (error, results, fields) {
// perform query
});
In an answer to an earlier question someone expressed the preference for using PoolCluster over changeUser but without exactly explaining why.
What might be the pros and cons of using one option over another?
Are there other options to consider?
2
Answers
Creating 1 database per user is a pretty unusual design. There’s some cases where this may make sense, so I want to give you the benefit of the doubt and assume you have a really good reason to.
My assumption is that you’re using Node to build some API or webservice, so broadly I think you have 2 options:
For example, if you have 1 username/password to access MySQL you could prefix every table with the database name:
If you prefix everything, there’s not really anything special you need to do. Just make one pool for everyone.
Pools are used for homogenous connections. e.g. Multiple stand-by connections to the same database.
Here is two scenarios –
Same username and password for all the Databases – You should avoid regular polling. You can use Redis to reuse the connections.
Different username and password for all the Databases –