ORMs are nice but they are the wrong abstraction | Hacker News Reader

ORMs are nice but they are the wrong abstraction

workdad.dev

38 pointsiccananea69 comments

The basic problem is that:

1) Relational databases are the best abstraction we've found for storing data. Despite years of attempts (OODB, XML databases, various nosql stores, etc.), we have not been able to improve on it. postgresql, by adding native JSON support, became a better mongo than mongo virtually overnight.

2) Most people never learn databases, and most people who do are idiots working on enterprise three-tier architectures, so mostly it's misused. There is an attempt to hide them.

3) ORMs generally make easy stuff easy, and hard stuff painful.

What I generally want is:

1) Something translating SQL syntax into my native language, but maintaining SQL full semantics and expressiveness.

2) This should allow me to be database-agnostic.

3) This should prevent things like injection attacks.

4) It should not map onto objects. It should maintain 100% of the capability of SQL. The only differences should be syntactic (e.g. instead of writing WHERE, I might write .where() or similar).

5) This may and ideally should have added functionality, for example, around managing and organizing database migrations.

Stored procedures and virtual tables are also very important (but poorly implemented in most databases). These:

1) Allow proper abstraction at the SQL level.

2) Improve performance.

What most programmers fail to understand -- since universities don't teach -- is how powerful and elegant the underlying theory of databases is.

Can it be database-agnostic and 100% expressive at the same time? Perhaps expressiveness is different depending on the engine.

SQLx comes close to this btw.

Yes.

The basic data model was invented a half-century ago. Expressiveness of basic SQL is not different depending on the engine. Relational algebra is the same however it's implemented.

Some databases have extensions, and those are okay to include. You should be able to either (1) choose to not use those or (2) lock yourself into a subset of databases which support the extension you need.

It's good if those extension were namespaced. For example:

* If I want to use a postgres JSON type, it should live under postgres.json (or if it cuts across multiple databases, perhaps extensions.json or similar)

* Likewise, database built-in functions, except for common ones like min/max/mean, should be available under the namespace of those databases.

* It's okay if some of those are abstracted out into a namespace like extensions.math, so I can use extensions.math.sin no matter whether the underlying datastore decided to call it SINE, SIN, SINE_RADIANS, SIN(x/180*PI), and if it doesn't have sine, an exception gets raised.

The basic relational data model provides the best expressiveness for representing data and queries created to date, and doesn't differ. It's a good theoretical model, and it works well in practice. There's good reason it's survived this long, despite no lack of competition. The places expressiveness differs are relatively surface things like data types and functions.

It's also okay to have compound types, where I am explicit about what the type is in different databases. e.g.: string_type = {mysql: 'TEXT', postgresql: ...

> It should not map onto objects

? To me, this is the main use of an ORM: don't let it do query building for you (unless it's fairly simple LINQ stuff), but just have something map the rows returned to typesafe objects for you.

Mapping rows returned from a query onto typesafe objects is okay, but doesn't require an ORM. It's quite literally just making sure each row:

1) Has the correct native type

2) Can be accessed by name

Many normal database APIs do that themselves. I guess there's a little bit more for writes.

The point of ORMs is that they generally map objects onto tables. You create a class, and the ORM will create the database table for you based on what you have in the class. For example, for Django, you write:

   class Blog(models.Model):
       name = models.CharField(max_length=100)
       tagline = models.TextField()

       def __str__(self):
           return self.name

