In his review of Refactoring Databases 18 years ago, @mfowler wrote: "After my refactoring book appeared I was delighted to find sophisticated tools appear with automated many refactoring tasks. I hope the same thing happens with databases, and we begin to see vendors offer tools that make continual migrations of schema and data easier for everyone."
Would be great to get a "state of the art" in 2024 on that. What tools have you used? How did you find using them?
@jasongorman@mfowler I tend to reach for Flyway - by far the best tool I’ve found to date.
I’m not a fan of Ambler’s book - it just never connected with me (from memory too much fluff, not much stuff). IMO it has now been well and truly superseded by Ron Ballard’s book “Relational Databases for Agile Developers” which is packed full of solid advice.
@jasongorman@mfowler The techniques are all battle-tested by myself & friends of mine (I admit I am a bit of a DB nerd), and I presented with Ron a while back.
If I seem a bit panicked it’s because I was expecting it to be an interactive workshop, not me on stage live-coding….but best laid plans etc…😱. I was doing my best duck impression - smooth on the surface, paddling like mad underneath to stay afloat 🤷♂️🤣
@matthewskelton@mfowler I've used ideas from Scott Ambler's Agile Database Techniques book and found they worked very well (provided you don't have someone on the team who just makes database changes on the fly and then forgets about them!) Worked on a couple of teams where we got it absolutely spot on (this was around 2004, I think), but we had to jerry-rig some basic tooling to do it as part of our build-deploy pipeline.
Looks like there are much more mature tools now on the process side.
@matthewskelton@mfowler Still haven't come across automated refactorings yet. I can imagine what they might be: "Rename... column, table" etc, "Extract table", and so on.
I guess where it would differ is that firstly the data would need to be migrated, and secondly these would need to become part of a script that can be replayed in test/prod and so on. Plus the safety aspect, so database versioning and back-ups. But I think tools like Liquibase already handle that end, though I've not used it.
I think that a serious barrier to database refactoring tooling is the lack of standards as to where all the definitions and usages are, of all the tables and columns.
How is the database schema built? Is it a text script file? Is it generated from classes or an XML specification file?
Where are all the uses? Embedded SQL? Dynamically generated SQL?
Tracking down and updating all relevant SQL can be challenging, and is different across projects.
String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";
Here?
That's a generic Java String value. Might be used for anything.
How can we BE SURE that it's SQL applied to the specific database we're refactoring?
Any given program could be doing SQL against a pretty much arbitrary number of different schemas, some owned by this system, some legacy external databases that cannot be changed.
And what if the SELECT statement is built over several lines? Or across functions, classes, or methods? What if some of it comes from configuration files?
(Not crazy: ALL the tables in the systems I've been working on for the past 14 years are specified in vendor-specific XML files.)
Let's consider this line in the CustomerRowMapper class:
customer.setID(rs.getLong("ID"));
Is this "ID" the one for the "CUSTOMER" table, and no other tables?
I would have to conclude that you cannot know for certain by looking only at this class.
OK; the generic parameter for this class is the Java "Customer" class. But how would an IDE know that some Java class called "Customer" corresponds to the "CUSTOMER" table we have in mind?
Our APIs and libraries, as currently defined are too flexible. There is almost no way to be sure that we have found all references and not mistakenly included incorrect references.
One can use heuristics. That's what we do, as humans. But how are you going to explain it to top management when the heuristics corrupt their production databases?
@JeffGrigg@jasongorman@paladin@mfowler all this discussion about "how can we be sure that this SQL applies to a specific database or table?" is another argument in favour of strong ownership/stewardship of services and data.
If a single team (or group) is the only one making changes, then they already know which SQL applies where within their area of stewardship.
No need to hunt around for usage: the team already knows because their services are team-sized.
Where another team needs to use SQL, the team that owns the database schema can define in-database “APIs” for the other team as views, procs and other database objects, and use database access controls to ensure that the other team cannot access the schema directly. The owning team can test that any changes they make to the schema do not break the contract with the other team.
Having one team to control all data access and keep anyone else from having dependencies seems nice, ... until you realize that they're now a bottleneck for all work everywhere. No one can implement a "vertical slice" of useful business functionality. Every last change to the system requires coordination with yet another team, with a different backlog, priorities, and staffing issues. More queues. More delays.
@JeffGrigg that's not quite true. Older ways of working that need immediate consistency result in that horrible dependency mess, but newer (or newly rediscovered) ways of working expect eventual consistency and design work and architecture to enable faster flow.
In the context of services without downtimes, one of the things people struggled with was that there needs to be compatibility between versions, so you need expand/contract patterns rather than, say, "rename column". If the rename is done in one step, the currently running version of the code breaks.
@jasongorman@paladin@matthewskelton@mfowler Yes, that's effectively what we did. But since we had no downtime (think about a progressive rollout), the database needed to be compatible with two versions of the code at the same time. A rename needs to proceed in a couple of steps (expand-contract pattern).
Add comment