Complex Production Database Deployments and Flyway

This article explains how, by use of schemas and stub interfaces, we can use Flyway to manage the main development work smoothly alongside any changes or additions required to maintain production-only code. It also demonstrates how this mechanism enables Flyway to manage a 'mock' or 'dummy' variant of a production schema, in development, so that the team can still develop and test code that, when deployed, will access production-only features. Read more

Undoing Actions on Groups of Database Tables

During development you'll occasionally need an undo script that drops a group of tables, or you might need to truncate a group of tables and then insert fresh data in order to run some tests. Unless you perform the required actions in the correct dependency order, you'll be tripped up by foreign key constraint violations. This article provides a SQL function that returns the list of tables in the correct dependency order. Read more

Getting Started with Flyway Migrations on Oracle

A quick demonstration of using Flyway with Oracle, for those of a nervous disposition. We'll use Flyway to run some migrations on an Oracle Autonomous Database, building the initial version of the database then filling it with development data. Read more

A Simple Example of Flyway Development using GitHub Branching

This article demonstrates one way to do branch-based database development with Flyway, using GitHub to manage the branches and Flyway configuration files to allow Flyway to switch smoothly between databases, when we move between branches in GitHub. Read more

Dealing with Mutually Dependent Databases in Flyway

How to create and manage 'stub' objects, in Flyway migrations, in order to overcome build failures caused by circular, or mutual, dependencies between objects in different databases. The technique should be applicable to any RDBMS. Read more

Dealing with Cross-Server Database Dependencies in Flyway Projects

How to tackle database development in Flyway when databases make cross-server references. The technique uses synonyms to represent the remote objects, and local 'stub' objects to overcome the problems caused by 'missing references' when building the individual objects. Read more

Setting up for Team-based Database Development using Flyway Desktop

This article provides a simple demonstration of how a small team of developers might set up a Flyway Desktop project to manage, automate, and control database development. Read more

Executing Optional Code in Flyway Callbacks

When you are building or migrating a database, it is nice to have a system that you can easily configure for various tasks, such as logging, loading or dumping data, or backing up of the database. This article demonstrates how we can use a Flyway placeholder as a simple on/off switch for each task, during Flyway migrations. Read more

Transferring Database Documentation into SQL DDL Scripts

We all love having documentation in source code, if not writing it. We just want to ensure that it gets propagated and retained so you and your team members can read it if they need to. This article demonstrates a cross-RDBMS PowerShell task that will extract the comments from a primary JSON source of database documentation and add it to a set of SQL DDL source files. Read more

Cross-RDBMS Code Quality Reports in Flyway

Before you commit your Flyway migration files, you may want to run some automated checks for style or 'code smells'. This article demonstrates how to run basic cross-RDBMS code quality checks using SQL Fluff. We analyze the results in PowerShell to produce reports and analytics on the number or types of issues found. Read more

Flyway Desktop in Database Development Work: An Overview

For those new to Flyway Desktop, this article takes a strategic overview of the components of a Flyway Desktop project, how to set up a project for team-based development work, and how we can use the tool in conjunction with a version control system and CI servers to manage a database development and release process. Read more

Maintaining a Utilities Schema in a Flyway Project

Whatever development methodology you use, it is useful to have, and independently maintain, a separate schema within a database for utilities. These utilities are database objects that monitor the functioning and operation of the database, but aren't part of the database. This article demonstrates how to manage these utilities from Flyway so that we can maintain and migrate them separately from the database objects. Read more

Building a Database Directly from Object-level Source Scripts

Table manifests, and object manifests, which are just ordered lists, are a very useful output from and development database build. This article show how to generate a table manifest in SQL, and once you have it, you'll start to find several uses for it, besides the build process. Read more

Reviewing SQL Migration Files Before a Flyway Migration

Your finger is hovering over the 'enter' key to set off the Flyway "migrate" command, but you hesitate. There is a large stack of migration files for this project: don't all these files need to be checked first? Yes, they do, but how? This article demonstrates how, once armed with the file path locations of all the scripts, you can use PowerShell to search them for various purposes such to review them for potentially disruptive changes, or run code quality checks, or to verify documentation standards. Read more

Using SQL Backup in Development Work

For a development team, SQL Backup provides a simple way to restore development or test databases, if required, while minimizing the tedium of the task of taking, managing, and restoring the backups. We can generate the required backup commands in the GUI and then use them to construct a SQL script that backs up all the required databases. We can even use the backup commands in a beforeMigrate SQL callback, in Flyway, to make sure the current version is safely backed up before we run migration. Read more