(source: https://docs.djangoproject.com/en/5.0/topics/db/queries/)

And it will make the table for you. If you change the class, it will make the migration for you. That's clean, simple, and easy if you're not doing anything complex. It saves a ton of work.

However, this is a Very Bad Idea for complex systems, since exactly as you point out, there shouldn't be a 1:1 mapping between tables and classes. There may very well be a 1:1 or many:1 mapping between queries and classes. More sophisticated ORMs can do a bit of that, but at some point, you run into the equivalent of Greenspun's Tenth Rule, where the ORM turns into a more complex, buggy version of SQL.

Sounds like jOOQ?

https://www.jooq.org/

Note that although advertised as for Java, it really supports any language that can run on the JVM which is many of them these days.

I learned about database theory a few times (and even interned as a DBA before I started uni). But it wasn't until my discrete math class in university that I finally understood, "oh, that's why we call a table a relation, a row a tuple, etc", and from there - started understanding the whole relational model and why it makes sense.

I think ORMs however, became a thing more so due to fear of having to write (correct) SQL. It was meant to reduce complexity for developers but of course that's not really how it panned out in reality.

https://orm.drizzle.team/ Basically this ?

Drizzle does exactly what I described in the article: it re-implements SQL in the target language (in this case TypeScript).

But you have raw SQL operator and all drizzle operations does is to build you the SQL query.

Well other orms do that as well ofc. But I feel this is a low level as it can get

https://GitHub.com/masterminds/squirrel sounds like what you want, in Go at least. They bill it as a SQL query builder instead of an ORM.

It lets you build SQL queries using code and just returns the built SQL query as a string, the list of variables to pass into the query, and an error if your query was invalid.

Actually executing the query and doing stuff with the result is explicitly outside their scope. I typically use it with sqlx with a struct per query, just to avoid writing tedious row iterators.

Makes it super easy to eg define a function that adds paging to a query and returns the paged query. You can even write a function that takes an HTTP request, reads out standard paging parameters and adds them to a query.

> Most people never learn databases, and most people who do are idiots working on enterprise three-tier architectures

Tell me how you really feel :)

My biggest problem with ORM's is that it causes people to sprinkle the ORM code throughout their codebase but they'd never do that with SQL, they'd want to try and sequester it to a system whose responsibility is the retrieving and updating of data.

It puts people into a poor mindset around their data.

That's like saying: my problem with Functions is that people sprinkle them throughout their codebase, instead of having one single place where all their Functions are defined.

For most applications, "Retrieving and updating data" is the entire point, or at least a deeply fundamental part of how they work. It shouldn't scare you that such a core function -- the entire reason your application exists -- is "sprinkled around" your codebase. It should scare you if it isn't! I think the reason it scares you is because you imagine the performance nightmare of every random function possibly hitting the database N times. That's using an ORM terribly. Good ORMs let you plan database actions, and compose those plans.

Guys, you really, genuinely, don't have to choose between (a) writing SQL strings in code, and (b) using an ORM badly. You can truly do better!

putting repeated data requests behind a function is what I'm suggesting...

sprinkling your ORM code all throughout your codebase is the equivalent of NOT using functions but instead copy/pasting the implementation everywhere it's needed.

Most ORM's recognize this is a problem, which is why they often try to bake in some sort of solution for re-use, only it's always done badly. They'll generally attach it to the model and it will turn into some sort of unobvious behavior or they'll attempt to be able to attach SQL fragments by name. What it ends up doing is effectively spreading an SQL query over several files. Often by convention so there's no real way to know if part of the query is going to be in file X without just checking or knowing ahead of time.

I kind of disagree. If you're using an ORM, the ORM is the abstraction layer which should be sprinkled throughout the code. You shouldn't wrap a layer in a layer.

If that's the right abstraction, use an ORM. If it's not, don't.

If all you have are lists of items (a todo list, a set of employee records, a set of products), an ORM is a fine abstraction. If you have or expect to do something more complex, it's probably a bad abstraction.

> If you're using an ORM, the ORM is the abstraction layer which should be sprinkled throughout the code. You shouldn't wrap a layer in a layer.

preventing people with this belief from sprinkling ORM code all throughout the codebase is reason enough to ban the use of an ORM.

The specifics of how you retrieve data is an implementation detail. If someone wants to use an ORM have at it, but don't sprinkle it throughout the codebase, place it behind well defined functionality (behind a system dedicated to pulling data).

The fact that ORM's have their own query language should be all you need to know.

One could easily argue that you should be able to manually interact with db readers all throughout the codebase, after all, it's an abstraction.

But no one would ever actually argue that. Like such forward-only db readers, ORM's are a way to pull data, they are not the abstraction layer for pulling data for your application.

> The specifics of how you retrieve data is an implementation detail.

Correct.

> (behind a system dedicated to pulling data).

That's the textbook definition of a good ORM.

> One could easily argue that you should be able to manually interact with db readers all throughout the codebase, after all, it's an abstraction.

It depends on the abstraction they present. SQL throughout the code would be a train wreck. On the other hand, something like:

* A stored procedure to have a filtered list of gizmos...

* Mapped onto an AJAX or a function call in a data layer...

* Being called whenever one needs a filtered list....

