Migrating from Elixir to SQLAlchemy declarative

migration.md

I have a project that's a few years old, but needs some maintenance. Since I last worked on the project, two major things have happened. First, Elixir -- a declarative layer on top of SQLAlchemy -- has not been maintained. Secondly, SQLAlchemy now has its own declarative layer.

Rather than continue using Elixir, I decided to migrate my data models to use the new SQLAlchemy declarative layer, which interestingly enough appear to be relatively compatible with Elixir's philosophy.

The first thing I did was do a direct mapping in my model columns and update the import statements.

# from Elixir import Entity, Field, DateTime, Unicode, Integer, Boolean, setup_all, create_all, session, metadata
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, Boolean
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

Entity => Base (This is what my models inherit from) Field => Column Unicode => String Boolean, DateTime, Integer, Boolean -- are all the same between Elixir and SQLAlchemy

That was basically it.

I also enabled join table inheritance. I have three models, two of which are subclasses of the first. Basically, in the parent class you have to add a new column to track the name of referenced child models, then tell the SQLAlchemy mapper which column to use. Elixir did this automagically, but we have to do it manually. We'll also add a tablename if you hadn't done so already.

In the parent class (Media), I add:

class Media(Base):
   # ... other column definitions here ...
   media_type = Column(String, default=u'')
   __mapper_args__ = {'polymorphic_on': media_type}
   __tablename__ = 'media'

In the subclasses, we only need two things (in addition to tablename). We are identifying the polymorphic name of this class (so that the parent class can reference it with the column type). We'll also create an id column. Elixir did both of these things automagically as well, so we're going to do them manually. In the two subclasses (Video, Image), we add:

class Image(Media):
   # ... other column definitions here ...
   __mapper_args__ = {'polymorphic_identity': 'image'}
   __tablename__ = 'images'
   id = Column(Integer, ForeignKey('media.id'), primary_key=True)

'video' looks identical, but with video in place of image. Maybe with a few extra columns (like codec type, duration etc.)

That's pretty much it. Now we just wrap up the connectors. Elixir exported a 'session' which was basically passthrough to an SQL Alchemy session. We need to call sessionmaker()'s factory to get the same effect:

# I put this at the bottom of the file

# Elixir bindings:
#create_all()
#setup_all()
#metadata.bind = "sqlite://mydb.sql"

# updated sqlalchemy:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine("sqlite://mydb.sql")
s = sessionmaker()
s.configure(bind=engine)
Base.metadata.create_all(engine)

#export session which is how the rest of the app expects it
session = s()

At this point, you should have your models all set up. The only difference now is that the query structure is slightly different.

In Elixir, I could do this:

db.Video.query.all()

But in SQLAlchemy, you do:

db.session.query(db.Video).all()

So I made a slight helper so that I can keep the same syntax style. The only difference is that we'll call class.query() as a function like: db.Video.query().all()

I'm also going to throw this on the super class, so that we don't have to redefine Video.query and Image.query separately (the only difference being that one calls session.query(Video), the other calls session.query(Image).

class Media(Base):
   # ... column definitions
   @classmethod
   def query(cls):
      return session.query(cls)

That's it! We use @classmethod which basically means that this function doesn't require a 'self', so it's basically static. However, we do get passed in an instance of the class type, so we can just pass that along to the global session variable.

Now, I can do this:

import db # this is my database file with my classes in it etc.
videos = db.Video.query().all()
images = db.Image.query().filter_by(category='cats').all()

To create a new object, we do have to add it to the session first. In Elixir:

db.Image(filename='cat.jpg')
db.session.commit()

Now we do:

img = db.Image(filename='cat.jpg')
db.session.add(img)
db.commit()

For deleting, we have to delete it directly in the session as well (db.session.delete(obj)). I created a nice helper for this too:

class Media(Base):
   # columns go here...
   def delete(self):
      return session.delete(self)

Now, we can do this, which is identical to the Elixir API:

obj = db.Image.query().filter_by(id=10).first()
obj.delete()
db.session.commit()

There are certainly cleaner ways to do it, for example not using a global session variable. I'll likely refactor that eventually, but for now the important part was minimal impact on the API.

Now, I can use the new version of SQLAlchemy and ditch Elixir. Thanks Elixir -- it was good while it lasted!

Thanks for this! Had some legacy code that barely worked with the new sqlalchemy, this elixir fork kept it working, but it's time to move on, and it seems it's not too painful! Just a few hours, most of which was testing features manually because the old code didn't have a proper test suite (obviously. old code never has a proper test suite)

I made this replacement to the declarative Base class, which:

1. sets the default table name for all models to have a prefix (i used a function in options_defaults["tablename"] before)
2. adds back the "table" and "query" attributes as "hybrid properties" (some weird sqlalchemy thing) instead of classmethods (no need to add () to every query)
3. adds back a trimmed down version of elixir's to_dict method which I used extensively in my application.
4. adds back get, an alias for query.get

@as_declarative()
class Entity(object):

    # replacement for elixir's options_defaults["tablename"]
    @declared_attr
    def __tablename__(cls):
        return 'dxir_%s' % cls.__name__.lower()

    @hybrid_property
    def table(self):
        return self.__table__

    @hybrid_property
    def query(self):
        return session.query(self)

    # dumb version of elixir's to_dict
    def to_dict(self):
        col_prop_names = [p.key for p in self.__mapper__.iterate_properties \
                                      if isinstance(p, ColumnProperty)]
        data = dict([(name, getattr(self, name))
                     for name in col_prop_names])
        return data

    @classmethod
    def get(cls, id):
        return session.query(cls).get(id)

I also did Field = Column at the module level just to make the diff smaller.

Also not covered by this guide: ManyToOne, OneToMany, ManyToMany relationships, which are significantly different on the model side, but luckily the usage from the application code seems to be the same. http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html explains it decently.

The changes I had to do to the rest of the application were surprisingly few. Mainly session.add after creating objects, and a single get_by()

@dequis Would it interest you to put all this in a library? I think this [awesome] work could be generally useful.

@dequis Seconding! That does sound useful. Even your pasted results have been incredibly helpful for migrating off of Elixir.

One other thing I found useful is modifying the init of the new Entity so that it automatically adds itself to the session for new objects like Elixir does.

Here's my Gist for how to do that: https://gist.github.com/kevinlondon/35a1d30e36d4f4ee47ec8c9975871829

Hi all. Randomly found these comments. So glad that you found it useful. The project itself is still humming along nicely.