• Flyway
• Team-based Development
• Generating migrations

Setting up for Team-based Database Development using Flyway Desktop

In the workflow described here, each developer:

• Works on a local copy of the Flyway Desktop project, connected to the same branch of a shared team repository in the version control system
• Works on a local, isolated copy of the development database
• Syncs their local environment to the latest development version in the shared repo
• Saves changes from their local development database to the project and commits an updated schema model and a tested migration script
• Regularly pulls changes made by the other developers and resolves any conflicts, before sharing their own changes.
• Participates in a team review process at the end of a sprint when a pull request is submitted to the release branch.

In this article, I describe the process to the point where the Flyway Desktop project is step up and configured and each developer has created their local development database, at the latest version, ready to start the next development ‘sprint’. In a subsequent article, I’ll demonstrate the ongoing ‘sprint’ workflow, where developers make, test, and save changes locally and then share them with other team members, to the point where all the changes are complete, and a pull request is submitted for a release.

It’s a simple setup but it demonstrates many of the basic concepts underlying Flyway Desktop, and it can be expanded to incorporate more sophisticated branching workflows, for example, and different development workflows, as may be required for larger teams.

If you’re unfamiliar with Flyway Desktop, I recommend you read Flyway Desktop in Database Development Work: An Overview. It explains all the components of a Flyway Desktop project and how they are used, so I won’t repeat all those details here.

Prepare the initial Flyway Desktop project for team development

The lead developer on the team, who I call Reg, is responsible for setting up the Flyway Desktop project in the shared team repository (pubsdb). The project is for the development of a production database named pubs. The following diagram summarizes the process he follows, which I already described in detail in my previous article, Getting Started with Flyway Desktop:

Reg creates a Flyway Desktop project called pubsdev and connects it to his development database (called pubsdev) and shadow database.

First, he uses Flyway Desktop to capture the baseline migration script from the pubsref database and saves it to his local pubsdev project. The pubsref database will have the same schema as the pubs production database, but minus any production-only objects, such as replication or security-related objects, and without any sensitive or personal data. The script used to create the pubsref schema and add sample data is here: V1.1__FirstRelease.sql.

He names the baseline script B1.1.20221007161071__pubsbaseline. If you’re tasked with creating the baseline for your Flyway Desktop project, it’s important that you set up the correct naming pattern so that subsequent migration scripts are automatically assigned names according to this pattern.

Reg used the initial B and the timestamp that Flyway Desktop automatically assigned to the file name but changed the version to 1.1 to match how the versioning was set up in the production database and reference database (see the previous article for details.) After the version number, Reg added a period, followed by the timestamp. Next came two underscores, which are required, and then a descriptive name (pubsbaseline).

The next migration script that Flyway Desktop generates will now adhere to the format V1.2.<timestamp>__<generated name>. Developers should not change the version number or timestamp when creating migration scripts, but they can change the name portion, which comes after the double underscores. Flyway Desktop will automatically increment the version numbers for each new migration script, as in V1.3, V1.4, V1.5, and so on. At some point, you’ll want to increment the major version number as well, but that should be done carefully to avoid conflicts that can occur when multiple developers are working on the same project.

Reg runs the baseline migration file on his empty development database and then saves the schema model for this database to the Flyway Desktop project. Finally, Reg commits the project to the Release branch of shared pubsdb team repo and then creates a Development branch, off the Release branch, so the team can start work.

Configure the local development projects and databases

All team members have Flyway Desktop installed on their local systems as well as git. In this way, they can commit changes locally and push and pull code to and from the shared pubsdb team GitHub repository, which Reg set up in the previous step.

Here’s an overview of how you can set up the team project:

Let’s discuss some of the details of project set up and configuration on each developer’s system.

Development databases

Flyway Desktop supports both the shared and isolated models of development, though for the reasons described in the documentation, Redgate recommends the use of the isolated model, where each developer has their own “dedicated development database environment”. If your team members use a shared development database, you’ll need to take steps to minimize the problems you might typically encounter.

This demo uses the isolated model, where each developer has a local instance of SQL Server to host their own development database (pubsdev). This model is much better aligned with use of distributed version control systems and avoids many of the problems and restrictions of shared development database.

