Alexandro Martinez

Posted on Mar 5, 2022

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

#vue3 #dotnet #saas #tutorial

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

Any SaasFrontends Essential or Premium edition.

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);
                 }
             }
             ...

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;
             }
         }
         ...

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));
        }
        ...

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; }
    }
}

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; }
    }
}

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; }
    }
}

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

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;
             ...

And update the database:

dotnet ef database update --context MasterDbContext

4. Run the application

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

ProjectConfiguration.MultiTenancy → DatabasePerTenant

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.

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:

Restart the application
Go to /admin/pricing
Click on "Click here to generate these prices in Database and your Subscription provider"
Log out
Go to /pricing and Register
Add some tasks

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

And each with their own 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!

DEV Community