9 ms·
Objection to ORM Hatred (2019)
- deleted 4y ago[deleted]
- kristiandupont 4y agoThat'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 ([link redacted]) 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.
- sally_glance 4y agoI'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!
- gmac 4y agoCheck out Zapatos too: https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/
- sally_glance 4y agoThanks, we will!
- seer 4y agoI 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 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.
- kristiandupont 4y agoThat 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?
- seer 4y agoI 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.
- kristiandupont 4y agoThat's impressive! I will try to see if and how I can incorporate your tool into my workflow.
- sally_glance 4y agoLooks 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 ;-)
- gmac 4y agoWould 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/ https://jawj.github.io/zapatos/
- kristiandupont 4y agoThey do indeed look very similar, yes!
- lcuff 4y agoMy 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.
- nextos 4y agoYeah, 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.
- isoprophlex 4y agoYes, 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.
- wvenable 4y agoI 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.
- ebiester 4y agoIs 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.
- monero-xmr 4y agoMy 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!"
- fzeindl 4y ago> 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.
- tehlike 4y agoORM 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.
- ozim 4y agoEspecially 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.
- yyyk 4y ago>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.
- KronisLV 4y agoI 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.
- rektide 4y agoI 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.
- throwaway231a 4y agoI 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.
- keithalewis 4y agoAre there any examples of ORMs that have been used for over 10 years that people don't hate?
- mo_po2 4y agoDjangoORM
- graypegg 4y agoRails activerecord? It’s got it’s quirks, but I feel like it’s pretty boring and predictable to use, in a good way!
- alkonaut 4y agoDapper https://github.com/DapperLib/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.
- thom 4y agoI 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.
- thunderbong 4y agoRuby's Sequel ORM - https://sequel.jeremyevans.net/ https://sequel.jeremyevans.net/
- plant-ian 4y agoSQLAlchemy: "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
- fckgnad 4y agoORMs 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.
- sirsinsalot 4y ago> 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
- fckgnad 4y agoIt'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.
- sirsinsalot 4y agoIs 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.
- serpix 4y agoI'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.
- indeyets 4y agoMy 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
- sirsinsalot 4y agoEvery sufficiently well implemented OO program that talks to an SQL database includes a poorly implemented ORM where one wasn't chosen.
- throaway2342 4y agoORM'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 https://github.com/launchbadge/sqlx
- pictur 4y agoMy 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.
- dragonwriter 4y ago> 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.