Database Migrations

Database migrations are a complicated topic. While easy solutions exist for simple migrations, trivial implementations can fail spectacularly in a real production environment, especially if you aren’t running the migration during a period when no one else is on the system. While we often think about our databases as simply data stores that are owned by our application, in many environments, that is not entirely true. In addition to our applications, our databases may be accessed by ETL processes that are loading data, reporting processes that are moving (and reshaping) data to place into another system for easier reporting, backup processes so that we don’t lose everything, auditing processes so that we meet compliance and security requirements, and even other applications. While it is helpful during development to consider that your database is owned by your application, when you are dealing with devops and production considerations, you may be better off thinking about it as more like a microservice for data storage. Phrased this way, you can make more reasonable decisions about how to alter the database without taking down other parts of the system. Additionally, you may find that approaches that were perfectly acceptable when the database was owned by your application, are completely unworkable when it is an independent and critical part of a larger system. While the shortest distance between two points may be a straight line, a straight line that causes system disruption is not a path worth walking at all. Whether you are doing automated database migrations or manual ones, there are issues you need to keep in mind when you are planning out just how a migration needs to occur. In particular, you will find that you are constantly working around things that may impact system uptime, responsiveness, and even possibly cause errors. Further, if you are an application developer who isn’t constantly messing around with the database, a lot of the issues that can occur will surprise you. Database migrations are a fact of life in modern development environments. While modern tools have made them easier, there are still things that can cause you problems. However, if you are prepared for the kind of things that might go wrong during a production migration, you can often avoid them. It’s not that database stuff is difficult – it’s just that we often don’t have to think about it too much during the development process (or we don’t want to, because we want to iterate quickly). Being careful about these things can greatly reduce the risks of taking code to production, which means you can do it more often, on smaller, less risky updates. Episode Breakdown Make sure you have a safety net. If you don’t have automated, regularly tested backup procedures and provable ability to restore, you need to handle that first. You should be testing this stuff in some sort of staging environment. While migrations might run well on a developer machine, you should also make sure that they can run from whatever tool is used in your CI pipeline (if any) or in a database scripting environment. Test your rollbacks as well, along with the functioning of the application AFTER a rollback. Make sure your database migrations are stored in source control, so that they can be correlated with the code that requires them. Should you need to roll back to an earlier version (or just look at application history for some reason), this will be critical. Keep timeouts in mind If an automated tool (such as your CI pipeline) will be running your database migrations, you need to be well aware of what kind of timeouts you’ll be dealing with. This is particularly important if your staging environment isn’t similar in size to your production environment. A good way to estimate this is to see how long something t...