Objection to ORM Hatred | Hacker News Reader

Objection to ORM Hatred

jakso.me

50 pointsdsego87 comments

Recently I was thinking that the biggest reason this discussion keeps coming up (and is so unedifying) is that many of us have entirely different experiences with ORMs that colour our views.

I "grew up" using Rails and ActiveRecord. I maintain that (from version 3 onwards) it's absolutely flipping amazing. Can't get enough of it. Never had an issue constructing queries, defining migrations, or dropping down to SQL when I needed to – and generally being able to define and use the entire "database layer" of an application in a robust and sensible manner. Love everything about it.

In contrast, I picked up GORM to use with Go – thinking that I was generally pro-ORM and so it would be a straightforward thing to use. It has been an active impediment to getting work done, and I hate interacting with it. Part of this is no doubt due to my mindset and expectations, so I don't think it's fair to criticise the project itself. But I think it revealed to me that other people might have an entirely different experience with the benefits and costs of using an ORM, and that this is likely derived from both the tools they have used and the projects they have worked on.

I guess the point is… being open-minded about the benefits and costs is always a good thing.

Can't agree more with you.

I have the same experience with the django ORM.

At work all the Go fans are advocating against ORMs because the exact reasons you give.

Then I look at the code they write, and just seeing hand written sql migrations ran by very flaky bash scripts makes me cringe.

Let's not talk about all those sql queries embedded in the code....

For me, ORM are the good illustration of the quote: "The road to hell is paved with good intentions"

For expert-beginner and "architecture manager", it looks like a good idea, giving an abstraction and co. But, I think that most haters are the people that already had used for a few of them for long enough to be burned and get the experience.

ORM job is supposed to simplify the job of dealing with the database, but most of the time, the final things is a lot more complicated and a mess because of the ORM.

The main issue being that ORM operations looks like auto magic to the user, but this auto-magic still have a lot of rules of specific functions and settings that have to be used for the things working correctly, because it is just executing sql in background.

And in the end, it is so much more complicated to do understand and do simple things with the orm that it is to do the original sql operation.

The worst of all is when the ORM has support for some callback or automatic hooks, like "post-update", etc...

I don’t find the “magic” SQL generation that problematic in practice. The main problem with ORMs is that as a class they are, broadly speaking, all fat model ActiveRecord patterns which encourage you to make the models fatter and to spread invisible dependencies on these models (and their related models and the methods) throughout the codebase. The result is invariably a big ball of mud.

Immutable entities, produced by a set of ORM-like and mostly automatic GetX/PersistX services, is more generally the sound way forward. Unfortunately it’s also far less convenient than most ORMs in practice, as frameworks like Rails prioritize realizing the messier vision.

A few examples of the magic and the implicit rules that leads to confusion:

query(object1).where(object1.prop=10).orderby(prop).join(object2).where(object1.prop=object2.bar)

Looks like to be logical blocks that you can easily chain together, but is the order important and can each "subfunction" be used anywere? or after a specific previous one? Probably join couldn't be used after orderby. What is the state before the join, the available tables? Where inside the sql will be applied the where? What object will be available?

Some ORM even do kind of "autojoin" on table that it detects based on arguments used in later where clauses.

The problem with the object-based model is that, by definition, it constrains the developer into patterns that have an impedance mismatch with the underlying database: as a general rule, the objects are either fat models that always pull the entire record from the database, or they're proxy objects that only fetch minimal data first, and then do a round-trip to the database for every subsequent property access.

Neither is a good fit for databases, which excel in slicing and dicing your data to retrieve only the exact properties you require, no more, no less. LINQ alleviates this somewhat because it allows anonymous objects, but the fundamental problem is still the same: the SQL (relational) model relies heavily on column filtering and result set composition, neither of which can be abstracted properly with object-oriented programming.

And let's not even get started about using the object model to enforce storage invariants, like NOT NULL or FOREIGN KEY validation. These invariants should have little bearing on how the models are treated during runtime, because there are valid reasons for a record to be incomplete (the partial resultset mentioned above, an object still under construction, etc).

So, in my view, here's what a good "ORM" should be able to do:

- construct and fetch an object using any subset of the model properties, leaving the others empty (preferably explicit)

- allow composition of multiple models in both data retrieval and search parameters

