Multiple Database Migrations Issue with Multi-Tenant Application

[abdullahshaqaliah]

Multiple Database Migrations Issue in Multi-Tenant ABP Application

Overview

We're encountering issues with database migrations in a multi-tenant application where each tenant can connect to up to 3 different databases. The default ABP migration system doesn't properly handle this scenario.

Current Architecture

• Multi-tenant application with ABP Framework
• Each tenant has multiple database connections (up to 3)
• Connection strings stored in TenantConnectionStrings table
• Database Provider: PostgreSQL
• .NET 8

The Problem

The migration system attempts to create all tables in every database connection, regardless of which tables should exist in each database. This leads to errors like:

42P01: relation "PermissionGrants" does not exist
POSITION: 80

Current Database Structure

We have three separate databases per tenant:

1. DMS Database: Contains DMS-specific and identity tables tables
2. Authentication Database: Contains Tenant and OpenIddicttables
3. Administration Database: Contains Permission and Setting and features tables

Current Implementation

EntityFrameworkCoreDMSDbSchemaMigrator

public class EntityFrameworkCoreDMSDbSchemaMigrator
    : IDmsDbSchemaMigrator, ITransientDependency
{
    private readonly IServiceProvider _serviceProvider;

    public EntityFrameworkCoreDMSDbSchemaMigrator(
        IServiceProvider serviceProvider)
    {
        _serviceProvider = serviceProvider;
    }

    public async Task MigrateAsync()
    {

        await _serviceProvider
            .GetRequiredService<DMSDbContext>()
            .Database
            .MigrateAsync();

        await _serviceProvider
            .GetRequiredService<AuthenticationDbContext>()
            .Database
            .MigrateAsync();



        await _serviceProvider
            .GetRequiredService<AdministrationServiceDbContext>()
            .Database
            .MigrateAsync();
    }
}

public class DMSDbMigrationService : ITransientDependency
{
    public ILogger<DMSDbMigrationService> Logger { get; set; }

    private readonly IDataSeeder _dataSeeder;
    private readonly IEnumerable<IDmsDbSchemaMigrator> _dbSchemaMigrators;
    private readonly ITenantRepository _tenantRepository;
    private readonly ICurrentTenant _currentTenant;
    private readonly IAbpDistributedLock _distributedLockProvider;


    public DMSDbMigrationService(
        IDataSeeder dataSeeder,
        IEnumerable<IDmsDbSchemaMigrator> dbSchemaMigrators,
        ITenantRepository tenantRepository,
        ICurrentTenant currentTenant,
        IAbpDistributedLock distributedLockProvider)
    {
        _dataSeeder = dataSeeder;
        _dbSchemaMigrators = dbSchemaMigrators;
        _tenantRepository = tenantRepository;
        _currentTenant = currentTenant;

        Logger = NullLogger<DMSDbMigrationService>.Instance;
        _distributedLockProvider = distributedLockProvider;
    }

    public async Task MigrateAsync()


    {
        var databaseName = "DMS";
        Logger.LogInformation("Started database migrations...");

        await using IAbpDistributedLockHandle handle = await _distributedLockProvider.TryAcquireAsync("Migration_" + databaseName);
        Logger.LogInformation("Lock is acquired for db migration and seeding on database named: " + databaseName + "...");
        if (handle == null)
        {
            Logger.LogInformation("Handle is null because of the locking for : " + databaseName);
            return;
        }

        var initialMigrationAdded = AddInitialMigrationIfNotExist();

        if (initialMigrationAdded)
        {
            return;
        }


        await MigrateDatabaseSchemaAsync();
        await SeedDataAsync();

        Logger.LogInformation($"Successfully completed host 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 all database migrations.");
        Logger.LogInformation("You can safely end this process...");
        Logger.LogInformation($"Lock is released for db migration and seeding on database named: {databaseName}...");

    }

    private async Task MigrateDatabaseSchemaAsync(Tenant tenant = null)
    {
        Logger.LogInformation(
            $"Migrating schema for {(tenant == null ? "host" : tenant.Name + " tenant")} database...");

        foreach (var migrator in _dbSchemaMigrators)
        {
            await migrator.MigrateAsync();
        }
    }

