• Flyway
• Desktop Development
• Database Versioning

Getting Started with Flyway Desktop

Flyway helps to develop and deploy a database in such a way as to eliminate the problems that are caused when it there is no clear way of determining the version of the schema of the database being developed. The tool makes it easier to collaborate on database development and apply changes to multiple database instances.

Flyway Desktop will automatically generate and maintain a database “schema model”, a set of DDL scripts representing the current state of every database object. From that model, it will automatically generate versioned SQL migration scripts. Each Flyway migration file contains all the code that can take a database to a particular version. Flyway Desktop uses the Flyway command line tool to execute these versioned migrations scripts on a target database, to update that database from one known version to another, while preserving all existing data. We can use these scripts to deploy schema changes to multiple environments.

Requirements

Let’s imagine that we have an existing production database (called pubs). Currently database development is chaotic. Developers often accidentally overwrite each other’s changes. To prepare for a deployment the developers manually script out the required changes. It is error-prone as nobody is sure what’s included in the latest release-candidate database. Just last week, the testers wasted a week testing a version that subsequently proved to be out of date and missing some of the required changes. Deployment failures are common, as a result, often exacerbated by the fact that someone has live-patched Production, but nobody is sure how or even why.

The team decides to use Flyway Desktop to bring a degree of control, consistency, and automation to all the development processes so that there is a common understanding about what is in a database version. Our job, perhaps as lead developer or database developer on the project, is to set up a Flyway Desktop project and to establish a development version of the pubs database, so that development work can recommence with a system that ensures that every step in the evolution of the database has a version number on it.

In this article, I’ll set up only one development copy of the database (pubsdev), but in the next article, I’ll discuss how to expand this out to support team-based development with Flyway Desktop.

Initial set up – establishing the baseline version

We’ll assume we’ve already worked with the DBA to create a copy of the database that is currently in production. You can find a script to create this database at the bottom of this article (V1.1__FirstRelease.sql). We’ll call this the ‘production reference’ database (named pubsref). Its schema is identical to the current production database, but the live data is replaced by a dataset suitable for development. This is the starting point for the next phase of development.

We’ve also agreed on a database version numbering system, and decided which version number will be applied to the current production database, version 1.1. The first task is to stamp both the production database and its identical twin, the pubsref database, by running Flyway from the command line (or from PowerShell) and using the baseline command.

We use the baseline command it to bring an existing database under Flyway’s control. By running it, Flyway marks, or baselines, the database as being at a specific baselineVersion, causing Flyway to ignore all migrations up to and including the baseline version.

Here is the command I used to ‘baseline’ the pubsref database at version 1.1:

We can confirm this worked by running the flyway info command, like this:

The output should look something like this:

+----------+---------+----------------------------+----------+---------------------+----------+----------+
| Category | Version | Description                | Type     | Installed On        | State    | Undoable |
+----------+---------+----------------------------+----------+---------------------+----------+----------+
|          | 1.1     | Production version of pubs | BASELINE | 2022-04-07 15:14:49 | Baseline | No       |
+----------+---------+----------------------------+----------+---------------------+----------+----------+

You can find a more detailed example of using the Flyway baseline command, with a SQL Server database, in the article Flyway Baselines and Consolidations.

We’re now ready to set up our Flyway Desktop development project!

1. Identify the development and ‘shadow’ databases

Every developer on the project will need access to a development database. This article assumes that each developer will use their own dedicated development database, rather than access a shared development database. Although Flyway Desktop can support a shared model, Redgate recommends that each developer uses a dedicated development database when collaborating on a database project with Flyway Desktop.

First, we create a development database – in this case just an empty database called pubsdev.

Every developer on the team who needs to generate and run migration scripts will also need to create a shadow database. This database is for the sole use of Flyway Desktop and can be an empty database. Flyway Desktop uses it as a ‘scratchpad’. It builds the current version of the database in it, verifying that any version can be built using the stored ‘chain’ of migration files. Flyway Desktop (for SQL Server, Oracle and PostgreSQL, at time of writing) has a built-in schema comparison engine that it uses to compare the latest ‘scratchpad’ version of the database to the current schema-model. It then generates a SQL migration file that can update any database from the current version to the new development version. (I’ll explain more on this shortly).

All we need to do to initialize a shadow database is to create an empty database on any convenient server. For this article, I created the pubsdev_shadow database on my local SQL Server instance. Flyway Desktop will continuously drop and add database objects to the shadow database, so it should not contain any important data. In fact, there is no reason for you to add any data.

