Adding Tenants Table and TenantId Field
To ensure tenant isolation, we need to add a TenantId
field to all relevant tables.
First, we will create a Tenants
table.
Since the MovieDB already contains records, we will define a primary tenant with ID 1 and assign this TenantId
to all existing records.
We will now create a migration for the Default database.
DefaultDB_20221230_134800_MultiTenant.cs
:
using FluentMigrator;
namespace MovieTutorial.Migrations.DefaultDB;
[DefaultDB, MigrationKey(20221230_134800)]
public class DefaultDB_20221230_134800_MultiTenant : AutoReversingMigration
{
public override void Up()
{
this.CreateTableWithId32("Tenants", "TenantId", s => s
.WithColumn("TenantName").AsString(100)
.NotNullable());
Insert.IntoTable("Tenants")
.Row(new
{
TenantName = "Primary Tenant"
});
Insert.IntoTable("Tenants")
.Row(new
{
TenantName = "Second Tenant"
});
Insert.IntoTable("Tenants")
.Row(new
{
TenantName = "Third Tenant"
});
Alter.Table("Users")
.AddColumn("TenantId").AsInt32()
.NotNullable().WithDefaultValue(1);
Alter.Table("Roles")
.AddColumn("TenantId").AsInt32()
.NotNullable().WithDefaultValue(1);
Alter.Table("Languages")
.AddColumn("TenantId").AsInt32()
.NotNullable().WithDefaultValue(1);
}
}
We have created the Tenants table in the Default database, where the user tables reside. Here, we add three predefined tenants, although only the first one with ID 1 is necessary.
We did not add the TenantId
column to tables such as UserPermissions
, UserRoles
, and RolePermissions
, as they inherently contain TenantId
information through their UserId
or RoleId
(since these tables already have TenantId
values).
Next, we will create another migration for the MovieDB tables to add the TenantId
column:
DefaultDB_20170430_194100_MovieDB_MultiTenant.cs
:
using FluentMigrator;
namespace MovieTutorial.Migrations.DefaultDB;
[DefaultDB, MigrationKey(20221230_194100)]
public class DefaultDB_20221230_194100_MovieDB_MultiTenant : AutoReversingMigration
{
public override void Up()
{
Alter.Table("Person")
.AddColumn("TenantId").AsInt32()
.NotNullable().WithDefaultValue(1);
Alter.Table("Genre")
.AddColumn("TenantId").AsInt32()
.NotNullable().WithDefaultValue(1);
Alter.Table("Movie")
.AddColumn("TenantId").AsInt32()
.NotNullable().WithDefaultValue(1);
}
}