Ask HN: What database version management tools do you use? | Hacker News Reader

Ask HN: What database version management tools do you use?

10 pointsdeytempo7 comments

A company I work with is looking for a way to track changes made to its developer, QA and production databases and have some kind of systematic approval process built into the tool like how gitlab can require someone to review the code before it can be submitted. Each alter query could be submitted then approved and then “merged” with the main DB

View on HN

Flyway. It’s super light weight. Works out of the box with Spring Boot and you barely have to change your process. Just write sql scripts in a folder with a convention for the file name and away you go.

The Java migrations are fairly handy for calling web services etc

Good for standing up end-to-end tests too.

Hmm one team at work uses python and Alembic to do migrations. It stores a hash on a table in the database so you see clearly what version the database is on. It can get pretty advanced, too: you can state that some revision is dependent on another etc.

But the more involved process you describe above was done manually at the stateside healthcare company I worked for: they had a team of DBAs that would review migrations and design and such and approve them. The chansges would go to staging. QA would test doing integration and other tests. And if passed it would roll out to production and if it failed for whatever reason it would go back to the developers. And then back to the DBAs for review and then to QA etc etc.

Other noteworthy tools include Sqitch. It is a super flexible migration tool. https://sqitch.org

Alembic looks interesting, I currently use Django where I can for version controlling database changes but it’s not ideal. I really want a terraform type tool.

A true version controlled database is tough given the nature of the data. A bad migration and you lose data. Being able to do them on a replica and then run tests is cool. If you use MySQL look into Githubs tool https://github.com/github/gh-ost

Phinx for PHP world, if your framework of choice hasn't a migration procedure for itself. Phinx is framework agnostic and allowed us being in control of DB changes for several environments and deployments, ie. several on premise production installs, etc.

DbUp, SQL files, and git.

We have the documentation in txt/md files inside the git repo. With a checklist for developers.

Then documentation for DBA to review.

Special attention on UPDATEs/DELETEs on user generated data.

Folders procedures, schema, and data.

Procedures are full, idempotent, execute everytime.

Schema is transactional, files starting with datetime.

Data is idempotent, execute everytime. Using sql server MERGE.

I mostly use Liquibase. Flyway is another popular one. Although mostly used on Java/JVM projects that's not a requirement. I have also used it separately where the migration XML and a script to run the migrations is all there was.

Reply on news.ycombinator.com