SQLAlchemy-Continuum versions your models without changing how you query them

Two lines of setup and an object that remembers its history

SQLAlchemy-Continuum is a versioning and auditing extension for SQLAlchemy, and the setup is deliberately small. You need two things: call make_versioned() before your models are defined, and add __versioned__ to all models you wish to add versioning to.

    import sqlalchemy as sa
    from sqlalchemy_continuum import make_versioned

    make_versioned(user_cls=None)

    class Article(Base):
        __versioned__ = {}
        __tablename__ = 'article'

        id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
        name = sa.Column(sa.Unicode(255))
        content = sa.Column(sa.UnicodeText)

The ordering requirement is the one thing to get right. make_versioned() has to run before the models are defined, because the extension works by mapping your models and adding the versioning behaviour to them at that point. Declaring it afterwards is the mistake everyone makes once.

The `user_cls=None` argument tells the extension that your models have no separate user model to attribute changes to, which is the common case for a library that has to work with whatever schema you already have.

The example is an Article with a name and content, and the demonstration of what versioning buys you is the interesting part. After adding the article and committing, the article has one version stored in the database, and `article.versions[0].name` returns the name as it was. Change the name and commit again, and `article.versions[1].name` gives you the updated name. Then `article.versions[0].revert()` rolls the article back to the first version, and `article.name` is the original name again.

So the history is not a separate API you query. It is an attribute on the object, indexed by position, and reverting is a method call on a version.

The feature list names two things a history table cannot do

The features list is eight bullets, and two of them are the reason to use this rather than writing an audit trigger.

The first is that it can revert objects data as well as all object relations at given transaction even if the object was deleted. So the revert is not just field values, it is the whole object graph at a point in time, and it works on an object that no longer exists.

The second is temporal relationship reflection, described as the version object's relationship showing the parent objects relationships as they were at that point in time. That is the subtle one. If your Article has a many-to-many with tags, then `article.versions[0].tags` gives you the tags as they were when that version was current, not the tags as they are now.

Without that, a history table is a list of rows and reconstructing a past state means re-implementing the join yourself. With it, the version object behaves like a snapshot of the graph.

The rest of the list covers the mechanics. It creates versions for inserts, deletes and updates, and it does not store updates which don't change anything, which is worth noting because the naive implementations of this pattern fill a version table with rows that record nothing. Transactions can be queried afterwards using SQLAlchemy query syntax, so the history is reachable with the same query constructs as the live data, and you can query for changed records at a given transaction.

There is also support for Alembic migrations, which matters more than it sounds, because adding a version table to an existing database without Alembic means hand-writing DDL for a schema that has to track your models, and the two will drift.

Native PostgreSQL versioning moves the write out of the application

The last feature bullet is the one with a different failure mode from everything else: support for native versioning for PostgreSQL database, trigger based versioning.

Everything else in the library works by intercepting the ORM, so the version row is written by the same transaction that writes your data. That gives you atomicity for free and it works on any database SQLAlchemy supports. The cost is that the write happens in your application, so any other path that writes to the table bypasses the versioning.

The trigger based path inverts that. The database itself maintains the history, so a script using psql, a data migration, a report tool or another service all get versioned rows whether or not they know the extension exists. The cost is that the behaviour is now tied to the database and its trigger support, and the library's Python-side conveniences have to work with rows the database produced.

So the choice is a boundary decision about who owns the write path. Application-level versioning fits a codebase where SQLAlchemy is the only way data is written. Native versioning fits a system where the database is shared, and where an audit trail that can be bypassed is not an audit trail.

The README links the PostgreSQL wiki pages for audit triggers at the bottom of the page, alongside references for slowly changing dimensions, change data capture, anchor modelling and shadow tables, which gives a sense of where the design sits in the wider literature on the same problem.

A working example, and the packaging around it

The README then gives a complete working example, which is the version to copy if you are starting from scratch.

    from sqlalchemy_continuum import make_versioned
    from sqlalchemy import Column, Integer, Unicode, UnicodeText, create_engine
    from sqlalchemy.orm import Session, configure_mappers, declarative_base

    make_versioned(user_cls=None)

    Base = declarative_base()
    class Article(Base):
        __versioned__ = {}
        __tablename__ = 'article'
        id = Column(Integer, primary_key=True, autoincrement=True)
        name = Column(Unicode(255))
        content = Column(UnicodeText)

    configure_mappers()
    engine = create_engine('sqlite://')
    Base.metadata.create_all(engine)
    session = Session(engine)