Either model requires good version control practices, and a simple Development-Release branching strategy is often recommended as a starting point for less experienced teams. In this strategy, every developer works on the same branch and, to avoid duplicate migrations, always ‘pulls’ changes committed by others into their local repo, and deals with any conflicts, before ‘pushing’ their own changes.

Shadow databases

The role of the shadow database in a Flyway Desktop project is well-explained in William Brewer’s article, and its configuration is described in the documentation. The most important point to remember is that, regardless of whether you use a single shared development database or dedicated databases per developer, every developer who will need to generate migration scripts must maintain his or her own shadow database.

In this example, all three developers on the team will generate migrations so will each have his or her own shadow database (pubsdev_shadow). Attempting to share access to a single shadow will likely cause conflicts and errors during the migration script verification process.

Project configuration files

In addition to the standard Flyway configuration file, Flyway Desktop adds a config file named flyway-dev.user.json to the project folder and one called flyway-dev.json.

The flyway-dev.user.json file contains user-specific configuration details and is excluded from any git commit or push operations. (It’s automatically added to the .gitignore file.) This setup is what makes it possible for developers to connect to their own development and shadow databases, without the connection information being shared with other users working on the same Flyway Desktop project.

The flyway-dev.json file contains project-level configuration details. It is intended for use when the team is working on a shared development database and is included when committing and pushing files to the git repository, making it possible to share connection settings with all developers.

When you link to a development database or connect to a shadow database in a Flyway Desktop project, you’ll see that the setting Save to user settings is selected by default. This means that the connection settings will be saved to the flyway-dev.user.json file. If you instead select Save to project settings, the connection settings will be saved to the flyway-dev.json file.

Synchronize the local projects and databases to the shared team repo

The lead database developer (Reg) is joined on the project by two more developers, who I call Carla and Devin. Each developer has Flyway Desktop installed locally, clones the pubsdb shared team repo to their local file system and connects to it, and then configures their local databases, as follows:

1. Clone the pubsdb git repo to the local file system
2. Open Flyway Desktop and connect to the existing pubsdev project.
   1. Select Open project, navigate to the pubsdb repository, select the pubsdev project folder, and click Open
   2. This project contains the baseline migration script (B1.1.<timestamp>__pubsbaseline) and schema model, both of which Reg generated previously from the pubsref database.
3. Connect the project to their development and shadow databases – In this example, both are new, empty databases:
   1. Developer database: pubsdev (one instance per developer)
   2. Shadow database: pubsdev_shadow (one instance per developer)

It’s not necessary for all the developers to assign the same names to their development and shadow databases, but for the purposes of this article, it helps to keep things simple.

Let’s say Carla is the first of the other two database developers to complete these steps. Once she completes steps 1-3 above, she returns to the Schema model tab. At this point, Flyway Desktop uses its built-in schema comparison engine to compare her local development database with the schema model folder in her local project and displays a list of objects that need to be changed, along with the type of change, to align the target with the source.

The Save to project view is used to save changes from the local development database to the project, so the database is the source for the comparison and the schema model folder is the target. Therefore, the list of ‘differences’ will reflect all the objects that need to be created, dropped or altered in the schema model so that it reflects the current state of the development database. Because Carla’s local dev database is empty and the schema folder contains the set of object-level scripts corresponding to “V1.1” of the database, she sees a long list of objects that need to be deleted from the schema model!

Of course, what she wants to do instead, at least in this instance, is the opposite, which is to update her local dev database so that its schema matches the current state of the project as reflected in the schema model, which is at “v1.1”, the starting point for the development process.

To do this, she can take one of the following routes:

• Sync her local development database with the schema model folder – using the “Apply to database” view

Or…

• Migrate her local database to the latest version – by the running the required migration scripts in the migrations folder.

Either approach is valid, and the way she proceeds depends on the team’s chosen development model, which I’ll discuss in more detail in the next article. For now, we’ll assume the team has chosen to use the second “migration-centric” approach, but first I’ll briefly review how to do schema model synchronization.

Sync the development database with the schema model

The Apply to database view of the schema model tab can be used to save changes, committed to the project by others, to the local development database. In this scenario, the schema model folder is the source and the database is the target of the schema comparison.

