Dynamic Database Connection Management in SaaS Applications with EF Core

Introduction

In the world of SaaS applications, efficiently managing multiple client databases can be a daunting task. Traditional approaches often involve deploying separate instances for each client, leading to increased overhead and maintenance challenges. In this blog post, we’ll explore a more efficient solution using Entity Framework (EF) Core. By dynamically changing the database connection at runtime based on the subdomain of the requestor, we can streamline our architecture and improve scalability.

Background

Before we dive into the technical details, let’s first understand a couple of key concepts: Entity Framework (EF) Core and the idea of multi-tenancy in SaaS applications.

Entity Framework (EF) Core

Imagine you have a bunch of data stored in a database, like information about users, products, or orders. EF Core is like a smart translator between your code and this database. It helps you easily read from and write to the database without having to deal with complicated SQL queries.

Multi-Tenancy

Now, let’s talk about multi-tenancy. Imagine you’re running a SaaS application like a fancy online store where different companies use your platform to sell their products. Each company is a “tenant.” Multi-tenancy means you’re sharing the same application and database among all these companies, but they each have their own space in the database to keep their data separate and private.

Got it? Great! Now, let’s see how we can use EF Core to make our SaaS application smart enough to know which tenant’s data to work with, based on who’s asking.

Implementation Strategy

Here’s how we make our SaaS application smart enough to connect to the right database, based on who’s asking:

Identify the Subdomain:

We need to figure out who’s making the request. One easy way to do this is by looking at the subdomain of the website they’re coming from. For example, if someone’s visiting “https://store.example.com", the subdomain is “store”.

Middleware to Handle Subdomain Extraction and Database Connection:

A pivotal component of our implementation is a custom middleware. This middleware not only extracts the subdomain but also dynamically configures the database connection for EF Core. By intercepting each request, the middleware ensures that the appropriate database context is used based on the subdomain.

Code Implementation:

Below is the implementation of the middleware incorporating subdomain extraction and dynamic database connection configuration:

public class DatabaseConnectionMiddleware
{
    private readonly RequestDelegate _next;

    public DatabaseConnectionMiddleware(RequestDelegate next)
    {
        _next = next;
    }

    public async Task Invoke(HttpContext context)
    {
        var subdomain = GetSubdomain(context.Request);
        var connectionString = GetConnectionString(subdomain);

        // Set the database connection string for the current request
        using (var scope = context.RequestServices.CreateScope())
        {
            var dbContext = scope.ServiceProvider.GetRequiredService<DataContext>();
            dbContext.Database.SetConnectionString(connectionString);
        }

        await _next(context);
    }

    private string GetSubdomain(HttpRequest request)
    {
        var host = request.Host.Host;
        var subdomain = host.Split('.')[0];
        return subdomain;
    }

    private string GetConnectionString(string subdomain)
    {
        var credential = AppSettings.DatabaseCredentials?.FirstOrDefault(x => x.Subdomain.Equals(subdomain, StringComparison.OrdinalIgnoreCase));

        if (credential is null)
            throw new Exception("Database credentials not found for the subdomain.");

        // Construct and return the connection string
        return $"Server={credential.Server};Database={subdomain};User Id={credential.UserId};Password={credential.Password};";
    }
}

Don’t forget to add middleware in program.cs file.

app.UseMiddleware<DatabaseConnectionMiddleware>();

Conclusion

In conclusion, by leveraging Entity Framework (EF) Core and implementing dynamic database connection management based on subdomains, we can significantly enhance the efficiency and scalability of SaaS applications. This approach allows for the seamless sharing of the same application instance among multiple tenants while ensuring data isolation and security. With the custom middleware described above, our SaaS application becomes adept at dynamically configuring database connections, thereby streamlining the architecture and reducing maintenance overhead.