Two details in there are easy to miss. The `configure_mappers()` call comes after the model definition and before the engine is created, and it is there because the extension adds attributes to your models that the ORM needs to resolve. And the engine is `sqlite://` in memory, so the example runs with no server.

The packaging is modern and specific about its dependencies. The project is SQLAlchemy-Continuum at version 2.0.0, it requires Python 3.11 or newer, and the single runtime dependency is SQLAlchemy at 2.0.2 or newer but below 2.2. The classifiers list Python 3.11 through 3.15, which is a wide range and suggests active testing across versions.

There is one set of optional extras and they are all Flask related, with Flask at 2.0 or newer, Flask-Login at 0.6 or newer and Flask-SQLAlchemy at 3.0 or newer. That combination is for the Flask-SQLAlchemy session integration, which is the point where the extension has to hook into a session that Flask manages rather than one you created.

The project files also show a recent move to modern Python tooling. There is a pyproject.toml using setuptools with a 77 or newer requirement, a uv.lock, a tox.ini for the test matrix, a .python-version file, a .pre-commit-config.yaml, and a zensical.toml for the documentation build.

Versions are tied to transactions, and that is the semantic to understand

The feature list refers to transactions rather than to saves or commits, and that word choice is doing real work.

A version is not created per attribute change or per flush. It is created per transaction, and the whole point of querying by transaction is that you can ask what the database looked like at a particular commit. The library keeps a transaction identifier on the version rows, and that identifier is the primary axis of the history.

The consequence is that you can ask questions that a per-save history cannot answer. Which records changed in transaction 4,213. What was the state of this object as of transaction 4,213. When did this field change. Those are audit questions in the literal sense, and they are the reason an audit trail that records only rows and timestamps is often not enough.

It also has a performance implication. The version table grows by transaction rather than by row change, and the library is explicit about not storing updates which change nothing, so a transaction that writes the same values does not add a row. On a system with lots of no-op saves, that is the difference between a history table that stays useful and one that becomes noise.

The revert operation then means something precise: reverting to a version restores the object and its relations to the state they were in at that transaction. It is not an undo stack in the application sense, it is a restore from the database's own record of the past, and the two are only equivalent if nothing else wrote to the table in between.

Where this sits against a hand-written audit trail

The realistic alternatives are a database trigger, a hand-rolled event log, or a separate versioning tool.

A database trigger is the closest comparison and the native PostgreSQL path is exactly that. The difference is that the trigger writes rows without knowing about your ORM, while the library writes rows through the ORM and therefore knows about your relationships, your model configuration and your session lifecycle, which is what makes temporal relationship reflection and graph-wide revert possible at all.

A hand-rolled event log, where you write an audit row yourself in each service that mutates data, has the opposite profile. It is explicit and it is under your control, but it only covers the code paths that remembered to log, and it does not reconstruct state, only record that something happened.

A separate versioning tool, such as one built on database snapshots or logical replication, captures the database as a whole and is a stronger guarantee, since nothing can bypass it. The cost is that the version is a database snapshot rather than an object, so you query the past with SQL against a schema rather than with `article.versions[0]`, and the ergonomics of working with a historical object are gone.

The library sits between these, and its strongest case is the ergonomic one. An audit trail that is reachable through the same objects and the same query syntax as the live data is much easier to build features on than one that is a separate dataset. The trade is the boundary condition: application-level versioning only covers what goes through SQLAlchemy, and if your system has more than one writer, the native trigger path is the one to look at.

Editorial conclusion

Adopt SQLAlchemy-Continuum if you need an audit trail or a point-in-time view of your data and you already use SQLAlchemy, since it keeps the versioning inside the ORM rather than beside it. Skip it if you need the versions to be written by the database rather than the application, because the native PostgreSQL path is trigger based and behaves differently, and if you are on an older SQLAlchemy, since the current release requires 2.0.2 or newer. Verify first how you want the version table managed, whether by Alembic migrations or by the native support, that you understand the transaction boundary semantics, because versions are tied to transactions, and which Python you are on, since the package now requires 3.11.

Frequently asked questions

Official sources

1. Issues
2. License: BSD-3-Clause
3. README
4. Releases
5. sqlalchemy-continuum/sqlalchemy-continuum on GitHub