Your SQLAlchemy test factory fails at random past 50 rows. Here's the one-line fix.

Rachid Jeffali — Sun, 27 Sep 2026 19:04:07 +0000

You write a test that seeds a hundred rows with a factory. It fails with IntegrityError: UNIQUE constraint failed: users.id. You run it again and it passes. You run it a third time and it fails.

Nothing is wrong with your models. The factory is drawing primary keys out of a hat.

I measured it with polyfactory 3.3.0 and SQLAlchemy 2.1.1, and the fix is one line. I maintain seedgraph, a library that seeds SQLAlchemy test data, so take my word for nothing: every number below comes from a script you can rerun.

The setup

A schema every test suite has some version of:

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)
    name: Mapped[str]
    posts: Mapped[list["Post"]] = relationship(back_populates="author")


class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped[User] = relationship(back_populates="posts")
    comments: Mapped[list["Comment"]] = relationship(back_populates="post")


class Comment(Base):
    __tablename__ = "comments"
    id: Mapped[int] = mapped_column(primary_key=True)
    body: Mapped[str]
    post_id: Mapped[int] = mapped_column(ForeignKey("posts.id"))
    post: Mapped[Post] = relationship(back_populates="comments")

And the factory, with nothing configured:

from polyfactory.factories.sqlalchemy_factory import SQLAlchemyFactory


class PostFactory(SQLAlchemyFactory[Post]):
    pass


def test_feed(session):
    session.add_all(PostFactory.batch(100))
    session.commit()

Each post comes with its own author and one comment, so this test writes 100 users, 100 posts and 100 comments.

How often it fails

100 runs per size, each on a fresh SQLite database, repeated over several series:

Posts written	Runs that fail, per 100	Predicted (see below)
50	25 to 36	31
100	76 to 82	77
200	100	100

Below 50 rows you will rarely see it, which is exactly why it reaches your CI before it reaches you.

Why

By default SQLAlchemyFactory fills primary key columns (__set_primary_key__ is True), and it fills integer columns with Faker's pyint(), whose range is 0 to 9999.

So every run draws 100 user ids, 100 post ids and 100 comment ids out of 10,000 possible values. Two draws landing on the same value is the birthday problem: the chance that n draws out of 10,000 are all distinct is about exp(-n(n-1) / 20000). Across three tables, that predicts the right-hand column of the table above, and the measurements land on it.

And it's intermittent because nothing fixes the random draws between runs. The same test, the same code, a different hat.

The fix

class PostFactory(SQLAlchemyFactory[Post]):
    __set_primary_key__ = False

The factory leaves primary keys empty and the database assigns them. The setting also applies to the authors and comments the factory builds for each post. With it: 0 failures in 100 runs of 200 posts.

One side effect: an object from build() now has id = None until the session flushes it. If a test reads ids from objects it never wrote, flush first.

Bonus: shared parents

PostFactory.batch(6) gives six posts and six authors. To get three users with two posts each, build from the top:

class UserFactory(SQLAlchemyFactory[User]):
    __set_primary_key__ = False


class CommentFactory(SQLAlchemyFactory[Comment]):
    __set_primary_key__ = False


users = [
    UserFactory.build(posts=[PostFactory.build(comments=CommentFactory.batch(5)) for _ in range(2)])
    for _ in range(3)
]

Measured: 3 users, 6 posts, 30 comments, every foreign key pointing at the right row.

Where seedgraph fits

If polyfactory is in your suite, the setting above is all you need. seedgraph is for when you'd rather declare the graph than build it:

from seedgraph import seed

graph = seed(session, User, user=3, post=2, post__comment=5)

The database always assigns the keys, with no setting to remember. After the write, seedgraph walks every link and raises IncoherentGraphError if a foreign key disagrees with the row it points at. Unique columns are checked against the rows already in the database, so seeding on top of existing data doesn't collide either. With the same 50 users and 4 posts each, it failed 0 times in 100 runs.

Rerun it yourself

git clone https://github.com/jrachid/seedgraph-comparisons
cd seedgraph-comparisons/polyfactory
uv run measure.py

The script prints each number above and asserts it, so it will fail loudly the day a polyfactory release changes the behaviour.

One correction, since this post is about measuring: an earlier version of the seedgraph README claimed polyfactory leaves foreign keys pointing at nothing. That was checked only on objects in memory. Once written, SQLAlchemy syncs the keys and the links are right. The collisions above are the real problem, and they have a one-line fix.

DEV Community: Rachid Jeffali