RFC: Reference implementation for pytest integration tests with alembic

[staticdev]

Following up on this discussion #871.

My idea would be to provide a simple example (on alembic documentation? example repo? example folder on code?) of tests deleting an object from a table with maybe one happy and not-happy path (ie. object does not exist), in a way there is one pytest.fixture with scope=session that creates a database and apply migrations, so that the same database is used on all tests. Another approach would be to generate from current schema from sqlalchemy, but then the actual migrations are not being covered by tests.

I could not find after some search a structured python project or cookiecutter that has database integration test with pytest using alembic. I got puzzled by that since should be a fairly common case (or maybe not on public github projects). I also do not found mentions in alembic or pytest documentation. Am I missing something?

[zzzeek]

what's the part of that project that you are having difficulty with ? is it how to run alembic programmatically? (see here). or is it how to have data created in tests that is rolled back inside of a transaction? (see here)

those are the two integration points for the case you're describing. we dont want to get into having "turnkey" examples because then people just run them as is without understanding them and expect us to support their specific issues.

[zzzeek]

the way im answering this is because you phrased your question as an "Idea - Should there be a complete example for this?" and I'm mostly leaning towards "not much, just some starter points at most".

if OTOH you are just looking for advice on this, then that's a question which we can provide a lot more opinions towards here.

[staticdev]

@zzzeek you bring great points, the high-level goal is provide QA reference for projects using alembic. From your comments I wonder, maybe applying migrations on integration test setups is not be best approach to have the migrations tested (because of specific database migrations or data values migrations, etc). I would like to know more based on your experience with real world projects using alembic, how would you approach testing the migrations? Maybe creating separate tests for each migration somehow? For using integration tests of business use-cases with SQLite, for example in a Postgres project, would you just setup and teardown the DB with current schema without applying alembic migrations?

[zzzeek]

the real world project ive been involved with that does this is Openstack, and they have historyicaly had really in-depth migration testing approach set up. looking at it now it seems to have been scaled back a lot, but the general idae remains:

1. all apps in production are MySQL/MariaDB apps, but the test suites all run on sqlite
2. for their test suite setup/teardown, openstack apps have different approaches: a simple create_all() for all sqlite and mysql, or run all the migrations for sqlite and mysql, or create_all() for sqlite and run the migrations for mysql. there is also, not sure if theyve changed this, at least one project that generates the SQLite database just once and then uses some special sqlite "copy" command to generate the DB per test
3. no openstack project is doing the "transaction per test that's rolled back" thing. there's way too much hackery and old history in openstack code for this to work reliably (which is a big issue with the "transaction rollback on test" thing - it's very brittle and any games being played with engines etc. tend to be disastrous)
4. all openstack apps have a "migration test" suite that's based on a concept called "snakewalk" - that is,it runs every migration individually and they have hooks that let them test the state of the database for individual migrations. im looking at some source now and it looks like they've massively scaled that back though - back when they used sqlalchemy-migrate, every new migration file had to have a unit test. seems to not be there now that they've moved to alembic. at some point the notion of "downgrades" was just removed completely. database "downgrade" operations are not really very realistic for production systems.
5. complicating openstack quite a bit is they support "online schema migrations" which means they have an "expand" / "contract" system set up using Alembic; two classes of migrations that both "expand" the db (add new columns and tables) and "contract" (remove old columns and tables, once code that refers to those structures has been fully upgraded)

openstack has refactored their database stuff hundreds of times over the years so it's hard to pick out one consistent pattern from their suite as it's a set of several dozen independent applications.

[staticdev]

@zzzeek I am almost convinced that my original idea of having one database setup, apply all migrations, run all tests are not really useful and reliable. Probably better general approach:

1. create a SQLite database for each integration test and setup with current schema (without migrations) using create_all(). This way should be fast because it is in-memory and avoid transaction problems.
2. test each migration upgrade/rollback individually, in specific tests for migrations, similar to what you mentioned in (4). Maybe this point can be further explored in documentation/cookbook if you find value on it.
3. only if some specific database feature not implemented by SQLite occur, worry about that and provide targeted tests for it.

Also, I would like to dig a bit into the code of those Openstack components to see in practice what you described. Could you share the repo(s) you know are using these ideas with alembic?

[zzzeek]

openstack apps are based on a helper library called oslo.db that I did not write, but in the past 7 years it's been my job to maintain it so a lot of it is "mine" for some value of "mine" these days. the basis for the "snakewalk" predates my involvement but it is based on the WalkVersionsMixin class, which used to be based on sqlalchemy-migrate, now works for alembic, and im not sure if it works for SM anymore or not.

some actual migration tests are for example in nova, where they seem to have made their own "walk" system these days. if we want to see projects using WalkVersionsMixin we can use openstack code search, where you can at least click around and see the kinds of things projects are doing. it's a lot of heavyweight code with lots of legacy decisions in flux all over the place, and not very good for simple examples of things.