SQLAlchemy

Monday 29 May 2006

Now that I’m building a serious db-backed site with Python, I have a better appreciation for what database mapping packages are like. I’m using Django (pre-magic-removal for the moment), and am already bumping up against the limitations of the object relational mapper (ORM).

So I was impressed to see SQLAlchemy‘s philosophy statement:

SQL databases behave less and less like object collections the more size and performance start to matter; object collections behave less and less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accommodate both of these principles.

SQLAlchemy doesn’t view databases as just collections of tables; it sees them as relational algebra engines. Its object relational mapper enables classes to be mapped against the database in more than one way. SQL constructs don’t just select from just tables—you can also select from joins, subqueries, and unions. Thus database relationships and domain object models can be cleanly decoupled from the beginning, allowing both sides to develop to their full potential.

We’re not moving away from the Django ORM (this week), but it’s good to have an understanding of the options out there, and SQLAlchemy looks quite promising.

Comments

[gravatar]
Capt. Kirk 3:04 PM on 29 May 2006

Are you able to use DB views from the Django ORM? IMO using views is a good way to push complex queries toward the DB layer while using an ORM.

[gravatar]
Robin Munn 3:38 PM on 29 May 2006

If you're interested in SQLAlchemy, I've got a tutorial up at http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html . Hope it helps.

[gravatar]
Jonathan Ellis 7:19 AM on 30 May 2006

"We're not moving away from the Django ORM"

Maybe you should think about it, though. :)

You probably hate comparisons to "that other toolkit" but TG seems to be moving to SA. Not only is SA out there and working already (vs rolling your own work-alike) but this stuff is _hard_. You really need to be a db expert as well as a Python expert and there aren't too many of those around. Mike Bayer has put months of work into this and the result is impressive.

[gravatar]
Anonymous 8:25 AM on 30 May 2006

I'd stay clear of SQLAlchemy if I were you. My housemate has been using it on a project for work and it has pretty much drained the will to live from him. I've looked at some of the code and there's good reason for this: it's full of bugs, missing features, and misunderstandings about how an RDBM should be used.

The philosophy statement is quite nice, but it's not really reflected in the code.

[gravatar]
Karl Guertin 9:38 AM on 30 May 2006

Care to expound on that? In particular, what database and what portion of the code? Different people seem to have different ideas on the 'proper' way to run relational db but otherwise, that's the first negative thing I've heard about sqlalchemy. I've been using it since 0.91 on mysql and have never had a problem, but I expect that mysql and postgres have the best exercised code paths.

[gravatar]
Jonathan Ellis 9:59 AM on 30 May 2006

I think "Anonymous" prefers to make vague assertions; if he were more specific he could be easily proven wrong.

SQLAlchemy is better-designed than any comparable system I've seen, and I've been pretty deep in the code of several other ORMs.

[gravatar]
Anonymous 12:03 PM on 30 May 2006

Specific problems:

- Transaction handling is inconvenient and encourages incorrect programs: you have to explicit try/except/else to make your code correct, every time you use a transaction. This bloats code and makes it easy to write broken code.

- In-memory reprensentation of objects can trivially become inconsistent with the database: attributes can easily end up with the wrong values or, perhaps worse, objects from the ORM can be "forgotten about" resulting in all subsequent usage providing semi-random behavior, ie all attributes silently taking on the value None.

- There is no in-memory revert support.

[gravatar]
mike 2:40 PM on 30 May 2006

1. transactionally correct code:

session.flush()

done.

2. Inconsistent object state: session.expire(object) / session.clear()

3. Revert: session.expire(object) / session.clear(). SA does have the abiltiy to directly revert modified properties in memory as well but its undocumented, since loading back from the DB is less error-prone.

4. It seems that you have a fundamental misunderstanding of how session-based development works. I suggest you consult the Hibernate website for further details. Django users would be better served improving their toolsets and honestly understanding alternatives rather than reactively spreading FUD.

[gravatar]
Anonymous 3:22 PM on 30 May 2006

Transactionally correct code -

foo()
session.flush()

