Declarative Mapping with SQLAlchemy · Monday, February 13, 2006

UPDATE: My work on ActiveMapper has been discontinued in favor of the elixir delcarative mapper.

For a long time now, I have been a huge fan of a Python object-relational mapper called sqlalchemy, which is much more flexible and powerful than the other popular ORM(object relational mapping) toolkits currently available. As much as I liked SQLAlchemy, I found its separation of database mapping and class design to be a little cumbersome, and hibernate-like.

As a result, I began to tinker around a bit with SQLAlchemy in order to create a way to define database mapping and class design in a single declarative way. A few days ago, Mike Bayer was kind enough to put my first pass at this into the SQLAlchemy subversion repository as the sqlalchemy.ext.activemapper module, along with unit tests that show it off. The module allows you to define your classes and your database mapping in one go, while not forgoing most of the amazing flexibility of SQLAlchemy's table and mapper objects.

ActiveMapper allows you to easily declare tables and classes like the following:

class Person(ActiveMapper):
    class mapping:
        id          = column(Integer, primary_key=True)
        full_name   = column(String)
        first_name  = column(String)
        middle_name = column(String)
        last_name   = column(String)
        birth_date  = column(DateTime)
        ssn         = column(String)
        gender      = column(String)
        home_phone  = column(String)
        cell_phone  = column(String)
        work_phone  = column(String)
        prefs_id    = column(Integer, foreign_key=ForeignKey('preferences.id'))
        addresses   = one_to_many('Address', colname='person_id', backref='person')
        preferences = one_to_one('Preferences', colname='pref_id', backref='person')
    
    def __str__(self):
        s =  '%s\n' % self.full_name
        s += '  * birthdate: %s\n' % (self.birth_date or 'not provided')
        s += '  * fave color: %s\n' % (self.preferences.favorite_color or 'Unknown')
        s += '  * personality: %s\n' % (self.preferences.personality_type or 'Unknown')
        
        for address in self.addresses:
            s += '  * address: %s\n' % address.address_1
            s += '             %s, %s %s\n' % (address.city, address.state, address.postal_code)
        
        return s


class Preferences(ActiveMapper):
    class mapping:
        __table__        = 'preferences'
        id               = column(Integer, primary_key=True)
        favorite_color   = column(String)
        personality_type = column(String)


class Address(ActiveMapper):
    class mapping:
        id          = column(Integer, primary_key=True)
        type        = column(String)
        address_1   = column(String)
        city        = column(String)
        state       = column(String)
        postal_code = column(String)
        person_id   = column(Integer, foreign_key=ForeignKey('person.id'))

ActiveMapper can infer the table name from the class name, and handle all the relationships between dependent objects. There is still plenty of work to do, and a few unit tests are failing. I have already received some feedback about the repetitiveness of typing `column` repeatedly, and a few other minor nits, but I really feel like we are on to something with this.

I am currently looking for a partner or two in crime to help me take this to a new level. Right now, its more of a proof-of-concept than a full layer on top of SQLAlchemy. Mike Bayer seems to be in full support of the effort, and has given me committer status on the SQLAlchemy repository, so lets get this thing rolling. If you want to join me, please leave a comment here, or shoot me an email so that we can make declarative SQLAlchemy a reality.