Hacker News new | past | comments | ask | show | jobs | submit login
Objection to ORM Hatred (2019) (jakso.me)
37 points by thunderbong on Jan 16, 2023 | hide | past | favorite | 115 comments



My problems with ORMs can be reduced to:

- SQL is so easy and so useful that being a professional engineer that can code but can't learn SQL is absurd

- You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd

- If you ever get enough scale to require optimization the ORM will be your enemy

In summation, anything related to ORMs and SQL is immediately invalid, other than questions like "Help! I joined a company where they mandate ORMs and I hate my life!"


(probably) not many are using ORMs because "SQL is hard". You're attacking a strawman. I use ORMs because they make two things incredibly easy:

- saving object graphs: it's trivial to write SQL for a simple CRUD form. It's far more difficult to work with real models where you have to deconstruct the form the user posted into a dozen tables with joins between them, doing insert/update/delete as needed. With a decent ORM you simply construct an object graph by assigning fields (the result can be very complicated), shove that into the database, and the ORM takes care of the rest. It may not be web scale™, but many of us don't need that.

- constructing queries on the fly: if you ever get any exposure to LINQ, I don't think you'll want to go back to SQL after that. It allows you to easily construct queries based on user input (in a type-safe way, unlike some other solutions), which saved me countless hours when building complicated tables with search and filtering on many (sometimes hundreds) of fields.

Here is a trivial example: https://stackoverflow.com/a/10884757

And with good ORMs like Entity Framework the underlying queries they generate are predictable.


> And with good ORMs like Entity Framework the underlying queries they generate are predictable.

Right up until they are not and generate crap. I went from "predictable" EF generated queries to handcrafted ones reduced the load on the DB by about 70% while having more queries per second...


from personal experience this was a much larger problem during .net framework days. Now with EFcore, most generated queries are much more sane and performant


I find that ORMs makes it significantly harder to construct queries for anything but the most basic of queries. I routinely end up having to fight the ORM.


Linq is not really an orm, though. I think it's a "query builder"


So much this. I hate micro-ORM, they lost the relational part of RDBMS.


A very popular alternative is to inject json in the DB.


I only end up doing that when using an ORM to work around the limitations. I have never needed to do it when just using SQL.


ORM is never about "not learning SQL". I am great at writing SQL, but i hate converting my resultset to object graph, converting lazy loading vs eager loading at times, dealing with types and changing types etc.

On "great" side of things, Linq brought massive type safety to .net land, and made queries also compile time checked.

IS it leaky? yes. Every once in a while you'll need to write SQL. Does it solve a lot of problems? Also yes.

Disclaimer: I was once a contributor to NHibernate.


Especially writing 80% of queries that are simple crud to update or read single table that needs to be written most of the time.

Such a waste of time to maintain that conversion code by hand.


>LINQ... made queries also compile time checked

Alas, it's far from a complete solution. It's too easy to type an expression that compiles but blows up at runtime because the underlying provider doesn't support it or gets confused.


This is very true, when NHibernate was getting LINQ it was easy to trip its provider to throw an exception.

But it was still great for majority of cases.


All your arguments are correct, but are unrelated to the main motivations for ORMs. ORMs are about:

- Convenience of using SQL from $LANGUAGE where mapping the result, (re)naming, etc. is handled for you. Do this manually, and eventually you will either make silly mistakes or create your own mini-framework.

- Connection pooling / retrying if desired. Can be done without, but again mini-framework.

- Migrations. Generating a schema from code is neat.

- Making our lives miserable when the ORM inevitably generates horrible SQL somewhere and we need to override it manually. That is, ORMs are NOT a replacement for learning SQL.


Realistically the only ORM part of your list is the first item. Everything else is frequently done without having an ORM.


I actively dislike merging ORMs with schema migrations. They really seem like they are two totally separate use cases and shouldn't be conflated like they were.


I only have experience with Java ORMs, so I'd say you forgot to add that ORMs require entity objects which basically duplicate everything - table structures, relationships, the whole lot is redefined.

In my experience Java domain code written with an ORM is about 4x the LOC compared to using stored procedures to do the same job.

Oh - and that 4x LOC gets you about 1/4 the performance, loosely speaking.

I am a substantially more productive developer since I kicked all those abstractions to the kerb.

It is the database and its structure that is the important part of an application. Building an additional layer of abstraction on top of an already existing abstraction no longer makes sense to me.


> You will never switch databases

We used SQLAlchemy on a Python project which allowed us to use an in-memory SQLite DB in our integration tests. This gave us blazingly fast database tests during development where a database was torn down and recreated, with data, for each test in milliseconds. When the commits were pushed, CI would run the tests against SQLite and MySql.

It worked extremely well and would be a tremendous amount of effort to handwrite SQL for both DBs. I'd do it on ever project if I could.


I know that a lot of people do this but I would have very little faith in my tests if they were run against a different type of database than my production one.


> I would have very little faith in my tests if they were run against a different type of database than my production one.

Hence why they were run against MySql on CI.


Ah, I missed that part. Makes sense.


Likewise, if there weren't also integration tests that ran against a DB engine more similar to that used in production.

But no unit test environment is ever going to emulate everything in production, nor should it.


I spin up a new database per test suite.

I use pg and test suites run in parallel against their own db. It’s pretty cheap with CREATE DATABASE helpers plus using container means little to no headache.


Switching DB's does happen, but it is generally rare in my experience, and usually a strategic mistake. Those who do it usually underestimate the migration time. It's not economical to spend a lot of time up front to prepare for a scenario that happens once in a blue moon.

The main reason to write mostly generic SQL is so new hire-ees can get up to speed.


Why would you also run the tests against SQLite during CI if prod database was MySQL?


