This may be true in some lines of work, but as a Rails developer I find it amusing. I've been happily using ActiveRecord for over a decade. I get that chasing the new shiny can be fun and look good on the resume, but the ORM shouldn't be a fad you chase that you need to swap out every couple of years.

You're not wrong that sometimes the ORM gets in the way and slows you down. There are certainly times I've been frustrated by having to figure out the right incantations to satisfy the APIs of ActiveRecord or Arel. However, this tradeoff is well worth it, because these tools protect us from so many footguns present with raw query string manipulation.

cough Parler springs to mind.

I think this speaks to the sad state of ORMs, but not the value of SQL.

I think SQL is rubbish: The two biggest things I hate about SQL are (1) that it's unstructured, so getting data into or out of SQL involves strings, (2) the syntax of those strings. Seriously. I really don't want to program in COBOL either.

ORMs are better on both these points, but as you point out, they are rife with pitfalls and I'll add I find their APIs absolutely reek of the SQL implementation they hide. I certainly want for better.

What we really need are languages to be better at dealing with data (especially data that is backed by permanent storage or distributed across multiple machines), but it's difficult to do this without (basically) creating a whole new language, and I think the decision to bring a new language into the world isn't one to be taken lightly.

Especially when you're asking people to trust their data to it. Bad software just gets turned off and on, and it resets, but people don't put up with a bad database for very long.

[0] https://en.wikipedia.org/wiki/Query_plan

ORMs are a doomed proposition. There is simply no way to write a database-agnostic ORM API while also taking full advantage of the underlying tech.

Very simple examples include whether to return rows that have been inserted. In Postgres, you can do that in a single query. In MySQL, it's two queries because INSERT does not return the inserted row. So if I'm using MySQL and a popular "agnostic" ORM I have to realize that I'm (probably) doing an extra query on every insert, even if I don't use the result of the second query.

Then, of course, some of these ORMs don't even implement any of the database-specific features, like MySQL's fulltext search, because they're catering to the least common denominator.

Not every application needs to take full advantage of the underlying DBMS. Sometimes it's just a place to put data and not lose it, e.g. a key-value store with transactions. ORMs handle that use case perfectly well.

