{ Repost from my personal blog @ https://blog.codezero.xyz/adding-revisioning-to-sqlalchemy-models }

In an application like Open Event, where a single piece of information can be edited by multiple users, it’s always good to know who changed what. One should also be able to revert to a previous version if needed. That is where revisioning comes into picture.

We use SQLAlchemy as the database toolkit and ORM. So, we wanted a versioning tool that would work well with our existing setup. That’s when I came across SQLAlchemy-Continuum – a versioning extension for SQLAlchemy.

Installation

The installation of the module is just like any other python library. (don’t forget to add it to your requirements.txt file, if you have one)

pip install SQLAlchemy-Continuum

Setup

Now, it’s time to enable SQLAlchemy-Continuum for the required models.

Let’s consider an Event model.

import sqlalchemy as sa

class Event(Base):
    __tablename__ = 'events'
    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
    name = sa.Column(sa.String)
	start_time = sa.Column(db.DateTime, nullable=False)
    end_time = sa.Column(db.DateTime, nullable=False)
    description = db.Column(db.Text)
    schedule_published_on = db.Column(db.DateTime)

We need to do three things to enable SQLAlchemy-Continuum.

1. Call make_versioned() before the model(s) is/are defined.
2. Add __versioned__ = {} to all the models that we want to be versioned
3. Call configure_mappers from SQLAlchemy after declaring all the models.

import sqlalchemy as sa
from sqlalchemy_continuum import make_versioned

# Must be called before defining all the models
make_versioned()

class Event(Base):

    __tablename__ = 'events'
    __versioned__ = {}  # Must be added to all models that are to be versioned

    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
    name = sa.Column(sa.String)
	start_time = sa.Column(db.DateTime, nullable=False)
    end_time = sa.Column(db.DateTime, nullable=False)
    description = db.Column(db.Text)
    schedule_published_on = db.Column(db.DateTime)

# Must be called after defining all the models
sa.orm.configure_mappers()

SQLAlchemy-Continuum creates two tables:

1. events_version which stores the version history for the Event model linked to the transaction table via a foreign key
2. transaction which stores information about each transaction (like the user who performed the transaction, transaction datetime, etc)

SQLAlchemy-Continuum also adds listeners to Event to record all create, update, delete actions.

Usage

All the CRUD (Create, read, update, delete) operations can be done as usual. SQLAlchemy-Continuum takes care of creating a version record for each CUD operation. The versions can be easily accessed using the versions property that is now available in the Event model.

event = Event(name="FOSSASIA 2017", description="Open source conference in asia")
session.add(event)  # Event added to transaction
session.commit() # Transaction comitted and event recored created

# This would have created the first version record which can be accessed
event.versions[0].name == "FOSSASIA 2017"

# Lets make some changes to the recored.
event.name = "FOSSASIA 2016"
session.add(event)
session.commit()

# This would have created the second version record which can be accessed
event.versions[1].name == "FOSSASIA 2016"

# The first version record still remains and can be accessed
event.versions[0].name == "FOSSASIA 2017"

So, that’s how basic versioning can be implemented in SQLAlchemy using SQLAlchemy-Continuum.