I was lead on migrating several systems from traditional deployment to a cloud provider for a major company. Part of the migration was switching a microsoft DB to MySQL. The java ecosystem and ORMs makes this so easy it's barely worth talking about. And that wasn't the only time db migrations have some up.

Not sure if that's enough to justify ORMs, but DB migration is a real use case and there are things you can do ahead of time to greatly simplify the process.


> You will never switch databases

No, you may never have had to switch databases, but I have worked on codebases where we've had to switch databases, and I'm sure others have to. Most likely when dealing with monoliths where you can't do a full re-write and you can't break out a separate service for various reasons. The repository pattern is your friend in these situations. An ORM should ideally be able to read/write to multiple database drivers which makes this easy by dual-writing to both databases from the repository layer.


I've even worked on a number of projects where a) the unit tests used an in-memory database b) dev environments used mySQL/postgres/SQLite or similar c) production used Oracle or SQL server or similar

Good luck doing that with hand-coded SQL.

But for me 99% of the benefit of using an ORM is compile-time checking of queries. I don't see why in principle that couldn't be possible using raw SQL, but I don't know of any good examples of it.


> You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd

One of the main reasons that Django has been so popular for so long is the idea of reusable "apps". Eg, Django Admin, which comes bundled with Django but there are also thousands of 3rd party ones that you can use to quickly put together very powerful sets of features out of polished, tested pieces. That whole ecosystem really only works because of the ORM, which means that apps mostly don't have to know/care what database might be used. (I assume some other frameworks have similar stories, but Django's what I'm most familiar with).

As far as scaling, I've developed and supported Django apps that have hundreds of thousands of users and serve thousands of requests per second. I would probably not even put the ORM in the top ten list of problems with achieving scale. I've occasionally found it generating slow queries and had to tweak or bypass it (both extremely easy to do) but honestly, those have been low hanging fruit when it comes to optimizing (and very easy to find thanks to reusable apps like `django-debug-toolbar`, which exists at least in part because of the ORM). Are ORMs unteneble once you're in the millions of users or tens of thousands of requests per second range? Maybe. But that's a very small part of the software landscape and for the vast, vast majority of the rest of us, a good ORM isn't going to be the problem.


The scaling problem is as future based and hypothetical as the switching issue. Which is it? Care about future hypotheticals or no?

I use Django a lot and have found the ORM invaluable for all use cases and can be optimised when needed, with some friction.


One way to have the best of both worlds for situations where you have complicated read queries is to create database views for the queries. You can then define them as an unmanaged table in Django (requires some boilerplate) and query them in a simple way as if they were a normal ORM model.


> You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd.

You actually might switch databases at some point, but in my experience this always included overriding some of the ORM-queries with custom ones that the particular RDBMS needed for some reason.

So you have to write SQL after all when switching, might as well do it from the beginning.


Most data I have is not tabular, but tree-like in nature. A course has instances of a course, which have students and assignments. Students have submissions per assignment. You can press this into tables, and then join it back together, but it is a detour. The question for me is not: why use an ORM, but why use an SQL database as the underlying storage.

My reason is that there are no good object databases yet. I would want something that allows you to take your C++ vector or Python list of plain old objects, and stuff it into a data structure:

- with a choice of rowwise (array-of-struct) or columnwise (struct-of-array) storage

- with custom indexes (e.g. not just map<string, MyStruct>, but Database<MyStruct> and tell it to index of propertyA, propertyB, ...)

basically taking performance oriented aspects of relational databases and applying them to an in-language data structure, without leaving the world of trees and objects. Back to the question, an ORM is for me not an alternative to SQL - it is an alternative to dicts and list comprehensions and manual storage.


- SQL is so easy and so useful

SQL is not hard, it just sucks. Massively. "So useful" in the same way BASIC was useful for 80's computers.

- You will never switch databases

You might not want, but you might need to

- If you ever get enough scale to require optimization the ORM will be your enemy

BS. Most ORMs do let you drop into SQL, no problem. This is usually one or two queries on a whole project that merit this treatment

If things are slow you can probably optimize stuff at the ORM level itself (or your modeling was done by interns - which would be a problem regardless of ORM or no ORM)


> You will never switch databases

So this is an interesting one. We didn’t switch databases, but deployed the same application in some different infrastructure due to how different companies in our “group” have different setup. So we went from having an API running in Azure connecting to a DB running in MS SQL Server, and had to also deploy that (with some UI changes) to Google Cloud with postgres DB.

It went surprisingly well, but there were a few fiddly bits and I was quite glad I didn’t have to worry about the DB interaction, EF Core handled it for us


First point is really absurd and I don’t understand why people make it.

I have never seen argument “I use ORM so I don’t need to know SQL”.

It is always people who boast they know SQL that they don’t need to know ORM.


Exactly. I'm happy with tsql [0] - template based, with safe sanitation, helper renderers/combinators, used in production for several years, would recommend this approach. It's basically pure sql with safe helpers when you want to use them. You can't use orm if you want to use underlying database beyond common denominator for basics.

[0] https://github.com/appliedblockchain/tsql


>template based

There's a reason why the modern approach is parameterized: The old PHP-style templating led to galore SQL injections, and worse performance. Don't see any reason to ever go back.


This is common misunderstanding.

Tagged templates in js allow you to write safe, injection free sql templates, tsql does exactly that.

Performance is better if anything, but irrelevant - you're talking about sql rendering performance, which is several orders of magnitude lower than invoking query?

You should double check it. See examples of usage. We use it for mssql but same principles apply to other vendors. Mssql has many interesting things in its dialect that can't be expressed in orm. With templates you simply write sql and aid yourself with functional combinators for complex statements. It works really well.


I'm referring to possible performance loss due to lack of parameterization. Yeah, one could enable forced parameterization and let parameter sniffing do its job, but that can be a double edged sword.


> You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd

I have a number of hobby projects that work with both SQLite and Postgres by virtue of a ORM. SQLite is great for testing and lightweight installs, while Postgres scaled much better.

So yes, there are absolutely use-cases where the ability to switch DBs is very valuable.


I also dislike ORM, mostly because it takes what I like (beautifully normalized relational data) and squishes it into ugly object graphs. No need for ORM if you don't do OOP.

But if I have to do OOP on data from database I much prefer ORM over doing it manually.


I'm not sure what you'd call them as they're not ORMs, but I really like libraries like Jdbi that make it easy to map results to objects.


I’ve worked on multiple database migration from something commercial to Postgres. ORM was a huge help.


Am I wrong to remind that Part of the reason to not generally write raw SQL is to prevent SQL injections?


Parameterized queries do that just as well.


Yes, then your junior PRs code that doesn't use it and it gets missed in the scuffle


My basic complaint about ORM is that it is such a massively leaky abstraction. That the (sometimes, but not always-made) claim that it 'abstracts away the sql' is just plain false. An understanding of SQL is typically necessary. Many ORMS are introduced by saying 'this expression translates to that sql'. Oops, no abstraction there. I like this sketch of objection because it's in some respects just friendler sql syntax.


Yeah, even SQL is leaky as two equivalent queries may have dramatically different execution plans.

Actually, knowing how to write efficient SQL is very well rewarded by the market.

So adding another layer on top of it, an ORM, is IMHO not a good idea.


Is that implementation or is that a base problem?

I agree - I would much rather write SQL. But like others have said in this thread, I hate writing custom code to translate anything but the simplest queries into what I actually need.

I also hate trying to combine slightly different queries with custom string manipulation that turns into 750 lines of code before you know it. Most ORMs handle that use case very well.


> claim that it 'abstracts away the sql‘

I have never ever met anyone who genuinely used ORM claim that in any form of shape. The only ever place where I’ve seen it is threads about “how bad ORMs are”, so it is only a strawmen.

ORMs are first and foremost a tool for OLTP-based workflows, helping mapping between objects and db rows, especially for insertions. They are also very great for CRUD operations on tables. They were never meant to abstract away the SQL model, that is one necessarily has to know the underlying tables, and often write queries with manual joins, etc (many ORMs allow custom queries in a cross-DB way). But even in these cases mapping the result is very convenient with an ORM.


I hate the complaint that everything is a "leaky abstraction".

Can't we just just accept a tool that augments and improves something without caring that it's not a complete and perfect abstraction over another technology? I use an ORM every day and I also use SQL every day. The ORM is such an improvement for what it does that of course I would use it. But I'm not restricted to using it. In fact, most ORMs encourage mixing SQL with their API; that's not a leaking abstraction -- it's bringing the fire hose.


Yes, I totally agree. Something isn't working? Oops, now you still gotta look at the SQL!

Also...

I've seen several anti-pattern usages where someone would query a list of IDs, and then in a loop with an accumulator fetch data from another table, one ID at a time.

If you don't grok joins, and someone tells you "yeah whatever don't bother learning SQL, we have an ORM", this happens.

An ORM is an anti-tool.


My problem wit ORMs is that majority of them assumes 1–to-1 correspondence between table-rows and objects. And forces me to build a layer on top which glues pieces of object-graphs into higher level abstraction. I often feel that writing custom mappers which skip this lower level works better. Original DataMapper and DataAccessObject patterns have this flexibility in mind, but are rarely used these days


That's interesting. I use Knex a lot and I agree that it's very easy to end up just writing what is basically an ORM on the spot. I haven't quite decided if I think it's a real problem though.

I created and use Kanel (https://github.com/kristiandupont/kanel) to generate Typescript types from my Postgres database which helps a lot. Knex itself has some attempt at type safety but it gets confused quite quickly, so that's where it's tempting to override things.


I really hope the community converges on just one common solution to this as I see soo many different approaches to the same “there’s no great modular ORM for typescript” problem.

I myself went the route of “lets just have raw sql queries” but extract distinct and specific types from them automatically- https://github.com/ivank/potygen

Great thing is that it handles sql of any complexity - CTEs, views, nested selects/joins/unions/custom functions you name it. All tools you can use to encapsulate logic in sql itself (what ORM were supposed to be for) and then have it be statically validated at compile time thus saving you the need for righting all those trivial unit tests that was also one of the key benefits of ORMs.

Admittedly if there was something like LINQ I would probably not have ventured into building potygen, but I’m glad I did as I learned so much about SQL as a language and what it had to offer - its silly how much logic I used to rely on writing in code that could easily be handled by sql in a much mode concise way.


Looks nice and I appreciate the README - those comparison tables are much more elaborate than my sibling comment, though looking at the single con in the table for potygen itself does make me wonder if they might be a little biased ;-)


That looks very neat! I use libpg_query to parse view definitions to create type information from there, but it's quite basic so far. Are you doing something similar, or did you implement the parser from scratch?


I started the project mostly as a way to play around recursive decent parsers, and then it actually turned into a useful tool.

So yeah its all pure JS, and not produced from a parser generator.


That's impressive! I will try to see if and how I can incorporate your tool into my workflow.


I'm currently observing the same with a client, also using Knex with Typescript. They're trying to work with the Knex-provided types, and built a (currently still small) set of functions for shared logic like wrapping into a transaction. Those require a non-trivial amount of type-juggling and I'm not sure yet whether I like it. I've seen both plain SQL and ORMs in larger code bases, choosing between those and the "query builder with some custom abstractions" is hard.

Compared to plain SQL, the obvious advantage is that you don't have to work with string replacements, but a bit of readability/maintainability is lost because of emerging complexity. Compared to an ORM there is much less working around it's quirks, but also the danger of re-inventing a good part of it.