is the way to do it. If all you have are tables of simple data, that's exactly what a good ORM would do.

right, I gathered from the first inane response that you believe anything that pulls data is an ORM.

That's never been how the acronym was defined. The reason you're now trying to define it that way is because the only other alternative is to admit you're wrong.

"what if that subsystem is pulling data from AS400?" -- still an ORM!

"what if that subsystem is pulling data from an INI file?" -- still an ORM!

"what if that subsystem is pulling data from an IOT device? via the MQTT protocol" -- still an ORM!

------

The difference between an ORM and what I'm describing is that the subsystem I'm describing actually abstracts the where and the how, an ORM _is_ an implementation detail. This is why sprinkling ORM code throughout your codebase presents a problem, it's akin to re-implementing the code to pull out of an INI repeatedly instead of putting it behind a function with a single implementation.

Now go away.

My best experience integrating with a Postgres database was using Clojure with the Honey SQL library [1]. Essentially Honey SQL operated solely as a query builder library allowing me to express queries as regular Clojure EDN maps and vectors (comparable to objects and arrays in JavaScript), or nested function calls returning the same kind of data. In essence SQL queries were expressed in a Clojure native data format which finally could be transformed into SQL syntax.

The huge win was that I could use the Clojure REPL to run my SQL-generating functions and see that the SQL actually matched what I was aiming for. Caching the query-building was as simple as memoizing the query building functions as queries are parameterised in any case, so can be reused.

There is something really nice about having the full power of SQL at your fingertips. The only drawback compared to ORMs of course is that it outputs the resulting query result in a flat structure. So any aggregation you'd need to handle in code or use something like Postgres JSON aggregation.

[1] https://github.com/seancorfield/honeysql

> What I generally want is:

> 1) Something translating SQL syntax into my native language, but maintaining SQL full semantics and expressiveness.

You have just described a good ORM used well.

> 2) This should allow me to be database-agnostic.

Meh, you sacrifice some powerful features if you demand total database agnosticism, and how often do you actually switch databases? Being database-agnostic is a side benefit of writing your logic simply against a good abstraction. The biggest benefit is composability. You can write (and optimize) one query against abstractions, and re-use that query in lots of different ways.

> 3) This should prevent things like injection attacks.

As all ORMs automatically already do.

> 4) It should not map onto objects. It should maintain 100% of the capability of SQL.

If it's not mapping onto objects, what is it doing? The problem is that your mental model of what "objects" means includes awful design decisions like deep inheritance trees, mutability, and lots of reference cycles (this.parent.child[0] == this, etc.) If your object model is already clean and following relational principles, then mapping into that model is exactly what you want.

It should not strive to maintain the capability of some bastardized pseudo-language which is despised by the progenitors of relational logic. It should strive to support the relational model. That's not the same thing.

> 5) This may and ideally should have added functionality, for example, around managing and organizing database migrations.

No, because your code versions and your database schemas advance together. To reliably run a database migration in code, you'd need to run it with the code version that exactly matches each step in the schema. That means for each step in the migration, you'd need to checkout the correct commit in git, compile, and run the ORM code in that version. Either that, or you're maintaining a code model that is compatible with every historical database schema, which is way worse.