- have an explicit distinction between storage invariants and business invariants (e.g. a function reporting on aggregate sales should not need to fetch every individual sales line, even though the model says that every sales order requires at least one item line)

At which point, you're merely replicating the relational model in your "ORM", and you're better off avoiding the abstraction entirely. That doesn't mean you shouldn't use model definitions that match your database records. But your ORM shouldn't be an abstraction between your models and your database -- it should expose the full relational capabilities to your developers, because they're going to need those capabilities anyway.

[ but note, I haven't actually worked with any recent ORM except for esqueleto -- and I'm pretty sure it doesn't qualify as an ORM ]

I have projects that use ORMs and projects that do not use ORMs. I have specific reasons for both types, and in certain circumstances one or another is more appropriate.

ORMs are a dependency that are often constantly updated, prone to corner cases, and require a lot of library specific knowledge-- but they often make code more secure against small mistakes or tossing in a string interpolation, which is important when working with others, especially junior developers. They can be quite nice to work with in having type checked queries, building up queries from pieces using an API, but they also tend to be heavy and miss some features of your DB.

Raw SQL on the other hand, is very flexible, standard, easy to read, and anyone who knows SQL can jump in. You can do REPL for queries and copy in, and edits to SQL are easy. The only dependency you need is the standard database drivers, and updates don't typically break things. If you have a basic understanding and use it right, security is great as well.

> If you have a basic understanding and use it right, security is great as well.

I'd say about 90% or so of the code I look at where someone is building raw SQL queries has trivial SQL injections in it. I'm sure there are a ton of reasonable use cases for raw SQL, but when I see the same dumb mistakes over and over again, it just makes me shake my head and wish more people just used ORMs.

Major breaches happen every day due to SQL injections, so building up a narrative that developers can do raw SQL right if they're smart enough is just irresponsible IMO. Over and over again when I tell developers that they should use an ORM, they get offended, and then I end up finding trivial SQL injections.

It kind of feels like telling people that it's perfectly safe to drive without a seat-belt so long as you're a good driver.

By Sturgeon's law [1] 90% of all code is crap, so it doesn't surprise me that that 90% of code contains sql injections ;-)

[1] https://en.wikipedia.org/wiki/Sturgeon%27s_law -> "Ninety percent of everything is crap."

In the Java ecosystem there is the PreparedStatement.

ORM isn't required to avoid SQL injections. The two concepts maybe conflated with some frameworks, but they aren't necessarily related.

I disagree. As long as you use prepared statments and bounded parameters, your application is safe from SQL injections. NEVER use string concatiation to generate any SQL queries - not in your app and not in your database! Its unsafe and slow. https://security.stackexchange.com/questions/15214/are-prepa...

That's easy enough to say, but time and time again I see codebases, even ones making extensive use of prepared statements, falling back to doing string concatenation from time to time. Prepared statements etc are an example of "opt-in security", which is a good band-aid to have for quickly fixing up old code, but it still allows for some pretty egregious errors.

Again, with the seat-belt analogy. As long as you're safe and careful all the time, seat-belts are worthless. Therefore seat-belts are only for dumb, reckless people.

Then again, prepared statements (and SQL injection) are a solved problem. Imagine what people who can't bother to use prepared statements would do with an ORM in non-trivial cases.

Anyone writing raw SQL should use parametrized queries for all cases where this is possible. That has been the recommendation for a long time now, and there is really no excuse to not doing that.

There are some cases where you can't use them, e.g. dynamic queries where you modify which columns are queried or larger parts of the entire query. But that more of an exception, and you do need to be careful about only using whitelisted terms when you modify the raw SQL part that can't be replaced by parameters.

> I'd say about 90% or so of the code I look at where someone is building raw SQL queries has trivial SQL injections in it.

Just parameterize the query and you're done. If someone isn't doing so in 2020 they're either working on a very old system or they're not doing it right.

SQL injection is just not a reason to avoid raw SQL.

One of the reasons I love Dapper.net is because it allows me to use raw SQL and helps solve the only pain point I have with raw SQL, dynamically building queries.

Interesting angle. I've come round to the benefits of ORMs for query building and results-to-objects. Especially since I used LINQ; linq-on-objects and linq-on-xml are incredibly useful even outside a database.

