Entity Framework 6 migrations with SQLite

I thought for a long time how to use migration also with SQLite. In this article I show a viable way how this can be implemented with EF6 and current SQLite version. The basic idea comes from Android, where a SQLiteOpenHelper class controls the migration.

Initial situation

SQLite Provider for ADO.Net / Entity Framework 6 does not support database creation and migration as it is supported for example by MS SQL Provider. It is therefore necessary to ensure that the customer always has the correct version of the database. Over time, it is certain that customers will have different database versions and will want to switch to the new software.

So how do you update both the database structure and the customer data with as little effort as possible? Until now, at least in our company, it was not really specified how to do this.

Solution idea

On Android the migration of the SQLite database is triggered with the help of the user_version. This version number can be set by the user in SQLite. SQLiteOpenHelper has two methods for this, which control the creation and migration and pass it to the developer. The required version is always passed as a constructor parameter.

1
2
3
4
5

if (version == 0) {
    onCreate(db);
} else {
    onUpgrade(db, version, mNewVersion);
}

Similar behavior would also be interesting for Entity Framework and sufficient for most application scenarios with SQLite. The way to achieve this will now be described below.

Starting project

We start a simple project with EntityFramework (Classic).

Let’s start with a very simple model that consists of only one table (object class).

public class WorkingTimeRange {
  public long Id { get; set; }
  public DateTime StartTime { get; set; }
  public DateTime EndTime { get; set; }
  public int PauseDuration { get; set; }
}

For the mapping between the class and the table we will create a mapping class.

public class WorkingTimeMapping : EntityTypeConfiguration<WorkingTimeRange> {
  public WorkingTimeMapping() {
    // Primary key
    HasKey(k => k.Id)
    // Nullables
    Property(p => p.StartTime).IsRequired()
    // Mapping
    ToTable("time_tracking");
    Property(p => p.Id).HasColumnName("_id")
      .HasDatabaseGeneratedOpt(DatabaseGeneratedOptionIdentity);
    Property(p => p.StartTime).HasColumnName("start_time");
    Property(p => p.EndTime).HasColumnName("end_time");
    Property(p => p.PauseDuration).HasColumnName("pause_duration");
  }
}

Normally, the context class would then look like this at the beginning (definition of the access points, constructors and mappings).

public class DatabaseContext : DbContext {
  private const string _DB_FILE_NAME = @"Data\Time.db"

  public DatabaseContext()
    : this(_DB_FILE_NAME) 
  
  public DatabaseContext(string dbFileName)
    : base(GetConnection(dbFileName), true) 
  }

  public IDbSet<WorkingTimeRange> WorkingTimes { get; set; }

  protected override void OnModelCreating(DbModelBuilder modelBuilder) {
    modelBuilder.Configurations.Add(new WorkingTimeMapping());
  }

  private static DbConnection GetConnection(string dbFileName) {
    var connectionString = new SQLiteConnectionStringBuilder() {
      DataSource = dbFileName,
      FailIfMissing = true,
      ForeignKeys = true
    }.ToString()
    return new SQLiteConnection(connectionString);
  }
}

This basic implementation unfortunately only works if the database already exists and matches our model. If the database is not in the specified location, an exception is generated (this is primarily done by setting FailIfMissing = true).

You can prevent this exception by setting FailIfMissing = false. This will cause the SQLite provider to create a new empty database if it does not exist. Only, as mentioned before, this one is completely empty. Thus, a database is always required if no other steps are implemented.

A delivery of a database is basically not the problem, but the migration of the data is, if the customer already has a database with an older structure and this is filled with his costly data. These must be migrated before the first access to the database, if possible without loss.

Preparation for migration capability

Version number

In order for us to determine whether we need to migrate the database, or can continue to work with the one we have, we need to record somewhere what version the database is at.

The obvious solution would be to have a table in the database that keeps a version number. This is feasible, but then all databases we ship must already contain this table. This is relatively difficult if the databases are already in use.

However, SQLite has an additional option, user_version. SQLite maintains two database versions (SQLite Pragmas):

1. schema_version: Automatically incremented by SQLite when a change is made to the structure of the database (new tables, views, triggers, etc).
2. user_version: This can be set by the user.

We will use user_version for our purposes, as is the case with Android.

For this we only need a few lines of code in our context. Once the SQL to be able to read and write the user_version:

private const String _DB_VERSION = "PRAGMA user_version";

And the property Version, with which we can access this information. To reduce the number of accesses, the version number is only read from the database if it has not yet been queried, or has been changed. Otherwise we access the cached version number.

private int? _databaseVersion = null;
public int Version {
  get {
    if(!_databaseVersion.HasValue) {
      _databaseVersion = Database.SqlQuery<int>(_DB_VERSION).Single();
    }
    return _databaseVersion.Value;
  }
  set {
    Database.ExecuteSqlCommand($"{_DB_VERSION}={value}");
    _databaseVersion = null;
	}
}

