Creating a test database with SQLAlchemy

Back from my Django days, there is one feature which I really liked about the test runner: It gave me a fresh test database for free and did clean up after the test run.

Now I am often using a combination which involves py.test as a test runner and SQLAlchemy as "the database tool".

Wondering how I could get a similar behavior for my current setup, I did a first attempt and came up with a working fixture for py.test. The current state is described in this article.

The goal

  • Create a database based on the name of the configured database by prefixing it with test_.
  • Create the database only if needed.
  • Clean up at the end of the test run.

Approach

I came up with the following list of fixtures:

  • test_db_url

    Used to plug in the database name from the test suite. I could not really decide if there is a specific mechanism how the database name should be figured out, so I ended up moving this duty out to the application's test suite.

  • test_db

    A session scoped fixture which returns an object representing the database. The test database will be created if this fixture is requested.

    Based on the support to register a finalizer on py.test's request fixture, the database will be destroyed at the end of it's usage.

  • test_engine

    Initially I did not have this fixture, but then came to add it due to the convenience it provides. It is session scoped and will create an engine which is connected to the test database.

In my application's test suite I can then depend on the fixture test_db or test_engine and perform an application specific bootstrapping to wire everything with the test database.

Integration into the application's test suite

The application's test suite builds on top of these basic fixtures to populate the test database as needed. So far I tend to define a fixture db in my application's test suite which requires test_db and does the application specific wiring.

Conclusion

My initial implementation is gaining a lot from py.test's fixture mechanism. I got the bigger part nearly for free:

  • Clean up automatically

    Thanks to py.test's request fixture, I just had to register a function as a finalizer.

  • Create the database only if needed

    py.test creates a fixture only if it is required. This way the test database will only be created if at least one test is requiring it or one of the fixtures of the test is requiring it.

Comments

comments powered by Disqus