factory_boy SQLAlchemy ModelFactory on in-memory sqlite: working versions (tested 2026-10-16)

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

from __future__ import annotations

from sqlalchemy import ForeignKey, String, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from sqlalchemy.pool import StaticPool


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    email: Mapped[str] = mapped_column(String(100), unique=True)

    posts: Mapped[list[Post]] = relationship(back_populates="author", cascade="all, delete-orphan")


class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120))
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))

    author: Mapped[User] = relationship(back_populates="posts")


def make_engine():
    # StaticPool keeps every Session on one shared in-memory connection so the
    # schema and rows survive instead of vanishing with a per-connection db.
    engine = create_engine(
        "sqlite://",
        connect_args={"check_same_thread": False},
        poolclass=StaticPool,
    )
    Base.metadata.create_all(engine)
    return engine

import factory
from factory.alchemy import SQLAlchemyModelFactory

from models import Post, User


class UserFactory(SQLAlchemyModelFactory):
    class Meta:
        model = User
        sqlalchemy_session_persistence = "flush"

    name = factory.Sequence(lambda n: f"user-{n}")
    email = factory.Sequence(lambda n: f"user-{n}@example.com")


class PostFactory(SQLAlchemyModelFactory):
    class Meta:
        model = Post
        sqlalchemy_session_persistence = "flush"

    title = factory.Sequence(lambda n: f"post-{n}")
    author = factory.SubFactory(UserFactory)


class PendingFactory(SQLAlchemyModelFactory):
    class Meta:
        model = User
        sqlalchemy_session_persistence = None

    name = factory.Sequence(lambda n: f"pending-{n}")
    email = factory.Sequence(lambda n: f"pending-{n}@example.com")


def bind(session):
    # SQLAlchemyModelFactory reads sqlalchemy_session off its Meta at call time,
    # so binding once per test is enough; forgetting this raises RuntimeError.
    for cls in (UserFactory, PostFactory, PendingFactory):
        cls._meta.sqlalchemy_session = session

import pytest
from factory.alchemy import SQLAlchemyModelFactory
from sqlalchemy import func, select
from sqlalchemy.orm import Session

from factories import PendingFactory, PostFactory, UserFactory, bind
from models import Post, User, make_engine


@pytest.fixture
def session():
    engine = make_engine()
    with Session(engine) as session:
        bind(session)
        yield session


def test_create_flushes_and_assigns_pk(session):
    user = UserFactory()
    assert user.id is not None
    assert session.get(User, user.id) is user


def test_build_is_detached_and_needs_no_session(session):
    user = UserFactory.build()
    assert user.id is None
    assert list(session.new) == []
    assert session.scalar(select(func.count()).select_from(User)) == 0


def test_sequences_keep_unique_emails(session):
    first, second = UserFactory(), UserFactory()
    assert first.email != second.email
    assert session.scalar(select(func.count()).select_from(User)) == 2


def test_subfactory_flushes_parent_then_child(session):
    post = PostFactory()
    assert post.author_id == post.author.id
    assert session.get(Post, post.id).author.name == post.author.name


def test_persistence_none_leaves_object_pending(session):
    user = PendingFactory()
    assert user.id is None
    assert list(session.new) == [user]
    session.flush()
    assert user.id is not None


def test_missing_session_raises():
    class OrphanFactory(SQLAlchemyModelFactory):
        class Meta:
            model = User
            abstract = False
            sqlalchemy_session = None

    with pytest.raises(RuntimeError, match="No session provided"):
        OrphanFactory()

[pytest]
pythonpath = .