Product articles Redgate Flyway CLI basics
Flyway with SQLite for Those of a…

Flyway with SQLite for Those of a Nervous Disposition

Get started with running Flyway migrations on SQLite databases, using PowerShell.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

I have been guilty in the past of getting too complicated too fast with Flyway and engaging in what my editor unkindly refers to as ‘Too Many Handbrake Turns’.

As a way of making amends, I offer up a quick demonstration of Flyway for those of a nervous disposition. We’ll use Flyway and PowerShell to create a copy a beefed-up copy of the Pubs database in SQLite, on your laptop or workstation.

Setting up SQLite and Flyway

SQLite is a relational database system contained in a file and installing it is simple. If you’re working on Windows, just download the latest version of the precompiled binaries from the SQLite website and extract the contents to a folder of your choice.

If you haven’t yet installed Flyway, now is the time to do that too. I use Chocolatey in PowerShell. I prefer this because Chocolatey allows you to update all the software that you’ve installed in Chocolatey in one gigantic swoop, unattended.

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

Getting Pubs onto SQLite

SQLite is convenient to haul relational data around with, for copying related tables, or storing test runs, because it is stored as a single file. The PubsSQLite folder of my PubsAndFlyway project, on GitHub, contains a file with my souped-up version of the Pubs database, and within the Scripts subfolder the Flyway migration scripts that I used to build the database (create all the tables), fill it with the original data and then do some fine-tuning of the table structures, so that it can hold a bigger volume of data.

The pubs database for SQLite

To get a copy of this PubsSQLite Flyway project, you can use my PowerShell cmdlet called Get-FilesFromRepo, 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 my PubsSQLite project. I’ve put it in a temporary directory (see the $ProjectFolder variable below) but you can change this to wherever you want to store it. For example, you might want to create your own GitHub project and store it there.

Hopefully you’ll then see the project directory containing the Pubs.sqlite3 database file, and the Scripts subdirectory:

Flyway migration scripts for SQLite

You may want to copy the Pubs.sqlite3 database file out of the PubsAndFlyway/PubsSQLite directory to somewhere more secure. I generally use a subdirectory of the user area for SQLite databases. For that reason, I have a $Database variable that you can set to the new location of the SQLite database.

Now we can get started with working with Flyway in PowerShell.

Using Flyway in PowerShell with SQLite

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 if you’ve installed it in a different place.

The $Database variable holds the path and filename of the SQLite database. This is needed by Flyway and is passed to it in the connection string. We put all the config settings we need in an array and ‘splat’ them to the Flyway application.

With this config array, Flyway can be easily told the location of the SQLite database and the location of the folder with the migration files.

We can test it out by running:

I’d already run the scripts through to V1.1.5 on the Pubs file that I supplied, so you should see something like this:

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…

Running Flyway migrations on SQLite

To see Flyway migrations in action, on SQLite, let’s rebuild the database. We’ll slip in a report just to make sure it is all clean and ready for the migration.

To see the results of your work, you can use an excellent IDE such as HeidiSQL to run queries, create new databases or create build scripts for the purposes of backup. If you want a simpler interface, you can run some simple commands and queries against the Pubs database, from the Windows command line:

sqlite> .tables
PublishersByPublicationType  pub_info
Sales                        publications
TagName                      publishers
TagTitle                     reptq1
authors                      reptq2
byroyalty                    reptq3
discounts                    roysched
editions                     stores
employee                     titleauthor
flyway_schema_history        titles
jobs                         titleview
prices

This is the improved version of the Pubs database (V1.1.6). However, if we wish, we can also reproduce the classic version of the Pubs database:

sqlite> .tables
authors                jobs                   sales
discounts              pub_info               stores
employee               publishers             titleauthor
flyway_schema_history  roysched               titles
sqlite>

Working with SQLite

It is surprisingly easy to develop SQLite databases using Flyway, but it requires a certain mindset, which I’ll explain. Firstly, feel free to experiment, because SQLite works well with Flyway. Secondly, get familiar with the simple but effective SQL-92 dialect.

Feel free to experiment

The ODBC standard, and the subsequent JDBC standard, is sufficient for most simple database work. It also allows transactions that can be committed or rolled back. Flyway uses manual-commit mode to rollback a failed migration. Fortunately, this works with SQLite. This means you can try out migrations in the confidence that a failed migration is rolled back and there is nothing else you need to do.

Get familiar with the simple but effective SQL-92 dialect

The most important change for any SQL Developer when developing for several different database systems is to stick to a reasonably simple SQL-92 grammar that will work with all databases with JDBC drivers. There are also techniques that make things simpler, such as inserting data into tables in the right order to prevent the need to use different techniques for disabling foreign keys.

ODBC and JDBC drivers are required to support a minimum sql-92 grammar. This means that SQL code that conforms to this minimum grammar should be usable on any database system that has an ODBC/JDBC driver. This makes it easy to develop databases for several different databases, even Excel or plain text.

It is fascinating to use an ODBC interface with MongoDB, for example, which you normally program in JavaScript and see how the SQL queries that you make via ODBC get translated into JavaScript. One of the more important aspects of this core SQL grammar for any migration is the ALTER TABLE statement. This is, unfortunately, not completely implemented in SQLite because it was always envisaged that such a database would be built, but not migrated. This means that one has to delete and rebuild tables to, for example, change a column. This has also prevented the JDBC driver from allowing the full SQL-92 ALTER TABLE statement.

There are, however techniques to get around this in the SQLite tutorial in their SQL Alter Table page. Basically, it involves creating a new table every time. As SQLite tables are unlikely to be enormously large this is probably less of a burden than it might seem. Sadly, SQLite doesn’t have procedures, so we can’t achieve an exact parallel with SQL Server or Sybase (who created the Pubs Database).

The ODBC/JDBC standard, which is at the core of Flyway’s versatility as a database tool, is a remarkable and complex achievement with a power that isn’t always appreciated. It is not just a ‘connection’ but a complete layer that allows a degree of database-independence for SQL code.

Conclusions

With the Pubs database on SQLite, you have a partially-made database for providing a system for book wholesalers. It is unlikely to work with any real company, but hundreds of queries exist for it in old SQL textbooks. You can do no damage by playing around with migrations to improve it and expand it. As it is a single-user database on your own machine, there will be no distant sounds of screams if you issue an unqualified DELETE statement, and no need for backups. Here is an ideal way of learning about migrations without pain to you or your kin. Enjoy it please!

Tools in this post

Redgate Flyway

DevOps for the Database

Find out more