Get best of the week

EF Core + Oracle: how to make migrations idempotent



Typically, the EF Core framework is used in conjunction with MS SQL, another Microsoft product. However, this is not a dogma. For example, at CUSTIS we write business logic in C #, and we use Oracle to manage the databases. EF Core has a wonderful migration mechanism, but in our case they are not idempotent. The fact is that Oracle and a number of other databases, such as MySQL, do not support transactional DDL . This means that if migration falls somewhere in the middle, it cannot be rolled or rolled back. How to implement idempotent migrations to EF Core without MS SQL?

Background


Our company has a fairly powerful tool for installing patches on an Oracle database, which we use in a number of projects. It was written when there was no Liquibase, EF migrations, and other open tools. Patcher allows you to work with hundreds of databases, track installation history, view logs, store secrets and much more. Scripts for changing the database are written in the form of SQL or m4 macros. With their help, you can, among other things, modify the structure: create tables, columns and other objects. Moreover, m4 macros are idempotent. This means that if you try to create, for example, the table, the script does not fall, but sees that it already exists and skips the creation.

Suppose a script to install a patch consists of two operations:

  1. Create table A.
  2. Creating Table B.

If the script crashes after the first operation, table A remains in Oracle. Re-applying the patch will work correctly: the script will verify that A already exists, so it will immediately proceed to the second operation.

In addition to the advantages, the patcher still has a drawback - the tool is closed and is used only in CUSTIS. Developers have to learn to work with him, and outside the company such an experience is not very valuable. In addition, the patcher does not support Code First mode of operation, so all the scripts for changing the database structure have to be written manually.

We wanted to try some ready-made mechanism for installing patches and chose migration. At the end of 2019, another project was just launched for the customer, on which we decided to test a new approach. The main problem of this mechanism was the non-idepotency of migrations.

Problem


In MS SQL, a chain of DDL statements or migration is performed as a single compound transaction. In case of interruption, the operation is completely canceled. Oracle DDL is non-transactional, so a drop in migration will lead to an inconsistent state of the database.

Back to the patch, which consists of two operations: creating tables A and B. If the migrator falls after the first, Oracle will remain in table A. Restarting will not work - the operator CREATE TABLEwill not like that A already exists. It will also fail to roll back the migration: EF Core writes to the system table that the migration was completed, only at the very end of the process. From the point of view of EF Core, if the migration has not yet been completed, then there is nothing to roll back.

Decision


The search for a ready-made solution for Oracle on the Internet did not yield results. All I have found are articles on how to write and  install patches when working with EF. A little later on StackOverflow I came up with the idea of ​​making my IMigrationsSqlGenerator . This interface is responsible for generating SQL code that processes EF operations.

The package Oracle.EntityFrameworkCore included OracleMigrationsSqlGenerator, implements IMigrationsSqlGenerator. For example, if you want to add a column, the following code will be generated:

ALTER TABLE MY_TABLE ADD (MY_COLUMN DATE)

Then the code is passed to other classes to run in the database.

To start, I tried to override a pair of OracleMigrationsSqlGenerator operations. The task turned out to be quite feasible, and I started writing an idempotent migrant. This is how CUSTIS.OracleIdempotentSqlGenerator came about .

Before operation EF, our migrator checks to see if it has been performed before. For example, a column is added like this:

DECLARE
    i NUMBER;
BEGIN
    SELECT COUNT(*) INTO i
    FROM user_tab_columns
    WHERE table_name = UPPER('MY_TABLE') AND column_name = UPPER('MY_COLUMN');
    IF I != 1 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE ADD (MY_COLUMN DATE)';  
    END IF;       
END;

Using


Using the package is very simple - you just need to replace it IMigrationsSqlGeneratorin the right context:

public class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.ReplaceService<IMigrationsSqlGenerator, IdempotentSqlGenerator>();
    }
}

Migrations are formed and set by the standard tools for EF Core :

dotnet ef migrations add v1.0.1
dotnet ef database update

The general approach laid down in CUSTIS.OracleIdempotentSqlGenerator can be implemented in generators written for MySQL, MariaDB, Teradata, AmazonAurora and other databases in which DDL is not transactional.

References


The package is available on NuGet
Sources on GitHub

More articles:


All Articles