3 ms·

So even trivial `select id, name from bar where baz = @foo`-style queries are hand-rolled?

mantrax5 12y ago
You can always have a function "roll" the query string for you, but the point is you pick the function and you handle its output, instead of making a metaquery to some engine, which calls your SQL server itself with something, but unclear what, unless it produces a log for you to inspect. 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.
- Jake232 12y ago
  Author of the article here (although not submitter). Pony actually has a way to log so that you can see every SQL query being ran against your DB. You can activate it with sql_debug(True). I monitored it for my first few Pony projects, although now I've just come to trust it will do the right thing. 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-queries http://doc.ponyorm.com/database.html#using-parameters-in-sql...
- marcosdumay 12y ago
  That does not fit any ORM I've looked at (and, sorry, Java people, I don't care to look at your tools anymore). 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.
kerkeslager 12y ago
Yeah, why not? What problem is an ORM actually solving in that case? 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.

You can always have a function "roll" the query string for you, but the point is you pick the function and you handle its output, instead of making a metaquery to some engine, which calls your SQL server itself with something, but unclear what, unless it produces a log for you to inspect. 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.

Jake232 12y ago
Author of the article here (although not submitter). Pony actually has a way to log so that you can see every SQL query being ran against your DB. You can activate it with sql_debug(True). I monitored it for my first few Pony projects, although now I've just come to trust it will do the right thing. 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-queries http://doc.ponyorm.com/database.html#using-parameters-in-sql...
marcosdumay 12y ago
That does not fit any ORM I've looked at (and, sorry, Java people, I don't care to look at your tools anymore). 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.

kerkeslager 12y ago

Yeah, why not? What problem is an ORM actually solving in that case? 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.