But what ORMs should be able to do (and I haven't found one that does this well) is generate SQL migration scripts for you, which you store. Those would be frozen relative to the database schema version, so all the above problems go away.

> What most programmers fail to understand -- since universities don't teach -- is how powerful and elegant the underlying theory of databases is.

The underlying relational model is powerful and elegant. SQL itself is not. SQL is disliked by the founders of the relational model. Good ORMs let you incorporate the relational model into your code.

> You have just described a good ORM used well.

No. I did not. ORM is an "object–relational mapping." It maps data relations onto objects.

https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapp...

> how often do you actually switch databases?

Very often. Virtually all of the systems I write have at least two back-ends to maintain that flexibility. At the very least, I'd like my systems to run well locally for development but also to scale. The easiest way to do that is to e.g. support both SQLite and Postgres, but there are other ways which make sense.

In proprietary settings, I like having a BATNA. The architectural flexibility means I get better prices on hosted services and aren't liable to turning into a cash cow through lock-in. That's savings even if I'm not switching.

> If your object model is already clean and following relational principles, then mapping into that model is exactly what you want.

This is where your thinking broke. A good object model is NOT a relational model, and a good relational model is NOT an object model.

Learn the theory of both. They're both good theories, but they're different.

An ORM makes sense if you want to use an object model, but want the backing store to be an RDBMS.

> But what ORMs should be able to do (and I haven't found one that does this well) is generate SQL migration scripts for you, which you store. Those would be frozen relative to the database schema version, so all the above problems go away.

I believe that's one instantiation of what I wrote: "This may and ideally should have added functionality, for example, around managing and organizing database migrations." It's actually exactly what I was thinking.

Some ORMs do this not badly, actually. Don't let the perfect be the enemy of the good. A simple system which does 90% of the work of generating a migration (with manual verification and tweaks) is often better than a complex one which tries to do 100% of the work.

> The underlying relational model is powerful and elegant. SQL itself is not. SQL is disliked by the founders of the relational model.

Citation required.

In either case, ORMs aren't translating just syntax, but also semantics. That's where the problem lies. If you're not doing that, you're not an ORM.

> Good ORMs let you incorporate the relational model into your code.

You're confused about what an ORM is. ORMs essentially map an RDBMS onto an object model (which an OODBMS does natively). The two models are fundamentally different. It's a translation layer.

Any good database library will let me "incorporate the relational model into my code." That's not an ORM.

You're being condescending, telling me to "learn the theory". We've clearly had different experiences and work with different kinds of systems. You're automatically discounting what I say by assuming I must be a newbie because I disagree with you. No. We both have things to learn from each other and you telling me "go RTFM, newbie" is shutting down that opportunity.

> Virtually all of the systems I write have at least two back-ends to maintain that flexibility.

It sounds like that's a worthwhile tradeoff for you, but it is a tradeoff, giving up some of the unique power of each individual database in order to support both. Realize that most people don't do this.

> A good object model is NOT a relational model, and a good relational model is NOT an object model.

Maybe this is our biggest disagreement. I believe there is a model that can get the biggest benefits of both. I'm not alone in this; papers in Software Engineering, "Out of the Tar Pit", prescribes this as a possible solution to a lot of woes in software engineering:

"The classical ways to approach the difficulty of state include object oriented programming which tightly couples state together with related behaviour, and functional programming which — in its pure form — eschews state and side-effects all together ... We argue that it is possible to take useful ideas from both and that — when combined with some ideas from the relational database world - this approach offers significant potential for simplifying the construction of large-scale software systems"

https://curtclifton.net/papers/MoseleyMarks06a.pdf

I agree with them: Object Oriented Programming alone is nice for UIs but otherwise has failed to live up to its hype; Functional Programming is elegant and pure but hard to actually get anything done with; the Relational Model is simple and powerful but writing an application entirely in database procedures is a Lovecraftian Horror. So look for a model that takes the best from all three, and apply that model -- at least conceptually -- in both your database design and your application code. It is actually possible, and it's wonderful. But it will piss off the zealots on both sides.

I'm not sure what you consider "a good object model", but there is very little agreement in our industry on what exactly that looks like. If yours permits the CS 101 inheritance examples like "Dog : Animal" then I'd strongly disagree with you. Or maybe yours is the Smalltalk message-passing version: better, but still not immune to improvement. Don't assume that everyone who doesn't 100% line-toe bog standard OOP and bog standard SQL RDMSes needs to "RTFM".

>> SQL is disliked by the founders of the relational model.

> Citation required.

"SQL isn’t just user hostile, it involves some very serious departures from relational theory ... Suffice it to say that those departures are so serious that I honestly believe SQL has no real right to be called relational at all." -- Chris Date, who worked closely with EF Codd and helped spread his ideas.

https://red-gate.com/simple-talk/opinion/opinion-pieces/chri...

Citations appreciated! I will not respond in-depth to most parts, because I agree with what you're writing. But I will make one or two points. To clarify a piece of confusion:

> Maybe this is our biggest disagreement. I believe there is a model that can get the biggest benefits of both. I'm not alone in this; papers in Software Engineering, "Out of the Tar Pit", prescribes this as a possible solution to a lot of woes in software engineering:

I don't necessarily disagree, Moseley does not describe an OO model. If you showed their proposal to someone who does e.g. just Java, they would puke.

> I'm not sure what you consider "a good object model", but there is very little agreement in our industry on what exactly that looks like

A more accurate statement is that there are many models. The very first one I learned formally was the Booch method, over a quarter-century ago. My productivity fell about tenfold when I began applying it. Second one was Java. Less than tenfold. Like you, I eventually rejected OO for most things other than UX, or relatively small objects which are more-or-less little more than data types.

-----

But onto the main point:

- Models have theoretical properties which work well in isolation.

- Mixing models often destroys those properties.

- For example, I can slice code vertically or horizontally. Either works well. If I mix the two, I have no abstraction or modularity left.

- Java got rid of MI for a reason. It's not that MI is bad, but it doesn't fit into the Java OO model.

- If I add a little bit of OO to functional, I get mutation, and almost all of the benefit disappears in a poof. (note: There are systematic ways to do this which maintain benefits of both)

- Aside from theoretical properties, models are a way to communicate. You know what to expect in code.

You can design hybrid models, but you can't just mix models. Code kinda falls apart. None of the OO models map cleanly onto relational, or vice-versa. It's possible to do other models that perhaps combine aspects of both, but they're no longer OO.

An ORM is a bastardization of the two models, by trying to mesh them together. In contrast, I like Moseley Marks a lot, which is it's own model.

-----

And a footnote: I no longer find it hard to get things done with functional programming. Most of my code is pretty close to pure functional, with a few well-controlled places with state (on the front-end, with Redux). Part of that is experience on my part, but part of that is that both Python and modern JavaScript surface many of the most useful aspects of functional in ways which are linguistically nice.

-----

As a second footnote: A lot of this is based on domain. For example, numerical code works great in functional, while as you pointed out, UX maps well onto OO.

The most interesting/fresh approach I've seen to this problem is Permazen.

https://github.com/permazen/permazen/

It starts by asking what the most natural way is to integrate persistence with a programming language (Java in this case, but the concepts are generic), and then goes ahead and implements the features of an RDBMS as an in-process library that can be given different storage backends as long as they implement a sorted K/V store. So it can sit on top of a simple in-process file based K/V store, RocksDB, FoundationDB, or any SQL database like PostgreSQL, SQLite, Spanner, etc (it just uses the RDBMS to store sorted key/value pairs in that case).

Essentially it's a way to map object graphs to key/value pairs but with the usual features you'd want like indexing, validation, transactions, and so on. The design is really nice and can scale from tiny tasks you'd normally use JSON or object serialization for, all the way up to large distributed clusters.

Because the native object model is mapped directly to storage there's no object/relational mismatch.

.. but you can't really do queries on a K/V store?

Sure you can. In Permazen they're expressed as set operations over collections of objects, so you use map, filter, fold etc as you would if programming functionally. Indexes are also exposed as collections.

Reads and writes on those objects are then mapped to K/V operations.

DPP (Deep Persistent Proxy Objects) is a javascript library starting from the same question:

https://github.com/robtweed/DPP

ORMs are useful when what you want to do matches their expressiveness. For example when I just want to get a record by its primary key. They have their limits, many times when doing complex reports. For that, most ORMs provide a raw SQL function. So just use the right tool for the job.

Stored procedures for business logic can be great for performance when they replace queries/mutations called thousands+ times.

ORMs are nice for "Hello World," and hit a brick wall later.

I don't mind them for simple systems, but for more complex systems which need SQL, don't use an ORM. Mixing ORMs with raw SQL generally mixes and breaks layers of abstraction. This leads to a situation where the overall system is more complex than having a sane relational abstraction. For example, ORMs do a lot implicitly. A code change at the language level, with something like the Django ORM, can and will break your SQL code. Your data logic is also split across two places.

The only time I've seen this work is for SQL for one-off analytics done at a command line, where the SQL code is never intended to be reused.

EITHER:

- Use an ORM, if you are building something simple like a todo list or a basic eCommerce web site; or

- Use full SQL if you are doing something more complex (e.g. if you ever expect to do analytics); or

- If your programmers don't understand SQL, consider whether you want an ORM, a nosql, or to find programmers better matched to the problem domain.

(Footnote: Many good programmers don't understand SQL; that's okay. They just shouldn't be designing databases, any more than e.g. database experts should be designing machine learning algorithms, or machine learning experts should be designing front-end user interfaces. They're different skill domains, and they're all equally important. The key thing is people should know their skills. Otherwise, you'll get an unusable UX, a regression as our ML model, and a broken schema. This is especially true for something which looks simple on the surface but has deep theory behind it. ORMs make it look easy.)

Then you'd never use an ORM, because you might require something an ORM can't handle at some point. I don't see why a coder couldn't handle a mix of both. The other factor is that ORMs tend to add features over time. Something that would require raw SQL today might be more elegantly handled with an ORM's language two years from now.

You missed the point entirely.

Language like "require raw SQL today might be more elegantly handled with an ORM's language" suggests that you do not understand the elegance of SQL or the relational data model.

However, you are correct. I used ORMs less and less often as I became a better SWE. They can save time on toy projects, but for those, I usually use a NoSQL or KVS directly.

I understand SQL and the relational model very well. But once I start using an ORM I want to keep using it for consistency, unless it's not the right tool for the job. Complex queries are usually the reason. Even if the ORM can technically do it, sometimes SQL is just more elegant, faster to write and I can be sure of doing it right.

If you don’t know SQL, you shouldn’t touch ORM either, no one ever said that you can.

Also, it’s pretty reductionist to say that it is only good for simple systems, when in fact, huge services are running it at almost all companies.

Here's a 2006 blog commenting on (and agreeing with) a blog entry from 2004 which I vividly remember: "ORM is the Vietnam of Computer Science"...

https://blog.codinghorror.com/object-relational-mapping-is-t...

At this point it is more accurate to call ORMs the Afghanistan of Computer Science, you where warned that it was going to be like Vietnam and somehow you managed stayed there even longer.

Too much ORM hate in this thread

They are really useful for like 90% of the work

The rest can be made with raw sql

Combine strengths of those two powerful tools, dont be religious

This here. If you follow Prisma ORM on GitHub, some of the pain you’ll see is missing features like “whereRaw”, but really most of the pain is forcing you to use raw SQL. And even then, Prisma is extensible, so build your own solutions on top of it. Like Zenstack which creates auth/role permissions on the Prisma schema.

Way too much ORM hate here.

Couldn't agree more and I just recently had few comments on the topic.

Everytime I use ORMs I feel frustrated by the capabilities compared to raw sql.

I feel handicapped in being able to have the data in a way I want.

SQL is so flexible. When I put myself as a user, I also prefer those tools that provide nice UI for filter/reporting, but also give me SQL interface to do advanced query, such as PostHog, Resmo, JIRA(?)

ORMs never block you from issuing raw SQL queries. But mapping the results to entities inside your programming language’s abstraction is a repetitive task, ready to be abstracted away. The reverse direction is the same way.

I feel most of the criticism of ORMs come from people who don’t actually know how to properly use one. They were never meant for OLAP, they are for OLTP.

Maybe I don't know how to use one properly, but I have used them for years. For me however tech should be learnable quicker to be beneficial.

Now I only use ORM really only for some basic CRUD queries, if that.

To be fair your 'only for some basic CRUD queries' is just a rephrasing of GP's 'they are for OLTP'.

But one issue with ORMs is also that they can bait you to try more complex queries, where eventually you might run into one slight edge case that you spend huge amount of time finding a solution for because reverting to raw SQL will not feel elegant at that point - and feels like you've failed in some way. So you might run into these edge cases and then also you might have terrible joins without really knowing. Also different ORMs have different APIs and capabilities, which means more time learning those things and being uncertain whether this particular ORM even supports what you want to do.

I think generally a lot of time will be spent in analysis paralysis and overthinking. Is this query doable with ORM? How long should I google? How far into docs I have to go, do I need to go to ORM source code to figure out how to implement this? If it's a project with other developers, then will they disapprove of me using raw SQL here, and giving up on trying to go for an elegant ORM solution.

To be clear, ORMs are not my preference either: https://stackoverflow.com/questions/65596920/use-django-subq...

Yeah, nice example, just overall I feel like I've spent more time on edge cases/not knowing syntax top of my head with ORMs compared to if I just went with raw sql. Especially if working with different languages, each ORM handles syntax slightly differently and it messes up muscle memory.

I still automatically generate types from the database table and use helper fns when I need them to do certain type of abstraction.

And if you only need CRUD/ORM basic functions, maybe why even need a relational database. Although I would still go with relational as my first choice even if I start out with only simple CRUD, just for future's sake, so maybe not a good point.

In an ideal World there should be some sort of type parser for an sql query though.

And first class support to analyze the SQL query within the IDE (e.g. you make a syntax typo in an sql string or expose a potential sql injection vulnerability), an automatic linting or IDE tool would alert you of it, but at the same time a mechanism to generate response type if creating a parser for the compiler/build tool/IDE doesn't seem enough.

Sometimes I do end up inventing my own "ORM" with helper fns and objects, but I still feel more confident about using this one as I know that I can get exactly as flexible as I want.

The DB/ORM mismatch is worse than it has to be, because SQL queries always return flat rows. But in code, we don't usually want rows; we want objects linked to other objects: "get me the Account objects and their associated Wishlists."

If you use a query language that knows that we want objects linked to other objects, you can still have an ORM, but it doesn't have to do as much heavy lifting. The query can already specify the objects and properties you want explicitly, so you don't need to worry about, "which properties of which objects do I flesh? Am I over-fetching?"

That's why I've become a booster of EdgeDB: https://www.edgedb.com/ It's a sort of "midpoint" between regular SQL and an ORM. And it's language-agnostic, unlike an ORM.

Yeah, that's the exact goal of EdgeDB -- unlock proper hierarchical data retrieval and mutation as well as making composition possible both at the schema and query layers. Most other perks EdgeDB has are the direct result of that.

I dislike ORMs because they make big promises in exchange for giving up comtrol. I know this is one of those topics where someone may tell me I'm wrong and that I should be using some other ORM that truly does everything right. In my experience all ORMs have shortcomings and ultimately get in the way more than just writing SQL and constructing objects yourself, even though they can save time upfront.

How about move the business logic to stored procedures

Writing my business logic with no libraries, no standard testing framework, and a syntax and overall feel comparable only to COBOL? No thanks.

(Some databases allow writing stored procedures in other languages, true, but that means a nonstandard interface, tightly coupled deployment model, and testing is still awful)

There is e.g. https://pgtap.org/ as a testing library for Postgres.

My first employment did this. All the business logic was in stored procedures. These procedures quickly grew bigger and bigger and were nightmare to understand and test.

This would be the right way to go if:

1) Databases had a consistent language for storage procedures

2) It was sane

