How to create a separate tenant database in module project? #2406

24 Answer(s)

• 

  0

  liangshiwei created 2 years ago

  Support Team Fullstack Developer

  Hi,

  Can you share a project to reproduce? shiwei.liang@volosoft.com thanks

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei,

  I have sent you the source code, please help us take a look.

  Thank you

  Save Cancel
• 

  0

  liangshiwei created 2 years ago

  Support Team Fullstack Developer

  Hi,

  Please try:

  public class MyDbMigrationService : ITransientDependency
  {
      public ILogger<MyDbMigrationService> Logger { get; set; }
  
      private readonly IDataSeeder _dataSeeder;
      private readonly ITenantRepository _tenantRepository;
      private readonly ICurrentTenant _currentTenant;
      private readonly IServiceProvider _serviceProvider;
  
      public MyDbMigrationService(
          IDataSeeder dataSeeder,
          ITenantRepository tenantRepository,
          ICurrentTenant currentTenant,
          IServiceProvider serviceProvider)
      {
          _dataSeeder = dataSeeder;
          _tenantRepository = tenantRepository;
          _currentTenant = currentTenant;
          _serviceProvider = serviceProvider;
  
          Logger = NullLogger<MyDbMigrationService>.Instance;
      }
  
      public async Task MigrateAsync()
      {
          Logger.LogInformation("Started database migrations...");
  
          var tenants = await _tenantRepository.GetListAsync(includeDetails: true);
  
          var migratedDatabaseSchemas = new HashSet<string>();
          foreach (var tenant in tenants)
          {
              using (_currentTenant.Change(tenant.Id))
              {
                  if (tenant.ConnectionStrings.Any())
                  {
                      var tenantConnectionStrings = tenant.ConnectionStrings
                          .Select(x => x.Value)
                          .ToList();
  
                      if (!migratedDatabaseSchemas.IsSupersetOf(tenantConnectionStrings))
                      {
                          await MigrateDatabaseSchemaAsync(tenant);
  
                          migratedDatabaseSchemas.AddIfNotContains(tenantConnectionStrings);
                      }
                  }
  
                  await SeedDataAsync(tenant);
              }
  
              Logger.LogInformation($"Successfully completed {tenant.Name} tenant database migrations.");
          }
  
          Logger.LogInformation("Successfully completed database migrations.");
      }
  
      private async Task MigrateDatabaseSchemaAsync(Tenant tenant = null)
      {
          Logger.LogInformation(
              $"Migrating schema for {(tenant == null ? "host" : tenant.Name + " tenant")} database...");
  
          var dbContext = _serviceProvider.GetRequiredService<UnifiedDbContext>();
          await dbContext.Database.MigrateAsync();
      }
  
      private async Task SeedDataAsync(Tenant tenant = null)
      {
          Logger.LogInformation($"Executing {(tenant == null ? "host" : tenant.Name + " tenant")} database seed...");
  
          await _dataSeeder.SeedAsync(tenant?.Id);
      }
  }
  
  [Dependency(ReplaceServices = true)]
  [ExposeServices(typeof(ConnectionStringsModal))]
  public class MyConnectionStringsModal : ConnectionStringsModal
  {
      private readonly MyDbMigrationService _migrationService;
  
      public MyConnectionStringsModal(ITenantAppService tenantAppService, MyDbMigrationService migrationService) :
          base(tenantAppService)
      {
          _migrationService = migrationService;
      }
  
      public override async Task<IActionResult> OnPostAsync()
      {
          ValidateModel();
  
          if (Tenant.UseSharedDatabase || Tenant.DefaultConnectionString.IsNullOrWhiteSpace())
          {
              await TenantAppService.DeleteDefaultConnectionStringAsync(Tenant.Id);
          }
          else
          {
              await TenantAppService.UpdateDefaultConnectionStringAsync(Tenant.Id, Tenant.DefaultConnectionString);
              await _migrationService.MigrateAsync();
          }
  
          return NoContent();
      }
  }
  
  context.Services.AddAbpDbContext<UnifiedDbContext>();
  

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei, sorry for my late response.

  Is your code must be put under *.Web.Unified? we are using Angular as UI, is there a fix for that?

  Save Cancel
