mysql - How to dynamically select the database(or "schema"), in a Sequelize model?

Question

I'm building a multi tenant application, using Nodejs, Seuqelize and Mysql.

I've decided on an approach where each tenant gets a separate logical database(From what i understand, in Postgres this would be called a "schema", but in Mysql it's simply a "database"). I chose this approach, because in my case, databases would need to be created after the Node process already started, and a DB connection was made, and there is no way to configure them in advance.

I do not know how to dynamically(during an HTTP request) tell a Sequelize model, which database should be used, for the particular query.

For example, let's say i have this GET/photo/ route:

router.get('photo/', async function (req, res) {
    const tenantId = req.user.id//Assume the auth middleware provides a tenant id.
    try {
        const records = await Photo.findAll()//Need to tell the Photo model, which database/schema this
        //should be queried in      
        
        res.json(records);
    } catch (error) {
        console.log(error)
        res.sendStatus(500)
    }

})

Of course, if i were using raw queries, i would just say

SELECT * FROM ${tenantId}.photo

But if i'm using Sequelize, i want to actually enjoy its full features.

How can i tell the Photo model, which database to use? Let me clarify again, that i'm not talking about a physical database, but just a logical one. The host is the same host, and i have only one connection. The initial "database" argument to the Sequelize config is a database that holds all tenants, with references to their respective databases.

question from:https://stackoverflow.com/questions/65852197/how-to-dynamically-select-the-databaseor-schema-in-a-sequelize-model

Answer 1

You would need to create a new instance of Sequelize for each of the schemas you want to create a connection for. The Models will need to be dynamically defined so that correct sequelize instance is passed in when the Model is created and subsequently returned.

1. getPhotoModelFor(tenantId) - try to get a Model for a particular tenant
2. should probably check a cache to see if it already exists, will need to handle expirations, etc
3. if the Model isn't in the cache for this tenant, get the sequelize instance for this tenant and create if it doesn't exist
4. define the model using this sequelize instance and cache
5. return Model and use it for queries.