testing: migration testing in postgres using flyway and pgtap

jeudi 29 octobre 2015

Ok, I'm going to do everything right this time.

I set up flyway for migrations and pgtap for testing. Now I want to find a good way to perform the migration testing.

I'm also going to have lots of stored procedures. So migration includes:

(maybe) testing before migration to ensure everything is in an appropriate state. I can rerun tests from the previous migration or assume everything is fine
migrating schema and data
changing stored procedures
testing the schema
validating data
unit-testing stored procedures
integration tests, docker makes it easier

So I have migrations in the flyway. This one seems to be hard.

 V34__split_a_column_in_a_table.sql

I want to test it. I have so many questions:

Should I add tests to a flyway migration? Should these tests be run in production database after tested in dev and staging environments? Should I only validate schema here? Should I only have somewhat lightweight tests here and have a separate set of heavy tests?
Should I have all stored procedures redefined one by one? Or all of them at once? I mean I can copypaste an SP to the migration code and change it, but tracking changes would be hard and it's error prone I guess. What if I copy an entire previous SPs file, change it and see those changes using diff?

What if I have a migration, stored procedures, and tests in a group of files.

 V34_1__the_migration_itself___split_a_column_in_a_table.sql
 V34_2__stored_procedures.sql
 V34_3__validate_schema.sql
 V34_4__tests.sql

Now I can see changes between current and previous sets of SPs:

 $ diff V33_2__stored_procedures.sql V34_2__stored_procedures.sql

Same for schema validation and tests.

What am I missing? Any ideas how to improve this workflow? What can go wrong? Any better workflow?

testing