This already gives us a way to check the current version of the database without having to check the complete structure of the database.

In the next step, we must now use this information to trigger a creation or a migration.

Migration steps

To perform migration, we define an interface that abstracts a step and provides general access methods.

public interface IMigrationStep<T> where T : DbContext {
  void MigrateStructure(T context)
  void MigrateData(T context);
}

For versions that should be skipped, we also define a default implementation that does not perform any actions.

public class NullMigrationStep<T> : IMigrationStep<T> where T : DbContext {
  private NullMigrationStep() { }
  public void MigrateStructure(T context) {
    // Do nothing, skipp
  }
  public void MigrateData(T context) {
    // Do nothing, skipp
  }
  public static NullMigrationStep<T> GetInstance() {
    return new NullMigrationStep<T>();
  }
}

For now, we just define a migration step that initializes the database if it is not already there.

public class InitDatabaseStep : IMigrationStep<DatabaseContext> {
  public void MigrateStructure(DatabaseContext context) {
    context.Database.ExecuteSqlCommand(Properties.Resources.InitDatabase);
  }
  public void MigrateData(DatabaseContext context) {
    // No data changes on initialization
  }
}

I simply use a SQL script for the initialization, which I store in the resources of the library. Of course you can also store it directly in the code, or store it in a file. This can be handled differently depending on the project needs.

Implementation of the migration

Now comes the glue, which now assembles the preparations into a working solution.

The context class gets a static constructor that contains the initializer. This is called whenever you connect to a database that has not yet been contacted in the running program.

static DatabaseContext() {
  Database.SetInitializer<DatabaseContext>(new CreationionAndMigrationInitializer());
}

The initializer now ensures that the migration is performed, depending on the current version.

public class CreationionAndMigrationInitializer : IDatabaseInitializer<DatabaseContext> {
  // Minimum version number of the database requiered to work with the programm
  private const int _REQUIRED_VERSION = 1;

  // Static migration step for skipping
  private static readonly NullMigrationStep<DatabaseContext> _SKIPP_MIGRATION_STEP =
    NullMigrationStep<DatabaseContext>.GetInstance();

 // List of all available migration steps. Index represents the version to migrate from to the next
  private static IMigrationStep<DatabaseContext>[] _MIGRATION_STEPS = {
    new InitDatabaseStep()
  };

  public void InitializeDatabase(DatabaseContext context) {
    // get current version
    var currentVersion = context.Version;

    // Check all migration steps to the required version are available
    if(_REQUIRED_VERSION > _MIGRATION_STEPS.Length) {
      throw new IndexOutOfRangeException("Not all migration steps are implemented!");
    }

    if(currentVersion < _REQUIRED_VERSION) {
      // Migration of data and structure

      // Check we have SQLite as databse
      var connection = context.Database.Connection as SQLiteConnection;
      if(connection != null) {
        // Close prior connection if open
        if(connection.State == System.Data.ConnectionState.Open) {
          connection.Close();
        }

        // get origin connection string
        var originConnectionString = connection.ConnectionString;

        // Create new connection for migration (required to override ForeignKey beahvior of the origin connection)
        var migrationConnectionString = new SQLiteConnectionStringBuilder(originConnectionString) {
          ForeignKeys = false
        }.ToString();

        // assign the new connection string to the context
        connection.ConnectionString = migrationConnectionString;

        // Open connection for migration
        connection.Open();
        using(var transaction = connection.BeginTransaction()) {
          // Migrate structure, before migrating data
          for(int i = currentVersion; i < _REQUIRED_VERSION; i++) {
            _MIGRATION_STEPS[i].MigrateStructure(context);
          }

          for(int i = currentVersion; i < _REQUIRED_VERSION; i++) {
            _MIGRATION_STEPS[i].MigrateData(context);
          }

          // Set Version to required version
          context.Version = _REQUIRED_VERSION;

          // Commit all migration changes to the database
          transaction.Commit();
        }

        // Close migration connection
        connection.Close();

        // Set the connection string to the origin value
        connection.ConnectionString = originConnectionString;
      }
    }
  }
}

In the initializer we specify the version we currently need for our program (_REQUIRED_VERSION). In the array _MIGRATION_STEPS we initialize all classes which are necessary for the migration. The index corresponds to the version from which it should be migrated to the next version.

In the example we have only one step, with index 0, which is called when the database is newly created (version 0).

Through the for loop, all migration steps are passed, starting with the current version of the database, up to the currently required version. The for loop must be run twice. In the first run, the structure of the database is adapted (mostly by SQL scripts), so that the database corresponds again to the current CodeFirst model. In the second run, any necessary data is added or changed.

In the last step the version number of the database is set to the required version.

The entire migration runs in its own transaction. Thus, either the database is run through with all migration steps, or not at all.

Source code and sources

• Project on Github
• Sqlite with Entity Framework Code First and Migration
• Database initializer and Migrations Seed methods
• Understanding Database Initializers in Entity Framework Code First