Try Before You Commit in Flyway

Sometimes we want to check whether it is possible to run a Flyway migration without error, but not actually make the changes. We might just need to 'sanity test' the performance of a migration on the Staging server, for example. By using a placeholder 'switch' to trigger a SQL Exception, we can get Flyway to roll-back its transaction, and therefore the migration, on demand. Read more

Finding the Version of a Flyway-managed Database Using SQL

Maintaining a version of a database opens a lot of possibilities, especially if an automated process can easily grab the current version, at runtime, using just SQL. You might, for example, have a routine that is only appropriate after a particular version. It is also very handy to be able to associate entries in an event log or bug report with the database version. No more desultory quests, when dealing with support issues, or when bug fixing, to find which database version was running when the bug happened. Read more

Rollbacks, Undos and Undonts

Rollback scripts are designed to allow us to recover safely from a failed deployment that leaves the database in an indeterminate state. They must check exactly what needs to be reverted before doing so. If you work with an RDBMS that cannot support transaction DDL rollback they are vital. This article proposes a strategy where you create and test a rollback file, at the same time as the forward migration, and reuse it as a Flyway undo script. Read more

Managing Static Data in Flyway Database Development

If your database application requires 'static data' to function, then the best way to manage that data is using a view based on a derived table. This article demonstrates ways to create these views, depending on your RDBMS's capabilities, and how to build and manage them in development work, using Flyway and PowerShell. Read more

Managing Datasets for Database Development Work using Flyway

A generic way of exporting, deleting and loading data, for database development work. It uses Flyway Teams, a PowerShell framework, JSON files for storage and a table manifest to define the correct order of dependency for each task. It should help a team maintain datasets between database versions, as well as to switch between the datasets required to support different types of testing. Read more

The Uses of Dependency Information in Database Development

Dependency information will allow you to avoid errors during a database build or tear-down, by ensuring you create or remove objects in the right order. It will also help you to avoid future 'invalid object' errors, because it will allow you to check that no database alterations have introduced broken references, during Flyway migrations. Read more

Performance Testing Databases with Flyway and PowerShell

Performance tests are central to the quality of the database changes we deliver because they ensure that any business process that accesses the database continues to return its results in an acceptable time. When Flyway creates a new version of the database, it is the ideal time to run these performance checks. Read more

Running Unit and Integration Tests during Flyway Migrations

In this article I'll give a practical example of developing a database, with Flyway, in such a way that it is automatically tested with whatever unit tests and integration tests you specify whenever you migrate the branch you're working on to the next version. If a test fails, you can work out why, undo the migration and then try again. Read more

Introduction to Testing a Flyway Development

How to use Flyway Teams to run basic tests whenever it successfully executes a migration, checking that all the business processes supported by our database always produce the expected results. Read more

Re-baselining a Database using Flyway Desktop

Over time, Flyway projects can accumulate a lot of migration scripts, with many database objects being created, altered, and dropped across many files. Tonie Huizer explains why you might want to create a new baseline migration file to create the latest version of a Flyway-managed database in a single leap, and how to persuade Flyway Desktop to do it. Read more

Building a Database with Flyway

Flyway, especially Flyway Teams edition, can be used in several different ways to accommodate a database development that was originally based on builds rather than migrations. This article explores four different ways to use Flyway to build a particular version of a database, from the ground up, using a single migration script. It should help teams select the best way to incorporate Flyway into an existing database build system, during development, while benefitting from use of Flyway's versioned migration system for deployments and releases. Read more

Increasing the Visibility of Database Changes in Flyway Development

If you save a metadata 'model' for every new version of a database created by Flyway, you can compare the current model to the previous one to see what changed. In turn, you can then generate a database E-R diagram that highlights the changed objects, instantly making those changes visible to other team members. Read more

Taming Database Documentation with Flyway and MySQL

Database object documentation is essential for explaining to busy developers, and the wider business, the purpose of each object and how to use it. The solution presented in this article consists of a SQL script to allow developers to add comments to MySQL database objects, without affecting the database version, and a simple way to generate a documentation report, in JSON. The SQL script will execute automatically as a callback, during any Flyway Teams migration run, and the report will allow the team to spot any gaps quickly. Read more

