How do you handle database schema migrations in a zero-downtime deployment?

If your deployment strategy is Blue-Green, you can use a third copy to apply and validate the schema. Known (by me) as Blue-Yellow-Green. This is suitable if the database software and schema are changing.

1. The yellow copy of the environment is built and synchronised with live data
2. schema and code updated in yellow
3. checks against data and schema change occur. any failures at this point can be spotted. If required a complete rebuild of yellow can restore state without effecting release cadence on blue/green.
4. when yellow is complete, green is synchronised with live data, and the schema applied ready for deployment.
5. green is made live, and blue updated with the schema and synchronised with live data.
6. yellow can be destroyed to save costs

For simpler changes, software can be written to enable upgrades and rollbacks of schema. Schema migration is where a diff of the schema changes are in version control and can sometimes provide a rollback. With this rollback in place, the schema migration can handle the changes to the database schema. This enables a schema migration within the QA/Staging environment for Blue-Green deployments.

For canary or rolling deployments, the code must detect and handle the schema migration. Any breaking changes must be handled by the application gracefully. This involves more management from the application side. This is expected as canary and rolling deployments have a minimum of two versions of code deployed at the same time.