Product articles
Flyway
Database Versioning
Getting Started with Flyway Migrations…

Getting Started with Flyway Migrations on PostgreSQL

Use PowerShell to run some Flyway migration scripts that will build, fill and modify a PostgreSQL database.

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.

Flyway’s value is most easily appreciated when you are working with different database systems in a single application development. Using Flyway makes sense even if you use only one database system, such as SQL Server, and works well if you prefer a migrations-first approach and especially for smaller databases that aren’t being worked on simultaneously by a team of developers.

However, it really comes into its own if you are developing an application with two or more different types of databases, such as PostgreSQL, CockroachDB, SQL Server, Oracle, MySQL, SQLite or Informix. It deals with a lot of the chores and checks and, by using Flyway, it ensures that these checks get done, and so you’re more likely to do successful builds.

To familiarize yourself with Flyway, it pays to try it out with a few database products that are outside your comfort zone. You’re likely to appreciate the value of a wider range of Flyway features. This article is aimed at SQL Server developers need to start understanding how Flyway works and performs with a different relational database system, in this case PostgreSQL.

I’ve chosen PostgreSQL, because I’m already somewhat familiar with it, and it is easy to obtain and install. Also, it is one of the few database systems where a migration can be wrapped into a transaction, so that the migration can be rolled back. The full list is DB2, PostgreSQL, Derby, EnterpriseDB and SQL Server. In another article, we’ll try MariaDB as an illustration of a database system that can’t do this.

Setting up PostgreSQL

For this demo, I installed PostgreSQL on a Windows Server. It would have been easier on a laptop, but then the rest of your team can try out a server-based install. It is probably also easier to use a containerized install.

Before doing a Windows Server install, set up a dedicated local Windows admin account to run the postgresql service, with all the necessary permissions. Flyway comes with its own set of JDBC drivers to access it.

The only thing that could cause difficulties here is ensuring that the port you select (5432 by default) is set in the firewall to let the traffic through. You may also need to edit the following line in your postgresql.conf file to allow authenticated access from clients. For test purposes, the following line, will allow authenticated access on all incoming connections:

Although it isn’t required, it is useful to have pgAdmin4, the PostgreSQL-equivalent to SSMS, which is both cute and useful.

For the examples, I’ve converted the hoary old Pubs database, originally created for Sybase, for use on PostgreSQL. This is so that even the greybeard SQL Server developers who trained on the Pubs database can cut some PostgreSQL queries quickly, to try it out.

Once the install is done, open pgAdmin4 (or use psql) to create an empty copy of the Pubs database, and then create a user who ‘owns’ the database.

Running Flyway migrations on PostegreSQL databases

To test things out, we’ll use Flyway to run a few migrations. I’ve provided some sample migration scripts for the Pubs database here. I’ll add to them over time, but here I’ll only cover the first five. Flyway migration scripts for PostgreSQL

V1.1.1 does the initial build of the Pubs database, V1.1.2 inserts the original Pubs data and then V1.1.3 does some fixes, including updates to some of the datatypes. V1.1.4 alters the schema to allow it to take a reasonable amount of data, and V1.1.5 inserts the larger quantity of data.

The easiest way to get started is to clone the PubsAndFlyway project which contains the PubsPostgreSQL directory that I’ve provided, with the Scripts folder shown above.

To run these initial migrations, your project directory must remain in your user area because we are going to get started by storing the password in a Flyway Config file within the project folder. The PowerShell script that generates it will also need to be saved in the user area until we can progress to use a credential more safely.

The following PowerShell script writes the required config details, including passwords, into a local Flyway config file within the PubsPostgreSQL project folder. Flyway will be able to pick up the config if you set PowerShell’s current directory to the PubsPostgreSQL project directory. This will allow Flyway to connect to the Pubs database on the server you specify and run database migrations.

Having run the initial config, above, you can now run the following Flyway migrations:

Each time we run Flyway info, we see the changes registered in the flyway_schema_history table revealed each time Flyway presents the info. At the end, it should look like this:

+-----------+---------+------------------------------+--------+---------------------+---------+
| Category  | Version | Description                  | Type   | Installed On        | State   |
+-----------+---------+------------------------------+--------+---------------------+---------+
|           |         | << Flyway Schema Creation >> | SCHEMA | 2021-07-26 14:30:49 | Success |
| Versioned | 1.1.1   | Initial Build                | SQL    | 2021-07-26 14:30:49 | Success |
| Versioned | 1.1.2   | Pubs Original Data           | SQL    | 2021-07-26 14:30:51 | Success |
| Versioned | 1.1.3   | UseNVarcharetc               | SQL    | 2021-07-26 14:30:51 | Success |
| Versioned | 1.1.4   | RenameConstraintsAdd tables  | SQL    | 2021-07-26 14:30:52 | Success |
| Versioned | 1.1.5   | Add New Data                 | SQL    | 2021-07-26 14:30:55 | Success |
+-----------+---------+------------------------------+--------+---------------------+---------+

On your own projects, if you haven’t reached the point where it works, you may want to watch progress, so you could do it version by version and get the info back on every migration

Once you’ve done all this, you can then start working on the database. The aim here is to repeatedly run the migration until it succeeds. This means a repeated edit/run cycle. In my case, I needed to convert the SQL Server code of Pubs to PostgreSQL, and I saw quite a few ‘red-screens’ before achieving a successful migration.

This means that the PowerShell interface with Flyway must be made as easy as possible to speed repetitive development. Changing SQL Server code into PostgreSQL required patience. If you try converting your favorite database from SQL Server to PostgreSQL, you can console yourself with that thought that as long as the line number for the latest exception error keeps incrementing, that’s progress.

Dealing with credentials more sensibly

There remains a bit of a problem. If we are using credentials, we have left a password in a config file, which is bad news, especially if it is a shared directory. We really need to pass it as a parameter instead. Let’s fix that now.

Now you can do your various Flyway operations such as migrating the database from scratch:

A neater way to handle parameters

But wait! We can do even better than that. If you are having to splat parameters with the @Credentials array to provide the UserID and password, why not splat them all? This allows you to do away with the config files so that the same source directory can be used by several different database users who wish to do Flyway migrations.

Now we can use the ‘credentials’ array to splat all the information that flyway needs and stop having to worry about a config file altogether. You can still add other parameters including the actions, of course.

Conclusions

This article provides a way of allowing you to use PowerShell to tinker with a PostgreSQL migration with Flyway, try things out, and appreciate the convenience of the system. I find it hard to find any real differences with a SQL Server flyway migration once one has got attuned to the differences in dialect. This is hardly surprising as both are mature and well-supported Relational Database systems.

Flyway is best seen as a JDBC database migration utility that can be used with a wide range of databases that have suitable JDBC drivers. This provides two great advantages if you need to support the development of more than one database system in an application. One major advantage to keeping all your database developments as flyway migrations whatever their platform: You can ensure that all the databases, however they are hosted, can all be kept in sync with the version of the application. The second attraction is the ease of managing builds and migrations. Where these database systems don’t support full transactional rollback, Flyway can seem to lose some of this obvious ease-of-use, but there are ways around this, as I’ll describe in a subsequent article.

Tools in this post

Flyway

DevOps for the Database

Find out more