Write better migrations with SQL tests

Testing SQL migrations is crucial to ensure reliable and seamless database upgrades, as migrations can introduce bugs and regression issues similar to other types of code. The challenge primarily lies in accommodating users who skip versions during upgrades, requiring a robust testing strategy. Ory's approach involves fully automated tests that verify schema migrations work across multiple steps by inserting data compliant with both old and new schemas and ensuring they function as expected post-migration. This involves initializing the database, applying migrations sequentially, inserting records after each step, and conducting assertions to confirm data integrity. The process eliminates the need for old data models, as the latest migration represents the schema users upgrade to. Although this testing pattern can be adapted for private projects with known databases, it might not fit every application scenario, and full-stack integration tests with older schema databases could further enhance confidence in migration reliability. Ory Hydra's migration tests provide a detailed example, and tools like github.com/gobuffalo/pop's TestMigrator can facilitate this process.