Using PostgreSQL schemas with SQLAlchemy and Elixir

At work, we're translating an existing Django application to TurboGears.  As expected, pretty much the largest chunk of work revolved around the ORM.  We're going to be using Elixir as a light layer on top of SQLAlchemy - of the various options around technologies for templating, framework, and so forth, it seems SQLAlchemy is the universal (amongst, you know, three people) choice for the ORM (while the Django/TurboGears and Genshi/Django/ClearSilver debates raged).

Anyway, that's incidental.

When using Django (0.95), we had to add a search_path to the Django database connector for PostgreSQL, because Django didn't support specifying the schema that the tables managed by the ORM used.  I got a bit side-tracked trying to do the same for SQLAlchemy, since searching for "sqlalchemy schema" wasn't getting me anywhere, since schema has multiple meanings.

In the end, it's trivially simple - just pass the schema in the table options.  Since Elixir is a thin layer on top of SQLAlchemy which tries not to limit the functionality of SQLAlchemy itself, it's quite simple - the using_table_options statement can pass the schema to the table options (if that wasn't obvious from the name of the statement):

class Channels(Entity):
    has_field('desc', Unicode(100))
    
    belongs_to('settings', of_kind='Settings')
    
    using_options(tablename='channels')
    using_table_options(schema='ips')

There was a small bug in Elixir's relationship management code that meant that cross-schema relationships didn't work despite SQLAlchemy supporting it.  I sent a one-line patch to the mailing list and it is fixed now.

With per-table schema support in SQLAlchemy, we can also create the database with the tables in the correct schema - something we couldn't do before.  Nor, likely, anything we'd ever do with this project.  But it's nice that we can in future projects.