I was asked about state-based deployments in Flyway, so I decided to show how this can work with a quick demo. This post walks through the process.
I’ve been working with Flyway and 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.
A Steady State
I have a Flyway project setup already. I’ve refreshed the screen and as you can see here, there aren’t any differences between my database and my project in version control.
In SSMS, I’ll create a new table in my FWState_1_Dev database. This is where my project is pointed in Flyway Desktop (FWD).
I’ve created a new table and it exists. If I check my QA database, FWSTate_3_QA, I don’t see this object.
If I refresh my project in Flyway Desktop (FWD), I’ll see my change. I can pick it and see the code, and then I’ll click “Save to project” in the upper right.
Once the code is saved, I see the confirmation and I have the code ready for commit to a VCS. I won’t do that here, though you should.
What I want to do now is go to the left bar and select “deploy”, which is the rocket icon.
When I do this, I see a blank screen, as I don’t have a target. I can select that on the right if I’ve configured one.
If I pick the drop down, you will see my dev and QA environments. I made changes in the dev environment, and want these changes to go to QA. I can also click the “Manage environments” button. Let’s do that.
When I do that, I see a list of environments. I use this a lot to double check that I’m deploying to the right place for this project and where to look for changes. If you work across multiple projects as I do, this is handy.
You can see below I’ve selected QA with the green checkmack in the radio button. Once you pick your environment, you can click “Confirm”. If you need a new target, click Configure.
I return to the deploy screen, and this time I see the changes that have not been deployed. In this case, that’s one change, but if there were more, I would see them. I can click any object to see the changes, and once I’ve selected what to deploy, I can click deploy.
When I click Deploy the script is generated and I see a preview. I also can see that this is from my schema model on disk being deployed to the Qa database. That’s handy as this might take a few minutes and who knows what distractions might appear in my life.
I also can execute this as a transaction or not, and copy the script if I want to examine it in another tool (or format it like SQL Prompt).
When I click Deploy, I need to confirm my deployment. Always a good step, though I think if you do this often, your muscle memory will be ready to just click confirm.
Once the deployment runs, we see a message this was successful.
If I now check the QA database, I see the object.
Summary
This post has shown how you can perform manual deployments of changes in a state-based method using Flyway. This is a quick way to move your changes from one machine to another.
Flyway can do much more, and for smoother automation, check out Flyway Enterprise.
Flyway is an incredible way of deploying changes from one database to another, and now includes both migration-based and state-based deployments. You get the flexibility you need to control database changes in your environment. If you’ve never used it, give it a try today. It works for SQL Server, Oracle, PostgreSQL and nearly 50 other platforms.
Video Walkthrough
Here is a video of the process below.
