What happens when you forget to remember?
When your SQL database is being an asshole for your own good
I try to fear as few things as possible, but I’m not without my phobias. For one, I’m reduced to my infant state at the sight of needles (something that’s caused me a lot of anguish given the, you know, global pandemic and multiple shoulder jabs that come with it).
I don’t fear a lot of things, but database migrations?
Shit, man. They terrify me.
“And terrify you they should!”, sensible senior developers waste no time telling me. Anyway, I recently fucked up a database migration at work, and I thought it made for a teachable moment, which is why I’m now writing this newsletter.
Before we begin, I have an embarrassing story to share. Back in 2020 while still learning programming at Bloom Institute of Technology (nee Lambda School), I launched an app (called Angels Among Us) during the start of the pandemic-related lockdown in Nigeria. When the lockdown was announced, I became very concerned about people who benefited from the informal and semi-formal economy (ie, the street sellers) and how it would put a strain on these families’ sustenance. Angels Among Us was a weekend project that made it trivially simple to make p2p donations.
“People helping people. That’s life,” as the website used to say.
Anyway, because I was an idiot (still am, to a lesser degree), I wrote a small database (in SQLite, I recall), and as the needs for data collection grew, I had to write new migrations. I was still a neophyte at the time and was terrified of migrations, so guess what I did?
I’d download the data from my database (as a CSV file), create a brand new table with the updated schema (using the knex ORM), and painstakingly re-import the old data (that is, the CSV file) back into the database.
I did that every time I needed to update the schema. Bloody hell.
My head of engineering, during my interview (for the role I currently hold), had questions about how I performed migrations and I told him this story and he laughed heartily. Found it charming, thank God, but fuck.
Today I work with PostgreSQL (a pretty popular RDBMS — relational database management system for SQL), and use Sequelize (which boasts that it is a ‘feature rich ORM for modern TypeScript and JavaScript — ORM means ‘Object-relational mapping’, which is a fancy way for saying ‘we figured out a way to write SQL as JavaScript objects. Yay, we are going to be so bad at writing SQL forever.’ )
Writing migrations is dependent on the tools you use (both the ORM and the programming language), the complexity of the database you’re working on, the preceding schema design and the future state you’re attempting to arrive at. If you’re migrating an active table with a billion entries, or a schema migration that locks an active table, you have to start thinking like a scientist. I’ve never had to do any of the above, but it’s only a matter of time before it becomes something I’ll have to mull over.
When thinking about database migrations, it probably helps to start with a broader definition than I supplied before. From the point of view of Sequelize, a migration is “a javascript file which exports two functions, up and down, that dictates how to perform the migration and undo it. You define those functions manually, but you don't call them manually; they will be called automatically by the CLI.”
Huh. A bit of a circular definition there: a migration is a file that dictates how to perform migrations. Let’s see if we can untangle it a little.
Imagine you have a table in your database. It’s a table of workers at a company. Assume we have a column in that table, and assume that column is named managerId. This is a pattern you’ll notice in SQL tables: a table with a foreign key. Here, we’re keeping track of all workers at a company, and also keeping track of their managers via a foreign key (here, the managerId). We’re effectively saying, ‘to find the manager of a worker in the workers table, take the managerId and search in the managers table for a manager with the matching id.) Without mentioning a managers table, we automatically assume there’s one, because of the pattern: where there’s a managerId column, there has to be a managers table somewhere to point to.
(Note how the presence of a departmentId column in the managers table hints at us that there’s probably a departments table somewhere in the database.)
To make this sort of association, we’ll have to introduce a foreign key constraint on the managerId column. What does that look like? In long-form, we’re telling SQL to only allow a manager Id in the managerId table. Practically speaking, we’re telling SQL how to make this association:
Manager.hasMany(Worker);
Reads like good prose. Straightforward, too: Each worker has one manager, and each manager belongs to many workers (since a manager can manage more than one worker). Sequelize is intelligent enough to automatically understand that this implies a ‘managerId’ column on the basis of this arrangement (see docs for nuance).
We can do more than add a foreign constraint. We can insist that all workers must have managers. In the design of the schema for the worker table we can add (on the managerId column):
allowNull: false;
Bazinga. You cannot have a null entry for the managerId column. ALL WORKERS MUST HAVE MANAGERS. You’re doing amazing, sweetie.
You wrap up your commits, push code for review, it gets merged and deployed, evening comes and morning arrives, and it’s the nth day. Things proceed smoothly for months, then one day a PM invites you to a stakeholder meeting about new features.
You sit patiently as they tell you that the company now wants to associate workers with company branches, not managers. Their justification is solid, too — managers have been getting fired, causing a need for unnecessary database modifications because all workers have to be reassigned. But branches are more durable entities (they’re buildings!) and so it makes more sense to associate workers with company branches, not managers.
‘Sweetie’, the PM purrs, ‘can you have the updates to the table done by close of business today?’
Looks easy enough, you think. You just need to rename the managerId column to ‘branchId’. Hey, you may even get time to see your kids before their bedtime!
So you write a migration file that looks something like this:
up: (_, queryInterface) => {
await queryInterface.renameColumn('workers', 'managerId', 'branchId');
},
down: (_, queryInterface) => {
await queryInterface.renameColumn('workers', 'branchId', 'managerId');
}
This is probably a good time to explain what migration files do. Remember the good ol’ definition from Sequelize: a migration is “a javascript file which exports two functions, up and down, that dictates how to perform the migration and undo it.” We just wrote a migration file a few lines above. Note the ‘up’ and ‘down’. Just like life. This file is basically saying:
When you run the migration, look in the workers table, find the managerId column and rename it to branchId
If you want to reverse the migration, do everything in reverse: find the workers table, and rename branchId back to managerId.
God, you’re a genius. Took you three minutes to write that, too.
Then you remember. You also have to change the foreign key constraint. Since the branchId column no longer points to managers, they need to point to branches. How do you fix that?
Well, we could add something to the equation: we could rewrite the attributes, like so:
await queryInterface.updateColumn({
allowNull: false,
references: {
model: Branch,
key: id,
}
});
So you run the migration. All should be well with the world.
But all is not.
You get the first interesting error:
column 'managerId' does not exist for table 'workers'
(Note: I paraphrase. Unfortunately, I didn’t screenshot the error as at the time I got it. so this is probably not the exact error.)
This is a difficult error to debug the first time you get it, but what it means is, your table expects there to always be a ‘managerId’ column (the very column you’re trying to get rid of). That’s because you’re violating a foreign key constraint you set before, when you said ‘let this column, managerId, point to the managers table’). The fact that you ran an updateColumn migration does not change that fact. You did not override the previous constraint — you just added another one.
Your database never forgets.
Okay, okay. Maybe we can revoke the original constraint? As a matter of fact we can! Before we run the updateColumn method, we can do this:
await queryInterface.removeConstraint('workers', 'workers_managerId_fkey')
That should do the trick, and I’m happy to announce to you, from experience, that it does.
So what have you done?
You renamed the column managerId to branchId
Then you removed the foreign constraint (the one which said this column would always refer to a manager)
…then you added a new foreign key constraint (this column will now refer to a branch!)
All’s well with the world, for real this time!
Except…you still have a bunch of entries in the branchId column (from before, remember?) that have ids pointing to managers. Even though you’ve removed the constraint, and rerouted them to the branch table, the data inside them contradicts your new schema design.
This contradiction is why your SQL database was throwing all those errors and curveballs at you. It thinks about all these things. It’s its job to. SQL brings determinism to your database based on your schema design, and when you’re flouting your own rules, the errors are calling you out, fam.
Now you have a branchId of 1234 but only 6 branches, because that branchId used to be managerId until you changed the schema. What are you gonna do?
In my case, I ended up truncating the table (effectively wiping out the old data — it wasn’t a big deal in my instance, I assure you). See the difference between TRUNCATE and DELETE. Riveting stuff.
Depending on your business requirements, may need to keep the old managerId table, and create a new branchId table. I don’t have the answers, Sway. I just wanted to highlight the benefits of thinking like a database (and the pitfalls of not doing so).
I hope this was even a little useful.
Thanks for reading, and please share as liberally as you can. I’m not very consistent with my writing, which is a shame. Still — I appreciate every read and every share.
I do have a noob question here. When the migration was done and I’m assuming you had a branch table already made. How does the new workers table know which branch Id to associate with a worker during the migration? Is this something where you update each row of the workers table with the correct id or is this some automagical thing that happens during the migration
Amazing read, Justin!