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?
There 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.
I 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.
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.
How 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?
Not every operation can be done "in place" if you have a high traffic table with 100MM records in it, can you run that alter without taking downtime? Where is the safety valve that stops your day one rookie from forcing a down time if you have a CD process?
CI/CD is suposed to be creating a safety net, not a way to push a massive screwup into production faster!
There isn't a magic tool that is going to make this easy, because every environment is different... RTFM for all your tools and figure out what works for you if "by hand" isn't going to cut it and build that.
If 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.
The beauty is that if something goes wrong, the rollback script gets us out of trouble.
ah, would it be that were true. Suppose you need to roll back until after the next install? I defy you to set up a reliable test plan that will handle the usual series of casual database tweaking popular with modern devs. The time spent developing and assuring the quality of the rollback will be mostly wasted energy when you're faced with rolling back a change which the next N changes are dependent on.
(more) seriously any sane scheme like yours seems to run into trouble when people want to add columns to some base table. Thusly I think "Alter Table" should be a banned operation for devs in any live system. But it's great job security for a DBA (speaking as a DBA, btw).
Typically 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.
Flywaydb 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.
I 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.
Ruby 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 which is "Rails migrations in non-Rails (and non Ruby) project".
For 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
