I wish writing SQL queries was more popular than ORMs

You don't even mention the 2 main advantages:

• ORM lets you to use plain objects over untyped strings. I take typed anything over untyped anything, everyday
• ORM lets you to use multiple database backends. For ex, you don't need to spawn a local postgres server, then clean/migrate it after each test suit, you can just use in-memory sqlite for that. OK this has some gotchas, but that's a massive improvement in productivity

Better than an ORM is to use a query builder. You get the expressiveness of SQL with the safety and convenience of an ORM.

Most developers that use ORMs create poorly performing monstrosities, and most developers who write raw SQL create brittle, unsafe and unmaintainable software. There is a happy medium here.

You miss the major reason of an orm, abstract vendor specif syntax, i.e. dialect and derived languages such as pl sql, t-sql, etc.

Orm are supposed to allow you to be vendor agnostic

Yeah, that's great, until you need to conditionally compose a query. Suddenly your pre baked queries are not enough. So you either:

• create your own shitty ORM based on string concatenation
• create your own shitty ORM
• or use a well supported ORM, those almost always support query composition and native queries

You write like it's ORM vs native. ORMs let you write native queries and execute them while also doing all the tedious work for you such as:

• mapping results to model objects
• SQL injection safety
• query composition
• connection builders
• transaction management

So if you love native queries write native queries in an ORM which will do all the tedious shit for you.

