Testing

Testing SQLAlchemy Models: factory-boy, Fixture Patterns, and Relationship Assertions

HelpMeTest

HelpMeTest

— 4 min read
Testing SQLAlchemy Models: factory-boy, Fixture Patterns, and Relationship Assertions

Writing tests for SQLAlchemy models means solving one problem over and over: creating realistic test data without duplicating setup code across hundreds of test files. The answer is factories. This guide shows you how to use factory-boy with SQLAlchemy to create clean, maintainable model tests—including relationship assertions, trait patterns, and fixture integration.

Why Factories Beat Manual Model Creation

The naive approach to test data is creating model instances by hand:

def test_order_total():
    user = User(id=1, email="test@example.com")
    product = Product(id=1, name="Widget", price=9.99)
    order = Order(user_id=1, product_id=1, quantity=3)
    # 10 lines of setup for one assertion
    assert order.total == 29.97

This breaks when models change—every test that creates a User needs updating. Factories centralize model construction so a field rename only requires one change.

Installing and Configuring factory-boy

pip install factory-boy sqlalchemy pytest-asyncio

For async SQLAlchemy, you need factory-boy >= 3.3.0 which supports async session injection.

Creating Your First Factory

# tests/factories.py
import factory
from factory import Faker
from models import User, Product, Order
from conftest import TestSession  # your SQLAlchemy session factory

class UserFactory(factory.alchemy.SQLAlchemyModelFactory):
    class Meta:
        model = User
        sqlalchemy_session = TestSession
        sqlalchemy_session_persistence = "commit"

    email = factory.LazyAttribute(lambda obj: f"{obj.username}@example.com")
    username = Faker("user_name")
    name = Faker("name")
    created_at = Faker("date_time_this_year")

The LazyAttribute for email depends on username—factory-boy evaluates attributes in dependency order, so username is always set before email is computed.

Factory Traits for Test Scenarios

Traits let you define named variations of a factory without creating a subclass for each scenario:

class UserFactory(factory.alchemy.SQLAlchemyModelFactory):
    class Meta:
        model = User
        sqlalchemy_session = TestSession

    email = Faker("email")
    name = Faker("name")
    is_active = True
    is_admin = False

    class Params:
        admin = factory.Trait(is_admin=True, is_active=True)
        inactive = factory.Trait(is_active=False)
        suspended = factory.Trait(is_active=False, suspended_at=factory.LazyFunction(datetime.utcnow))

Use traits in tests:

def test_admin_can_access_dashboard(db_session):
    admin = UserFactory(admin=True)  # applies the 'admin' trait
    assert admin.is_admin is True

def test_inactive_user_cannot_login(db_session):
    user = UserFactory(inactive=True)
    with pytest.raises(AuthenticationError):
        login(user.email, "password")

Testing Relationships

SQLAlchemy relationships need special handling in factories. Use factory.SubFactory for ForeignKey relationships:

class OrderFactory(factory.alchemy.SQLAlchemyModelFactory):
    class Meta:
        model = Order
        sqlalchemy_session = TestSession

    user = factory.SubFactory(UserFactory)
    quantity = factory.Faker("random_int", min=1, max=10)
    created_at = factory.LazyFunction(datetime.utcnow)


class OrderItemFactory(factory.alchemy.SQLAlchemyModelFactory):
    class Meta:
        model = OrderItem
        sqlalchemy_session = TestSession

    order = factory.SubFactory(OrderFactory)
    product = factory.SubFactory(ProductFactory)
    unit_price = factory.LazyAttribute(lambda obj: obj.product.price)

SubFactory creates the related object automatically. If you already have a User and want to attach it to an Order:

def test_order_belongs_to_user(db_session):
    user = UserFactory()
    order = OrderFactory(user=user)  # override SubFactory with existing instance

    db_session.refresh(order)
    assert order.user_id == user.id
    assert order.user.email == user.email

Asserting Relationships After Commit

After committing, ORM attributes may be expired. Always refresh or use selectinload to avoid lazy-load errors in async contexts:

from sqlalchemy import select
from sqlalchemy.orm import selectinload

