• Flyway
• Variants, Localization
• Database Versioning

Complex Production Database Deployments and Flyway

Unless you have a small database, close-coupled to an application, there is a job of work in the deployment process that will usually involve a larger team. Of course, the release candidate always needs to be checked through and tested, but there will be more involved than that.

There will be connections with other production systems for a start: most operational databases live in a connected world, with dependencies on other components. There will be data feeds, Import/Export facilities, monitoring systems, queues, downstream reporting systems, maybe also other associated databases. There are also issues such as security and performance that need to be tackled. The deployment process will turn a database release into a functioning system.

Whereas, in many deployment methodologies, the various participants can make free with changes to the migration script, a Flyway development requires a more disciplined approach. Changes that need to be a permanent part of the database, such as missing or incorrect indexes or poorly performing routines, are easily changed with any migration-based approach: they are done with a versioned patch that is then permanently added to the migration scripts.

However, there are some changes to a database that are for the production environment only, such as payment gateways, interfaces with other production databases, audit, production access systems: This article suggests an approach that makes this aspect of deployment easier.

Firstly, I’ll outline some of the problems that can hit a deployment.

The operational contribution to a live database

Deployment isn’t always tricky. It can be a simple process, particularly in microservice components or where the changes are small. In such cases, the release that the developers deliver, once subject to the customary tests and checks, can be deployed “as is”.

However, it is more usual that a production database will require extra ‘operational’ features, beyond the scope of development, such as access control, replication, messaging, alerts, ETL systems, and interfaces to downstream systems dependent on data. This means that certain aspects of the database will have to be added or altered to install the changes within a server context, or to provide operational security, or to accommodate the development changes being deployed.

In a conventional approach, the synchronization script for the new release candidate will be applied, firstly to a backup of the production system in staging, and then to production. The Ops team will often need to make changes to the production systems to accommodate changes in the database being deployed.

The question is: how do we manage these operational changes, given that with a migration-based approach such as Flyway we cannot change the existing migration scripts? In some cases, such as index changes, it is quite easy to incorporate the migrations into the main development work. However, we must handle the migrations for other types of operational changes with a degree of separation.

Operational changes to correct or improve development work.

While often carried out after the release candidate is delivered, some ‘productionizing’ changes need to become a permanent part of the development database, with indexing improvements perhaps being the most obvious example.

In a DevOps environment, we can tackle some of these operational issues within development at the point at which the required changes for a release are incorporated into the main branch. The migration scripts of the development branch will be consolidated into a release migration, in the parent branch, at which point the versioning numbering used in the development branch has no further relevance. Therefore, we can add scripts at this stage to address operational concerns such as indexing strategies, setting up message queues, agent scripts or data extracts.

If the operation team need to make urgent changes to the production system, such as to add indexes, they can deploy the changes via a versioned ‘patch’, done as a migration, and the retrospectively added to the development release code in the trunk (main). Again, this is fine for corrective changes that should become a permanent part of the development database. In fact, it is good practice as long as the patch is also kept with the development branch, thereby preserving the changes.

I’ve already demonstrated how we can manage incorporate these sorts of changes into a Flyway development in my article, Flyway Branching Walkthrough, so will refer you there for details. In this article, I’ll focus on the knottier problem of how to manage the migrations for ‘production only’ code.

Managing service-level or “production only” code

Unfortunately, we cannot eliminate post-development ‘productionizing’ work entirely simply by incorporating into the development migration scripts. We’ve already mentioned that ‘productionizing’ also includes access control, replication, messaging, alerts, ETL systems and so on. Few organizations that are responsible for the security of sensitive or personal information in their databases would want general access to the access-control system, alert systems, monitoring systems, server configuration or many other operational arrangements for a production database.

Therefore, we will need to maintain “production only” migration code that makes any required changes to service-level functionality. This code should be separate from development. It can’t be added to the development migration code because a database system in development shouldn’t have the production clutter like replication or auditing objects and mustn’t have the production security and access code. Ops maintain their own settings for access control entirely separately.

Of course, the team need to maintain a roles/permissions system for development, but they should do this separately from the main development work, so there’s no chance that changes to made in development to access-control and object permissions can accidently a leak into production.

There will also be cases where a ‘mock’ version of a production system will need to exist in development, for testing purposes. For example, if you are interfacing with a card payment system, the interface and functionality needs to be in both development and production but the two will be entirely different.

The mock and production versions will be maintained separately, and the development team will need to manage the mock version separately from the main development work, from the Flyway perspective. After all, you wouldn’t want to accidentally send your dummy development payments to the real bank or update your corporate accounting system from a test run, but you still need to do those test runs. We can use a migration approach to keeping the production aspects in sync with the development database, to update or build any version.

