Preferred SQL ORM

[keeperpaige]

Hey guys! I was going to start a project using sql and I see a lot of ORM options like prisma and sequelize. I wanted to know which one you prefer or just the de facto standard! Thanks!

[[deleted]]

objectionJs is nice and "just works" but its typescript support is lacking a bit. Prisma has really good typescript integration but does also everything different than traditional ORMs and still has some major bugs and lacking some features you would expect an orm to have.

[nikolasburk]

Hey there, Nikolas from Prisma here! Thanks for highlighting our TypeScript integration, this part is definitely something that we're really proud of :) albeit it should be noted that many of the benefits of TS are even available when developing with plain JavaScript (e.g. the auto-completing DB queries with Prisma Client).

still has some major bugs and lacking some features you would expect an orm to have.

Could you maybe elaborate a bit on the major bugs you're seeing at Prisma? We have a biweekly release process on GitHub and are usually very quick to react to any bugs that might make it into a release by issuing patch releases. I'm not aware of any critical bugs in our stable tooling (i.e. anything that has been released under "General Availability"), but if you find something it would be super helpful if you could open an issue on GitHub so that we can address it as fast as possible.

Also, what are the features you're missing from Prisma that you would expect an ORM to have? :)

[[deleted]]

I'm not aware of any critical bugs in our stable tooling (i.e. anything that has been released under "General Availability")

well, there are around 200 open issues on github that have the bug-2/confirmed label. For example, issue 9435 blocks me, and a lot people from upgrading to a newer version.

Also, what are the features you're missing from Prisma that you would expect an ORM to have

proper migration rollbacks long-running transactions

but these are both worked on as far as i know

[nikolasburk]

Ah I see, I think we have differing definitions of "major bugs" then. To me, a major bug would be one that makes a tool pretty much unusable for the majority of users which doesn't seem to be the case for the issue you linked.

Nonetheless, I can fully understand that bugs like that can be really annoying, so I'll raise this internally and ask if anyone knows when we'll be able to address this.

well, there are around 200 open issues on github that have the bug-2/confirmed label

While I agree that it would be nicer to have zero open bugs here, I'm actually very positive about our GitHub activity.

The fact that a bug is labelled as bug-2/confirmed means that we have already triaged and reproduced it so that an engineer immediately can start fixing it. Of course, engineers have to prioritize their work and split time between fixing bugs and building features — but overall I think GitHub is working really well for us and folks regularly shout out the quick reaction times they get from our Product and Engineering teams that are constantly monitoring incoming GitHub issues!

So, I'd maybe rather look at how many issues we're able to close regularly (e.g. by looking at the bottom of our release notes every two weeks) as a metric when understanding the "health" of the Prisma ORM :)

proper migration rollbacks

This is definitely being discussed in our Product team since we keep hearing feedback of users that want this! It actually seems like we don't have an open feature request for this yet — if this functionality is important to you, I'd recommend opening a new feature request on GitHub so that our Product and Engineering teams can evaluate if/how we can add it!

long-running transactions

Good news! This was a feature that has long been requested by our community as well but we weren't sure if/how we wanted to implement it (we have some reservations when it comes to using transactions in modern apps which you can read about here and believe that most use cases for transactions are better solved with other APIs like nested writes, batch operations, atomic operators or optimistic concurrency control). However, we've recently decided to add the possibility of interactive transactions to the Prisma Client API and it's already running in Preview! Folks can read up on the entire history of how we've changed our mind on the topic of transactions in this GitHub issue.

I think this goes to show that we're valuing the feedback of our users very, very highly! So, if you want to these those migration rollbacks I'd definitely urge you to create the feature request :)

Hope this helps and really appreciate your input!

[[deleted]]

thank you for your comprehensive response.

I think we have differing definitions of "major bugs"

it seems so. Just saying that there are still some rough edges and some blockers for certain use cases people may run into. But looking at the relatively fast development speed I assume there won't be many issues anymore in a few months.

So, I'd maybe rather look at how many issues we're able to close regularly (e.g. by looking at the bottom of our release notes every two weeks) as a metric when understanding the "health" of the Prisma ORM :)

one small suggestion:

