How to register multiple IDbConnectionFactory instances using Funq in ServiceStack.net part 2

Question

ServiceStack has delivered on EVERYTHING I've thrown at it, except the SAAS (Multi-tenant) use case where single API instance is using several databases of the same schema, one per tenant. These databases, because of legal reasons need to be housed in separate instances.

So, my question is this, "Is it possible to change the connection per Request based on meta from a filter?

My question is somewhat similar to this one, but with the added twist that each database is the same.

Thank you, Stephen

Edit: If memory serves me correctly I think mythz and I have discussed this before and found that even if we were able to get the ADO connection to change, the IAuthRepository is then broken. So it's not feasible.

Answer 1

I've added an example Multi Tenant test showing my preferred approach where I would use a custom IDbConnectionFactory wrapper so I have better visibility and control of the Db Connections being created, i.e:

public class MultiTenantDbFactory : IDbConnectionFactory
{
    private readonly IDbConnectionFactory dbFactory;

    public MultiTenantDbFactory(IDbConnectionFactory dbFactory)
    {
        this.dbFactory = dbFactory;
    }

    public IDbConnection OpenDbConnection()
    {
        var tenantId = RequestContext.Instance.Items["TenantId"] as string;
        return tenantId != null
            ? dbFactory.OpenDbConnectionString(GetConnectionString(tenantId))
            : dbFactory.OpenDbConnection();
    }

    public IDbConnection CreateDbConnection()
    {
        return dbFactory.CreateDbConnection();
    }
}

I'll also prefer to have a master dbFactory singleton instance to use as a default for non-tenant requests which also specifies which Dialect Provider to use:

var dbFactory = new OrmLiteConnectionFactory(
    AppSettings.GetString("MasterDb"), SqlServerDialect.Provider);

container.Register<IDbConnectionFactory>(c =>
    new MultiTenantDbFactory(dbFactory));

To indicate that a Service is tenant-specific, I'll just create a custom interface:

public interface IForTenant
{
    string TenantId { get; }
}

Which Request DTO's can implement to indicate they're tenant-specific requests, i.e:

public class GetTenant : IForTenant, IReturn<GetTenantResponse>
{
    public string TenantId { get; set; }
}

Which can be easily detected throughout ServiceStack's Request pipeline like a Global Request Filter to pull out what tenant the request is for and add it to the RequestContext, e.g:

GlobalRequestFilters.Add((req, res, dto) =>
{
    var forTennant = dto as IForTenant;
    if (forTennant != null)
        RequestContext.Instance.Items.Add("TenantId", forTennant.TenantId);
});

The MultiTenantDbFactory can then read this back and open the Db Connection to the desired tenant:

var tenantId = RequestContext.Instance.Items["TenantId"] as string;
return new OrmLiteConnectionFactory(GetConnectionStringFor(tenantId))
    .OpenDbConnection()

Which will be used whenever anyone accesses base.Db in their services or dependencies.

Answer 2

Resolve connection string dynamically

This method uses a global request filter to determine who is making the request, and sets the connection string into a request item. Then when the IoC tries to resolve the IDbConnectionFactory it will retrieve the connection string for that request and establish a database connection.

public override void Configure(Container container)
{
    // Tell the IoC to get the database connection factory on each request
    container.Register<IDbConnectionFactory>(c => GetDatabaseConnectionFactory()).ReusedWithin(ReuseScope.Request);

    // Create a filter that will determine the tenant and set the appropriate connection string
    GlobalRequestFilters.Add((req,res,obj) => {

        // Determine the connection string based on the some parameter you know about the tenant.
        var dbConnectionString = ...

        // Use a default value if the tenant was unknown
        var defaultConnectionString = ConfigurationManager.ConnectionStrings["AppDb"].ConnectionString;

        // Save the connection string to the RequestContext.Items collection, so we can read it later
        HostContext.RequestContext.Items.Add("ConnectionString", dbConnectionString ?? defaultConnectionString);
    });
}

// This method returns the correct database connection to the request
public static IDbConnectionFactory GetDatabaseConnectionFactory()
{
    // Read the connection string from our Items
    var dbConnectionString = HostContext.RequestContext.Items["ConnectionString"];

    if(dbConnectionString == null)
        throw new Exception("Connection string has not been set");

    // Return the connection factory for the given connection string
    return new OrmLiteConnectionFactory(dbConnectionString, SqlServerOrmLiteDialectProvider.Instance));
}

IUserAuthRepository support

If you also make the IoC resolve the connection string dynamically on each request then the correct repository will be made available when authenticating.

container.Register<IUserAuthRepository>(c => 
    new OrmLiteAuthRepository(GetDatabaseConnectionFactory())).ReusedWithin(ReuseScope.Request); 

I hope that helps.