Customizing the migration process

Executing operations before and after the migration

It is possible to perform operations before and / or after a migration using the C# decorator pattern with the IMigrator interface.
Each module of a cluster can define the operations it needs to execute.

Note

The decorator pattern is a design pattern that allows behavior to be added to the database migration, dynamically, without affecting its behavior. The decorator pattern is often useful by allowing the functionality of the migration to be extended without being modified.

Warning

In a multi-tenant context, you will not be able to use entity nor entity view repositories to perform operations on all tenants at the same time.

Before starting you must decide which module will contain the decorator.
Then open the Application layer business assembly C# project of the module.
Make sur that the project contains a reference to the Scrutor package.
Then you can create your decorator class :

public class MyMigratorDecorator : IMigrator
{
    private readonly IMigrator _baseMigrator;

    public MyMigratorDecorator(IMigrator baseMigrator)
    {
        _baseMigrator = baseMigrator;
    }

    /// <inheritdoc />
    public async Task<Result<int>> ExecuteAsync(MigrationOptions? options = null)
    {
        // Place operations to launch before the migration here

        Result<int> result = await _baseMigrator.ExecuteAsync(options);
        if (result.IsFailed)
        {
            return result;
        }

        // Place operations to launch after the migration here

        return result;
    }
}

Warning

Always check the result of the call to the base implementation before the post-migration code.

Next, you need to create or update the Startup class at the root of the project and register your decorator in the ConfigureServices method for IMigrator using the Decorate method :

public static class Startup
{
    public static void ConfigureServices(IServiceCollection services)
    {
        services.Decorate<IMigrator, MyMigratorDecorator>();
    }
}

Customizing the migration

The IMigrationInterceptor interface exposes methods that can be used to add code or commands that will be executed at different stages of the migration.
This approach is more versatile that using a migration decorator that can only execute operations before and after a migration.
Each module of a cluster can define one or several migration interceptors that will all be used during a migration.
Interceptors and commands are created in the Application layer business assembly C# project of a module.

Note

This is the preferred method for customized database migration. It's simple to implement and well integrated into the migration process. What's more, in the case of a command implementation, you benefit from an instance of the FluentMigrator.Migration class, with all the features provided by FluentMigrator.

Creating commands to execute during the migration

An interceptor allows the customization of the commands Neos will execute during the migration.
Custom commands can be created by implementing the ICommand interface. Another interface IRunOnceCommand allows you to implement commands that will be executed only once.

For example, when an application is deployed in production, if we mark as required an entity property that previously was not, we will need to updated existing database records to remove null values for this property during the migration.
In order to do that, we will create a new command class :

private class ReplaceNullOrderNotesCommand : ICommand
{
    public void Execute(FluentMigrator.Migration migration, ICommandExecutionArgs args)
    {
        if (args.DatabaseType == DatabaseType.SqlServer)
        {
            // Setting a value using raw SQL
            // Useful when access to other columns of the row is needed
            // Here we set the Name column value with a concatenation of a text value and the ID column value
            migration.Execute.Sql("UPDATE DemoEntity SET Name = 'Default name ' + CONVERT(NVARCHAR(100), ID) WHERE Name IS NULL");
        }
        else if (args.DatabaseType == DatabaseType.PostgreSQL)
        {
            // Setting a value using the Fluent Migrator syntax
            // Here we set the Name column value with a text value
            migration.Update.Table("DemoEntity").Set(new { Name = "Default name" }).Where(new { Name = DBNull.Value });
        }
    }
}

The Execute method contains the code that will be executed by the migration. Here we set an empty string in the Notes column of the Order table since the column can no longer contain null values.

The migration parameter is a Fluent Migrator object that allows you to interact with the database. You can check the syntax to use to manipulate data here.

Warning

You should only need to use the Insert, Update and Delete methods to handle data in tables.
Database structure changes should always be handled in Neos Studio.

The args parameters contains information on the context of migration, notably the type of the database being updated.

This other example lets you create database objects not yet managed by Neos Studio. The command below will create the GetProductName function, which will be used in Neos Studio via a ProgrammableObject.

public class CreateGetProductNameFunction : IRunOnceCommand
{
    public string CommandName => nameof(CreateGetProductNameFunction);

