Multi tenant - create database dynamically for each tenant

Question

We are doing a multi tenant web application and the architecture is single code base - multiple database.

We have a master database which will store all the tenant and their corresponding user information.

Whenever a new tenant register with us, we need to have separate DB for each of them. There are around 15 tables for each tenant.

There are two options visible for us to handle this scenario

1. Can have a sql file which will be part of the project and whenever a new tenant registers, we can run the script to create new DB and the tables. Here I need to maintain all the schema modifications and there are few static table. Static table data will get modified as we move on. So all these history has to be maintained and we need to run the entire script when a tenant registers.

2. There can be a empty database with the latest schema. So when a tenant registers, I can dynamically create script from the db and run it. I don't need to maintain the history as the empty db schema will be the latest always.

Please let me know if there are any other ways to do it.

As features grow, we also need to modify the schema(new column, new table) for the existing tenant tables. When considering this scenario, We believe the first option could be the best as we will have the alter queries but in second option, I need to compare the schema which could be tedious.

Please share your thoughts on how design it.

Answer 1

We do something similar. We have a shared code base with lots of configuration options. Each tenant has their own database and storage and we run most of them as a single web application.

We have a shared configuration database that manages the tenant information. We also have a portal that creates Tenants and manages them e.g. enables certain features, provisions storage accounts, databases etc. Each tenant configuration is a database row in our configuration database. Then we provision a Database per tenant and generate a tenant user and create a connection string which is stored in the configuration database.

At app startup the application identifies which tenant it is - gets the connection string to use for tenant database from the configuration. We are using Entity Framework migrations migrate to the latest version so the first time a client uses their database the schema is created. If we update schema the migrations are added and next time client connects to the database the new schema is applied. Works fine for us.