5 ms·
I've largely abandoned ORMs that generate SQL queries for me. A mapping layer that takes a SQL result and stuffs it into an object (DTO) is useful, but anything
by gregmac 9y ago
I've largely abandoned ORMs that generate SQL queries for me. A mapping layer that takes a SQL result and stuffs it into an object (DTO) is useful, but anything beyond that is more trouble than it's worth.
For complex SQL, it's better to write it by hand. You get to tune it and ensure the indexing is correct (either the query usage, or create/modify your indexes), and you get to see exactly what it's doing.
For even simple conditional joins, there's something nice about knowing exactly what's going on, and knowing it's not going to be doing something that results in a nested join loop (eg: the type of slow query you don't notice until you're using it in production and performance suddenly drops).
I'm a big fan of Dapper for this, so a lot of my data layer code looks like:
public IEnumerable<Order> LoadOrders(int customerId)
{
using (var db = GetConnection())
{
return db.Query<Order>("SELECT * FROM orders WHERE customerId = @customerId", new { customerId });
}
}
The one exception I make to ORM-generated code is INSERT/UPDATE queries. For most cases, the ORM doesn't have much it can screw up, and since it's essentially just mapping code ("Name = @Name, Address1 = @Address1, etc") it's more likely I'll make a typo or copy/paste error than anything else.
I've spent too many hours debugging crappy ORM queries, and I find it generally takes twice as long (with quadruple the frustration) to get the ORM to generate the complex SQL you want it to vs just writing the SQL.
- senorjazz 9y agoI fully agree. For simple things ORMs can be useful (very simple) anything else they get in the way. Firstly, have to write out the SQL, then convert it to the ORM format, only to find the ORM doesn't support something you are using, so you rewrite the query in a more long winded way, then convert it to the ORM.
- gregmac 9y ago> Firstly, have to write out the SQL, then convert it to the ORM format Ha, you know I had actually forgotten about that, but I have done that many times. You model the data, write some queries to essentially test out the structure/data/concepts, then instead of pasting the queries into your code, you spend another bunch of time trying to tell your ORM how to come up with the query you pretty much already have. Even if you're using a very well-tested, robust ORM that flawlessly handles your use case and you're an absolute expert at using it, it's still more work than just pasting a query you already have.
- flukus 9y ago> Firstly, have to write out the SQL, then convert it to the ORM format, only to find the ORM doesn't support something you are using, so you rewrite the query in a more long winded way, then convert it to the ORM. Or you could avoid the conversion step entirely. If the ORM is useful 90% of the time and in the way 10% of the time then use different tools for the 10%.
- ReidZB 9y agoAt my workplace, we've started using jOOQ for this sort of thing. https://www.jooq.org/ https://www.jooq.org/ (albeit we're a Java shop, not a C# shop) It still sort of generates SQL for you, but really you end up writing the SQL yourself except in a type-safe way.
- overcast 9y agoAll I want is a modeling layer, and callbacks, like thinky.io provides for RethinkDB. It's very rare when any query I'm writing can just be generated by one of these ORM packages. It just becomes more confusing, and less powerful.
- electrum 9y agoThat looks very similar to JDBI for Java: http://jdbi.org/ http://jdbi.org/
- cryptonector 9y agoEven for data modification statements, if you can use TRIGGERs, CHECK and other constraints, and/or stored procedures / functions to implement business logic, then you'll be better off doing that than using an ORM.
- swalsh 9y agoThis is funny to read, I've recently started giving up on ORM's, but I felt really guilty about it. Like I have to really justify myself. Like you, I use it in a hybrid manner, simple queries I still use it... but for complex selects, I have yet to meet an ORM that can dish it out as good as I can.
- tracker1 9y agoThis is where I really think scripted languages can shine... I find that using SQL in JS you really don't need an ORM, except maybe to shape your response for the purpose of an API interface... even then it's still better than the boilerplate that ORMs bring into the mix more often than not.