Btw they also wrote up a sizeable number of record types, Kanel looks interesting for auto-generating those - thanks!


Check out Zapatos too: https://jawj.github.io/zapatos/


Thanks, we will!


Would it be fair to say that Kanel is similar to half of my library Zapatos, which generates TS types from Postgres, but then also gives you some simple tools to use them in querying the database?

https://jawj.github.io/zapatos/


They do indeed look very similar, yes!


I've been dabbling with Prisma after almost a decade of staying away from ORMs such as EJB3 / Hibernate / iBatis.

With Prisma I like the ability to generate migrations and the DB schema from a single schema definition file. I also like the autogenerated simple crud use cases.

It did not take long to have to go raw. A simple case of selecting the top most occurring unique columns from a table with a join to limit by user id is brutally trivial oneliner in SQL that just rolls off the keyboard. I took a good try to do the same with Prisma and I believe it can be done, but the complexity of what the DSL will look like is laughable.

So I still have the same opinion I've had for these past years, ORMs are okay for simple crud stuff but the moment you need something slightly beyond that bailing for the raw sql is the best thing that you can possibly do for the maintenance team that comes after you have changed projects.


I like the premise, that query-builders are lower-abstraction hearts of how programming languages and SQL can meet. In my limited experience though, I feel like there's almost always very painful limits to query-builders and it's unclear where the boundaries are.

I wish I knew more good options to try, but in JS, I've tangled with Knex (and some Bookshelf, which I have over time felt compounds the problems), and while it usually starts off okay but repetitive, with little shared code, it's rarely clear whether what you want to do is just impossible without gratuitous .raw() madness spiced in or not.

I've seen too many engineers try to out-engineer the limited help something like Knex can offer, try to make better libraries of tools to do common queries. These confound & limit, again and again, provide long term pain, suffering, & misery for everyone who latter stumbles upon them. There's good heart here, but we'd have been better served by a library of knex and/or sql queries to derive from & rewrite, rather than these painful attempts to write higher level tools, which always fall short of the in-practice needs. The sheer expressiveness of sql keeps feeling unmatched, and attempts to capture the main cases keep becoming painful stumbling blocks that limit comprehension & growability. SQL keeps feeling more appealing.


My main expectation from things like ORM is to be able to think of a query with SQL logic. so the end result will be SQL and I should be able to picture it in my mind. but this is where things get messy. It seems difficult to achieve this with ORMs. yes, they make a lot of things easier and they have conveniences, but to understand it, you need to grasp the logic they offer.


> My main expectation from things like ORM is to be able to think of a query with SQL logic. so the end result will be SQL and I should be able to picture it in my mind. but this is where things get messy. It seems difficult to achieve this with ORMs

That's largely the opposite of the purpose of an ORM, which is to isolate “SQL logic” and present OOP logic.


Every sufficiently well implemented OO program that talks to an SQL database includes a poorly implemented ORM where one wasn't chosen.


I am very good at SQL and loved composing queries in a query browser and then using those in a project. its easy to debug and optimize. Havnig said that its all like for nothing going to great lengths for an idea which may end up used by no one.

Thinking of using prims ORM for my next side project.


Are there any examples of ORMs that have been used for over 10 years that people don't hate?


Rails activerecord? It’s got it’s quirks, but I feel like it’s pretty boring and predictable to use, in a good way!


SQLAlchemy: "SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL."

  - connection pooling, multiple drivers
  - core layer (can use to build sql without orm, describe table metadata, output ddl, etc.)
  - orm layer (automatic class to table mapping, relationships, configurable relationship loading strategies, easy ad-hoc optimization of loading objects, write custom SQL to load objects, load relationships from subqueries, etc.)
  - db reflection
  - db inspection
  - migrations (via alembic)
  - now supports async drivers
  - 2.0 coming out soon with more consistent interfaces
Pretty much does almost everything or provides a compatible way for you to do it yourself. I think most people complaining about ORMs just haven't used this one. You still have to know SQL.

edited: formatting


Dapper https://github.com/DapperLib/Dapper/

Granted, it doesn't do everything (it just does what it needs to not be hated). I used it because it does the boring error-prone bits of manual SQL but not much else.


I never hated Hibernate. It gave me huge amounts of power and flexibility, outs to use raw SQL or even stored procedures when necessary, and was extensible enough that I could even map to legacy schemas that were basically insane.


Ruby's Sequel ORM -

https://sequel.jeremyevans.net/


DjangoORM


ORM's come and go just like web frameworks, so I really don't care for them. I use sqlx [1] when I can.

1: https://github.com/launchbadge/sqlx


I think the Java ecosystem is an interesting example, because it has many different types of ORMs available:

  Hibernate: a fairly "traditional" ORM, that can abstract away SQL almost entirely, it even has a DB-agnostic DSL for some use cases (HQL)
  myBatis: an ORM that lets you write actual SQL in mappers and generate statements dynamically, with conditions/loops/etc. defined in XML
  jOOQ: an attempt at letting you generate queries in pure Java code (as opposed to the myBatis approach), which looks a bit like the one in the article
  JDBI: a very lightweight wrapper on top of JDBC, which perhaps isn't a full ORM, but makes querying the database with SQL a bit easier
  (probably some others, too)
Personally, I think that the "Object–relational impedance mismatch" isn't necessarily the worst thing ever in the day to day, when you need to get things done: most ORMs can be good enough, when used responsibly and for the things they actually do well. For example, in my eyes it makes sense to take advantage of DB views and keeping how data is queried (think complex SQL queries) mostly on the DB side, whilst keeping the mappings in the actual ORM layer relatively simple, instead of trying to do a bunch of joins in the ORM.

