8 ms·

ORMs are nice but they are the wrong abstraction

38 points by iccananea 3y ago

mewpmewp2 3y ago
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.
- jovezhong 3y ago
  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(?)
- kaba0 3y ago
  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.
  - mewpmewp2 3y ago
    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.
    - OJFord 3y ago
      To be fair your 'only for some basic CRUD queries' is just a rephrasing of GP's 'they are for OLTP'.
      - mewpmewp2 3y ago
        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.
        OJFord 3y ago
        To be clear, ORMs are not my preference either: https://stackoverflow.com/questions/65596920/use-django-subquery-as-from-table-or-cte-in-order-to-have-a-window-over-an-aggre https://stackoverflow.com/questions/65596920/use-django-subq...
        mewpmewp2 3y ago
        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.
TacticalCoder 3y ago
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-the-vietnam-of-computer-science/ https://blog.codinghorror.com/object-relational-mapping-is-t...
- DrDroop 3y ago
  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.
kgbcia 3y ago
How about move the business logic to stored procedures
- lmm 3y ago
  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)
  - funcDropShadow 3y ago
    There is e.g. https://pgtap.org/ https://pgtap.org/ as a testing library for Postgres.
- leosanchez 3y ago
  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.
- blagie 3y ago
  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.
- hardware2win 3y ago
  Oh god, no, never. Painful to maintain, test, debug, etc.
- nunez 3y ago
  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.
jasfi 3y ago
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.
- blagie 3y ago
  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.)
  - jasfi 3y ago
    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.
feoren 3y ago
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.
- mrkeen 3y ago
  > 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.
  - feoren 3y ago
    > 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!
gmac 3y ago
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/ https://jawj.github.io/zapatos/. I should say I also like what I've seen of https://kysely.dev/ https://kysely.dev/ and https://pgtyped.dev/ https://pgtyped.dev/.
- ejflick 3y ago
  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.
- PH95VuimJjqBqy 3y ago
  > 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.
- iccananea 3y ago
  Agreed, but tools like https://sqlc.dev 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.
blagie 3y ago
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.
- brainlessdev 3y ago
  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.
  - blagie 3y ago
    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: ...
t00 3y ago
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.
mike_hearn 3y ago
The most interesting/fresh approach I've seen to this problem is Permazen. https://github.com/permazen/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.
- pjc50 3y ago
  .. but you can't really do queries on a K/V store?
  - mike_hearn 3y ago
    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.
- amadeuspagel 3y ago
  DPP (Deep Persistent Proxy Objects) is a javascript library starting from the same question: https://github.com/robtweed/DPP https://github.com/robtweed/DPP
hardware2win 3y ago
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
- selfportrait 3y ago
  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.
Taikonerd 3y ago
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/ https://www.edgedb.com/ It's a sort of "midpoint" between regular SQL and an ORM. And it's language-agnostic, unlike an ORM.
- 1st1 3y ago
  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.
ravenstine 3y ago
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.
nunez 3y ago
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
- OJFord 3y ago
  Complex SQL is when you need to avoid them - at a certain point you hit the limit of what the ORM thought to support.
ranedk 3y ago
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.
- iccananea 3y ago
  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.
cdelsolar 3y ago
sqlc sqlc sqlc. Use sqlc.
infinitezest 3y ago
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.

38 points by iccananea 3y ago

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.

jovezhong 3y ago
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(?)
kaba0 3y ago
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.
- mewpmewp2 3y ago
  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.
  - OJFord 3y ago
    To be fair your 'only for some basic CRUD queries' is just a rephrasing of GP's 'they are for OLTP'.
    - mewpmewp2 3y ago
      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.
      - OJFord 3y ago
        To be clear, ORMs are not my preference either: https://stackoverflow.com/questions/65596920/use-django-subquery-as-from-table-or-cte-in-order-to-have-a-window-over-an-aggre https://stackoverflow.com/questions/65596920/use-django-subq...
        mewpmewp2 3y ago
        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.

TacticalCoder 3y ago

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-the-vietnam-of-computer-science/ https://blog.codinghorror.com/object-relational-mapping-is-t...

DrDroop 3y ago
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.

kgbcia 3y ago

How about move the business logic to stored procedures

lmm 3y ago
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)
- funcDropShadow 3y ago
  There is e.g. https://pgtap.org/ https://pgtap.org/ as a testing library for Postgres.
leosanchez 3y ago
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.
blagie 3y ago
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.
hardware2win 3y ago
Oh god, no, never. Painful to maintain, test, debug, etc.
nunez 3y ago
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.

jasfi 3y ago

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.

blagie 3y ago
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.)
- jasfi 3y ago
  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.

feoren 3y ago

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.

mrkeen 3y ago
> 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.
- feoren 3y ago
  > 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!

gmac 3y ago

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/ https://jawj.github.io/zapatos/. I should say I also like what I've seen of https://kysely.dev/ https://kysely.dev/ and https://pgtyped.dev/ https://pgtyped.dev/.

ejflick 3y ago
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.
PH95VuimJjqBqy 3y ago
> 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.
iccananea 3y ago
Agreed, but tools like https://sqlc.dev 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.

blagie 3y ago

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.

brainlessdev 3y ago
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.
- blagie 3y ago
  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: ...

t00 3y ago

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.

mike_hearn 3y ago

The most interesting/fresh approach I've seen to this problem is Permazen. https://github.com/permazen/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.

pjc50 3y ago
.. but you can't really do queries on a K/V store?
- mike_hearn 3y ago
  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.
amadeuspagel 3y ago
DPP (Deep Persistent Proxy Objects) is a javascript library starting from the same question: https://github.com/robtweed/DPP https://github.com/robtweed/DPP

hardware2win 3y ago

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

selfportrait 3y ago
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.

Taikonerd 3y ago

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/ https://www.edgedb.com/ It's a sort of "midpoint" between regular SQL and an ORM. And it's language-agnostic, unlike an ORM.

1st1 3y ago
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.

ravenstine 3y ago

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.

nunez 3y ago

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

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

ranedk 3y ago

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.

iccananea 3y ago
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.

cdelsolar 3y ago

sqlc sqlc sqlc. Use sqlc.

infinitezest 3y ago

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.