• 

  0

  liangshiwei created 2 years ago

  Support Team Fullstack Developer

  Hi,

  You can put it under the IdentityServer project.

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei, the "ConnectionStringsModal" could not be found in the IdentityServer project, do I need to add any extra dll?

  

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi, pls ignore the above question, I managed to resolve it, just one more clarification.

  The "UnifiedDbContext" is located under Web.Unified project, does it mean we need to add Web.Unified project as reference?

  

  Save Cancel
• 

  0

  liangshiwei created 2 years ago

  Support Team Fullstack Developer

  Hi,

  You need to replace UnifiedDbContext with IdentityServerHostMigrationsDbContext

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei, it does not work, still got the error below:

  [12:21:29 ERR] Connection ID "17798225737568747565", Request ID "8000002e-0002-f700-b63f-84710c7967bb": An unhandled exception was thrown by the application.
  Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'IdentityServerClients'.
     at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
     at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
     at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
  --- End of stack trace from previous location where exception was thrown ---
     at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
  --- End of stack trace from previous location where exception was thrown ---
     at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
     at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
     at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
     at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
     at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
     at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
     at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
     at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
     at Volo.Abp.IdentityServer.Clients.ClientRepository.FindByCliendIdAsync(String clientId, Boolean includeDetails, CancellationToken cancellationToken)
     at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
     at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
     at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
     at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
     at Volo.Abp.IdentityServer.Clients.ClientStore.FindClientByIdAsync(String clientId)
     at IdentityServer4.Stores.ValidatingClientStore`1.FindClientByIdAsync(String clientId)
     at IdentityServer4.Stores.IClientStoreExtensions.FindEnabledClientByIdAsync(IClientStore store, String clientId)
     at IdentityServer4.Validation.AuthorizeRequestValidator.LoadClientAsync(ValidatedAuthorizeRequest request)
     at IdentityServer4.Validation.AuthorizeRequestValidator.ValidateAsync(NameValueCollection parameters, ClaimsPrincipal subject)
     at IdentityServer4.Services.OidcReturnUrlParser.ParseAsync(String returnUrl)
     at IdentityServer4.Services.ReturnUrlParser.ParseAsync(String returnUrl)
     at IdentityServer4.Services.DefaultIdentityServerInteractionService.GetAuthorizationContextAsync(String returnUrl)
     at Volo.Abp.Account.Web.Pages.Account.IdentityServerSupportedLoginModel.OnGetAsync()
     at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory.NonGenericTaskHandlerMethod.Execute(Object receiver, Object[] arguments)
     at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeHandlerMethodAsync()
     at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeNextPageFilterAsync()
     at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Rethrow(PageHandlerExecutedContext context)
     at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
     at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeInnerFilterAsync()
     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ExceptionContextSealed context)
     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
     at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
     at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
     at Volo.Abp.AspNetCore.Auditing.AbpAuditingMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
     at Volo.Abp.AspNetCore.Auditing.AbpAuditingMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
     at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.<>c__DisplayClass5_1.<<UseMiddlewareInterface>b__1>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
     at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
     at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
     at IdentityServer4.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events)
     at IdentityServer4.Hosting.MutualTlsTokenEndpointMiddleware.Invoke(HttpContext context, IAuthenticationSchemeProvider schemes)
     at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
     at IdentityServer4.Hosting.BaseUrlMiddleware.Invoke(HttpContext context)
     at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
     at Microsoft.AspNetCore.RequestLocalization.AbpRequestLocalizationMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
     at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.<>c__DisplayClass5_1.<<UseMiddlewareInterface>b__1>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at Volo.Abp.AspNetCore.MultiTenancy.MultiTenancyMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
     at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.<>c__DisplayClass5_1.<<UseMiddlewareInterface>b__1>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at Microsoft.AspNetCore.Builder.ApplicationBuilderAbpJwtTokenMiddlewareExtension.<>c__DisplayClass0_0.<<UseJwtTokenMiddleware>b__0>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
     at Volo.Abp.AspNetCore.Tracing.AbpCorrelationIdMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
     at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.<>c__DisplayClass5_1.<<UseMiddlewareInterface>b__1>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at Microsoft.AspNetCore.Server.IIS.Core.IISHttpContextOfT`1.ProcessRequestAsync()
  ClientConnectionId:3cc0c63b-c8bb-4a39-b808-af40264b7192
  Error Number:208,State:1,Class:16
  [12:21:29 INF] Request finished in 109.9776ms 500 
  
  

  Save Cancel