The problem is that stored procedures work differently in every database, and most have come up with crazy / stupid ways to do them. That's a fixable problem, but it has not been fixed.

Oh god, no, never.

Painful to maintain, test, debug, etc.

that's also nightmare enducing since sql is great for interacting with data but less great at expressing how that data should be stored and the relationships within the data. this worsens quickly the more complex the business logic gets.

stored procs also tie you to that database engine, which could be bad if, for example, you're paying a fat stack of cash to oracle and they come back asking for more.

i think a business logic api in a programming language that leverages raw SQL is the best middle-ground IMO.

I know tons of Java and Go Dev's who don't like ORMs and for a good reason. But no Django developer ever cribs about ORMs.

Ironically I started drafting this article in 2021, when I had to deal with a Django codebase littered with nested N+1 queries due to using the ORM in the most pythonic way.

ORMs are very similar to the back and forth on using ChatGPT/Copilot for coding; they definitely help make it easier to leverage a database and model data within an application, at the cost of becoming further decoupled from SQL and how the data actually lands into the database.

i personally avoid them, but i don't often write complex SQL

Complex SQL is when you need to avoid them - at a certain point you hit the limit of what the ORM thought to support.

I use ORMs all day every day on very large projects. Its painful sometimes and some are less painful than others. When I've tried for using raw SQL even for personal projects, it was much more painful. Right tool for the job etc.

