Cross-database relations in SQLAlchemy?
Written by on in Snaking.

Set out to make an app use two different databases today; one for a high-write-volume set, the other for low-volume mostly-read set.  It's a TurboGears 2.0 application using SQLAlchemy.  I'd half-way hoped for "tell these tables they're in that db" and then have the ORM figure out all the magic of which session to use for queries, how to "join" across the two databases efficiently, and generally just... you know... magic it all away so I can sling tables hither and fro without a care in the world.

I got it all working, but it required manually managing the connections at every point where the two databases reference each other (or rather, the two sets of objects, there's only one-way pointers at the DB level).  That wasn't horrible, but it didn't make me go "wow, that was clean and elegant".

I wanted "relations" in all but name, queries across the DB that would let the code continue to say "x.yes" to get the collection of yes in the other DB, with all the ORM-y goodness, adding items to the collection, passing object references and having them auto-convert to foreign-key field references, etc.  Basically, I wanted the old code to just work, even if it did have to do some magic behind the scenes.

Maybe SQLAlchemy 0.7 makes it all magic?  Oh well, for now I'm almost done the transition, and I'm not sure I'd really be all that satisfied with the magic anyway, after all, magic does have a habit of blowing up in your face.

Comments

  1. Eric

    Eric on 10/25/2011 1:52 a.m. #

    Dejavu[0] used to do this sort of thing. It wasn't always the most performant, but it was definitely magic! Bob did an interesting talk at PyCon 09 I believe[1].

    It isn't actively developed but it had some really interesting ideas and there might even be some code someone could draw from for SQLAlchemy.

    [0] http://www.aminus.net/dejavu/
    [1] http://blip.tv/pycon-us-videos-2009-2010-2011/dejavu-language-integrated-query-for-data-1958907

  2. Tobu

    Tobu on 10/25/2011 4:37 a.m. #

    PostgreSQL has foreign tables if that helps:

    CREATE SERVER film_server FOREIGN DATA WRAPPER pgsql OPTIONS (host 'foo', dbname 'foodb', port '5432');
    CREATE FOREIGN TABLE films (
    code char(5) NOT NULL,
    title varchar(40) NOT NULL,
    did integer NOT NULL,
    date_prod date,
    kind varchar(10),
    len interval hour to minute
    )
    SERVER film_server;

    http://www.postgresql.org/docs/current/static/sql-createforeigntable.html
    http://pgxn.org/tag/fdw/

  3. mike bayer

    mike bayer on 10/25/2011 12:24 p.m. #

    if you set up a Session against two engines, link one class to one engine, the other to the other, and use "lazy loading" so that only one table is referenced at a time, it is pretty much automatic. you just have to configure "primaryjoin" between them if they don't use a traditional foreign key relationship. that's also nothing new it's been able to do that since version 0.1. couldn't get relationship() to work ?

  4. Mike Fletcher

    Mike Fletcher on 10/25/2011 4:24 p.m. #

    Oh.. for criminies sake... the docs (http://turbogears.org/2.1/docs/main/MultipleDatabases.html) don't seem to mention anything about being able to use a single session... argh.

  5. mike bayer

    mike bayer on 10/25/2011 5:40 p.m. #

    yeah don't read the TG docs for these things. I get a lot of user issues due to alternate documentation bases. SQLAlchemy is enough of a productivity tool for those who know it well that its really well worth it to go through the docs fully, in this case you'd go through http://www.sqlalchemy.org/docs/orm/session.html .

Comments are closed.

Pingbacks

Pingbacks are closed.