A scriptable ‘versioned’ approach to maintaining production-only code

Ops scripts to be used only on in staging and production, which handle access control, interfaces and so on, must be under the same discipline as in development. All aspects of the database that are added or amended, to support operational requirements, must be scripted, archived and managed so that the entire database system can be reproduced in the event of disaster, and so that the entire database is auditable.

We must allow the Ops team to maintain production-only systems speartely,  allow for any necessary changes to be made, after the development team deliver a release candidate. In achieving this, we also need to retain Flyway’s principle of the ‘immutability’ of existing migration scripts. So how do we do it?

Use schemas to separate production components of the database.

Operational, or “production only” code would normally be maintained separately from the development database by the Operations team, in a separate source repository, the Configuration Management Systems (CMS).

In using a Flyway development, you can’t achieve this purely by chopping the database into development and production parts, because the developers need to test their work as they write it. This testing requirement becomes more of a problem if a database is made up of several close-coupled databases, either on the same server or on linked servers.

A typical technique used in the more complex databases is to place code that supports your database, but isn’t part of it, in a separate schema. In this case, the operations team would maintain in the CMS, a set of ‘production only’ schemas, containing the latest versions of all the operational code. In parallel, they would provide stubs that provide sufficient functionality for the developers to do their testing (more on this shortly).

Fortunately, use of schemas is an approach that works well with Flyway. At first glance, it looks like Flyway operates at the database level, but in fact a Flyway “version” only applies to the schemas that you’ve specified, even though it doesn’t actually stop you altering database objects in other schemas. This allows databases to be made up of several Flyway Projects, each managing a different set of schemas and each versioned separately by Flyway.

The importance of interfaces and ‘mocks’

One might think, at this point, that this technique of separation into schemas eliminates the problem. However, developers still need to be able to develop and test code that, when deployed, will access production-only features.

This means that teams in different projects must create an interface for each of these production-only features (such as a payment processing system). Developers test against the correct interface, with all the same inputs and outputs as the real system, but in development this interface will consist of a ‘mock’ version of the real payment system, composed of ‘stub’ objects..

A stub object is a ‘dummy’ version of the real object, which exists in another schema and isn’t part of the Flyway-managed database. It has parameters with the same name and datatype as the original and return objects or results of the same datatype. I’ve described their use elsewhere to resolve ‘missing dependency’ problems in database builds.

In this case, we them to create two variants of the production schema, one with the real production code, maintained in the Ops CMS, and one with the mocked-up stubs that mimic the interface with the real system, maintained in the development repository. In development, we’ll use Flyway to maintain the ‘dummy’ production schema, but independently of the main development work. During the deployment process, these ‘mocks’ can be replaced with the real production schema(s) with functioning interfaces.

A practical demonstration

With our long-suffering Pubs demo we’ll add a production-only feature. Imagine that our fictitious users have requested an addition to the system that provides the title, author and a summary of any book that matches their search criteria. This would allow the booksellers with access to the system to find enough information to order a book on request.

This production system consists of a table function, called GetlistOfBooks, that accepts a search string and then extracts the requested details for all matching books from an online book service, accessed via an oData interface. This function and any other required code relating to the service is maintaining in a separate production schema called BookQuery.

We need to imagine that access to this online book service comes with an expensive per-seat license and so can’t be used in development (in real-life, there are plenty of systems, particularly in healthcare, that are necessarily very access-restricted).

However, the developers need to be able to develop and test the ‘front end’ application that relies on data from this service, and train users how to use it. So how do they do it? In development, they have a ‘dummy’ BookQuery schema that contains a stub of the GetlistOfBooks function.

It returns results in the same format as the live service but contains completely fake but feasible book details. With this, they can do all their tests without abusing the real system, the users can run their acceptance tests, and trainers can show it to end-users. During deployment, this dummy BookQuery schema is swapped out for the real thing.

Maintaining the dummy ‘BookQuery’ schema in a Flyway project

I’ve demonstrated in a previous article, Maintaining a Utilities Schema in a Flyway Project, the idea of having Flyway maintain a separate schema for ‘utilities’ that any aren’t part of a database application but need to be maintained and deployed by Flyway. In this example, the utilities were cross-database, so the Flyway project to manage the utilities schema was created completely separately from the main Pubs development project.

Here, we need to maintain the ‘dummy’ BookQuery schema as part of the Pubs project so we can develop and test all the routines within Pubs that we’ll need to add and we can ensure that the interface works in practice. To do this using the Flyway teamwork framework, we can manage this schema from a separate branch of the same project. In practice, this is likely to be the responsibility of the production team.