• 

  0

  liangshiwei created 2 years ago

  Support Team Fullstack Developer

  Hi,

  Please remove the tenant database and update the connection string.

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei, I tried but it does not work, the OnPostAsync of MyConnectionStringsModal has never been called if I update the tenant connection string.

  Save Cancel
• 

  0

  liangshiwei created 2 years ago

  Support Team Fullstack Developer

  Hi,

  Sorry, I forget you are using Angular UI.

  please try:

  [Dependency(ReplaceServices = true)]
  [ExposeServices(typeof(ITenantAppService))]
  public class MyTenantAppService : TenantAppService
  {
      private readonly MyDbMigrationService _migrationService;
  
      public MyTenantAppService(
          ITenantRepository tenantRepository,
          IEditionRepository editionRepository,
          ITenantManager tenantManager,
          IDataSeeder dataSeeder,
          MyDbMigrationService migrationService) :
          base(tenantRepository, editionRepository, tenantManager, dataSeeder)
      {
          _migrationService = migrationService;
      }
  
      public override async Task UpdateDefaultConnectionStringAsync(Guid id, string defaultConnectionString)
      {
          await base.UpdateDefaultConnectionStringAsync(id, defaultConnectionString);
  
          await _migrationService.MigrateAsync();
      }
  }
  

  

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei, the database has been created, thanks.

  However, it's not what I want, I want to have a separate database for the Module not for the Main.

  can we have separate database for tenant and they share the same Main database?

  or they can have separate database for both Module and Main:

  Is it possible?

  Thank you.

  Save Cancel
• 

  0

  liangshiwei created 2 years ago

  Support Team Fullstack Developer

  Hi,

  Yes, this is possible.

  You need custom the Angular UI and tenant app service.

  For example(pseudo-code):

  public async Task UpdateConnectionStringAsync(UpdateConnectionStringAsyncInput input)
  {
      var tenant = await TenantRepository.GetAsync(input.Id);
  
      // For your case, should be: PartnersBuddy; Server=localhost;Database=....
      tenant.SetConnectionString(input.ConnectionName, input.ConnectionString);
  
      // you need to use distributed event bus (rabbitmq or other)
      _distributedEventBus.PublishAsync(new TenantConnectionStringChangedEto() { ..... });
  
  }
  

  Handle the event in the HttpApi.Host:

  public class MyTenantConnectionStringChangedEventHandler : IDistributedEventHandler<TenantConnectionStringChangedEto>, ITransientDependency
  {
      private readonly IDataSeeder _dataSeeder;
      private readonly IServiceProvider _serviceProvider;
      private readonly ICurrentTenant _currentTenant;
  
      public MyTenantConnectionStringChangedEventHandler(
          IDataSeeder dataSeeder,
          ICurrentTenant currentTenant,
          IServiceProvider serviceProvider)
      {
          _dataSeeder = dataSeeder;
          _currentTenant = currentTenant;
          _serviceProvider = serviceProvider;
      }
  
      public async Task HandleEventAsync(TenantConnectionStringChangedEto eventData)
      {
          using (_currentTenant.Change(eventData.TenantId))
          {
              var dbContext = _serviceProvider.GetRequiredService<MyProjectHttpApiHostMigrationsDbContext>();
              await dbContext.Database.MigrateAsync();
              await _dataSeeder.SeedAsync();
          }
      }
  }
  
  context.Services.AddAbpDbContext<MyProjectHttpApiHostMigrationsDbContext>();
  

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei, thanks for your prompt reply!

  I am still not quite clear about the solution. My understanding is the Abp Framework allows only 1 connection string when update the tenant, how does it know that this connection string is for the Main or Module?

  Let's say I want to go with the option 1: having separate database for Module and share the same database for Main,

  And I tried it before by setting the connection string pointing to the separate Module database (this database I created manually by executing the "dotnet ef database update" command)

  Tenant connection string:

  Module database

  But when I tried to login to tenant, it prompted the error could not find the IdentityServer tables in the new database as I reported above.

  Could you help me elaborate more on this solution?

  Thank you.

  Save Cancel