ORMs suck, but raw SQL embedded in your code sucks too.

This might be good time to plug my Postgres/TypeScript non-ORM: https://jawj.github.io/zapatos/.

I should say I also like what I've seen of https://kysely.dev/ and https://pgtyped.dev/.

I've found embedding raw SQL into my code has been beneficial in two ways:

1. Hot reloading code. As a Java developer, this is important for prototyping as I can iterate quicker.

2. Clarity. I can read exactly what's going on and I know exactly what query is being executed.

> ORMs suck, but raw SQL embedded in your code sucks too.

_strongly_ disagree and I bet anyone who has this opinion can't articulate a good, concrete, reason for it. The best they're going to come up with is FUD about changing table names, which is one of those theoretical things that very rarely manifests itself in reality.

Agreed, but tools like https://sqlc.dev, which I mention in the article, are a good trade-off that allows you to have verified, testable, SQL in your code.

TLDR: Use tools like sqlc (GO), dapper (.net) etc to hydrate and check syntax using already validated SQL, instead of being limited to ORM quirks and narrowed down query syntax.

But...

The actual main problem of ORM is not just ORM but a multi-layered translation of target language query code

  - to internal target language database schema modeled from/to a db schema
  - then to SQL syntax
  - then to wire request to db
  - then to SQL validator in db
  - then to internal database query planner targeting relevant schema elements
  - then to raw index/hash/scan executors