That way, if you ever need to debug how something is selected, then you can simply look at the data in the view, which is going to be the problem in most cases. In my eyes, that's a good way to also avoid running into the N+1 problem too often - if there's a table in your APP (a list in your API) that you need to populate with data, that might as well be a single query against MY_BUSINESS_DATA_V or something like that.

As for doing the opposite - modifying data, you might also benefit from simple mappings, perhaps even taking advantage of some of the codegen capabilities available. Point a CLI tool at where an instance of your DB is running and generate your entities from the existing schema (or vice versa). As long as you don't have too complex DB types, it shouldn't be too bad.

If you choose a different approach, in my experience you'll spend a lot of time struggling with the internal complexity of the ORM. Hibernate is a good example of this, I've seen people spend entire days trying out how to express their super complex queries in the ORM, not really knowing how to get the equivalent of whatever SQL they need.

SQL is good for working with data, so do that whenever it feels like a good fit for your problem. SQL is sometimes worse for certain other aspects (if you need CPU intensive processing for a limited subset of data, subjectively also readability, how expressive the code is, testing, logging, tracing, the available libraries and so on; things that general purpose languages can do better) so don't try to put everything in SQL either. Try to bridge the gap between SQL and whatever language you're using in a low friction way, if possible.


ORMs are bad because it's a high level abstraction on top of a high level abstraction. SQL is already high level, so it's pointless to build on top of it. It also makes things much much more harder as SQL is a leaky abstraction and all leaks need to be passed through from one high level abstraction all the way up another one.

Let's say I wanted to build an abstraction on top of ORMs. You would think I'm crazy. Why? Pause here for a bit.

Your rationale here... is exactly why you don't want to build an abstraction over SQL.

Anyway. The author does have a somewhat valid point about the auto joining of foreign key relationships on a table. But this is only one missing feature. Creating a whole new high level dsl on top of another high level dsl just for this is over engineering.

The real abstraction needed here is some sort of extension to SQL. Either extend the language itself in the standards, or the implementation or typescript style via transpilation.

The only other valid reasoning behind ORMs is just cosmetic such that you don't have to have to deal with SQL primitives as strings inside another language.


The role of ORM is to convert relations (sets of tuples) into the object structures our applications usually model data as. Unless your entire application is modelled as relations, there is no avoiding this transformation step, be it whether you use a toolkit to help you or slog it by hand.

SQL doesn't help you here. SQL is just a query language that is designed around the relational model (more or less). The same relational model that your application isn't (likely) designed around. Thus, again, you need object relation mapping to provide the conversion between SQL's model and your application's model.

Some ORM toolkits also bundle query builders. The query builder may be what you find unnecessary, but I'm not sure the existence of query builders is crazy. These query builders essentially become another programming language that compiles to SQL. The idea of a programming language compiling to another programming language isn't crazy at all. We do it all the time with great success.

Although there is debate as to whether or not it is due to SQL/RDBMSes not being purely relational or if it is more fundamental, what is agreed upon is that there is an object relation impedance mismatch. This makes it a hard problem to solve and many ORM toolkits only provide half solutions, and this is where a lot of friction comes into play.


>The role of ORM is to convert relations (sets of tuples) into the object structures our applications usually model data as.

I am saying this transformation step is pointless. It's a leftover philosophy from a time when everyone thought object oriented programming was the only way to go.

You don't need the object abstraction. SQL is already a high level abstraction. Tables and foreign keys already cover everything you need including the relations.

You are converting tables to objects. One high level abstraction to another. It's like translating English to Chinese to explain something to someone who is already bilingual.

Here's another way to put it:

The models already exist in the database. Why build a repetitive model in the application language? It's a cosmetic thing because people don't want to deal with string manipulation. Overall most of your logic and abstractions lives in the strings you pass to the database. There should be minimal structure and logic for database queries outside of strings in the application code.


> It's a leftover philosophy from a time when everyone thought object oriented programming was the only way to go.

I'm not sure object oriented programming ever really caught on. The only object oriented languages that ever gained any claim to fame are Smalltalk, Objective-C, and Ruby. And none of them really gained what I would consider mass appeal, let alone thinking that they were the "only way to go".

> SQL is already a high level abstraction.

SQL is a high level query abstraction, but we're talking about the data model. Again, a relation is nothing more than a set of tuples[1]. That is a very primitive data abstraction.

Using Javascript notation, consider the relation: [["John",32], ["Jane",23]]. Now consider a function that takes that input and outputs: [{name:"John", age:32}, {name:"Jane", age: 23}]. That's ORM. That's all ORM is. You see no value in those higher level structures?

> The models already exist in the database. Why build a repetitive model in the application language?

Primarily because your data store is an implementation detail of your repository layer that shouldn't leak into the rest of the application. Even if your application's domain model can be ideally represented as relations, you still will want a conversion step to not bind that data model to the database's model. Schema changes to the database shouldn't break the users of your code.

> It's a cosmetic thing because people don't want to deal with string manipulation.

String manipulation? Are you encoding your application's state into something like JSON at every turn, or where do strings come into play? Don't do that. Even if you do need to share your application's state (e.g. to share over the network) in a stringly fashion, use another conversion pass to convert your internal representation to the sharable format. Your central domain model should be optimized for the machine's local memory.

[1] If we are being pedantic, SQL actually deviates from the relational model by not staying completely true to sets. But the terminology has stuck regardless.


> The only object oriented languages that ever gained any claim to fame are Smalltalk, Objective-C, and Ruby. And none of them really gained what I would consider mass appeal, let alone thinking that they were the "only way to go".

What about Java, C#, and Python? The first two have been go-to at many businesses since the turn of the century. If C++ isn’t mainstream it’s because in no small part people picked those instead.


I expect Java was once confused as being object oriented because James Gosling came from a Smalltalk background and Patrick Naughton from an Objective-C background and as a result Java takes a lot of inspiration from those two languages. But it misses the part that provides orientation. Java's object model is much closer to C++'s in that regard, and C++ definitely isn't object oriented. The coiner of the term even once literally said so.

