Hey everyone, hope you're doing well! I've been involved in various projects, and I've noticed that managing database scripts tends to be a real challenge that requires a lot of attention and manual input. I'm curious to hear your thoughts on a few specific aspects: 1. How do you manage your database scripts using pipelines? 2. What do you find to be the most challenging part of the implementation? 3. How do you handle rollbacks when necessary? 4. What's the trickiest thing you've had to deal with when designing database scripts pipelines?
6 Answers
Back in the day, there was a dedicated role for managing databases, but with the rise of microservices handling their own databases, that role is kind of fading away. The complexity of database management really depends on the type of database in use; each has its own specific mechanisms and recommendations. Luckily, modern databases usually have built-in solutions for managing schema changes, although they can be costly in terms of computation.
Git combined with a migration tool has been my go-to approach for handling database changes.
We rely on Atlas for our database management, and it has been quite effective.
I've used a system that works fairly well for SQL-based fintech applications, even if I'm not entirely fond of it. Here’s how it goes: 1) We have a table to track the database version. 2) Developers write migration scripts and save them in source, ensuring filenames start with a unique version number. 3) The migration folder gets included with the build artifacts. 4) The deployment pulls the current db version and runs the necessary scripts in order, wrapped in a transaction to either fully apply changes or roll back to prevent half-applied scripts. 5) I don't focus on rollback scripts since they can be a headache to test, and if something goes wrong, we just handle it by rolling forward or restoring from backups. It's not the most elegant system, but it gets the job done consistently.
I recommend using a dedicated database migration tool like Flyway or Liquibase. These tools allow you to 'stamp' a specific version to a schema revision. Upgrades and rollbacks are handled through migration scripts, where developers write both the upgrade from version A to B and the downgrade script. Sometimes, though, if an upgrade goes wrong, it may be easier to just create a new migration to fix the error instead of rolling back to the previous version.
Yeah, Liquibase has been a lifesaver for many of my projects!
We use Flyway for some projects. I’m not directly involved anymore, but I haven't heard of any major issues popping up.
Sounds like a manual version of what Liquibase/Flyway do, but it definitely works!