Automated Testing of Database Schema Migrations

Abstract: Modern applications use databases, and the majority of them are relational databases, which use schemas to impose data integrity constraints. As applications change, so do their databases. Database schemas are changed using migrations. Certain conditions can result in migrations failing in production environments, leading to a broken database state and testing can be problematic without accessing production data which can be sensitive.Two migration validation methods were proposed and implemented to automatically reject invalid migrations that are not compatible with the database state. The methods were based on, and compared to, a default method that used Liquibase to structure and perform migrations. The assertion method used knowledge of what a valid state would look like to generate pre-conditions from assertions to verify that the database’s state matched expectations and that the migrations were compatible with a database’s state prior to migration. The schema method, used a copy of the production database’s schema to perform migrations on an empty database in order to test the compatibility of the old and new schemas. 108 test cases consisting of a migration and a database state were used to test all methods. Both valid and invalid test cases that were not compatible with the database’s state were used. The distribution of aborted, failed, and successful migrations were analyzed along with the automation, traceability, reliability, database interoperability, preservability, and scalability for each method.Both the assertion method and the schema method could be used to stop most of the invalid migrations without access to the production data. A combination of the assertion method and the schema method would result in only 2/108 migrations failing and could reduce the failure rate even further through using a schema to reduce complexity for uniqueness constraints and to improve support for handling data type conversions.