𝗧𝗲𝘀𝘁𝗶𝗻𝗴 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗠𝗶𝗴𝗿𝗮𝘁𝗶𝗼𝗻𝘀 𝗪𝗶𝘁𝗵 𝗦𝗵𝗮𝗱𝗼𝘄 𝗦𝗰𝗵𝗲𝗺𝗮𝘀

𝗧𝗲𝘀𝘁𝗶𝗻𝗴 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗠𝗶𝗴𝗿𝗮𝘁𝗶𝗼𝗻𝘀 𝗪𝗶𝘁𝗵 𝗦𝗵𝗮𝗱𝗼𝘄 𝗦𝗰𝗵𝗲𝗺𝗮𝘀

Database migrations risk production. Code looks right. It fails on real data. A shadow schema solves this.

A shadow schema is a separate environment. It mirrors production. It reveals issues unit tests miss.

• Slow index creation.
• Wrong column types.
• Unexpected null values.
• Data failures.

Use a three phase process.

• Clone the schema into a shadow database.
• Load a representative data slice.
• Run the migration and check results.

Do this in CI with a disposable container. For large systems, make it a pipeline stage.

A good test suite checks more than the script run. Verify these points.

• The migration applies to a production snapshot.
• Rows meet constraints after the change.
• Backfills produce correct values.
• App queries still work.
• Rollback restores the prior state.

Do not only run SQL. Encode expectations your application needs.

• Count rows before and after.
• Check for nulls in required fields.
• Match derived values to source data.
• Validate foreign keys.

Large tables lock rows. This slows your app. Use batch updates. Update in chunks of 10,000 rows. This is safer at scale.

Automate the process. Add a pipeline job.

• Start a database container.
• Apply the base schema.
• Load sample data.
• Run the migration.
• Execute assertions.
• Delete everything.

Fail the build if the migration fails. This makes safety a requirement.

Before shipping, confirm these five things.

• It runs from scratch on a shadow database.
• It passes with representative data.
• Key data invariants hold.
• Rollback is tested.
• Migration time and locks are low.

Shadow schema testing gives you a controlled place to prove your work. It catches structural and operational failures.

Make your deployments boring.

Source: https://dev.to/therizwansaleem/testing-database-migrations-safely-with-a-shadow-schema-1mmb