Entity Framework Core and sqlite: database migration (using VS2017 macOS)

In this article, I’ll illustrate how to use the migrations feature of Entity Framework Core (EF Core).

This post is a continuation of my EF Core introduction article (https://medium.com/@yostane/entity-framework-core-and-sqlite-getting-started-on-macos-and-visual-studio-code-27f3830db77c) where I created a simple console app that uses an SQLite database using Entity Framework Core and Visual Studio Code. I also adopted a code first approach.

Let’s start by understanding the interest of database migrations.

What is EF Core database migration?

Suppose you shipped an app that creates a database that contains a table with two columns. While developing the next version, you figure that you will need to to modify the structure of the database to add the marvellous features that you are working on . The fastest way to do this is to delete the old database and create a fresh one with the new column. However, when users update to the new database, all previous data will be lost. This is extremely annoying in most cases. That’s where database migration comes to play

Migration consists in modifying the database from version x to version y while maintaining previous data as much as possible. Common tasks performed in a migration process are: add or remove a column, add or remove a table, etc.

Now that we understand what migrations consist of, let’s see what Entity Framework Core offers here (spoil, it is very interesting 😁)

EF Core and migrations

EF Core can handle all the migration work for you. However, EF Core needs to know the different database version. This is performed by performing an operation called “adding a migration”. Thus, the migration life-cycle can be defined as follows:

  1. Develop the first version of the app
  2. Before shipping: add a database migration
  3. Ship the app
  4. Develop new version of the app
  5. Before shipping and if the database has been modified: add a new migration and call context.Database.Migrate(); before the first database operation
  6. Ship the app
  7. Repeat steps 4, 5 and 6 for each new version.

Okay, let’s dive into some code and details. In this following, we will try to achieve to following use case:

We have developed a video-game library app using this post: https://medium.com/@yostane/entity-framework-core-and-sqlite-getting-started-on-macos-and-visual-studio-code-27f3830db77c. Before shipping , we want to crate the first migration. This will make EF Core capable of migrating the database in later releases. After shipping the app, we intend to develop a new version that adds a new column to a table. When the user installs the latest release, he should get the new column on his database while keeping previous data.

Readyyy, set, go !

Making the first database migration

As indicated earlier, the first step consists in making database able to handle EF Core migrations. We achieve this by adding a migration on the first version of the app. You can start working on this commit:

Before going further, I want to point that you should not forget to add a migration before shipping you app to the users. Otherwise, EF Core would not be handle cleanly future migrations.

Okay, now that that I got that warning out of the way, let’s install necessary EF Core migration packages and tools:

Open the solution or csproj using VS 2017 on you favourite OS. After that, right-click on dependencies -> Add packages.

Search for: Microsoft.EntityFrameworkCore.Tools. Check it and apply

Eventually, accept additional packages and wait for the setup to complete

Next, install the “Microsoft.EntityFrameworkCore.Design” package:

Only one last package to install (I promise ^^) and it will be added by hand. Right click on the project->Tools->Modify the file. Then, add this line to the ItemsGroup tag:

<DotNetCliToolReference Include=”Microsoft.EntityFrameworkCore.Tools.DotNet” Version=”1.0.1" />

The content of the csproj should be like this:

We are ready to add our first migration. Open up the terminal by right clicking on the projet -> Tools -> Terminal and run the following commands:

dotnet restore (this ensures that we have all the required packages)

dotnet ef migrations add InitialMigration (create the 1st migration)

After executing these commands, the database becomes migration aware and can handle future updates to its structure.

The last step here is to tell EF Core in the code to create the database by taking migration into account. It consists in replacing context.Database.EnsureCreated() by context.Database.Migrate() .

The program.cs class should look like this:

We can ship the first version of the app serenely because future migrations will be handled by EF Core. And that’s exactly what we are going to do in the next section.

Creating a migration for a new version of the database

Now that we have prepared the database fro migration and shipped it to our users. Let’s tackle the next version by adding a column to the VideoGame class:

We will add now a new migration that will handle the transition from the previous version of the database to the new version.

dotnet ef migrations add Version2

This migration allows EF Core to estimate the delta between the previous database migration and the current one. Thus, EF Core will be able to handle both of these situations: if there is no database when the app starts, EF Core will create one. If an older version of the database is found, EF Core will follow the migration plan until it reaches the last database version.

In the code, never call context.Database.EnsureDeleted() because we don’t want the database to be deleted (the migration will be useless otherwise). Do not also forget to call context.Database.Migrate() before querying the database.

And now, the time for experimentation has finally come!

Let’s experiment

In order to experiment migrations, we will need to run the first version of the app, add some data to it, and then run the last version an check if EF core kept the old data while adding the new column.

I have hopefully prepared two separate commits; one for the 1st version and an other for the next version.

This first commit corresponds to the first version of the app. So clone the repository, update to the following commit and run the app.

You should see the following output:

You can also check the structure of the sqlite file.

Next, update to this commit which is new version of the app along with the database migrations.

The main program asks EF Core to migrate and adds some data.

Run the app, you should notice in the output the the video games that were saved in the previous version as well as the new data added in the current execution. You should also note that RealeseDate of previous data is set to 0.

You have succeffully used your first EF Core migration :)

Conclusion

This article has guided you through the process of database migrations using EF Core. For the sake of diversity, we used Visual Studio 2017 instead of VSCode. After experimenting with EF Core migrations, I find it very easy while being very powerful. However, the only caveat is to remember to add a migration of the initial database before the first release.

Happy coding 😎

Links