Phil Factor

15 May 2025

• Flyway
• Tracking development changes
• Team-based Development

Tracking Down Object Changes in Flyway Migration Files

One of the common frustrations when participating in database development through Flyway migrations is the difficulty of finding out where a specific object, such as a table, view, or stored procedure, was last changed or created. This isn’t a problem with small-scale database development, but it becomes especially challenging once your project accumulates hundreds of migration scripts scattered across several subdirectories.

Whether you’re debugging an issue, looking for comments or documentation about a table or index, performing an audit, or simply trying to understand how a schema evolved, you need an efficient way to search across the migrations for relevant DDL changes to a specific object.

Increasing the visibility of Flyway development

I spend a lot of time inventing ways to automate Flyway development. It’s not just to save myself from tedium, though I’m easily bored, or to eliminate human error, though that helps too, as I’m also forgetful. I mainly use DevOps automation to increase visibility into the development process. I explain why in Database Development Visibility using Flyway and PowerShell: when many people and applications rely on a shared, organizational database, we need to make changes visible early. Database developers need to stay aligned on ongoing changes to avoid conflicts and dependency chaos. Report writers, testers, developers, and project managers all need time to adapt, coordinate, and give input while designs are still flexible.

I’ve previously shown how to diff two database states to get a high-level overview of changes made by Flyway migrations, and how to compare versioned database models to identify structural differences. The goal in this article is a bit different: we want to help developers search in the migration files themselves to find exactly where and how a specific object has changed over time.

Code Visibility: Browsing through Flyway Migration Files took the first step by turning SQL into HTML for easier reading and review. The next step was automation and Searching Flyway Migration Files using Grep and Regex showed how to use Select-String and Regex to search through migration files and piece together information on which objects were changed. This article builds on that work and turns it into a more versatile, and “arms-length” way to track down object changes across a growing set of migrations. We wrap the process into a reusable PowerShell function that handles nested folders, filters by migration type, and outputs annotated matches with useful context.

Why searching in Flyway migration files is not so simple

Flyway’s file-naming convention will often hint at what each migration might contain (e.g. V1.1__create_jobs_table.sql), but we need to be able to search inside the files to identify exactly what objects were changed or created and when.

The idea in this article is that a developer provides credentials, the location of the migration files, and the name of an object to a PowerShell function (Find-ObjectDDL) and it will search the files in version order and return a report of where and how the object was created or altered.

So, what are the potential complications?

Searching in version order

We need to search migrations in version order so that we see all the changes in the correct version and can understand the sequence. In the Grep and Regex article, I did that by connecting to the database and fetching the versions from the JSON output of flyway info. Here, I just parse version numbers directly from filenames to sort them in proper version order.

Searching different types of migration

Another obvious detail to take care of is specifying the type of migration to search. Normally it will be the versioned migration files, but I can imagine wanting to search within undo or baseline files. The utility I provide lets you select the types of files you want to search.

Determining Flyway locations

Perhaps the trickiest part is figuring out where all the Flyway migration files are located. They can be located in several locations, each one potentially containing a set of deeply nested-directories. These locations are usually configured in your project-level configuration file (flyway.toml) but these can also be overridden or augmented by:

• environment variables (e.g., FLYWAY_LOCATIONS),
• command-line parameters (-locations=…), or
• additional config files merged at runtime.

Flyway merges all these inputs at runtime, but it doesn’t expose the final resolved list via the CLI. There’s no built-in command that tells you exactly which locations are active, so it’s difficult to know where to search.

In a simple Flyway project, where all the migrations are in a single folder, we can just provide the path to it. But if not, how do we resolve all these locations, so we know we’re searching through all the active migrations?

If you’re using my Flyway Teamwork framework, it does the hard work for you. When you run its preliminary.ps1 script it replicates Flyway’s full configuration resolution logic, including overrides from environment variables and additional config files. It returns a structured $DBDetails object containing the final list of filesystem: locations, ready to pass directly to Find-ObjectDDL.

