[ElectricalUnion]

> ORMs are good for management and simple CRUD cases

I for one think that "simple CRUD cases" is bullshit, those applications don't exist. In practice, System-of-Records systems are rare. (and should be, their value are inversely proportional of how many of those you have in your overall system).

Because if it was "just simple CRUD", one would use the database directly? Databases are already capable of handling CRUD and much more with way less implementation bugs.

Even assuming your application "is a system-of-record", how is it giving any more value that directly using a ready-made solution like Oracle REST Data Services, or PostgREST?

[theptip]

No. If you have a simple line-of-business app, writing Django/Rails models is FAR easier than the equivalent SQL.

Even if you think that maintaining your domain model is easier in SQL (it’s not, for most full-stack engineers), the extra capabilities you get from an ActiveRecord framework such as full-stack admin pages, free migrations, etc. win overall.

I can believe that the gap is closing with the “api for your Postgres” frameworks but really, try reaching your frontend developers sql and see if they have a better time than learning Django/Rails.

[moxza]

I wrote my own ORM for exactly this reason. It's far from enterprise grade but it solves for 1) the domain model needs to stay clean and properly normalised and 2) that's not a job that can be distributed across the whole team.

One lesson I've carried for years is that most of the time the client needs denormalised views on the data model. That's the boundary; the server has the clean domain model, and the client works with views on that model. Isn't that exactly what an ORM is for?

I built mine in Dart because I want the server and client to share DTOs. Then I built a visualizer for clientside devs to be able to explore entity relationships (DDD style) and generate a JSON contract. The end result is no REST back and forth, no GraphQL complexity, just everyone in the team focusing on what they're good at.

I think the theme that ORMs are easy to start, but you pay for it with the edge cases, so good devs end up back at SQL does not apply when you're thinking about how to build a platform. Everyone has their strengths and weaknesses. Aligning the team on playing to their strengths was my goal when I reached for yet another ORM as the solution.

[thwarted]

> extra capabilities you get from an ActiveRecord framework such as full-stack admin pages

The naive "here's a row-level view of the database records" that things like ActiveRecord/ActiveAdmin give you by default are entirely inappropriate for any line of business administrative interaction. Line of business admin sites should be workflow based and focused on surfacing specific information needed for processes outside of the admin site itself. Non-developer staff should not be expected to interpret the state of rows and relationships among the tables.

[delusional]

As my career progresses, I'm starting to understand just how many developers have trouble comprehending invariants and how they affect system design. If you do not comprehend invariants, then every system is CRUD.

The specific danger of CRUD is that all operations are expressible in it. If your system is CRUD, everything goes. A developer who doesn't understand the system's design might be inclined to assume an application is "just CRUD" and add all sorts of misfeatures to it that violate otherwise constrained states. They will turn the application into CRUD.

All it takes for an application to go from carefully modeled to CRUD is for people to believe it already was just CRUD.

[imtringued]

This comment reads crazy poorly.

Like, if the simple insert, read, update and delete SQL queries are forbidden then what do you guys do all day?

Are you really doing inserts exclusively based on the data of another table? You never take user input from a website? You never need to just get a list of data according to a query with some filtering?

Honestly I'm not buying it, since the opposite would basically require you to write Hasura style monster queries for pgsql all day.

[Izkata]

An invariant is something that must always be true. The most basic example of this is a not-null foreign key, where a value in one table refers to a row in another table and that row in the other table must always exist.

> Are you really doing inserts exclusively based on the data of another table? You never take user input from a website? You never need to just get a list of data according to a query with some filtering?

None of these go against what GP said in any way.

[Charon77]

Agreed. Simple CRUD is something that only shows up in the beginning of the project, everyone was told to use ORM for that purpose, business grow, and you had awkward requirements that require complex ORM features which might exist but requires deep dive into ORM library's corner case, or just straight not possible and makes you bang your head wishing you'd write SQL instead where it would have been obvious what to write.

The only good thing about ORM is the type safety, but I find rust's sqlx or java's jooq to be hitting the sweet spot.

[dsego]

