Agile database integration tests with Python, SQLAlchemy and Factory Boy


So you are interested in testing, aren’t you?
Not doing it yet? That’s the right time to start then!

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:

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.

It’s simple, I’ll just insert data with a couple of raw “insert” queries and cleanup tables with “deletes”.


Do it, and you’ll most likely face walls of SQL code, hardly reusable, hard to read and even harder to maintain. A very tedious and error prone method.
Moreover you can encounter performance issues, especially if more tables are involved or if there are cascading deletes.

shocked and shattered expression
You, after reading 300 lines of SQL statements

The best way to isolate your test case is an already present feature of SQL: transactions. Yes, they can help you.

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()

def connection():
    connection = engine.connect()
    yield connection

def session(connection):
    transaction = connection.begin()
    session = Session(bind=connection)
    yield session

Woah! So easy?! Yes, it is.
In a few words:

… Ok, but we haven’t entered any data yet! …

You’re right, let me introduce you to …

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): = name = email

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

    class Meta:
        model = User

Now, just calling 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 writingUserFactory.create().

Mind. Blown.

Want more objects? UserFactory.create_batch(6)

*Mind. Blown. Again.*

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

def session(connection):
    transaction = connection.begin()
    session = Session(bind=connection)
    UserFactory._meta.sqlalchemy_session = session # NB: This line added
    yield session

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

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


    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! 😃
Such a smooth integration testing!

clean sensation

Of course this example is intentionally trivial, you can find slightly more real and organized examples in this repository I created on purpose.


testing flow

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

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

If there is not a factory library or system for your language, do it yourself and open source it!

Thanks for your time!

Let me know if you liked the article and what you think about it! 👋