4 ms·

This is why I have a mild dislike for ORMs. They're pretty handy, but most of the time, I prefer to hand-make SQL statements. Are they more work? Yes. But no OR

by adtac 8y ago

This is why I have a mild dislike for ORMs. They're pretty handy, but most of the time, I prefer to hand-make SQL statements. Are they more work? Yes. But no ORM will ever give me the actual flexibility and performance of raw SQL queries (unless it becomes SQL itself).

GrayShade 8y ago
I'm fine with "thin" ORMs that map rows to objects, and not much more. You can just write SQL (or use a view) while avoiding some repetitive code.
dimgl 8y ago
In a shop I used to work at all of the devs were looking into Entity Framework as an abstraction layer for access to an MSSQL database. They were using T4 generators to create classes based off of the tables they were interested in (it was hundreds) and so they were looking into unit of work/repository patterns. I told them I had plenty of experience going down this route (my most popular StackOverflow question is on UOW/repo) and to just use Dapper and not look back. There is a pleasant simplicity in using plain old C# classes and simple queries that you can visualize just by looking at them without having to run a trace to figure out what's actually being executed. This was one battle of many. I lost that battle.
- GrayShade 8y ago
  You'll love this: https://www.earthli.com/news/view_article.php?id=2288 https://www.earthli.com/news/view_article.php?id=2288 > This was one battle of many. I lost that battle. Have a hug :-).
  - spencercw 8y ago
    We use Hibernate at work and I've seen it generate some pretty gnarly queries (like, tens of kilobytes of just SQL). People like to rag on Oracle, but it's kind of impressive that it stands up to such abuse without complaining.
nightski 8y ago
If you take an inexperienced dev and have them hand write SQL it is not guaranteed to be very good either. Either way with ORM or SQL you have to understand the underlying database technology. The problem is lack of skills not tech.
- citrablue 8y ago
  I agree, and also think the tools we use shape us (and vice-versa). It may be a tradeoff between lower standards (e.g. "Can you use an ORM?") and steeper learning curve before someone can produce useful work. The former optimizes for more bodies that meet a certain criteria, the latter may optimize for a higher skill level on average. There are many other factors that contribute, but it's interesting to think about. (I know, for example, the Navy SEALs lowered their standards a ways back to increase enrollment... and some friends have said it's had a negative impact on the teams, because they have taken some people with behavioral profiles that would never have made it in the past. But they had more missions due to changing nature of warfare, and needed the numbers.)
  - nightski 8y ago
    Maybe it's perception. I don't view ORM as a tool to make working with a database easier. In fact, it's harder to use but can have real advantages. For example, it would be nice to use raw SQL in our app but we have lots of reporting and data analysis functionality with support for custom grouping, filtering, etc... Composing queries on the fly with an ORM is trivial. On the other hand composing SQL strings or writing out manually the thousands of potential combinations would be very painful and brittle. I also find ORMs are great for transactional updates where you are only modifying a few entities at a time. They really reduce the amount of boilerplate required (reducing errors) and allow you to use refactoring tools (find usages, etc..) to locate where a particular column is used in the code base. So to me it's not about making SQL easier - because lets face it you have a complex translation layer there that requires full understanding. Rather it's about capturing those clear benefits.
    - citrablue 8y ago
freedomben 8y ago
I agree. I've moved toward raw SQL in almost every app I build. That said, ORMs are far from equal. Some are much better and closer to SQL than others. I've been impressed with ActiveRecord in that regard, and also Ecto
- dimgl 8y ago
  Elixir's Ecto is probably the most impressive ORM I've ever used. It absolutely blew my mind when I used it.
core-questions 8y ago
I wrote an ORM once, back in the mid-2000s. One of my principles was that when I know a damn good SQL query that will get me what I want, the ORM better not get in the way. The result? My query generating classes would happily take in individual parts of an SQL statement, or a whole query, handle it through the same database abstractions as a generated one, and give me back workable objects as best as possible. This came as a result of seeing what miserable performance I got handling joins as application logic, recursing through things one at a time when the correct query would bring me back every piece of information I needed at once. I still use some of that old code from time to time, mostly because I miss how well it meshed with the way I think about things. Sometimes writing your own, if you've got the time, is a nice way to become truly productive and comfortable.
mdpopescu 8y ago
Not a bad point. Then again, once you hit your first 1200-line stored procedure, full of logical bugs including SQL injections, you'll realize SQL isn't that amazing either :)
- dimgl 8y ago
  If your stored procedure has 1200 lines, you're doing something wrong.
  - DisposableMike 8y ago
    How about recreating a report to feed into a legacy system from an ERP installation with 5,000 tables and 12,000 stored procedures - none of which you're allowed to modify or face losing support?
