In this little example, I’m going to show a possible procedure to easily test your piece of code that interacts with a database.

Doing database integration tests, you will generally need few things for each test case:

• Setup test data in one or more tables (setup)
• Run the under-test functionality
• Check actual state on database is as expected (assertions)
• Clear all for the next test (teardown)

If not done carefully, most of these steps could bring more-than-wanted lines of code. Moreover, the resulting test runs are often quite slow.

The most difficult part is, in my experience, setting up and tearing down the database state to test the piece of code in question.

Let’s suppose you are using the excellent pytest framework for your tests, a couple of well designed fixtures will do the work:

import pytest
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine('DB_CONNECTION_URL')
Session = sessionmaker()

@pytest.fixture(scope='module')
def connection():
    connection = engine.connect()
    yield connection
    connection.close()

@pytest.fixture(scope='function')
def session(connection):
    transaction = connection.begin()
    session = Session(bind=connection)
    yield session
    session.close()
    transaction.rollback()

• connection fixture makes sure there is only one database connection and that it’s closed after all test are run.
• session fixture ) makes sure each test runs in a separate transaction, and cleanup is guaranteed by the rollback.

Factory Boy

Factory Boy is a tool for creating on demand pre-configured objects. It’s very handy and I personally suggest you to use it extensively.

Let’s say you have a User class with name and email. With Factory Boy you can create a class bound with your User model.

import factory

class User:
    def __init__(self, name, email):
        self.name = name
        self.email = email

class UserFactory(factory.Factory):
    name = factory.Faker('name')
    email = factory.Faker('email')

    class Meta:
        model = User

Now, just calling UserFactory.build() will generate a filled User instance. There are lots of features, such as properties overriding, sequences and so on. Take a look at the docs .

...… Umm, nice! But what has this got to do with database? …

Well, what if I say you can actually bind factories with SQLAlchemy models?

class UserModel(Base):
    __tablename__ = 'account'

    id = Column(BigInteger, primary_key=True)
    name = Column(String, nullable=False)
    email = Column(String, nullable=False)

class UserFactory(factory.alchemy.SQLAlchemyModelFactory):
    id = factory.Sequence(lambda n: '%s' % n)
    name = factory.Faker('name')
    email = factory.Faker('email')

    class Meta:
        model = UserModel

That’s all! Now creating and having a user on your database is simple as writing UserFactory.create().

Want more objects? E.g. want 6 objects?: UserFactory.create_batch(6)

If you think you didn’t get it, here is a full integration test in its splendor:

@pytest.fixture(scope='function')
def session(connection):
    transaction = connection.begin()
    session = Session(bind=connection)
    UserFactory._meta.sqlalchemy_session = session # NB: This line added
    yield session
    session.close()
    transaction.rollback()

def my_func_to_delete_user(session, user_id):
    session.query(UserModel).filter(UserModel.id == user_id).delete()

def test_case(session):
    user = UserFactory.create()
    assert session.query(UserModel).one()

    my_func_to_delete_user(session, user.id)

    result = session.query(UserModel).one_or_none()
    assert result is None

At line 5, you can see how SQLAlchemy session is bound to the factory class. Every action performed by the factory will use our session, whose lifecycle is handled by fixtures themselves.

Easy, few lines long, clean, understandable and … obviously it works!

Recap

In order to have a clean database integration testing, you should aim for:

• Running your test cases in isolated transactions, and rollback them.
• Describe your entities as models and delegate their creation to a factory system.