Entity to support multiple dialects

I have an SQLAlchemy entity written for PostgreSQL dialect and it uses server_default=func.clock_timestamp():

row_created = sa.Column('row_created_', sa.DateTime(timezone=True), server_default=func.clock_timestamp(), nullable=False)

I also need to use it with SQLite, which throws an error:

> sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unknown function: clock_timestamp()

How do I make this work with SQLAlchemy? Define the server_default only for SQLite? Conditionally add entire column only for specific dialect? Somehow make clock_timestamp() known to SQLite (I don't care what the function will do, as long as it does not fail)?

You can use: text("CURRENT_TIMESTAMP") for the server default:

class TimeStamp(Base):
    __tablename__ = "timestamp"

    id: Mapped[int] = mapped_column(
        primary_key=True,
    )

    datetime_with_tz: Mapped[datetime] = mapped_column(
        types.DateTime(timezone=True),
        server_default=text("CURRENT_TIMESTAMP"),
    )

This will create a table with the following SQL:

PostgresSQL:

CREATE TABLE timestamp (
        id SERIAL NOT NULL,
        datetime_with_tz TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY (id)
)

SQLite:

CREATE TABLE timestamp (
        id INTEGER NOT NULL,
        datetime_with_tz DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY (id)
)

If you must use clock_timestamp() on PostgreSQL, you can set server default
to:

server_default=text("CURRENT_TIMESTAMP") if use_sqlite else text("clock_timestamp()"),

postgres

CREATE TABLE timestamp (
        id SERIAL NOT NULL,
        datetime_with_tz TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
        PRIMARY KEY (id)
)

sqlite

CREATE TABLE timestamp (
        id INTEGER NOT NULL,
        datetime_with_tz DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY (id)
)

You can also use "default" for it to work on both databases.

timestamp_auto = Annotated[
    datetime.datetime,
    mapped_column(
        default=datetime.datetime.now,
    ),
]


class TimeStamp(Base):
    __tablename__ = "timestamp"

    id: Mapped[int] = mapped_column(
        primary_key=True,
    )

    datetime_with_tz: Mapped[timestamp_auto] = mapped_column(
        types.DateTime(timezone=True),
    )

I got a tip from a colleague of mine, which I ended up using and it works fine for both postgresql as well as sqlite.

server_default=func.now()

afaik it is the same as func.clock_timestamp(), but clock_timestamp() is postgresql-specific and now() is supoported by several DBs.