NODEJS [DB] architecture help needed - Multi tenant [mysql]-CodePudding

I am creating a sass app using nestjs with knex and objectionjs (mysql)

Possible solutions are:

Single database using tenantId diffrentiator
Schema per tenant
Database per tenant
Database for a group of tenants

I prefer going with Hybrid i.e. Database for a group of tenants.

I am able to connect to multiple databases persisiting the connection in memory and passing the knex instance in Objection Models.

These are options I can think of: 1 - Connecting to all the databases and keep the connections in memory and change the knex connection on per request basis.

2 - I don't connect to dbs on application startup and connect to db on request and keep the connection live for further requests.

3 - I can create a pool of connections to server without specifying the database. And use the connections for all the dbs while changing the database on per request basis.

connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'your_database_user',
    password : 'your_database_password',
}

Which option will be good to go?

Another usecase is for scheduled jobs and queues. How should I proceed ? Should I go with creating a knex instance for each task in these cases ?

references: https://vincit.github.io/objection.js/recipes/multitenancy-using-multiple-databases.html

CodePudding user response：

I would not go for Database for a group of tenants option if I were you since it has cons of both Database per tenant and Single database options.

I'm also currently working on a multi-tenant app and I chose Single database because can you imagine how hard it is to maintain, optimize and migrate for example 1,000 databases? (which still very low number of customers.)

Also there will be situations where you need to perform jobs on databases (move records to archive for example) or connect it to Kafka to stream database events, In those situations having 1,000 to change would be nightmare if not impossible.

I think using multiple databases is good when you have very low customers count which pay you very well which then makes it worth it to go through all of those problems. Some pros of multiple databases would be performance since table's record count is drastically lower than Single Database method. Which still can be mitigated by partitioning tables by their tenantId or optimizing queries.

But if you are not convinced I suggest you to use durable providers or LRU cache to maintain a certain number of database connections at a time.

CodePudding user response：

A single database with a tenantId column in all your tables is the way to go. If you wish, you can later shard the tables by tenantId if it ever makes sense to do so in the future.

I would also advise using string UUIDs for id generation instead of numbers.