SQLAlchemyFactory with relationship sub factory for foreign keys

[MetaHG]

Hi! I'm working with SQLAlchemy models where some fields are relationships or foreign keys, and I'm using Polyfactory’s SQLAlchemyFactory to generate these objects.

I'm wondering if it's possible to define SubFactory for relationship or foreign key fields directly, similar to the way it’s done in Factory Boy. Specifically, I'd like the SubFactory to handle both:

• Automatically creating related objects if they don’t already exist.
• Optionally referencing existing database entries for foreign key fields when available.

For example, in a ChildFactory that references a ParentFactory through a foreign key (parent_id) or a relationship (parent), could Polyfactory support setting up a SubFactory for parent (as a relationship) or parent_id (as a foreign key)?

Right now, if some foreign key parent_id is defined with its relationship parent: Mapped[Parent] = relationship(back_populates="child"), polyfactory does not seem to be able to link them together.

Thank you for your help, and any examples or guidance would be greatly appreciated!

[adhtruong]

Hi @MetaHG ,

Currently polyfactory is naive to the relationship as resolution may be difficult to fully capture behaviour. Instead this is delegated to SQLA.

Would the example here work for your use case? This doesn't have the upsert behaviour but may work if fine insert each time

[MetaHG]

Hi @adhtruong,

Sorry for the delay in my response, thank you for replying so quickly!

My question is not exactly related to persistence actually. I realize I might have been a bit too broad in my initial question as it initially stemmed from an example I encountered where I found some limitations with the library. My use case is related to database and migration testing. Let's take an example:

from __future__ import annotations

from polyfactory.decorators import post_generated
from polyfactory.factories.sqlalchemy_factory import SQLAlchemyFactory
from sqlalchemy import ForeignKey, Integer, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


# Models
class Cake(Base):
    __tablename__ = "cake"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)

    # Relations
    menus: Mapped[list[Menu]] = relationship(back_populates="cake")


class IceCream(Base):
    __tablename__ = "ice_cream"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)

    # Relations
    menus: Mapped[list[Menu]] = relationship(back_populates="ice_cream")


class Menu(Base):
    __tablename__ = "menu"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)

    # Foreign keys
    cake_id: Mapped[int] = mapped_column(ForeignKey("cake.id"))
    ice_cream_id: Mapped[int] = mapped_column(ForeignKey("ice_cream.id"))

    # Relations
    cake: Mapped[Cake] = relationship(back_populates="menu")
    ice_cream: Mapped[IceCream] = relationship(back_populates="menu")


# Factories
class CakeFactory(SQLAlchemyFactory[Cake]):
    __model__ = Cake
    __set_relationships__ = False
    __set_primary_key__ = False

    id = None


class IceCreamFactory(SQLAlchemyFactory[IceCream]):
    __model__ = IceCream
    __set_relationships__ = False
    __set_primary_key__ = False

    id = None


class MenuFactory(SQLAlchemyFactory[Menu]):
    __model__ = Menu
    __set_relationships__ = True
    __set_primary_key__ = False

    cake_id = 1
    cake = None
    ice_cream_id = None
    ice_cream = IceCreamFactory

I have a case where I know that some foreign_key_id must exists in my database at some specific revision. Consequently, when testing, I will set this foreign_key_id of my model Menu to some valid id, e.g. cake_id=1. However, the model Menu references another model IceCream with a foreign key, which is known not to be pre-populated in the database. Consequently, when testing, I want to use __set_relationships__ = True in my factory to create that object on the fly following its factory spec.
This is here that the issue arise.

__set_relationships__ = True makes all relation fields available and visible. All of them will be populated. Consequently, I need to set cake = None in my factory MenuFactory as this one does not need to be generated by the model. However, at the SQLAlchemy level, it seems that a relation object has priority other it's specified foreign_key_id. Consequently, when committing an object created by MenuFactory.build(), the database thinks that I am trying to set a null object with a null cake_id=None instead of cake_id=1 because I specified cake=None.

In a sense, I believe that the flag __set_relationships__ = True is too coarse grained for what I am trying to achieve, but I might be missing something. Let me know if you need additional clarifications and if you have any guidance for my issue!

[adhtruong]

I think this should work if set cake = Ignore() on MenuFactory. Currently it is being set to a value which gives this behaviour but Ignore omits this value entirely.

Does this work?

[MetaHG]

Hi @adhtruong ,

Your suggestion seems to resolve the issue, thank you :). It requires a bit more manual work, but it will do fine for now!

Thank you again for your help!