If you’re not using Teamwork, one workaround is to capture the output from Flyway’s debug output (flyway info -X) and then use a regex search to extract the resolved locations into a $locations variable. It’s a bit of a compromise: it won’t detect locations passed as direct parameters, and it runs quite slowly, but you only need to do it once per debug session.

A practical PowerShell solution

The PowerShell script I provide, Find-ObjectDDL, acts as a wrapper around Select-String (PowerShell’s version of GREP). It searches through your Flyway migration files for ALTER or CREATE statements that reference a specified database object, giving you just enough surrounding context to understand the change.

You specify the object name to search for by providing a name or a regex string, and the list of Flyway locations to search. You can also specify:

• The type of migration (e.g., ‘V’ for versioned, ‘U’ for undo, ‘B’ for baseline, ‘R’ for repeatable)
• How many lines before and after each match you want to include for context

Why you might need a utility like this

For developers, this kind of traceability is essential when:

• Preparing documentation or a changelog
• Merging branches and verifying changes across features
• Debugging unexpected schema changes
• Understanding object dependencies in layered architectures

Flyway doesn’t track what’s inside migration files, so this tool fills that gap with a lightweight, scriptable way to locate and review changes.

It could also be useful to DBAs who need to trace the history of a table or procedure across all relevant scripts, without guessing filenames or connecting to a live database. In that sense, I hope it’s a small but practical step toward smoother collaboration, earlier feedback, and fewer surprises in testing or deployment.

Usage Examples

<# Search migration files in a single location for all changes to the 'jobs' object in versioned migrations #>
Find-ObjectDDL -ObjectName 'jobs' -locations @('filesystem:.\Migrations') -MigrationType 'V'

<# Search the migration files locations defined in the variable fetched from Flyway info -X #>
Find-ObjectDDL -ObjectName 'address' -locations $locations

# Use the Flyway Teamwork framework
. .\Preliminary.ps1 
Find-ObjectDDL -ObjectName 'jobs' -locations $dbDetails.locations -MigrationType 'V'

How It Works

1. The Script parses the location list you provide and converts Flyway’s filesystem: entries to local paths.
2. Recursively scans up to 5 levels deep in each directory and its subdirectories for migration files.
3. Extracts metadata (type, version, description) from each migration filename.
4. Orders the files by version number by converting the string version number into a .NET object to allow the filenames to be sorted in version order.
5. Filters by migration type, e.g., only V (versioned) migrations.
6. Use a case-insensitive regex search to find DDL statements matching your object name.
7. Prints the line and file where the string was found, and the matched string, the contextual lines from before and after each match, so you can read what’s being done without needing to open the file. The number of lines is adjustable.

This is especially useful in teams using Flyway where migrations are often grouped into various directories and a developer may not know where a change was made or who made it.

Sample output

CREATE TABLE People.Address /*This contains the details of an addresss,
any address, it can be a home, office, factory or whatever */
  (
  Address_ID INT IDENTITY /*surrogate key */ CONSTRAINT AddressPK PRIMARY KEY,--the unique key 
/* line 171, file: V1.1.10__AddAddressesPhonesEtc.sql */
;
GO
CREATE TABLE People.AddressType 
/*  the way that a particular customer is using the address (e.g. Home, Office, hotel etc  */

  (
/* line 303, file: V1.1.10__AddAddressesPhonesEtc.sql */
  );
/* the email address for the person. a person can have more than one */
CREATE TABLE People.EmailAddress
  (
  EmailID INT IDENTITY(1, 1) NOT NULL CONSTRAINT EmailPK PRIMARY KEY,--surrogate primary key 
  Person_id INT NOT NULL CONSTRAINT EmailAddress_PersonFK FOREIGN KEY REFERENCES People.Person, /*

The full code listing for Find-ObjectDDL

Conclusion

Over time, especially in larger projects or teams, the only reliable history of how, why or when a database object such as a table or view was changed, may be buried in the migration files. A targeted search through these files, sorted in version order, is often the quickest way to locate the original definition of a table, the rationale behind a structural change, or even embedded end-of-line comments that never made it into formal documentation. Tools like this PowerShell wrapper around PowerShell’s Select-String make it feasible to extract that information quickly, preserving the context that would otherwise be difficult to find.