I'm also a big fan of raw SQL. Most ORMs are fine for CRUD stuff, but the moment you want to start using the "relational" part of the database (which... that's the whole point) they start to irritate me. They also aren't free - if you're lucky, you pay at comptime (Rust's Diesel) but I think a lot of ORMs do everything at runtime via reflection and the like.

For CRUD stuff, I usually just define some interface(s) that take a query and manually bind/extract struct fields. This definitely wouldn't scale, but it's fine when you only a handful of tables and it keeps the abstraction/performance tradeoff low.

Agree 100%. Especially when you're doing more complicated queries, working with ORM adds so much complexity and obfuscation. In my experience, if you're doing much of anything outside CRUD, they add more work than they save.

I also tend to doubt their performance claims. Especially when you can easily end up mapping much more data when using a ORM than you need to.

I think ORMs are a great example of people thinking absolutely everything needs to be object oriented. OO is great for a lot of things and I love using it, but there are also places where it creates more problems than it solves.

I once had a task stripping a ODM out of a large project, reverting to the native driver, because of its (extremely) poor performance. Also the fun of profiling the project to prove the ODM was to blame. I also empathize with the "supposed to make things simpler, makes them more complicated instead" point you make.

From many experiences, I hate ORM/ODMs and am immediately suspicious of anyone who likes them.

I really like ORMs when they are well designed. With a bad API though, it hurts me to use them over a general query string.

I built a small driver for ArangoDB that just uses AQL behind the scenes because it's so much easier to manage.

Since working with SQLAlchemy a lot (specifically it's SQL compiler, not it's ORM), I don't want to work with SQL any other way. I want to have the possibility to extract column definitions into named variables, reuse queries as columns in other queries, etc. I don't want to concatenate SQL strings ever again.

Having a DSL or even a full language which compiles to SQL is clearly the superior way to work with SQL.

Hmmm

I worked on one project only which used what I guess is an ORM-like pattern, and I have to say it was actually really nice. The code was Javascript, and there was a mapping:

• Class <-> DB table
• Field <-> DB column
• Row <-> Object

For each class, there was a big mapping table which indicated which database-backed fields needed to exist in that class, and then there was automated code that (1) could create or update the database to match the specified schema (2) would create helper methods in each class for basic data functions -- the options being "Create me a new non-database-backed object X" "I've set up the new object, insert it into the DB" "give me an iterator of all database-backed objects matching this arbitrary query", "update the appropriate row with the changes I've made to this object", "delete this object from the DB," and "I'm doing something unusual, just run this SQL query".

I honestly really liked it, it made things smooth. Maybe it was the lack of hesitation about dropping back to SQL for stuff where you needed SQL, but I never had issues with it and it seemed to me like it made life pretty straightforward.

They're nice if they also migrate your db schema. That way you define your schema once and use it both to setup your db and interact with it via code. I do write raw sql for more complex queries, e.g. when there's recursion.

I find ORMs exist best in a mid-sized project, most valuable in a CQRS context.

For anything small, they massively over complicate the architecture. For the large enterprise systems, they always seem to choke on an already large and complex domain.

So a mid size project, maybe with less than a hundred or so data objects works best with an ORM. In that way, they've also been most productive mainly for the CUD of the CRUD approach. I'd rather write my domain logic with the speed and safety of an ORM during writes, but leverage the flexibility and expressiveness of SQL when I'm crafting efficient read queries.

The SQL generation is great. It means you can quickly get up and running. If the orm is well designed it should perform well for the majority of queries.

The other massive bonus is the object mapping. This can be an absolute pain in the ass. Especially between datasets and classes.

I wish SQL was standardized across vendors.

Completely agree. Most ORMs focus on hiding SQL away (for good reasons, such as portability and type safety), but I wish there were more approaching it in the reverse. That is, have the user write schemas, queries and migrations in SQL, and generate models with typesafe APIs in return. I'm only aware of SQLDelight in this space, but it's such a great idea to have the source of truth be actual SQL, and a build time generator and validator working alongside you.

I find SQL, especially prepared statements, to be essentially a function call with a string of text containing identifiers that get swapped out, then a bunch of arguments that get swapped in.

Perhaps there are better SQL libraries that make this more fluent.

But that is so close to an ORM, I feel like I might as well use an ORM.

TL;DR you can't be an expert at every aspect of coding, so I let the big boys handle SQL and don't torture the world with my abysmal SQL code.

I've seen enough bad SQL to claim you're wrong (I write bad SQL myself, so if you write SQL like I do, you're bad at it).

Seriously, the large majority of devs write terrible SQL and don't know how to optimise queries in any way. They just mash together a query with whichever JOIN they learned first. NATURAL JOIN? Sure, don't mind if I do! Might end up being a LEFT JOIN, RIGHT JOIN, or INNER JOIN, but at least I got my data back right? Off the top of your head, do you know all the joins that exist, when to use which one, and which ones are aliases for another? Do you know how to write optimal JOINs when querying data with multiple relations?

When writing similar queries, do you think most are going to copy-paste something that worked and adapt it? What if you find out that it could be optimised? Then you'll have to search for all queries that look somewhat similar and fix those.

When you create an index for a table, are you going to tell me you are going to read up on the different types each time to make sure you're using the one that makes sense? Postgres has 6, MySQL only has 2 tbf depending on storage engine, but what about other DBs? If you write something for one DB and a client or user wants to host it with another, what will your code look like afterwards?

Others have brought up models in code, so that's already discussed, but what about migrations? Do you think it's time well-spent writing every single migration yourself? I had the distinct pleasure of having to deal with hand-written migrations that were copy-pasted and modified columns that had nothing to do with the changed models, weren't in a transaction, failed half-way through, and tracking down which migration had actually failed. These were seasoned developers who completely forgot to put any migration in transactions. They had to learn the hard way.

I used to be an ORM-hater, but my experience with Django has changed my mind, somewhat. I still think there are projects where ORM is unnecessary or even harmful, but for some projects, being able to lean on an ORM to create simple queries/updates or to handle DB migrations is a big time saver. And you can always fall back to hand-written SQL when you need to as long as the ORM allows it, which it absolutely should.

ORMs introduce a different API for making SQL queries, with the aim to make it easier.

I wouldn't say that, but instead, that they strive to keep everything contained in one language/stack/deployment workflow, with the benefit of code reusability (for instance, it's completely idiotic, if you ask me, that your models' definition and validation code get duplicated in 3 different application layers (front/API/DB) in as many different languages.

ORMs are not a 100% solution, but do wonders for the first 98% while providing escape hatches for whatever weird case you might encounter, and are overall a net positive in my book. Moreover, while I totally agree that having DB/storage-layer knowledge is super valuable, SQL isn't exactly a flawless language and there's been about 50 years of programming language research since it was invented.

Check out Elixir's Ecto. You basically do write SQL for querying, it's just lightly wrapped in a functional approach.

I usually use them only for very basic cruds operations. For everything else I just write raw sql.

In my experience. ORM has its limitations.

You can only depend on ORM upto a point. Beyond that you have to go use Arel (relationship algebra in Ruby), execute prepared SQL statement, trigger and functions.

I use ORM for concise, easier to read and maintainable code. e.g. joining three or more tables in SQL is cumbersum and verbose. Writing related multiple query is too time consuming, etc.

I learnt from relational algebra, SQL, ORM to vendor specific SQL.

Dapper.net is the right balance imo.. you get the shape the query and get object field mapping.

Many-to-many associations are a pain to manage without an ORM.