Voice of the DBA

Tag: Flyway

Friday Flyway Tips–Deploying Migrations with a Target

Recently I was working with Flyway Desktop (FWD) and helping a customer work on deploying part of their work. They weren’t sure how easy this could be, but this post follows what I showed them.

Using the ability to run Flyway commands in FWD, we can deploy some migrations and not others. This post shows how to configure this.

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.

Picking Migrations

I’ve got a FWD project here, and you can see the migrations below. In this case, I’ve selected a target of my QA machine and we can see that I have migrations applied up to 5 and there are pending migrations from 6-8 (ignore the undo).

If I want to apply migration 6, but not 8, I can do that. First, I’ll click the Advanced settings on the right side. When I do that, I see text with a “add parameters” button.

If I click the Add parameters button, I get a drop down that is searchable.

I can start typing “ta” in here and you see matching items. “Target” is the last one and this is the parameter that you want.

The value of the target is the last migration you want to run. In this case, I can pick 6 and it will run only migration 6. If I pick 7, it will run 6 and 7.

Once I do this, I can click back (or add more parameters) and on the main screen I see that target is in blue, as a parameter added. In the command text box, I’ve highlighted this command as added to the CLI.

Note: This text is what you could run in a CI system or at a cmd/shell .prompt

When I click migrate, the command is run and I get output about which migrations ran.

If I close this, then I see 6 is successfully applied (after unchecking only show pending) and 7 and 8 are above target. In another post, I’ll explain those.

Try Flyway Desktop 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.

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:

December 15, 2023
Try, Try Again, Until It’s Right

One of the challenges with making changes in a database environment is that undoing those changes can be hard. What’s often preferred is rolling forward with a new change to correct the issue, but that’s often done with limited analysis and thought. Instead, we hope our staff makes a quick patch and a better decision under pressure than they did with more time to examine the problem. That works if it’s a simple mistake that was made in implementation but not if we haven’t designed our solution well at the start.

I ran across an article on DoorDash that I thought was interesting. During the pandemic, their business exploded and they outgrew the Aurora PostgreSQL database. They migrated to Cockroach, a cloud version of PostgreSQL that’s distributed and can (theoretically) scale much higher.

The thing I found interesting is that the engineers at DoorDash were trying to break apart their monolith and get better scalability, primarily from certain tables, by extracting their tables to get single writers in a cluster, which should help them handle a larger workload. They wanted to use their main identity table as a test, which I assume is the table that tracks each user in the system. They tried to migrate this and cutover to a new cluster 4 times before a fifth attempt worked.

I think any large migration is fraught with issues, but I appreciated the design here that allowed them to rollback their change and revert to the previous version of the database. That’s something I don’t see many teams think about or build into their database change process. I think having a clear, known, tested way to undo changes is important, at least for some of your tables.

There are two pieces of advice they give that I often give to customers as well. First, learn to spread out changes across batches. When I work with Flyway customers, I always let them know they need to think of a migration script as a unit of deployment and break those apart as best you can. Those often also become units of rollback, so keep them small. Not necessarily every change in its own script, but don’t bundle too many things together.

Second, keep things simple. Too often I find engineers build clever solutions that make sense to them, but no one else. You never know the quality of your next hire, so don’t overcomplicate things without a really good reason.

Did their process work? They’ve grown to about 1.9PB of data. That’s a lot of food orders. They’ve also had other metrics of success, and seem to be saving time for their tech team, which is often one of the main reasons to build a better process and use it consistently.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

December 15, 2023
Friday Flyway Tips–Comparison Options
Recently a customer asked how they could get index changes to be captured in Flyway Desktop. In their case, they wanted a different fill factor, but I decided to investigate a bit more how things work.

This post looks at how to control the comparison options in Flyway Desktop (FWD).

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 Setup

I’ve got a table and an index, which I created with this script.
```
CREATE TABLE [dbo].[Customer]
(
[CustomerID] [int] NULL,
[CustomerName] [varchar] (75) NULL,
[PrimaryContact] [int] NULL,
[PrimaryAddress] [int] NULL,
[PurchaseLimit] [numeric] (10, 2) NULL,
[Status] [tinyint] NULL
)
GO
CREATE NONCLUSTERED INDEX [nci_customer_custname] ON [dbo].[Customer] ([CustomerName], [Status])
GO
```
I saved this in Flyway Desktop, which we can see here in the filesystem:

and here in VS Code.

If I refresh the schema model tab in FWD, there are no changes.

Making Index Changes

I’m going to alter this index. Specifically, I’m changing the pad index, fill factor, and statistics options. Here’s the script I’ll run.
```
ALTER INDEX [nci_customer_custname] ON [dbo].[Customer] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF, 
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
```
Once this runs, I’ll refresh FWD and I see this. Note that I see an index change, but only one option is captured: STATISTICS_NORECOMPUTE.

What’s happening is that FWD is using the SQL Compare engine and the default options set in the engine. Among these are to ignore fill factor and pad index. However, I can change this.

Changing Configuration

In the past, I would need to edit a config file to make this change, but the team has enhanced FWD to add new options. In this case, notice the button near the top of the Schema model tab: Static data & comparisons. Not a great name, but it’s there:

Once I click that, I get a new dialog. This starts with static data, but I’ll click the second tab, which is Configure comparisons. This shows all the options available in the Compare engine

Rather than scroll, I’ll type in the search box, and I see fill gets me the “Ignore fill factor and index padding” option.

I’ll uncheck this and click OK.

Once I do that, I’ll refresh the comparison, and now I see my options.

Try it 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.

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:
December 8, 2023
Friday Flyway Tips – Adding the Type of Database Project

There was an update to Flyway Desktop which lets you see the type of database your project is associated with, and this post shows how to get this in your list of projects.

As an example, you can see below my first project is a “SQL Server” project.

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.

Two Simple Steps

The first step to seeing the type of database project is to upgrade Flyway Desktop. The team is releasing basically every week. My version, upgraded before this post, is 6.9.3, so anything after this should have this capability.

The second step is you need to open your project. If I open the “DBCode” project above, it starts the comparison.

I don’t have to wait for the comparison, I can just close the project. Once I do, the type of project appears on the right side.

If you’ve been working with Flyway Desktop for awhile, you might have noticed an “Upgrade project” in the upper right. This is to upgrade the project from a JSON format to a TOML format, which doesn’t matter for you, but it does make the management of the internals of Flyway and Flyway Desktop easier for the developers.

In any case, you don’t need to upgrade the project. If I open and close a PostgreSQL project, I see this:

If I reopen the FWPoC_PostgreSQL project (named before this feature appeared), I see the upgrade is still there.

Try it 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.

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:

November 10, 2023