• Flyway
• CLI basics
• Database Versioning

Flyway with MariaDB for Those of a Nervous Disposition

I provide a GitHub project with some ready-made SQL migrations scripts for MariaDB databases. When you run them, from they will that will create a beefed-up copy of the classic Pubs sample database in MariaDB. I demonstrate how to connect from Flyway to MariaDB, in PowerShell, while handling credentials securely, and then how to run “Flyway migrate” and report on the results.

Setting up MariaDB and Flyway

If you haven’t done so already, you will need to install MariaDB server. This is easiest done using the installer they provide on your workstation on ‘localhost’ (equivalent to the special IP address 127.0.0.1). I prefer to have an installation on a server that can be accessed over a network. You can have several instances on a single server as long as they are on different ports. The MariaDB installer does all the hard work for you.

You’ll also need to install Flyway. I use Chocolatey in PowerShell to do this job, because it means I can update all the software that I’ve installed in Chocolatey in one operation without having to attend to the process.

Having installed Chocolatey, you can then use it to install Flyway using the version of PowerShell with administrator privileges.

Getting the Pubs database onto MariaDB

The PubsMariaDB folder of my PubsAndFlyway project, on GitHub, contains a build script for the beefed-up version of Pubs with extra data and, in the Scripts subfolder you’ll find the step-by-step Flyway migration scripts that build the original database (create all the tables), fill it with the original data and then do some fine-tuning of the table structures, so that we can then import a bigger volume of data. To get a local copy of this project, you might like to use my Get-FilesFromRepo PowerShell cmdlet, which you can get from here via cut-n-paste.

If you’re new to PowerShell, I’d advise you to use the PowerShell ISE, but if you are using any version of PowerShell after v6, you’ll need to use ISE mode in Microsoft’s Visual Studio Code (VS Code). Just execute the Get-FilesFromRepo code in the ISE, to load the cmdlet, and then run the following code to copy the PubsMariaDB project. I’ve put the project in a temporary directory and saved the location in $ProjectFolder variable so we can use it to tell Flyway where to find it. Just set the path to the project folder where you store your Flyway projects

We can check to make sure it all worked well:

…and hopefully you’ll then see the project directory and the Scripts subdirectory.

Get started on MariaDB with Flyway in PowerShell

To do any serious work, you’ll need a good IDE to execute SQL code and to test things out. I use HeidiSQL for this, which is in the MariaDB distribution, or you can download from Chocolatey. HeidiSQL has the advantage of working with several databases (MariaDB, MySQL, MS SQL, PostgreSQL and SQLite). In HeidiSQL’s session manager, you click on the “New” button to create a new connection, and most default settings are already set for you, except for the password, which is the one you were asked to provide when you installed MariaDB server.

MariaDB, like SQLite, doesn’t understand schemas the same way as SQL Server or almost any other relational database system (every RDBMS that supports schemas has subtle differences in the way they do it). In MySQL, a schema is synonymous with a database, at the physical level, and the keywords SCHEMA and DATABASE are also synonymous.

Therefore, since we use different schemas in our pubs database (mainly dbo, but later versions of our extended Pubs database use other schemas such as people) we need to create these dbo and people schemas ‘schemas’ on the same server. I used HeidiSQL to do this.

Setting the connection details and getting the login credentials

Firstly, set an alias for Flyway to make it easier to use. You’ll need to change that if you have a different version, or you’ve installed it in a different place.

Now we’ll need to set up an array with credentials and maybe passwords. This will tell Flyway, and anything else we need to use, how to access the database. We are storing any passwords encrypted in the user area. We store passwords separately for every sever, database and RDBMS, so that if the same server hosts MariaDB and Postgres, they have separate credentials.

Doing a migration

Having run the above code to set everything up, we can, in the same PowerShell session, do several different operations:

To build the MariaDB version of the Pubs database, you merely run:

Or if you want just the classic version …

We can test it out by running

…which should give…

Flyway Community Edition 7.8.1 by Redgate
Database: jdbc:sqlite:C:\Users\MyName\sqlite\Pubs.sqlite3 (SQLite 3.34)
Schema version: 1.1.5
+-----------+---------+-----------------------------+------+---------------------+---------+
| Category  | Version | Description                 | Type | Installed On        | State   |
+-----------+---------+-----------------------------+------+---------------------+---------+
| Versioned | 1.1.1   | Initial Build               | SQL  | 2021-07-27 16:33:52 | Success |
| Versioned | 1.1.2   | Pubs Original Data          | SQL  | 2021-07-27 16:33:52 | Success |
| Versioned | 1.1.3   | UseNVarcharetc              | SQL  | 2021-07-27 16:33:52 | Success |
| Versioned | 1.1.4   | RenameConstraintsAdd tables | SQL  | 2021-07-27 16:33:52 | Success |
| Versioned | 1.1.5   | Add New Data                | SQL  | 2021-07-27 16:34:05 | Success |
+-----------+---------+-----------------------------+------+---------------------+---------+

If you wish to be able to use this information, such as when you need to find out the current version of the database, you can read in a JSON version of the output:

And we can get neater reporting, as follows:

version description                  state   installedBy
------- -----------                  -----   -----------
        << Flyway Schema Creation >> Success Phil Factor
1.1.1   Initial Build                Success Phil Factor
1.1.2   Pubs Original Data           Success Phil Factor
1.1.3   UseNVarcharetc               Success Phil Factor
1.1.4   RenameConstraintsAdd tables  Success Phil Factor
1.1.5   Add New Data                 Success Phil Factor
1.1.6   Add Tags                     Success Phil Factor

Any time you need to rebuild the database, you can do this…

Here is the database in its full splendor, as shown in HeidiSQL:

What can go wrong?

The magic of Flyway is that it can detect any errors in the migration and roll back the migration containing the error, so you can correct the error and attempt the migration again. It does this by executing your code within a transaction. With most database systems, some errors are considered too minor to automatically roll back a transaction, but Flyway makes sure every possible migration is either ‘all done’ or ‘not done at all’. Once an error happens, the whole sequence is stopped.

In most database systems, there are some DDL operations that can’t be reversed if an error occurs elsewhere in the migration. If Flyway detects that a migration cannot be run in a transaction, it will warn you and you must explicitly add a configuration file that allows Flyway to go ahead and use the file, acknowledging that you will have to manage the rollback.

Unfortunately, with MySQL and MariaDB, most ‘build’ SQL Code (DDL statements) cannot be rolled back when executed in a transaction because they are committed at the time they are executed. If a migration fails, you must ensure that the database is exactly as it was before the migration was attempted, and then you must run the Flyway Repair action. Flyway can’t do this for you.

With reasonably small database development projects, the easiest course of action is to, instead, run the Clean action to delete the database and then re-run the migrations to get to the previous successful migration. If you are updating production servers on the other hand, you risk costly data loss and, if the database is left in an indeterminate state, the probability of unpredictable points of failure.

Getting started with MariaDB is easy, but any development work needs to pick a good strategy for dealing with failed migrations that is both safe and convenient. I deal with some of these strategies in my next article, Dealing with Failed SQL Migrations in MariaDB or MySQL.

Conclusions

To introduce the idea of using Flyway with MariaDB, I’ve provided you with a partially made database for providing a system for book wholesalers. You can do no damage by playing around with migrations to improve it and expand it. MariaDB is a real multi-user relational database with many years of robust usage. It requires a rather different approach to development because of the way that it has implemented transactions, but I’ll cover that in more detail in a subsequent article.

Tools in this post

Flyway

DevOps for the Database

Find out more