Blog Post

Friday Flyway Tips–State-based Deployments


Recently the Flyway Desktop (FWD) team release support for state-based deployments. These are similar to SQL Compare deployments, but with your code source control, which is where you want it. This post looks at how this works.

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

State-based Projects

When I create a new project, I now have a choice: migrations or schema model (state-based). You can see the choice below.

2024-05-21 18_52_42-Flyway Desktop

I’ll demonstrate this now by creating a new project. I give it a name, folder, type, and then I pick the Schema model deployment source.

2024-05-21 18_52_38-Flyway Desktop

Once that is complete, the project is set up and I need to link a development database. I’ll do that first, picking a database with a few objects. In this case, I’ve picked my FWState_1_Dev database.

2024-05-21 18_56_06-Flyway Desktop

Once this is linked, the FWD tool will read the database, compare to my filesystem and tell me which objects have changed. Since this is a new project, all the objects are new and show up. I’ll select them all and click “Save to project”.

2024-05-21 18_56_42-Flyway Desktop

This gets all the code for my objects into the file system (and a VCS). I won’t commit these now in this demo, but normally I would save this code.

The main change in FWD is the change on the left side menu. Where we have “Generate migration scripts” and “Migration scripts” as options for a migration based project, for a state-based project, we have just a “Deploy” option. I’ll select that.

2024-05-21 18_57_15-Flyway Desktop

Once this happens, I have to pick a target. I haven’t configured anything (this is a new project), so I’ll select the “Manage environments” in the upper right section of FWD.

2024-05-21 18_57_25-Flyway Desktop

When I do this, I see the environments I’ve configured. So far, I’ve only configured a development environment, but I can click “Configure new database” to add a downstream target.

2024-05-21 18_57_30-Flyway Desktop

I get the standard FWD connection dialog, and I’ll enter details for my QA database. I usually also click “Test” to ensure I haven’t typo’d something. You can see I didn’t make a mistake below.

2024-05-21 18_57_52-Flyway Desktop

Once this is added, it is selected by default. However, I can always click the radio button in the Manage environments dialog and click “Confirm”.

This brings me back to the FWD screen where I see the changes that exist in the project, but haven’t been deployed to the target. In this case, it’s everything (QA is empty). I’ll just click the tbl_Customers table.

2024-05-21 18_58_17-Flyway Desktop

When I click Deploy, the script is generated just as it would be in SQL Compare to update the target with the code from the source. I can review the script, as well as change options. I can add an explicit transaction around all changes, or let them run with the default implicit transaction for each statement. I can also copy the script to the clipboard if I want.

2024-05-21 18_58_32-Flyway Desktop

If I check QA, I see no tables.

2024-05-21 18_58_45-SQLQuery3.sql - ARISTOTLE_SQL2022.FWState_1_Dev (ARISTOTLE_Steve (70))_ - Micros

I’ll click “Deploy now” in FWD and the deployment starts. I confirm this is what I want to do.

2024-05-21 18_58_52-Flyway Desktop

Once this is done, FWD returns to the screen of objects. Note that tbl_Customers is no longer listed. The project and the target are in sync for this object.

2024-05-21 18_59_08-Flyway Desktop

If I go back and refresh QA in SSMS, I see the table exists.

2024-05-21 18_59_29-SQLQuery3.sql - ARISTOTLE_SQL2022.FWState_1_Dev (ARISTOTLE_Steve (70))_ - Micros


This short post shows how FWD and the Flyway system can be used to perform state based deployments, similar to SQL Compare or SQL Source Control. If you are used to working in SQL Source Control, this is an easy transition for you to a more modern tech, which will also support Oracle, PostgreSQL, and MySQL.

Try Flyway Enterprise out today. If you haven’t worked with Flyway Desktop, download it today. There is a free version that organizes migrations and paid versions with many more features.

If you use Flyway Community, download Flyway Desktop and get a GUI for your migration scripts.

Video Walkthrough

I made a quick video showing this as well. You can watch it below, or check out all the Flyway videos I’ve added:

Original post (opens in new tab)
View comments in original post (opens in new tab)


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating