Adding Tenants Table and TenantId Field

We need to add a TenantId field to all tables, to isolate tenants from each other.

So, we first need a Tenants table.

As Northwind tables already have records, we’ll define a primary tenant with ID 1, and set all existing records TenantId to it.

It’s time to write a migration, actually two migrations, one for Northwind and one for Default database.

DefaultDB_20170430_134800_MultiTenant.cs:

  1. using FluentMigrator;
  3. namespace MultiTenancy.Migrations.DefaultDB
  4. {
  5.     [Migration(20170430134800)]
  6.     public class DefaultDB_20170430_134800_MultiTenant
  7.         : AutoReversingMigration
  8.     {
  9.         public override void Up()
  10.         {
  11.             this.CreateTableWithId32("Tenants", "TenantId", s => s
  12.                 .WithColumn("TenantName").AsString(100)
  13.                     .NotNullable());
  15.             Insert.IntoTable("Tenants")
  16.                 .Row(new
  17.                 {
  18.                     TenantName = "Primary Tenant"
  19.                 });
  21.             Insert.IntoTable("Tenants")
  22.                 .Row(new
  23.                 {
  24.                     TenantName = "Second Tenant"
  25.                 });
  27.             Insert.IntoTable("Tenants")
  28.                 .Row(new
  29.                 {
  30.                     TenantName = "Third Tenant"
  31.                 });
  33.             Alter.Table("Users")
  34.                 .AddColumn("TenantId").AsInt32()
  35.                     .NotNullable().WithDefaultValue(1);
  37.             Alter.Table("Roles")
  38.                 .AddColumn("TenantId").AsInt32()
  39.                     .NotNullable().WithDefaultValue(1);
  41.             Alter.Table("Languages")
  42.                 .AddColumn("TenantId").AsInt32()
  43.                     .NotNullable().WithDefaultValue(1);
  44.         }
  45.     }
  46. }

I have created Tenants table in Default database where user tables are. Here we add 3 predefined tenants. We actually only need first one with ID 1.

We didn’t add TenantId column to tables like UserPermissions, UserRoles, RolePermissions etc, as they instrinsicly have TenantId information through their UserId or RoleId (as these tables already have TenantId value)

Let’s write another migration for Nortwhind database to add TenantId column to required tables:

NorthwindDB_20160110_093500_MultiTenant.cs:

  1. using FluentMigrator;
  3. namespace MultiTenancy.Migrations.NorthwindDB
  4. {
  5.     [Migration(20170430194100)]
  6.     public class NorthwindDB_20170430_194100_MultiTenant
  7.         : AutoReversingMigration
  8.     {
  9.         public override void Up()
  10.         {
  11.             Alter.Table("Employees")
  12.                 .AddColumn("TenantId").AsInt32()
  13.                     .NotNullable().WithDefaultValue(1);
  15.             Alter.Table("Categories")
  16.                 .AddColumn("TenantId").AsInt32()
  17.                     .NotNullable().WithDefaultValue(1);
  19.             Alter.Table("Customers")
  20.                 .AddColumn("TenantId").AsInt32()
  21.                     .NotNullable().WithDefaultValue(1);
  23.             Alter.Table("Shippers")
  24.                 .AddColumn("TenantId").AsInt32()
  25.                     .NotNullable().WithDefaultValue(1);
  27.             Alter.Table("Suppliers")
  28.                 .AddColumn("TenantId").AsInt32()
  29.                     .NotNullable().WithDefaultValue(1);
  31.             Alter.Table("Orders")
  32.                 .AddColumn("TenantId").AsInt32()
  33.                     .NotNullable().WithDefaultValue(1);
  35.             Alter.Table("Products")
  36.                 .AddColumn("TenantId").AsInt32()
  37.                     .NotNullable().WithDefaultValue(1);
  39.             Alter.Table("Region")
  40.                 .AddColumn("TenantId").AsInt32()
  41.                     .NotNullable().WithDefaultValue(1);
  43.             Alter.Table("Territories")
  44.                 .AddColumn("TenantId").AsInt32()
  45.                     .NotNullable().WithDefaultValue(1);
  46.         }
  47.     }
  48. }