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.

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

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.

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.

Yes, I am aware. How is this relevant at all to the context of this thread?

The rest of your comment: You are simply wrong. A heavy use of LINQ is one of the surest sign that one needs to run from a project. It is almost always used and embraced by people who have no concept of the consequences, imagining that the conciseness of LINQ = programming goodness, when the opposite is generally true.

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.

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.

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.