MySQL schema migrations with Flyway in Docker

Intro

0:00 Hello and welcome back to episode eighteen, where we're going to change the tables in our database without deleting anyone's account, with versioned migrations. Let's begin with a quick look at where we're going to be by the end of the episode.

0:13 Our database is at version one, with three players in it, and Flyway takes it to version two, which gives the players table a column for health. And the three players are still there, with their experience, and a health that the server is going to fill in as they play.

0:30 On the website of the series you can find more information about the project. The code for the project is open source on GitHub, tagged at each step of each episode so that you can follow along.

Last time

0:42 Now, before we get to today's work, let's quickly go over what we did last time. We gave the player a character screen, with the name, level, health, experience and position that the client already had. Next to them, a camera of its own looks at a copy of our figure, idle and facing the camera, which the renderer draws into a render texture, for Dear ImGui to show. And the C key, the View menu and the window's close button all flip the one flag that decides whether it's open. One of the numbers on that screen is your health, which the server doesn't save yet.

Today

1:14 Today, we start with what it takes to change a table in our database right now, which is deleting all of it. Then we bring in Flyway, which keeps the database's tables in versions, each of them a SQL file in our repo. The first version is the tables we have, and Docker Compose runs Flyway before anything that uses MySQL starts. Then the second version adds a column for health, so that the server keeps it, and the players keep their accounts through the change. And the tests run the migrations too, on each push to GitHub. All right, let's get started with what we keep of a player today.

The problem

1:50 Since episode thirteen, what the game server keeps of a player from one session to the next is their progress: where they are, and their experience. After each tick, it goes into Redis, and the saver takes it on to the players table in MySQL. Health isn't part of it, so when you join, the server gives you full health, whatever you had when you left. So a player who's losing a fight can log out and back in, and come back healed, which is the kind of loophole that players find quickly. To keep the health, the players table needs a new column, and so far, a new column has meant deleting the database.

2:24 So let's see what it takes to change a table in our game today. I've started the game from the end of last episode, and twenty bots have played on it, so MySQL has twenty accounts. Our tables come from db slash schema dot sql, which MySQL runs when it starts on an empty volume, and at no other time. So in episode ten, to add the experience column, we ran docker compose down dash v, which deletes the volumes along with the containers. When MySQL starts again, its volume is empty, so it runs schema dot sql, and makes the tables with whatever columns the file has now. And the twenty accounts are gone, along with each player's progress. That was fine for test accounts, but it's not something we can do to players. We could also run an ALTER TABLE by hand, on each database we have. However, nothing would record which databases have had it, so my machine, CI and a server would slowly drift apart.

Versioned migrations

3:18 The usual answer is migrations, where each change to the tables is a SQL file in the repo, with a version number, and the first one creates the tables. Each database keeps a table of the versions it has run, so the tool that runs them compares that table with the files, and runs the ones that database hasn't, in order. So my machine, CI and a production server all end up with the tables the code expects, however old each database is. And the migrations run as a step of their own, before the servers start, so a server doesn't come up against tables it wasn't written for.

3:52 We could write our own tool to run them, as it's a loop over files and a table, with a lock so that two of them don't run at once. However, the edge cases, such as a file that fails halfway, are where the work is, and that's code we'd have to look after. Laravel, from episode twelve, has migrations of its own, written in PHP. With them, the website would own the tables that our C++ servers depend on, which is the wrong way round. Liquibase is a widely used tool for this, with each change written in XML, YAML or SQL. My proposal is Flyway, which takes plain SQL files, so what we write is what MySQL runs.

Flyway

4:32 Flyway is by Redgate, a company that makes tools for databases, and its engine is open source, on GitHub. Its README says it's a way to evolve a database's schema across all your instances, where the schema is the tables and the rest of the database's structure. It runs on Windows, macOS and Linux, and in Docker, as a command-line tool or from Java. And it works with more than fifty databases, MySQL among them, so what we do today carries over to PostgreSQL and the others. We're going to run it from its official Docker image, so there's nothing to install on our machine.

5:07 Its documentation says that versioned migrations are applied to a database in order, once each, and that a table called flyway schema history keeps track of which ones have run. Each one is a file, named with a capital V, the version, two underscores, and a description, which Flyway shows in its logs with spaces for the underscores. Versions are sorted as numbers, so version ten comes after version nine, and not after version one.

Flyway in Compose