Not sure why you think C# and Python are object oriented? They have what could be considered objects, but where does the orienting come into play?


Because that’s how their creators describe them. C# started as COOL, as in “C-like Object Oriented Language”. Python incorporates that term in descriptions from the 90s and see e.g. http://python-history.blogspot.com/2009/01/introduction-and-... Both incorporate other paradigms like every other popular language but that doesn’t negate the huge influence OOP had on several generations of languages.

If you’re on some weird purity bent where you insist mainstream usage is wrong, well, I can’t stop you but I’ve never seen that result in something insightful or educational.


Looking over the Python spec again, I can maybe see how you might consider it object oriented. In my experience using Python in practice, I am not sure it is commonly used as an object oriented language, though. Not like the other three languages are. I expect this is more like saying Go is a functional language[1] because it has some functional-inspired features.

C# didn't yield the same results. As you suggest, it even changed its name away from containing 'Object Oriented' during the project scope evolution to not confuse people about what it is. I can't find any primitives that would even allow you to write object oriented-style code.

That said, I won't completely dismiss C# as being object oriented as it is the language I have the least familiarity with and very well could have overlooked the obvious. As before, I ask where the orienting comes into play. What language features at least come close the oriented paradigm?

[1] And, indeed, many do call Go a functional programming language. They're not strictly wrong, but it can make communication hard when you're loose like that. In these sorts of discussions you do have to be a little more precise, otherwise you'll end up talking past each other.


The distinction between smalltalk and other oop languages is not relevant.

The majority of the human race thinks of java as oop and is referring to such languages when communicating. Please do the same.


>I'm not sure object oriented programming ever really caught on.

Uh they have. They were the dominant paradigm for a long time. You haven't been around long enough. I'm going to be real with you, a lot of what you say shows that you're relatively new.

>Using Javascript notation, consider the relation: [["John",32], ["Jane",23]]. Now consider a function that takes that input and outputs: [{name:"John", age:32}, {name:"Jane", age: 23}]. That's ORM. That's all ORM is. You see no value in those higher level structures?

Doesn't seem like a relation. Seems like your referring to a single table with no relationship to other tables specified.

You took a tuple and converted it to a named tuple. In SQL named tuples or named sets is the exclusive syntax... all columns are defined by name and type, all columns are queried by name. Indexed ids must be explicitly defined and in SQL they are not the default.

>Primarily because your data store is an implementation detail of your repository layer that shouldn't leak into the rest of the application. Even if your application's domain model can be ideally represented as relations, you still will want a conversion step to not bind that data model to the database's model. Schema changes to the database shouldn't break the users of your code.

This is incorrect. First off with an orm you are implementing your datastore in two places. You define the model in SQL and redefine it in application code. There is no separation here. The orm is by definition a leak. It destroys the concept of a single source of truth.

Second. A change in schema in SQL necessitates a change in model in application code. It cannot be separate by definition. Otherwise your model in code doesn't match what exists in reality.

>String manipulation? Are you encoding your application's state into something like JSON at every turn, or where do strings come into play? Don't do that. Even if you do need to share your application's state (e.g. to share over the network) in a stringly fashion, use another conversion pass to convert your internal representation to the sharable format. Your central domain model should be optimized for the machine's local memory.

Where on earth does Json come from? No man. String manipulation is what an orm does for you. SQL databases can only operate on strings. If you don't have an orm then you are doing the string manipulation yourself because strings of SQL are literally the only interface these databases can accept.

I don't think you fully understand the mechanism at work here. Your orm is sending strings to your SQL database over a network. That is what an orm is.


> They were the dominant paradigm for a long time. You haven't been around long enough.

I've only been programming for about 30 years, so yes still quite young, but during that time the C++/Java style object model has been king, and while it is based on objects, they are definitely not oriented. For orientation you need, at very least, message passing.

When was oriented programming popular and not just a cool novelty that rises up from time to time?

> Doesn't seem like a relation. Seems like you're referring to a single table with no relationship to other tables specified.

A table is represented by a relation[1], yes. A relation isn't a join. A relation is a set of tuples, by literal definition.

Does your confusion here stem from not being familiar with the relational model?

> String manipulation is what an orm does for you.

What string manipulation is required in the ORM phase? You must be thinking of query building, but that's a completely different level of abstraction. Many ORM toolkits also bundle query builders, but you could create an ORM toolkit that expects one to write SQL by hand. They are not intrinsically linked. In fact, most ORM toolkits, even those which come with query builders, do allow you to write SQL by hand if you prefer.

[1] Ignoring the peculiarities of SQL which deviate from the mathematically pure model.


If you've been programming for 30 years, it doesn't show. I'm sorry.

This is what a relation is: https://www.techopedia.com/definition/21677/relation in the context of relational databases.

I think you're confused.

>What string manipulation is required in the ORM phase?

You're just on some strange pedantic purity bent as the other person commented. Even the op refers to his "query builder" as an "orm". Most ORMs intrinsically tie "query building" and the orm together without even referencing the term query builder.

Despite all this it's fairly obvious you know what we mean and what we are trying to say. You're just playing a part here.


> This is what a relation is: https://www.techopedia.com/definition/21677/relation

"Relation is sometimes used to refer to a table in a relational database" – True, but a relation is any set of tuples. Joining multiple tables together also produces a relation. It's not just tables. It's the more abstract concept of rows and columns, basically.

But, as noted earlier, SQL actually deviates from the relational model here. It doesn't actually operate on sets. This is where you find a lot of the gotchas it is famous for. Something like QUEL, which Postgres used instead of SQL for the first 10 years of its life, is closer to Codd's original model. It actually has relations.