(I'm not a fan of ORMs personally, but they seem to work fine for logs of applications.)

Okay, yes... But an ORM is a TON of baggage for such a trivial use case, is it not? And these data have no interesting relationships to each other at all?

Not to mention that transactions work differently across DBMSs, too. So what is your ORM's default? Is it the same default as your DBMS? Does it know that MySQL doesn't support nested transactions, but Postgres does? Do you (metaphorical "you")? Do you fully understand what happens if you try to nest transactions in your ORM?

Also, do you understand how your ORM is going to convert data types and which ones are supported? Remember that JavaScript numbers don't even have the full range of a MySQL/Postgres BIGINT? Probably your programming language doesn't even support DECIMAL types natively- is your ORM going to just cast a DECIMAL to a float for you, or give you an error? Which is worse (I have my opinion)?

What about Dates and Times? How will your ORM handle timezones? How does your DB handle timezones?

ORMs don't seem to help with any of this. In fact, it seems like it only complicates things even more because you have to figure out how your DBMS works AND what your ORM is going to do about it.

Using an ORM seems like a reasonable choice for multi-platform use cases or at least some of them. The alternative would be to implement something more or less from scratch.

Structured types are in SQL since 1999.

People should really look into JOOQ to see what a SQL friendly ORM looks like.

Your "parameterized" queries are still serialized strings. Many language bindings for SQL interfaces don't have good support for serializing data types, so you will often see things like ?::datetime in the template string. Some don't have the ability to parameterize column names (or have subtle bugs) either.

> People should really look into JOOQ to see what a SQL friendly ORM looks like.

I've never heard of JOOQ. Do you know of a good introduction?

How else would you do it, short of a binary query format?

Or you can just stick with a JPA implementation (like Hibernate) which is highly performant and has been around for over a decade.

Someone will probably mention poor performance and yes, if you routinely query multiple millions of rows you'll probably want to hand code som SQL (something you can easily do without ditching JPA).

Otherwise, if you just happen to do the famous n+1 query, just get over it and actually learn how to use your ORM.

(Sibling comment also mentions ActiveRecord. I think the canonical .Net ORM has been stable for a few years already. Not everything is a Javascript/NoSQL :-)

Recently I had a chance to work on a simple 6-months 4-developers Java project from scratch. We had to interface with legacy software through an Oracle db and I was getting ready to use Hibernate again but our team lead asked if we really want to use it or just do it cause it's the "best practice". The project was pretty simple from data POV and we were mostly fetching lots of records to be processed and bulk updating a few "status" fields, but I've seen simpler projects using Hibernate for no good reason.

Using SQL directly was a breath of fresh air and I estimate it cut at least a month from our schedule.

Sounds like a perfect example for when you just want to whip up a few SQL queries, yes.

I'm not against raw sql.

I'm just against people presenting it as either/or and claiming that there is no gopd use case for ORMs.

You're probably a Hibernate expert. I'm not. I used it once, several years ago. I'm a polyglot, Jack of all trades, developer- I know lots of languages reasonably well, I know several high profile frameworks well enough that I at least know what to look up when I need to, etc.

Hibernate has a steeper learning curve than you might remember if you've been using it extensively or for a long time. I can't give specific examples, because it's been some time, but I just vaguely remember it having some of the same rough edges as other annotation-heavy Java frameworks- some of the annotations didn't actually work together the way I expected, it's mostly only checkable at runtime, remembering to use `@Column(nullable = false)` instead of just `@NotNull` (I just looked that one up because I remembered it), etc.

I also vaguely remember some issue where I had accidentally defined a class field as `int` when the column in the database was nullable. Then, instead of exploding when the ORM pulled a null out, I think it just magically converted the null to 0 and set my field to 0. Took a while to figure out why we had such strange results for some data. I'm not totally positive if I'm remembering that right, though.

You can accuse me of laziness for not wanting to learn the intricacies, shortcomings, opinions, and foot-guns of an ORM like Hibernate (and keep up to date with it even when I'm not doing Java this month/year). But I don't see it that way. I see it as being defensive. There is no way I'm going to be able to remember Hibernate's weird parts, AND Symfony's weird parts, AND ActiveRecord's weird parts, AND whoever else's weird parts. If I can just put the thinnest possible layer over SQL, map the result columns to specific types, and then build my object(s) from those "by hand", I'm perfectly happy. And I don't believe that my dev speed is slowed down by a meaningful amount. Will that part of the code take me longer to write compared to an expert in Hibernate? Probably. But that's probably not a significant amount of time relative to the whole project. And it saves me a lot of ramp up and debugging time. This is what I've convinced myself of, anyway.

Plus there's basically always an inefficiency in modeling an entity class per table. What if one of my domain types is constructed by taking a subset of multiple tables' columns joined together? Do I have to define an Entity class for every subset of columns I plan to use? That would be tedious, and POSSIBLY even more so than just writing some SQL and getting a typed tuple out per result row. Or can Hibernate et al understand pulling partial entities somehow? Is this easy and simple or does it require me to spend a couple of hours reading documentation and figuring out tricky annotations?

Everyone talks about these 80% rules: ORMs make 80% of stuff trivial and then you just break out the SQL when you need it. To me, that's a losing proposition. That 80% was already trivial to me. Writing a basic JOIN is trivial. It's the more complex stuff that I'm worried about and it just doesn't seem like ORMs do much for us there.

Yes, on your case there's hardly any reason to use an ORM.

I have worked with Symfony but these days it is always .Net Core or Java it seems and I can live with that (thankfully none of the clients I work for use Javascript or on the backend. One used Python with plain SQL and secrets stored in the application code though :-)

My current comfort zone is if I can find a query builder that has enough static typing that it has all of the keywords of my preferred SQL flavor, has prepared statements with placeholders- mainly for safety/security, and basically returns a string when you're done.

At least then I'm just dealing with SQL instead of figuring out Hibernate's arcane caching feature or why in the FUCKING FUCK JDBC will return a `0` if a column was an int value that was `null` in the result set. IT WAS NULL- GIVE ME NULL.

Some self-promotion (shameless, I know): https://github.com/lelanthran/libsqldb/tree/v1.0.0-rc2

I'm intending to rewrite it ("the first one is always to throw away" - I put too much unnecessary functionality into it and not enough RDBMS server backends) but I've used it in a few projects (use the latest branch) and am happy with it for postgres or sqlite usage.

See https://github.com/lelanthran/libsqldb/blob/v1.0.0-rc2/src/s... for example usage, but the basic premise is:

1. Send parameterised string to DB.

2. Get back records one at a time as an array of strings (NULLs are empty strings, as are empty strings) no matter the datatype of the column.

Here's the code from the current version as of this message. It's in mysql-connector-java-8.0.23.jar. Class com.mysql.cj.jdbc.result.ResultSetImpl. Lines 814-818:

    @Override
    public int getInt(int columnIndex) throws SQLException {
        Integer res = getObject(columnIndex, Integer.TYPE);
        return res == null ? 0 : res;
    }

EDIT: Postgres does the same thing: https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main...