Automating Flyway Development Chores using Database Diagrams

If you can produce a quick Entity-Relationship diagram for any version of a database, you'll have a simple way to 'sanity check' it for unreferenced tables, missing keys and other design flaws. This article shows how to auto-generate these diagrams when running a migration, using Flyway Teams and PowerShell. Read more

Managing Database Code Quality in a Flyway Development

In this article, I'll discuss the most important quality metrics for a database development, and then practical ways to ensure that a Flyway-managed database is designed and implemented to a high enough standard that it is reliable to use. Read more

Getting Started with Flyway CLI and any RDBMS

How to get started with Flyway, as simply as possible, using PowerShell. This article provides a practice set of Flyway migration scripts that will build the original pubs database on either SQL Server, PostgreSQL, MySQL, MariaDB or SQLite and then migrate it from version to version, making a series of improvements to its schema design. Read more

Simple Reporting with Flyway and Database Models

If you can generate a file-based (JSON) model for each new version of a database, produced by a Flyway migration, then you have an easy way to run simple reports to help you search, list, and understand the structure of these databases. I'll show how to produce the models using PowerShell and then run some queries against them to generate the reports. Read more

Getting Started with Flyway Desktop

Robert Sheldon helps you get started with Flyway Desktop by walking you through the process of creating a Flyway Desktop project for an existing database. It covers the basics of building a schema model, generating and running migration scripts, and saving changes to source control. Read more

Flyway How-tos: a User’s Perspective

Flyway provides a database-independent way for a team to track, manage and apply database changes, while maintaining strict control of database versions. It updates a database by running a series of versioned migration scripts, in order, and keeps track of all the changes in a special "schema history" table. It sounds simple, but it is easy to derail this team discipline if you don't find the right answers to the following questions… Read more

Database Development Visibility using Flyway and PowerShell

The payback of DevOps is not simply in automation but in using that automation to increase the visibility of the development processes. This article demonstrates way to make Flyway developments more visible, regardless of your RDBMS, such as by providing a detailed migration history, and change reports that reveal detail of what is going on to all involved. Read more

What is the Flyway Teamwork Framework?

The FlywayTeamwork PowerShell framework is designed to help get you started quickly with scripting Flyway migrations for a range of database systems. It introduces a PowerShell task library to help with the scripting of repetitive chores and to generate some of the 'build artifacts' that are often required during team development work. This article explains the basics of the framework's design and provides a demo how to use Flyway to migrate a PostgreSQL database, while generating a high-level narrative of the changes made between versions. Read more

Creating Flyway Migration Files using Redgate Schema Comparison Tools

How to use Redgate's schema comparison engines to generate object-level scripts for every database version that Flyway creates, and then use them to create ad-hoc, Flyway-compatible migration files. Read more

Getting an Overview of Changes to a PostgreSQL Database using Flyway

How to use Flyway and PowerShell to automatically generate a database build script every time Flyway successfully created a new version. You can then investigate schema changes between versions simply by using a Diff tool to compare build scripts. Read more

Flyway Gotchas

Explaining some of the 'gotchas' that can trip up the unwary Flyway user, and how to avoid them. One or two of these you'll encounter quickly, such as the case-sensitivity of parameters and arguments. Others, such as potential problems with undo scripts or running scripted callbacks, only when you are tackling more complex development processes. Read more

Moving from ‘Chaotic’ to ‘Managed’ Database Development using Flyway

This article describes a route to adopting Flyway in order to bring management and control to a chaotic database development process. It is based on use of Flyway migrations to update a database from version to version, while maintaining object-level source scripts for tracking changes between versions. Read more

Reporting on the Progress of a Flyway Database Development Project

This article demos a novel way to report on the progress of your Flyway development project. It provides both SQL and PowerShell versions of code that extracts information for each database version from the Flyway schema history table and then plots it in a Gantt chart. Read more

A Programmer’s Guide to Flyway Configuration

Phil Factor offers a programmer's guide to Flyway's configuration settings, explaining the different categories of parameters, the role of each of parameter within each category, and how to exploit Flyway's multi-level configuration file system. Read more

Debugging Flyway Callbacks and Migrations that Use Placeholders

Placeholders come in very handy in Flyway, but troubleshooting the SQL migration and callback scripts that use them can be tricky. This articles demonstrates how to develop, test and debug these scripts in a tool designed for these tasks, such as SSMS, leaving Flyway to do what it's designed for, which is running the scripts. Read more

