How to manage database connections to a MySQL instance and multiple databases?

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:

1. 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

2. 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?

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:

1. If you need to make a separate connection per user, then I think I would not use any pools at all because users will hit your service sparingly and it's just not worth keeping any connections open.
2. If you can re-use the connection for multiple users, then just use a regular pool.

For example, if you have 1 username/password to access MySQL you could prefix every table with the database name:

SELECT * FROM databasename.tablename

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 -

1. Same username and password for all the Databases - You should avoid regular polling. You can use Redis to reuse the connections.

2. Different username and password for all the Databases -

   • First you need to store the database credentials in master DB with mapped database with user.
   • You can store your database connection in Redis instead of creating database every time.
   • You can check connection is available in Redis for this particular database use it otherwise create it and store it in Redis for future use.

Proceed from the demand (manage multiple databases under the same instance through API, and use unused users).
I suggest using Devart's MySQL ODBC driver to manage database connections in the API. After the link is established, users who can call API can dynamically switch databases. Configuration items are also very simple. Simply configure your API to accept user credentials or identifiers as input and use them to determine the appropriate database to connect to. I think this should be the most flexible and efficient solution.