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.

You'll love this: https://www.earthli.com/news/view_article.php?id=2288

> This was one battle of many. I lost that battle.

Have a hug :-).

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.

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.)

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.

The day-to-day is certainly up for debate -- I prefer SQL myself. But imagine the initial learning curve for someone fairly new to programming. Persisting data to, and reading data from, a "database" is a lot easier in code than learning SQL (which frequently use a client-server system, only further increasing the surface area of knowledge required to produce working results).

The difficulty required to think about a solution increases significantly with additional components.

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.

> you have to be careful of SQL injection attacks.

Surely you're using prepared statements?!

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.

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

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

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.

> 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).

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.

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.

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

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 :)

If your stored procedure has 1200 lines, you're doing something wrong.

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?

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.