[place1]

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?

[zer00eyz]

How do things run in production vs your CI/CD process?

Is your CI environment a FULL CLONE of your production environment?

Lets take a look at the mysql manual for a second:

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html#alt...

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.

[acutesoftware]

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.

[no-s]

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).

[flukus]

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.