What ORMs have taught me: just learn SQL (2014) (woz.posthaven.com)

Agree so much on this, efficient stored procedures are a godsend when refactoring applications and are excellent at backup authorization checks.

No

query builders are the sweet spot.

A lot of the reasons listed in this article actually made me shift from sophisticated ORMs like Hibernate back to a query based approach. A really nice framework for this (in Java) is jOOQ which gives you the possibility to write typesafe SQL via code generation. https://www.jooq.org/

(I'm not at all affiliated with jOOQ - just a happy user)

If you have not read The Vietnam of Computer Science[1] I highly suggest it.

http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...

I have to agree. ORMs can be great when an application is just starting, because that's when you're writing the most tedious queries and statements, but beyond that I personally find that ORMs just get in the way.

As soon as I need to write something more complicated than select-by-id I end up reaching straight for SQL. Otherwise I have to learn both the ORM's query API or DSL and have the proper mental model for how it translates to actual SQL.

Or I could just write SQL and be done with it. No mysteries.

Maybe this is just me, but I've never written a join in an ORM that I had the slightest bit of confidence in.

He says he likes SQLAlchemy, but doesn't say why. I'm interested to know that though.

To me, the big win with SQLAlchemy is that it separates the SQL expression layer from the ORM layer so cleanly. I can write very complicated queries with the expression layer that wouldn't be possible at the ORM layer, and do it in a much more composable way than concatenating SQL strings. Example: http://btubbs.com/postgres-search-with-facets-and-location-a...

I prefer to use SQL directly as well.

But some points to be made in favor of ORMs (some of them, anyway):

* Multiple backend support to handle different SQL engines.

* Minimized risk of accidental injection.

* Migrations.

LOL! I got fired from a project for making this argument. OMG, just get your data from sql statements or some sort of cache and be done with it...wtf.

> ORMs, however, encourage this use and often make writing precise projections as tedious as they are in SQL.

"As tedious as SQL" is not an argument to use SQL instead.

Why are we even having this debate? There are some ORMs that bring so much value to the table that you'd be stupid not to use them. Example being Django's ORM or SqlAlchemy.

Also be specific in what ORM you are comparing to raw SQL. Are you talking about Hibernate or SQLAlchemy. Are you talking about a query builder?

Good ORMs help tremendously with maintainability and security. They also let you drop down to raw SQL when needed.

I don't think rails would have been as popular if you had to use SQL.

I think saying "Just use SQL" is probably a bad idea. You'll most likely end up implementing an ORM anyway, or you will end up with your model code mixed up everywhere with your views.

I do think a lot of people use ORMs as a crutch, which sucks. Also, ORMs often provide too much abstraction, forcing people who actually know SQL to relearn how to do everything the way the ORM happens to like it. I should not have to learn twice as much to be productive due to an abstraction.

What I prefer are really lightweight ORMs that give me models which I can then enhance with custom code. I don't need an ORM that supports plugins or inheritance or a dozen different kinds of joins. All of that can be done more efficiently with custom code.

Also, I think SQL builders are really useful. I think a lot of people conflate SQL builders with ORMs but they're actually very different problems.

I don't hear people complaining about say LINQ. Maybe it's just that your language and/or ORM or integration thereof suck?

  var customers = Customers.Include(c => c.Orders).Single(c => c.CustomerNo == '1234')

I'd rather learn the ins and outs, problems and issues, highs and lows, of SQL rather than an ORM.

ORM require just as much investment in time and even then you still need to learn the sql to get the ORM to do what you want it to do.

SQLAlchemy on Python is a truly fine piece of software but in the end it was much simpler and felt more powerful for me to write the SQL. And not even hard BTW.

I only have a limited amount of time available for learning and if I can trim out an entire class of technology (i.e. the ORM) then that's a whole bunch of stuff I just don't need to spend time learning.

Object-relational mapping is in many cases an excessively leaky abstraction. I try keep away from it.

A DSL for writing SQL in a nice, composable way is a useful thing.

Some libraries, like SQLAlchemy, provide both levels, not insisting on using the object mapper.

It depends on what you're after.

I'm very comfortable in SQL, and would prefer to write my queries. But our team has grown, and we've found that developers say they know SQL, but they really don't. In general, I've found that it's not the syntax that messes people up. There's a significant mental "jump" between the usual procedural coding paradigm and the "set based" paradigm offered by SQL. Some people just never catch on.

So we're going to start using an ORM (Entity Framework Core 2) so that the "mere mortal" developers can pitch in. I know there's a way to run raw SQL, so I know that when we find spots where the ORM fails, we can just rewrite it with some good SQL if we decide that's best.

But maybe that'll never happen? We've been trying to do simpler SQL stuff as of late so that the heavier lifting in the system is done by the application/client instead of the database (bottleneck). As the database sees simpler, less unique queries, more stay in the plan cache, indexes are more reliably hit as expected, and performance increases.

I've found ORMs (such as Entity Framework) great for operations that can be described as "find a single thing by PK and update it." For read operations I've favored this strategy: "Imagine the ideal result set for the task at hand. Use SQL to deliver that result set. Do the rest of the work in your app language of choice."

Edit: I guess I should clarify that I would favor using any library that maps result sets to lists of objects. And I would consider that to be part of "do the rest of the work in your app language of choice."

I do agree every one who needs to get data from an SQL database should know SQL, and mostly, should know about indexes and how and why queries can be slow.

But a query builder is extremely useful and in any complex application, if you don't use one, you end up bulding one yourself, which may not be a very good idea if you don't understand things like query injection.

So learn SQL, learn ORM, and choose in a case by case basis.

I guess my experience is limited, but I’ve not seen much of this despite working in an ORM environment with around 75 entities for the last few years (my recent experience anyway, the rest goes back 16 years). Maybe that is small potatoes, I don’t know, but I’ve found that anyone who understands JPA well enough can work to avoid any pitfalls of using ORM. It seems to me that having a good mix of understanding SQL and ORM is a good thing; and especially understanding exactly what the ORM system is doing for you and how it is doing it. Dropping ORM altogether sounds like a bad idea since it provides a number of built-in security features as well as an abstract modeling paradigm that is fairly easy to conceive and maintain; provided, of course, that you learn to say “No” to protect the integrity of the model (such as rejecting the attribute creep the article warns about).

I have found, in my experience, that people who tend to want to write SQL over ORM usually want to do so because they simply know SQL better. That’s okay, there is nothing wrong with that. But that doesn’t immediately mean ORM systems are bad. No need to be tribal about it.

The problem I see is that many new software developers these days sometimes can’t see the forest for the trees because they dwell too much on what they think is better instead of simply seeing the software and abstractions as nothing more than tools in the tool belt. It happens everywhere — PC vs Mac, iOS vs Android, Scala vs Java, SQL vs ORM. It’s fine to have opinions, I have many, but as I’ve aged I’ve become acutely aware that my biases are almost solely rooted in the limitations of my understanding.

"What ORMs have taught me: just learn SQL"

Could be retitled:

"I should have learned SQL before dealing with ORMs."

Every single point the author brings up comes seems to come down to simple database design, lazy development, or not understanding their tools. They really don't seem to have anything to with ORMs or query languages.

Any screwdriver can make for a bad hammer and some screws may go in with a large enough mallet.

> Perhaps the most subversive issue I've had with ORMs is "attribute creep" or "wide tables"

Normalization of data is required whether you're using a query language or an ORM to access it. The fact that ORMs make it easy to "hide" the fact that you've added 500 columns to a table isn't the ORM's fault.

> Knowing how to write SQL becomes even more important when you attempt to actually write queries using an ORM. This is especially important when efficiency is a concern.

What do they think the ORMs are doing? Magical incantations over the disks? The ORMs are just using queries too. You can write really horrifically bad queries in a query language and also abuse ORMs, but that doesn't make either one bad. Most ORMs can let you see precisely the SQL they are creating. If not, the database will surely log the queries for you and let you know what's going on.

> The problem is that you end up having a data definition in two places: the database and your application.

Welcome to the fact that we have multi-layered technology? There's always going to be discrepancies between the layers that have to be ironed out because no data designs are perfect or future-proof. The author then attempts to bring migrations into the picture as if database migrations are somehow just not a problem if you aren't using ORMs (hint: database migrations have always been tough even in very well-design systems).

> Dealing with entity identities is one of those things that you have to keep in mind at all times when working with ORMs, forcing you to write for two systems while only have the expressivity of one. What this results in is having to manipulate the ORM to get a database identifier by manually flushing the cache or doing a partial commit to get the actual database identifier.

Sounds like a pretty frustrating example, but I've worked with at least 10 different ORMs I can think of off of the top of my head and not a single of them required "manually flushing a cache" or a "partial commit" to "get the actual database identifier." I wouldn't write this up as being an issue with ORMs or that this problem would be magically fixed by only writing SQL either.

> Transactions. Something that Neward alludes to is the need for developers to handle transactions. Transactions are dynamically scoped, which is a powerful but mostly neglected concept in programming languages due to the confusion they cause if overused.

Transactions are pretty straightforward and I cannot agree with: "The concept of a transaction translates poorly to applications due to their reliance on context based on time."

Transactions don't care about time at all. They care about order and making sure that things are completed in a certain series of steps. This actually translates very well to applications, especially when you have processes that take a long time, where you don't want something to happen unless another thing happens first.

While a decently-written article, this comes across as someone who learned about ORMs more deeply than databases, discovered the flaws that ORMs have, and decided that query languages must be the only way forward.

This ignores the fact that we created and adopted ORMs after struggling through years of rigid queries smattered throughout code.

Writing bare queries has a time and a place, but ORMs have saved countless hours of development time, and allowed for vastl...

Ah yes - the proverbial "ORMs are bad, just learn SQL" post. This is analogous to saying "don't use frameworks". Sound ridiculous? Yes, yes it is.

The law of leaky abstractions applies to many, many things, ORMs included. I would also argue they apply in different degrees, usually related to the design of the ORM (the post mentions SQLAlchemy vs. Hibernate, for example).

But consider the following:

1) Why do people still use ORMs? Exactly.