Carla can switch to this view, select all the objects, and click the Apply to database button.

On doing so, Flyway Desktop will generate and execute a script that will create, drop, or alter objects in the database so that it reflects the state of the schema model. In this case, since the local database is empty, it will create every object in “V1.1” of the database. The local development database is now aligned with the schema model, reflecting “V1.1” of the database, and the schema model tab will display no changes.

An interesting feature of this approach is that although the schema of Carla’s local database would now be at “V1.1”, effectively, Flyway has no way of knowing this because we did not use the Flyway engine to apply the changes. If we want Flyway to control and manage the version of any target database, we must apply changes by running Flyway’s versioned migration scripts.

Update the development database by running migrations

The alternative approach, and the one Carla chooses, is to switch to the Migrations tab, connect to her development database, and use the Flyway migrate feature to execute the baseline migration script that is already saved to the project and verified.

Then she simply selects the Migrate command from the dropdown and clicks the Run migrate button in the right section. Flyway Desktop runs the script against the development database and returns a message indicating that the script has been applied. She then closes the message screen to return to the Migrations tab.

The listing for the baseline script now includes the date and time of the migration in the Date migrated column and the Baseline value in the State field column, indicating that the baseline has been applied and Carla’s local database is now at V1.1.<timestamp>.

Flyway tracks execution of migrations on a database by making entries in a schema history table that it creates in the target database, and the information you see on the Migrations screen is extracted from that table

More generally, Flyway makes this information available via its flyway info command. You can’t currently run this command directly from the command selection dropdown in Flyway Desktop, but you can try the following if you’re working on a Windows computer:

1. On the Migration tab, connect to your local development database.
2. Use View Command to copy the migrate command and paste it to Notepad (or similar).
3. Change the migrate command to an info command and delete the “-licenseKey=***” portion of the command.
4. In Flyway Desktop, click the Open in console button at the top right corner of the screen, paste in the modified flyway info command and run it.

You should see output like this:

Schema version: 1.1.20221108151160
+----------+--------------------+--------------+--------------+---------------------+----------+----------+
| Category | Version            | Description  | Type         | Installed On        | State    | Undoable |
+----------+--------------------+--------------+--------------+---------------------+----------+----------+
| Baseline | 1.1.20221108151160 | pubsbaseline | SQL_BASELINE | 2022-11-18 17:56:37 | Baseline | No       |
+----------+--------------------+--------------+--------------+---------------------+----------+----------+

Note the Schema version just above the table, confirming that the schema of the local database is at version 1.1.<timestamp>.

Assuming Devin follows the same path, all three developers now have their local database schemas “synched” to the Flyway Desktop project, which in turn reflects the schema of the current version (V1.1) of the production database

They are now almost ready to start development work, but what about development data?

Loading development data

The team will need some way of loading development data into their local databases. In this simple example I “cheated” somewhat and simply edited the baseline migration script and added some INSERT statements, before saving the script to the project.

However, generally, data should not be handled within a versioned migration script because data does not determine the version of the database. In other words, if we alter the data, it should not affect the schema version (except for static/reference data).

Moreover, development data is likely to change as team members make schema alterations during development, so they need some way to maintain it. What’s required is an easy way to import and export data and should involve a similar volume of data to that which the production version has or is expected to have.

If you’re using SQL Server, then you might consider generating the data using SQL Data Generator and then importing and exporting it, as required, using BCP. See, for example, Getting Data In and Out of SQL Server Flyway Builds and Bulk Loading Data via a PowerShell Script in Flyway.

Conclusions

Flyway Desktop was designed for development teams that needed a way to work together on their database projects, while avoiding the types of versioning challenges that often come with database development. Each team tackles database development tasks a little differently from others, so there is no one-size-fits-all approach. Flyway Desktop’s support for tracking the latest ‘state’ of the database (schema model), plus its ability to generate versioned migration scripts, means that it can accommodate many different approaches to development. However, this also means that a team must be willing to invest the time to understand how the tool will work best for their database projects.

In the next article, I’ll review the various development workflows that Flyway Desktop supports and then demonstrate how a typical team development sprint might progress.

Tools in this post

Flyway Desktop

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

Find out more

Redgate Flyway

DevOps for the Database

Find out more