We need to handle dynamic connection strings. The idea is that different clients of our Web API need to have different databases. We want that when client A makes a request to the Web API, database X is used; and when a client B does the same, then database Z is used.
One reason why we might want this is if we have a multi-tenancy environment and we want our clients to have individual databases. It is not mandatory to have individual databases in multi-tenancy environments, but it is a requirement that some companies may have.
Solution
When we are configuring the DbContext in an ASP.NET Core web application, we typically use AddDbContext as follows:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| services.AddDbContext<ApplicationDbContext>(options => | |
| options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection") | |
| )); |
Notice that as an AddDbContext parameter we have an Action. With an Action we encapsulate a method. In this case, the Action must construct the DbContext options. What interests us most is building the connection string according to the client making the HTTP request, say, the client sends us a ‘tenantId’, but where will we get this information from?
We can get this data wherever we want:
- URL
- A field in the HTTP header
- A cookie
I am going to pretend that such information comes from a field in the HTTP header.
The second thing we need is a way to build a connection string. In my case, I am going to have a connection string template which is going to point to a certain server, but the name of the database is going to be the tenantId. For this in my appsettings.json I put the following:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| "ConnectionStrings": { | |
| "PlaceHolderConnection": "Data Source=.;Initial Catalog={dbName};Integrated Security=True", | |
| } | |
| … |
Notice that in Initial Catalog we have {dbName} which is where we will dynamically place the name of the database. You could also make the Data Source dynamic if you want.
Note: Another solution would be to use the SqlConnectionStringBuilder class to dynamically construct the connection string.
Then we need the HTTP header inside the AddDbContext. For this, we are going to use an overload that gives us an IServiceProvider, in this way, we will obtain the HttpContext, and thus, the desired header. Suppose the name of the header is TenantId. Therefore, the following code does the job:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| services.AddSingleton<IHttpContextAccessor, HttpContextAccessor>(); | |
| services.AddDbContext<ApplicationDbContext>((serviceProvider, dbContextBuilder) => | |
| { | |
| var connectionStringPlaceHolder = Configuration.GetConnectionString("PlaceHolderConnection"); | |
| var httpContextAccessor = serviceProvider.GetRequiredService<IHttpContextAccessor>(); | |
| var dbName = httpContextAccessor.HttpContext.Request.Headers["tenantId"].First(); | |
| var connectionString = connectionStringPlaceHolder.Replace("{dbName}", dbName); | |
| dbContextBuilder.UseSqlServer(connectionString); | |
| }); |
As you can see, after getting the connection string template, we search for the database name which is equal to the value of the tenantId header, then we do the replacement, and finally we configure the DbContext with the connection string.
In this way we have configured EF Core with a dynamic connection string.
Security
You should not simply allow the user to send a tenantId with any value, since this would allow them to access the data of any client. One solution is to create an application-level policy that verifies that the user’s request is valid. This can be done by verifying that the user’s token (if you use JWT) contains the proper tenantId.
Courses
If you want to learn more about developing Web APIs with ASP.NET Core, get my course on Udemy today: https://www.udemy.com/course/building-restful-web-apis-with-aspnet-core/?referralCode=DAFD27F4028D04B62181
Learn how to develop interactive web applications with C# using Blazor: https://www.udemy.com/course/programming-in-blazor-aspnet-core/?referralCode=8EFA9D9FF38E3065DF0C
Regards!
Felipe Gavilán 