I expect that deviation is why we're having trouble communicating here. As SQL isn't actually relational, calling them relations is technically a misnomer. While I appreciate the purity bent you say you're off on, I have indicated multiple times that I use relation with acknowledgement of SQL's deviation from the term in its purest sense. Yes, again, SQL relations aren't truly relations, but for the sake of ORM they serve the same purpose.

> Most ORMs intrinsically tie "query building" and the orm together without even referencing the term query builder.

Such as? All of the ORMs with query builders I'm familiar with are like "You an use our query DSL and you can even write raw SQL!". They present a clear separation between the ORM and the query building phases. If they were tightly coupled in those toolkits, they wouldn't be able to enable you to use raw SQL queries.


>I expect that deviation is why we're having trouble communicating here.

No the deviation is deliberately manufactured here. The oop pedantism is obvious. Smalltalk was the first but the word as evolved, everyone knows that. Including you but you pretend not to know this.

>While I appreciate the purity bent you say you're off on,

I never said this It was said by me and another poster that YOU are the one off on this bent of being overly pedantic about terms.

I think you're just trolling now. This kind of thing is against the rules here fyi.


Funny that I am the one being called pedantic, despite flat out acknowledging that I am using the term informally. Yes, again, technically a SQL relation isn't actually a relation. I get it. But we all know what relation means with respect to the conversation, so what is to be gained by ignoring that context?

Formal definition matters only if you haven't reached a shared understanding. Before definitions entered the discussion we started at that end, but all the information necessary to understand what relation means in SQL-speak, particularly with respect to ORM, has been given. There is no value in hanging off any other definition and doing so is merely a straw man.

Whatever you want to call it, the abstraction that converts between the database's data model and the application's data model is not the same abstraction that builds queries. They are very much distinct operations and ORM is only concerned with one of them (hint: Not the one that builds queries).


You are being pedantic by virtue of even turning the conversation in this direction. Stop trolling.


The conversation hasn't turned. We are still waiting on examples of ORM toolkits that don't provide separation between the ORM and the query building operations. I suspect ad hominem entered the chat because one couldn't be found, but I'll continue to wait as I love to learn new things.


It turned when you decided to pretend that you thought all OOP meant smalltalk style OOP and when you chose to act like you were talking about relations in terms of sets and not sql. You pretended you didn't know the context when it's fairly obvious what the context is. You are playing a part.

You don't love to learn new things, that is not your objective here, your objective is to troll. This conversation is over.


OOP in the discussion was undefined prior to me choosing a definition. Smalltalk-style OOP is quite significant to the topic as what are probably the only respected ORM toolkits (CoreData, ActiveRecord) lean heavily on the facilities provided by what you call the Smalltalk-style OOP paradigm. I might even go as far as to say that the active record pattern is unusable without Smalltalk-style OOP. Other languages have certainly tried, but it gets janky really quickly.

Now, it's okay if OOP means something different to you. Words are just tools to help with communication, not some source of pride or identity. That is why definitions were laid out to ensure that we weren't just talking past each other. Despite this making you believe I am young, in my 30 years of development experience I've learned that you can't assume everyone uses the same definition. In software, it is actually quite rare to find any common definition across any sizeable group of developers.

There is no logical reason to leave a discussion because what OOP meant to me differs from what OOP meant to you. The OOP talk that was present early in this thread didn't even carry into the remainder of the conversation about ORM, so it is moot anyway. The reality is that this conversation has reached a dead end because there is nothing left to talk about. We've established that ORM and query building are different operations, which rounds out the entire topic of the thread, and that's that.


>There is no logical reason to leave a discussion because what OOP meant to me differs from what OOP meant to you

there is reason to do so if you're trolling.


>don't have to have to deal with SQL primitives

I believe the root cause is that many programmers do not like string manipulations inside their very own programming "language", be it Spring Boot or Nodejs /s


I completely agree. I think it's ocd thing. Programmers turn to ORMs because they appear elegant as it gets rid of string manipulation.

You need a higher level perspective shift to realize that the orm is actually the uglier abstraction then just using plain strings.


It's also because having your SQL just be a plain string means you lose type safety and have to do a bunch of type casting on the results of your query, which may or may not be faithful to the DB's actual model


If you want static checks it needs to be done separately from the main language.

What I find is that a good ide or editor is capable of doing this for a string if you tell it what that string is.

Static checks are not a reason to create a whole new abstraction over a language. As I said it's a sign that new features are needed as an extension. In terms of type safety for SQL I find certain ides already cover most of this so even a typescript style extension on top of SQL is not needed.


> SQL is already high level, so it's pointless to build on top of it.

I'm not sure that's a truism at all


It's a generality that's generally true. It's also common sense.

For the same reason why Translating python to ruby is generally and obviously pointless.

The main thing that makes ORMs alluring is that people don't like to mess with a language in a language. Dealing with strings is inelegant.

Seeing why the orm is worse then string manipulation requires a 4 dimensional perspective shift that's really hard for people to comprehend.


Is TypeScript bad because it is built on a high level abstraction? What about Sass. Or perhaps the HTTP request handler abstractions built on the (already high level) HTTP protocol abstractions?

Or any high level language built on a highly abstracted VM? Are they bad just because of their abstraction degree?

Highness of abstractions is relative not absolute.

What about a UI library? Or image manipulation library? Must you mutate a bitmap yourself?

I don't think it always holds true, but rather many poor tools happen to be leaky abstractions or compounded leaky abstractions.

I don't think the "level" is important except where any lower level is a bad foundation.

They're not all bad foundations.


I stated in my first post that the proper abstraction is an extension to SQL. It is ok to extend a language to cover missing features or correct mistakes.

Typescript and sass are extensions, that's why they are good. ORMs are not extensions.