- adtac 8y ago
  I also try my best to avoid SQL procedures as much as possible (except for database migrations; no avoiding them there). The 1200 line procedure should definitely be written in a real programming language, but what's done is done, I guess haha.
jrs95 8y ago
Funny enough, I had gone from ORM to SQL a couple years ago and now I've just recently gone back to ORM. For simple projects, raw SQL can be quite good. But once your model is complex enough that you're doing 3+ way joins relatively often, mapping the results manually by hand is very time consuming, and building an abstraction is really building the beginnings of your own crappy ORM.
- GrayShade 8y ago
  ORMs do so much more than mapping. How about defining views and using those with your ORM?
mateo411 8y ago
I prefer the control of writing SQL too. However, you have to be careful of SQL injection attacks. Honestly, if you are developing a web app, when you have it working, part of the routine should be to look at the SQL queries for each operation and spot any N+1 issues. I use Django, so this is easy to do in Django Debug toolbar. I'm sure similar tooling exists in other frameworks as well.
- isostatic 8y ago
  > you have to be careful of SQL injection attacks. Surely you're using prepared statements?!
  - mateo411 8y ago
    I've string interpolated SQL queries before... I know I'll only do this for internal apps, not for anything on the public internet. This is not a good practice.
blattimwind 8y ago
> But no ORM will ever give me the actual flexibility and performance of raw SQL queries (unless it becomes SQL itself). Enter SQLAlchemy Expressions. The reason why that's actually an excellent idea is twofold: For one, the entity-mapping part (i.e. you can write very complex queries and still get your mapped objects back), the other is that you're essentially writing the AST of your SQL queries using real objects, so it's very easy to manipulate and reuse, which is quite hard to do with textual SQL. (Also it's easy to mix textual SQL and SQL expressions in SQLAlchemy; but the need to do that is very, very rare indeed).

by adtac 8y ago

GrayShade 8y ago

I'm fine with "thin" ORMs that map rows to objects, and not much more. You can just write SQL (or use a view) while avoiding some repetitive code.

dimgl 8y ago

In a shop I used to work at all of the devs were looking into Entity Framework as an abstraction layer for access to an MSSQL database. They were using T4 generators to create classes based off of the tables they were interested in (it was hundreds) and so they were looking into unit of work/repository patterns. I told them I had plenty of experience going down this route (my most popular StackOverflow question is on UOW/repo) and to just use Dapper and not look back. There is a pleasant simplicity in using plain old C# classes and simple queries that you can visualize just by looking at them without having to run a trace to figure out what's actually being executed. This was one battle of many. I lost that battle.

GrayShade 8y ago
You'll love this: https://www.earthli.com/news/view_article.php?id=2288 https://www.earthli.com/news/view_article.php?id=2288 > This was one battle of many. I lost that battle. Have a hug :-).
- spencercw 8y ago
  We use Hibernate at work and I've seen it generate some pretty gnarly queries (like, tens of kilobytes of just SQL). People like to rag on Oracle, but it's kind of impressive that it stands up to such abuse without complaining.

nightski 8y ago

If you take an inexperienced dev and have them hand write SQL it is not guaranteed to be very good either. Either way with ORM or SQL you have to understand the underlying database technology. The problem is lack of skills not tech.

