PostgreSQL is great and ORMs suck

(written by lawrence krubner, however indented passages are often quotes). You can contact lawrence at: lawrence@krubner.com

Over the last 10 years I think I’ve heard all the arguments for Database Abstractions Layers (DBAL) and I’ve come to hate them. I’ve twice seen data moved from one database to another (from Oracle to MySQL and from MySql to PostGres) but in all cases I was able to write the import/export script in a day or two, so DBAL was never needed. More so, DBAL means giving up on what makes a database unique, and so you throw away all the special features of your chosen database as soon as you use a DBAL. And, more so, DBALs may have seemed like a reasonable idea when all database were SQL, but now we have an explosion of new data storage technologies, and they are incompatible, so the idea that using DBAL will protect you from change is ridiculous. If you decide to move from MySql to MongoDB, no DBAL will help you.

But if we throw out DBALS, then ORMs become simpler. If we are using an object language, getting objects from the database can be useful, though ORMs will enforce contracts on your objects when you try to save to the database, and you may not want to enforce contracts at that point.

The key point is, ORMs force you to make sacrifices:

My CTO wrote custom PostgreSQL functions to generate all the JSON that our API returns. First time I saw it, I said “Holy crap, that query is two pages long. You could just tell Rails to serialize the object in 3 lines of code.” He replied “I know. That’s how we did it at first. But generating the JSON directly in postgres is about 700x faster.”

Eventually we even moved a very complex scoring algorithm from an overnight Hadoop Java process, into Postgres as a compiled extension in C. We used to spend all night calculating those scoring results and caching them in an enormous table. But with the Postgres+C solution, we could calculate and store it in realtime by having Postgres do the hard part.

The database software we use has amazing powers, and we throw all that away when we use DBALs or ORMs:

One thing that drives me absolutely over the cliff is how ORMs try so hard (and fail) to abstract the power and expressiveness of SQL. Before I write further let me say that Frans Bouma reminded me yesterday there’s a difference between ORMs and the people that use them. They’re just tools (the ORMs) – and I agree with that in the same way I agree that crappy fast food doesn’t make people fat – it’s the people that eat too much of it.

Instead of ripping ORMs apart again – I’d like to be positive and tell you just why I have stopped using their whack-ass OO abstraction on top of my databases :). In short: it’s because SQL can expertly help you express the value of your application in terms of the data. That’s really the only way you’re going to know whether your app is any good: by the data it generates.

Source