Creating databases in SQLAlchemy tests with PostgreSQL

Question

I am building a Pyramid web application which is built on the top of SQLAlchemy and solely relies PostgreSQL as its database backend.

What would be a way to have the unit tests structure so that

• Database is built once per test run - not on every test setUp() as this is too slow for a complex application

• Database tables are (re)created as they would be created in production (e.g. run migrations from Alembic). Any unclean databases are destroyed at the start of the test run.

• It is possible to choose a custom test runner á la py.test if a specific features outside the standard library unittest framework makes it easier to write test cases.

Answer 1

Nose test runner supports setup_package() and teardown_package() methods. Here's an excerpt from the docs:

Test Packages

nose allows tests to be grouped into test packages. This allows package-level setup; for instance, if you need to create a test database or other data fixture for your tests, you may create it in package setup and remove it in package teardown once per test run, rather than having to create and tear it down once per test module or test case.

To create package-level setup and teardown methods, define setup and/or teardown functions in the init.py of a test package. Setup methods may be named setup, setup_package, setUp, or setUpPackage; teardown may be named teardown, teardown_package, tearDown or tearDownPackage. Execution of tests in a test package begins as soon as the first test module is loaded from the test package.

In my application I have setup_package() which looks roughly like the following:

def _create_database():

    template_engine = sa.create_engine("postgres://postgres@/postgres", echo=False)

    conn = template_engine.connect()
    conn = conn.execution_options(autocommit=False)
    conn.execute("ROLLBACK")
    try:
        conn.execute("DROP DATABASE %s" % DB_NAME)
    except sa.exc.ProgrammingError as e:
        # Could not drop the database, probably does not exist
        conn.execute("ROLLBACK")
    except sa.exc.OperationalError as e:
        # Could not drop database because it's being accessed by other users (psql prompt open?)
        conn.execute("ROLLBACK")

    conn.execute("CREATE DATABASE %s" % DB_NAME)
    conn.close()

    template_engine.dispose()


def setup_package():
    _create_database()

    engine = sa.create_engine("postgres://postgres@/%s" % DB_NAME, echo=False)

    session = sa.orm.scoped_session(sa.orm.sessionmaker())
    session.configure(bind=engine)
    Base.metadata.bind = engine
    Base.metadata.create_all()


def teardown_package():
    # no need to do anything as the old database is dropped at the start of every run

In addition, all test case classes are subclassed from a base class, which, importantly, defines a common tearDown method:

class BaseTest(unittest.TestCase):

    def setUp(self):
        # This makes things nicer if the previous test fails
        # - without this all subsequent tests fail
        self.tearDown()

        self.config = testing.setUp()

    def tearDown(self):
        testing.tearDown()
        session.expunge_all()
        session.rollback()

Subclasses often override base setUp, but there usually no need to override tearDown - by rolling back the transaction it ensures that the next test will start on a completely clean database.