SqlAlchemy Annotations

One of the great joys of python is SqlAlchemy ORM, specifically Mapped Classes. If you map enough columns, though, you’ll soon find yourself wanting to reuse a mapped type across different tables or on different columns in the same table. I often reach for composition in this situation, for example to centralize logic for primary keys.

class PrimaryKeyMixin:
    id: Mapped[int] = mapped_column(
        BigInteger.with_variant(Integer, "sqlite"),
        primary_key=True,
        autoincrement=True,
    )

class MyModel(PrimaryKeyMixin, Base):
    ...

However, today that was NOT the solution I needed, and it took me way too long to find the actual syntax for my need: Annotations. In my case, what prompted my need for Annotations was the presence of many differently-named columns that all needed an appropriate numeric type. Annotations are a useful way to reuse mapped_columns in a way that encourages best practices and avoids silly mistakes. Why write this

class MoneyTable(Base):
    amount: Mapped[Decimal] = mapped_column(Numeric(10,2))
    quantity: Mapped[Decimal] = mapped_column(Numeric(10,2))
    cash: Mapped[Decimal] = mapped_column(Numeric(10,2))

when it is prone to forgetfulness (falling back to the default Decimal parameters), higher in maintenance costs (changes in my places if we want to change its configuration), and otherwise very repetitive?

Instead, use Annotations to share this type everywhere it’s needed.

MoneyDecimal = Annotation[Decimal, mapped_column(Numeric(10,2))]

class MoneyTable:
    amount: Mapped[MoneyDecimal]
    quantity: Mapped[MoneyDecimal]
    cash: Mapped[MoneyDecimal]

This is obviously a contrived example, but you can imagine reusing this across your codebase as needed.

Let me know what applications you find for Mapped Column Annotations in your python app, and make sure you bookmark the documentation!