Increasing the Visibility of Database Changes in Flyway Development
If you save a metadata 'model' for every new version of a database created by Flyway, you can compare the current model to the previous one to see what changed. In turn, you can then generate a database E-R diagram that highlights the changed objects, instantly making those changes visible to other team members.
In any database development environment, other team members may want to know what tables, views or other database objects you are working on, especially if some of the objects they are working on are dependent on yours, or vice versa.
It is easiest, of course, to be working entirely independently of your colleagues, but that doesn’t always happen. Often, someone will be working on a function that uses a table you’re changing. The potential for conflicts increases if you are, for example, re-engineering a group of tables and their dependencies. The rest of your team probably don’t want to know about all the changes, just the ones that affect their work. You’ll also want to know what they are up to if you’re working in a branch, and they are working on the same objects (or dependent objects). You’ll want to know about changes being made to the parent.
When you’re working on a single communal development database, it doesn’t take long to detect other changes. Well, it doesn’t take long if you are geeky. In SQL Server, for example, there is the default trace, and the ‘modified date’ in the metadata. For the rest of us, the way to achieve ‘visibility of changes’ is via a graphical display of the hierarchical list of objects, which highlights an object in some way, if it’s been changed since it was created in the database. As a more sophisticated display, SQL Source Control used the ‘spinning orbs’ in the SSMS object explorer pane.
However, almost no way of representing a database and its objects, except perhaps a heatmap, scales to the huge size of a typical corporate database. In this article, we’re going to tackle the requirements for a fairly typical Flyway database, meaning one that is compact, and serves a single application.
Detecting metadata changes
One might have thought that there would be a simple generic way to detect metadata changes in a database, but there is no SQL Standard to support this.
We’re starting out with the modest aim of making metadata changes more visible; we want to notify other team members when ‘objects of interest’ are created, dropped, or modified. The first difficulty we face is in trying to work out when an object is simply renamed, because a simple comparison will tell us that one object was deleted and a similar one created with a different name. We need a way to detect changes reliably and, if we are looking at the broad sweep of relational databases, we need it to extend across the entire database estate. We also have the problem of how to monitor all the copies of databases in your project. If a developer is working on a feature branch, they need to know if they are using a group of related tables that you’re about to re-engineer.
Once you have a slick method of identifying a change, you need to be able to see what other database objects might be affected by a change. This entails mapping the relationships. Is that table that Mary has changed going to affect the feature that Mo and Jeff are working on? A database object is likely to have ‘hard-wired’ relationships, enforced by constraints, and ‘soft-wired dependenciespenencies, where a view, procedure or function has a query that references one or more tables. It is relatively easy to detect a hard-wired relationship, but only SQL Server, Oracle and PostgreSQL, as far as I’m aware, allow you to list soft relationships easily.
Alerting the team of metadata changes
Once you’ve found a way of finding out when an object is being altered, added or deleted by someone in the team, you have to decide how to inform anyone who needs to know about it. Every team has their own way of handling team communications and I’m not going to get into that in this article; However, I ought to explain that there are a range of ways of doing this.
You can choose the route of sending alerts to individuals. This means that you’re likely to need a way of registering an interest in certain objects. It also means that team members need to be able to specify what they want to hear about. This seems like hard work, and most people would prefer to just declare an interest of all objects related in any way to a named object.
How should you implement alerts? There is still a lot to be said for emails but they can be a bit overwhelming. Most development teams seem to use Chat systems such as Slack, Gitter, RocketChat or Mattermost. The issue here is whether you can use cloud-hosted systems or maintain your own Chat servers. Whatever system you choose, Webhooks and Bots are the usual ways of sending alerts from a script. These are automated messages that are sent to a unique URL. They’re much like SMS notifications.
The most interesting problem to solve with this sort of notification system is to work out who to send a message to for any particular changes. Whichever way you choose to do it, you need to be able to direct them to a page that is referenced by a link. This makes a website or document wiki the ideal way of storing your development documents. I generally settle on a website-based team reporting system, because it is simple to do and has plenty of support. I use a LAN-based website with access control. Nowadays, in the absence of an Ops guy who’s keen on all that, I just get a NAS Device with website, chat, Git, wiki and all the other services we need already installed.
After you and your team have decided on how to manage alerts and display your documentation, how would you get the information and display the changes?
How to get metadata changes from Flyway
If you are using my Flyway Teamwork PowerShell framework, designed for use with Flyway Teams edition, a lot of the foundation work is already done. There is a task in the framework that will generate a JSON model of the database (as well as object-level JSON files) for every successful migration ($SaveDatabaseModelIfNecessary), and another that will compare the current database model to the one for the previous version and tell you what has changed ($CreateVersionNarrativeIfNecessary). You can run these tasks in a script callback, with Flyway Teams, or you could otherwise do it after calling Flyway in a script.
I won’t repeat the details of how to generate the models and reports here as I’ve explained it all in previous articles. For example, Simple Reporting with Flyway and Database Models shows how to generate the JSON model, and Reporting on Changes Made by Flyway Migration Scripts shows how to run the task as a script callback. Also, Database Development Visibility using Flyway and PowerShell explains how to generate the ‘metadata changes’ report.
If you’re using my FlywayTeamwork-Pubs project, the following code will create versions 1.1.1 through to 1.1.8 of the pubs database, successively, generating the JSON models and metadata change reports for each version:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
cd <MyPathToTheFlywayDirectory> . '.\preliminary.ps1' Write-Output @" Processing the $($dbDetails.variant) variant of $($dbDetails.branch) branch of the $($dbDetails.project) project using $($dbDetails.database) database on $($dbDetails.server) server with user $($dbDetails.installedBy)" "@ $PostMigrationTasks = @( $GetCurrentVersion, #checks the database and gets the current version number #it does this by reading the Flyway schema history table. $SaveDatabaseModelIfNecessary, #Build a JSON model of the database $CreateVersionNarrativeIfNecessary ) @('1.1.1', '1.1.2', '1.1.3','1.1.4', '1.1.5', '1.1.6', '1.1.7', '1.1.8') | foreach{ Flyway migrate "-target=$($_)" # Migrates the database to a particular version if (!($?)) {break} Process-FlywayTasks $dbDetails $PostMigrationTasks } |
You’ll find these in each Versions directory. Two of these provide information about metadata changes, one JSON version and one CSV (filetype ‘.report’):
Here’s an extract from the JSON version:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
{ "Ref": ".dbo.table.discounts.Foreign key.FK__discounts__stor___173876EA", "Source": "dbo.stores", "Target": { }, "Match": "<-" }, { "Ref": ".dbo.table.employee.columns[6]", "Source": "pub_id char(4) -- the publisher that the employee works for", "Target": "pub_id char(8) -- the publisher that the employee works for", "Match": "<>" }, { "Ref": ".dbo.table.authors.Check constraint.CK__authors__au_id", "Source": { }, "Target": "([au_id] like \u0027[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]\u0027)", "Match": "->" }, |
The first entry tells us that the foreign key the foreign key FK__discounts__stor___173876EA of dbo.table.discounts was deleted. The match was ‘<-‘, meaning it was in the source (the model for the previous version) but not the target (the model for the current version).
The second tells us that the sixth column of the table dbo.employee (it calls it dbo.table.employee.columns[6]) is still called pub_id, but has been changed from char(4) to char(8). The match was ‘<>‘, meaning it was in both the source and target, but different.
The third entry tells us that the CHECK constraint dbo.authors.CK__authors__au_id has been added and its new value is ([au_id] like \u0027[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]\u0027). Simple, eh?
This file can be read into a script and interpreted.
Generating a database diagram showing which objects changed between versions
We’ve seen that a database model can be generated at the end of every migration run. These are then to generate the JSON file of metadata changes, by comparing the current model with the previous one. This means that not only do we know the changes, but we can draw an entity diagram of some or all the database design on every migration, showing the objects that have changed.
You could, of course, try to kid your manager that you stay late every evening to draw these entity relationship diagrams by hand, on every migration. You and I know that there is another task in the framework that will do this for you.
In a previous article, Automating Flyway Development Chores using Database Diagrams I showed how to generate the PUML code for a database model. Here, I’ve taken that a step further and included highlighting of changed objects. I’ve also rolled all this into a new framework task called $WriteOutERDiagramCode.
So, if we simply run the following code…
|
1 |
Process-FlywayTasks $dbDetails $WriteOutERDiagramCode |
…it will generate the PUML code from the JSON model of the current version (v1.1.8, in the previous example). It will incorporate highlighting of any changed objects, by inspecting the MetadataChanges.json report for that version. It saves the resulting ERdiagram.puml file to the reports subfolder for that version.
All you then need is the web-based PlantUML editor or the plantumlc.exe application (with the latter, you get to be able to specify the type of output) to render the above PUML code into an E-R diagram. If you’re using the online editor, simply paste in the code and hit “Submit“:
This diagram tells us that the latest migration added the Editions table and the Publication_Types table and altered the TitlesAndEditionsByPublisher view. At this stage, that’s all we need to know. It is like the fact that the Fire fighters just need to know the street. Once they are there, the smoke and the shouting will tell them the rest. Likewise, a quick scan of the change report will tell you what’s changed.
I’ve used the ‘hand-drawn’ style because it is a standard way of denoting that the diagram is a work-in-progress. In this case, it is a database design work-in-progress and nobody is pretending that this database design signifies a real release.
Here’s the PUML code source for this diagram, as produced by the $WriteOutERDiagramCode task:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
@startuml skinparam class { BackgroundColor WhiteSmoke ArrowColor black BorderColor gray } skinparam wrapWidth 150 skinparam handwritten true skinparam monochrome false skinparam packageStyle rect skinparam defaultFontName Buxton Sketch skinparam shadowing true skinparam MessageAlign left skinparam header{ FontColor black FontSize 14 } skinparam footer{ FontColor black FontSize 10 } left to right direction Title Pubs project develop branch PubsDev 1.1.8 header Date: 16/05/2022 footer <back:pink>Pink background</back> means deleted <back:lightgreen> green Background</back>means added and <back:gold>Gold background</back> means altered. !define table(x) class x << (T,mistyrose) >> !define user_table(x) class x << (T,mistyrose) >> !define view(x) class x << (V,lightblue) >> !define dml_trigger(x) class x << (R,red) >> !define table_valued_function(x) class x << (F,darkorange) >> !define aggregate_function(x) class x << (F,white) >> !define scalar_function(x) class x << (F,plum) >> !define clr_scalar_function(x) class x << (F,tan) >> !define clr_table_valued_function(x) class x << (F,wheat) >> !define inline_table_valued_function(x) class x << (F,gaisboro) >> !define stored_procedure(x) class x << (P,indianred) >> !define clr_stored_procedure(x) class x << (P,lemonshiffon) >> !define extended_stored_procedure(x) class x << (P,linen) >> stored_procedure(dbo.byroyalty) stored_procedure(dbo.reptq3) table(dbo.authors) table(dbo.discounts) table(dbo.editions) #lightgreen ##[bold]green table(dbo.employee) table(dbo.jobs) table(dbo.prices) table(dbo.publications) table(dbo.Publication_Types) #lightgreen ##[bold]green table(dbo.publishers) table(dbo.pub_info) table(dbo.roysched) table(dbo.sales) table(dbo.stores) table(dbo.TagName) table(dbo.TagTitle) table(dbo.titleauthor) view(dbo.PublishersByPublicationType) view(dbo.titles) view(dbo.TitlesAndEditionsByPublisher) #gold ##[bold]saddlebrown view(dbo.titleview) dbo.discounts }|..|| dbo.stores dbo.editions }|..|| dbo.publications dbo.editions }|..|| dbo.Publication_Types dbo.employee }|..|| dbo.jobs dbo.employee }|..|| dbo.publishers dbo.prices }|..|| dbo.editions dbo.publications }|..|| dbo.publishers dbo.pub_info }|..|| dbo.publishers dbo.roysched }|..|| dbo.publications dbo.sales }|..|| dbo.stores dbo.sales }|..|| dbo.publications dbo.TagTitle }|..|| dbo.TagName dbo.TagTitle }|..|| dbo.publications dbo.titleauthor }|..|| dbo.authors dbo.titleauthor }|..|| dbo.publications @enduml |
Working with the $WriteOutERDiagramCode PowerShell task
This task that can be executed after every migration, or just for any version in which you are interested. Since it uses just a couple of data files, it doesn’t even need a database connection, which makes things a lot easier. It just creates the PUML file that you can then use to create the type of image that you want. If you need to display the image within HTML, then an SVG file is going to give a better result than a JPEG file.
As well as generating the E-R code for the current version (as above), we can generate it for any other version just by specifying the version as a parameter:
|
1 |
Process-FlywayTasks $dbDetails $WriteOutERDiagramCode @('1.1.6') |
We can change other parameters too, but they are ignored if set to NULL. This one works like the previous examples but just does version 1.1.7 of the current project:
|
1 2 3 4 5 6 7 8 |
Process-FlywayTasks <a id="post-6920116-_Hlk103862067"></a>$dbDetails $WriteOutERDiagramCode @( '1.1.7', #version - the flyway version of the database. Leave null if using framework $null, #Title - the flyway project. Leave null if using framework $null, #FileLocations - where to store all files $null, #MetadatachangeFile - Specify if not using the default location $null, #modelFile - Specify if not using the default location $null #MyPUMLFile - The path to the PUML file ) |
Here we get more ambitious, and we change the title and the location of the files:
|
1 2 3 4 5 6 7 8 |
Process-FlywayTasks $dbDetails $WriteOutERDiagramCode @( '1.1.7', #version - the flyway version of the database. Leave null if using framework 'MyTitle', #Title - the flyway project. Leave null if using framework 'MyFileLocation', #FileLocations - where to store all files $null, #MetadatachangeFile - Specify if not using the default location $null, #modelFile - Specify if not using the default location $null #MyPUMLFile - The path to the PUML file ); |
We don’t actually need any database details because we are getting all our information from file. We were just using it to get the version, file locations, and the details for the title. That means that if we explicitly provide the file locations, we can call it without the full $dbDetails hash-table.
|
1 2 3 4 5 6 7 8 9 10 |
Process-FlywayTasks @{ problems=@{};warnings=@{};feedback=@{};writeLocations=@{} } $WriteOutERDiagramCode @( '1.1.7', #version - the flyway version of the database. Leave null if using framework 'MyTitle', #Title - the flyway project. Leave null if using framework 'MyFileLocation', #FileLocations - where to store all files $null, #MetadatachangeFile - Specify if not using the default location $null, #modelFile - Specify if not using the default location $null #MyPUMLFile - The path to the PUML file ); |
You can find the $WriteOutERDiagramCode scriptblock task, along with all the other tasks, in the DatabaseBuildAndMigrateTasks.ps1 file in the Resources folder of the framework.
Conclusions
As it stands, this utility gives you a diagram for each schema. If you have a lot of tables in a schema, you’ll run out of space. You’ll usually be working on a group of linked tables, though and so you’d want to alter the code to do a subset of tables. I’ve not added that feature to the $WriteOutERDiagramCode scriptblock task but my Create-PUMLEntityDiagram.ps1 cmdlet includes support for generating E-R diagrams for a subset of tables, so you can see how it’s done.
While I was debugging all this code, I got to look at a few E-R Diagrams and, embarrassingly, noticed several errors in the practice database. Although they weren’t intentional, it turned out that they were rather useful for demonstrations, so I left them in; but it proved the point that there is a great deal to be said for a visual representation of your database when you are developing it.
Tools in this post
Flyway Teams
Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.
You may also like
-
Article
Testing Databases: What's Required?
An overview of the challenges of database testing and test data management, reviewing the different types of database test that need to run during development work, what sort of test data they require, and how to manage all the required data sets, during development, in a way that allows rapid cycles of parallel testing.
-
Article
Testing a Flyway Database Migration
Often, we want to test the new version of a database, produced by a Flyway migration, before committing the new migration file, or to test the same migration run on a number of different databases. This article demonstrates how to do it, by generating and using JSON parameter files to run a series of Flyway actions on any number of databases, on any number of servers.
-
Webinar
JPA + Flyway: Database Migration Challenges in Java Apps
Database migration is an essential part of the development process. It includes many challenges: from writing the migration scripts in the first place to making them runnable and avoiding loss of data. In this webinar we’ll look at common DB migration challenges and how to overcome them.
-
Article
Tracking Development Changes using Flyway and Database Models
A database model is a standard document that represents the logical design and structure of a database. If we save a model each time Flyway creates a new version of the database, then we can find out what's in each version, and get an overview of how that structure changed between any two versions. This has all sorts of uses in team-based database development work.
-
Article
A Database DevOps Workflow Using Flyway Enterprise and Redgate Test Data Manager
Tony Davis describes a typical database development cycle and deployment pipeline supported by Flyway Enterprise and Redgate Test Data Manager. It allows branch-based database development, using disposable databases (clones) and version control tools, promotes continuous integration and testing of changes and automates the build and deployment processes so that they are repeatable, fast and reliable.
-
Article
Running Unit and Integration Tests during Flyway Migrations
In this article I'll give a practical example of developing a database, with Flyway, in such a way that it is automatically tested with whatever unit tests and integration tests you specify whenever you migrate the branch you're working on to the next version. If a test fails, you can work out why, undo the migration and then try again.
