Voice of the DBA

Author: way0utwest

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
A New Word: Nementia

nementia – n. the post-distraction effort to recall the reason you’re feeling particularly anxious or angry or excited, trying to retrace your sequence of thoughts like a kid gathering the string of a downed kite.

I’m pretty level headed, but sometimes I over-react to something and may get angry, upset, etc. Sometimes I have to go through the process, but often my wife might interrupt the thoughts, asking me to go through some nementia and figure out why I reacted this way.

Like the rest of us, I’m human, I do silly things, and I sometimes can’t figure out why I reacted that way.

From the Dictionary of Obscure Sorrows

December 15, 2023
Clearing Intellisense in SQL Prompt

I got this question from an account rep: if a customer is using SQL Prompt and a snippet, for example AT, that was also used as an alias, is there a way to avoid it triggering the snippet or do they just avoid hitting the tab key?

It’s a good question. Let’s see how to deal with this.

This is part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag.

The Scenario

Say I have this code:

Notice I have an alias that is the same as a snippet, in this case AT. Seeing this pop up is annoying, but there are ways to avoid getting concerned about this.

First, the Esc key will get rid of the intellisense popup. Pressing escape at this point brings me back to this:

My other option is to type a ., essentially keep typing. If I do that, I get this:

The same thing if I had a longer alias. Here I’ve added 2 characters, but if I type the 3rd, the snippet goes away and I get the table. I could hit tab here to fill this in or type the s.

The other thing to do is raise the time for intellisense if it’s popping up too quickly and annoying you. I don’t know the default (nor do I want to reset lots of stuff), but here I’d set it to 100ms, which is a nice delay for me. I find 500 too long. Of course, you can CTRL+space to pop it open.

If you haven’t tried SQL Prompt, download the eval and give it a try. I think you’ll find this is one of the best tools to increase your productivity writing SQL.

Video Walkthrough

I made a video of getting rid of intellisense. You can also see all my SQL Prompt Tips.

December 13, 2023