Testing a Flyway Database Migration

Often, we want to test the new version of a database, produced by a Flyway migration, before committing the new migration file, or to test the same migration run on a number of different databases. This article demonstrates how to do it, by generating and using JSON parameter files to run a series of Flyway actions on any number of databases, on any number of servers. Read more

Exploring the Flyway Schema History Table

Flyway uses a schema history table to track the version of each database, recording in it every versioned migration file applied to build that version. It's worth understanding exactly how Flyway uses this table, the possible dangers of moving it to a non-default location and how to do it safely, if required. Read more

Reporting on Changes Made by Flyway Migration Scripts

Phil Factor demonstrates some PowerShell tasks that will produce a high-level overview, or narrative, of the main differences in the metadata between two versions of a database, during Flyway Teams migrations. Read more

Using Flyway Teams with SQL Change Automation PowerShell Cmdlets

Use Flyway to run your database migrations, each time automatically creating a SQL Change Automation release object to provide object-level scripts and a build script for the new version, along with change reports and code analysis reports. Read more

Adding Test Data to Databases During Flyway Migrations

How to add test data to a SQL Server database, during Flyway development. We use SQL Data Generator to produce some "realistic but fake" data and then SQL Data Compare to produce an INSERT script that Flyway can use to load the data into a newly-built version of the database. Read more

Flyway Teams and the Problem of Database Variants

The 'ShouldExecute' script configuration option in Flyway Teams simplifies 'conditional execution' of SQL migration files. This makes it easier to support multiple application versions from the same Flyway project, to deal with different cultural or legislative requirements. It also helps developers handle environmental differences between development, test and staging, such as the need to support multiple versions or releases of the RDBMS. Read more

Dry Runs for Database Migrations using Flyway Teams

How Dry Run scripts work, and how they can be used to deliver a single-script release artifact to Staging, verify placeholder substitutions in SQL migration scripts, and simply team code reviews. Read more

A Flyway Teams Callback Script for Auditing SQL Migrations

Demonstrates a cross-database PowerShell callback script for reporting on and auditing Flyway migrations, telling you which scripts were used to create each version, when they were run, who ran them and more. Read more

Secrets of Flyway Teams Undo

Flyway Teams has an Undo facility that enables you to roll back one or more migrations. This article demonstrates how it works and explains how Undo migrations make testing migration scripts easier and branch-based development simpler. Read more

Flyway Without the Typing

How to use Flyway configuration files to minimize typing during ad-hoc development from PowerShell or DOS; you just type in the Flyway commands you need and hit "go" and the config files take care of all the tiresome connection, authentication and project details. Read more

Dealing with Failed SQL Migrations in MariaDB or MySQL

This article explains the fastest ways to restore the previous version of the database, to recover from a failed Flyway migration that leaves the database in an indeterminate state, and then how to adapt your database development process to avoid these problems. Read more

Flyway with MariaDB for Those of a Nervous Disposition

This article will get you up and running quickly with Flyway migrations on MariaDB or MySQL databases, from PowerShell. Read more

Discovering What’s Changed by Flyway Migrations

A set of PowerShell cmdlets that will 'diff' two versions of a database and provide a high-level overview of the major database changes made by successive Flyway migrations. You can 'diff' a SQL Server database to the same one on PostgreSQL and find out which objects are the same and which are different. Read more

One Flyway Migration Script for Diverse Database Systems

How to create a single set of SQL migration scripts for Flyway that we can use across multiple database systems, or for all regional variants of a database. Read more

Flyway with SQLite for Those of a Nervous Disposition

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

Getting Started with Flyway Migrations on PostgreSQL

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

What is Flyway Enterprise?

How Flyway Enterprise will help you ensure that databases and applications can grow and develop in step, and to implement a workflow for delivery of database changes that is resilient, repeatable, fast and visible. Read more

Testing Flyway Migrations Using Transactions

When you are using Flyway, how can you test your database migration script first to make sure it works exactly as you intended before you let Flyway execute it? Read more

Managing database changes using Flyway: an Overview

This article describes the principles of using Flyway migrations to build a database from scripts, to a specified version, and to track, manage and apply all database changes. Read more

OVERVIEW

BY NEED

BY ROLE

BY INDUSTRY

PASS Data Community Summit 2023

Product articles Database migrations

Redgate Hub

Product Articles

University

Events

Forums

Community

Simple Talk