Page 18 - MSDN Magazine, October 2019

Figure 3 The Flyway_schema_history Table Shows Two Migrations Have Been Applied
migrations are inserted as rows in the flyway_schema_history table. Figure 3 shows part of the listing of the two new rows.
Had there been any problems, Flyway would have stopped exe- cuting and output details about
Running Flyway from the Command Line
I already had a SQL Server running in a Docker container that was exposed to my environment on localhost:1601. (There are now many articles in the Data Points series that use SQL Server in Docker. The initial introductory article from July 2017 can be found at msdn.com/magazine/mt784660.)
Rather than including the URL, user, password and other needed parameters in every command, I’ve stored them in a Flyway configuration file, flyway. conf, in my project.
With Flyway, you need to specify the database connection with a URL. I’m using the syntax for SQL Server and setting master as the default database:
jdbc:sqlserver://localhost:1601;database=master
The Flyway docs provide examples for various database types. Rather than including the URL, user, password and other needed parameters in every command to Flyway, I’ve stored them in a Flyway configuration file, flyway.conf, in my project:
flyway.url=jdbc:sqlserver://localhost:1601;database=master flyway.user=sa
flyway.password=P@ssword1 flyway.locations=filesystem:sql/
flyway.mixed=true
The last configuration—mixed—allows both transactional and non-transactional SQL commands in the same migration file.
Because I want you to see Flyway perform an update, I’m going to muck up the name of the third SQL file, making the initial letter a lowercase “v”—v1_2__SeedData.sql—causing Flyway to ignore it on the first run.
With this in place, I can finally run flyway migrate at the com- mand line in my project folder.
Flyway will read the configuration file and then look to see if its migration table, named flyway_schema_history, exists on the server yet. In my case it doesn’t, so Flyway creates that table inside the default database (master). Then it notes how many migration files it found, compares those files to what’s in the flyway table (none yet) and runs any that aren’t yet listed in the table.
Flyway’s logs note the current version of the schema, which is “<Empty>” at this point; then that it “successfully validated 2 mi- grations”; and that it “successfully applied 2 migrations.” The applied
the issue. As I’m not doing anything advanced in this scenario, the migrations aren’t being run in a transaction. Therefore, at this early stage of learning, you’d need to roll back any changes that were made before any failures.
Now let’s introduce the database change represented by the third SQL file. I’ll change the name back to V1_2__SeedData.sql so Flyway will see it.
I’ll rerun flyway migrate and this time, the logs tell me it found three migrations, noted that the schema version was 1.1 and that it applied the seedData migration. Here’s some of the detail from the ouput:
Successfully validated 3 migrations
Current version of schema \[dbo\]: 1.1
Migrating schema \[dbo\] to version 1.2 - seedData WARNING: DB: Changed database context to 'DB_Agilistas'. Successfully applied 1 migration to schema \[dbo\]
Looking at the database in Azure Data Studio, I can see all of the assets created by the various migrations, including the new tables and their data. The flyway_schema_history table also has a new row representing the latest migration.
A More Robust Path for Server Migrations
Flyway uses concepts similar to those you may be familiar with from working with EF or EF Core, especially with respect to the history table in the database server. However, using an explicit mechanism to perform migrations on a production database rather than depending on EF Core to do them from within your application is preferable when your deployment has any level of complexity.
There’s still more to learn if you plan to use Flyway in a hybrid approach to leverage EF Core migrations for certain tasks and to perform migrations on your production database. But my goal here was to introduce you to the basic concept and let you get a feel for how the pieces go together. And while I chose Flyway as the tool for running my SQL scripts, you might want to translate this hybrid approach to some other tool that allows you to run scripts. Remember, though, that one advantage of Flyway (and Liquibase) is that it works with a number of relational databases. Other tools focus on a single database such as SQL Server. n
Julie lerman is a Microsoft Regional Director, Microsoft MVP, Docker Captain and software team coach who lives in the hills of Vermont. You can find her presenting on data access and other topics at user groups and conferences around the world. She blogs at thedatafarm.com/blog and is the author of “Programming Entity Framework,” as well as a Code First and a DbContext edition, all from O’Reilly Media. Follow her on Twitter: @julielerman and see her Pluralsight courses at bit.ly/PS-Julie.
Thanks to the following technical experts for reviewing this article: Julia Hayward (Redgate), Diego Vega (Microsoft)
14 msdn magazine
Data Points