ORMs are a massive benefit to productivity and security for about 80% of your database access. The problem is that people expect to use them for 100% of their database access. For that last 20%, they just get in the way and cause more problems than they solve.

> For that last 20%, they just get in the way and cause more problems than they solve.

If I had a nickel for every project I worked on that started using an ORM but eventually was forced to write actual SQL to cover those use cases the ORM just couldn't I'd be rich. In fact it's the number 1 reason I don't even bother with ORMs anymore.

If I abstract my data access to a set of APIs then all I have to do is reimplement the SQL or whatever behind each API when I need to move to a new storage system which usually ends up not being very difficult compared to the headaches of trying to get an ORM to cover that last 20%.

While I do write SQL during exploration, for the purposes of production systems I've never had a query that I haven't been able to represent using SQLAlchemy. The reason for this is that in addition to being an ORM, it exposes an object-oriented general SQL abstraction layer.

> For that last 20%, they just get in the way and cause more problems than they solve.

This is why my preferred measure of an ORM is how effectively one can write your own SQL with it, and what you get out from your database call when you do so (do you still get "real" business objects or just key-value pairs?)

That said, I've found you can trick even a rather recalcitrant framework into making sophisticated queries by setting up a database view and tell your ORM that the view is just another table...

On which world?

We still do them by hand, as ORMs don't match the performance of the data that we work with.

Plus it is dumb to have data cross the wire for DB operations that can happily stay on the server.

I always enjoy showing some devs how well written SQL outperforms their beloved ORM.

> WELL WRITTEN SQL outperforms their beloved ORM

"Well, that's the real trick, isn't it?" </Han Solo>

The only time I've had a problem with SQL injection is with a PHP dev who just didn't get the whole issue. Mind you, he didn't get a lot of things.

It's one of those things you generally learn once and never have a problem with again.

Though I'm glad ORMs caught on too, preventing injections is covered in almost newbie tutorial when dealing with SQL.

Yes, ORMs let newbies code queries safely.

However you can deal with SQL safely bypassing the ORM if you compose the queries correctly. An example with Ruby and the pg driver for PostgreSQL:

    db.prepare("select_title", "select title from posts where author = $1::text")
    ...
    db.exec_prepared("select_title", [ author ]) do |result|
      ...
    end

I concede that it's more prone to unsafe coding than Post.where(author: author).pluck(:title) Everybody in the team must resist the temptation to ever write a db.exec("select title from posts where author = #{author}")

What I do is using the ORM all the times, except for complex queries that would be a nightmare to code with ActiveRecord/Arel and to understand when written in Ruby. In those case I use ActiveRecord's find_by_sql. It can be protected by SQL injections easily. I quote the documentation:

    Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date]

Again, it has the same problem of having to enforce the discipline of passing the arguments in the right way but there are countless ways of making a project unsafe and the team must be trained to prevent unsafe coding practices. SQL is only one of them.

Intro tutorials will cover lots of ways to prevent common security issues.

Hard experience has taught that programmers still won't adopt those approaches in significant numbers unless their tools are forcing it on them by default.