The one big anti-pattern I've seen in systems that use ORMs is the "manual query" or especially "manual update": rather than doing something in the DB, the programmer retrieves some objects, iterates over them with a FOR loop or similar, makes changes, and then writes them back to the database. This is absolutely terrible for performance and prone to concurrency errors.

(are there ORMs in which all returned data objects are forced to be immutable? I think that would help.)

There are some patterns ORMs like Hibernate use to combat the races: versioning, pessimistic locking, etc.

And while performance can be an issue for many businesses it's the read queries that are most often the bottleneck. If write performance is critical then hopefully you've got sustainable revenue to cover the cost to optimize it.

> (are there ORMs in which all returned data objects are forced to be immutable? I think that would help.)

I'm not sure if this is exactly what you're asking, but Entity Framework has .AsNoTracking(), which will simply return the objects without doing any change tracking on them, so feel free to modify them as you wish afterwards without accidentally updating your database.

One case I find really annoying to write in SQL is inserting or updating entities with several many-to-many relationships. That is just plain annoying to do by hand, so I'd either have to use an ORM or write a small wrapper myself for this.

Reading entities with many-to-many relationships is also often easier with an ORM, though you really have to be aware how the ORM will write this kind of query to avoid issues. ORMs are a very leaky abstraction when performance is concerned.

Another part that is very useful is the query building itself, though you can get that without an ORM. Having composable query fragments is very useful, doing that by hand is extremely tedious, error-prone and a potential security risk if you're not careful.

I'm comfortable writing SQL by hand, but I do prefer to have an ORM as long as I can also bypass it for queries that are difficult or impossible to do with the ORM. But an ORM can't replace knowing how databases work, and they do require you to understand more under-the-hood details than most other types of libraries.

Personally, I think ORM has a reasonable use case here, but I also find doing this using SQL to be straightforward and easier to reason about during future refactors.

Take a look at JDBI's Repository pattern for a simple compromise between SQL and ORM for this use case in particular.

I enjoy using the SQLAlchemy ORM. It makes it trivial to divide a query in several parts that you can mix and match. In my experience, features such as a search engine that supports many filters are much easier to maintain if they are written with an ORM rather than with raw SQL. I don't have a preference for simple queries.

I consider myself reasonably comfortable in SQL and don't mind crafting queries when it is optimized for performance and/or ORM wouldn't be able to produce something optimum. However, for typical CRUD stuff, I almost always use ORM because not having to deal with the boilerplate makes things so much more easier. E.g. JPA's query creation[0] allows me to dynamically generate the query based on the method name - that is just way too convenient to give up, I could use SQL too but there is no payoff in writing boilerplate compared to the convenience and decent performance provided by the ORM.

[0]: https://docs.spring.io/spring-data/jpa/docs/current/referenc...

As a complete amateur regarding the backend I use ORMs for two reasons:

1. Migrations - I only recently started diving deeper into the concept so an abstraction is in order for now.

2. Security - I'm at a point where I don't know what I don't know and thus would rather not rely solely on my own knowledge. I'm sure such systems introduce their own security risks, but since those are mostly open-source projects I can at least count on somebody to eventually discover any such issue.

Database migrations are completely independent of using an ORM. You can still create migrations from your model definitions but that doesn't mean that you have to use an ORM to query a database.

Some tools you might find useful: Flyway, Liquibase, Evolve

You don't need an ORM for migrations. Tools like flyway and others can rely purely on `.sql` files with some naming conventions and can help keep your app and db schemas in sync.

You also don't need an ORM for security, and an ORM doesn't always prevent you from footgunning. Use query builders, `?` or `:named` placeholders (as your DB driver recommends), and almost never use string operations to build queries. Similarly, grow suspicious of your db or application model if you find yourself having to write increasing amounts of SQL to appease your ORM.

ORMs are a tool. Neither good or bad on their own. Many applications outgrow them, or they don't properly refine their object models as the db or application changes. Lots of applications hold on to the security blanket of their ORMs for far too long, or they let the DB objects proliferate very deeply into their stack such that changing the db or the app model become nearly impossible. This is when apps increasingly try to outsmart the ORM, creating massive amounts of tension and friction in doing even simple things. (As anyone who has tried to do large-scale migrations or backfills that weren't possible in raw sql will tell you.)

Learning to recognize all this is above the level of "amateur", but it seems like you've already learned to identify some of the good that the tooling can provide.

