If you stop your examination of a technology at the most trivial use case I'm not sure what there is to discuss but...

Right off the bat, its obvious how to do a transaction in the first example but not the second. Its also obvious to use complex SQL functions in the first style and not the second.

It's not a dichotomy between raw SQL and ORMs.

Here's your query in knex, a query builder for JS:

    knex.insert({ bar: 'bar', baz: 'baz' }).into('foo')

If you find yourself having to type insert queries over and over again, then you're probably doing something wrong. I mean it takes like what, 5s to write that line of SQL?

Exactly, I don't get why people are against it. All ORM allows you to write raw query and using ORM does not mean that you suck at SQL or vice versa.

It is the same as with all frameworks: it promises productivity and speed of development, but in exchange for that the problem that you are trying to solve must fit within the model and the assumptions that were made by authors of the framework. In the end, that is never really the case and depending on the situation, fixing the last 20% of your work takes less/all/more of the amount of time that you saved. So sometimes a framework is a good choice, but is never as easy as the tutorial makes it seem.

ORM's are a bad case of this, because they try to bridge the impedance mismatch between how data is stored and linked in the database and the programming language you are using. This creates a forced model and a whole lot of assumptions and in the end it is still not good enough. Fixing the part of your project that does not fit the ORM's assumptions gets magnitudes harder because the bridge they built is very complex.

Yes, most ORM's allow you to write direct SQL queries, but that does not really solve the problem. You work the way it was intended, or you do not not. If the latter is the case, you will encounter difficulties, no matter the way break the 'rules'.

If the code is written clearly and it uses just SQL, I can almost immediately understand what is does. For ORMs it takes a lot more effort to understand what's going on when you read the code.

I have seen a lot of people in interviews that have failed SQL exercises and said that they are rusty because they have been using ORMs for too long

I'm also rusty on my assembler because I generally use high level languages and compilers/interpreters; that's what abstractions do, and not really a reason to avoid them, since people can generally solve the problem anyway, it just takes a bit more time (more than saved by using the abstraction most of the time).

But assembler is the wrong analogy for SQL. SQL isn't a lower level version of procedural code, it's a domain specific language.

When you write a technical paper in English, you switch to math equations when appropriate. Math isn't assembly language, its the correct language. It's the native language.

Yes, you can always describe your equations with English prose—and if you're doing simple addition and subtraction, it's probably nicer to write that in English. But once you want to do anything remotely complex, writing it as a math equation is more terse and less ambiguous.

> Yes, you can always describe your equations with English prose—and if you're doing simple addition and subtraction, it's probably nicer to write that in English. But once you want to do anything remotely complex, writing it as a math equation is more terse and less ambiguous.

Right! But 99% of the time, I do not want to do something complex - I just want to load a few rows based on simple search parameters, and save a changed values (which may involve heavy data processing, but not relational). Hence only using SQL rarely, and therefore getting rusty.

If you're only doing basic CRUD operations, then fair enough.

That said, based on my experience analysing applications, "loading a few rows" to perform "heavy data processing" and then "save a changed value" smells suspiciously like "the entire task can be rewritten as a single, moderate complexity query and the data never even had to leave the database server."

I'm not saying that's the case in your own generic hypothetical, as there are indeed forms of business logic and advanced manipulations that exceed the scope of a database server. But you might be amazed at what you can do within the SQL language. For example if you aren't intimately familiar with window functions like DENSE_RANK() then do yourself a favour and learn about those—and then contemplate how you could use them within a subquery joined to a table that you're updating.

Well, my current "heavy data processing" is generating a 3D render from some metadata stored in the database. Postgres is amazing, but I don't think it can do that yet :)

My experience over my career has been this: there's CRUD, lots of IO, and a bunch of data processing that just needs specialized software (image, video, weather simulation, etc). I try to offload what I can to the database - and yeah, I know about window functions - but other than for the occasional report, it just doesn't move the needle.

Fair enough. It looks like we use databases in rather different ways.

Why have two styles when its obvious you'll have to fall back to raw SQL?