Page 14 - MSDN Magazine, October 2019

Flyway Basics
The Flyway app describes itself as “version control for your database” and it works with a large number of relational databases. There are three editions: the free Community edition, Pro—which adds more features—and Enterprise. It’s pretty straightforward: You provide a series of SQL script files and it will run them for you. Similar to EF migrations, it stores a history of which scripts have been run in a special migrations history table. This table will get stored in whatever default database is indicated by the database connection string. You’ll see more about that later.
Flyway is cross-platform and can run on a variety of operating systems—including mobile platforms. I’ve used it in Docker contain- ers to execute scripts as part of a CI/CD process, as demonstrated in the video I mentioned earlier. Flyway also has a rich API, although I’ll only be using the most basic function—migrate—for this article.
The order in which scripts are executed is critical. Flyway has a naming convention to ensure that scripts are executed in the correct order. Key elements of the name are “V” for version (to differentiate from U for Undo scripts and R for Repeatable scripts). I’m going to focus only on Version scripts in this article. Next comes the ver- sion number, where you can use an underscore separator for sub versions. The critical double underscore follows, to separate the version info from a descriptive name. I once struggled with SQL files getting ignored because I used a single underscore by mistake in this position, so watch out for this. A typical file name might look like V1__BuildTables or V1_1__CreateRoles.
I installed Flyway to my MacBook using Homebrew (brew install flyway). You can find install instructions for other operating sys- tems on Flyway’s website. The brew installation ensured that flyway was on my file path so I had easy access to it from the command line without having to go look for it. Flyway requires Java and brew guided me to easily install what I needed.
Here’s how I went about the hybrid migration using EF Core and Flyway.
Start with EF Core Migration Files
I let EF Core create migration files for the model I’m working with. For example, my simple model for the conference session I men- tioned is shown in Figure 1. There’s an Agilista class (to keep track of all of my gal pals in the Agile community) and a Category class to represent the areas of Agile they focus on, such as Agile Testing.
The order in which scripts are executed is critical.
I then let EF Core create my first migration using dotnet ef migration add initial. The resulting file uses the migration API to describe defining the tables, relationship constraints and indexes.
I also want a little bit of test data that I’ll seed so I can do a bit of testing (though not performance testing). Therefore, I added the EF Core HasData method in the DbContext class to define some seed data. Figure 2 shows some of that code, although the down- loadable sample code inserts a few more agilistas and categories.
Figure 1 My Simple Domain Model
You can read more about HasData in my August 2018 column (msdn.com/magazine/mt829703).
I created a second migration with dotnet ef migration add seeddata, which describes inserting the data into the tables created in the first migration.
Instead, you want to be explicit about which migrations to script; that way, the outputted SQL can represent incremental changes to the database and be applied as needed.
Rather than letting EF Core apply these migrations (which means it will generate the relevant SQL in memory and then execute it), I’ll tell EF Core to just output the SQL for me. I’m intentionally excluding the –idempotent flag when generating these scripts. Flyway will take care to avoid running scripts more than once. However, EF Core’s script command, by default, will script every migration file found. That means if you script your first two migrations, then add a third migration and call script again, a script will be created for all three. This won’t work for my plans.
Instead, you want to be explicit about which migrations to script; that way, the SQL that’s output can represent incremental changes to the database and be applied as needed. I could combine both of my existing migrations into a single script, but because they perform different types of tasks, I’ll create separate scripts for explicitness.
Figure 2 Seeding Some Data with EF Core HasData
Agilista.cs
- Id (Guid)
- Name (string)
- PrimaryFocus (Category) - PrimaryFocusId (Guid)
- Twitter Handle (string)
Category.cs
- Id (Guid)
- Description (string)
protected override void OnModelCreating (ModelBuilder modelBuilder \{
modelBuilder.Entity<Category> ().HasData (
new \{Id = new Guid ("167d1f6b-a93d-49e4-8a0d-e651369e018b"),
Description = "Agile Testing" \},
new \{Id = new Guid ("5f6d6f80-9f9a-469e-9036-07ecbb3971ea"),
Description = "Exploratory Testing" \} );
modelBuilder.Entity<Agilista> ().HasData (
new \{ Id = new Guid ("5efdb55d-1205-419f-8a0b-9cc7a15f8565"),
Name = "Lisa Crispin",
PrimaryFocusId = new Guid ("167d1f6b-a93d-49e4-8a0d-e651369e018b") \}, new \{ Id = new Guid ("83eda86f-c652-4666-ba17-db90b218a54b"),
Name = "Linda Rising",
PrimaryFocusId = new Guid ("c5b6a0e8-e43f-4765-906f-e15e019a19d8") \} );
10 msdn magazine
Data Points