Pool connection for Multiple Mysql Database in Node.js

Question

I am trying to create a multi tenant node.js application (with also sub domains) where each user has their own database under a single host, (say an rds instance).

So it will be easier for me to handover the database to that particular user to access that particular database.

So my problem here is when an user request the api, is it possible to dynamically change the database name based on the request.

One thing that popped up in my mind is to create a pool connection with credentials that have access to all database and add the database name in the query, in this way i can achieve what i want, but just out of curiosity is it possible to add the database name after the connection pool has been created, without adding it to the query.

My solution :

select * from 'dbname'.users

What i am looking for:

const pool = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    // database: process.env.DB_NAME, leaving out since db name should be dynamic
    connectionLimit: 5,
    supportBigNumbers: true,
});

And in middleware when i need db access, i should be able to set the db name based on the request.

The reason behind this is, I have created the application and wrote the queries without the database name, now i should add the logic to every query in my application.

Phalan

I am having the similar issue .How I can create multi tenant database connection pool for multiple databases. So basically I want to create connection pool at Instance level(don't want to create connection pool for every database) and according to request i'll ask connection for particular database and access the database – Arnie

Answer 1

Reading the official docs: https://github.com/mysqljs/mysql/blob/master/Readme.md#connection-options

It is mentioned that the database option is optional. You can discard this option when initializing the pool and specify the right database in your SQL query. You cannot create pool without database configuration and then set a database to the connection. You must use the query for this purpose.

Rogan

i have edited my question, by admin credentials i meant the user that has access to all the database – Phalan