Well, ORMs are useless if you're of the opinion that string interpolation and concatenation are a good way of composing logical units. Many of us prefer a proper API for doing that, and good ORMs like SQLAlchemy provide us with one.

PDO in PHP has parameter binding and a way of loading a row from a resultset into a class. Safe, simple, and flexible. That's good enough for me. YMMV.

"foo > 123 or foo IS NULL and bar < 12"

Now write this in SQLAlchemy without strings, without concatenation, and let's see which provides you with more clarity.

(FooBar.foo > 123) | (FooBar.foo == None) & (FooBar.bar < 12)

No strings, and I get an actual expression object instance that I can, for example, store in a variable and reuse in any number of queries.

Yes! Plus if you have a syntax error Python will catch it for you. Peewee works the same way.

LINQ. Composable queries rock.

First of all, are you aware that LINQ is not only about database access?

Second, how is it a black box? It is the particular query provider implementation that is a black box.

Third, there's absolutely no correlation between the amount of LINQ and success of a project.

Absolutely. In my latest project, I just have a very thin facade between the application and the DB client library, just enough to simplify the process of getting a connection from the pool, fishing the required data out of a response, handling errors etc. The application doesn't build any queries dynamically; they're all string literals with parameters that get filled in by the DB client library.

I also wrote a little utility to handle migration. Each migration is a file with SQL in it; forward migrations only, no going back. The tool makes it easy to apply a given migration to a given database (dev, staging, production or whatever) and does the book-keeping to ensure they're only applied once. Couldn't be simpler.

I'm really happy with this set-up. It gives access to all the features of the database instead of just the lowest common denominator, and makes it much easier to read the code and know exactly what's going on.

The only times I've seen an ORM work well in a non-CRUD application is when it was designed and written from scratch to work with one database to supply exactly the operations and semantics that the application needs.

ORM is most supposed to save you from repeating the same load-db-put-data-into-objects over and over. It is also supposed to do caching and other similar optimizations for you.

If we insist on it, SQL itself was supposed to smooth over differences in db implementation. Ehm.

If all you need in one query, then figuring out how to make ORM running may be waste of time. If you need gazimilion of them and you need results cached reasonably and so on and so forth, ORM starts being very useful.

A SQL statement will return the results as plain data.

Data never goes out of fashion, it's easy to use, it's usually text, and it's usually returned in convenient lists.

If you want to select a gazmillion rows, you're probably doing something wrong if you're doing it with an ORM.

The overhead of storing all that metadata and the object structure overhead for each row would make it a poor choice for massive volumes.

Caching results from a database seems a bit pointless to me, since databases do that already anyway.

Rather figure out how to optimize your SQL so that you don't have to build a cache. The database is almost never the bottleneck with well-written SQL.

If texts and lists were easy to use, nobody would have invented structures, objects and the other thousands of abstractions available on modern languages.

An ORM will give you a description of the database you can use by reflection, and an abstraction layer that avoids using hard to use lists and tables. The first one is way more usefull than the later, but both are good things.

Also, application servers are normaly much more numerous than database servers, and much easier to scale up. Thus, anything that offloads work from the database to the application will simplify your environment when you grow.

Of course, there's things like SQLAlchemy Core which give you a lot of the syntax of an ORM for building dynamic, complex queries (no more gluing strings together when the shape of your query is dynamic), while returning proper cursors which return arrays and dictionaries.

He is not talking about fetching a gazillion rows but about writing a gazilkion similar-but-not-the-same queries!

In an indie game's development that I'm familiar with they use to use an orm or something similar but switched to manual SQL after the orm was generating way too many redundant statements. realtime mmorpg