DEV Community

Cover image for Build a To-Do app - Part 3 - Database per Tenant
Alexandro Martinez
Alexandro Martinez

Posted on

Build a To-Do app - Part 3 - Database per Tenant

In order to generate a separate database for each tenant, we need to make some adjustments.

Every time a user registers, a new Tenant database will be created.

Requirements


Steps

  1. Fix Database per Tenant errors
  2. Removing Foreign Keys
  3. Migrations
  4. Run the application
  5. Disadvantages

1. Fix Database per Tenant errors

While making this tutorial I realized I left an error on the DatabasePerTenant configuration.

You can fix it by updating the these 3 files:

1.1. [1/3] AppDbContext.cs

src/NetcoreSaas.Infrastructure/Data/AppDbContext.cs

...
namespace NetcoreSaas.Infrastructure.Data
{
    public class AppDbContext : BaseDbContext
    {
        ...
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
             ...
             if (ProjectConfiguration.GlobalConfiguration.MultiTenancy == MultiTenancy.DatabasePerTenant)
             {
-                var tenantUuid = _httpContextAccessor.HttpContext.GetTenantUserId();
+                var tenantUuid = _httpContextAccessor.HttpContext.GetTenantUuid();
                 if (tenantUuid != Guid.Empty)
                 {
                     connectionString = ProjectConfiguration.GlobalConfiguration.GetTenantContext(tenantUuid);
                 }
             }
             ...
Enter fullscreen mode Exit fullscreen mode

1.2. [2/3] HttpContextExtensions.cs

src/NetcoreSaas.Infrastructure/Extensions/HttpContextExtensions.cs

...
namespace NetcoreSaas.Infrastructure.Extensions
{
    public static class HttpContextExtensions
    {
         ...
         public static Guid GetTenantUuid(this HttpContext context)
         {
             try
             {
-                return Guid.Parse(context.User.Claims.FirstOrDefault(c => c.Type == "TenantUserUuid")?.Value!);
+                return Guid.Parse(context.User.Claims.FirstOrDefault(c => c.Type == "TenantUuid")?.Value!);
             }
             catch
             {
                 return Guid.Empty;
             }
         }
         ...
Enter fullscreen mode Exit fullscreen mode

1.3. [4/3] WorkspaceController.cs

Update the following line:

src/NetcoreSaas.WebApi/Controllers/Core/Workspaces/WorkspaceController.cs

namespace NetcoreSaas.WebApi.Controllers.Core.Workspaces
{
    [ApiController]
    [Authorize]
    public class WorkspaceController : ControllerBase
    {
        ...
        [HttpGet(ApiCoreRoutes.Workspace.GetAll)]
        public async Task<IActionResult> GetAll()
        {
             var tenantUser = await _masterUnitOfWork.Tenants.GetTenantUser(HttpContext.GetTenantId(), HttpContext.GetUserId());
             if (tenantUser == null)
                 return BadRequest("api.errors.unauthorized");
-            var records = await _appUnitOfWork.Workspaces.GetUserWorkspaces(tenantUser);
+            var records = await _masterUnitOfWork.Workspaces.GetUserWorkspaces(tenantUser, HttpContext.GetTenantId());
             return Ok(_mapper.Map<IEnumerable<WorkspaceDto>>(records));
        }
        ...
Enter fullscreen mode Exit fullscreen mode

And since the Workspace entity belongs to the Master database, replace all "_appUnitOfWork." occurrences with "masterUnitOfWork".

2. Removing Foreign Keys

Since each tenant will have its own database, you will not be able to Include properties like Tenant, Workspace, ModifiedByUserId and CreatedByUserId in your queries.

We need to remove the Foreign Key relationship on the following interfaces/classes:

