SQLAlchemy2.0 + mapped_as_dataclass - how do I init only foreign key or link property?

[arogozhnikov]

Short version:

@reg.mapped_as_dataclass
class Child:
    __tablename__ = "child"
    id: Mapped[int] = mapped_column(primary_key=True)
    parent_id: Mapped[Optional[int]] = mapped_column(ForeignKey("parent.id"), default=None)
    parent: Mapped[Optional[Parent]] = relationship(default=None)

Adding object Child(id=7, parent=<parent object>) does work.

Adding object Child(id=7, parent_id=14) does not work - parent_id = None is passed to database instead.

Assigning properties drives to similar result. It looks like foreign key property is ignored by alembic in insert/update operations.

long version to reproduce

from sqlalchemy import select, create_engine

engine = create_engine('sqlite://')
connection = engine.raw_connection()
cursor = connection.cursor()

# creating tables and inserting a single parent

cursor.execute("create table parent (id integer)")
cursor.execute("create table child (id integer, parent_id integet)")

cursor.execute("insert into parent (id) values (101)")

# creating registry
from typing import List, Optional

from sqlalchemy import ForeignKey, Engine
from sqlalchemy.orm import Mapped, Session
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import registry
from sqlalchemy.orm import relationship

reg = registry()

@reg.mapped_as_dataclass
class Parent:
    __tablename__ = "parent"
    id: Mapped[int] = mapped_column(primary_key=True)

@reg.mapped_as_dataclass
class Child:
    __tablename__ = "child"
    id: Mapped[int] = mapped_column(primary_key=True)
    parent_id: Mapped[Optional[int]] = mapped_column(ForeignKey("parent.id"), default=None)
    parent: Mapped[Optional[Parent]] = relationship(default=None)

def get_parent_ids(sess):
    print([x.parent_id for x in sess.execute(select(Child )).scalars().all()])

with Session(engine) as sess:
    parents = sess.execute(select(Parent)).scalars().all()
    sess.add( Child(id=5, parent=parents[0]) )
    sess.add( Child(id=6, parent_id=parents[0].id) )
    sess.commit()
    
    get_parent_ids(sess)

# outputs: [101, None]

[zzzeek]

this is because you have default of None for Parent. When you do this:

c1 = Child(parent_id=1)

you now have an active c1.parent of None, you have essentially done this:

c1 = Child()
c1.parent_id = 1
c1.parent = None

The .parent relationship takes precedence.

Solution is to not set c1.parent to None, leave it unassigned. for dataclasses you'd need to remove it from your __init__() method if you want to use foreign key values, or more typically, you probably don't want to use the dataclass constructor. Set your class up instead like this:

@reg.mapped_as_dataclass(init=False)
class Child:
    __tablename__ = "child"
    id: Mapped[int] = mapped_column(primary_key=True)
    parent_id: Mapped[Optional[int]] = mapped_column(ForeignKey("parent.id"), default=None)
    parent: Mapped[Optional[Parent]] = relationship(default=None)

that will use the default SQLAlchemy declarative constructor where all fields are optional.

This issue absolutely came up in the past month and there was discussion about other ways we might make this nicer and I simply cannot find it now.

[arogozhnikov]

But yes, from user perpective, I'd like to have something like relationship(default=DONT_SET)

[zzzeek]

I wonder why dataclasses itself has no way of providing this?

[zzzeek]

here's kind of a big thread about it: https://stackoverflow.com/questions/55099243/python3-dataclass-with-kwargsasterisk

[thecodingwizard]

relationship(default=DONT_SET) would be helpful for me as well. Are there any plans to add support for something like this? Thanks!

[CaselIT]

it's linked here as something to document #9410

[mchataigner]

Hello, we faced this exact same problem and found a hacky workaround:

Non working example

class UserWithIssues(Base):
    __tablename__ = "other_users"

    id: Mapped[UUID] = mapped_column()
    company_id: Mapped[UUID] = mapped_column()
    company: Mapped[Company] = relationship(default=None)

In this example, it works if we create a UserWithIssues by passing a Company object but not with a company_id:

correct_user = UserWithIssues(company = my_company) # works
invalid_user = UserWithIssues(company_id = UUID(...)) # company_id is saved to null in the DB

Workaround

from sqlalchemy.orm.base import NEVER_SET


class Base(MappedAsDataclass, DeclarativeBase):
    __allow_unmapped__ = True

    def __post_init__(self, **kwargs: Any) -> None:
        for _name, infos in sa.inspect(self.__class__).relationships.items():
            if getattr(self, infos.key) == NEVER_SET:
                setattr(self, infos.key, None) # we have to trick the instance here otherwise it will refuse to delete the attribute
                delattr(self, infos.key) # removing the attribute in this case will avoid trashing the foreign_key attribute

