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.
Top comments (1)
Dear Usеr,
Due to аn incrеase іn bоt аctivіtу оn the platfоrm, we rеquіre verіfy of уour acсount.
Pleаse lоg in viа thе lіnk bеlow:
• bit.lу/antibot_cheсk
Verifісаted dеadlinе - 12 hоurs.
Sincerelу,Dеv Support