Oops, foo() raised an exception after it changed some stuff. What state is the database in? What state are the in-memory objects in? Who knows, some random state. Correct would have been:

try:
foo()
except:
session.rollback()
# Perhaps raise here, depending on what else is going on
else:
session.flush()

The fact that you got it wrong here is a good demonstration of how it encourages broken software.

Perhaps the issues I raised are fundamental to "session-based development" works and SQLAlchemy's only fault is replicating them. I haven't used Hibernate.

FWIW, I'm not a Django user. I suspect SQLObject has many of the same problems as SQLAlchemy. I'm not advancing any agenda here, save that of well-written, bug-free software.

[gravatar]
mike 4:04 PM on 30 May 2006

right, other ORMs automatically catch all exceptions thrown in a program regardless of where they occur and upon catching completely revert the state of all your persisted objects without your permission. where can i get an ORM like that ?

[gravatar]
Ned Batchelder 4:48 PM on 30 May 2006

Whoa, everyone! Settle down! Sounds like Anonymous (if that is your real name!) doesn't like SQLAlchemy, and mike does. If I read this right, Mike's getting a bit sarcastic because he thinks Anonymous is expecting too much from an ORM.

I'd love to have a healthy debate here, but you guys might have to try harder to explain your points and understand each other's.

[gravatar]
Glyph Lefkowitz 1:25 AM on 31 May 2006

mike: I'm not sure if you're saying that in-memory revert is impossible or that it's a bad idea; however, a number of Python database tools do it. For example, I believe ZODB does - there's not much documentation on it, but that's because you only have one view of your database objects, they just "are", there is no load/query/store API. Reverting a transaction reverts all your Python objects. Divmod's first database project, Atop, was very similar to ZODB and managed transactions in a similar way.

Of course, if you raise exceptions outside a transaction in these systems, your database and your in-memory objects will be in whatever state it happened to be in when the exception was raised; there's nowhere to roll it back to.

Axiom, Divmod's current database layer, which is somewhat ORM-like (although it depends on particular features of SQLite) can also do in-memory revert; it only reverts database-persistent state, and it does it by issuing queries to determine the state of all objects which are still cached after all the other revert work is done. It also uses a simple higher order function called "transact" to remove boilerplate try/except/else statements, which is basically just this:

def transact(f):
.begin()
.try:
..result = f()
.except:
..abort()
..raise
.commit()
.return result

BTW: Hi ned! We should really hang out some time. Antonio said he'd introduce us but I'm still waiting, I get the feeling he's busy enough that if I just keep waiting we'll never meet :).

[gravatar]
mike 2:20 PM on 31 May 2006

hey glyph -

SA is actually keeping track of the "original" value of an attribute when you change it to something new, and it also does something similar for a list based element when you modify its contents. the main purpose of this history tracking is to determine "what's changed?" when you do a flush.

Originally, SA had the fact that you can "roll back" the attributes of an object back to their original value completely in-memory listed as a prominent feature, but early on some folks were playing with it and were getting it confused with database rollbacks, etc. also im not too certain about what kinds of issues might arise regarding related state in the object which is not persisted within the DB, if theres any gotchas involving cascading onto child items, etc., and it seems ambiguous why you would want to roll back "in-memory" changes when you can just "expire" and get back exactly what was in the DB (and a big theme of SA, particularly in 0.2, is getting rid of ambiguously similar ways of doing something).

So while its there, its the kind of thing Id want to revisit in a more careful way, getting some dialogue over how exactly it should function and how/when it should be used, before re-introducing it as a prominent feature.

[gravatar]
Glyph Lefkowitz 6:59 PM on 1 Jun 2006

Mike: it sounds like it might be good to have an interactive discussion where we can compare notes. I'd like to show you some features of Axiom and see where ideas can be stolen or shared. My AIM handle is "theglyph" if that would be useful to you :)

Add a comment:

Ignore this:
Leave this empty:
Name is required. Either email or web are required. Email won't be displayed and I won't spam you. Your web site won't be indexed by search engines.
Don't put anything here:
Leave this empty:
URLs auto-link and some tags are allowed: <a><b><i><p><br><pre>.