    private async Task SeedDataAsync(Tenant tenant = null)
    {
        Logger.LogInformation($"Executing {(tenant == null ? "host" : tenant.Name + " tenant")} database شseed...");

        await _dataSeeder.SeedAsync(new DataSeedContext(tenant?.Id)
            .WithProperty(IdentityDataSeedContributor.AdminEmailPropertyName, "[email protected]")
            .WithProperty(IdentityDataSeedContributor.AdminPasswordPropertyName, IdentityDataSeedContributor.AdminPasswordDefaultValue)
        );
    }

    private bool AddInitialMigrationIfNotExist()
    {
        try
        {
            if (!DbMigrationsProjectExists())
            {
                return false;
            }
        }
        catch (Exception)
        {
            return false;
        }

        try
        {
            if (!MigrationsFolderExists())
            {
                AddInitialMigration();
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (Exception e)
        {
            Logger.LogWarning("Couldn't determinate if any migrations exist : " + e.Message);
            return false;
        }
    }

    private bool DbMigrationsProjectExists()
    {
        var dbMigrationsProjectFolder = GetEntityFrameworkCoreProjectFolderPath();

        return dbMigrationsProjectFolder != null;
    }

    private bool MigrationsFolderExists()
    {
        var dbMigrationsProjectFolder = GetEntityFrameworkCoreProjectFolderPath();
        return dbMigrationsProjectFolder != null && Directory.Exists(Path.Combine(dbMigrationsProjectFolder, "Migrations"));
    }

    private void AddInitialMigration()
    {
        Logger.LogInformation("Creating initial migration...");

        string argumentPrefix;
        string fileName;

        if (RuntimeInformation.IsOSPlatform(OSPlatform.OSX) || RuntimeInformation.IsOSPlatform(OSPlatform.Linux))
        {
            argumentPrefix = "-c";
            fileName = "/bin/bash";
        }
        else
        {
            argumentPrefix = "/C";
            fileName = "cmd.exe";
        }

        var procStartInfo = new ProcessStartInfo(fileName,
            $"{argumentPrefix} \"abp create-migration-and-run-migrator \"{GetEntityFrameworkCoreProjectFolderPath()}\"\""
        );

        try
        {
            Process.Start(procStartInfo);
        }
        catch (Exception)
        {
            throw new Exception("Couldn't run ABP CLI...");
        }
    }

    private string GetEntityFrameworkCoreProjectFolderPath()
    {
        var slnDirectoryPath = GetSolutionDirectoryPath();

        if (slnDirectoryPath == null)
        {
            throw new Exception("Solution folder not found!");
        }

        var srcDirectoryPath = Path.Combine(slnDirectoryPath, "src");

        return Directory.GetDirectories(srcDirectoryPath)
            .FirstOrDefault(d => d.EndsWith(".EntityFrameworkCore"));
    }

    private string GetSolutionDirectoryPath()
    {
        var currentDirectory = new DirectoryInfo(Directory.GetCurrentDirectory());

        while (currentDirectory != null && Directory.GetParent(currentDirectory.FullName) != null)
        {
            currentDirectory = Directory.GetParent(currentDirectory.FullName);

            if (currentDirectory != null && Directory.GetFiles(currentDirectory.FullName).FirstOrDefault(f => f.EndsWith(".sln")) != null)
            {
                return currentDirectory.FullName;
            }
        }

        return null;
    }
}

What We Need

1. A way to map specific modules to their respective databases
2. Proper migration sequence handling for new tenants
3. Mechanism to ensure each database only receives relevant migrations

Questions

1. What's the recommended approach for handling migrations with multiple database connections per tenant?
2. How can we configure module-to-database mapping for migrations?
3. Is there a way to extend ABP's default migration behavior for this scenario?

Additional Notes

• The system works correctly with single database per tenant
• We need to maintain separate schemas for different modules
• Looking for best practices in managing multiple databases in ABP's multi-tenant architecture

Environment Details

• ABP Version:9.0.4
• Database: PostgreSQL
• Platform: Windows
• Framework: .NET 9

---

Tags

#abp #efcore #migrations #multi-tenant #postgresql #dotnet

@maliming

[abdullahshaqaliah]

@maliming ???