You see the picture? If the database schema wire protocol could only be converted to the target language objects without SQL translation sitting in the middle, query would run optimally and with up-to-date statistics to query planner.

Think of WASM-like protocol (as opposed to JS) to which queries are compiled in the client and passed to the db.

It has been done before on a basic key-value wire protocols and to an extent on graph databases with json requests. Structured relational data is making this hard to do as one would need to have up-to-date db stats in the client along with all index details to run an optimal query.

On the other hand, compiling this:

  db.Posts.Get(p => (p.Authors.Includes(a => a.IsBoss))
    ? { Date: p.Date, Bosses: p.Authors = p.Authors.Filter(a => a.IsBoss), Groups: p.Groups.OrderBy(x =>   x.Name).Top(5) }
    : { Date: p.ModifyDate, Bosses: null, Groups: p.EmployeeGroups.First() }
  )

Into SQL would be close to impossible in an optimal way. Alternatively, with schema access in the client it, one could write internal db "assembly language" procedure for looking up using indexes, conditional querying and spitting out binary result for minimal effort hydration.

Just an idea, I am not aware of similar client query planner solutions available.

Every time I see a post talking about how bad ORMs are, they're using ORMs in an awful way. It's not really their fault: most ORMs seem like they encourage the worst ways to use them. But they're entirely missing the point: SQL doesn't have any form of abstraction at all. It's completely impossible to write reusable, adaptable SQL. All SQL must be specifically written bespoke for its individual use. Sure, you can rely on Views (or Procedures if you really hate your company and want to write the core logic of your application in a terrible, unmaintainable language), but that's relying on a concretion, not an abstraction. Abstraction would mean that you can change the behavior of a query by changing its input views when you call it, from some set of possible input views that conform to the expectations of the query. ORMs let you do that, SQL doesn't. Stop using ORMs badly and start asking how they can support you better organizing your queries by using abstraction.