2. Create a Flyway Desktop project in source control.

Just about everything you do within Flyway Desktop is included within the context of a Flyway project. The project provides everything that is needed for building a schema model based on your development database. This model will then allow you to generate versioned migration scripts that can be committed to source control for subsequent use in deployments.

For this article, I created a project for SQL Server and saved it to a Git repository on my local machine. The repo is currently empty except for the standard Git files. It has two branches: develop and main. We’ll be setting up the pubsdev database for the Develop branch.

When you first launch Flyway Desktop, you’re provided with options for opening an existing Flyway Desktop project, creating a new project, or importing a project from a Flyway configuration file, as shown in the following figure.

When you click the New project button, Flyway Desktop opens the New project details dialog box, where you can specify the project location, project name, and database engine. For example, when I set up the project for this article, I specified my Git repository (C:\DataFiles\git), I named the project pubs database project, and specified SQL Server as the database engine, as shown in the following figure. The right pane displays the initial file and folder structure that will be generated when you create the project.

Click the Create project button. Flyway Desktop will generate the project folder and subfolders and add the necessary configuration files to the project folder. The following figure shows the project folder in Windows Explorer.

When you first create the project, the schema-model folder and migrations folder are empty. You’ll be adding files to them as you build your schema model and generate migration scripts. The files in the pubs database project directory itself are all configuration files. For information about these files, refer to the Flyway Desktop documentation.

After you create a Flyway Desktop project, you’re taken to the main interface window, on the Schema model tab, which is shown in the following figure.

The Flyway Desktop interface includes the following four tabs:

• Schema model. Capture the object definitions in the development database and save them to the schema model, updating the model as the development database changes.
• Generate migrations. Generate versioned migration scripts based on changes made to the development database.
• Migrations. Inspect project scripts; test and manage the build in a target database.
• Version control. Interface directly with your Git repository in order to commit changes, pull and push files, or create branches.

At this point, you can commit the project files to source control, using the Version control tab, or wait until you’ve completed the initial project setup. I prefer to wait until I’m sure I have all the pieces in place.

3. Link development database

Go to the Schema model tab, click the Link development database button (shown in the preceding figure) and provide the necessary connection information. For this article, I needed only to add the database name (pubsdev) because I was connecting to a default SQL Server instance on my local machine and using Windows authentication, as shown in the following figure. In versions of Flyway Desktop V5.14 and later, the Encrypt option might be enabled by default. For this article, just for simplicity, I did not encrypt the SQL Server connection (though of course, you should, generally).

As you provide the connection details, Flyway Desktop automatically updates the JBDC URL needed to connect to the SQL Server instance. You can also edit the URL directly to fine-tune the way that the connection is made. Note that TCP/IP connectivity must be enabled on your SQL Server instance to link to a database from Flyway Desktop.

Before saving the connection, you have the option to test it to ensure that you’re connecting as expected. You might want to do this to verify that the connection details are correct. If everything is okay, the dialog box will display a message stating that the connection has succeeded (as shown in the preceding figure). Once satisfied that your connection is correct, you can save it by clicking the Test and save button.

After you save the connection, Flyway Desktop returns you to the Schema model tab and automatically checks for any development database changes that aren’t currently part of the Flyway Desktop project. I’ll explain how it does this shortly but for now, there are no changes to detect.

4. Link the Shadow database to the project

As we make changes to the pubsdev, Flyway Desktop can generate versioned migrations scripts that capture these changes. It saves them in the migrations folder in the project. To generate the scripts, it needs to access a copy of the database called the shadow database.

Flyway uses the shadow database to verify that it can build the latest version of the database by running the migration scripts in version order. It will then compare this with the latest schema-model for the project and generate a new migration script to capture any changes that are in the schema-model but not the migrations folder. The migration script that is generated will take the database from the current version to a new version that includes the changes made on the database on your workstation.

To link the Flyway Desktop project to your shadow database, go to the Generate migrations tab and click the Set up shadow database button, which is shown in the following figure.

This launches the Link an erasable database dialog box. As you can see in the following figure, the dialog box is like the one used to link to the development database, only this time, you enter the name of the shadow database, rather than the development database.

Before you can save the connection, you must select the Okay to erase data check box. Redgate has included this option to ensure that your shadow database is indeed one that can be used for development purposes and that it contains no important data.