But SQL is low level, eg. you can't dynamically pass in filters and construct statements without knowing what the query will be ahead of time. ORMs have query builders that allow you do dynamically construct SQL statement based on parameters, they allow avoiding N+1 queries by doing joins in memory and much more. It's just not possible with vanilla SQL unless you concatenate strings or have multiple versions of the same query for every situation. A good ORM is an abstraction layer above that gives you more powerful tools, it's like comparing high level OOP code vs machine code.

[simondotau]

Query builders are a doddle to write, extremely trivial to debug, and generally far easier overall than having to shoehorn data into a structure that your ORM likes.

The problem is not that ORMs fail to expose every feature of a particular SQL database. The problem is that they encourage you to model your data in a way that is convenient for the ORM, rather than in a way that is correct for the domain.

Any sufficiently powerful ORM eventually has to provide escape hatches into SQL. At that point, the abstraction has failed: the ORM is no longer helping you understand the database, it is getting out of the way so you can use the database properly.

An ORM is a straitjacket. It pushes you toward sub-optimal structures, and those structures deny you access to the most powerful aspects of SQL: relational modelling, constraints, joins, aggregation, views, transactions, and set-based operations.

[dsego]

It seems like you are throwing away the baby with the bathwater. I don't think providing 90% of the structure you need is a failed abstraction. And it just doesn't follow that it is pushing you towards sub-optimal structures, not sure where this conclusion comes from. All ORMs I've seen have ways to describe relations between models, even polymorphic types, aggregates, eager loading (to avoid N+1) etc.

[ElectricalUnion]

> I don't think providing 90% of the structure you need is a failed abstraction.

It is, when the "10%" is the actual hot queries that your system will use the most?

Code right now "is so cheap". You can provide your favourite LLM with your database schema, and some domain comments, and ask it a query to fetch/update data, and it will generate somewhat sane queries for you. You can then inspect those queries yourself, send them to another LLM or human to review and, when they look OK, ship it.

And when it comes time to debug it, you have, you know, an actual query, not some pseudo-query in a custom DSL. No need to implement runtime telemetry just to try to figure out if the ORM actually made the query you thought it was supposed to do.

[nodamage]

Even if you replace ORM generated queries with hand generated queries or LLM generated queries you're still missing a huge chunk of functionality provided by an ORM.

For my projects I would say that the majority of the value an ORM delivers occurs after the query has returned from the database.

But for some reason everyone focuses on query generation as if it were the only feature of an ORM.

[sandreas]

If the ORM is capable of validation or integrates with such a component, i personally think that it integrates well for these parts of an APP, where simple datamanagement is required... E.g. adding, editing and deleting DB records, that need forms and validation.

[willtemperley]

Developing apps in SwiftUI it’s extremely useful to have SwiftData, an ORM that can act like a system of records. For my own use case this is basically metadata for large scale datasets in Parquet or similar.

That said it’s still my most frequent cause of crashes, however I think mostly it’s just because this is simply a hard problem that SQLite just isn’t cut out for (although it did take Apple until macOS 27 to supply a codable decorator grrr).

Ideally databases could evolve to fit OR mapping more closely, which incidentally is what Arrow and Parquet have done to an extent.

[imtringued]

I actually spent a majority of the last ten years on simple CRUD cases.

I mean think about it, creating a single row is CRUD, retrieving a row by ID is crud. Retrieving all the rows that belong to a user with pagination is crud, updating a specific row by ID is CRUD, deleting a specific row by ID is CRUD.

You have a settings page? CRUD

User profile? CRUD

Application form with more than 120 input fields, complex tables, split across multiple pages? CRUD

Heck I have a version of that where you get to nest multiple sub application forms into a single application with absurd amounts of nesting and it is still CRUD.

Most cases that aren't CRUD tend to be niche cases.

I mean think about it. An extended search feature that has hundreds of options is still CRUD and tends to work much better with an ORM since the query builder dynamically builds the SQL query for you instead of messing around with a static SQL query with a massive amount of feature flags.

The cases where you don't have CRUD are the rare cases. Things like reporting, batch jobs that process multiple rows at once or reconciliation that tries to find the differences between two databases.

Maybe it's not clear, but the arbitrarily complex application logic obviously is not written in SQL so even if the application is more complex than a straw man CRUD example doesn't mean that the database sees something more complicated than row creation, retrieval, updating and deletion.