In SQLAlchemy 2.1, how do you map a Money dataclass onto price_cents and price_currency columns with composite(), and how does it behave in queries?
answer
- one attribute, several columns
- dataclass fields give the column types
- comparisons expand per column
- replace the object, do not edit it
- return_none_on and the Optional annotation
basics
~20 scomposite() maps one attribute onto several columns and builds the dataclass from them. Comparisons expand to one condition per column. In SQLAlchemy 2.1, all-NULL columns give Money(None, None) unless return_none_on is set, which Mapped[Money | None] does.
solid answer
~40 s`price: Mapped[Money] = composite(mapped_column('price_cents'), mapped_column('price_currency', String(3)))` maps one attribute onto two columns and builds a `Money` dataclass from them. Each column's type and nullability come from the dataclass fields, so `amount_cents: int` gives `INTEGER NOT NULL`. In queries the attribute acts as one expression: `select(Subscription.price)` selects both columns and returns `Money` objects, and `Subscription.price == Money(999, 'EUR')` renders `price_cents = :price_cents_1 AND price_currency = :price_currency_1`. Assigning a new `Money` writes the changed columns at flush, but `sub.price.amount_cents = 1299` is not tracked unless the class uses `MutableComposite`. In SQLAlchemy 2.1 the attribute always returns an object, `Money(None, None)` when every column is NULL, unless `return_none_on` is set; `Mapped[Money | None]` sets it for you.
code
python · 41 linesimport dataclasses
from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, composite, mapped_column
@dataclasses.dataclass
class Money:
amount_cents: int
currency: str
class Base(DeclarativeBase):
pass
class Subscription(Base):
__tablename__ = "subscription"
id: Mapped[int] = mapped_column(primary_key=True)
price: Mapped[Money] = composite(
mapped_column("price_cents"), mapped_column("price_currency", String(3))
)
print(select(Subscription.id).where(Subscription.price == Money(999, "EUR")))
print(Subscription().price) # 2.1: a Money of Nones, not None
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session:
sub = Subscription(price=Money(999, "EUR"))
session.add(sub)
session.commit()
sub.price.amount_cents = 1299 # in-place: not tracked
print(sub in session.dirty)
sub.price = Money(1299, "EUR") # a new object: tracked
print(sub in session.dirty)
session.commit()go deeper
Recall that composite() turns several columns into one attribute holding a dataclass, and that you change it by assigning a new object.
Explain how column types come from the dataclass, what SQL a comparison renders, and the 2.1 return_none_on rule.
Show you would catch in-place edits that never flush and an Optional annotation that leaves NOT NULL columns behind.
Decide when a value object earns a composite rather than plain columns or a custom type, and whether the team mandates immutable value classes.
## What composite() maps `sqlalchemy.orm.composite()` groups several mapped columns into **one attribute** whose value is an object you define. The usual target is a **dataclass**, a value object with no identity of its own, such as an amount and a currency: ```python @dataclasses.dataclass class Money: amount_cents: int currency: str ``` The table keeps ordinary columns (`price_cents`, `price_currency`); only the Python attribute is composite. ## Declaring it 1. Write the value class. A dataclass needs nothing else; a plain class must also provide `__composite_values__()`, returning the column values in order. 2. Annotate the attribute with the class: `price: Mapped[Money] = composite(...)`. 3. Pass one `mapped_column()` per field, in field order, naming the columns: `composite(mapped_column("price_cents"), mapped_column("price_currency", String(3)))`. 4. Let the dataclass supply the types: `int` becomes `INTEGER NOT NULL`, `str` a string type. Fields typed `int | None` produce nullable columns. SQLAlchemy 2.1 also accepts `composite(Money, column_template="price_%s")`, which generates `price_amount_cents` and `price_currency` from the field names. It is accepted only for a dataclass; any other class raises `ArgumentError`. ## How it behaves in queries | Expression | What SQLAlchemy does | |---|---| | `select(Subscription.price)` | selects both columns and returns `Money` objects | | `Subscription.price == Money(999, "EUR")` | `price_cents = :price_cents_1 AND price_currency = :price_currency_1` | | `Subscription.price < Money(...)` | one `<` per column joined with `AND`, not a lexicographic comparison | | `Subscription(price=Money(999, "EUR"))` then flush | `INSERT INTO subscription (price_cents, price_currency, ...)` | Ordering comparisons deserve care: "less than" on a composite means every column is less, which is rarely what money arithmetic wants. A custom `Comparator` on the composite can redefine operators. ## Writes: replace, do not edit - **Assigning** a new `Money` records a change; the flush writes the columns whose values differ. - **Editing in place** (`sub.price.amount_cents = 1299`) changes the Python object but fires no attribute event, so the Session does not see it and nothing is written. - To track in-place edits, base the class on `sqlalchemy.ext.mutable.MutableComposite` and call `self.changed()` from `__setattr__`. - The simpler rule is to treat the value object as immutable, for example a frozen dataclass, and always assign a new one. ## NULL columns and the 2.1 rule SQLAlchemy 2.1 made the composite's NULL behaviour explicit with `composite.return_none_on`, a callable that receives the column values and returns `True` when the attribute should be `None`. | Declaration | All columns NULL returns | Columns created as | |---|---|---| | `Mapped[Money]`, fields `int` and `str` | `Money(amount_cents=None, currency=None)` | `NOT NULL` | | `Mapped[Money \| None]` | `None` (`return_none_on` is set for you) | still `NOT NULL` | | `Mapped[Money \| None]`, fields `int \| None` and `str \| None` | `None` | nullable | Under 2.0, a pending object whose composite columns were all `None` returned `None`; 2.1 returns the object, which matches what persistent objects and direct queries return. The `| None` on the attribute only sets `return_none_on`; it does not make the columns nullable. ## composite() versus a custom type Both turn database values into a Python object; they differ in how many columns are involved and in what the database can see: | | `composite()` | `TypeDecorator` | |---|---|---| | Columns | several, each an ordinary column | one | | Database view | each field can be filtered and indexed on its own | only the stored form, such as cents | | Conversion code | none for a dataclass; the ORM builds the object | `process_bind_param` and `process_result_value` | | Core `Table` queries | not available; it is an ORM attribute | the type travels with the column | A `Money` value with an amount and a currency is the classic composite: two facts, both worth filtering on. A single amount stored as integer cents is the classic `TypeDecorator`. ## Pitfalls - Expecting `Mapped[Money | None]` alone to allow a missing value in the table. - Editing a composite in place and finding no UPDATE. - Reading `<` and `>` on a composite as tuple ordering. - Passing `column_template` with a non-dataclass value class.
- Why does Mapped[Money | None] not make the price columns nullable?The `| None` on the attribute sets `return_none_on`, which only decides what Python gets back when every column is NULL. Column nullability comes from the dataclass fields or from each `mapped_column()`, so `amount_cents: int` still gives `NOT NULL`. Declare the fields as `int | None` and `str | None`, or pass `nullable=True` to each `mapped_column()`.
- How do you make sub.price.amount_cents = 1299 persist?Base `Money` on `sqlalchemy.ext.mutable.MutableComposite` and call `self.changed()` from `__setattr__`, so every change is reported to the parents holding the object. Without it the Session never sees the edit; the simpler rule is to make the value object immutable and assign a new one.
- composite() serialises the value object into a single column
- Editing sub.price.amount_cents in place is flushed like any column change
- Mapped[Money | None] makes the composite's columns nullable
- In SQLAlchemy 2.1 an all-NULL composite always comes back as None
- A < comparison on a composite orders the columns like a tuple
Assumes SQLAlchemy 2.1, which added composite.return_none_on and column_template; under 2.0 a pending object whose composite columns were all None returned None.