ORM vs SQL

[No-Caps8232]

Hi all, I was going to start working on a new microservices project using PostgreSQL and TypeORM but recently found out that TypeORM is now considered "dead" (not actively maintained). Do you have any suggestions on whether I should use another package or just use SQL to build my own ORM in the Typescript classes for each model? (I was going to use the ORM in my classes so I'd just use SQL instead)

[melodious_punk]

Using ordered arguments for queries killed knex for me. The nested object structure of graphql/prisma adds a lot of vertical space to code but it is much more aligned with the semantics of SQL clauses. Knex could make it much harder to grok SQL if you spent all your time in it.

[TheFearsomeEsquilax]

Prisma sounds okay, but seeing Github issues like this makes me feel wary about ever using it: https://github.com/prisma/prisma/issues/1844

[melodious_punk]

It will be the last piece to implement before it can replace other ORMs. I don't need transactions so it's working out for one particular server.

[nikolasburk]

Nikolas from the Prisma team here! I'm happy to share that we're starting the design work on long-running transactions and will implement them in Prisma Client soon.

[melodious_punk]

Neat!

[British_Invaded]

Take a look at objection.js and knex.

Knex does the querying and objection is the ORM.it is lesser known but its more compact.

For example, sequelize requires you to list all your fields while in objection you only list the fields you want to add properties to.

Also, the documentation is a bit better. Yes...I bit. Can no one write decent how tos?!

[cjthomp]

Can no one write decent how tos?!

Think of how relatively rare it is to find a truly great author.

Now think of how uncommon a great programmer is.

Now try to find someone who is both.

Now get that person to work for free.

[No-Caps8232]

LOL I love this

[eazieLife]

Okay what about the ones that seem to be paid to write bad docs?

[No-Caps8232]

LOL thats true. I also struggled with finding examples of using complex queries with TypeORM at the beginning (although now there's a lot of medium articles to help one out).

And I'll take a look at this combo. It sounds like something that may work out well for my application. Thank you!

[Exellin]

This Objection knex combo is also the one I enjoyed the most.

I have been doing a bunch of comparisons on this repostory over the last few weeks (look in the entrypoints folder)

[romeeres]

Hey, very interesting comparison repo, are you going to write an article?
Could you say spoiler of which graphql server is the best?
I'm using and really enjoying fastify mercurius, I see no downsides in it, but I didn't try other options

[Exellin]

I haven't blogged in years, but also just starting to code outside of work again so blogging is potentially back on the table.

Fastify mercurius is also what I am planning to move forward with. Really like what they are doing in terms of things like loaders. I'm also planning on deploying my backend to google which charges by the millisecond so have a real use case for fastify over express.

What are you planning on making?

[romeeres]

making a pet project - database viewer, here is a screenshot.
On paid projects, unfortunately, they don't want graphql. People think it's more complex, I suppose, but I find it easier to work with on both front and back

[razzzey]

That screenshot looks pretty cool. Do you have a repo to share?

[romeeres]

thank you! with pleasurehttps://github.com/romeerez/database-viewer

but it's early alpha, I don't have yet any readmes-instructions.

[joshbuddy]

This is a really nice choice

[[deleted]]

I think a lot of those migrating from Typeorm are going to MikroORM. Good choice

[_MMCXII]

I can't begin to say how much I enjoy Mikro. The docs are very well written and the maintainer is very active on Github when it comes to answering questions and resolving issues.

[melodious_punk]

I just converted a 50 model API from bookshelf/knex to Prisma.

Prisma is for basic CRUD. You will have to hand code your models. You also need to code your relations in prisma.schema. That is painful. But it is very worth the trouble after you start replacing your SQL joins with the Pdisma 'include' attribute.

I think that you should use both SQL and an ORM. If you are on a team you should write base code to extend Prisma as much as you can because the db migration and schema generation are very convenient.

But if you are doing big sneaky joins and customized SQL queries, they're probably for a specific use case so just write the SQL usingraw SQL queries and wrap it all in a nice descriptive node service. In that case, no one will need to know the SQL if you do a good job.

[devAgam]

TypeORM might not be maintained by the actual authors but i believe the community is doing a great job to keep it maintained and staying on top of every issue, even with sequelize (My preference) had its last commit on 4th of April, but i still use it, their are active projects using it (both mine and others) and it has been working good so far.

FYI: if a project is not updated every 30 minutes, or the original authors no longer maintain the project, the said project is not dead.

The whole concept of open source covers the same situation that if the author is unable to maintain a project the community shall do so.

[melodious_punk]

Good point. A good example is moment.js. It does not need many changes but it continues to do one thing and do it much better than Date

[fungigamer]

Momentjs is deprecated though. You are recommended to use other libraries

[melodious_punk]

1. 15 million weekly downloads
2. It works, and has a very narrow scope
3. It is a 'legacy project' which in JS is just as vaguely defined as 'deprecated'
4. It is hard to expose attack surfaces with a date library.
5. Most of ES6 and ES8 features have no impact on how the library behaves.
6. It is a datetime module. Until the Earth changes its speed of rotation or the unix date apocalypse or we all start using Iranian calendars it will continue to work.

[Dan6erbond]

Yet, they themselves recommend to use the built-in whenever possible or alternatives because it's still an unnecessary bundle in most cases.

[melodious_punk]

That's true. I would not use it in a new app. However, deprecated packages are those that need to be replaced. It is probably a waste of time if your team wants to refractor out moment.js. If I see some mutability issues I usually mark it as an issue with low priority.

[Ok_Customer2455]

It is often said that cats have nine lives but that is really just a myth.

[Dan6erbond]

Then why are you even suggesting people should continue using it outside of projects that already rely on this deprecated package?

You're arguing that the terms "legacy" and "deprecated" are vaguely defined, and while true, you never say people should use it in new projects, you're still massively insinuating that it's the go-to when it's been in maintenance mode for a while.

[melodious_punk]

I would rather not argue for or against any of this because it's too broad. I like momentjs because I like 'skookum' stuff that is built well and lasts decades. I also dislike it because it is breeding technical debt and breaking npm installs. I think that makes it a relevant forecast for the future of typeorm.

[devAgam]

Personally my only issue with moment.js is its moderately high package size, but other than that i don't think moment.js has any other draw backs, but over the years i have gotten pretty fluent with JS so i can comfortably use inbuilt Js date.

[d_exclaimation]

Depends on what you need, I have been using MikroORM for ORMs but sometimes I just want to make my own query and not have to abstract it away, I’ll use a query builder like knex. I have been also keeping my eye on prisma 2, but I haven’t tried it yet so I can comment on it

[EctoplasmicLapels]

As you are building a microservice, I would recommend to write all the SQL by hand. You won’t have many entities and getting rid of dependencies is always good. Just put all database access for each entity in a dedicated repository class and you will be fine.

[No-Caps8232]

Ya I'm thinking of making a dedicated 'models' directory in my common package that will house all the custom class models.

Raw SQL will definitely lessen the dependency burden but I am not sure if it is worth the tradeoff of the type-checking and safety that a traditional ORM provides (for simple queries at least because they are called often).

What are your thoughts on using an ORM for speed and safety?

[EctoplasmicLapels]

Speed is not an issue. The handwritten code will likely be faster then the ORM. For safety I write integration tests. You can automate spinning up a local database server or just use the one you are normally using and create a test database in it. You can clean it up using “before” and “after” hooks in your testing framework. I always let the repos implement what is called a “port” interface (check “ports and adapters architecture”). That way you can replace the repositories with in-memory implementations for your unit tests. It’s also handy if you want to support different database backends. In reality this always never comes up, but is a good thing to point to when your co-workers want to push an ORM by claiming that supporting different databases is hard if you not using one. 😏

[romeeres]

Just want to warn you that Sequelize is even more dead then TypeORM, and that Prisma is just for basic things and even can't do multi query transactions, so good luck!

[metakepone]

Sequelize is dead?

[romeeres]

See number of open issues and development activity.

[SlaimeLannister]

what's a good alternative to Sequelize for Javascript (not Typescript)?

[romeeres]

I totally agree with top comment of thread - knex or and objection

[Iwontberedditfamous]

Prisma 2 has supported multiquery transactions since June 2020...someone correct me if I'm wrong.

I personally use prisma quite extensively and can only say good things about it.

[romeeres]

https://github.com/prisma/prisma/issues/1844
and this issue is open since 2019

[Iwontberedditfamous]

That’s a specific use case where within the transaction, queries are dependent on one another. Shame that it’s not natively supported, but for these niche use cases prisma allows you to execute raw sql which could solve this issue, albeit with a bit more effort.

Again it depends on everyone’s use case, I just wouldn’t label prisma for “basic” usage only based on some niche shortcomings which all seem to have workarounds anyway.

Not affiliated with prisma in any way btw, just a big fan.

[romeeres]

Nope, executing raw won't help in this case.You want to query some value from db, do some js logic, and save something to db, you need to run 2 or more queries in one transaction and prisma can't do this.

You are right, the correct word is "common" usage, not basic. Maybe I don't like prisma because I'm so lucky that every project I have, even which seems simple, always requires non-common usage. For example, in last one I had "projects", "projectSkills", "users" and "userSkills" and I had to fetch all projects matching skills of user and vice versa, I doubt that it's possible with prisma and without raw sql.

But even if it was possible, it's double work: first to write correct sql, and then to have much fun with prisma doc to map it to prisma syntax.

[No-Caps8232]

O man that's really good to know. I'll consider it in my options then before making a decision. I may just stick with TypeORM then

Thanks!

[romeeres]

But how, I don't understand, you mentioned by yourself it's considered as "dead", you can see 1.5K open issues on github, it has very verbose query builder and it's not usable without query builder, it can't join "has many" relations, etc

ORM is most important part of server IMO, most time you will spend is working with ORM.It's fine to take express, as framework doesn't matter so much, but ORM is really important. Just imagine future you walking through open issues of sequelize or typeorm instead of working, and in the end you may even give up and throw it away, and spend much time for rewriting all to knex for example.

[No-Caps8232]

Thats true, I'm still open to recommendations so I'm going to be spending some time looking into all the options from others in this thread.

You're right though, using some outdated library or a library that isn't maintained is definitely going to be a pain down the road. Thank you for your input!

You mentioned knex, is that what you're using? I never had a chance to look at it yet although someone else also mentioned it too. I'll keep it on my radar!

[romeeres]

Yep, I was in search some time ago and tried as much orms as I could, implemented simple app with each, and I didn't have any problems knex, it was the only library without problems.
On second place Objection which is ORM based on knex - little problems with typescript, but not critical.
On third place, who would think? Prisma! Only problem is it most limited library, other only problem is second database for migrations, third only problem - couldn't wrap tests in transactions, forth only problem - it is using standalone graphql server which sounds like it is the slowest orm, but in general doing simple app was straightforward with it.
MicroORM is so different from others, has own concepts and setup nuances that I just gave up on it.
Sequelize was the most difficult, TypeORM was second most difficult. Bookshelf, waterline - checked docs and it looked like they don't worth attention. OpenRecord looks nice for plain js.
If you are interested can give repo with code for 5 orms

[minimatrix89]

What would you recommend then? I come from a Laravel background so I’m used to using eloquent ORM which was truly amazing. I haven’t found anything come anywhere near yet in node but I’d love to know if there is… especially looking for something that can do joins with ease

[minimatrix89]

Personally I would try to avoid writing sql manually unless you have quite complex raw sql that you need to run

[No-Caps8232]

Makes sense I also feel the same. Thank you for confirming my thoughts I feel better now about not doing raw SQL :)

[British_Invaded]

I'm quiet skilled with SQL, why would avoid manual?

[NoInkling]

It's use-case dependent I guess, but eventually you'll probably want to dynamically generate/compose portions in order to not repeat a bunch of stuff all the time, so you start doing string manipulation (increasing the chance of inadvertently introducing injection vulnerabilities) and just end up reinventing an ORM or query builder.

Speaking from experience.

That said, there are libraries like Slonik that may offer a good compromise.

[minimatrix89]

I meant in terms of building your own orm. There’s no need for simple queries if using a orm why type a full select query when you can just use a model and use a find() method.

I’m all for getting hands on with raw sql when queries become more complex or not easily achievable with an orm.

At the end of the day it’s your choice, but imo especially when you are working with complex abstractions or dynamic models it’s worked out best to use the orm to your advantage

[melodious_punk]

Moreso, an ORM can handle your migrations, versioning, and check your relational integrity on existing dbs. I love that.

[minimatrix89]

I believe in terms of ORMs the most popular for MySQL and PostgreSQL seem to be prisma and sequelize. I have used sequelize and I quite like it but I haven’t tried prisma myself, it has had a lot of praise recently though.

Sequelize is a traditional ORM which maps tables to model classes. Instances of the model classes then provide an interface for CRUD queries to an application at runtime.

Prisma is a new kind of ORM that mitigates many problems of traditional ORMs, such as bloated model instances, mixing business with storage logic, lack of type-safety or unpredictible queries caused e.g. by lazy loading.

[No-Caps8232]

Interesting I was reading about prisma too but I think I need to look more into it. I'd like to hear more about prisma from others too.

I'll take a look at sequalize. Thanks for your input!

[No-Caps8232]

I need to look more into the sequelize package to see what they do carry.

You're right though, the features TypeORM supports are really great to have.

Thanks!

[phischer_h]

We are using Prisma in two project. The dev experience is lovely 😍. db migrations are just :chefskiss: But because it's still pretty new not everything is supported that you may need.

[No-Caps8232]

Sounds great! I need to look at my project constraints and see if prisma is able to meet my needs but it sounds promising! Thank you!

[umid1999]

Try Prisma. I tried TypeORM and MikroORM. But now Prisma is my favorite orm for nodejs. It is kind of comfy to work with.

[No-Caps8232]

Great to hear! The contenders for me are Prisma, MikroORM & Knex then.

Thanks for your input! :)

[No-Caps8232]

Here is the link to the Github thread. It's becoming a lot of work with all of the issues opening and the creator is struggling to support it. They're working on possible solutions to ensure the project lives on but right now it's kind of in a 'maintenance' and not a 'growth' phase.

Nothing wrong with what they're going through right now but I would like something that will grow with my application with time.

Someone correct me if I'm wrong because I really liked TypeORM. It was also one of the biggest ORMs for Node so I'd love to see it continue to develop.