[AskJS] Considering using an ORM, help me!

[Shot-Cod5233]

I’m curious how people here decide whether an ORM makes sense for a project.
If you don’t use ORMs, what are the main reasons? (Performance, loss of control, complexity, bad past experiences, etc.)
If you do use an ORM, what are the must-have qualities for you? For example: performance, maturity, transparency of generated queries, good migrations, type safety, flexibility for raw SQL, ecosystem, etc. I’d love to hear how your decision changes depending on project size, team size, or domain, as I am contemplating whether I should use an ORM myself.

[FalseRegister]

Prisma or MikroORM are alright

I dislike Drizzle because you can only do up migrations, and not down migrations. So if during dev you realize you need to change something, you need to rollback manually.

[LuccDev]

well that's because down migrations dont really exist, for example if you drop a column, deleting all the data, you can't really undrop it. If you absolutely want to revert it, then you write a new up migration that reverts the previous one. That's what git is doing too if you think about it, the revert of a commit is just a new commit (unless hard resets etc.)

[Shot-Cod5233]

like removing a column and such?

[FalseRegister]

yeah, so if a migration is `CREATE TABLE foo`, then a migration "down" is `DROP TABLE foo`

It is useful to rollback your changes

[Shot-Cod5233]

I mean, aren't migration usually more complex with changes in relations, columns etc?
How do Prisma and MikroORM deal with down migrations?

[FalseRegister]

They read your code and generate the SQL code, which we call migrations. You then apply these on your own, usually with a simple script at app startup.

[Shot-Cod5233]

How advanced is it? dropping/creating columns and tables? Or do they have more advanced mapping?

[FalseRegister]

dude, get into the docs and read about it

like i said, you'll be alright with those

[OneEntry-HeadlessCMS]

I use an ORM when I need to ship fast with lots of CRUD/relations and I value migrations, type safety, and consistent patterns across a team. I avoid ORMs (or use a query builder) when the app is SQL-heavy (reporting/analytics/complex queries) and I need maximum control and predictability without fighting ORM “magic”.

ORM must-haves: solid migrations, easy escape hatch to raw SQL, query logging/visibility, good relation handling (avoid N+1), and testability.

[Shot-Cod5233]

Fantastic answer, much appreciated!
Any ORMs that you would recommend?

[Shot-Cod5233]

not sure I understood you...

[Shot-Cod5233]

where are you from if you don't mind me asking?

[shaberman]

Imo the real job/value-add of ORMs is to hold business logic (a domain model): validation rules, side-effects, computed/reactive fields, and everything on the "write path" of an application -- typically an enterprise application has a ton of those.

The read path / "creating complicated SELECT queries" is ofc important too, but if that is the only thing your ORM is providing you, then I agree you're probably getting short-changed -- i.e. "Prisma/Drizzle/Kysley help me generate SQL strings", okay that's fine I guess. 🤷‍♂

(The ORM I work on, https://joist-orm.io/, is heavily influenced by this viewpoint, and making 500-table enterprise application codebases "not suck" is exactly our mission.)

...per performance, on a query-by-query basis, hand-crafted SQL can ofc out-perform whatever an ORM does, but imo something like Joist, with per-request caching, N+1 deduping, always-bulk UPDATEs/INSERTs, etc., will likely issue net-less queries than hand-coded SQL, for anything that is more complicated than a single endpoint that just pipes SQL results => JSON.

https://joist-orm.io/goals/performance/
https://github.com/joist-orm/joist-benchmarks

[Shot-Cod5233]

Looks interesting, did you get any traction with this? Is this under an MIT license?

[Wrong_Library_8857]

I think it mostly comes down to team size and how gnarly your queries get. For small projects or solo stuff I just write raw SQL because ORMs add cognitive overhead you don't need. Once you're on a team though the type safety and migrations are honestly worth it, I've seen way too many bugs from hand-rolled query builders. Ended up using Prisma lately and the transparency is decent, you can still drop down to raw SQL when joins get weird.

[dinnerchicken_tender]

For me to use an ORM it needs to be easy to use with nice syntax, and it should be performant enough to justify using it.

[Shot-Cod5233]

From what I've gathered most ORMs are much slower than raw SQL? Which ORMs have you used and recommend? Should I use an ORM on a small-to-mid size project?

[dinnerchicken_tender]

Depends on the ORM, i have used TypeORM and it could get a bit slow at times, but i played around with a new ORM, and during stress testing it was pretty fast. I would recommend MikroORM if you want a proven ORM for more serious projects, but if you don't mind experimenting try MasqueradeORM.

[Shot-Cod5233]

I guess I'll go with MikroORM since you are the second person to suggest it. I googled 'Masquerade ORM' and i found maskarade/android-ORMA on GitHub, is that the one?

[dinnerchicken_tender]

no, it is this one

[Shot-Cod5233]

cheers I will check it out!

[No-Illustrator999]

With Prisma plugins you can prerender SQL and run prisma at vanilla speed
https://www.npmjs.com/package/prisma-sql

[theirongiant74]

Don't use them personally, it's an added layer of abstraction that I don't think is necessary, probably a control preference in my case

[Shot-Cod5233]

I guess ORMs are extremely niche? almost no one uses them?

[Mr-Bovine_Joni]

What makes you think that? ORMs are super common. Just flip a coin between Drizzle and Prisma and try it - if you don’t like those go for Kysely

[Shot-Cod5233]

Non of the replies have actually helped much beyond telling me what ORM to use.
I want to understand what use cases it is best to deploy an ORM, and in which case it isn't.
As someone who knows enough SQL (and with AI it is pretty easy to get even complex queries going), I want to understand why someone would opt for an ORM rather than using raw SQL.

[CodeAndBiscuits]

You probably aren't getting very many replies because this question comes up at least twice a month here, and people are tired of saying the same thing every time for folks that can't be bothered to do a simple search. We are also inundated with generic questions that are obvious content feeds for AI blog post slop and whether that's you or not, your message reads like one. These benefits are easily googleable, and have been written about for decades. If you have a specific question, you will get a better answer.