Adding a Last Modified At column in Open Event Server

This blog article will illustrate how, with the help of SQLAlchemy, a last modified at column, with complete functionality can be added to the Open Event Server database. To illustrate the process, the blog article will discuss adding the column to the sessions api. Since last modified at is a time field, and will need to be updated each time user successfully updates the session, the logic to implement will be a slightly more complex than a mere addition of a column to the table.

The first obvious step will comprise of adding the column to the database table. To achieve the same, the column will have to be added to the model for the sessions table, as well as the schema.

In app/api/schema/sessions.py:

...
class SessionSchema(Schema):
   """
   Api schema for Session Model
   """
   ...
   last_modified_at = fields.DateTime(dump_only=True)
   ...

And in app/models/sessions.py:

import pytz
...

class Session(db.Model):
   """Session model class"""
   __tablename__ = 'sessions'
   __versioned__ = {
       'exclude': []
   }
   ...
   last_modified_at = db.Column(db.DateTime(timezone=True),   
   default=datetime.datetime.utcnow)
   def init(self, ..., last_modified_at=None))
     #inside init method
     ...
     self.last_modified_at = datetime.datetime.now(pytz.utc)
     ...

NOTE: The users for the open event organiser server will be operating in multiple time zones and hence it is important for all the times to be in sync, hence the open event database maintains all the time in UTC timezone (python’s pytz module takes care of converting user’s local time into UTC time while storing, thus unifying the timezones.) From this, it directly follows that the time needs to be timezone aware hence timezone=true is passed, while defining the column.

Next, while initialising an object of this class, the last modified time is the time of creation, and hence

datetime.now(pytz.utc) is set as the initial value which basically stores the current time in UTC timezone format.

Finally, the logic for updating the last modified at column every time any other value changes for a session record needs to be implemented. SQLAlchemy provides an inbuilt support for detecting update and insert events which have been used to achieve the goal. To quote the official SQLAlchemy Docs,  “SQLAlchemy includes an event API which publishes a wide variety of hooks into the internals of both SQLAlchemy Core and ORM.

@event.listens_for(Session, 'after_update')
def receive_after_update(mapper, connection, target):
  target.last_modified_at = datetime.datetime.now(pytz.utc)

The listens_for() decorator is used to register the event according to the arguments passed to it. In our case, it will register any event on the Session API (sessions table), whenever it updates.

The corresponding function defined below the decorator, receive_after_update(mapper, connection, target) is then called, and session model (table) is the the registered target with the event. It sets the value of the last_modified_at to the current time in the UTC timezone as expected.

Lastly, since the changes have been made to the database schema, the migration file needs to be generated, and the database will be upgraded to alter the structure.

The sequence of steps to be followed on the CLI will be

> python manage.py db migrate
> python manage.py db upgrade

Resources

Migrations make us crazy!

Our Open Event team is not small, so problems with migrations occur very often. I’d like to describe how to solve these common problems and avoid Contributors frustration. Because more of us didn’t know how to solve this problems at the beginning so we wasted a lot of time to find a source of problem.

The most common mistake is that we forget run migration on Heroku. Developer is sometimes surprised because something works for him but he forgets to run migration on server. These small mistakes lead to huge problems and at that time our app throws a lots of bugs related to database. We can often see “Internal server error”.Screen Shot 2016-06-17 at 22.55.25.png So if developer changes table he has to run migration!

vagrant@vagrant-ubuntu-trusty-64:/vagrant$ python manage.py db migrate

But above command Quite often doesn’t solve our problem, because We face other problems while updating DB for example

alembic.util.exc.CommandError: Multiple head revisions are present for given argument ‘head’; please specify a specific target revision, ‘<branchname>@head’ to narrow to a specific head, or ‘heads’ for all heads

this problem is caused by two developers which push code to Github with migrations without merging two heads to achieve one head.

So to solve this problem you only have to know ids of two heads

vagrant@vagrant-ubuntu-trusty-64:/vagrant$ python manage.py db heads

e38935822969 (head)
f55fde3d62b1 (head)

Then you have to merge it

vagrant@vagrant-ubuntu-trusty-64:/vagrant$ python manage.py db merge e38935822969 f55fde3d62b1

Generating /vagrant/migrations/versions/ecb671d1eb4b_.py … done

Upgrade DB

vagrant@vagrant-ubuntu-trusty-64:/vagrant$ python manage.py db upgrade

INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.runtime.migration] Running upgrade 00ea66754d06 -> d84f976530e1, empty message
INFO [alembic.runtime.migration] Running upgrade d84f976530e1 -> 1b3e4f5f56bd, empty message
INFO [alembic.runtime.migration] Running upgrade 1b3e4f5f56bd -> e38935822969, empty message
INFO [alembic.runtime.migration] Running upgrade e38935822969, f55fde3d62b1 -> ecb671d1eb4b, empty message

And finally run migrations

vagrant@vagrant-ubuntu-trusty-64:/vagrant$ python manage.py db migrate
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.ddl.postgresql] Detected sequence named ‘role_id_seq’ as owned by integer column ‘role(id)’, assuming SERIAL and omitting
INFO [alembic.ddl.postgresql] Detected sequence named ‘microlocation_id_seq’ as owned by integer column ‘microlocation(id)’, assuming SERIAL and omitting….