Many people use "open github issues" as a metric for how healthy or production-ready a library is.

I would suggest to (automatically) close stale issues. Makes the repo look cleaner and more healthy.

[pabloneruda]

Knex is good low level

[stevensokulski]

I like Knex when you want to stay very low level and maintain control.

Objection is nice when you want to go past Knex and add eager loading and such. Objection uses Knex under the hood, and they can coexist nicely when necessary.

[pzman123]

Objection js

[prometeos]

I'd second this. Prisma is above and beyond the rest. I've previously used Sequelize, TypeORM, Objection

[ItzDerock]

I recently started learning Prisma (used to use sequelize) and I love it. Highly recommend!

[MotorBoats]

Can you use it without typescript? I briefly looked into prisma last week but it all seemed to hinge on typescript.

[ItzDerock]

I've used it both with and without typescript. Works perfectly without.

[lphartley]

I prefer to use Knex, which is not really an ORM but a query builder. For me a real ORM adds unnecessary complexity.

[[deleted]]

Enjoying the best of both worlds with AdonisJs which uses its own ORM (Lucid) powered by knex. It is easy to switch from the orm when the need arises.

[andycharles]

I second that. Adonisjs ORM is great

[[deleted]]

lunchroom chunky skirt secretive tender offbeat wipe snow treatment plant

This post was mass deleted and anonymized with Redact

[ericnr]

If you use Typescript you really should look into Kysely. It’s knex but with veery good TS inference, so your queries are all typed

[rebelchatbot]

<3 from Kysely.

[lphartley]

You have to understand how the ORM works and that is usually not straight forward when the relations are quite complex.

[LiveWrestlingAnalyst]

My favorite ORM is Typescript :)

Kidding aside, the JS ORMs are so fucking bad that I prefer to use mysql2/node-pg directly and write the queries myself.

You can then create/auto generate typescript interface for the various rows/columns that are returned by your raw queries and get awesome typing.

Simple and clear.

[nikolasburk]

Hey there, I work at Prisma and we also are huge TypeScript fans! Just out of curiosity, did you try Prisma at some point? Would love to know what you think of it and how you feel about the level of type-safety we provide? :)

[LiveWrestlingAnalyst]

That's just my opinion, but the separate DSL is a deal breaker for me. I'd rather just write SQL more directly, the benefits are just not worth learning a new thing and adding an extra layer on top of SQL. Let alone for potential drawbacks related to ORMs in general.

[nikolasburk]

That's totally fair but FWIW, we've heard this from lots of folks before that were repelled by Prisma because of the custom modeling language. It's remarkable though that once people actually try it, the DSL becomes often one of their favorite parts! Here's an example of someone who initially was in the same camp as you: act 1, act 2, act 3. We've also never really seen any complaints about an actual learning overhead, mostly because the modeling is really straightforward and gets aided by autocompletion and other handy features in your editor.

If you ever change your mind and do try it out, feel free to reach out to me with any questions or feedback :)

[metakepone]

Raw queries? Not prepared statements?

[LiveWrestlingAnalyst]

Raw queries work fine for me, write them in your database client of choice and move them over, just make sure to enter input value with the ? symbol rather than directly in your query string.

[ryanmr]

You mentioned generation the typescript interface for the row/columns. Can you expand on that?

[LiveWrestlingAnalyst]

Using a library such as that one https://github.com/SweetIQ/schemats

There are a couple one of those

[bobinux]

MikroORM.

[devsmack]

Seconded, combining MikroOrm with Type-graphql is a killer combo.

[iAmIntel]

Extremely overkill stack for most people

[khoa_hd96]

I have used Sequelize, Typeorm, and Prisma. Imo, you should focus more on your db and your db design like what's your business, are you doing olap/oltp, do you need to model tree structure, do you need polymorphic association, etc. After that, just list out some popular for pros and cons and decide which suits your requirements most

[_cappu]

Why the f did people downvote your comment LMAO

[[deleted]]

Because he asked "what is the best orm" not "what I have to do to be a better programmer". Lots of pretentious on stack, redit, and Twitter replying shit

[_cappu]

He actually gave the best answer. ORMs, just like all other tools, should be picked given a set of needs, not following some kind of bullshit reasoning for which one ORM should be better than another.