• 

  0

  liangshiwei created 2 years ago

  Support Team Fullstack Developer

  Hi,

  We support setting individual connection strings for each module in later versions.

  You need custom the Angular UI and tenant app service.

  But for 3.3.2, as I said you need to custom the Angular UI.

  For example, you can add your own Update module connection string action to set the module connection string. see: https://docs.abp.io/en/abp/latest/UI/Angular/Entity-Action-Extensions

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei, May I know from which version that Abp supports individual connection string for each module? I will try to take a look on it.

  Thank you.

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei, please ignore it, I have found the release:

  4.4 (2021-08-02)
  See the detailed blog post / announcement for the v4.4.
  ...
  Allow to set multiple connection strings for each tenant, to separate a tenant's database per module/microservice.
  ...
  

  Let me take a look on it.

  Thank you.

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei, I just tried the module template version 4.4.4 to check the individual connection strings for module, but I found there is still only 1 connection string when creating new tenant, where is a place to configure the connection string for module?

  

  And I found one issue is when I keyed in the connection string and clicked "Apply database migrations":

  I wait for a while but nothing happened, no database created, no error logs in both HttpApi and IdentityServer, is it a known bug?

  Save Cancel
• 

  0

  liangshiwei created 2 years ago

  Support Team Fullstack Developer

  HI,

  Try:

  Configure<AbpDbConnectionOptions>(options =>
  {
      options.Databases.Configure("<Module connection name>", configure =>
      {
          configure.IsUsedByTenants = true;
      });
  });
  

  

  I wait for a while but nothing happened, no database created, no error logs in both HttpApi and IdentityServer, is it a known bug?

  No, this is not a bug, because the module template hosts(include angular UI) are used for development, this is not production-ready, see: https://docs.abp.io/en/abp/latest/Startup-Templates/Module#host-projects

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei, do you mean that the HttpApi.Host is not production-ready?

  The module template is used for creating a service/microservices: https://docs.abp.io/en/commercial/latest/startup-templates/module/creating-a-new-solution#without-user-interface

  if it's not production-ready then how to deploy it as a microservice?

  Save Cancel
• 

  0

  liangshiwei created 2 years ago

  Support Team Fullstack Developer

  Hi,

  The HttpApi.Host project is production-ready. you can use it.

  But when you use the HttpApi.Host project as a service, you may need to do some extra work, e.g: configure distributed event bus. (you can refer to the microservice template.)

  Save Cancel
• 

  0

  nhontran created 2 years ago

  Hi @liangshiwei, I have followed your instruction, I can see a section to configure the module connection string. However, the "Apply database migrations" does not work, can advise on how to resolve the issue? or what are the next steps?

  Save Cancel
• 

  0

  liangshiwei created 2 years ago

  Support Team Fullstack Developer

  Hi,

  Apply database migrations just published a DB migration event, we have implemented it in the app-pro template but not module-pro template.

  You can try:

  public class MyTenantConnectionStringChangedEventHandler : 
  IDistributedEventHandler<TenantConnectionStringChangedEto>,
  IDistributedEventHandler<ApplyDatabaseMigrationsEto>,
  ITransientDependency
  {
      private readonly IDataSeeder _dataSeeder;
      private readonly IServiceProvider _serviceProvider;
      private readonly ICurrentTenant _currentTenant;
  
      public MyTenantConnectionStringChangedEventHandler(
          IDataSeeder dataSeeder,
          ICurrentTenant currentTenant,
          IServiceProvider serviceProvider)
      {
          _dataSeeder = dataSeeder;
          _currentTenant = currentTenant;
          _serviceProvider = serviceProvider;
      }
  
      public async Task HandleEventAsync(TenantConnectionStringChangedEto eventData)
      {
          using (_currentTenant.Change(eventData.TenantId))
          {
              var dbContext = _serviceProvider.GetRequiredService<MyProjectHttpApiHostMigrationsDbContext>();
              await dbContext.Database.MigrateAsync();
              await _dataSeeder.SeedAsync();
          }
      }
      
      public async Task HandleEventAsync(ApplyDatabaseMigrationsEto eventData)
      {
          if (eventData.TenantId == null)
          {
              return;
          }
  
          using (_currentTenant.Change(eventData.TenantId))
          {
              var dbContext = _serviceProvider.GetRequiredService<MyProjectHttpApiHostMigrationsDbContext>();
              await dbContext.Database.MigrateAsync();
              await _dataSeeder.SeedAsync();
          }
      }
  }
  
  

  Save Cancel