Multiple dynamic connections (500 or more)

[gustavoportaluppi]

I have an API in nodeJS and need to connect dynamically to different PostgreSQL databases (500 or more connections), all on the same server. I have a default database for all users, which will save the basic data for authentication. After being authenticated, the user's stream will be directed to another database (each user will have access to a specific database).

I could instantiate the Sequelize for each connection, but I think it will be dangerous for app performance. Is there any other solution / resource so I can access different databases through the same instance of Sequelize?

[StyleT]

Hi! I would suggest you not to use ORM for such purpose & stick to the simple PostgreSQL library.

[gustavoportaluppi]

Yes, this is a solution, but an ORM would make it a lot easier.
I'm searching on Sequelize and found the option to change the "searchPath".
My application is multi-tenancy, and I think that's exactly what I need.
I have hundreds of databases, all with the same models.

models.user.findAll ({searchPath: 'base2'});

When executing the above code, I get this error: Unhandled rejection SequelizeDatabaseError: relation "users" does not exist.
Do you have any idea what it might be?

[sushantdhiman]

Whenever an user joins your application you need to open a connection to new database, you can initialize one sequelize instance with max pool set to 1. This will keep only one connection open for that specific user (and its database).

Although I feel this is not a good approach, you should use schema instead.

If your application need multi tenancy you can use schemas as well, thats what searchPath sets. A schema works like a way to organize tables inside database.

I have to warn you schema support is lacking in Sequelize but can we worked with.

Lastly error you are facing is because there is no users table in base2 schema.

[sandeep0009]

hey i got the same issue will anyone guide me right solution because i have multiple tenant and i want access particular instance of them