I don't want magic on my database layer, because when it backfires, and it will, it might ruin the most precious thing that my application relies on.

I absolutely do not want a lack of understanding on my part to cause permanent damage to my data (already got burned by that). And since ORMs are usually complex beasts, I certainly don't want the burden of learning every single use case before even deciding how to model the thing.

The level of complexity does not balance the value added. It does not hide SQL, because you WILL have to learn both the framework and SQL to debug, and debug you will, often. It does not, in my opinion, increase productivity since I will lose many an hour figuring things out when things do not work as expected, and they won't. Simple bugs and performance issues become a great burden.

Simply put, I don't won't to forfeit control of the most sensitive part of the system, the data. Everything that goes on with data persistence should be really clear and simple, no magic.

Anything that isn't CRUD or a simple aggregation. Real world applications tend to have: oddball joins, subqueries, window functions, case statements and all other sorts of crazy once you get into the value-add parts of the system. What I really need is a clever way to project the value or set into a structure that I can easily process or convert into UI elements.

On the subject of CRUD, I would be really impressed with an ORM that would detect my constraints and enforce them in the application layer. I am a big fan of DRY, but I am a bigger fan of bulletproof relational models that prevent bad data from ever getting to the system. So rather than declaring these sorts of things in the app code, where they won't be enforced in things like stored procs or ad hoc SQL, I'd much rather have the database be the one source of the data constraints. For really complicated things (e.g. validation triggers), I'd want a way to communicate a violation back up to the ORM layer.

Not sure if this is what you wanted, but for some reason I felt compelled to brain dump what I have been thinking on the subject as of late.

Yes, an ORM saves you the time of writing out the code to map the fields on the row to the fields on the object. But it doesn't do that for free. Let's say the results of your query are, shortly thereafter, looped over and one property is checked. Well, in NHibernate, that property is lazy-loaded by default, so now that you're touching it, it generates an individual select query for every element in the list. That's simple enough to fix: change that property to not be lazily loaded. Now suddenly a completely different part of the system is getting an OutOfMemoryException because you're querying over an unfiltered dataset and now it's loading a huge table into memory.

Your trivial query in SQL is not a trivial query in an ORM. Let's keep trivial problems trivial.

There are tools that do this better, by essentially providing a domain-specific language that generates SQL queries (LINQ-to-SQL, parts of sqlalchemy, and, it looks like, PonyORM). These certainly result in more aesthetically pleasing code, but you're still essentially writing SQL, except that you don't have access to the entire SQL implementation. You want to remove constraints, apply an update, and then add the constraints back? Good luck doing that with anything other than SQL. This probably won't hurt you for trivial queries, but having the ORM in your code means that it's only a matter of time before someone on your team starts doing something non-trivial with it, and then the beast will raise its ugly head. And besides, while you can be careful with these DSL-style ORMs and they won't hurt you, they won't help you that much either. Typing a little more isn't the bottleneck.

In the latter case it's also unclear when your queries run in a transaction, which isolation level the transaction is, heck, in many ORM you barely know when the server gets called at all (it's usually not when you expect it's called), nor you have any guarantees about the order in which your updates are fed to the server.

You also have no control to supply direct SQL when you need to as you have no direct access to the connection, or the ORM relies on caching where direct access would cause inconsistent data.

The difference between an ORM, and a simple query generator under your control seems like a subtle detail, but as the wise ones said, the devil's always in the details.

You also can supply direct SQL to the DB via Pony, the documentation covers this: http://doc.ponyorm.com/database.html#using-parameters-in-sql...

ORMs normaly default to either "in transaction" or "not in transaction", and require explicit code to change that. They guarantee the order of commands that change data, and let you get into the database any time you want.

The only characteristic that fits my experience is that you don't know when SELECTs are executed. And, if this is a problem for you, you are doing something very strange.