async def test_order_with_items(db_session):
    order = OrderFactory()
    item1 = OrderItemFactory(order=order)
    item2 = OrderItemFactory(order=order)
    await db_session.commit()

    result = await db_session.execute(
        select(Order)
        .where(Order.id == order.id)
        .options(selectinload(Order.items))
    )
    fetched_order = result.scalar_one()

    assert len(fetched_order.items) == 2
    assert all(item.order_id == order.id for item in fetched_order.items)

Async Factory Support

For async sessions, factory-boy 3.3+ supports async session injection via a custom manager:

# tests/factories.py
import factory
from factory.alchemy import SQLAlchemyModelFactory

class AsyncSQLAlchemyFactory(SQLAlchemyModelFactory):
    """Base factory for async SQLAlchemy sessions."""

    class Meta:
        abstract = True
        sqlalchemy_session_persistence = "flush"  # flush but don't commit

    @classmethod
    def _create(cls, model_class, *args, **kwargs):
        # Override for async: caller manages commit
        return model_class(*args, **kwargs)

Then in your async conftest:

@pytest_asyncio.fixture
async def user_factory(db_session):
    created = []

    def make(**kwargs):
        defaults = {"email": f"user{len(created)}@test.com", "name": "Test User"}
        defaults.update(kwargs)
        obj = User(**defaults)
        db_session.add(obj)
        created.append(obj)
        return obj

    return make

Use it in tests:

async def test_multiple_users(db_session, user_factory):
    alice = user_factory(name="Alice")
    bob = user_factory(name="Bob", email="bob@test.com")
    await db_session.flush()

    result = await db_session.execute(select(func.count(User.id)))
    assert result.scalar() == 2

Batch Creation and create_batch

Factory-boy's create_batch creates multiple instances at once:

def test_user_list_pagination(db_session):
    UserFactory.create_batch(25)  # creates 25 users

    page1 = get_users(limit=10, offset=0)
    page2 = get_users(limit=10, offset=10)

    assert len(page1) == 10
    assert len(page2) == 10
    assert page1[0].id != page2[0].id

For async tests, flush after batch creation:

async def test_bulk_operations(db_session):
    products = [ProductFactory.build() for _ in range(50)]
    db_session.add_all(products)
    await db_session.flush()

    result = await db_session.execute(select(func.count(Product.id)))
    assert result.scalar() == 50

Note factory.build() vs factory.create(): build() creates the instance without persisting it; create() saves to the database. In transactional fixtures, build() + add() + flush() gives you more control.

Testing Unique Constraints

Unique constraint violations are common bugs. Test them explicitly:

async def test_duplicate_email_raises_integrity_error(db_session):
    UserFactory(email="dupe@example.com")
    await db_session.flush()

    duplicate = User(email="dupe@example.com", name="Duplicate")
    db_session.add(duplicate)

    with pytest.raises(IntegrityError):
        await db_session.flush()

Always catch IntegrityError from sqlalchemy.exc, not from the underlying database driver.

Factory Sequences for Unique Fields

Use factory.Sequence for fields that must be unique across instances:

class ProductFactory(SQLAlchemyModelFactory):
    class Meta:
        model = Product
        sqlalchemy_session = TestSession

    sku = factory.Sequence(lambda n: f"SKU-{n:05d}")
    name = Faker("product_name")
    price = Faker("pydecimal", left_digits=3, right_digits=2, positive=True)

Sequences reset between test sessions but not between individual tests in the same session. If your tests rely on specific SKU values, set them explicitly rather than depending on sequence order.

Integrating with HelpMeTest

Unit tests with factory-boy cover model behavior, but they miss the full stack. Add HelpMeTest monitoring to verify your SQLAlchemy-backed API endpoints work correctly in production. Write tests that create entities through the API and assert the returned data matches what was stored—catching serialization bugs and ORM configuration issues that factories won't expose.

Summary

Key patterns for SQLAlchemy model testing with factory-boy:

  • Traits for named variations (admin, inactive, suspended)
  • SubFactory for related model creation
  • LazyAttribute for computed fields that depend on other fields
  • Sequences for unique fields like SKUs and usernames
  • create_batch for bulk test data
  • flush() not commit() in transactional fixtures to keep test isolation

Factories make model tests maintainable at scale. A codebase with 500 model tests should have 10-20 factory definitions, not 500 independent setup blocks.

Read more