In my Flyway Teamwork framework, each ‘branch’ of a development project, for a particular database, equates to a Flyway project, with its own set of migration scripts and its own flyway.conf file. Generally, each branch manages the same core set of user object schemas, but it’s perfectly feasibly to have a branch manage a separate schema, such as our BookQuery ‘dummy production’ schema. Any migration we run on the BookQuery ‘branch’ won’t affect the version of the user object schemas.

Therefore, I simply created a BookQuery project folder as a branch from ‘main’, within the Pubs development folder. It contains the migration script that creates the GetListOfBooks stub. This, in turn will require supporting functions, procedures views and tables but these need not be visible or accessible to the database, only the stub interface represented by the GetListOfBooks function:

This query, executed within the Pubs project…

…will produce something like this, with a different result each time …

Also, in the BookQuery project folder, we need the flyway.conf file and preliminary file:

As we’re using Flyway Teamwork, the Flyway.conf file just specifies the server and the database, the same as the main project. The difference is in specifying the BookQuery schema:

We’re assuming that the username is specified in the flyway.conf file that is in the User Area. If you’re not using Flyway Framework, I’m assuming that your password is tucked into a session-based environment variable: in PowerShell I use:

$env:FLYWAY_PASSWORD='MySecretPassword'

Finally, you just need to run the framework’s preliminary.ps1, though for this project it isn’t essential, because this is the Flyway-equivalent to a trip to the corner-shop, and in this case you just need the password for the database you’ve specified as an environment variable.

After making the BookQuery directory your working directory, a Flyway info command shows that all is in order.

+-----------+---------+--------------------------+------+--------------+---------+----------+
| Category  | Version | Description              | Type | Installed On | State   | Undoable |
+-----------+---------+--------------------------+------+--------------+---------+----------+
| Versioned | 1.1.1   | Initial Book Search Stub | SQL  |              | Pending | No       |
+-----------+---------+--------------------------+------+--------------+---------+----------+

Then a Flyway Migrate and you should see this. Sadly, you need SQL Server 2019 (14) or better, because that was the point where the OpenQuery function that I use allowed a variable as a path parameter. The Flyway Teamwork framework has a function that tests your server level that can be used to prevent this being run on the wrong version.

Creating schema [BookQuery] ...
Creating Schema History table [PubsDev].[BookQuery].[flyway_schema_history] ...
Current version of schema [BookQuery]: null
Migrating schema [BookQuery] to version "1.1.1 - Initial Book Search Stub"
Successfully applied 1 migration to schema [BookQuery], now at version v1.1.1 (execution time 00:00.493s)

We can also clean it out without harming the objects in the other schemas by using Flyway Clean:

Successfully dropped pre-schema database level objects (execution time 00:00.009s)
Successfully cleaned schema [BookQuery] (execution time 00:02.736s)
Successfully cleaned schema [BookQuery] (execution time 00:00.213s)
Successfully dropped post-schema database level objects (execution time 00:00.013s)
Successfully dropped schema [BookQuery] (execution time 00:00.218s)

The code is here in the FlywayTeamwork-Pubs project as V1.1.1__Initial_Book_Search_Stub.sql.

Deployment: swapping in the real production version

In production, this ‘dummy’ BookQuery schema will be replaced by the real production schema, which accesses the real online book data service.

The migration file supplies by the development team (from the main branch of the project) will be used to in the deployment pipeline to provision any test environments that are required, and is deployed to a staging database, set up as a copy of production. This migration file will update the schemas ‘owned’ by the development database.

The stub object can be substituted in Staging by the Ops team, who will maintain a parallel production Flyway project that will update the production BookQuery schema, if that is ever necessary, via a separate migration script. The easiest approach is to keep the versions in sync, so that the current version of the production schema is the nearest lower or the same as the version of the development schemas.

Summary

Schemas can have a good use in the larger databases. Not all RDBMSs support schemas, but then you wouldn’t want to put the database of any substantial organisation in SQLite. Flyway works with a collection of schemas rather than an entire database. This means that you can, where necessary, partition up a database into several separate pieces of work ‘owned’ by a different team, especially if you use agreed interfaces between them. We’ve demonstrated very simply how this can be made possible by using mocks that support the interface and generate data that looks somewhat like the real data.

This use of interfaces can prevent the need for a great deal of provisioning in Development. You can provide development databases without having to worry about the peripheral ‘bits’ of the production database that would otherwise need to be supplied. This means of imposing structure helps the deployment process greatly because it allows the operations team to do their work much more freely without having to change development code. Flyway provides a very simple way of supporting this way of working.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Teams

Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.

Find out more