5:33 We start with a new file in the db folder, V1, two underscores, accounts and players, and our tables go into it as they are. So schema dot sql goes, as V1 is now where those tables are created. In compose dot yaml, MySQL's service loses the line that mounted the db folder for its first start, as from now on, Flyway runs our SQL. Then a service of its own, flyway, from Flyway's official image, pinned like the others, to thirteen point ten point oh. Its command is migrate, which runs the versions this database hasn't run yet. Its settings come from environment variables, like Grafana's did in episode four. The URL is how Java programs name a database, so it starts with J D B C and MySQL, then the service's name on Compose's network, its port and our database. The two options after the question mark turn on TLS, and trust the certificate MySQL makes the first time it starts. MySQL's default way of checking a password, caching sha two password, takes it over an encrypted connection, and our servers' MySQL library uses TLS by default too. On Compose's own network, trusting MySQL's certificate as it is will do. On a real server, we'd give MySQL one from our own authority, like our servers have had since episode fourteen. Then the user and the password, which are the ones our servers use. Locations says where the migrations are, a folder inside the container, and validate migration naming makes Flyway stop at a file whose name it can't read. Without it, a typo in a name, such as one underscore instead of two, would make Flyway skip that file, with no more than a line in its log, and the change would quietly be missing. The volume mounts our db folder there, for reading, and Flyway waits for MySQL to be healthy, like our servers did. Then the services that use MySQL wait for Flyway instead, until it has run its migrations and exited with zero. That's the login server, the game server, the saver, the website and the tests, so if a migration fails, none of them start against tables they weren't written for.

7:36 Let's run it, and first, on the database we had until now, which MySQL made from schema dot sql. Flyway finds a schema with tables in it, and no history table, so it refuses to touch it, as nothing in that database records which versions it has run. It suggests a baseline, which records a version for a database without running it, and that's what we'd do for a database we want to keep. Ours holds nothing but test accounts, so this once, docker compose down dash v deletes it, and from here on, each change is a version. Docker compose up builds and starts the login server, the game server and the saver. On the way, it starts MySQL, then Flyway, and waits for Flyway to finish before it starts the servers. Docker compose logs flyway prints what Flyway did while the rest waited. It found MySQL nine point seven, and warns that it's been checked against MySQL up to nine point four. Our migrations are plain SQL, and our tests are going to run through them on each push, so if Flyway had trouble with nine point seven, the tests would show it. Then it made its history table, found an empty schema, and migrated it to version one, accounts and players. Docker compose run flyway info runs Flyway again, with info instead of migrate, and lists each version, with when it ran, and its state, success. Then twenty bots play for a moment, so that the database has players in it, and MySQL has their twenty accounts.

Saving health

9:06 Now, let's look at how the server is going to keep the health. The progress the server keeps of each player gets their health, next to where they are and their experience. So it goes into each player's hash in Redis, and from there, the saver takes it to a new column in the players table. A player who logs out while dead would come back dead, so for them, we keep what the respawn would give them: the middle of the island, at full health. And a player saved before today has no health saved at all, so they join at full health, as they did until now.

9:39 In players dot h, Progress gets the health, an int like the player's own, which starts at zero. In players dot c c, load selects the player's health from the players table too. We leave it without COALESCE, unlike the columns before it, so a player with no health saved comes back as NULL, which our database class hands us as an empty string. What Redis has goes on top, as before, so HMGET asks for the health field too. Then, once the other numbers are read, we read the health, if there is one, and if it isn't a number, that's an internal error, like a broken position. An empty one leaves the player's health at zero, and we're going to see what zero means when the player joins. Save puts the health into each player's hash, next to the experience. In flush, HMGET reads it back for each player the saver takes to MySQL, and the INSERT gets a fifth column, health. Its value goes through NULLIF, which turns an empty string into NULL. A hash saved before today has no health field, so Redis hands us an empty string for it. With our IGNORE, MySQL would put a zero there, with no more than a warning, where NULL says that no health was saved. Over it, we write: a hash saved before we kept health has none, which goes in as NULL. And on a duplicate key, the health is updated like the rest. In server dot c c, progress of is where a player turns into what we keep. A player who's dead gets a progress of their own, and over that check, we write why: the dead come back in the middle, at full health, so that's what we keep. So their progress leaves the position at zero, which is the middle of the island, and keeps their experience, with the full health of their level. Otherwise, the progress has their position and their experience, as it did before, and now it gets their health as well. In world dot c c, join used to give each player full health. Now it does that when the health is zero, and leaves a saved health as it is. Over the check goes the reason: zero when none was saved, as for a player from before we kept health. Last, the column goes into the players table, which V1 creates. Health goes after experience, as a SMALLINT UNSIGNED, which holds up to sixty-five thousand, a lot more health than our levels give. It can be NULL, for the players whose health we haven't saved yet.

