SQLModel integration / docs

[fraser-langton]

Would be great to add some docs on setting up for SQLModel - shouldn't be too comlex (hypotehically) since SQLModel is sqlalchemy under the hood

[AbdealiLoKo]

Thats a great idea !
I haven't used sqlmodel myself, would you be interested in contributing some instructions if you got it working ?

[icemac]

Here is what I did with the help of Copliot: It seems to work well enough for me to use it in production for real battle testing. (I am copying only the needed changes from the real application, just obfuscating the imports):

myproject/database.py – integration with database models

import sqlalchemy_history
import sqlalchemy_history.builder
import sqlalchemy_history.model_builder
import sqlalchemy_history.transaction
import sqlalchemy_history.utils

sqlalchemy_history.make_versioned(user_cls=None)

# sqlalchemy_history 2.1.4 is incompatible with SQLModel / Pydantic v2 out of
# the box.  Two issues need to be worked around:
#
# 1. get_declarative_base() returns sqlmodel.SQLModel for our versioned classes,
#    but Pydantic's ModelMetaclass rejects non-annotated SA columns (e.g. the
#    Transaction.id BigInteger column) with PydanticUserError when
#    configure_mappers() fires.  Fix: swap the base to a plain SA
#    DeclarativeBase for sqlalchemy_history's internal models only; our own
#    *_version tables are still added to sqlmodel.SQLModel.metadata.
#
# 2. sqlalchemy_history reads Base.registry._class_registry to look up models by
#    name, but Pydantic's metaclass raises AttributeError for 'registry'. Fix:
#    expose it explicitly via type.__setattr__ to bypass Pydantic.
#
# See https://github.com/corridor/sqlalchemy-history/issues/159

class _HistoryInternalBase(sqlalchemy.orm.DeclarativeBase):
    """Plain SA base used only for sqlalchemy_history's Transaction model.

    Keeps Pydantic out of the way for sqlalchemy_history's internal classes
    while leaving all application models on sqlmodel.SQLModel.
    """


_original_get_declarative_base = (
    sqlalchemy_history.builder.get_declarative_base
)


def _patched_get_declarative_base(cls: type) -> type:
    """Return a plain SA base instead of SQLModel for history internals."""
    result = _original_get_declarative_base(cls)
    if result is sqlmodel.SQLModel:
        return _HistoryInternalBase
    return result  # pragma: no cover


sqlalchemy_history.builder.get_declarative_base = _patched_get_declarative_base
# model_builder.py imports get_declarative_base directly from sqlalchemy_utils
# (not via sqlalchemy_history.builder), so it must be patched separately.
sqlalchemy_history.model_builder.get_declarative_base = (
    _patched_get_declarative_base
)

# Expose SQLModel._sa_registry as .registry so sqlalchemy_history can look up
# models by name without hitting Pydantic's AttributeError guard.
type.__setattr__(sqlmodel.SQLModel, "registry", sqlmodel.SQLModel._sa_registry)

# sqlalchemy_history defines TransactionBase.issued_at with a timezone-aware
# default (datetime.now(tz=utc)).  Patch the column to use a naive datetime
# to match all other timestamps in the database.
sqlalchemy_history.transaction.TransactionBase.issued_at = sqlalchemy.Column(
    sqlalchemy.DateTime(timezone=False), default=datetime.datetime.now
)

... # definition of the SQLModel models using `__versioned__ = {}` to enable history

# sqlalchemy-history registers version/transaction tables lazily via SA mapper
# events.  configure_mappers() must be called explicitly here so that *_version
# tables and the transaction table are added to their respective MetaData
# objects
sqlalchemy.orm.configure_mappers()

env.py - integration with Alembic

import sqlmodel
from myproject.database import _HistoryInternalBase

# Alembic supports a list of MetaData objects: we need both
# sqlmodel.SQLModel.metadata (application tables + *_version tables created by
# sqlalchemy-history) and _HistoryInternalBase.metadata (the Transaction table
# created by sqlalchemy-history's internal base).
target_metadata = [sqlmodel.SQLModel.metadata, _HistoryInternalBase.metadata]

myproject/conftest.py - test intgration

...  # somewhere when preparing a database for tests
        # The Transaction table lives on sqlalchemy_history's internal
        # metadata (not SQLModel.metadata) and must be created separately.
        myproject.database._HistoryInternalBase.metadata.create_all(engine)