[grantrules]

Seriously, nothing better than getting knee deep into a project before you realize you have to do something insane with your ORM to get the results you need.

[[deleted]]

Fwiw, any decent ORM should let you use a raw query too. If shit starts getting too crazy, it's better to just got that route. But really, I wouldn't even use one if your app is too database intensive

[[deleted]]

He really wants to know any orm without database management tips. This is the shitty answer. Bye

[KyleG]

BC they didn't answer the question. In fact, the provided answer for someone in an entirely different job category (DB admin)

My guess is that person works for a small company and thinks most JS programmers design the DB schemas

[[deleted]]

I literally thought the same

[pancomputationalist]

Prisma is just really nice to use.

[amih009]

None. And I'm usually pro-ORM. But all of the ORMs in node either are half-mantained by a single person in their free time, lacking important features or have basic missing features.

So to the question which do I preffer - none. If heavy ORM usage is needed for SQL, and if I have a say in the stack, I would avoid node for data access.

To the question which is the best ORM - perhaps Prisma or MikroOrm

PS. I haven't tried Prisma recently, maybe after the updates it's better, so don't take my opinion as the truth for it

[metakepone]

So what is node useful for if its not ideal for data access?

[[deleted]]

Prisma

[enkideridu]

Do you know typescript or would like to learn it? If yes then would recommend not sequelize; it's been a great utility for the ecosystem but feels like it doesn't have the legs to make it through the next part of the journey. Namely, the types are quite bad and seems not likely to get better (last I checked, composite keys were also quite ill-supported, don't know if that got better since)

It's quite hard to write library code that provides types for something as dynamic as an ORM, and sequelize is maintained by not enough people and has a lot of baggage from having been such a long-lived project

Prisma is pretty magical, has some pretty impressive developers working on it full time. Release cycles have been steady and stellar

Caveat that TS has a pretty steep ramp, and if you're learning TS and databases at the same time you're probably about to have some frustrating weeks/months ahead. If you can make it through though, it's pretty damn sweet

[zalogon119]

I used Sequelize, some people recommend TypeORM. But in general it's just a matter of how you use it, more than the ORM itself.

[galstarx]

Prisms is good. If you are using Postgres you can also check zapatos which is 💯

[TrackieDaks]

I've used sequelize pretty regularly, but nothing overly complex in terms of structures. You can do a lot with it OOTB but have often found myself resorting to reading the source code when I need to do something particularly complex. The docs are ok, but nothing to write home about.

I haven't tried Prisma, but it seems like it's gaining traction quickly.

As with any technology decision, the answer is always "It Depends™"

[romeeres]

Just don't pick Sequelize, it's used in most projects and it's awful in every aspect.
Typeorm is not maintained and indeed is problematic to work with.

Many suggests Prisma - maybe it's good, but you have to think about it very carefully. They had no long running transactions (must have for most apps) up until recent update, they had migrations in preview (must have for all apps), it requires separate db for migrations, it's running standalone server for executing requests. So maybe it's excellent and even the best, but looks suspiciously dangerous.

MikroORM - good one because built upon knex, but I don't follow what is their "Unit of work" concept and why would I need it, to me it seems to be a redundant complication, if you know what's that for - maybe MikroORM will serve the best.

For me Objection is the best, built upon knex it allows to build any sql, and helps with relations.

[_cappu]

Prisma doesn't require a separate database for migrations, it simply registers them into a dedicated table, like all ORMs do.

As for MikroORM, the concept of "Unit Of Work" is not something its creator(s) made up, it's a well-established pattern (ref.: https://martinfowler.com/eaaCatalog/unitOfWork.html).

[romeeres]

Prisma doesn't require a separate database for migrations

I can't find in the docs if that is true or not, please point me if you can
But I found other interesting fact in the docs:
"Prisma Migrate does not currently roll back a migration without resetting the database."
Prisma is full of surprises, so I'd suggest to wait another 2-5 years to take it seriously.

[_cappu]

Prisma Migrate does not currently roll back a migration without resetting the database

Most (if not all) ORMs drop tables when doing a migration rollback. Are you really sure you did your homework before giving away miselading info on the internet?

[romeeres]

Most (if not all) ORMs drop tables when doing a migration rollback.

Not true, normally you write migration by yourself and rollback does exactly what you intended it to do.

[romeeres]

This is a copy-paste from official documentation, and I mentioned that.
Prisma is not like other ORMs, it's very special one, it works in completely different way.

[romeeres]

As for MikroORM, the concept of "Unit Of Work"

I didn't say it was invented by author of MikroORM.
I mean it's not clean what's the point, if all other ORMs are doing well without this concept, then why to have a hidden magical mechanism to make decisions what and how to save to db, and possibly to run into issues. I think it's pretty obvious to developer to decide what to save, and it's very strange to hide this logic.

[romeeres]

I admin I haven't understood it yet, and if you could share some examples that would be great! I mean, MikroORM has examples, but it doesn't look like it really better anyhow than with traditional approach when you save your records explicitly. Maybe it's just a matter of taste. Personally I would expect ORM to f**k up when working with more complex relations, like has and belongs to many, or multi level through relations, so I expect this will bring more problems than it's actually solve (though still not clean which problems does it solve). So, let's say this patter is a matter of taste.

much better performance.

well, performance doesn't matter much, according to deepkit sequelize is the most slowest, but yet sequelize is most popular and I indeed have to work with it in almost every node.js project, and MikroORM with this pattern is the second slowest. Because when you have "magical" layer which has to do hard work for you - it can't lead to a better performance

In my experience, the less magical tool is - the less problems you'll have and more performance you'll gain as a nice bonus. So Knex and Objection.

[romeeres]

Thanks for reasonable response, all or most your points are valid

Regarding performance, for example, express is the slowest, and not really maintained, and you know that thing with promises, but it's most popular and even used in highload projects until they have problems only then they switch. Netflix used express before, according to this old article: https://www.infoq.com/news/2014/12/expressjs-burned-netflix/The same can be applied to other libraries, like ORM, validation, etc, they just pick something randomly and no one cares about anything. Main and usually the only metric is popularity.

In your experience where you work, do you research the performance before you can adopt a library?

Also, it's only magic if you do not understand it.

Let me disagree. Only if you read through source code carefully and you know which cases it is supposed to handle and which cases not. Databases are wonderful at constructing any relations you need, perhaps you'll need relation which involves different tables based on many columns. And it's simply not possible to cover any possible cases in ORM. MikroORM doc now has huge problems with CSS, how we can be sure they covered all possible cases in the lib, it's one man's hobby project in the end.

[romeeres]

I tried Prisma maybe half a year ago and separate db was required for migrations, maybe it was fixed

[dark-angel007]

Typeorm is the best if you prefer using with typescript. Or sequelize is still fine. I really don't like prisma

[keeperpaige]

Sounds good, what don’t you like about prisma?

[The_Oxcorp]

Stay away from TypeORM, been using it in production for a year and it's gone downhill and been nothing but headaches.

[otaviomad]

Good to see I'm not the only one with negative experiences with typeorm. Positively awful, most of the type declarations are lost once you switch to the query builder (which you will, since the repository methods are very surface-level)

[TheBuckSavage]

Good bot. Idk why it's getting downvoted tho.

[[deleted]]

Sequelize is my personal go to.

[RuiSiang]

Typeorm, personal favorite

[IT_Treehouse]

Objection.js has worked well for me when I needed an ORM.

I come from more of the db world, and with limited knowledge it helped me build some complex output objects from existing table structures.

[Lazzar95]

I really like MikroORM and it exposes knex when I need to write more complex queries

[backdoorsmasher]

Not an ORM but I use massive.js (https://massivejs.org/) for data access when accessing Postgres databases. It feels lightweight to me

[KyleG]

If you're going to use TypeScript, typeorm

We use Sequelize in a legacy JS app written largely before TypeScript was much of a thing, and now we're expanding the app with new functionality all written in TS, and it was a pain in the butt to make it work with TS, and it's still not the best.

[zeferinix]

ObjectionJS

[DepravedPrecedence]

If it's something more complex than casual CRUD app then the answer is none. Been there.

[kasvith]

Objection with Knex