5. Generate the ‘baseline migration’ script

Our development database (pubsdev) is currently empty. We need it, instead, to reflect the current state of the production version of the pubs database (V1.1), the starting point, or ‘baseline’, for our development work. Each developer joining the project will run the baseline migration on their development database, to ensure that everyone is working from the same starting point. Any subsequent changes to the schema—after the baseline migration is created—will be reflected in versioned migration scripts.

Therefore, our first task is to generate a baseline migration script that we can use to bring pubsdev from empty to V1.1. To create the baseline script, click the Create baseline button.

This launches the Create baseline dialog box, which is shown in the following figure.

Click the Connect to database button. This launches the Target database connection required dialog box. Here you can define a connection to a target database that contains the schema you want to use to create the baseline. For this article, I used the production reference database (pubsref).

After you’re created, tested and saved your connection, you’re returned to the Create baseline dialog box. This time, click the Baseline button. Flyway Desktop will generate the baseline script and display information about the script on the Generate migrations tab, as shown in the following figure.

Here you can view the file’s contents and save it to your project. Flyway Desktop generated it by comparing our pubsref database (source) to the current version of the database as represented by the migrations folder (target), which it builds in the shadow database. You can only create a baseline for a project with no migrations, so the target will always be an “empty” database. Therefore the baseline script will always contain DDL CREATE statements for all the objects as they exist in the source database (in our vase, v1.1 of pubsref).

Script file names, including the baseline script, are automatically assigned version numbers that conform to the Flyway format, and are incremented consecutively with each new file. The file name starts with a prefix, for the type of script (in this base B for baseline). This is followed by the version number with dots or underscores, in this case 001_timestamp. The timestamp helps avoid duplicate names when multiple developers are working on the same project. Then there must be a separator (double underscore) then finally an optional description.

In this case, we know that the pubsref database is baselined at V1.1, so we change the version number to match that. I also dispensed with the timestamp, so the file name is B1.1__pubsrefbaseline.sql.

When you click Save, Flyway Desktop generates that script and confirms its creation and where it’s been saved.

You can access the script file in the migrations subfolder in the project folder, as shown in the following figure. Notice that there is only one script file at this point.

Now, switch to the Migrations tab and you will see the newly created versioned migration script listed:

You can view the contents of these migration scripts in Flyway Desktop just by clicking it. The script contains all the objects needed to create the ‘baseline’ version of the database (schema only) on our development server.

We can also click Open, at the top left of the script pane, to open it in SSMS and we can then modify the script to meet our specific needs. For example, you might want to add INSERT statements to provide development data (you can copy them from the V1.1__FirstRelease.sql script) then resave the script.

6. Applying migrations to a target database

We can now execute the baseline script on our pubsdev database to migrate it from “empty” to Version 1.1. Similarly, we can use it to build V1.1. of the database in a test, staging, or production environment. Flyway Desktop uses the Flyway command line engine to run these migrations.

On the Migrations tab, click +Add target database. Connect to the development database (pubsdev). Once connected, you should see that the baseline migration script is listed as “pending.” (The displayed information is extracted from the flyway_schema_history table, using the Flyway info command):

I’ll review the other options on this screen, around Flyway configuration settings and use of dry run scripts, in a later article. For now, though, simply click Run migrate to execute a Flyway migration on our target database.

You should, see confirmation that Flyway applied one migration (B1.1__pubsrefbaseline.sql) to the pubsdev database, and you will see this reflected in the information from the schema history table. Flyway Desktop executes the migrate command, on the command you see in the righthand pane is the one that would be issued to the migration engine if calling it from the command line, as part of an automated process (See, for example, Getting Started with Flyway and SQL Server.)

If you open the pubsdev database in SSMS you will see that you now have a fully populated “V1.1” of the database on which to start development work.

7. Update the schema-model for the development database

Switch back to the schema-model tab and hit refresh and Flyway Desktop starts comparing the development database (source) to the existing schema-model (target). The schema-model is currently empty, so the tab lists all the objects in v1.1 of the database, as shown in the following figure.

When you select an object in the comparison results, the tab shows the results of the comparison in the bottom pane. In this case, the authors table is selected, so the pane shows the CREATE TABLE statement in the authors new column. However, it shows nothing in the authors current column, indicating that the table definition has not yet been added to the schema.

