4 ms·
Ask HN: How do you do database version control, deployments, etc?
I'm working on a side project and I want to deploy the application to production as soon as possible, but I'm dreading having to deal with deploying schema changes, testing database deployments and everything else. In the past I've rolled my own primitive deployment scripts and database versioning scheme, but I'm wondering if there is a more robust solution out there. Is there a solid CD or CI solution out there for databases?
- aregsarkissian 9y agoThere are various database agnostic schema migration tools that can help. There are rails, django and laravel migrations or alembic if you use python/sqlalchemy. There also pure sql based migration systems such as flyway. There is a great pluralsight course on proper way to do migrations using flyway. Most of it applies to migrations with any migration tool. You might be able to check it out with the free trial. But basically you want to do all your add columns and add tables before deploying a new version of code. Just to make sure the old version still runs. Then after deploying the new version of code. at some point you can delete the unused columns and tables. I also like to have one database object per migration file. That way the cost of each migration is smaller and if the migration fails you don't have to fix a partial migration since each migration operation is atomic.
- w4tson 9y agoI really like flyway. It’s lightweight and the best thing I can say about it is that I rarely have to bother with it. It just works. I too find it useful to keep the migrations small and succinct. For Oracle do migrations where the DDL cannot be rolledback I choose to separate these from straight data manipulation. A downside to the project that I see is that although it’s quite mature the author is very very slow to engage with open source modifications. I’d still make it my first choice because of its simplicity.
- daleholborow 9y agoCheck out dbup (in c# land) for a strategy and tools , and check the migrations into your repo along with your side.
- zer00eyz 9y agoI have never found a tool better than "by hand" Simply put the DB isn't code, and a DB changes are in another category when looking at the risks involved. Deploying to your own MYSQL cluster vs AWS vs Postgres vs oracle are all very different mindsets, tools and requirements. Can you get away with a tool in the short term, probably - and it would be one specific to the language you work in. Pick something your side project is written in, or something your interested in learning if your willing to go higher risk.
- place1 9y agoHow does this scale once you have multiple developers/teams and an automated CI/CD process in place? Could you elaborate on how to manage this manual step (potentially for multiple environments) when an automated deployment depends on them being done?
- flukus 9y agoTypically you either use a tool like flywaydb so that each individual step is only run once or you make your scripts re-runnable. IME the flywaydb approach is much better, it works well for local development and naturally progresses through to production releases with as little friction as possible.
- acutesoftware 9y agoIf the database changes are substantial enough to break the applications, I would have a new schema setup to have enough time to test carefully and then when ready, point the application to the new schema. For small changes, we use scripts 'INSTALL_[SCHEMA].sql' and 'ROLLBACK_[SCHEMA].sql' which do everything needed including backups / restores of any tables needed. This is tested in the DEV/QA environments as needed then run in Prod right before deploying the application changes. We haven't had high availability requirements and can get away with a scheduled 3 minute outage but this works for us. The beauty is that if something goes wrong, the rollback script gets us out of trouble.
- no-s 9y ago
- twunde 9y agoFor a side-project you're probably going to want to use the migrations logic of whatever ORM/language you're using, but alternatively there are db devops tools like dbmaestro or datical, although those are typically targeted at enterprise companies
- bryan11 9y agoI built a system for MySQL a while ago so database patches (data fix, schema change, etc) are applied with code deploys. All database patches are written to be repeatable, which means they contain logic that checks if they've been run already. They also have release and sequence ID so they're tracked and may be run out of sequence if needed. Once everyone got accustomed to the system, it's worked well for several years now.
- nathanken 9y agoYou could try Liquibase.
- shoo 9y agoFlywaydb does schema versioning and migrations and is pretty simple. Each migration is a single SQL script. It doesn't support rollbacks as the authors claim from experience that rollback scripts tend to be needed rarely, and if needed they tend not to work. Flywaydb doesn't cover db testing.
- imhoguy 9y agoRuby on Rails migrations do best for me for over decade. If you don't work with RoR project then you can also try https://github.com/thuss/standalone-migrations https://github.com/thuss/standalone-migrations which is "Rails migrations in non-Rails (and non Ruby) project".
- slipwalker 9y agoFlyway, Liquibase or Nextep ( https://github.com/christophefondacci/nextep-designer/wiki https://github.com/christophefondacci/nextep-designer/wiki ) depending on which client, which project...