Using multiple database schema in a multi tenant application

Question

I am moving my current application to a multi tenant architecture. With a single code base, i need to address multiple tenants. I am using single database, multiple schema approach. Each tenant will be assigned a separate schema with the meta data saved in the default schema.

Application is built with ASP.NET MVC. I use Dapper to connect to my SQL Server. I am having 50+ functions which calls the database using direct query as well as stored procedures. Is there any method by which i can set the schema when the dapper is initialized for each tenant without altering the functions.

Answer 1

In SQL Server, the schemas are associated with users. Each user has atleast one default schema. You can associate the tenant with schema, that means you will have mapping for

Tenant -> Schema -> User

Dynamically construct your SQL Connection string and pass the SQL user corresponding to that tenant. This way the default schema is automatically pointed out and you Dont have to change your Dapper queries and dont have to write dynamic queries as well.

Note: Don't forget to check the Connection pooling implication.

Answer 2

I would try a simple approach.

With Dapper, for easy maintenance, I store the querys in an external XML File. So, you could have an external XML file for each of your customers. Another solution, but I didn't tried it before, I think that you could add to your query a parameter that would be the schema name. So in every query you would always need to specify the schema.