To add all these objects to your schema model, select the check box next to Object name and then click the Save to project button. Flyway Desktop adds the objects to the schema and displays a message indicating that the schema model has been updated. The Schema model tab also displays options to Commit changes or Generate migration, as shown in the following figure.

If you click the Commit changes button, Flyway Desktop takes you to the Version control tab. If you click the Generate migration button, Flyway Desktop takes you to the Generate migrations tab. You can also go to either tab directly.

The schema model is saved in the schema-model folder in the project folder, as shown in the following figure. The schema-model folder contains subfolders for each type of object in the database and within each subfolder are SQL CREATE scripts describing the current start of each object. The RedGateDatabaseInfo.xml file contains database-related configuration information.

8. Commit your project to version control

At this point, you should have created your project, generated the baseline migration script, executed it on your development database and built the initial schema model. You can now use the Version control tab to commit all the project files generated so far to the Git repository. The tab lists all files that have yet to be committed, as shown in the following figure.

When committing files to Git, you must add a comment to the text box. You should also ensure that only the files you want to commit are selected. From there, you need only click the Commit button. You should then receive a message that the files have been successfully committed.

Development work begins!

We’ve now set up our Flyway Desktop development project so that a developer, or team of developers, can start work on it. The team can begin making, testing and committing changes to their v1.1 development databases, sharing changes by pushing and pulling updates to the schema-model, and saving incremental database changes as versioned migration files, in the migrations folder. The migration scripts can then be applied to different environments, ensuring that we can always reproduce a particular version of the database, on any server.

I’ll describe how this team-working process for Flyway Desktop might work in my next article. As a brief preview, however, I simply made some improvements to the titleview view, in SSMS, fixing some code analysis issues detected by SQL Prompt, such as use of unqualified object names and old-style join clauses. Here’s the improved version of the view:

When I subsequently hit refresh on the schema-model tab in Flyway Desktop it immediately detects the change. We see the current and new definitions of the view, in the left and right panes:

We can hit save to project to update the titleview.sql file in the schema-model so it has the new definition. If we now switch to the Generate migrations tab and hit refresh, Flyway desktop will run a schema comparison to compare the schema-model to the current version of the database as represented by the migrations folder (v1.1, which it will verify that it can build in the shadow database) and generate a migration script that will make the changes to titleview. This produces the V1.2 migration file, as shown in the following figure:

After the script has been generated, click the Save button. You should receive a message confirming that the migration script has been created. Click Finish to return to the Generate migrations tab.

You can now switch to the Migrations tab, and you’ll see two migration files listed, with the second one listed as “pending” (i.e., not yet executed on the target database, which currently is still pubsdev):

Of course, although we have not run the V1.2 script on pubsdev, we have already made the V1.2 changes to it. What we might do is run the Flyway Clean command (select it from the dropdown):

This removes all schema objects and data from pubsdev, after which we can then run the Migrate command again, and it will execute both the B1.1 and V1.2 scripts on pubsdev and register in the flyway_schema_history table that the database is now at V1.2.

We can also change the target database to pubsref. In this case, we can see that Flyway correctly understood that it does not need to run the B1.1 script on this database, because we established version 1.1 as the current production version using the baseline command, earlier.

Making the most of Flyway Desktop

Flyway Desktop is a new product and still has some kinks to work out. In addition, the documentation is still a bit limited, and inconsistent in places. The product’s newness also means that there is not yet much of a user community, so you’re often on your own to figure things out. However, if you’re already familiar with the Flyway command line tool, you should have an easier time getting started with Flyway Desktop.

Flyway Desktop will no doubt continue to improve with each new release, and despite any existing rough edges, the tool could already prove a useful addition to organizations trying to incorporate SQL Server or Oracle Database into their DevOps operations. However, they’ll need to take the time necessary to learn about Flyway Desktop and incorporate it into their workflows.

In the next article, I’ll describe how Flyway Desktop can be used by multiple developers who are working on the same database. This relies on shared source control and careful coordination. Typically, you’ll want one developer to create the project and commit it to source control. Other team members can then pull the project into their local repositories. When they open the project, they’ll be able to link it to their own development and shadow databases. They can also use Flyway Desktop to apply the schema model to their development databases to bring them up to date.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Desktop

Flyway Desktop helps you easily and safely version control your database schema and prepare validated deployments

Find out more

Flyway Enterprise

Standardize Database DevOps across the organization

Find out more