Thursday, 14 May 2009

Elixir and SQLAlchemy

After my initial foray into SQLAlchemy I started the process of mapping my current object model onto database tables and came across issues with my custom, hand-rolled relationship classes. While I expect I could have shoehorned these into SQLAlchemy mappers I decided instead that I'd be better served with an object model that was slightly more integrated with the database structure.

Cue Elixir. Elixir is a thin, ActiveRecord style wrapper on top of SQLAlchemy. It provides some useful features (such as simple to use relationship objects, and polymorphic associations) and allows you to drop down into SQLAlchemy for those features that they haven't wrapped, such as AssociationProxy (for doing a relationship through another relationship). Elixir is the missing link in the stack I was building up, giving me a powerful ORM and database layer with a lovely declarative syntax for building up all the objects I needed.

It took me about a week to go from my old model, through SQLAlchemy and into Elixir but in doing so, I have avoided a whole mess of spaghetti code involved in writing my objects out to a database, obtained database independence, and forced myself to refactor out a lot of code that was proving less than suitable for my requirements. On top of that, I should be able to integrate my system into a pylons or django web application much more easily now than I could have a week ago. Now if only I could get a similar system for XML parsing and allow me to concentrate on the important tasks I still have to do :)

Friday, 8 May 2009


Continuing on with my sort of 'series' on various libraries and languages, I've recently started to use SQLAlchemy as an ORM layer in my current project.

The reason for using an ORM library came from the immortal problem of suddenly realising that I had a complicated object model defined and an equally complicated database schema and needing to get information between the two of them. I had started to hack together a script that would dump the data in using SQL, but I realised after the first few inserts that it would be a long hard slog that would be inflexible and complicated spaggeti code.

I didn't want to have to rewrite my object model significantly though, which meant I had to find an ORM library that would allow me to set up the mapping myself. I also needed it to work in Python, which then led me to SQLAlchemy. This nifty library comes in several libraries and is very flexible in the right ways to make it suitable for my needs (at least so far).

I'm still in the process of setting up the internal data structures. I'm needing to redo some parts of my internal object system but not significantly so, although I'm worried about it becoming a bit of a mess.

I'll comment on a bit more of the technical side of things in a few days when I've gotten more up to speed with the library.