  • IAppEntity.cs → remove User and Tenant objects
  • IAppWorkspaceEntity.cs → remove Workspace object
  • AppWorkspaceEntity.cs → remove User, Workspace and Tenant objects

2.1. IAppEntity.cs

src/NetcoreSaas.Domain/Models/Interfaces/IAppEntity.cs

...
namespace NetcoreSaas.Domain.Models.Interfaces
{
    public interface IAppEntity : IEntity
    {
         Guid? CreatedByUserId { get; set; }
-        User CreatedByUser { get; set; }
         Guid? ModifiedByUserId { get; set; }
-        User ModifiedByUser { get; set; }
         Guid TenantId { get; set; }
-        Tenant Tenant { get; set; }
    }
}
Enter fullscreen mode Exit fullscreen mode

2.2. IAppWorkspaceEntity.cs

src/NetcoreSaas.Domain/Models/Interfaces/IAppWorkspaceEntity.cs

...
namespace NetcoreSaas.Domain.Models.Interfaces
{
    public interface IAppWorkspaceEntity : IAppEntity
    {
          Guid WorkspaceId { get; set; }
-         Workspace Workspace { get; set; }
    }
}
Enter fullscreen mode Exit fullscreen mode

2.3. AppWorkspaceEntity.cs

src/NetcoreSaas.Domain/Models/Core/AppWorkspaceEntity.cs

...
namespace NetcoreSaas.Domain.Models.Core
{
    public abstract class AppWorkspaceEntity : Entity, IAppWorkspaceEntity
    {
         public Guid? CreatedByUserId { get; set; }
-        public User CreatedByUser { get; set; }
         public Guid? ModifiedByUserId { get; set; }
-        public User ModifiedByUser { get; set; }
         public Guid WorkspaceId { get; set; }
-        public Workspace Workspace { get; set; }
         public Guid TenantId { get; set; }
-        public Tenant Tenant { get; set; }
    }
}
Enter fullscreen mode Exit fullscreen mode

3. Migrations

Our database schema changed because we removed the Foreign Keys. Add another migration called DatabasePerTenant.

cd src/NetcoreSaas.WebApi
dotnet ef migrations add DatabasePerTenant --context MasterDbContext
Enter fullscreen mode Exit fullscreen mode

Ignore the Initial migration:

NetcoreSaas.WebApi/Migrations/...Initial.cs

namespace NetcoreSaas.WebApi.Migrations
{
    public partial class Initial : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
+            return;
             ...
Enter fullscreen mode Exit fullscreen mode

And update the database:

dotnet ef database update --context MasterDbContext
Enter fullscreen mode Exit fullscreen mode

4. Run the application

Before you start the application, update the following value appSettings.Development.json:

  • ProjectConfiguration.MultiTenancyDatabasePerTenant

Run the app and add some tasks.

4.1. Inspect Tenant Database

Using your database tool, you should now see a new database with the prefix PRODUCT_NAME becasue we did not set the App.Name setting at appSettings.Development.json.

Tenant Database

4.2. Create another tenant

If you haven't already, set the following values:

  • [appSettings.Development.json] SubscriptionSettings.PublicKey
  • [appSettings.Development.json] SubscriptionSettings.SecretKey
  • [.env.development] VITE_VUE_APP_SUBSCRIPTION_PUBLIC_KEY
  • [.env.development] VITE_VUE_APP_SUBSCRIPTION_SECRET_KEY

Now:

  1. Restart the application
  2. Go to /admin/pricing
  3. Click on "Click here to generate these prices in Database and your Subscription provider"
  4. Log out
  5. Go to /pricing and Register
  6. Add some tasks

Inspect your server again, there should be another tenant database.

Another Tenant Database

And each with their own tasks:

Tenant 1 Tasks

Tenant 2 Tasks

5. Disadvantages

There are a few disadvantages when using this approach:

5.1. Include

You cannot Include properties that are in the Master database:

  • Tenant
  • Workspace
  • ModifiedByUser
  • CreatedByUser

For example, if you'd like to add an Assignee property to the Task.cs model, you would have to populate the users by hand.

5.2. Migrations

If you change the master database schema, you would have to migrate your changes to all tenant databases by hand. This codebase does not provide an easy way to handle this.

5.3. Maintenance

While it's easier to perform audit operations on a single tenant, it could be a problem when auditing all tenants. Imagine you have 1,000 customers, you'd now have 1,000 databases to maintain.


If you have the SaasFrontends Vue3 essential edition, you can ask for the code.

Let me know if you have any questions!

Top comments (0)