Using Objection for more than a year in a medium sized ERP project. Not bad, but for sure on the next project I will use directly knex as querybuilder (with no ORM part). The ORM part that Objection add to knex don't bring much value and add a layer of complexity. The only functionality that I would miss would be the graph operations (withGraphFetched, upsertGraph, ecc.) but writing them in SQL (using KNEX) would not be a great problem.

We're using knex at $dayjob, and we're not even using it for half our queries. Knex is pretty nice for simple CRUD operation, but for more complex queries we've found it much nicer to drop down to raw SQL (with knex.raw providing escaping).

What we have found helpful is to stick common queries into a helper function so that we can get a consistently shaped object back.

We use both in our code base. Sometimes knex makes sense, sometimes Objection makes sense. That's actually one of the nice things about it wrapping knex.

Have you considered a tagged template literal sql builder, such as https://github.com/blakeembrey/sql-template-tag ? Keeps the SQL syntax so less to learn, but still offers parametrized queries.

Considered it, but I think that there are advantage in programmatically construct the query (see for example knex modify). Directly write down SQL or use template with sql make it harder. The only problem with knex is that it mutate at every operation instead of returning a new instance. I'm experimenting in wrapping it in a lazy monad that return a Fluture (https://github.com/FbN/iodio)

I find the loud aversion for peoples choice of tools quite "eye roll"-worthy.

IMHO use whatever that helps you actually deliver something.

IYHO there's no possibility of people:

(a) using the wrong tool for the job

(b) succumbing to fads and hype

(c) forcing said tools to other's throats (e.g. manager making decisions, or team lead, or company policy, or "opinionated" non-technical customer)

I'd say those cases deserve "loud aversion for peoples choice of tools".

None of those is justified, no.

Where you see me using the wrong tool for the job, I see myself using a tool that may be flawed but is good enough for what I need right now.

Where you see me succumbing to fads and hype, I see you as needlessly conservative, or see myself as trialling a new piece of tech.

Where you see me forcing a tool down your throat, I see myself standardising on a compromise solution so we don't have ten approaches to the same problem in one single codebase.

On any one of those situations, either of us might be right, wrong, or (most likely) somewhere in between. It's important to keep that in mind before loudly denouncing others' opinions, especially when the alternative is to just argue for your own opinion and letting me decide.

Where I loudly denounce something it's usually because it is supported by a wave of hype that will drown out muted objections.

I can count 11 or 12 instances of this happening in tech. There's a reason Larry Ellison said the only industry more fashion driven than women's fashion is tech. Tech is extremely herdlike.

If you are going against the herd you kind of have to be loud to get heard. Large corporations with marketing budgets and the people who got caught up in them will drown you out otherwise.

Loud objections can cut through the bullshit-with-a-big-marketing-budget and in many cases (e.g. mongo) I'm pretty glad it did.

>Where you see me using the wrong tool for the job, I see myself using a tool that may be flawed but is good enough for what I need right now.

Sure, but one might also be delluded, and the tool they use be not just "flawed but good enough", but not even "good enough" for what they actually need, if not actively detrimental to their use case.

>Where you see me succumbing to fads and hype, I see you as needlessly conservative, or see myself as trialling a new piece of tech.

Sure, but that might more often than no be delluded, and be indeed "succumbing to fads and hype", and "trialling a new piece of tech" for no good business or technical reason other than that.

So, "none of those is justified" is premised on some misunderstanding from the part of those doing the critique, which is not always (or even often) the case.

Like I said — in any one given situation you might be right and I might be wrong, or vice versa. Disagreement is healthy. My point is just that it's the "loud aversion for peoples choice of tools" bit that is not healthy.

These cases do exist.

These cases are the small minority of cases where other tech folks are yelling at them for doing it wrong. If the tech community cannot distinguish between helpful and harmful advice, it is better to keep quiet.

You can find "X sucks" blog posts for virtually any X in software development. Languages, language paradigms, testing procedures, development lifecycles, hiring practices, management and org structures, support styles, oss licenses. Everything. Most of these hot takes are not helpful.

>You can find "X sucks" blog posts for virtually any X in software development. Languages, language paradigms, testing procedures, development lifecycles, hiring practices, management and org structures, support styles, oss licenses.

Yes, and they're all pretty accurate.

But you can also find "~X sucks". They can't both be right.

You'd be surprised!

>I created an ORM called objection.js for exactly the reasons Thomas listed. The design goal of objection.js is to allow you to use SQL whenever possible and only provide a DSL (Domain Specific Language) or a custom concept when something cannot be easily done using SQL.

That's the exact opposite way I use an ORM.

Query builders (as I call these type of tools) are great, especially when they allow typesafety over the SQL barrier. jOOQ[1] (on JVM) does this with code generation: migrate your db, re-generate the project specific jOOQ lib and all incompatible queries are not showing up red-underlined in your IDE.

I liked this as well in ORMs: the type safety them CAN provide.

Query builders VS ORMs, both have their advantages. Doing a Users.byId(123) is easier to read'n'write than doing so with a query builder. Its the complex queries where ORMs suck, string-SQL gives no type safety and query builders shine (given they provide type safety).

1: https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-...

> Doing a Users.byId(123)

jOOQ can do that as well through various ways if that's a burden: https://www.jooq.org/doc/latest/manual/sql-execution/daos/

I stand corrected. And thanks for chiming in chief :)

It has warts and limitations, but I find the approach that RedBean PHP uses to be very intuitive.

It supports other patterns, but the one where you open up the ORM and let it make the schema dynamically as your code runs, then later "freeze" it, is pretty neat.

See https://www.redbeanphp.com/index.php?p=/quick_tour for an overview. Are there popular ORMs that work this way for other languages?

I'm beginning to suspect a big problem here is one of level-violation. At least in the often promised push-button domainObjectClass to domainEntityTable ORM (batteries included).

Three common (IME) manifestations:

1. the domainObjectClass is disallowed to do something across the ORM due to constraints enforced on the domainEntityTable (varchar truncation, anyone? No? Just me?)

2. some domainObjects fetched through the ORM are deserialized into an implicitly (e.g. null/empty collection members) or explicitly (violates some invariant enforced by domainObjectClass) invalid state

3. domainObjectClass has to grow hairy with bookkeeping metadata (read: implementation details) to facilitate work with partial object graphs while still enforcing domain invariants on the data in scope (null vs DbNull, "object with PK already exists" oh God please just kill me now)

The common cause for the preceding examples is that the ORM (and it's tutorials) encourage you to do the same domain modelling twice, for the price of one. To wit: You do your domain modelling in your classes and the ORM translates this into a database schema with the same entities at the same level of abstraction. This causes a coupling of the semantics of database constraints to your domain objects and vice-versa: your database records may not violate domainObjectClass invariants otherwise inexpressible in the database engine.

Whether or not this is a problem depends on context, but it happens either way. If and when it becomes a problem your options are usually: quit, rant on you blog or both.

I use dapper with a plugin called dapper.simplecrud (https://github.com/ericdc1/Dapper.SimpleCRUD). This already helps to reduce the need to write simple SQL code repeatedly quite a lot. When doing SQL operations more complicated than a simple crud, I resort to raw SQL or stored procedure.

I think real improvement from SQL would be the ability to construct query in sets. Like I'd make a set of persons, check if it looks right, then another set of pets and if both are alright then compose the result set by joining them. Not by cutting and pasting a code but actually using variables. Your examples do everything in one statement which does not look so easy to compose, why?

My favorite reasons for pushing something down into the database include performance, security, concurrency, and portability. Yes, portability. My client language is way more likely to change than my database.

I don't mean that. I just like to construct any nontrivial query piecewise. SQL is about sets, when doing query you in fact make subsets and combine them using set operations. But the syntax makes this composition error prone and objection.js seems not to do much to improve it.

You've sorta captured my distaste for ORMs. I'm good at sets and relational algebra. Anything ORM interface that isn't RA is a leaky abstraction, and eventually I just have to go around it.

SQL has views and functions for this. It's indirect when looking at the code of one application accessing the database, but as the other poster said, those can be many, in different languages, at the same time and over time.

> People always seem to ignore the third option: using an ORM that embraces SQL!

I had a similar revelation several years ago, based on the distinctions between a "domain model" and "persistence model" described by Mehdi Khalili, resulting in Atlas for PHP: http://atlasphp.io

Does this correspond to the data mapper pattern? Seems like people mostly object to active record orm variants.

https://typeorm.io/#/active-record-data-mapper/what-is-the-d...

Yes, the upper/outer layers are a Data Mapper implementation; cf. the Atlas.Mapper package.

FWIW, the Data Mappers are built on top of Table Data Gateways, which in turn are built using Query Builders and a connection locator. This lets you start at lower levels and work your way up, while at the same time allowing you to "reach down" from the upper levels to the lower ones when you need to.

The hate against ORMs is mostly irrational but sadly self-sustaining at this point:

I recently saw someone here honestly believe that the n+1 problem is unsolvable in major ORMs like Hibernate. That person seemed to be delighted to get a pointer to a solution, but way too often many just dismiss all explanations.

If you are still reading you might be a bit interested so here are some things some of you might find interesting (Disclaimer, this is mostly from a PHP/Java/.Net-background):

- When people like me and others choose ORMs it isn't because we don't know SQL, I for example knew SQL long before I touched an ORM.

- There is generally no problem to break out of the ORM if you really need to optimize a statement.

- But to be honest once you get rid of the (famous) n+1 situations ORMs are crazy fast. I think often some of them can be faster for a lot of people just like compilers started outsmarting more and more programmers lately.

I don't mind SQL; my main reason for using an ORM at the moment (gorm, it has its flaws) is one, automatic database migration, and two, I don't have to manually map a resultset to my struct; code like that is tedious and error-prone.

Gorm is not the best when it comes to managing relationships though, to put it mildly.

I'm more averse to people not using their DB to their full extent than whether they're doing that with an ORM or by just writing overly simplistic SQL. Granted, it's a bit more common with ORMs, especially if it's all ActiveRecord. But theoretically you could express yourself sufficiently well with DBIx::Class, SQLAlchemy, Hibernate and whatever's not just stubbed NPM-ware for JS.

Just as long as people avoid too many uneccesary requests and/or do too much in code what could easily be solved by a view, window function, lateral join, stored procedure etc.

And yeah, a lot of that gets thrown out if you need to boil it down to a lowest common denominator to ease testing or different deployments, but a lot of programmers don't even utilize SQlite to a degree beyond beginner's SQL-92.

I was about to comment how similar the examples look to Laravel's Eloquent ORM/query builder. Objection.js says it's built on top of something called Knex.js, and sure enough:

> "Special thanks to Taylor Otwell and his work on the Laravel Query Builder, from which much of the builder's code and syntax was originally derived." [1]

I've always loved Eloquent. It doesn't shy away from the fact that it's never going to be able to do everything a SQL query can, instead it focuses on making the stuff that's tedious and error prone to do by hand really expressive, and lets you drop back to SQL when you really need to.

[1] http://knexjs.org

Objection.js is neat but give me Objection with full type safety (Prisma style) and I am in haven.

The funny thing is that the article here preaches that ORMs are here to make SQL easier, which runs counter to the perceived benefit that your devs won't deal with SQL once an ORM is in place (or atleast that is how I was introduced to Hibernate). I understand the point, but once I am SQL-aware, kinda don't see the point of having the middle layer.

I've certainly fallen in to the anti-ORM camp, because I know SQL well and at least like to think I can put together a schema better than any ORM can. (I usually can.) However, then my code becomes tightly coupled to the schema, despite my abstractions, which makes it harder to change and basically impossible to unit test.

What do you end up doing, then?

Hope I get it right (or right enough) first time an then worry about how I'm going to set up integration testing for this "thing" :P

I make my own abstractions. I might have a file called 'kvs' which implements a key-value-store abstraction, or one called 'friendnet' which implements a graph of social relations.

* Those files have all the SQL. I never have raw SQL in my main application.

* Those files never e.g. call into my web framework, have HTML, or otherwise. They're a stand-along abstraction.

An ORM mutates a good abstraction (SQL) into a not-as-good one. Making a generic abstraction which works everywhere is hard, and SQL is the best one we've got. By making it domain-specific, we can mutate it into a good one.

One of the key principles I use is to try to have more than one back-end. For some (like a kvs), that might be Elasticache AND in-memory AND postgres. For something relational, it might be postgres and sqlite.

Normally, i go by ORM by default.

If i know it's a heavy query or the ORM won't optimize it ( eg. lots of relations and creating a n+1 problem), then i do the query myselve.

If something is going slow, I try to optimize it. If that means writing SQL, sure. Why not

A programmer's toolbox contains multiple tools. Use the right one.

Not a fan of ORMs at all, especially ActiveRecord style. They just bring a costly new layer of complexity and not enough value to justify it.

I do very much like the object hydration that ORMs bring though. I think libraries like Dapper hit the perfect balance of manual everything vs automagic complexity.

JavaScript ORM hatred is well deserved. None of them are great. TypeORM is probably the best but its documentation leaves alot to be desired and there are many rough edges you have to watch out for.

I like Mongoose for MongoDB. For my dinky API and mobile app, it does exactly what I need it to.

Have you looked at Prisma? The documentation is very extensive.

Would love to hear your thoughts.

Disclaimer: I work there.

I inherited TypeORM and I've used Sequelize in the past. I looked at Prisma out of curiosity. My main concerns were:

1. Focus on integrating with tech I don't care about (e.g Next.js, GraphQL).

2. Don't understand the business model. Seems like you want to lock folks into your ecosystem and then start charging for the surrounding products (e.g. Studio, Migrate).

Nikolas here, I'm Daniel's (2color) colleague :) quickly want to follow up!

> 1. Focus on integrating with tech I don't care about (e.g Next.js, GraphQL).

Just to clarify, we don't focus on any particular stack but Prisma can be used with any framework/library you like! Would love to learn more about what exactly gave you the impression that we'd focus on Next.js and GraphQL so that we can make sure folks don't misinterpret this in the future :)

> 2. Don't understand the business model. Seems like you want to lock folks into your ecosystem and then start charging for the surrounding products (e.g. Studio, Migrate).

We are commercial open source company, similar to companies like Hashi, Vercel or Gatsby. It's not a secret that we are a VC backed company and will need to make money eventually. In fact, we're currently working on a first version of a commercial cloud product that, among other things, will enable developers to collaborate better when using Prisma's open source tools in their projects. This commercial offering will be primarily targeted at teams and larger development organizations.

Nonetheless, the open source tools remain the core of our business and we're investing a lot of time and energy into them. Nobody will be forced to use the commercial services if all you need for your app is an ORM :)_

Came here to object to objection to ORM hatred.

ORM were never worth the trouble. There's on "impedance mismatch", you just use data wrong (wrapped as objects as opposed to data records).

Nobody uses relational data anymore except when doing analytics or aggregation.

Relational data is almost always mapped to an object.

>Nobody uses relational data anymore

Source?

I meant using the relational data from a query directly. The moment you're doing a join, you're simply mapping to an object manually

I think it's more that people don't seem to know what relational actually means, and schools are doing a terrible job in teaching it. And so we see the results.

Care to explain?

I'm not porcupine, but I feel that we are definitely using relational models. The drive towards objects (and REST as well) tries to project data into a hierarchy. Orders belong to customers, so you nest orders under customers. But you are B2B, so you break into one market and realize that suddenly joint purchases are a thing. Suddenly you have orders with multiple customers.

If you were using a relational model for backing the orders and customers, there is no real issue. An order is just a tuple of data, a customer is just a tuple of data, the "belongingness" is just modeled via foreign key constraints. Maybe you were smart enough to already model it as many to many and you just relax a uniqueness constraint in the join table or you had no such uniqueness constraint already and it was enforced by the application. Otherwise, you move the foreign key out of the order tuple into a joining table and move on to changing the projection queries and the downstreams. If you were storing it as nested data documents... I don't know because I have never done that, but I do know the trouble with trying to invert hierarchical addresses in such cases and it is annoying and inefficient (abstraction inversion).

In a relational model, any hierarchy is a contrivance via foreign key constraints. These same attributes are easy to invert. If you have a business need for a hierarchy, that shows up in the business logic by the precise query used to project the relations along the foreign keys.

In the scope of the comment made. I find it hard to believe that someone doesn't see that we are indeed using relational models of data and I find it sad that the known benefits apparently aren't so widely known. Yes, in a concrete use case you project out the data you need and type that (map it to objects). But if you modeled the data well, as your use cases increase in number or evolve your data model often doesn't need to change as much as your business logic if you did your job well. The job being relational data modelling.

I myself use async programming, and there aren't really good ORMs for that since you don't want every single property of your object to be an async property (which most ORMs do with lazy loading).

Yes, this is roughly what I was getting at.

Relations are sets of sets, and the relationships are products of the query method, not a static property of the storage of the data. This insight by Date and Codd was a huge discovery in data management.

Unfortunately SQL tended to paper over some of this beauty with its talk about "tables" with "columns", which are just one way of representing relation and tuples. I can't count the number of times I've heard someone say "but my data doesn't fit into a table", completely not getting it.

That combined with the rapid rise in object-oriented programming through the 90s and early 2000s, as well as a bunch of other things I won't get into, led to many people mistreating and/or rejecting the relational database.

We were taught, or taught ourselves to model our applications in an object-oriented way, and then became frustrated because we treated the database as a "datastore" rather than an information modeling and retrieval tool like it was meant.

Without an understanding of relational fundamentals people repeated the same mistakes that Date and Codd were originally trying to rectify with their pioneering work on the relational database. The relational model was a response to the problems with hierarchical and network databases; to try and formalize a more flexible model, with a mathematical and logical basis.

"Graph databases" and "document databases" and "object databases" _look_ more flexible from the front, but fundamentally are the opposite. They confine the kinds of relationships data can take, in advance.

The object oriented model is good for representing some aspects of our world; especially, perhaps, graphical interfaces, or simulation systems. In my opinion it is inferior for the modeling of data/information itself.

We need a relational renaissance.

Hello, J2EE circa 2002 called.

Ahh.. the beautiful times of the .war and .ear files. So much information got lost from J2EE, and now we're rebuilding things with so many different tools.

Tell that to the company I used to work for that build custom business systems where all business logic and all the crud was written as stored procedures in SQL Server.

In particular CRUD maps very nicely to SQL, and there’s no reason to map it to objects.

This looks a lot like LINQ to SQL. Is it the same? I'm surprised the article doesn't mention LINQ.

Nor does it mention jOOQ [1], arguably even more similar (as it is a lib and not a language built-in).

Some page with actual queries from the docs: https://www.jooq.org/doc/3.14/manual-single-page/#jooq-in-7-...

Obligatory mention of the (in)famous article, "ORM is the Vietnam of Computer Science", frequently linked to from HN: http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...

The Vietnam of Computer Science goes into more detail with the problems that plague ORMs and why some people have "ORM hatred". Some of these reasons are glossed over or not mentioned by TFA.

ORMs solve several problems that depending on what you do may or may not be important for you.

Problem #1: translating between objects/classes and table rows is mildly tedious to program depending on what language you use. For small amounts of tables it's actually not that much and it's relatively straightforward to maintain this. I'd argue that having a large number of tables is probably a result of using an ORM and encountering the object impedance mismatch. So, it's a problem you aggravate by using an ORM and not doing a proper domain design. More tables usually means more joins. And that's only convenient because the ORM does this for you. It's also slow and a bit of an anti-pattern whether you use ORMs or not.

Problem #2: writing SQL natively is hard (for some). It can be of course; and you can do some complicated things in SQL. But if your table structure is simple, it's actually fairly easy to write the handful of queries you need. Being able to test and prototype your queries against a development database actually makes it easier. Also, this is another problem that is may be made worse by using an ORM because having too many tables means your queries become a mess of nested joins.

Problem #3: porting queries between different databases is hard as they each have their own SQL dialects and subtle implementation differences. Having your ORM generate the appropriate queries for you side steps this problem. But of course you then lock yourself in to your ORMs way of writing queries making switching to another ORM harder. Also, you get to deal with the limitations of your ORMs support for SQL, which typically is a subset of native features. Also, switching databases is not actually that common as it is quite invasive to do. And of course they can be different enough that even using an in memory database for tests and postgresql for production is going to cause you issues with subtle differences in transaction semantics, or how certain queries work, etc. I usually side step that by just using a dockerized db. That's valid whether you use ORMs or not.

Whether you use ORMs or not, good database design is important and knowing enough SQL that you don't need an ORM is kind of a pre-requisite for that. I've seen a lot of bad ORM usage where the people using it were quite clueless about that and made a lot of rookie mistakes. You can't blame the tools for that obviously.

In my case, the above 3 problems are not really problems I have or that are high on my list of solving. I keep my table structure simple and easy to query. Whenever possible, I dumb my schema down to a glorified document store with just a handful of columns. I can knock those queries out in SQL easily and generally I don't migrate between different databases. In any case, most of my projects also use Elasticsearch so most of my querying is actually done against that instead.

Reply on news.ycombinator.com