One of the important functionality required of a LIMS is keeping a history of the changes applied to the data stored within the underlying database. This can be a tricky aspect to develop and/or put in place and there are certainly a number of ways one can go about implementing such a solution.
Fortunately for all you SQLAlchemy fans out there, a fully implemented versioning solution is provided in the ORM’s examples page. While the examples page provides a few different implementations, my preferred one is Versioning with a History Table.

It’s ridiculously simple to put in place and it’s entirely transparent once activated, requiring no extra code whatsoever… Sweet !
The implementation offers a mixin designed to work with declarative. For users of the classical mappers, a set of functions is also available.

It’s ridiculously simple to put in place and it’s entirely transparent once activated, requiring no extra code whatsoever.

Let me show you how through an example:

Basically, you just need to import the necessary mixin Class and session wrapper, add the mixin to your class declaration and wrap your SQL session in the versioned_session wrapper.

So this snippet:


Base = declarative_base()

class SomeClass(Base):
    __tablename__ = 'sometable'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))


Session = sessionmaker(bind=engine)
sess = Session()
# do some stuff within your session

Becomes:


# Add the necessary import statements
from history_meta import (
Versioned, 
versioned_session
)
Base = declarative_base()

class SomeClass(Versioned, Base):
    __tablename__ = 'sometable'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))


Session = sessionmaker(bind=engine)

# Wrap your session inside a versioned_session
versioned_session(Session)

sess = Session()
# do some stuff within your session

That was easy enough !
What this does is create sister tables (with _history appended to their name) for every Versioned object declared. These tables hold all the information from the original table plus a version attribute (basically an auto_increment) and an optional changed attribute of type utc_timestamp.

From here on out, every time an update or delete is applied to the main object, its status *before* the change is pushed in the _history sister table and the version attribute is incremented. The great thing is: this takes place auto-magically, without writing any extra lines of code.

And how do you access the history to retrieve past states of a DB entry ? Well, the following lines of code detail how to get a class corresponding to the History of the sister class which you can then use as any other SQLAlchemy object:


SomeClassHistory = SomeClass.__history_mapper__.class_
past_states = SomeClassHistory.filter(SomeClassHistory.id == some_id)\
                  .order_by(SomeClassHistory.version.desc()).all()

## Do stuff with the past states :)

Great huh ?
Now get crackin’ and deploy Versioned objects in any project that could benefit from having them !