Database integration tests with Pytest, SQLAlchemy and Factory Boy with Faker
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 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
Want more objects? E.g. want 6 objects?:
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!
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.