Before the advent of advanced natural language processing like chatGPT, SQL was literally one of the highest level of language abstractions we can arrive at. It's declarative and English like.

Thus, given that SQL is generally at the highest level... any abstraction you would've built on top SQL would inevitably and in general not be higher. These abstractions are movements in progress that are horizontal in nature and therefore relatively pointless.

The point of most abstractions is to provide a higher level interface. An orm does not do this. Instead an orm is a shift in philosophy that declares the SQL philosophy to be incorrect.

I don't completely disagree with that philosophy. But the proper way to implement your philosophy is to write a database; not a transpiler that transpiles your philosophy onto a technology with a different philosophy.


I don't think it is that black and white. An ORM has utility for many use cases.

I think too many people try and discuss tools based on things that aren't relevant or are idealistic or that remove the nuance from the argument.

Does transpiling make for a bad tool? Depends on the use case. Does mapping from tables to objects make a bad tool, again depends on the use case.

I think any discussion of tools without a clearly scoped use case is next to useless.


>I think too many people try and discuss tools based on things that aren't relevant or are idealistic or that remove the nuance from the argument.

Some people think the universe is just apple and oranges. Everything is a tool that can be used somewhere nothing is truly good or bad. Well how about that? On the entire earth there isn't a technology that is absolutely great or raw shit and anyone who has such an opinion lacks nuance or is too idealistic.

No. To over-rely on nuance is to say nothing is black and nothing is white and everything is nuanced even when the nuance is inconsequential. This is wrong. Generalities and essential truths exist. For example, punching yourself in the face is generally bad, but if you talk about nuance and context then if you had a poisonous bug on your face, then punching yourself to save your own life is a good thing. But despite this the generality remains true.

The nuance for ORMs is only this: if you want to create a tree or a graph for graph algorithms like bfs or dfs on top of a non graph database then ORMs are literally the main abstraction to use. That's it. This is rare, because usually you have other databases other then relational that are better for this. But if you don't have the resources to load an entirely new type of database into production then ORMs are one way to perform graph algorithms on an existing SQL database.

For this instance the situation is clear, the nuance is negligible. ORMs are bad for most cases the same way eating poison is bad for you in most cases. If you want to talk about the nuance of when eating poison is sometimes good for you, be my guest. But the generality remains true.

>I think any discussion of tools without a clearly scoped use case is next to useless.

Agreed. Let's scope it to the all practical use cases seen in the wild. My argument is for the vast majority of use cases ORMs provide little benefit other then cosmetic.

This whole thing about scope is quite pointless imo. Because obviously the above scope I described basically what I'm referring to. You know it. Why do I need to spell this out specifically or be useless? Shouldn't the implication of scope be derived automatically from the discussion via usage of common sense?


> if you want to create a tree or a graph for graph algorithms like bfs or dfs on top of a non graph database then ORMs are literally the main abstraction to use. That's it. This is rare

You said it yourself, if you want to store a graph (in memory object instancss referring to each other by reference) in a non-graph database (say postgres or mysql) then an ORM is the thing to use.

This is literally what everyone uses them for. You defined their use, and that's what we're all doing with them and finding immense value from them doing it simply.

It isn't rare. It is literally the common case in every web app everywhere. There are more examples of an ORM being good and providing value, in the real world than not.

I'll just carry on using them and finding them valuable, and in the rare corner case their limit is exceeded then take the harder or more complex option.

Best tool for the job.


Some tools are garbage. But according to the logic you present here such a universe with garbage tools doesn't exist, even a piece of shit has some sort of use in programming. "best tool for the job" is a niave and illusory statement. There is merit in declaring something bad at everything or good at everything.

Anyway. Dfs or bfs queries are extremely rare in SQL and much of web dev too and they still can be done without an orm and all within database code. The amount of times you see a recursive query is the amount of times an orm would've made that slightly easier. Not worth an entire abstraction for such a rare and impractical feature.

If what you say is true and it's not rare, then I offer you the concept of a graph database that's designed for graph algorithms. That is the "best tool for the job" rendering the orm not the best at anything.

(I edited this from "your logic" to the "logic you present" not making any remark on your intelligence here hope it doesn't come off that way)


Even if your technical point is true, "right tool for the job" goes beyond the rigidity of the technical.

I use an ORM because it is cheap, easy, quick, uses a known quantity database which is readily available and deployable and allows me to solve my business case with relatively low complexity exposure and risk.

Best tool for the job. Best, as subjectively defined by me, for my needs as I see them in this un-black-and-white world.

Idealism is the enemy of done and I care about done (with proper engineering constraints) much more than "right". I care even less about trying to define a vague approximation of "right" when there's things to get "done" and the ORM I use is tried and tested to get things done.

That's my use case. Done. That's my tool for that use case.

Everything else is balancing debt, risk and entropy. Right doesn't factor into it for me.

It is quite liberating.


>Idealism is the enemy of done and I care about done

Nobody is talking about idealism. It's all practical. What my claim is this: You are wrong. In both cases you can get things "done" but you'd get things done faster and more easily without the ORM, you just don't know it. That's the claim I am making.

Your claim is that my reasoning is based on theoretical ideals. But I never stated such a thing, so I am stating it now: The basis of my logic rests on practicality NOT idealism.

The funny thing is, SQL as strings is viewed as more "practical" given how inelegant and "un-ideal" string manipulation is. The ORM is more of the misguided attempt to reach an idealism through abstraction.


Do you have any patterns for this? Not OOP Design Patterns necessarily, but your preferred workflow patterns for managing complexity. For example (just examples!):

  * Apps (usually) only use
    (materialized?) view tables
  * Deployments are checked in as
    migrations
  * Aggressively leverage db features
    like caching or other specific
    performance features (not just
    indexing)
  * GraphQL to firewall backend devs from
    rapidly changing frontend "widget"
    requests




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: