22 ms·

Our journey in dropping the ORM in Go

107 points by henvic 5y ago

bborud 5y ago
I tried to use ORMs in Java years ago, but realized that while it makes simple things simpler, sooner or later you will need to do something that the ORM can't do simply. Or you will bang your head against the fact that you are trying to marry object graphs to relational models and you'll burn a lot of CPU, IO and memory in the translation. I have to admit I've been against ORMs ever since. You end up having a weak grasp on what is happening under the bonnet. You can make much better use of your database, and understand what is going on a lot better, if you let the database be the database. My approach is to forget about the database, and focus on what the application needs from the persistence layer. Then design an interface for the persistence. Then I implement that interface for each kind of persistence backend I need (SQL, files, memory, etc) while maintaining a single set of unit tests and benchmarks that are written against the interface. This isn't new. A lot of applications and libraries do this. And I think it is a good way to design things. Usually the database I use to develop a SQL schema is Sqlite3, since it allows for really nice testing. Then I add PostgreSQL support (which requires more involved testing setup, but I have a library that makes this somewhat easier: https://github.com/borud/drydock https://github.com/borud/drydock). (SQLite being in C is a bit of a problem since it means I can't get a purely statically linked binary on all platforms - at least I haven't found a way to do that except on Linux. So if anyone has some opinions on alternatives in pure Go, I'm all ears) In the Java days JDBC every single method implementing some operation would be a lot of boilerplate. JDBC wasn't a very good API. But in Go that is much less of a problem. In part because you have struct tags, and libraries like Sqlx. To that I also add some helper functions to deal with result/error combos. Turns out the majority of my interactions with SQL databases can be carried out in 1-3 lines of code - with a surprising number of cases just being a oneliner. (The performance hit from using Sqlx is in most cases so minimal it doesn't matter. If it matters to you: use Sqlx when modeling and evolving the persistence, and then optimize it out if you must. I think I've done that just once in about 100kLOC worth of code written over the last few years). And best of all: I get to deal with the database as a database. I write SQL DDL statements to define the schema, and SQL to perform the transactions. I don't have to pretend it is a object model, so I can make full use of the SQL. (Well, actually, I try to make do as far as possible with trivial SQL, but that's a whole different discussion). The interface type takes care of exposing the persistence in a way that fits the application. (Another thing I've started experimenting with a bit is to return channels or objects containing channels instead of arrays of things. But there is still some experimenting that needs to be done to find a pleasing design)
- 62951413 5y ago
  In Java there's another popular alternative to ORM/Hibernate, JOOQ (https://www.jooq.org/ https://www.jooq.org/). Which is basically type-safe SQL. The idea is to * start from an existing relational schema * run a CLI tool to generate Java code that represents all the tables/fields, * write queries in the application code that look like ".select().from(AUTHOR).join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)).fetch();" So you don't need to mess up with strings anymore or learn any DSLs. Your queries look exactly like regular SQL and so are very readable. This approach is well aligned with schema migrations represented as SQL scripts too. But not everyone is comfortable with using code generation.
  - bborud 5y ago
    SQL is actually pretty okay for writing SQL. I was never in love with this rather roundabout way of doing things since it didn’t solve problems I was actually having.
hk1337 5y ago
I feel like the biggest problem with an ORM is how people try and use them. It’s like an all or nothing approach. You hit a wall where using an ORM for that sucks and so now using an ORM for everything sucks. I never would have thought of using an ORM in golang, it doesn’t seem like that kind of language. Even an ORM in python is debatable.
- bigmattystyles 5y ago
  I use them for the really simple stuff, where I basically know what the query to be generated is. What I don’t understand though, what is wrong with writing a sprocket or sql function and invoking it as a method from your oo layer? That gets to 98% of my usage and it’s fine.
marcus_holmes 5y ago
So frustrating. You got like 80% of the way there, and then went "nope, too much work" and diverted to add more complexity. The answer is to write the SQL yourself, and the scan methods yourself. Code generation is better than ORM, but still a wrapper, still adds complexity, and still brings problems. Yes it's a pain in the arse to write all that boilerplate in one go (pun intended). But if you'd started without an ORM you'd have written them one at a time as you needed them and barely noticed it. Keeping your code aligned with your database schema is very little effort - database schema changes are usually rare and significant. I write a view for each access method (so I can change the schema without worrying about changing every access method), and a function for each update/insert/delete (for the same reasons). It takes maybe 20 mins to write the whole set each time I have to add a feature with new data, which is a rounding error in the time it takes to write all the rest of it. The point is that the database schema is optimised to storing data in the best way possible. The middle layer is optimised for processing data in the best way possible, and the front end is optimised for displaying data in the best way possible. None of these three things are equal. Use an interface between each of them. The interface is important and needs to be carefully considered.
- e12e 5y ago
  > I write a view for each access method (so I can change the schema without worrying about changing every access method), and a function for each update/insert/delete (for the same reasons). Database view and stored procedures?
  - marcus_holmes 5y ago
    yeah. Postgres has functions, SQL Server has sprocs.
- butMyside 5y ago
  What if there are no layers and you can’t think outside hierarchy? There’s no reason for anyone else to take your view as sacrosanct since many many many apps and services are working just fine without ORMs. You might accomplish something interesting today if you set aside protecting unimportant ephemera you talked yourself into believing.
cjdell 5y ago
In NodeJS Prisma is really good for just building statically typed CRUD-like queries. It gives you result types inferred from the schema so you're less likely to make mistakes. Would still use real SQL for serious stuff, but in my experience most queries are CRUD anyway so I don't see lightweight ORMs as a problem as long as they're not doing magic caching in the background and lazy fetching. Let me decide how I want to do that.
- topicseed 5y ago
  Using MikroORM in NodeJS and works wonders. Has the "raw" option for queries I want to handle myself, or even the "knex" builder for something in between
  - cjdell 5y ago
    Hmm... Looks like a "active record" pattern (like Hibernate). This is exactly what I don't want. Essentially I only use ORMs as query builders. No magic behinds the scenes.
    - topicseed 5y ago
      Author said it isn't[0] a year ago but not sure. I think it allows a couple of strategies to be used. [0] https://github.com/mikro-orm/mikro-orm/issues/403 https://github.com/mikro-orm/mikro-orm/issues/403
nauticacom 5y ago
Never understood the hate for ORMs. I need to map from my data storage to my domain model somehow, why write all that code myself
- rob74 5y ago
  Because relational databases are very good at (surprise!) relations. So in SQL you can query data from several joined tables, query only certain columns, aggregate columns etc. etc. And all of these things are made needlessly complicated by using ORM. Even with the best ORM, you still have to learn an arcane new syntax for stuff you could write in 30 seconds when using SQL...
  - nauticacom 5y ago
    Right, but for most of that stuff I can just write a one-off query, munge it into the ORM's objects and pass that off to my view for rendering. But probably 80% of my queries are "get this object by ID" or "get these objects and some related ones by a simple condition" or maybe a slightly complex condition. In these cases an ORM works wonders and has almost zero downsides
  - hello_moto 5y ago
    ORM has escape hatch so you can write SQL for the complex queries (and hopefully don't open yourself to SQL injection along the process) and still benefit from ORM for the simple to moderate case. > So in SQL you can query data from several joined tables Can do that in your ORM too. > query only certain columns So far no issue with ORM
- pionar 5y ago
  The hate isn't in the mapping. It's perfectly reasonable to abstract away mapping of relational data to objects. It's the translation of objects to SQL queries that's the problem. ORMs typically output inefficient and mostly obfuscated SQL, when a human can do a better job of hand-writing the SQL, knowing the data usage patterns needed.
  - 5y ago
dominotw 5y ago
i thought we'd all agreed 15 yrs ago to not use vietnam analogy to discuss orms.
- pixelgeek 5y ago
  What sort of ghoul compares a programming issue with something like the conflict in Vietnam? When he started writing about 15 million people dying you think that would have made him stop and rethink the metaphor.
  - erect_hacker4 5y ago
    This is standard fare on HN… Just remember that HN is not Reddit, that’s the most important thing.
  - erect_hacker5 5y ago
    This is standard fare for HN. Just don’t mention that HN is like Reddit - that’s off limits.
  - deleted 5y ago
    [deleted]
- ufo 5y ago
  The saddest part of the analogy is how US-centric it is. "Vietnam" is a boondoggle to exit from, not a home that's suffering through a bloody conflict.
- gunfighthacksaw 5y ago
  We have a new, shiny Afghanistan analogy to use!
- rjsw 5y ago
  The last reference to Vietnam that I saw in the MSM was that it had done a good job of handling COVID-19. Maybe the article is describing a good use of an ORM.
  - 5y ago
robertlagrant 5y ago
Some small points: > The ORM was a natural culprit: it’s easy to see that many ORMs will make use of object introspection to build SQL statements, hydrate results, or both. Gorm’s memory footprint is extreme although sadly not uncommon. Bridge started with Python on the backend and we used Django’s ORM to interact with the database which had similar problems. I believe these are both active record ORMs, which are not as good for various reasons (including default memory usage) as good data mappers such as SQLAlchemy and Hibernate. As for where enums etc are "mastered" - this is still a decision to make with or without ORMs. I think the main tricky bit of ORMs is that objects are trees and relations are graphs; this causes problems. Not so much anything about encapsulation etc.
dekhn 5y ago
This article would have been better without the unnecessary war analogy and history lesson.
- lioeters 5y ago
  Indeed the analogy has not aged well. It's in bad taste, and somewhat offensive, to hear the term "Vietnam of programming".
  - deleted 5y ago
    [deleted]
  - tommek4077 5y ago
    Why did it not age well? Are there any new findings regarding Vietnam?
    - quesera 5y ago
      To most people in the world, "Vietnam" is not a war, and is not merely a symbol of absurdly misconceived incompetence.
      - PostThisTooFast 5y ago
        Yes. The author should've said "the Iraq of programming."
  - tuan 5y ago
    Yeah, as a Vietnamese, this title definitely feels offensive to me. Maybe this article is written with American audience in mind. In Vietnam we call the war American war btw. Depends on where you are from, the title could have used the term "USA of programming" :)
- dmos62 5y ago
  The funny thing is that it references Vietnam, while the Afghanistan exit happened just a few months ago.
hinkley 5y ago
There’s two reasons a problem stays unsolved. Perhaps three if you believe Jim Highsmith (we are managing paradoxes). One, it’s unsolvable, like the Halting Problem. Two, it’s an XY problem and nobody is asking the right question. I’d been thinking recently that you could build a stupid simple ORM if you set up your SQL results to have the exact same names as your target object/struct and fed the type in as a parameter. Push these results into this box. But that’s not the functionality that sells ORMs. It’s the object graph. Mapping relational data to graphs, ORM or not, is quite messy. Maybe that says we should be using graph databases for this sort of data instead.
kevmo314 5y ago
As a junior dev I had no idea what an ORM was. As a mid-level dev I discovered them and wanted to use them everywhere. As a senior dev I've gone back to manually writing queries. Such is life.
- m0zg 5y ago
  Real fun begins if your team has all three stages of seniority. Typically it's somewhere in the middle, so your default is to use ORM, even though as a senior person you know everyone will profoundly regret this in the long run (and you will regret it immediately).
- hparadiz 5y ago
  I've gone past this and have a very clean and efficient query builder in my ORM. I maintain one for work and another is my open source PHP framework (15 years old now). Mine supports simple joins and relationships but anything beyond that requires writing custom queries.
  - unclebucknasty 5y ago
    How does your query builder work? Is it an API that you use to build queries during runtime or do you generate database access code from queries?
    - hparadiz 5y ago
      The framework has 3 layers, Query Builder -> ActiveRecord based ORM -> CRUD Controllers The CRUD controllers are then mapped with a router to different paths. When you create a CRUD controller you extend the base class which has everything you need and then you set a single parameter, className which sets the object for which this CRUD controller is for. The base class works out of the box with front ends like agGrid. The query builder itself is only part of the puzzle. Every single ORM in existence is converting the object's data from the native types of the language into the types of the database and vice versa. There's almost always some configuration for each object type in whatever format from native code to yaml to xml to plain text. Every ORM has a toDatabaseValue() normalization function that also goes in reverse when pulling data from the database. Some ORMs will call this "data mapper" or "mapping" or "normalization". When this becomes evident the queries themselves become suuuuper simple because what you send into your INSERT or UPDATE queries is a complete array of strings with field values already predetermined. This is necessary because a database engine might have dozens of types like enum, string, text, longtext,smalltext,bigtext,varchar, char but all of them in your language could be "string". Advanced ORMs will know the database engine you use and even warn you if you try to save a string over 255 chars to a (255) char database engine but most don't. My code is available here. https://github.com/Divergence/framework/blob/6af1b6b0e56b25cbcfc4e9e13aea1f77477da972/src/Models/ActiveRecord.php#L716-L735 https://github.com/Divergence/framework/blob/6af1b6b0e56b25c... Where people go wrong is trying to do this field normalization logic inside their query builder. That leads to all sorts of problems. The query builder shouldn't know what is going on with your ORM at all. It's just gluing strings together to form a query. You can see my dead simple query builder here: https://github.com/Divergence/framework/tree/develop/src/IO/Database/Query https://github.com/Divergence/framework/tree/develop/src/IO/... I honestly used to not have one as it's so simple but decided it was a good abstraction to assimilate from other frameworks. My query builder does not attempt to sanitize anything. The ActiveRecord class takes care of that through the data mapping conversion functions for all the basic HTTP CRUD functionality.
Ciantic 5y ago
The hate should be directed at SQL builder part of most ORMs. Which gets complicated pretty quickly, and it's like learning a new language when SQL works usually much better. If you write your SQL, and some tool, like code generation, then maps it to object, you are golden. It's the part you'd have to write anyway. However if the code generation goes bust (like gets abandoned like most hobby mappers), you should make sure you can take the code and continue like you never used the generator in the first place. It looks like the authors suggestion SQLC is pretty close to that. It seems to generate a lot of code though, so it might be an issue if you don't want to maintain SQLC, test it out in here: https://play.sqlc.dev/ https://play.sqlc.dev/
- hn_throwaway_99 5y ago
  I've been a huge fan of Slonik, https://github.com/gajus/slonik https://github.com/gajus/slonik, which makes it easy and safe to write direct SQL queries. The author of Slonik had a good article about the downsides of query builders, previously discussed on HN, that I totally agree with: https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
  - meitham 5y ago
    On the contrary, I hate dealing with the various escaping characters for SQL, and to me SQLAlchemy expression is a life-saver. It gives me back a textual sql expression as a string and I can choose exactly how to run it. I do avoid the ORM part as I like to be in total control of the transaction, and I am often in the python async world, where ORM don't play nicely.
    - hn_throwaway_99 5y ago
      > I hate dealing with the various escaping characters for SQL I don't understand that bit, with something like Slonik you don't use escape characters, it does it transparently for you by taking advantage of JavaScript's string template literals.
FpUser 5y ago
I avoided this Vietnam completely. It was clear to me from the beginning that data model optimal for relational storage and the one optimal for internal application state (I usually write stateful servers in C++) are 2 different things.
makeitdouble 5y ago
I guess that there’s not many people going to write a long essay on how they use ORMs moderately, knowing the limitations, and it’s perfectly fine. It wouldn’t gather a whole crowd either. What feels wrong to me in this post is the lack of nuance on how they used their ORM. For instance the whole part on how the ORM generated queries they wish were done in a different manner: why didn’t they bother writing either lower level code or just raw SQL to get their data as they wished ? It looks to me they could have kept their ORM for the 80% cases it works great, and hand tune the 20% that could be problematic. Just like any other optimization problem. There would be still the performance part where some overhead would still be there, but it’s usually not an issue if the ORM option was on the table in the first place.
- marktangotango 5y ago
  > It looks to me they could have kept their ORM for the 80% cases it works great, and hand tune the 20% that could be problematic. Just like any other optimization problem. It doesn't make for "good" blog posts I suppose. Personally I find these discussions to be tiresome and depressing. Take for example this seven year old post from the creator of Hibernate[1]. Predates a lot of todays "seniors". This field is clearly stuck in local maxima on this and a few other areas. Will there ever be anything more than this endless trial and error? [1] https://www.reddit.com/r/programming/comments/2cnw8x/what_orms_have_taught_me_just_learn_sql/cjheyec/ https://www.reddit.com/r/programming/comments/2cnw8x/what_or...
dvt 5y ago
The actually story here is having the hubris to think you can build a good ORM (in Golang no less, lol) with a handful of engineers at a small company that's not even a technology company. How did this guy become a CTO again? Complete failure of leadership. Mature ORMs (that still often suck) are built by literally thousands of open-source contributors.
- PostThisTooFast 5y ago
  So you contradict your already pompous and judgmental initial statement with your last one. Good job.
aitchnyu 5y ago
I use this pattern in Django and I feel without ORM or query builder this will need 4 raw queries. ``` results = Foo.objects()... if a: results = results.filter(a=a) if b: results = results.filter(a=b) ```
- EdwardDiego 5y ago
  select * from foo where case when :filter_on_a and not :filter_on_b then a=:a when :filter_on_b and not :filter_on_a then b=:b when :filter_on_a and :filter_on_b then a=:a and b=:b else true end Should preserve all your semantics. I can't remember if Python DBAPI handles named parameters, so let's pretend it does and the colon-prefixed parameters are in the right syntax. cur.execute("above query", filter_on_a=bool(a), filter_on_b=bool(b), a=a, b=b) Whether the more complex SQL is worth it really depends on your use case.
deleted 5y ago
[deleted]
languagehacker 5y ago
We will never be able to separate this fine argument from the fact that gorm has been terribly immature for quite some time. ORMs take a long time to grow. Look at Arel and ActiveRecord. Sufficiently mature ORMs provide addordances to work around the fundamental N+1 and abstraction issues that always crop up, but they take time, dedication, and a broad community. Go is an interesting case in which there are enough useful applications that don't comprise "web apps" that we might not be in at the point of ORM maturity. I've used both gorm and go pg, and have definitely encountered frustrating limitations coming from both ends of the problem.
mleonhard 5y ago
// updates the whole table, ignoring conditions db.Model(<table>).Where(<conditions>).Updates(<values>) // updates the rows matching conditions db.Model(<table>).Updates(<values>).Where(<conditions>) This is a massive footgun in Gorm. Don't use tools that help you make catastrophic errors.
- ufmace 5y ago
  Yeah wow. I don't understand why any ORM would ever let you do that. I don't think I've ever heard of a case where someone actually wanted to update every row in a table from application code. IMO, any decent ORM should block that entirely and force you to drop to SQL if you actually want to do it, or make you call some obnoxiously named function, like: db.Model(<table>).YesIReallyDoWantToUpdateEveryRowOnTheTable().Update(<values>)
eric4smith 5y ago
Heh. Recently I had to stand up a quick elixir project and decided to write all queries by hand and not use Ecto. It was extremely enjoyable writing every query from the start. Just thinking carefully about what columns I needed, and crafting the best joins and where clauses made efficiency baked in from the beginning. If one is not careful and just be lazy with an ORM you get back all columns all the time - and this is the biggest sin of an ORM. Not to mention sub-optimal where clauses that kill performance. And the next positive side effect was the amazing speed of the database interactions in the app. It’s not something I’d do all the time since sometimes you do need the discipline of Ecto, but I say we should write more sql by hand instead of less.
- c0balt 5y ago
  The problem is that writing SQL by hand tends to led to more errors. Experienced devs might be able to minimize those errors (and potential vulnerabilities, remember all those injection bugs?) but especially for junior devs I would recommend using an ORM to avoid shooting themselves in the foot. The question is Performance <=> Security, which is most likely gonna be security for most people on the data/ application layer.
- makeitdouble 5y ago
  If you’re not careful and just be lazy with your hand crafted queries you’ll miss updating old queries when you alter your schema. You won’t convert your types the same way in each places you query the data, or you’ll forget checking your data’s consistency where it’s falling in the cracks between your SQL rules and the application’s requirements. The “not careful and just be lazy” way won’t get you in a good place whatever approach you take.
  - eric4smith 5y ago
    The PG Driver through the Ecto Repo nicely handles and converts all the database types into Elixir types handily. Integers go to Ints and JSON B gots into Maps as one would expect.
- dnautics 5y ago
awinter-py 5y ago
how are they doing migrations?
TN1ck 5y ago
I did the exact opposite move - moving away from sqlc to gorm. Sqlc is great and fast and it was a joy to use, but I needed the ability to track changes to database models. So either I would build my own abstraction on top of sqlc or work with database triggers. Gorm provided an easy way to hook into all of that as well as making several update queries easier. I still think that sqlc should be the first choice when you don’t need more complex features, but gorm does provide a lot of goodies.
ordiel 5y ago
Meenwhile I am "traped at a fortune 500 enterprise" with a half baked project that I think would address all this, yet to affraid to drop that job due to "stability" (which COVID came to meke laughable) and also too afraid to make it publicly available for I think its not yet ready even to start reveiving feedback (even tho I have been able to start usong it gor simple cases) and draming of the day I will be able to spend all my working hours on it...
deeg 5y ago
Shameless plug: I'm working with a small team on a data framework that replaces the ORM with an abstraction the eliminates the impedance mismatch. You can take a look at https://github.com/zeidon/zeidon-joe https://github.com/zeidon/zeidon-joe . I'd be happy to answer questions.
sa46 5y ago
Having written a non-ORM Go-Postgres tool [1] similar to sqlc, I'm a big fan of this article, especially their acknowledgment that using SQL moves the eng culture towards data-centric engineering. Some thoughts: - Application code should not rely on database table structure (like the ActiveRecord pattern). Modeling the database as a bunch of queries is a better bet since you can change the underlying table structure but keep the same query semantics to allow for database refactoring. - Database code and queries should be defined in SQL. I'm not a fan of defining the database in another programming language that generates DDL for you since it's another layer of abstraction that usually leaks heavily. - One of the main drawbacks of writing queries in plain SQL is that dynamic queries are more difficult to write. I haven't found that to be too much of a problem since you can use multiple queries or push some of the dynamic parts into a SQL predicate. Some things are easier with an ORM, like dynamic ordering or dynamic group-by clauses. [1]: https://github.com/jschaf/pggen https://github.com/jschaf/pggen Similar comment from a few months ago comparing Go approaches to SQL: https://news.ycombinator.com/item?id=28463938 https://news.ycombinator.com/item?id=28463938
- zzzeek 5y ago
  say your application has 120 tables. Do you write out 120 "INSERT" statements, 120 "UPDATE" statements, 120 "DELETE" statements as raw strings or otherwise every time you need to do any DML ? if not, and instead you have something that dynamically writes these out given some kind of object state to persist, you are using an ORM. similarly, when you transfer the states of structures and/or objects to those INSERT/UPDATE/DELETE statements, or the rows from the cursor execution of a SELECT statement into object/structural state, that is also using an ORM. I tend to consider "a query builder that writes SELECT statements" to be the least important thing an "ORM" can do, and is not even necessary to still have a library defined as an ORM. The "mapping" is mostly to do with automating the movement of data between the objects / database, which is, the relational tuples mapped to object attributes.
  - sa46 5y ago
    > Do you write out 120 "INSERT" statements, 120 "UPDATE" statements, 120 "DELETE" statements as raw strings Yes. For example: https://github.com/jschaf/pggen/blob/main/example/erp/order/customer.sql https://github.com/jschaf/pggen/blob/main/example/erp/order/.... > that is also using an ORM ORM as a term covers a wide swathe of usage. In the smallest definition, an ORM converts DB tuples to Go structs. In common usage, most folks use ORM to mean a generic query builder plus the type conversion from tuples to structs. For other usages, I prefer the Patterns of Enterprise Application Architecture terms [1] like data-mapper, active record, and table-data gateway. [1]: https://martinfowler.com/eaaCatalog/ https://martinfowler.com/eaaCatalog/
choiway 5y ago
Took me a while to realize that ORMs are fine as long as you don't use them for the "R".
kgeist 5y ago
We had many problems using ORM's in production: - performance issues (DB calls in a loop the worst offender) - various obscure bugs due to the concept of "flushing" (for example, we just deleted an entity, but it's still retrievable from its repository in the same transaction, which breaks logic) - additional cognitive load because you have to learn ORM's concepts and how it maps to both DB and your domain model - you have to either add ORM-specific attributes directly to domain objects (which is leakage of infrastructure details to the domain layer) or write tons of functions which convert ORM models to your domain entities and back So in new code we now use raw SQL and we don't find it all that hard - it's easy to maintain and doesn't take a lot of time. It's problematic with complex aggregates, but our models tend to be anemic. There's the hypothetical "what if you'll want to change the DB" but all DB calls are encapsulated in the infrastructure layer, so we'd only need to rewrite repository implementations. And you can migrate to a new DB gradually, microservice by microservice, and a typical microservice won't have more than ~20 repositories, so it's pretty manageable.
noisy_boy 5y ago
Am I the only one who doesn't see this as a binary choice? I've written SQL for decades and I still like ORMs for most scenarios because: 1. I always check the SQL being generated 2. I profile the SQL being generated to ensure that the indices are being used optimally 3. I only fetch what I need and try to reduce trips to database 4. Most of the time, this is good enough and helps reduce boilerplate. 5. Sometimes, after doing the above, I see that the ORM isn't upto the task - then I just drop to SQL and get exactly what I want the way I want it. The ORM doesn't block me from doing so.
8589934591 5y ago
SQLAlchemy's core is a great option to use for building dynamic queries. I wonder if we could use python and go to interface with each other for building queries. go -> python-sqlalchemy-build-query -> run query from go and get result. https://docs.sqlalchemy.org/en/14/core/ https://docs.sqlalchemy.org/en/14/core/
nycdotnet 5y ago
It’s a good article. At Namely we use Dapper which is a “micro-ORM” developed by the folks at StackOverflow for .NET. I really like it because it gives us the mapping part without the sql code generation part which I think is the thing that ultimately gets you in trouble, and it works really well with Postgres and SQL Server.
- mjul 5y ago
  Yes. The key is designing to the tool’s strength. Build a row oriented database model in the application language that matches the DB schema. This is trivial for the ORM (like Dapper or ActiveRecord) and you save boiler plate. For simple apps this might be enough. For richer domain models it is often simpler to map to the domain model from the database model mapping in application code than fighting the ORM to do it just right from DB to domain model. Another alternative is to design to tools and design your domain model to reduce the friction from the ORM. These trade-offs are specific to each application.
KronisLV 5y ago
Personally, i think that both proponents and opponents of ORMs have a number of good points; personally, i also rather enjoyed the idea about using ORMs with generated code (e.g. schema first, a bit of model driven development) for the simpler things and then dropping down to SQL for non-trivial queries. Essentially knowing both SQL and what your ORM provides and using both for the use cases where they excel. That said, why does no one almost ever mention ORMs that allow you to generate SQL code dynamically and then map it to objects? The best and perhaps only example that i can think of is myBatis XML mapping, which to me felt like a really powerful, versatile and actually easy to understand approach: https://www.tutorialspoint.com/mybatis/mybatis_mapper_xml.htm https://www.tutorialspoint.com/mybatis/mybatis_mapper_xml.ht... That way you get the power of SQL, the ability to customize your queries at runtime as needed (e.g. filtering criteria or ordering, or pagination, or even selecting column values from different tables if need be) while also getting good performance (since it's just SQL queries which you can optimize as needed) with a pretty decent mapping layer. The biggest drawbacks that i can think of personally would be not being able to work with your DB directly if you don't have query logging on and can't get the full query out of the app when you need it, for debugging. Of course, doing that is pretty easy, however i hate how most ORMs out there don't let you log SQL queries with parameter examples (not just some special character for where the parameter value is supposed to be) in case you ever want to copy those into your SQL shell/tool of choice to run it. Of course, that's more of an inconvenience, rather than a deal breaker. Have a look at some of the example dynamic SQL here: https://www.tutorialspoint.com/mybatis/mybatis_dynamic_sql.htm https://www.tutorialspoint.com/mybatis/mybatis_dynamic_sql.h... Why don't more stacks strive for something like that? (sidenote: they also have docs on GitHub, but GitHub is down at the moment, what a day)

107 points by henvic 5y ago

I tried to use ORMs in Java years ago, but realized that while it makes simple things simpler, sooner or later you will need to do something that the ORM can't do simply. Or you will bang your head against the fact that you are trying to marry object graphs to relational models and you'll burn a lot of CPU, IO and memory in the translation. I have to admit I've been against ORMs ever since. You end up having a weak grasp on what is happening under the bonnet. You can make much better use of your database, and understand what is going on a lot better, if you let the database be the database. My approach is to forget about the database, and focus on what the application needs from the persistence layer. Then design an interface for the persistence. Then I implement that interface for each kind of persistence backend I need (SQL, files, memory, etc) while maintaining a single set of unit tests and benchmarks that are written against the interface. This isn't new. A lot of applications and libraries do this. And I think it is a good way to design things. Usually the database I use to develop a SQL schema is Sqlite3, since it allows for really nice testing. Then I add PostgreSQL support (which requires more involved testing setup, but I have a library that makes this somewhat easier: https://github.com/borud/drydock https://github.com/borud/drydock). (SQLite being in C is a bit of a problem since it means I can't get a purely statically linked binary on all platforms - at least I haven't found a way to do that except on Linux. So if anyone has some opinions on alternatives in pure Go, I'm all ears) In the Java days JDBC every single method implementing some operation would be a lot of boilerplate. JDBC wasn't a very good API. But in Go that is much less of a problem. In part because you have struct tags, and libraries like Sqlx. To that I also add some helper functions to deal with result/error combos. Turns out the majority of my interactions with SQL databases can be carried out in 1-3 lines of code - with a surprising number of cases just being a oneliner. (The performance hit from using Sqlx is in most cases so minimal it doesn't matter. If it matters to you: use Sqlx when modeling and evolving the persistence, and then optimize it out if you must. I think I've done that just once in about 100kLOC worth of code written over the last few years). And best of all: I get to deal with the database as a database. I write SQL DDL statements to define the schema, and SQL to perform the transactions. I don't have to pretend it is a object model, so I can make full use of the SQL. (Well, actually, I try to make do as far as possible with trivial SQL, but that's a whole different discussion). The interface type takes care of exposing the persistence in a way that fits the application. (Another thing I've started experimenting with a bit is to return channels or objects containing channels instead of arrays of things. But there is still some experimenting that needs to be done to find a pleasing design)

62951413 5y ago
In Java there's another popular alternative to ORM/Hibernate, JOOQ (https://www.jooq.org/ https://www.jooq.org/). Which is basically type-safe SQL. The idea is to * start from an existing relational schema * run a CLI tool to generate Java code that represents all the tables/fields, * write queries in the application code that look like ".select().from(AUTHOR).join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)).fetch();" So you don't need to mess up with strings anymore or learn any DSLs. Your queries look exactly like regular SQL and so are very readable. This approach is well aligned with schema migrations represented as SQL scripts too. But not everyone is comfortable with using code generation.
- bborud 5y ago
  SQL is actually pretty okay for writing SQL. I was never in love with this rather roundabout way of doing things since it didn’t solve problems I was actually having.

hk1337 5y ago

I feel like the biggest problem with an ORM is how people try and use them. It’s like an all or nothing approach. You hit a wall where using an ORM for that sucks and so now using an ORM for everything sucks. I never would have thought of using an ORM in golang, it doesn’t seem like that kind of language. Even an ORM in python is debatable.

bigmattystyles 5y ago
I use them for the really simple stuff, where I basically know what the query to be generated is. What I don’t understand though, what is wrong with writing a sprocket or sql function and invoking it as a method from your oo layer? That gets to 98% of my usage and it’s fine.

marcus_holmes 5y ago

So frustrating. You got like 80% of the way there, and then went "nope, too much work" and diverted to add more complexity. The answer is to write the SQL yourself, and the scan methods yourself. Code generation is better than ORM, but still a wrapper, still adds complexity, and still brings problems. Yes it's a pain in the arse to write all that boilerplate in one go (pun intended). But if you'd started without an ORM you'd have written them one at a time as you needed them and barely noticed it. Keeping your code aligned with your database schema is very little effort - database schema changes are usually rare and significant. I write a view for each access method (so I can change the schema without worrying about changing every access method), and a function for each update/insert/delete (for the same reasons). It takes maybe 20 mins to write the whole set each time I have to add a feature with new data, which is a rounding error in the time it takes to write all the rest of it. The point is that the database schema is optimised to storing data in the best way possible. The middle layer is optimised for processing data in the best way possible, and the front end is optimised for displaying data in the best way possible. None of these three things are equal. Use an interface between each of them. The interface is important and needs to be carefully considered.

e12e 5y ago
> I write a view for each access method (so I can change the schema without worrying about changing every access method), and a function for each update/insert/delete (for the same reasons). Database view and stored procedures?
- marcus_holmes 5y ago
  yeah. Postgres has functions, SQL Server has sprocs.
butMyside 5y ago
What if there are no layers and you can’t think outside hierarchy? There’s no reason for anyone else to take your view as sacrosanct since many many many apps and services are working just fine without ORMs. You might accomplish something interesting today if you set aside protecting unimportant ephemera you talked yourself into believing.

cjdell 5y ago

In NodeJS Prisma is really good for just building statically typed CRUD-like queries. It gives you result types inferred from the schema so you're less likely to make mistakes. Would still use real SQL for serious stuff, but in my experience most queries are CRUD anyway so I don't see lightweight ORMs as a problem as long as they're not doing magic caching in the background and lazy fetching. Let me decide how I want to do that.

topicseed 5y ago
Using MikroORM in NodeJS and works wonders. Has the "raw" option for queries I want to handle myself, or even the "knex" builder for something in between
- cjdell 5y ago
  Hmm... Looks like a "active record" pattern (like Hibernate). This is exactly what I don't want. Essentially I only use ORMs as query builders. No magic behinds the scenes.
  - topicseed 5y ago
    Author said it isn't[0] a year ago but not sure. I think it allows a couple of strategies to be used. [0] https://github.com/mikro-orm/mikro-orm/issues/403 https://github.com/mikro-orm/mikro-orm/issues/403

nauticacom 5y ago

Never understood the hate for ORMs. I need to map from my data storage to my domain model somehow, why write all that code myself

rob74 5y ago
Because relational databases are very good at (surprise!) relations. So in SQL you can query data from several joined tables, query only certain columns, aggregate columns etc. etc. And all of these things are made needlessly complicated by using ORM. Even with the best ORM, you still have to learn an arcane new syntax for stuff you could write in 30 seconds when using SQL...
- nauticacom 5y ago
  Right, but for most of that stuff I can just write a one-off query, munge it into the ORM's objects and pass that off to my view for rendering. But probably 80% of my queries are "get this object by ID" or "get these objects and some related ones by a simple condition" or maybe a slightly complex condition. In these cases an ORM works wonders and has almost zero downsides
- hello_moto 5y ago
  ORM has escape hatch so you can write SQL for the complex queries (and hopefully don't open yourself to SQL injection along the process) and still benefit from ORM for the simple to moderate case. > So in SQL you can query data from several joined tables Can do that in your ORM too. > query only certain columns So far no issue with ORM
pionar 5y ago
The hate isn't in the mapping. It's perfectly reasonable to abstract away mapping of relational data to objects. It's the translation of objects to SQL queries that's the problem. ORMs typically output inefficient and mostly obfuscated SQL, when a human can do a better job of hand-writing the SQL, knowing the data usage patterns needed.
- 5y ago

dominotw 5y ago

i thought we'd all agreed 15 yrs ago to not use vietnam analogy to discuss orms.

pixelgeek 5y ago
What sort of ghoul compares a programming issue with something like the conflict in Vietnam? When he started writing about 15 million people dying you think that would have made him stop and rethink the metaphor.
- erect_hacker4 5y ago
  This is standard fare on HN… Just remember that HN is not Reddit, that’s the most important thing.
- erect_hacker5 5y ago
  This is standard fare for HN. Just don’t mention that HN is like Reddit - that’s off limits.
- deleted 5y ago
  [deleted]
ufo 5y ago
The saddest part of the analogy is how US-centric it is. "Vietnam" is a boondoggle to exit from, not a home that's suffering through a bloody conflict.
gunfighthacksaw 5y ago
We have a new, shiny Afghanistan analogy to use!
rjsw 5y ago
The last reference to Vietnam that I saw in the MSM was that it had done a good job of handling COVID-19. Maybe the article is describing a good use of an ORM.
- 5y ago

robertlagrant 5y ago

Some small points: > The ORM was a natural culprit: it’s easy to see that many ORMs will make use of object introspection to build SQL statements, hydrate results, or both. Gorm’s memory footprint is extreme although sadly not uncommon. Bridge started with Python on the backend and we used Django’s ORM to interact with the database which had similar problems. I believe these are both active record ORMs, which are not as good for various reasons (including default memory usage) as good data mappers such as SQLAlchemy and Hibernate. As for where enums etc are "mastered" - this is still a decision to make with or without ORMs. I think the main tricky bit of ORMs is that objects are trees and relations are graphs; this causes problems. Not so much anything about encapsulation etc.

dekhn 5y ago

This article would have been better without the unnecessary war analogy and history lesson.

lioeters 5y ago
Indeed the analogy has not aged well. It's in bad taste, and somewhat offensive, to hear the term "Vietnam of programming".
- deleted 5y ago
  [deleted]
- tommek4077 5y ago
  Why did it not age well? Are there any new findings regarding Vietnam?
  - quesera 5y ago
    To most people in the world, "Vietnam" is not a war, and is not merely a symbol of absurdly misconceived incompetence.
    - PostThisTooFast 5y ago
      Yes. The author should've said "the Iraq of programming."
- tuan 5y ago
  Yeah, as a Vietnamese, this title definitely feels offensive to me. Maybe this article is written with American audience in mind. In Vietnam we call the war American war btw. Depends on where you are from, the title could have used the term "USA of programming" :)
dmos62 5y ago
The funny thing is that it references Vietnam, while the Afghanistan exit happened just a few months ago.

hinkley 5y ago

There’s two reasons a problem stays unsolved. Perhaps three if you believe Jim Highsmith (we are managing paradoxes). One, it’s unsolvable, like the Halting Problem. Two, it’s an XY problem and nobody is asking the right question. I’d been thinking recently that you could build a stupid simple ORM if you set up your SQL results to have the exact same names as your target object/struct and fed the type in as a parameter. Push these results into this box. But that’s not the functionality that sells ORMs. It’s the object graph. Mapping relational data to graphs, ORM or not, is quite messy. Maybe that says we should be using graph databases for this sort of data instead.

kevmo314 5y ago

As a junior dev I had no idea what an ORM was. As a mid-level dev I discovered them and wanted to use them everywhere. As a senior dev I've gone back to manually writing queries. Such is life.

m0zg 5y ago
Real fun begins if your team has all three stages of seniority. Typically it's somewhere in the middle, so your default is to use ORM, even though as a senior person you know everyone will profoundly regret this in the long run (and you will regret it immediately).
hparadiz 5y ago
I've gone past this and have a very clean and efficient query builder in my ORM. I maintain one for work and another is my open source PHP framework (15 years old now). Mine supports simple joins and relationships but anything beyond that requires writing custom queries.
- unclebucknasty 5y ago
  How does your query builder work? Is it an API that you use to build queries during runtime or do you generate database access code from queries?
  - hparadiz 5y ago
    The framework has 3 layers, Query Builder -> ActiveRecord based ORM -> CRUD Controllers The CRUD controllers are then mapped with a router to different paths. When you create a CRUD controller you extend the base class which has everything you need and then you set a single parameter, className which sets the object for which this CRUD controller is for. The base class works out of the box with front ends like agGrid. The query builder itself is only part of the puzzle. Every single ORM in existence is converting the object's data from the native types of the language into the types of the database and vice versa. There's almost always some configuration for each object type in whatever format from native code to yaml to xml to plain text. Every ORM has a toDatabaseValue() normalization function that also goes in reverse when pulling data from the database. Some ORMs will call this "data mapper" or "mapping" or "normalization". When this becomes evident the queries themselves become suuuuper simple because what you send into your INSERT or UPDATE queries is a complete array of strings with field values already predetermined. This is necessary because a database engine might have dozens of types like enum, string, text, longtext,smalltext,bigtext,varchar, char but all of them in your language could be "string". Advanced ORMs will know the database engine you use and even warn you if you try to save a string over 255 chars to a (255) char database engine but most don't. My code is available here. https://github.com/Divergence/framework/blob/6af1b6b0e56b25cbcfc4e9e13aea1f77477da972/src/Models/ActiveRecord.php#L716-L735 https://github.com/Divergence/framework/blob/6af1b6b0e56b25c... Where people go wrong is trying to do this field normalization logic inside their query builder. That leads to all sorts of problems. The query builder shouldn't know what is going on with your ORM at all. It's just gluing strings together to form a query. You can see my dead simple query builder here: https://github.com/Divergence/framework/tree/develop/src/IO/Database/Query https://github.com/Divergence/framework/tree/develop/src/IO/... I honestly used to not have one as it's so simple but decided it was a good abstraction to assimilate from other frameworks. My query builder does not attempt to sanitize anything. The ActiveRecord class takes care of that through the data mapping conversion functions for all the basic HTTP CRUD functionality.

Ciantic 5y ago

The hate should be directed at SQL builder part of most ORMs. Which gets complicated pretty quickly, and it's like learning a new language when SQL works usually much better. If you write your SQL, and some tool, like code generation, then maps it to object, you are golden. It's the part you'd have to write anyway. However if the code generation goes bust (like gets abandoned like most hobby mappers), you should make sure you can take the code and continue like you never used the generator in the first place. It looks like the authors suggestion SQLC is pretty close to that. It seems to generate a lot of code though, so it might be an issue if you don't want to maintain SQLC, test it out in here: https://play.sqlc.dev/ https://play.sqlc.dev/

hn_throwaway_99 5y ago
I've been a huge fan of Slonik, https://github.com/gajus/slonik https://github.com/gajus/slonik, which makes it easy and safe to write direct SQL queries. The author of Slonik had a good article about the downsides of query builders, previously discussed on HN, that I totally agree with: https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
- meitham 5y ago
  On the contrary, I hate dealing with the various escaping characters for SQL, and to me SQLAlchemy expression is a life-saver. It gives me back a textual sql expression as a string and I can choose exactly how to run it. I do avoid the ORM part as I like to be in total control of the transaction, and I am often in the python async world, where ORM don't play nicely.
  - hn_throwaway_99 5y ago
    > I hate dealing with the various escaping characters for SQL I don't understand that bit, with something like Slonik you don't use escape characters, it does it transparently for you by taking advantage of JavaScript's string template literals.

FpUser 5y ago

I avoided this Vietnam completely. It was clear to me from the beginning that data model optimal for relational storage and the one optimal for internal application state (I usually write stateful servers in C++) are 2 different things.

makeitdouble 5y ago

I guess that there’s not many people going to write a long essay on how they use ORMs moderately, knowing the limitations, and it’s perfectly fine. It wouldn’t gather a whole crowd either. What feels wrong to me in this post is the lack of nuance on how they used their ORM. For instance the whole part on how the ORM generated queries they wish were done in a different manner: why didn’t they bother writing either lower level code or just raw SQL to get their data as they wished ? It looks to me they could have kept their ORM for the 80% cases it works great, and hand tune the 20% that could be problematic. Just like any other optimization problem. There would be still the performance part where some overhead would still be there, but it’s usually not an issue if the ORM option was on the table in the first place.

marktangotango 5y ago
> It looks to me they could have kept their ORM for the 80% cases it works great, and hand tune the 20% that could be problematic. Just like any other optimization problem. It doesn't make for "good" blog posts I suppose. Personally I find these discussions to be tiresome and depressing. Take for example this seven year old post from the creator of Hibernate[1]. Predates a lot of todays "seniors". This field is clearly stuck in local maxima on this and a few other areas. Will there ever be anything more than this endless trial and error? [1] https://www.reddit.com/r/programming/comments/2cnw8x/what_orms_have_taught_me_just_learn_sql/cjheyec/ https://www.reddit.com/r/programming/comments/2cnw8x/what_or...

dvt 5y ago

The actually story here is having the hubris to think you can build a good ORM (in Golang no less, lol) with a handful of engineers at a small company that's not even a technology company. How did this guy become a CTO again? Complete failure of leadership. Mature ORMs (that still often suck) are built by literally thousands of open-source contributors.

PostThisTooFast 5y ago
So you contradict your already pompous and judgmental initial statement with your last one. Good job.

aitchnyu 5y ago

I use this pattern in Django and I feel without ORM or query builder this will need 4 raw queries. ``` results = Foo.objects()... if a: results = results.filter(a=a) if b: results = results.filter(a=b) ```

EdwardDiego 5y ago
select * from foo where case when :filter_on_a and not :filter_on_b then a=:a when :filter_on_b and not :filter_on_a then b=:b when :filter_on_a and :filter_on_b then a=:a and b=:b else true end Should preserve all your semantics. I can't remember if Python DBAPI handles named parameters, so let's pretend it does and the colon-prefixed parameters are in the right syntax. cur.execute("above query", filter_on_a=bool(a), filter_on_b=bool(b), a=a, b=b) Whether the more complex SQL is worth it really depends on your use case.

deleted 5y ago

[deleted]

languagehacker 5y ago

We will never be able to separate this fine argument from the fact that gorm has been terribly immature for quite some time. ORMs take a long time to grow. Look at Arel and ActiveRecord. Sufficiently mature ORMs provide addordances to work around the fundamental N+1 and abstraction issues that always crop up, but they take time, dedication, and a broad community. Go is an interesting case in which there are enough useful applications that don't comprise "web apps" that we might not be in at the point of ORM maturity. I've used both gorm and go pg, and have definitely encountered frustrating limitations coming from both ends of the problem.

mleonhard 5y ago

// updates the whole table, ignoring conditions db.Model(<table>).Where(<conditions>).Updates(<values>) // updates the rows matching conditions db.Model(<table>).Updates(<values>).Where(<conditions>) This is a massive footgun in Gorm. Don't use tools that help you make catastrophic errors.

ufmace 5y ago
Yeah wow. I don't understand why any ORM would ever let you do that. I don't think I've ever heard of a case where someone actually wanted to update every row in a table from application code. IMO, any decent ORM should block that entirely and force you to drop to SQL if you actually want to do it, or make you call some obnoxiously named function, like: db.Model(<table>).YesIReallyDoWantToUpdateEveryRowOnTheTable().Update(<values>)

eric4smith 5y ago

Heh. Recently I had to stand up a quick elixir project and decided to write all queries by hand and not use Ecto. It was extremely enjoyable writing every query from the start. Just thinking carefully about what columns I needed, and crafting the best joins and where clauses made efficiency baked in from the beginning. If one is not careful and just be lazy with an ORM you get back all columns all the time - and this is the biggest sin of an ORM. Not to mention sub-optimal where clauses that kill performance. And the next positive side effect was the amazing speed of the database interactions in the app. It’s not something I’d do all the time since sometimes you do need the discipline of Ecto, but I say we should write more sql by hand instead of less.

c0balt 5y ago
The problem is that writing SQL by hand tends to led to more errors. Experienced devs might be able to minimize those errors (and potential vulnerabilities, remember all those injection bugs?) but especially for junior devs I would recommend using an ORM to avoid shooting themselves in the foot. The question is Performance <=> Security, which is most likely gonna be security for most people on the data/ application layer.
makeitdouble 5y ago
If you’re not careful and just be lazy with your hand crafted queries you’ll miss updating old queries when you alter your schema. You won’t convert your types the same way in each places you query the data, or you’ll forget checking your data’s consistency where it’s falling in the cracks between your SQL rules and the application’s requirements. The “not careful and just be lazy” way won’t get you in a good place whatever approach you take.
- eric4smith 5y ago
  The PG Driver through the Ecto Repo nicely handles and converts all the database types into Elixir types handily. Integers go to Ints and JSON B gots into Maps as one would expect.
dnautics 5y ago

awinter-py 5y ago

how are they doing migrations?

TN1ck 5y ago

I did the exact opposite move - moving away from sqlc to gorm. Sqlc is great and fast and it was a joy to use, but I needed the ability to track changes to database models. So either I would build my own abstraction on top of sqlc or work with database triggers. Gorm provided an easy way to hook into all of that as well as making several update queries easier. I still think that sqlc should be the first choice when you don’t need more complex features, but gorm does provide a lot of goodies.

ordiel 5y ago

Meenwhile I am "traped at a fortune 500 enterprise" with a half baked project that I think would address all this, yet to affraid to drop that job due to "stability" (which COVID came to meke laughable) and also too afraid to make it publicly available for I think its not yet ready even to start reveiving feedback (even tho I have been able to start usong it gor simple cases) and draming of the day I will be able to spend all my working hours on it...

deeg 5y ago

Shameless plug: I'm working with a small team on a data framework that replaces the ORM with an abstraction the eliminates the impedance mismatch. You can take a look at https://github.com/zeidon/zeidon-joe https://github.com/zeidon/zeidon-joe . I'd be happy to answer questions.

sa46 5y ago

Having written a non-ORM Go-Postgres tool [1] similar to sqlc, I'm a big fan of this article, especially their acknowledgment that using SQL moves the eng culture towards data-centric engineering. Some thoughts: - Application code should not rely on database table structure (like the ActiveRecord pattern). Modeling the database as a bunch of queries is a better bet since you can change the underlying table structure but keep the same query semantics to allow for database refactoring. - Database code and queries should be defined in SQL. I'm not a fan of defining the database in another programming language that generates DDL for you since it's another layer of abstraction that usually leaks heavily. - One of the main drawbacks of writing queries in plain SQL is that dynamic queries are more difficult to write. I haven't found that to be too much of a problem since you can use multiple queries or push some of the dynamic parts into a SQL predicate. Some things are easier with an ORM, like dynamic ordering or dynamic group-by clauses. [1]: https://github.com/jschaf/pggen https://github.com/jschaf/pggen Similar comment from a few months ago comparing Go approaches to SQL: https://news.ycombinator.com/item?id=28463938 https://news.ycombinator.com/item?id=28463938

zzzeek 5y ago
say your application has 120 tables. Do you write out 120 "INSERT" statements, 120 "UPDATE" statements, 120 "DELETE" statements as raw strings or otherwise every time you need to do any DML ? if not, and instead you have something that dynamically writes these out given some kind of object state to persist, you are using an ORM. similarly, when you transfer the states of structures and/or objects to those INSERT/UPDATE/DELETE statements, or the rows from the cursor execution of a SELECT statement into object/structural state, that is also using an ORM. I tend to consider "a query builder that writes SELECT statements" to be the least important thing an "ORM" can do, and is not even necessary to still have a library defined as an ORM. The "mapping" is mostly to do with automating the movement of data between the objects / database, which is, the relational tuples mapped to object attributes.
- sa46 5y ago
  > Do you write out 120 "INSERT" statements, 120 "UPDATE" statements, 120 "DELETE" statements as raw strings Yes. For example: https://github.com/jschaf/pggen/blob/main/example/erp/order/customer.sql https://github.com/jschaf/pggen/blob/main/example/erp/order/.... > that is also using an ORM ORM as a term covers a wide swathe of usage. In the smallest definition, an ORM converts DB tuples to Go structs. In common usage, most folks use ORM to mean a generic query builder plus the type conversion from tuples to structs. For other usages, I prefer the Patterns of Enterprise Application Architecture terms [1] like data-mapper, active record, and table-data gateway. [1]: https://martinfowler.com/eaaCatalog/ https://martinfowler.com/eaaCatalog/

choiway 5y ago

Took me a while to realize that ORMs are fine as long as you don't use them for the "R".

kgeist 5y ago

We had many problems using ORM's in production: - performance issues (DB calls in a loop the worst offender) - various obscure bugs due to the concept of "flushing" (for example, we just deleted an entity, but it's still retrievable from its repository in the same transaction, which breaks logic) - additional cognitive load because you have to learn ORM's concepts and how it maps to both DB and your domain model - you have to either add ORM-specific attributes directly to domain objects (which is leakage of infrastructure details to the domain layer) or write tons of functions which convert ORM models to your domain entities and back So in new code we now use raw SQL and we don't find it all that hard - it's easy to maintain and doesn't take a lot of time. It's problematic with complex aggregates, but our models tend to be anemic. There's the hypothetical "what if you'll want to change the DB" but all DB calls are encapsulated in the infrastructure layer, so we'd only need to rewrite repository implementations. And you can migrate to a new DB gradually, microservice by microservice, and a typical microservice won't have more than ~20 repositories, so it's pretty manageable.

noisy_boy 5y ago

Am I the only one who doesn't see this as a binary choice? I've written SQL for decades and I still like ORMs for most scenarios because: 1. I always check the SQL being generated 2. I profile the SQL being generated to ensure that the indices are being used optimally 3. I only fetch what I need and try to reduce trips to database 4. Most of the time, this is good enough and helps reduce boilerplate. 5. Sometimes, after doing the above, I see that the ORM isn't upto the task - then I just drop to SQL and get exactly what I want the way I want it. The ORM doesn't block me from doing so.

8589934591 5y ago

SQLAlchemy's core is a great option to use for building dynamic queries. I wonder if we could use python and go to interface with each other for building queries. go -> python-sqlalchemy-build-query -> run query from go and get result. https://docs.sqlalchemy.org/en/14/core/ https://docs.sqlalchemy.org/en/14/core/

nycdotnet 5y ago

It’s a good article. At Namely we use Dapper which is a “micro-ORM” developed by the folks at StackOverflow for .NET. I really like it because it gives us the mapping part without the sql code generation part which I think is the thing that ultimately gets you in trouble, and it works really well with Postgres and SQL Server.

mjul 5y ago
Yes. The key is designing to the tool’s strength. Build a row oriented database model in the application language that matches the DB schema. This is trivial for the ORM (like Dapper or ActiveRecord) and you save boiler plate. For simple apps this might be enough. For richer domain models it is often simpler to map to the domain model from the database model mapping in application code than fighting the ORM to do it just right from DB to domain model. Another alternative is to design to tools and design your domain model to reduce the friction from the ORM. These trade-offs are specific to each application.

KronisLV 5y ago

Personally, i think that both proponents and opponents of ORMs have a number of good points; personally, i also rather enjoyed the idea about using ORMs with generated code (e.g. schema first, a bit of model driven development) for the simpler things and then dropping down to SQL for non-trivial queries. Essentially knowing both SQL and what your ORM provides and using both for the use cases where they excel. That said, why does no one almost ever mention ORMs that allow you to generate SQL code dynamically and then map it to objects? The best and perhaps only example that i can think of is myBatis XML mapping, which to me felt like a really powerful, versatile and actually easy to understand approach: https://www.tutorialspoint.com/mybatis/mybatis_mapper_xml.htm https://www.tutorialspoint.com/mybatis/mybatis_mapper_xml.ht... That way you get the power of SQL, the ability to customize your queries at runtime as needed (e.g. filtering criteria or ordering, or pagination, or even selecting column values from different tables if need be) while also getting good performance (since it's just SQL queries which you can optimize as needed) with a pretty decent mapping layer. The biggest drawbacks that i can think of personally would be not being able to work with your DB directly if you don't have query logging on and can't get the full query out of the app when you need it, for debugging. Of course, doing that is pretty easy, however i hate how most ORMs out there don't let you log SQL queries with parameter examples (not just some special character for where the parameter value is supposed to be) in case you ever want to copy those into your SQL shell/tool of choice to run it. Of course, that's more of an inconvenience, rather than a deal breaker. Have a look at some of the example dynamic SQL here: https://www.tutorialspoint.com/mybatis/mybatis_dynamic_sql.htm https://www.tutorialspoint.com/mybatis/mybatis_dynamic_sql.h... Why don't more stacks strive for something like that? (sidenote: they also have docs on GitHub, but GitHub is down at the moment, what a day)