Database Migrations and You

Database?

(Skip if you understand the phrase "SQL Server relational database")

A database, for the purpose of this discussion, is a service that hosts managed data in tables. This data is stored in rows, with each column of the table representing specific property of the data entered. An example is below:

The above table represents the kind of data that would be entered into a relational database: the kind like my site, and some others, uses.

So, Database upgrades?

Yeah, database upgrades. They're hard. Database schemas (the headers of the table up there) aren't really meant to be upgraded, they're usually pretty static. Or meant to be. But people make poor design decisions for their databases, and so, we must update them.

I, in fact, made some pretty poor decisions about my own database for this site. It was a pain in the ass to fix, mostly because I had no idea what I was doing. But now I do.

Back to database upgrades. So, any decent database server (software that hosts the tables you store your data in) will have some interface for you to manipulate your data.

For my database, MariaDB, this interface is on the command-line. I personally love it, but I have some colleagues who definitely wouldn't. Through this interface, you can manipulate both the data and the table schema with various commands. This is the first, and most simple approach:

Doing It By Hand.

The DIBH method is awful, terrible acronym meant to encapsulate that. First of all, how are you supposed to register your changes with whatever source control you use? And if you don't use a source control for your database and whatever is reading from it, may you find solace in the afterlife because I will beat you with a hammer.

The DIBH method is typically done by those who don't know jack shit about database administration. Hi, I'm Lylink, and before yesterday, I knew jack shit about database administration. Yeah, come at me with your hammers, I deserve it. I updated everything with the database manually, because:

1. My database schema wasn't committed to my source control. The horror!
2. I was making small, incremental changes.

This all changed when the terrible design decisions attacked.

For you see, I had failed to realize that if I wanted to make any changes, I had to painstakingly do it manually, risking destroying the data in my database I had worked so hard on (read: decent writing and blog posts).

In comes the next approach, draped in silk and finery:

Re-learning SQL and Writing Scripts to Handle it instead.

Yeah, I had to re-learn SQL. This made my life so much easier, though. Instead of testing and running various different commands, I could instead create a script, commit it to my source control along with the prior and new versions of my database schema, and have a record of the changes I was making!

It was great! Frankly, this is where you could stop with your database migration strategies. It works fine. It makes your database migrations work totally fine, meaning that you can set up a script for database changes, test it on your local machine with a copy of your production data, and see that...

goddamnit, it's broken? Well, at least you can see it's broken.

This is a great way of doing database changes. It's something you can commit to your source control, it works fine. But I was not satisfied, reader! For you see: every time I made a database change, I had to manually run the script via the command line! It sucked!

(There were some other, more technical reasons why this didn't work great for me. Specifically: it had strange fail states due to some idiosyncrasies with MariaDB, and it made database migrations a small pain.)

This is where the final, and in my opinion, easiest step is.

Write a CLI App to do it for you.

Truly, the peak of software engineering. Why bother doing all this shit manually if you can just write a script to do it instead? And reader, it works like a beaut.

It is fantastic. All I need to do is set my database migration scripts up in folders, and it'll automatically check against each of them, pull the database migration scripts that are needed to get it to the latest version, and then it runs them. And it works!

It'll make upgrading my database on the website's server a lot easier. Just queue up the Database Migrator CLI app with the upgrade scripts, and watch it go.

But, reader, there's a fourth step, and beyond. Don't let my decision to stop here mark the end of your road for database management. For example, you can just buy a managed database from your hosting provider if they have the option. Super easy. Or, you can set up actions to migrate your database to the new version on merge into develop.

But one of those is the coward's way out, and the other is too hard. So I'll sit here, manually updating my website's database with a script.

Thanks for reading.