Multidatabase Support

[whizkidwwe1217]

Should be able to support multidatabase, e.g. run migrations for each tenant without restarting the app.

Is there any way I can use Dotnettency for multi-database multi-tenancy model?

The app should support the following scenarios:

1. Should be able to register multiple tenant records. (done)
2. Has a default tenant with its own database. (done)
3. Each tenants are configured with their own hostname/ports and connection strings. (done)
4. When a tenant accesses it's host name, the db context changes its connection by setting the connection string from the currently identified tenant. (done using OnConfiguring method of db context).
5. Since the connection has changed, it should be able to run migrations and create database for the current tenant. (this is what I need and I don't know what to do).
6. Should be able to add new tenant, configure it's host and connection string, create database and run migrations (#5), and remove existing tenants (i.e. drop database, delete from tenant catalog) without resarting the app.

[dazinator]

For 4 and 5, you could try the following Register the tenants DbContext inside the Tenant's container rather than the application container - i.e register it inside here: https://github.com/dazinator/Dotnettency/blob/develop/src/Dotnettency.Sample/Startup.cs#L46

That way, when you call AddDbContext() at that point, you should have access to the Tenant's details including its connection string.

In order to run migrations for each tenant, you should try to perform the migration when the tenants middleware pipeline is first initailised (happens once for each tenant) - i.e here: https://github.com/dazinator/Dotnettency/blob/develop/src/Dotnettency.Sample/Startup.cs#L90

In there you can try the typical ApplyMigrations code for EF Core which looks something like this:

using (var serviceScope = appBuilder.ApplicationServices.GetRequiredService<IServiceScopeFactory>()
    .CreateScope())
{
    serviceScope.ServiceProvider.GetService<RegistryDbContext>()
         .Database.Migrate();
}

Let me know how that goes?

[dazinator]

For 6 - As long as you don;t have to restart the app in order for it to listen on new ports / bindings (i.e the app is already listening for requests on potential new tenant urls) then yes this is the main goal of this library - however this library is still a work in progress and not a finished product.

[whizkidwwe1217]

@dazinator Thanks for the suggestion and for this awesome library! I'm currently using the recent unstable version: 1.4.0-unstable0039. However, I can't find the Events method of the container builder (containerBuilder.Events). I think it's only available in the develop branch. I managed to make #4 (change connection) worked by placing the AddDbContext inside the WithStructureMap method since the Event method is not yet available (not sure if I'm doing it correctly). However, I still have to override the OnConfiguring method in the db context and inject the connection string from the Tenant service.

For #4 (tenant db migration), it didn't work for me. I'm getting 'login failed for user' errors. However, I managed to run migrations through middleware, although it's costly because I have to check if the database exists and run migrations if not yet ran on every http request. At least it's working for now.

I tried to use the develop branch but there are tons of changes. I guess I have to wait for the next stable release. Thanks!

[dazinator]

Thanks for letting me know how it turned out. Yes develop branch has changed a bit and I am working on a new EF Core sample which I should have soon. Once its released I will update you here.

[dazinator]

Decided there are probably two difference scenarios I want to cover for EF. The first is where you have a single database, and the tables have a TenantID to seperate the tenant data. I have just added a new sample covering that case here: https://github.com/dazinator/Dotnettency.Samples/tree/aspnetcore20/src/Sample.EFCore

The second case, is where each tenant has a completely seperate database (i.e different connection string). I'll extend the sample soon to cover that case as well.

To see the sample in action, run the project. if using VS, make sure you have "Sample.EFCore" selected as the startup project rather than IISExpress:

If you browse localhost:5000 and 5001 - thats one tenant. If you browse on port 5002 thats another. Browsing to either of these, will also create / initialise the database (I am using sqllite). If you browse on path /Posts it will show you the blog post entities for the tenant. If you browse to /AddPost it will create add a new blog post entity.

Notes:

I use EF Core 2.0 features such as Global Query Filters to automatically apply a filter to queries based on the current tenant id. I also set Tenant ID up as a Shadow Property so you dont actually need to add this as a property to any of your entities. Likewise when creating entities, the DbContext automatically sets the TenandID shadow property for them to the current tenant ID.

Would be interested on your thoughts.

[dazinator]

@whizkidwwe1217 I now have samples showing how to do this. See https://github.com/dazinator/Dotnettency.Samples/tree/aspnetcore20/src

There are two EF Core samples there. One shows a single database, which holds records for all tenants, where those records have some TenantID to seperate them. The second shows each tenant having a seperate database. I think this sounds like the one you will be interested in. (it's called Sample.EFCore.PerTenantDb) You will have to upgrade your dotnettency packages to the versions used by the sample in order to use the Events etc.

Let me know if you have any issues and I will try and assist.

[whizkidwwe1217]

Awesome! Thanks @dazinator ! I'll definitely try this and I'll send you feedback afterwards.

[whizkidwwe1217]

@dazinator Is there a way to register dependencies through StructureMap? I tried to follow the sample code in your comment in issue#26 but the the dependencies were not loaded to the container builder. It works perfectly when I use only basic structuremap.

using basic structuremap configuration:

using Dotnettency:

RepositoryConvention.cs -> This is used to automatically register dependencies that implements the IRepository interface using Repository pattern.

The IRepository is getting a null value when injected to the controller:

Error:

[dazinator]

Yeah the issue with your code there is you are creating a new container for the tenant and not configuring the one that dotnettency is using for the tenant..

I surface the configuration of the tenant container using IServiceCollection at present, so I think I will need to add an overload so you can also configure it using structuremap api directly.