2) Question 1 but s/ORM/framework_or_widely-used-library

3) ORMs allow you to develop faster, and deliver value

4) Beginners already have a hard time coding, designing, and understanding what they're doing. ORMs provide a nice abstraction over underlying data stores

5) Although fraught with peril, ORMs provide a common interface that'd give _some_ help if you switch data stores

6) Multi-line SQL statements are a huge pain in some languages

> If you're using an RDBMS, bite the bullet and learn SQL.

If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks, providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL, so you'll end up inventing that part yourself without an ORM (I recommend doing so, on a less critical project, to learn the kinds of issues that present themselves). None of those things should be about "hiding SQL", and you need to learn SQL first before you work with an ORM.

  postgres=# SELECT * FROM domains WHERE id = 'foo';
  ERROR:  invalid input syntax for integer: "foo"
  LINE 1: SELECT * FROM domains WHERE id = 'foo';
                                           ^

An ORM is not a "query builder"

An ORM turns your result into a collection of objects. THAT is what an ORM is for and about.

The fact that they are built on top of query builders and lighten the load when developing is just a handy side effect.

This topic has been done to death and the rate at which comments have been made since this was posted tells me that we either haven’t learned much of anything as a collective, or we just like to rehash the same talking points because... we can.

Know how and when to use ORMs. Probably learn SQL first. Don’t believe that any shiny bullet is silver.

Or in short: “No”

The first time I've seen an ORM I was fascinated by this seemingly beautiful idea. But I have quickly realized that it's almost useless in real life projects, plain old SQL seems just much much better. Now I don't understand why would anybody use an ORM actually.

Also, basic SQL can be easily taught in as little as 10 minutes (I have been initially taught it at middle school during MS Office Query, Access and VBA class). An image of a programmer that can't use SQL (I don't mean advanced cases which can indeed be a bit tricky but these are far beyond the powers of any ORMs anyway) seems really bizarre to me.