pytest fixtures + factory_boy with SQLAlchemy 2 sessions: working versions (tested 2026-10-15)

sqlalchemy==2.1.4
factory-boy==3.3.3
pytest==9.1.1

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(unique=True)
    is_admin: Mapped[bool] = mapped_column(default=False)

class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    total_cents: Mapped[int]
    user: Mapped[User] = relationship()

import factory
from models import Order, User

class UserFactory(factory.alchemy.SQLAlchemyModelFactory):
    class Meta:
        model = User
        sqlalchemy_session_persistence = "flush"  # get ids without committing
    email = factory.Sequence(lambda n: f"user{n}@example.com")
    is_admin = False

    class Params:
        admin = factory.Trait(is_admin=True, email=factory.Sequence(lambda n: f"admin{n}@example.com"))

class OrderFactory(factory.alchemy.SQLAlchemyModelFactory):
    class Meta:
        model = Order
        sqlalchemy_session_persistence = "flush"
    user = factory.SubFactory(UserFactory)
    total_cents = factory.Faker("pyint", min_value=100, max_value=10000)

import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from models import Base
from tests.factories import OrderFactory, UserFactory

@pytest.fixture(scope="session")
def engine():
    eng = create_engine("sqlite://")
    Base.metadata.create_all(eng)
    return eng

@pytest.fixture
def session(engine):
    # each test runs inside a transaction that is rolled back afterwards
    conn = engine.connect()
    tx = conn.begin()
    s = Session(bind=conn, join_transaction_mode="create_savepoint")
    for f in (UserFactory, OrderFactory):
        f._meta.sqlalchemy_session = s
    yield s
    s.close()
    tx.rollback()
    conn.close()

from sqlalchemy import func, select
from models import Order, User
from tests.factories import OrderFactory, UserFactory

def test_subfactory_creates_user(session):
    o = OrderFactory()
    assert o.id and o.user.id and 100 <= o.total_cents <= 10000

def test_trait_and_batch(session):
    UserFactory.create_batch(3)
    admin = UserFactory(admin=True)
    assert admin.is_admin and admin.email.startswith("admin")
    assert session.scalar(select(func.count()).select_from(User)) == 4

def test_isolation_between_tests(session):
    assert session.scalar(select(func.count()).select_from(Order)) == 0

[pytest]
pythonpath = .