citrablue 8y ago
I agree, and also think the tools we use shape us (and vice-versa). It may be a tradeoff between lower standards (e.g. "Can you use an ORM?") and steeper learning curve before someone can produce useful work. The former optimizes for more bodies that meet a certain criteria, the latter may optimize for a higher skill level on average. There are many other factors that contribute, but it's interesting to think about. (I know, for example, the Navy SEALs lowered their standards a ways back to increase enrollment... and some friends have said it's had a negative impact on the teams, because they have taken some people with behavioral profiles that would never have made it in the past. But they had more missions due to changing nature of warfare, and needed the numbers.)
- nightski 8y ago
  Maybe it's perception. I don't view ORM as a tool to make working with a database easier. In fact, it's harder to use but can have real advantages. For example, it would be nice to use raw SQL in our app but we have lots of reporting and data analysis functionality with support for custom grouping, filtering, etc... Composing queries on the fly with an ORM is trivial. On the other hand composing SQL strings or writing out manually the thousands of potential combinations would be very painful and brittle. I also find ORMs are great for transactional updates where you are only modifying a few entities at a time. They really reduce the amount of boilerplate required (reducing errors) and allow you to use refactoring tools (find usages, etc..) to locate where a particular column is used in the code base. So to me it's not about making SQL easier - because lets face it you have a complex translation layer there that requires full understanding. Rather it's about capturing those clear benefits.
  - citrablue 8y ago

freedomben 8y ago

I agree. I've moved toward raw SQL in almost every app I build. That said, ORMs are far from equal. Some are much better and closer to SQL than others. I've been impressed with ActiveRecord in that regard, and also Ecto

dimgl 8y ago
Elixir's Ecto is probably the most impressive ORM I've ever used. It absolutely blew my mind when I used it.

core-questions 8y ago

I wrote an ORM once, back in the mid-2000s. One of my principles was that when I know a damn good SQL query that will get me what I want, the ORM better not get in the way. The result? My query generating classes would happily take in individual parts of an SQL statement, or a whole query, handle it through the same database abstractions as a generated one, and give me back workable objects as best as possible. This came as a result of seeing what miserable performance I got handling joins as application logic, recursing through things one at a time when the correct query would bring me back every piece of information I needed at once. I still use some of that old code from time to time, mostly because I miss how well it meshed with the way I think about things. Sometimes writing your own, if you've got the time, is a nice way to become truly productive and comfortable.

mdpopescu 8y ago

Not a bad point. Then again, once you hit your first 1200-line stored procedure, full of logical bugs including SQL injections, you'll realize SQL isn't that amazing either :)

dimgl 8y ago
If your stored procedure has 1200 lines, you're doing something wrong.
- DisposableMike 8y ago
  How about recreating a report to feed into a legacy system from an ERP installation with 5,000 tables and 12,000 stored procedures - none of which you're allowed to modify or face losing support?
adtac 8y ago
I also try my best to avoid SQL procedures as much as possible (except for database migrations; no avoiding them there). The 1200 line procedure should definitely be written in a real programming language, but what's done is done, I guess haha.

jrs95 8y ago

Funny enough, I had gone from ORM to SQL a couple years ago and now I've just recently gone back to ORM. For simple projects, raw SQL can be quite good. But once your model is complex enough that you're doing 3+ way joins relatively often, mapping the results manually by hand is very time consuming, and building an abstraction is really building the beginnings of your own crappy ORM.

GrayShade 8y ago
ORMs do so much more than mapping. How about defining views and using those with your ORM?

mateo411 8y ago

I prefer the control of writing SQL too. However, you have to be careful of SQL injection attacks. Honestly, if you are developing a web app, when you have it working, part of the routine should be to look at the SQL queries for each operation and spot any N+1 issues. I use Django, so this is easy to do in Django Debug toolbar. I'm sure similar tooling exists in other frameworks as well.

isostatic 8y ago
> you have to be careful of SQL injection attacks. Surely you're using prepared statements?!
- mateo411 8y ago
  I've string interpolated SQL queries before... I know I'll only do this for internal apps, not for anything on the public internet. This is not a good practice.

blattimwind 8y ago

> But no ORM will ever give me the actual flexibility and performance of raw SQL queries (unless it becomes SQL itself). Enter SQLAlchemy Expressions. The reason why that's actually an excellent idea is twofold: For one, the entity-mapping part (i.e. you can write very complex queries and still get your mapped objects back), the other is that you're essentially writing the AST of your SQL queries using real objects, so it's very easy to manipulate and reuse, which is quite hard to do with textual SQL. (Also it's easy to mix textual SQL and SQL expressions in SQLAlchemy; but the need to do that is very, very rare indeed).