How to specify the type of a column_property in SQLAlchemy

---

I'm posting this as it may help anyone who's having the same problem I faced last week.

I was constructing a query with SQLAlchemy and I had some real columns and one column_property. Mapping SQL expressions to a model with hybrid properties or column_property is extremely handy. Here's a link to the official documentation, However, one thing I couldn't explicitly find in the documentation is how to specify the type of a column_property.

Usually, SQLAlchemy is able to infer the type of a property. However, NullType is used as a default type for those cases where a type cannot be determined.

For example, in the following example SQLAlchemy cannot determine the type of the column best_foot, which returns the value of the biggest value between left_foot and right_foot.

from sqlalchemy import Integer, func
from sqlalchemy.orm import (
    column_property,
    declarative_base,
    mapped_column,
)


Base = declarative_base()

class Player(Base):
    __tablename__ = "players"
    id = mapped_column(Integer, primary_key=True)
    left_foot = mapped_column(Integer)
    right_foot = mapped_column(Integer)
    best_foot = column_property(
        func.greatest(left_foot, right_foot),
    )

from sqlalchemy.types import NullType
assert type(Player.best_foot.type) == NullType

More often than not, this doesn't make a big difference, and your queries will work as expected; but when attempting a UNION ALL query you might run into an error as Postgres expect all columns in a UNION ALL query to be of the same type.

You can test this out real quick in psql with this query:

SELECT 1::int UNION ALL SELECT '1'::text;

And you'll be greeted by an error message along these lines: ERROR: UNION types integer and text cannot be matched.

While this simple query is obviously something you'll never find in a real application, what I was trying to do involved a UNION ALL between two queries with a column_property and NullType kicked in.

Luckily, the solution is very simple, and since I've run into this issue I always try to be explicit when creating a column_property:

...

class Player(Base):
    __tablename__ = "players"
    id = mapped_column(Integer, primary_key=True)
    left_foot = mapped_column(Integer)
    right_foot = mapped_column(Integer)
    best_foot = column_property(
        func.greatest(left_foot, right_foot),
        func.cast(func.greatest(left_foot, right_foot), Integer),
    )

assert type(Player.best_foot.type) == Integer

This will ensure that best_foot will have the specified type and prevent a whole class of errors related to column types.

I hope this can help someone else out there!