6 ms·

This was a fun blog post, and was shockingly prescient: SQLAlchemy rose right about this time to become one of my favorite libraries and an impressive feat of s

This was a fun blog post, and was shockingly prescient: SQLAlchemy rose right about this time to become one of my favorite libraries and an impressive feat of solving the ORM issue with finality. They have a sort of low-level python SQL api, a higher level declarative ORM layer, and a custom glue that let's you wire objects and sql together in arbitrary but useful & maintainable ways. I think this was possible because the SQLAlchemy devs had this insight: "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."

habitue 13y ago
Yeah, I think sqlalchemy broke the mold. It starts from the idea that you already know and understand SQL and relational constructs well, then adds a layer of abstraction that lets you skip tons of boilerplate and write really elegant code. Personally, while there are lots of good decisions in sqlalchemy like the unit of work pattern (in contrast to the active record pattern), the one that pays off time and again is representing the SQL ast as python objects. It eliminates and entire class of text munging issues that occur when you write raw SQL and just completely makes you wonder "where's the mismatch they keep talking about?"
- _pmf_ 13y ago
  Do you know of any Java-OR mappers in the same spirit?
  - teacup50 13y ago
    Java: http://www.jooq.org/ http://www.jooq.org/ Scala: http://slick.typesafe.com/ http://slick.typesafe.com/ They also have the benefit of being fully type-safe across query projections.
    - lukaseder 13y ago
      I don't think Slick can be considered a very SQL-centric API. While the Slick folks embrace the relational model, they certainly do not embrace the SQL language. Just as with LINQ, this can be desirable if you want to reason about collections in a more general sense. On the other hand, the SQL standard has gone far beyond the "occasional" OUTER JOIN that can turn out to be a true challenge to Slick. In other words, SQL has never been purely relational. It is a beast of its own.
forgotprevpass 13y ago
I played around with sqlalchemy a bit this week, it seems you still have to dive down to the bare relational manual mapping (by creating a table by hand) to do something simple, like many-to-many mappings. Is there a better way to do it?
pyalot2 13y ago
I've been there with SQLAlchemy in late 2006, and I vowed never to return. Let me state this, SQLAlchemys pool/engine/SQL-Api is great, and it's got nothing whatsoever todo with ORM. But SQLAlchemy does contain an ORM, and it does become unmaintainable just like every other ORM. It roughly goes like this: 1) You use the ORM 2) Things get slow 3) You write a bit of custom mappers 4) Soon all your logic can't be operated without custom mappers 5) Congratulations, you've now written all the handwired code you would have written without an ORM, plus all the ORM code on top, it's now unmaintainable, full of bugs and oversights AND slow. And that about sums up everybodies experience with ORMs unless they do trivially small things. It's at this junction people begin thinking about solving this ORM problem. And they learn it's bloody hard. Obligatory XKCD reference: There are N ORMs on the market, they all suck. Let's solve all these problem and do a new ORM to rule them all. There are now N+1 ORMs that all suck on the market.
- IgorPartola 13y ago
  Second this experience. A single row pk lookup using SQLAlchemy adds 30ms overhead to a query. That is huge. To avoid this, last time I touched it, I ended up just creating my own DB access library that spit out simple objects and used raw SQL underneath. I believe for any large system that is going to be the answer. Having said that, do not underestimate the number of apps doings "trivial" things. There are lots. The problem with SQLAlchemy I think is that it is actually advanced enough to have you use it right up to the point where you must rewite all your code because it suddenly is just dead slow. In contrast the Django ORM is too simplistic to get too far with it.
  - pyalot2 13y ago
    Sure for simple things, of which there are many, ORMs can work sufficiently up to a point. But, the problem is that, you rarely get to go to your boss and say, well, this thing we've been using to do, you know, our data stuff, it's completely wrong. We need to throw everything away and start from scratch. The only situation in which you get to say that, is when you're explicitly make it clear that you're writing a raw prototype that'll not scale, and that nothing short of a complete rewrite is gonna be required as soon as you finish (and even then, it can turn out tricky to actually do that). Unfortunately, most people who get themselves into the ORM mess don't realize that this coming for them in the future. So they don't go their boss and make the prototype proposal. This all but cements the entire failure of the project right then.

