Adding revisioning to SQLAlchemy Models
{ 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.
- Call
make_versioned()before the model(s) is/are defined. - Add
__versioned__ = {}to all the models that we want to be versioned - Call
configure_mappersfrom 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:
events_versionwhich stores the version history for theEventmodel linked to the transaction table via a foreign keytransactionwhich 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.
Here is my kitchen converted to a work space, my GSoC Lab:)
You must be logged in to post a comment.