Voice of the DBA

Tag: Flyway

Friday Flyway Tips: Git Integration in Community Edition

Redgate added Git integration to the free, Community edition of Flyway Desktop. I saw the announcement and decided to make this post to show how this can work for a new project.

We do need git installed, so head over to the free git download if you don’t have it. From there, install Git and you’re ready to go.

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.

Source Controlling Your Project

When you start Flyway Desktop Community, you should see the edition in the upper left, as shown here.

I’ll click Open project, and choose one of my existing projects. When I do that, I see all the migrations in the project. I can also select or add a target and run flyway commands from here.

What’s new is the right hand sidebar, which now has the VCS controls. If I click the left arrow in the upper right, I get the sidebar to expand. I can see I don’t have any changes. This bar wasn’t available previously, but now it is.

Let’s make a change. I’ll close this (click the arrow at the top) and return to the migrations screen. I’ll click the “add migration” button (the arrow points to this in the image below).

When the editor opens, I’ll add some code. I’ll also change the name. Notice there are no changes in the right sidebar.

When I save this, all of a sudden, there is a single change in the middle of the bar.

Expanding the sidebar and clicking on the middle icon, I see my one change has been added as a migration script.

I can add a comment and commit this or continue working. When I’m done committing, I can easily push my changes from here to the remote.

I’m manually managing scripts in Community Edition, but I can do it all from Flyway Desktop, including all the version control work.

Flyway Enterprise

If you want to get more from Flyway, try Flyway Enterprise out today. If you haven’t worked with Flyway Desktop, download it today.

If you use the CLI Flyway Community, download Flyway Desktop and get a GUI for your migration scripts as well as version control.

Video Walkthrough

No video walkthrough this week as I’m on the road.

You can check out all the Flyway videos I’ve recorded.

August 30, 2024
Friday Flyway Tips–Comparison Defaults
One of the little details that I find matter more and more in enterprises is understanding why a tool behaves a certain way. OSS/home-grown ones often have limited docs, but vendor tools should have great docs. Today I learned about how to easily find Flyway comparison defaults, which is the topic of this post.

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.

The Defaults

Do you know all the SQL Compare defaults? Would you realize it if someone changed them? Do you think they’re the same in Flyway? I actually don’t know about the latter question, and I’m not digging through and comparing the options.

In any case, I saw an internal discussion recently about documentation and someone pointed out that we have this page that discusses where you change options. It’s good for that purpose, but it doesn’t list the defaults.

However, at the bottom, there are links:
- Oracle comparison options
- SQL Server comparison options
If you click through, then you get a list of default options. There is first a link to the full list of options (SQL Server, Oracle) as well as a link to SQL Compare options (SQL Server, Oracle).

The SQL Server page looks like this:

Note the sentence just below the image, which links to all the options. The second link is for the SQL Compare option explanations. Below this, you see the default options. This is a table of options, which looks like this image. Note this says certain options are set to true and all others false.

If I click through to the full list, I see this:

I can see all the settings and if they are required. I also see an example of the TOML file below this, where I can set these and store them in version control.

If you want to change behavior of the comparison engine in Flyway, or double check if someone else has changed something, this is the place to check.

Note, if you are looking to induce certain behavior, changes should be made in a TOML file for the project and flow through a PR process for approval and into a pipeline. Don’t edit these options directly, or change them in a pipeline.

Flyway Enterprise

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:
July 19, 2024
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.

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.

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.

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”.

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.

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.

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.

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.

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.

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.

If I check QA, I see no tables.

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

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.

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

Summary

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:

May 24, 2024
Using Flyway with Feature Flags

There is a nice article at Harness.io on their use of feature flags and how they deployed their next generation experience. It’s worth a read if you want to improve your database deployment experience, especially if you want to control how and when you release to customers.

A nice bonus is that they mention Flyway as a tool to help them manage database code changes. Hint from me is that you can use Flyway to not only help manage DDL changes but also DML changes as well.

I talk about this in my Architecting Zero Downtime Database Deployments talk. Most of the time when you have changes that might disrupt users, take a lot of time, or require coordination with different apps/systems, using feature flags and backwards compatible deployments is what makes zero downtime, or minimal downtime, possible. If your changes can’t be backwards compatible, you break the database changes into multiple steps that are backwards compatible.

Flyway makes it easy to ensure scripts run once, they are deployed as a group or individual transactions, and they run in order, so you can test your scripts in multiple environments.

I know I sound a little sales-y there, but Flyway is a fantastic tool and I was pro-purchase when Redgate bought the tool. I was also pushing to replace some other internally-developer deployment tech with Flyway in all our products, which we’ve done.

The big way Flyway supports feature flags is by ensuring you can break up your changes into separate scripts and limit when they run. This is best managed with branches in your VCS and PRs, but this also will be handled by the new Deployment Rules, which are in preview.

May 22, 2024