habitue 13y ago

Yeah, I think sqlalchemy broke the mold. It starts from the idea that you already know and understand SQL and relational constructs well, then adds a layer of abstraction that lets you skip tons of boilerplate and write really elegant code. Personally, while there are lots of good decisions in sqlalchemy like the unit of work pattern (in contrast to the active record pattern), the one that pays off time and again is representing the SQL ast as python objects. It eliminates and entire class of text munging issues that occur when you write raw SQL and just completely makes you wonder "where's the mismatch they keep talking about?"

_pmf_ 13y ago
Do you know of any Java-OR mappers in the same spirit?
- teacup50 13y ago
  Java: http://www.jooq.org/ http://www.jooq.org/ Scala: http://slick.typesafe.com/ http://slick.typesafe.com/ They also have the benefit of being fully type-safe across query projections.
  - lukaseder 13y ago
    I don't think Slick can be considered a very SQL-centric API. While the Slick folks embrace the relational model, they certainly do not embrace the SQL language. Just as with LINQ, this can be desirable if you want to reason about collections in a more general sense. On the other hand, the SQL standard has gone far beyond the "occasional" OUTER JOIN that can turn out to be a true challenge to Slick. In other words, SQL has never been purely relational. It is a beast of its own.

forgotprevpass 13y ago

I played around with sqlalchemy a bit this week, it seems you still have to dive down to the bare relational manual mapping (by creating a table by hand) to do something simple, like many-to-many mappings. Is there a better way to do it?

pyalot2 13y ago

I've been there with SQLAlchemy in late 2006, and I vowed never to return. Let me state this, SQLAlchemys pool/engine/SQL-Api is great, and it's got nothing whatsoever todo with ORM. But SQLAlchemy does contain an ORM, and it does become unmaintainable just like every other ORM. It roughly goes like this: 1) You use the ORM 2) Things get slow 3) You write a bit of custom mappers 4) Soon all your logic can't be operated without custom mappers 5) Congratulations, you've now written all the handwired code you would have written without an ORM, plus all the ORM code on top, it's now unmaintainable, full of bugs and oversights AND slow. And that about sums up everybodies experience with ORMs unless they do trivially small things. It's at this junction people begin thinking about solving this ORM problem. And they learn it's bloody hard. Obligatory XKCD reference: There are N ORMs on the market, they all suck. Let's solve all these problem and do a new ORM to rule them all. There are now N+1 ORMs that all suck on the market.

IgorPartola 13y ago
Second this experience. A single row pk lookup using SQLAlchemy adds 30ms overhead to a query. That is huge. To avoid this, last time I touched it, I ended up just creating my own DB access library that spit out simple objects and used raw SQL underneath. I believe for any large system that is going to be the answer. Having said that, do not underestimate the number of apps doings "trivial" things. There are lots. The problem with SQLAlchemy I think is that it is actually advanced enough to have you use it right up to the point where you must rewite all your code because it suddenly is just dead slow. In contrast the Django ORM is too simplistic to get too far with it.
- pyalot2 13y ago
  Sure for simple things, of which there are many, ORMs can work sufficiently up to a point. But, the problem is that, you rarely get to go to your boss and say, well, this thing we've been using to do, you know, our data stuff, it's completely wrong. We need to throw everything away and start from scratch. The only situation in which you get to say that, is when you're explicitly make it clear that you're writing a raw prototype that'll not scale, and that nothing short of a complete rewrite is gonna be required as soon as you finish (and even then, it can turn out tricky to actually do that). Unfortunately, most people who get themselves into the ORM mess don't realize that this coming for them in the future. So they don't go their boss and make the prototype proposal. This all but cements the entire failure of the project right then.