Monolith Solution: Database Migrations¶

Database Migrations¶

Entity Framework Core Tools¶

The command-line interface (CLI) tools for Entity Framework Core perform design-time development tasks. For example, they create migrations, apply migrations, and generate code for a model based on an existing database.

Installing the tools¶

dotnet ef can be installed as either a global or local tool. Most developers prefer installing dotnet ef as a global tool using the following command:

To use it as a local tool, restore the dependencies of a project that declares it as a tooling dependency using a tool manifest file. Tool manifest file can be found under .config folder at project root.

dotnet tool restore

Uninstall a global tool or local tool.

dotnet tool uninstall

You can set the version of the tool package to install.

dotnet tool install --global dotnet-ef --version X.Y.Z

Replace version number with matching version with application modules projects.

How To Add New Migration?¶

This solution configured so it uses two database schema;

Default schema is used to store host & tenant data (when the tenant uses shared database).
Tenant schema is used to store only the tenant data (when the tenant uses a dedicated database).

In this way, dedicated tenant databases do not have host-related empty tables.

To make this possible, there are two migrations DbContext in the EntityFrameworkCore project. So, you need to specify the DbContext when you want to add new migration.

When you add/change a multi-tenant entity (that implements IMultiTenant) in your project, you typically need to add two migrations: one for the default DbContext and the other one is for the tenant DbContext. If you are making change for a host-only entity, then you don't need to add-migration for the tenant DbContext (if you add, you will get an empty migration file).

Adding Migration to the Default DbContext¶

Using EF Core command line tool;

dotnet ef migrations add Initial --context PSSXDbContext --output-dir Migrations\Default
````

You can replace `Initial` with your migration name.

#### Adding Migration to the Tenant DbContext

Using EF Core command line tool:

```powershell
dotnet ef migrations add Initial --context PSSXTenantDbContext --output-dir Migrations\Tenant

You can replace Initial with your migration name.

How To Generate SQL scripts?¶

EF Core supports generating idempotent scripts, which internally check which migrations have already been applied (via the migrations history table), and only apply missing ones. This is useful if you don't exactly know what the last migration applied to the database was, or if you are deploying to multiple databases that may each be at a different migration.

Generate a script that can be used on a database at any migration:

dotnet ef migrations script -i -o migrations-authserver.sql
dotnet ef migrations script -i -o migrations-apihost.sql

Hangfire Database Migrations¶

Add the Hangfire.EntityFrameworkCore NuGet package to your project.

dotnet add package Hangfire.EntityFrameworkCore

Use EF Core migrations to create the necessary tables for Hangfire in your database.

dotnet ef migrations add Hangfire --context PSSXHangfireDbContext --output-dir Migrations\Hangfire

Multi Schema Support¶

You can customize the database schema to fit your application's needs.

Step 1: Run dotnet migration with schema name argument

dotnet ef migrations add HangfireAuthServer --context PSSXHangfireDbContext --output-dir Migrations\Hangfire -- "AuthServer"

In this case, the migration will be created with the schema name AuthServer.

Step 2: Delete PSSXHangfireDbContextModelSnapshot.cs

Step 3: XXXX_HangfireAuthServer migration file will be created in the Migrations\Hangfire folder with the schema name AuthServer.

Add return statement in the Up method to avoid the migration script execution.

protected override void Up(MigrationBuilder migrationBuilder)
{
  return;
  ....
}

Step 4: Run dotnet migration with schema name argument

dotnet ef migrations add HangfireApiHost --context PSSXHangfireDbContext --output-dir Migrations\Hangfire -- "ApiHost"

In this case, the migration will be created with the schema name ApiHost.

Step 5: Target migration names must match with the schema name at PSSXHangfireContextDbSchemaMigrator service.

foreach (var schemaName in schemaNames!)
{
    using var dbContext = new PSSXHangfireDbContext(options, schemaName);
    {
        string targetMigration = "Hangfire" + schemaName;
        await dbContext.Database.GetService<IMigrator>().MigrateAsync(targetMigration);
    }
}

Step 6: Add schema name to the appsettings.json in Siemens.PSSX.DbMigrator project.

"Hangfire": {
  "Schemas": [
    "AuthServer",
    "ApiHost"
  ]
}

Updating the Databases¶

It is suggested to run the DbMigrator application to update the database after adding a new migration. It is simpler and also automatically handles tenant database upgrades.

Database Migrator¶

DbMigrator is a console application and used for database migrations and seeding data. It is located under the src folder in the monolith template solution. DbMigratorModule depends on EntityFrameworkCoreModule and ApplicationContractsModule modules.

MigrateAsync method migrates all the databases.

Identity Module Data Seeding¶

IdentityModule seeding is required for AuthServer since it seeds the admin user/password (identity data) and initial OpenIddict data (applications, scopes).

You can manage secrets with help of .NET SDK Secret Manager.

public async virtual Task<IdentityDataSeedResult> SeedAdminAsync(string adminEmail, string adminPassword, string? adminUserName = null, Guid? tenantId = null)
{
    Check.NotNullOrWhiteSpace(adminEmail, nameof(adminEmail));
    Check.NotNullOrWhiteSpace(adminPassword, nameof(adminPassword));

    using (CurrentTenant.Change(tenantId))
    {
        await IdentityOptions.SetAsync();

        var result = new IdentityDataSeedResult();

        // default admin user name ise adrian
        if (adminUserName == null)
            adminUserName = "adrian";

        var adminUser = await UserRepository.FindByNormalizedUserNameAsync(
            LookupNormalizer.NormalizeName(adminUserName)
        );

        if (adminUser != null)
        {
            return result;
        }

        adminUser = new IdentityUser(
            GuidGenerator.Create(),
            adminUserName,
            adminEmail,
            tenantId
        )
        {
            Name = adminUserName
        };

        adminUser.SetShouldChangePasswordOnNextLogin(true); // Force user to change password on next login

        (await UserManager.CreateAsync(adminUser, adminPassword, validatePassword: false)).CheckErrors();
        result.CreatedAdminUser = true;

        //"admin" role
        const string adminRoleName = "admin";
        var adminRole = await RoleRepository.FindByNormalizedNameAsync(LookupNormalizer.NormalizeName(adminRoleName));
        if (adminRole == null)
        {
            adminRole = new IdentityRole(
                GuidGenerator.Create(),
                adminRoleName,
                tenantId
            )
            {
                IsStatic = true,
                IsPublic = true
            };

            (await RoleManager.CreateAsync(adminRole)).CheckErrors();
            result.CreatedAdminRole = true;
        }

        (await UserManager.AddToRoleAsync(adminUser, adminRoleName)).CheckErrors();

        return result;
    }
}

OpenIddict Data Seeding¶

DbMigrator Data Seeding use OpenIddictDataSeeder. The seeder uses OpenIddict section in appsettings.json file to seed client and resource cors and redirectUri data.

{
  "OpenIddict": {
    "Applications": {
      "PSSX_Web": {
        "ClientId": "PSSX_Web",
        "ClientSecret": "1q2w3e*",
        "RootUrl": "https://localhost:44322"
      },
      "PSSX_Web_Public": {
        "ClientId": "PSSX_Web_Public",
        "ClientSecret": "1q2w3e*",
        "RootUrl": "https://localhost:44338"
      },
      "PSSX_App": {
        "ClientId": "PSSX_App",
        "ClientSecret": "1q2w3e*",
        "RootUrl": "http://localhost:4200"
      },
      "PSSX_Swagger": {
        "ClientId": "PSSX_Swagger",
        "ClientSecret": "1q2w3e*",
        "RootUrl": "https://localhost:44366"
      },
      "PSSX_Blazor": {
        "ClientId": "PSSX_Blazor",
        "ClientSecret": "1q2w3e*",
        "RootUrl": "https://localhost:44377"
      }
    }
  }
}

Creating API Scopes¶

Since this is monolith application only one scope is defined.

private async Task CreateScopesAsync()
{
    if (await _scopeManager.FindByNameAsync("PSSX") == null)
    {
        await _scopeManager.CreateAsync(new OpenIddictScopeDescriptor
        {
            Name = "PSSX",
            DisplayName = "PSSX API",
            Resources =
            {
                "PSSX"
            }
        });
    }
}

Creating Applications¶

Following five application/client created by default.

private async Task CreateApplicationsAsync()
{
    var commonScopes = new List<string>
    {
        OpenIddictConstants.Permissions.Scopes.Address,
        OpenIddictConstants.Permissions.Scopes.Email,
        OpenIddictConstants.Permissions.Scopes.Phone,
        OpenIddictConstants.Permissions.Scopes.Profile,
        OpenIddictConstants.Permissions.Scopes.Roles,
        "PSSX"
    };

    var configurationSection = _configuration.GetSection("OpenIddict:Applications");

    // Web Client
    var webClientId = configurationSection["PSSX_Web:ClientId"];
    if (!webClientId.IsNullOrWhiteSpace())
    {
        var webClientRootUrl = configurationSection["PSSX_Web:RootUrl"]!.EnsureEndsWith('/');

        /* PSSX_Web client is only needed if you created a tiered solution. Otherwise, you can delete this client. */
        await CreateApplicationAsync(
            name: webClientId,
            type: OpenIddictConstants.ClientTypes.Confidential,
            consentType: OpenIddictConstants.ConsentTypes.Implicit,
            displayName: "Web Application",
            secret: configurationSection["PSSX_Web:ClientSecret"],
            grantTypes: new List<string> //Hybrid flow
            {
                OpenIddictConstants.GrantTypes.AuthorizationCode,
                OpenIddictConstants.GrantTypes.Implicit
            },
            scopes: commonScopes,
            redirectUri: $"{webClientRootUrl}signin-oidc",
            postLogoutRedirectUri: $"{webClientRootUrl}signout-callback-oidc",
            clientUri: webClientRootUrl,
            logoUri: "/images/clients/aspnetcore.svg"
        );
    }

    // Console Test / Angular Client
    var consoleAndAngularClientId = configurationSection["PSSX_App:ClientId"];
    if (!consoleAndAngularClientId.IsNullOrWhiteSpace())
    {
        var consoleAndAngularClientRootUrl = configurationSection["PSSX_App:RootUrl"]!.TrimEnd('/');
        await CreateApplicationAsync(
            name: consoleAndAngularClientId,
            type: OpenIddictConstants.ClientTypes.Public,
            consentType: OpenIddictConstants.ConsentTypes.Implicit,
            displayName: "Console Test / Angular Application",
            secret: null,
            grantTypes: new List<string>
            {
                OpenIddictConstants.GrantTypes.AuthorizationCode,
                OpenIddictConstants.GrantTypes.Password,
                OpenIddictConstants.GrantTypes.ClientCredentials,
                OpenIddictConstants.GrantTypes.RefreshToken,
                "LinkLogin",
                "Impersonation"
            },
            scopes: commonScopes,
            redirectUri: consoleAndAngularClientRootUrl,
            postLogoutRedirectUri: consoleAndAngularClientRootUrl,
            clientUri: consoleAndAngularClientRootUrl,
            logoUri: "/images/clients/angular.svg"
        );
    }

    // Swagger Client
    var swaggerClientId = configurationSection["PSSX_Swagger:ClientId"];
    if (!swaggerClientId.IsNullOrWhiteSpace())
    {
        var swaggerRootUrl = configurationSection["PSSX_Swagger:RootUrl"]!.TrimEnd('/');

        await CreateApplicationAsync(
            name: swaggerClientId,
            type: OpenIddictConstants.ClientTypes.Public,
            consentType: OpenIddictConstants.ConsentTypes.Implicit,
            displayName: "Swagger Application",
            // secret: configurationSection["PSSX_Swagger:ClientSecret"],
            secret: null,
            grantTypes: new List<string>
            {
                OpenIddictConstants.GrantTypes.AuthorizationCode,
            },
            scopes: commonScopes,
            redirectUri: $"{swaggerRootUrl}/swagger/oauth2-redirect.html",
            clientUri: swaggerRootUrl,
            logoUri: "/images/clients/swagger.svg"
        );
    }

    // Web Public Tiered Client
    var webPublicTieredClientId = configurationSection["PSSX_Web_Public:ClientId"];
    if (!webPublicTieredClientId.IsNullOrWhiteSpace())
    {
        var webPublicTieredRootUrl = configurationSection["PSSX_Web_Public:RootUrl"]!.EnsureEndsWith('/');

        await CreateApplicationAsync(
            name: webPublicTieredClientId,
            type: OpenIddictConstants.ClientTypes.Confidential,
            consentType: OpenIddictConstants.ConsentTypes.Implicit,
            displayName: "Web Public Tiered Application",
            secret: configurationSection["PSSX_Web_Public:ClientSecret"],
            grantTypes: new List<string> //Hybrid flow
            {
                OpenIddictConstants.GrantTypes.AuthorizationCode,
                OpenIddictConstants.GrantTypes.Implicit
            },
            scopes: commonScopes,
            redirectUri: $"{webPublicTieredRootUrl}signin-oidc",
            postLogoutRedirectUri: $"{webPublicTieredRootUrl}signout-callback-oidc",
            clientUri: webPublicTieredRootUrl,
            logoUri: "/images/clients/aspnetcore.svg"
        );
    }

    // Blazor Client
    var blazorClientId = configurationSection["PSSX_Blazor:ClientId"];
    if (!blazorClientId.IsNullOrWhiteSpace())
    {
        var blazorRootUrl = configurationSection["PSSX_Blazor:RootUrl"]!.TrimEnd('/');

        await CreateApplicationAsync(
            name: blazorClientId,
            type: OpenIddictConstants.ClientTypes.Public,
            consentType: OpenIddictConstants.ConsentTypes.Implicit,
            displayName: "Blazor Application",
            secret: null,
            grantTypes: new List<string>
            {
                OpenIddictConstants.GrantTypes.AuthorizationCode,
            },
            scopes: commonScopes,
            redirectUri: $"{blazorRootUrl}/authentication/login-callback",
            postLogoutRedirectUri: $"{blazorRootUrl}/authentication/logout-callback",
            clientUri: blazorRootUrl,
            logoUri: "/images/clients/blazor.svg"
        );
    }