and then we set all relationship to NEVER_SET in all models, like so:

class User(Base):
    __tablename__ = "users"

    id: Mapped[UUID] = mapped_column()
    company_id: Mapped[UUID] = mapped_column()
    company: Mapped[Company] = relationship(default=NEVER_SET) # setting default to None does not work as it shadows the corresponding foreign_key

As a result, it’s possible to create a user by either passing a company_id or a company object.

user1 = User(company = existing_company) # still works
user2 = User(company_id = UUID(...)) # also works now

I was wondering if you have a better workaround or a proper fix in mind in order to avoid this "hack". Thank you for your time.

I submitted a small PR #11639 describing the issue.

Other tentative

We also tried to play with InitVar but it’s not possible to have an InitVar and a property (regular python, or hybrid one) with the same name (at least in Python 3.12, not tested with previous Python versions).

[JacobHayes]

I also found a custom __setattr__ works well too:

from sqlalchemy.orm import NO_VALUE

class Base(MappedAsDataclass, DeclarativeBase):
    if not TYPE_CHECKING:  # Avoid type checkers allowing any attribute to be set.

        def __setattr__(self, name: str, value: Any, /):
            if value is not NO_VALUE:
                super().__setattr__(name, value)

[timhughes]

I just ran in to this because I followed the docs at https://docs.sqlalchemy.org/en/20/orm/dataclasses.html#relationship-configuration which says to use default=False

I ended up using the init=False way to get it to work.

import os
from contextlib import contextmanager
from typing import Annotated, Iterator

from sqlalchemy import ForeignKey, create_engine
from sqlalchemy.orm import (DeclarativeBase, Mapped, MappedAsDataclass,
                            Session, mapped_column, relationship, sessionmaker)

# Get the database URL from the environment variable
DATABASE_URL = os.environ.get("DATABASE_URL")

# If the environment variable is not set, use a default value
if not DATABASE_URL:
    DATABASE_URL = "sqlite:///:memory:"

# Create a SQLAlchemy engine
engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False},  # Only for SQLite
)

# Create a SessionLocal class
TestingSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


# Create a dataclasses declarative base class 
class Base(MappedAsDataclass, DeclarativeBase):
    pass

@contextmanager
def session() -> Iterator[Session]:
    Base.metadata.create_all(bind=engine)
    db = TestingSessionLocal()
    try:
        yield db
    finally:
        db.close()
        Base.metadata.drop_all(bind=engine)

int_pk = Annotated[int, mapped_column(primary_key=True)]

class Parent(Base):
    __tablename__ = "parent"
    id: Mapped[int_pk] = mapped_column(init=False)
    children: Mapped[list["Child"]] = relationship(
        default_factory=list, back_populates="parent"
    )


class Child(Base):
    __tablename__ = "child"
    id: Mapped[int_pk] = mapped_column(init=False)
    parent_id: Mapped[int] = mapped_column(ForeignKey("parent.id"))
    parent: Mapped["Parent"] = relationship(init=False) 


def family(db: Session) -> None:
    db_parent = Parent()
    db.add(db_parent)
    db.commit()
    db.refresh(db_parent)

    db_child = Child(parent_id=db_parent.id)
    db.add(db_child)
    db.commit()
    db.refresh(db_child)

    print(db_parent)
    print(db_child)


if __name__ == "__main__":
    with session() as db:
        family(db)

[notypecheck]

Ran into this when following https://docs.sqlalchemy.org/en/20/orm/dataclasses.html#relationship-configuration too.
If we have a

class Child(MappedAsDataclass, Base, kw_only=True):
    __tablename__ = "child"
    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    parent_id: Mapped[int] = mapped_column(ForeignKey("parent.id"), default=None)
    parent: Mapped["Parent"] = relationship(default=None)

It's not always desirable to fetch Parent instance from a db instead of using it's id. Without dataclasses it's not a problem. With dataclasses however child.parent is assigned a None value, making parent_id None too.
Some available ways to solve this are:

• Only using parent - just not always desirable
• Adding init=False to parent or/and parent_id - this removes possibility to use them in init, leading to worse API for creating that specific model instance.

Perhaps as people mentioned we could have a NOT_SET constant (for example something like strawberry.UNSET: https://strawberry.rocks/docs/types/resolvers#optional-arguments)

[CaselIT]

it's tracked by #12168 and it's planned for 2.1

[zzzeek]

Here's a generalized recipe for ignoring m2o relationship as None for the 2.0 series until #12168 is available: #12168 (comment)