
SQLAlchemy vs Other ORMs - Sami_Lehtinen
http://www.pythoncentral.io/sqlalchemy-vs-orms/
======
tdicola
Am I weird for preferring raw SQL over query abstractions like these ORMs
expose? IMHO SQL is a perfectly fine language. I do however like letting a
library do all the dirty work of turning SQL query output into objects.
There's a good talk from this year's PyCon that shows SQLAlchemy's core
library and how it gives a nice closer to raw SQL interface to a database:
[https://www.youtube.com/watch?v=0PSdzUxRYpA](https://www.youtube.com/watch?v=0PSdzUxRYpA)

~~~
andybak
It's an interesting question but I want to make a meta point.

I came here to read people's thoughts on the article but the top comment
shifts the debate onto a wider topic.

This happens all the time on HN and is something I find slightly irritating if
I'm actually interested in the topic itself. It's like that person at a dinner
party who always changes the subject onto what THEY want to talk about.

I'm not blaming you - it's the fact that your post is the top one and the one
most commented on that derails the original topic - which is a group decision.

Oh well. Democracy in action I suppose.

~~~
rosser
So find, upvote, and participate in — or, if necessary, _create_ — a thread
that offers the kind of discussion you're hoping to see. Coming into a thread
that's not the discussion you wanted, and being all, "But _guys_! We should be
talking about _$other_thing_!" is an even more annoying dinner party guest,
IMO.

~~~
andybak
Yep. I was aware of that as I wrote the post. :)

In my defence - the $other_thing is the _actual_ article though.

It's also that there are some predictable topics. Every post about a new
Google product has someone bringing up the Reader shutdown and every post
about ORMs has someone talking about how much they prefer raw SQL...

------
scardine
A Python ORM that stands out of the pack is Pony:
[http://ponyorm.com/](http://ponyorm.com/)

~~~
smnrchrds
Agreed. It is the most Pythonic ORM by far. Unfortunately it will never be as
popular as its less restrictively licensed counterparts.

~~~
mercurial
An ORM which decompiles Python generator bytecode and turns it into SQL? Never
heard of it before, but that's precisely the kind of insanity I like.

------
andybak
With the addition of custom lookups and transforms in Django 1.7* I'd be
curious to know where it still falls behind other ORMs. I've only needed a
couple of bits of raw SQL across a dozen or more Django projects.

* [https://docs.djangoproject.com/en/dev/ref/models/custom-look...](https://docs.djangoproject.com/en/dev/ref/models/custom-lookups/)

~~~
hcarvalhoalves
I've been working with Django for 5 years. In my experience, Django's ORM is
pretty good for the common cases (specially when your query maps one-to-one to
a single model), but for anything that you need a bit more flexibility
(dealing with scalars, GROUP BY, a custom JOIN) you're on your own with raw
SQL, which gets unmaintainable quickly since you can't chain it with QuerySet
and suddenly you're not compatible with the rest of the codebase.

SQLAlchemy is more interesting in that it doesn't hide SQL away, just provides
an API for it. Underneath everything you're still dealing with strings (you
can just print most objects, like queries, columns, expressions, SQL
functions, etc), so you're less likely to be suddenly incompatible with the
rest of the API when doing something that deviates a little more from the
common cases.

------
rafekett
I had to use ActiveRecord last week after a year straight of using SQLalchemy
and it was downright painful. doing anything beyond simple CRUD is near-
impossible without writing raw SQL. i used to like ActiveRecord more when I
didn't understand relational databases, but now I don't understand how anyone
who's ever used SQL directly in their life could tolerate AR.

~~~
joevandyk
ActiveRecord supports lots of stuff through arel
([https://github.com/rails/arel](https://github.com/rails/arel)).

Are you sure you understood how to use the full functionality? What was
missing (besides CTEs)?

~~~
rafekett
maybe this has changed, but arel's documentation last time i used it was about
as good as the rails documentation, so it was wildly incomplete/inaccurate.

for example, last week I wanted to a bulk INSERT. maybe arel can do this
(though I highly suspect it cannot since this isn't a part of relational
algebra at all), but that's kind of worthless if i can't find any evidence of
how to do it without reading the arel source.

------
kbd
I'd love to see a comparison with non-Python ORMs. I've used Sequel for Ruby
and it's _wonderful_ but, even though I've used SQLAlchemy as well, I don't
have a sense of pros and cons of their respective general approaches.

------
rch
I use SQLAlchemy for just about everything won't suffer too much from the
expected ORM overhead. I've found it to be powerful, intuitive, and well
suited to larger projects.

Are there many other Python developers working directly with libpqxx though?
I've recently started exploring the idea of moving some of my relatively
stable, postgres-specific code into a dedicated library and would be grateful
for tips or words of caution. My hope is that this approach will wind up being
useful in cases where I'm coding against a large body of pre-existing stored
procedures.

------
wldlyinaccurate
I've used Doctrine 2 with PHP, and am yet to find another ORM like it in any
language. It stands out as unique (to me) in that you just write plain old
classes which don't need to know about the ORM. You write a separate schema
which Doctrine reads and then uses proxy objects for the mapping.

Am I the only person that finds this preferable to coupling your objects with
the ORM?

~~~
rch
It's been a while, but I enjoyed using Castor before other approaches became
so widely adopted in the Java world. Does it seem conceptually similar at
least?

[http://castor.codehaus.org](http://castor.codehaus.org)

~~~
wldlyinaccurate
From the very little I read of the documentation, it seems like a similar
concept -- mapping the objects to the data is done _outside_ of the objects.

------
gabordemooij
Too bad the idea of on-the-fly ORM never really caught on in the Python
community
([https://pypi.python.org/pypi/pybean/0.2.1](https://pypi.python.org/pypi/pybean/0.2.1)
not my project btw).

I believe this is a better way to approach the mismatch between the object
model and the relational model.

~~~
zzzeek
some people love this idea, and for many years we've had a product called
SQLSoup
([https://sqlsoup.readthedocs.org/en/latest/](https://sqlsoup.readthedocs.org/en/latest/))
which does exactly this on top of the SQLAlchemy ORM, and the docs for pybean
look quite similar.

However I don't have the resources to maintain this very old project right now
(it's only about 500 lines, anyone can pick up the source if they cared).

A more SQLA-centric version of this idea is recently released as the automap
extension
([http://docs.sqlalchemy.org/en/latest/orm/extensions/automap....](http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html))
which includes the "map everything on the fly" step plus relationship support,
and you then use traditional Query patterns with it. Reaction to it has been
mixed, depending on where the user is coming from.

~~~
Nullabillity
Slick ([http://slick.typesafe.com/](http://slick.typesafe.com/)) seems to
support this as well in a type-safe manner, but personally I'm not too keen on
having the compilation depend on having a stateful database available.

------
fideloper
No mention of Active Record vs Entity Mapping ORMs? Are there not many Entity
Mappers in existence? The only popular one I know of is Symfony's Doctrine (in
PHP land)

~~~
numbsafari
I think you'll find examples of each in the OP, and most of them follow the
Entity Mapper-style.