    public void Execute(FluentMigrator.Migration migration, ICommandExecutionArgs args)
    {
        switch (args.DatabaseType)
        {
            case DatabaseType.Oracle:
                migration.Execute
                    .Sql(@"CREATE FUNCTION GetProductName(IdOfProduct IN NUMBER) RETURN NVARCHAR2
IS
  product_name NVARCHAR2(100);
BEGIN
  SELECT ""ProductName""
  INTO product_name
  FROM ""Product""
  WHERE ""ProductId"" = IdOfProduct;

  RETURN(product_name);
END;");
                break;

            case DatabaseType.PostgreSQL:
                migration.Execute
                    .Sql(@"CREATE FUNCTION ""GetProductName"" (""IdOfProduct"" integer)
RETURNS character varying(100)
AS $$
DECLARE 
  ""NameOfProduct"" character varying(100);
BEGIN
  SELECT ""ProductName"" INTO ""NameOfProduct""
  FROM ""Product""
  WHERE ""ProductId"" = ""IdOfProduct"";
						
  RETURN ""NameOfProduct"";
END;
$$ LANGUAGE plpgsql;");
                break;

            case DatabaseType.SqlServer:
                migration.Execute
                    .Sql(@"CREATE FUNCTION [dbo].[GetProductName] (@IdOfProduct int)
RETURNS VARCHAR(100)
AS
BEGIN
  DECLARE @NameOfProduct VARCHAR(100)
						
  SELECT @NameOfProduct = ProductName
  FROM Product
  WHERE ProductId = @IdOfProduct
						
  RETURN @NameOfProduct
END");
                break;

            default:
                throw new NotSupportedException();
        }
    }
}

As this command implements the IRunOnceCommand interface, it will only be executed once.

Note

Compared with the ICommand interface, IRunOnceCommand requires implementation of the CommandName property. This type of command is stored in the $NeosObject system table, after successful execution. Before execution, the presence of the command in the table is tested, thus guaranteeing a unique execution.

Creating a migration interceptor class

Interceptors are classes that implement the IMigrationInterceptor interface.
This interface exposes methods that allow you to execute code or commands at different stages of the migration.

`OnCommandsCreatedAsync`

Executed after the existing and expected database schemas are compared and the standard migration commands to execute have been determined.
Allows to add custom commands to the list of commands or totally replace it.

The data parameter contains information on the context of the migration.

The commandList parameter contains the command list. You can add custom commands to the list. See here for more information.

Here is an example adding the custom command that we created earlier that replaces null values in the Notes column of the Order table before the commands that (re)activate constraints :

public class MyMigrationInterceptor : IMigrationInterceptor
{
    public Task<ICommandList> OnCommandsCreatedAsync(CommandsCreatedEventData data, ICommandList commandList)
    {
        if (data.ExistingSchema.Columns.Exists(c => c.TableName == "Order" && c.Name == "Notes" && c.Nullable)
            && data.ExpectedSchema.Columns.Exists(c => c.TableName == "Order" && c.Name == "Notes" && !c.Nullable))
        {
            commandList.AddBefore<SetNotNullConstraint>(new ReplaceNullOrderNotesCommand());
        }

        return Task.FromResult(commandList);
    }
}

Registering a migration interceptor

After a migration interceptor has been created, you need to create or update the Startup class at the root of the project and register it in the ConfigureServices method using the AddMigrationInterceptor method :

public static class Startup
{
    public static void ConfigureServices(IServiceCollection services)
    {
        services.AddMigrationInterceptor<MyMigrationInterceptor>();
    }
}

Command execution order during the migration

Standard migration commands determined by Neos to migrate from the current database structure to the one configured in the metadata are always executed in the same order. The ICommandList interface allows you to add custom commands between them.

--> ICommandList.AddFirst(ICommand) adds a command here at the start of the list.

ExcludeTable : excludes a table from the migration.
ExcludePrimaryKey : excludes a primary key from the migration.
ExcludeForeignKey : excludes a foreign key from the migration.
ExcludeIndex : excludes an index from the migration.
DropView : drops a view.
DropForeignKey : drops a foreign key.
DropPrimaryKey : drops a primary key.
DropIndex : drops an index.
DeleteNotNullConstraint : deletes a not null constraint.
DeleteDefaultValueConstraint : deletes a default value constraint.
RenameColumn : renames a column.
CreateTable : creates a table.
CreateColumn : creates a column.
ChangeColumnTypeToLocalizableString : changes a column type to a type able to manage localizable strings (JSON).
AlterColumn : alters a column.
DropTable : drops a table.
DropColumn : drops a column.
CreateView : creates a view.
SetNotNullConstraint : sets a not null constraint.
CreatePrimaryKey : creates a primary key.
RenameIndex : Renames an index.
CreateIndex : creates an index.
CreateForeignKey : creates a foreign key.
UpdateSchemaVersion : updates the schema version.

--> ICommandList.AddLast(ICommand) adds a command here at the end of the list.

You can use the ICommandList.AddBefore<TCommand>(ICommand) / ICommandList.AddAfter<TCommand>(ICommand) methods to insert custom commands at any stage of the migration. If the specified command is not found, the custom command will be inserted at the next step, respecting the order of precedence.

Note

The parameter type TCommand of the generic methods ICommandList.AddBefore<TCommand>(ICommand) / ICommandList.AddAfter<TCommand>(ICommand) is of type ICommand.

Warning

The ICommandList.AddBeforeContraintsCreation(ICommand) method is obsolete use the ICommandList.AddBefore<ISetNotNullConstraint>(ICommand) method instead.

Given the following standard commands to be executed
When I add the custom command CustomCommand before/after a specified standard command
Then I must have the following command list

CreateTable
CreateColumn
AlterColumn
DropColumn
CreatePrimaryKey
CreateIndex

commandList.AddBefore<IDropColumn>(new CustomCommand());

CreateTable
CreateColumn
AlterColumn
CustomCommand <--
DropColumn
CreatePrimaryKey
CreateIndex

AlterColumn
DropColumn
CreatePrimaryKey
CreateIndex

commandList.AddBefore<ICreateTable>(new CustomCommand());

CustomCommand <--
AlterColumn
DropColumn
CreatePrimaryKey
CreateIndex

Note

As the standard command CreateTable does not exist, the custom command CustomCommand is inserted just before the following existing command AlterColumn at the start of the list.

CreateTable
CreateColumn
AlterColumn
DropColumn
CreateIndex

commandList.AddAfter<ICreatePrimaryKey>(new CustomCommand());

AlterColumn
DropColumn
CustomCommand <--
CreateIndex

Note

As the standard command CreatePrimaryKey does not exist, the custom command CustomCommand is inserted just before the following existing command CreateIndex.

CreateTable
CreateColumn
AlterColumn
DropColumn
CreatePrimaryKey

commandList.AddBefore<ICreateIndex>(new CustomCommand());

CreateTable
CreateColumn
AlterColumn
DropColumn
CreatePrimaryKey
CustomCommand <--

Note

As the standard command CreateIndex does not exist, the custom command CustomCommand is inserted at the end of the list.

Warning

When you specify a custom command to search for and add before/after, a error will be thrown (i.e. InvalidOperationException). Only standard commands listed above are allowed.

Executing operations after tenant database migration

It is possible to perform operations after the migration for all the tenants associated with the database thanks to the OnTenantDatabaseMigratedAsync method of the ITenantDatabaseMigrationInterceptor interceptor.
For example, when a shared database has 3 associated tenants, the interceptor will be executed 3 times.

Creating a tenant database migration interceptor

When an interceptor is executed, its OnTenantDatabaseMigratedAsync method is called. It is in this method that you can set your logic.
ITenants.GetCurrentTenant, INeosTenantInfoAccessor.NeosTenantInfo, repositories... are set for the current tenant.

Example :

internal class MyTenantDatabaseMigrationInterceptor : ITenantDatabaseMigrationInterceptor
{
    private readonly IProductRepository _productRepository;

    public MyTenantDatabaseMigrationInterceptor(IProductRepository productRepository,IUnitOfWork unitOfWork)
    {
        _productRepository = productRepository;
        _unitOfWork = unitOfWork;
    }

    public async Task<Result> OnTenantDatabaseMigratedAsync(TenantDatabaseMigratedEventData tenantDatabaseMigratedEventData)
    {
        if (!_productRepository.GetQuery().Any(c => c.Name == "MyProduct"))
        {
            _productRepository.Add(new()
            {
                Name = "MyProduct",
            });
        }

        return await _unitOfWork.SaveAsync();
    }
}

Registering a tenant database migration interceptor

After a tenant migration interceptor has been created, you need to create or update the Startup class at the root of the project and register it in the ConfigureServices method using the AddTenantDatabaseMigrationInterceptor method :

public static class Startup
{
    public static void ConfigureServices(IServiceCollection services)
    {
        services.AddTenantDatabaseMigrationInterceptor<MyTenantDatabaseMigrationInterceptor>();
    }
}

When is the tenant database migration interceptor executed ?

In multi-tenant mode

When the application is executed using the neos run -mt command or when the application is deployed and configured with multiTenancy=true option, the interceptor is executed in several cases :

After the database migration process
In a shared database, the interceptor is called sequentially for each tenant. There is no parallelization to avoid database access problems and facilitate debugging.
In a separate database, the interceptor is called only once per database on the single tenant it is associated with.

Example with a migration request on a shared database containing 3 tenants :

sequenceDiagram
    autonumber
    participant A as Requester
    participant B as Business Cluster - Backend
    A->>B: Request migration
    B->>B: Migration successful
    B->>B: OnTenantDatabaseMigratedAsync on tenant 1
    B->>B: OnTenantDatabaseMigratedAsync on tenant 2
    B->>B: OnTenantDatabaseMigratedAsync on tenant 3
    B->>A: Migration result

During the tenant initialization after a successful migration

sequenceDiagram
    autonumber
    participant A as Requester
    participant B as Business Cluster - Backend
    A->>B: Publish "TenantInit" to create Tenant 'MyTenant'
    B->>B: Tenant initialization
    B->>B: Migration successful
    B->>B: OnTenantDatabaseMigratedAsync on tenant 'MyTenant'
    B->>A: TenantInit result

Single tenant mode (automatic migration)

When a application is in single tenant mode, the backend runs database migrations automatically.

Warning

Even if the application is not in multi-tenant mode, OnTenantDatabaseMigratedAsync will be called as we consider that there is one tenant in the database.

By default, in development mode, when you execute the neos run command, the backend runs migrations and when successful, the interceptor is executed.

graph
    A(Business Cluster - Backend)-->B(Automatic migration : true)
    B-->C{Run migration}
    C-->|Successful| D[Run OnTenantDatabaseMigratedAsync]
    C-->|Failed| E[OnTenantDatabaseMigratedAsync not executed]

Inserting data and forcing the value of auto-incremented properties

When you need to import data during the migration, you may be inserting data that contain auto-incremented values, like an "ID" property for example. By default, this value is automatically set by the database and you cannot specify a the value you want.

When you create the ICommand class that will import data, you can inject and use an IInsertIdentityScope to force an auto-incremented property to use a specified value. This value will automatically be incremented for each subsequent insertions.

Here is an example. You can find a complete example in the technical demos cluster in the TechnicalDemos.sln > Business > Core.Application > ImportDataMigration folder :

internal class ImportDataCommand : ICommand
{
    private readonly IInsertIdentityScopeService _insertIdentityScopeService;
    private readonly DatabaseContext _databaseContext;

    public ImportDataCommand(IInsertIdentityScopeService insertIdentityScopeService, DatabaseContext databaseContext)
    {
        _insertIdentityScopeService = insertIdentityScopeService;
        // Need to add the Persistence project to references.
        _databaseContext = databaseContext;
    }

    public void Execute(Migration migration, ICommandExecutionArgs args)
    {
        // Example using connection with SQL
        migration.Execute.WithConnection((connection, transaction) =>
        {
            using IDbCommand hasCompanyCommand = connection.CreateCommand();
            hasCompanyCommand.CommandText = "SELECT EXISTS(SELECT 1 FROM \"Company\")";
            bool hasCompany = (bool)hasCompanyCommand.ExecuteScalar()!;
            if (!hasCompany)
            {
                using (IInsertIdentityScope insertIdentityScope = _insertIdentityScopeService.Start(connection, "Company", "Id", transaction))
                {
                    // Insert the first company with the id 5.
                    using IDbCommand insertCommand = connection.CreateCommand();
                    insertCommand.Transaction = transaction;
                    insertCommand.CommandText = "INSERT INTO \"Company\" (\"Id\", \"Name\") VALUES (5, 'AKANEA')";
                    insertCommand.ExecuteNonQuery();
                    // If not explicitly specified, then dispose will stop the scope automatically.
                    insertIdentityScope.StopScope();
                }

                // The second company will have the id 6.
                using IDbCommand insert2Command = connection.CreateCommand();
                insert2Command.Transaction = transaction;
                insert2Command.CommandText = "INSERT INTO \"Company\" (\"Name\") VALUES ('IRIUM')";
                insert2Command.ExecuteNonQuery();
            }
        });
    }
}

Table of Contents

Customizing the migration process

Executing operations before and after the migration

Note

Warning

Warning

Customizing the migration

Note

Creating commands to execute during the migration

Warning

Note

Creating a migration interceptor class

OnCommandsCreatedAsync

Registering a migration interceptor

Command execution order during the migration

Note

Warning

Note

Note

Note

Warning

Executing operations after tenant database migration

Creating a tenant database migration interceptor

Registering a tenant database migration interceptor

When is the tenant database migration interceptor executed ?

In multi-tenant mode

Single tenant mode (automatic migration)

Warning

Inserting data and forcing the value of auto-incremented properties

`OnCommandsCreatedAsync`