Refused

12:01 Let's build it and start it on the database from before, which has run version one, and has our twenty bots in it. Docker compose up builds the new servers, starts MySQL, and then Flyway, which fails. The servers wait for Flyway to complete successfully, so none of them start. Running Flyway on its own shows us why: validate failed, with a checksum mismatch for migration version one. When a migration runs, Flyway keeps a checksum of its file in the history table, and before it migrates, it checks the files against those checksums. Ours doesn't match any more, as we added a line to V1. With schema dot sql, editing the file was how the tables changed, so it's an easy habit to bring along. However, this database has run version one already, so it isn't going to run it again, and the column would be missing here. A fresh database, such as the one CI makes for the tests, would run the new V1 and have it. So our databases would drift apart, without an error anywhere, and that's what the checksum is there to catch. Flyway suggests reverting the file, or running repair, which would record the new checksum. Repair is for a change that makes no difference to the tables, such as a comment, and here, it would hide the missing column. So, version one goes back as it was, and the column gets a version of its own.

13:19 So, back in V1, the health line changed a file that databases have run already. It goes, so V1 is again the file our databases ran. Then a new file goes next to it, V2, two underscores, players health. In it, we change the players table that V1 made, with a new column for health, a SMALLINT UNSIGNED that can be NULL, like we had it. A column that can be NULL works for the servers from before this change too, as they don't name it. So while the new servers replace the old ones, both can run against version two. Removing or renaming a column goes the other way round, in two versions: first, the code stops using it, and then a later version drops it. Algorithm instant asks MySQL to make the change by changing the table's definition alone, which it can do for a new column at the end of a table. If a change can't be made that way, MySQL refuses it, instead of quietly rebuilding the table, which on a table with millions of rows takes a long time.

14:21 Let's run it on the database from before again, with version one and our twenty bots in it. This time, Flyway finds a new version, and the servers start after it. Its log says the schema was at version one, and that it migrated it to version two, players health, in well under a second. Then the twenty bots come back, and log in with the accounts they made before the change. Their rows in the players table carry on from where they were. And once the saver has been round, each of them has a health, which is a hundred at level one, unless a skeleton got to them first. And flyway info lists both versions now, each with when it ran.

Tests

15:00 Now the tests, starting with the players tests, which run against MySQL and Redis. In loads where the player was saved, the second save gets a health. As that no longer fits on one line, the progress gets a name of its own, later, with thirty-five health. After loading, we expect the health to be thirty-five, which is the round trip through Redis. In flushes the players to MySQL, the progress gets sixty health this time, and we save it and flush it. Then the SELECT reads the health column back, so the row we expect ends in sixty. That one goes through the saver's INSERT, and through the column that V2 added. In the world's tests, joins players at full health becomes joins players with the health they left with. Alice joins with no health saved, and gets the full health of her level, as before. And a second player, Maria, joins with forty health, and keeps it.

15:53 Docker compose run tests builds the tests and runs them next to MySQL and Redis, on an empty database, like CI does. Before the tests start, Compose runs Flyway, as the tests depend on it like the servers do, and Flyway takes the empty database through version one and version two. Then all of the tests pass, with the three that now cover health among them. CI runs this step on each push, so a migration that fails stops the build there, before it gets anywhere near a server.

Docs

16:25 Last, the README, where a new section on migrations says how to name a file, and that a migration doesn't change once it has run. It also says how to write a change that the servers from before it can work with. It shows flyway info and flyway migrate, and what a volume from before today needs, which is deleting, or a baseline. And the part on saving players says that health is kept now, along with where you are and your experience.

Wrap-up

16:54 So, today we started with what changing a table cost us, which was deleting the database, and all the accounts in it. Flyway now keeps our tables in versions, SQL files in the db folder, and records which ones each database has run. Docker Compose runs it before the servers, the saver, the website and the tests, and they don't start if it fails. When we put the health column into version one, Flyway refused, as that file had run already, and the change would have missed the databases that ran it. So the column went into version two, which MySQL added in place, and the bots kept their accounts and their progress. Players keep their health now, from one session to the next. And the tests run through the migrations too, on a fresh database, on each push.

Playing it

18:39 Let me know in the comments what you'd like to see next. And if you want to follow along with the series, subscribe to the channel. I hope you find this useful, and thanks for watching.