Introducing Retrofit

In my last couple roles I worked with teams spending an outsized amount of their time either avoiding making changes to their SQL database schemas or cleaning up debt in their schemas and production databases.

The non-avoiders were performing simple tasks like fixing the types on primary keys (yes, they were running out of numbers), renaming columns, linking tables with foreign keys, or deduplicating columns across tables and the data within those tables (how many email columns do you have, company wide?).

Each change required days or weeks of planning, testing against snapshots, and finally scripted zero-downtime changes against production, only to be babysat by one or more senior engineers over a weekend.

The avoiders eventually just hired consultants, like myself, to assist with the fixes (yes, they were running out of numbers).

It felt like there was a better way.

As a developer I’ve been refactoring code for decades. But I’ve also run away from many projects that required a SQL backend, in part for the compounding complexity and a lack of simple refactoring flow.¹

And as a developer and engineer, I usually attempt to find and build better ways to solve repeating challenges.

In the early 90’s I was building interactive web sites for clients in C++ (and frequently SQL). I felt there must be a better way than C++ (or perl), so I created iPerform, a database-backed web scripting language for Netscape and MS IIS web servers.²

A decade later I was consulting on a large geo-spatial project in Europe. The team was force fitting a relational database model into what amounted to a document model. My bias against working with relational databases may have colored my opinions, but this felt overly complex and wrong. It was definitely very difficult to develop against.

At about the same time Apache Hadoop broke out of Apache Nutch and I was one of the first to kick the tires.³

With some of my former client’s challenges in mind (not exactly the document model mismatch) and the intent to bypass SQL entirely, I realized Hadoop needed an alternate API, so I created Cascading (a suspiciously relational model as an API) to solve those problems in a better way than Map-Reduce could.⁴

Having started my career build SQL driven applications and having evolved through and contributed to the NoSQL movement, now I find myself back to facing SQL backed applications, or interfacing with them more often.

So I’ve spent the last few months reflecting, researching, and designing what I hope is a better way to design, build, and importantly, maintain database-backed applications. Especially applications that have accumulated debt and need to be modified to unlock more value from them. And doubly so for applications being operated by second or third generation engineers who fear making structural changes.

We’ve spent all our time helping developers write code faster, but the actual bottleneck is the database.

“The trend I’m hearing more and more often is that this bottleneck is happening at the database.”

– Nicole Forsgren, PhD, DORA co-creator, ComputerWeekly (2018)⁵

Developers need to refactor, collaborate, and release database schemas with the same level of trust as they do with source code. This requires schema changes to be safe by construction.

“Schema changes (i) require costly data migration, (ii) burden the DBA with tedious and error prone maintenance for queries, and (iii) cause unacceptable system downtimes.”

– Carlo A. Curino, Hyun J. Moon, and Carlo Zaniolo, Graceful Database Schema Evolution: the PRISM Workbench (2008)⁶

With the emergence of LLM coding agents, the problem becomes more acute. LLM agents are great at making code changes, but I do not want one writing scripts to modify my database.

So if an LLM is given a tool that intermediates changes to the database, and provides a query interface to the underlying semantic and structural model, I believe the LLM will be much more effective at refactoring an application and it’s underlying data model, as will developers.

With more trust in the schema and data changes, the smaller the database release bottleneck becomes, the more value developers can ship to users in a given amount of time.

This is why I created retrofit.

Migration tools typically manage what your schema is. retrofit captures how it changes.

retrofit is a CLI (command line interface) that maintains a text log of logical schema change operators, not hand-written DDL. The operators are logical, thus a column rename is one operation with one logical outcome, not as raw DDL without any semantic meaning or history. There is value in quickly seeing and understanding how a table or column has changed over time and the dependencies between, for example, tables and views.

AddColumn, RenameColumn, CastColumn, CopyTable, DecomposeTable, and MoveColumn are just a few operations out of about 70 retrofit supports. Each of these are a refactoring operation, many of which are reversible (data neutral), and the details on how they are implemented (executed) is deferred until the snapshot or migration scripts are rendered, on demand from the CLI.

With a log of change operations (an oplog), you can render a DDL snapshot of your database (with or without seed data in DML) at any point in the change history.

Or render migration scripts between any two points in the change history. And if the operations are reversible, then you can render a script that downgrades a database.

And if you (or someone else) changed your database in place, you can detect the drift and import the delta into the oplog to bring it up-to-date. It can even detect common refactorings, like a column rename, or table split or join, and mint them as a logical operator.

Sealing a set of operations allows you to mark a stable version in the oplog. The seal itself allows for quick detection of any edits in the log that may have been merged in by accident that could corrupt your production database if they were applied. If the seal is broken, you are prevented from rendering a schema snapshot or migration script until recovered (from git or user intervention).

Actually, since the oplog is safe by construction, you cannot add an operation that makes no sense, like renaming a non-existent (dropped) column or table.

You can see retrofit in action on retrofit.sh.

There is still a lot more work to do before I release it publicly. Plus I have a fair bit of off-the-grid vacationing between now and then, and a bit more coding work to do.

On the site you can sign up to be notified when it’s released. If interested in early access, you know how to reach me.