> SQL doesn't have any form of abstraction at all. It's completely impossible to write reusable, adaptable SQL.

SQL is the abstraction. It's a high-level DSL which saves you from the low-level details of an ORM.

Unfortunately it came out 20 years before the first ORMs, so people default to thinking that ORMS must be an improvement over SQL.

> SQL is the abstraction.

SQL is an abstraction in the same way any programming language is an abstraction, but that's beside the point. Within the programming language itself, some support abstraction better than others (and lots of different varieties; it's not well-ordered). SQL basically just doesn't.

> It's a high-level DSL

I agree, but tell that to the people who write the core logic of their entire application in Procedures: that's not very specific. They're using the wrong tool for the job.

> which saves you from the low-level details of an ORM.

This doesn't make sense. The abstraction that SQL is over is the low-level details of how the database query engine works, not an ORM. And an ORM is not "lower level" than SQL. An ORM is just a different abstraction, one that tries to fit more readily into your application language than building SQL strings does. Building SQL strings directly is awful. Get yourself an ORM that allows composition of queries using the abstraction tools available in your language.

It's wild to me that this idea is so controversial that I literally get downvoted for suggesting it, when it's very obviously way better if you try it (or even seriously think about it). It's just an example of how deeply religious database developers are. I don't think any other software engineering sub-discipline is as intolerant of disagreement as database developers.

> people default to thinking that ORMS must be an improvement over SQL

It's not a default judgement. It's 15 years of experience, and tasting the fruit of an ORM done really well. It's also the opinion of the original progenitors of the relational model: EF Codd, Chris Date, etc. (At least, that there vastly better relational abstractions than SQL.) To all you overly religious database developers: guys, your prophets didn't even like SQL!

sqlc sqlc sqlc. Use sqlc.

Reply on news.ycombinator.com