Automated testing for migrations

[Michael Cooper]

Tests are great. Tests have saved my bacon many times. But we don't test
everything.

Does anyone know of any tools that test migrations? In particular, I'm
thinking about South, but the new Django Migrations would also be
interesting. Schema migrations I can trust pretty well since a computer
wrote them, but it would be great to have tests for data migrations that
verify the data changes in the expected way.

Is this something anyone does? Want to do? Tried and it failed?

[Selena Deckelmann]

oops replied to coop only-- resending to list:


I'm not aware of special tools for testing that are useful among the suite
of tools that I use (SQLAlchemy, Alembic).

However, what we've done for Socorro is add a "alembic downgrade -1" and
"alembic upgrade heads" to every integration test run. This is part of the
script that travis-ci runs. This has caught just about every migration
problem possible to catch within the constraints of our existing data
fixtures. It has also enabled everyone on the team to write migrations and
feel confident that it was tested before deploy.

The main barrier to having a well-oiled migrations system in my experience
is that very few people write the migrations, in addition to them being
unreviewed and untested. This results in the bulk of the responsibility to
make these falling on maybe one person who has a hard time getting code
review or participation from the rest of the team (no judgement intended!
-- its just how I've experienced this problem).

I've found that the best way to get migrations working is to:

* cause the acceptance test from automated testing to fail if there's
something wrong
* use a migration system that works well with existing code base and team
practices

For socorro, this meant: migrations in python, NOT SHELL; using a
well-supported and up-to-date package (alembic); using an ORM for the
modeling -- not necessarily for writing queries in the app (SQLAlchemy).

I gave a presentation about our use of alembic at PyCon this year that
talks about most of what I just said, but with slides and for 45 minutes
instead of a couple minutes (lol):
https://www.youtube.com/watch?v=_ZdqwCr4c7Q

> _______________________________________________
> dev-webdev mailing list
> dev-w...@lists.mozilla.org
> https://lists.mozilla.org/listinfo/dev-webdev
>

[Peter Bengtsson]

When you don't use the fancy optimizations from django-nose, aren't all the
myapp/migrations/*.py's forward() function executed from the
myapp/migrations/0001_initial.py
Thus running the whole suite basically executes all of those migrations.

Am I missing something obvious? Is that now how it works?

On Thu, Feb 5, 2015 at 9:34 AM, Michael Cooper <mco...@mozilla.com> wrote:

> Tests are great. Tests have saved my bacon many times. But we don't test
> everything.
>
> Does anyone know of any tools that test migrations? In particular, I'm
> thinking about South, but the new Django Migrations would also be
> interesting. Schema migrations I can trust pretty well since a computer
> wrote them, but it would be great to have tests for data migrations that
> verify the data changes in the expected way.
>
> Is this something anyone does? Want to do? Tried and it failed?
> _______________________________________________
> dev-webdev mailing list
> dev-w...@lists.mozilla.org
> https://lists.mozilla.org/listinfo/dev-webdev
>

--
Peter Bengtsson
Mozilla Web Engineering

[Selena Deckelmann]

That's a cool feature!

One issue with tests that only run migrations forward is that they don't
test rolling a migration back. If you don't test rolling back migrations,
you aren't really testing them.

-selena


On Thu, Feb 5, 2015 at 10:32 AM, Peter Bengtsson <pbeng...@mozilla.com>
wrote:

[Michael Cooper]

Selena brings up a good point, this doesn't test the reverse migration. It
also only tests that the migrations don't break anything. For example, we
just had a data migration that in the reverse case did not error, but
created bad data (and the forward case was badly tested too). This is more
about data migrations that touch real data, which simply running the
migrations forward on a test DB doesn't test well.

On Thu, Feb 5, 2015 at 12:08 PM, Selena Deckelmann <sel...@mozilla.com>
wrote:

[Selena Deckelmann]

We have a tool called 'fakedata' that Rob Helmer wrote to preload sample
data.
https://github.com/mozilla/socorro/blob/master/socorro/external/postgresql/fakedata.py

I've added examples of weird things we need to test for in there and other
fixtures from time to time as needed.

I wish there was a better tool for this! There are some random data
generators, but I think what stops a tool from this being made is that
people experience the problem a couple times a year but otherwise can get
along well enough without it.

The main bits you need to cover are:

* test upgrade AND downgrade (and make it part of integration tests)
* provide test data similar to production (not hard, but tedious)
* have a plan for downgrading (and enough time to recover) (make a
checklist)
* design your system to tolerate temporary database failures so that you're
not totally hosed by a migration issue (i can provide some advice here if
you want it by looking at the schema/system and thinking through common
failure modes)

None of those things are particularly easy to do with existing tooling. The
first three aren't terribly hard, and can somewhat easily be implemented
with three short checklists for reviewing commits and for making production
changes (that eventually are automated!). The last one is just hard. :)

-selena

[will kahn-greene]

Input is small and data migrations have been straight-forward so far, so
I haven't spent the time to figure out how to do integration testing on
migrations with representative data.

I do have print statements in all my data migrations that print out
results allowing me to verify by eye that it did the right thing. When I
run them on my local db which has representative data, I can see what
they did. Plus when I deploy them, I see what they did in the logs.
That's worked for me so far. Example:

https://github.com/mozilla/fjord/blob/973d501e09375ba634207bc4aedf5c37ae112304/fjord/feedback/south_migrations/0043_fix_android_browser.py#L18

Maybe it makes sense to have the migration itself verify it did the
right thing? i.e. Do some stuff, then run some queries and assert things
to make sure the end results are correct?