How to use `Mapped[]` and `with_variant` in SQLAlquemy 2?

Question

I used to use .with_variant() function, like this:

    num_followers = Column(
        Integer().with_variant(
            postgresql.INTEGER, "postgresql"
        ).with_variant(
            mysql.INTEGER(unsigned=True), "mysql"
        ),
        unique=False,
        index=True,
        nullable=True,
        comment="Number of followers of a user",
    )

But now SQLAlquemy Version 2 has Mapped[] ( https://docs.sqlalchemy.org/en/20/orm/mapping_styles.html#declarative-mapping )

Is this correct to use?

    num_followers: Mapped[int | None] = mapped_column(
        type_=Integer()
        .with_variant(postgresql.INTEGER, "postgresql")
        .with_variant(mysql.INTEGER(unsigned=True), "mysql", "mariadb"),
        primary_key=False,
        unique=False,
        index=True,
        nullable=True,
        server_default=None,
        comment="Number of followers of a user",
    )

Because I feel that Mapped[int | None] is redundant with type_ and nullable.

---

If it is correct, can I use also with datetime, like this?

    created_at: Mapped[datetime] = mapped_column(
        type_=DateTime(timezone=True)
        .with_variant(postgresql.TIMESTAMP, "postgresql")
        .with_variant(mysql.DATETIME(timezone=True), "mysql", "mariadb"),
        primary_key=False,
        unique=False,
        index=True,
        nullable=False,
        server_default=func.now(),
        comment="Date when the user was created inside the database"
    )

Thanks

Answer 1

This is how you use with_variant:

from sqlalchemy.types import String
from sqlalchemy.dialects import mysql

string_type = String()

string_type = string_type.with_variant(
    mysql.VARCHAR(collation='foo'), 'mysql', 'mariadb'
)

Your code:

    num_followers: Mapped[int | None] = mapped_column(
        type_=Integer()
        .with_variant(postgresql.INTEGER, "postgresql")
        .with_variant(mysql.INTEGER(unsigned=True), "mysql", "mariadb"),
        primary_key=False,
        unique=False,
        index=True,
        nullable=True,
        server_default=None,
        comment="Number of followers of a user",
    )

In Mapped, you define the type of num_followers to be an integer or None. This can be used by a type checker to check the attribute's type. nullable=True is redundant as it can be inferred from the " | None" in Mapped.

In mapped_column, __type is used to define a more specific TypeEngine type or instance to be used by the database column. This is not redundant, as both serve their own purposes. Note that Mapped uses a Python type, and mapped_column uses a TypeEngine type.

primary_key=False is the default, so it is redundant. server_default=None so this is redundant as well. You can check the source code to see what default values are.

So, you can just write:

num_followers: Mapped[int | None] = mapped_column(
    Integer().with_variant(postgresql.INTEGER, "postgresql").with_variant(mysql.INTEGER(unsigned=True), "mysql", "mariadb"),
    index=True,
    comment="Number of followers of a user",
)

As for your second question, server_default should be set up like:

server_default=text('NOW()')

A text() expression will be rendered as-is, without quotes.

Please check the documentation for more details: server_default