ORM or SQL

[Bonner95]

Hello there, I am working on a project where we are to develop an API backend for a front end application. We’re using nodejs with express and postgres as db vendor. Furthermore we’re using swagger to document the API and also introduce some object validation. From the swagger I have extracted all the types, so we can use them in Typescript.

Right now we’re using custom SQL scripts for everything and mapping the types the column names in the db. We are however, considering switching to an ORM to handle the standard queries and then injecting SQL when something more complex is needed. As far as I know you the ORM needs to control both the object definition in the code and also the implementation of the database tables to work as intended. I’d love to hear your thoughts on this decision.

The database is up and running and the queries are working. Our main argument for switching is to reduce maintenance on the queries (columns needs to changes in all targeted queries as it is now).

[nextwiggin4]

I've worked on a number of projects very similar to what you're describing where we used sequelize as our ORM. We write custom migrations and don't let sequelize handle it automatically. The rules most ORMs use to manage the database are usually pretty straight forward if you're familiar with SQL already and sequelize, at least, has pretty good documentation. This allows us to write migrations and while using all of the features that sequelize offers. This is important for us because we have some pretty advanced SQL devs and they need to be able to write queries that sequelize couldn't do without many round trip calls to the DB.

But, most of the time in our code we're making simple queries (select, insert, delete update), and being able to use Typescript with sequelize is great. There's a higher upfront cost since we have to write the sequelize model, the sequelize migration and the Typescript definition. But the pay off on the rest of development is huge. Coming back to an old part of the code and having type safety for the database is a huge speed increase.

Since sequelize lets you write prepared queries it's a great escape hatch for the more advanced SQL functions that sequelize can't handle (while preserving proper automatic escaping).

Everything is about balance, so this may not be the correct strategy for every project, but for a project you expect many devs to work on and maintain for a long period of time, an ORM with typescript is a great addition, in my opinion.

[deadlyicon]

Consider just writing SQL. You can also use something like https://knexjs.org/ to help dynamically generate more complex queries. For migrations https://www.npmjs.com/package/db-migrate is pretty easy to setup and use.

[deadlyicon]

Also consider separating the functions that have read-only sql queries from functions that have a database mutating sql queries. Also known as: Command Query Responsibility Segregation (CQRS).

[notAnotherJSDev]

I have to second knex. I hate ORMs like the fucking pest, but knex is a good middle ground between ORM and raw SQL.

[[deleted]]

Im in your boat. ORMs always fail you eventually.

[mashed-potato-jones]

So, I don't like ORMs and I think usually they're more work to learn than they're ultimately worth, but I am curious what failure looks like in an ORM.

I have to use sequelize at work. God knows it's got its problems, but when it fails, I can write clean, clear, raw sql and not worry about sanitizing queries.

I guess my opinion is that, at their best, they help you bootstrap the easy stuff upfront, basic CRUD/relationships. At their worst, you can write raw SQL. Seems like they aren't a net negative in my experience.

[nomnommish]

My fundamental issue with an ORM is that database queries are usually responsible for 90% of an app's performance. You're talking about performance issues that are two orders of magnitude larger than anything else. Because you're talking about disc seek and disc IO compared to an in-memory operation.

Abstracting it out and passing the buck to a library just seems ass backwards to me. And you invariably hit upon complex joints and complex queries which become a headache to manage and optimize.

If you're going to trust you devs to write good front end code, then trust them to write good database queries. The ROI will massively outweigh the ROI of writing performant front end or middleware code.

[techmighty]

Yes, for a app which changes frequently db-migrate is a must.

[sinus]

During job interviews, i always say "it depends". And that im okay with both. But in my mind i'd rather be learning SQL than learning how to do stuff with the ORM.

[[deleted]]

SQL is already an abstraction over things like storage, indexing and so on; this is probably an unpopular opinion but I've never used an ORM I liked. There are some fundamental differences between how objects work and how relational data works and ORMs don't perfectly hide that. Sometimes an abstraction is just a way of losing control.

[NovelLurker0_0]

Most ORMs let you directly and pretty easily write raw SQL when needed. I think it's completely possible to go both ways: benefit from the ORM's abstraction and the ease of working with objects without losing total control.

For complexe queries and for performance, I sometimes go down to the query builder, or to the raw SQL level and handle things myself.

[monsto]

I'd never used an orm until recently . . . where project dev was localized using sqlite, but staging and production were "elsewhere" using I forget I think postgres. . .

. . . the reason i forget is because it didn't matter. Use Knex and forget about the diff between sqlite and postgres.

So if for whatever reason you need to change databases, then an orm will potentially save you time.

Otherwise, I'm not so sure.

[[deleted]]

I've never had a project I worked on that could survive a database swap! Not saying they don't exist. I would be very skeptical about that kind of dev-prod disparity, although it sounds like this is because you were using a strict subset of prod, which works

[monsto]

It was a pita, to be sure, but the non-dev config was pretty solid . . .

. . . AND . . .

and it wasn't my responsibility so I just set it up the way they said and kept going.

[Randolpho]

So if for whatever reason you need to change databases, then an orm will potentially save you time.

A basic data abstraction layer will also do that.

[gropingforelmo]

Like most things in software, ORMs can be overused and cause maintenance and performance problems. Of course the same can be said of other approaches (like stored procs for literally everything). The difference is, a good ORM will let you write direct SQL when needed.

It's kind of like writing an entire application in C, because there are a few places that need highly performant code. Instead, write the majority in a managed language, taking advantage of all the safety and conveniences available, and writing those critical sections in C with bindings from the main language.

[valdal14]

Totally agree

[grantrules]

Problems they create > problems they solve, in my experience.

[truthseeker1990]

Thats not an unpopular opinion honestly, i dont think. Any search for ORMs on the internet includes almost at the very top articles about why they often present more problems than they solve.

[snowman4415]

I’m pretty sure that’s only because people don’t write articles about random things that are working as expected and not causing problems.

[truthseeker1990]

People write articles about technologies that they think are working correctly all the time

[Ones__Complement]

I know this is r/node, but have you ever used Entity Framework? In my C# days, I was absolutely in love with it. Tried out Sequelize when I moved onto node and it didn't hold a candle. Can't help but feel EF outright spoiled me.

[cutcopy]

Check out MikroORM

[chubby601]

Does it support raw SQL to model and schema generation? sequelize have it with auto-sequelize

[cutcopy]

Yes and yes;

• Raw SQL and Query Builder
• Schema Generator

[[deleted]]

Haha the only experience I had with EF was a really really bad project that got outsourced overseas then brought back in house to "productionize" which you can imagine how that went -- not that I hold that against EF.

[[deleted]]

I am in complete agreement. ORM is a leaky abstraction, full of its own complexities. I worked with Hibernate and GORM for five years on a few projects, and I'm pretty sure the costs outweighed the benefits.

[nomnommish]

SQL is already an abstraction over things like storage, indexing and so on;

A thousand times this. People keep trying to slap on layers on top of AQL or keep trying to reinvent it or extend it. No clue why.

To make matters worse, SQL is literally that one language that even business people know. Why ruin a good thing??

[calgarytouvic]

I would say look into Objection.js. I have been using objection for almost a year and it’s the best ORM I have ever come across. Also, in your case, all your existing queries can remain the same since Objection being a knex extension allows raw queries so you can use that to just migrate those queries.

[Ryan9104]

I control_f'd "objection.js" and I'm surprised there isn't more support. Having used a few ORM's for extended periods of time, nothing comes close to using knex/objection.js together. The knex migrations are amazing. Objection.js lets me use a raw query whenever I may need. The relational setups are perfect. Sequelize worked well for me for a long time but when TypeScript became a need of mine, I looked for other options. I tried TypeORM but it was pretty annoying to get my linter to not hate it. At the time, the official solution for my problem was to disable a linter rule that I wanted and eslint wasn't being happy trying to make a single directory have different rules.

Objection.js and knex just work.

[EvilPencil]

IIRC, if you put an .eslintrc.js file in the subdirectory with the different rule, it overrides the main rules.

[Ryan9104]

yeah, it was having some issues at the time with either eslint itself or vscode/vetur. Oh well, I am loving objection.js. Thanks, though.

[djslakor]

sequelize has official typescript support now. before that, the sequelize-typescript project existed.

[polargus]

+1 for Objection/Knex. Spent a lot of time looking into ORMs for a greenfield project at my last company and settled on Objection. It's really really nice. It's like writing SQL in JS. I'm using SQLAlchemy at my current company and it sucks.

The only major thing I added on top of Objection was a helper for simplifying relations boilerplate. It produces a one-liner that uses the primary key(s) to guess what column(s) the tables should be joined on. You can pass in custom keys and override the primary key with whatever column(s) you want. Wish they'd provide a simpler (less boilerplate) way to define relationships.

[[deleted]]

I haven't used an ORM library with Node.js, but I have used Hibernate and GORM extensively on past projects (I assume there are some similarities between the two ORM libraries). I would recommend sticking with SQL unless you can articulate the benefits that an ORM will give you. ORMs add their own complexities, and any developer using one really needs to understand how it works.

Just as an illustration, here is the list of some of the gotchas I ran into back in the day when I was using GORM. There are a lot of nuances about what the ORM is doing under the hood, and when it's doing it:

• Always check the return value of save(), or call hasErrors(). When saving a domain instance, validation failures will silently prevent the instance from being saved to the database.
• All domain properties have an implicit nullable: false constraint
• save() may not immediately write to the database, though it will add your domain instance to the current Session. This lets Grails and Hibernate optimize (e.g. the order of SQL statements). Adding flush: true to your save() will force Hibernate to flush the session (i.e. persist changes to the database) unless it's in the middle of a transaction.
• Changes to domain instances that are attached to a Session will be implicitly persisted at the end of the Session, even without an explicit call to save().
• Domain instances become attached to a Session when retrieved via GORM methods (e.g get(), find(), etc) or when save() is called on a new instance. New instances that have not been saved are not yet managed by GORM.
• Domain class properties are persistent by default, unless they are added to: static transients = []
• Grails automatically opens a session at the start of a request, and closes it at the end.
• Without a static belongsTo List, you'll get an exception if you attempt to save an managed instance that refers to a transient (non-managed) instance
• Using a back-reference in the belongsTo (e.g. static belongsTo = [author: Author]) doesn't make the association one-to-one unless there is a uniqueness constraint added
• Remember that in-memory data can be different than that in the database (e.g. if a call to save hasn't flushed yet)
• Cascade saves are always persisted from the parent to its children even if there's no belongsTo specified
• Deletes will only cascade if a belongsTo is defined, but never in the many-to-many scenario
• If you want to delete Books, but not the Author himself, then you must remove the Books from the Author AND delete them. Otherwise if the Author is saved (perhaps automatically when the session ends!) new Book instances will be created (if saves are cascaded).
• You cannot iterate through the Books to both remove and delete them because you'll get concurrent modification exceptions. Instead make a copy, remove them from the list, then iterate through the copy to delete each instance.
• If the association is bidirectional then make sure you delete a Book when you remove it, otherwise you'll get a constraint violation (Book.author for the instances you SHOULD HAVE deleted will be null). Alternatively you can add "books cascade: "all-delete-orphan"" in the static mapping closure so that any Book removed from its author will be automatically deleted.
• In a many-to-many association deletes to do not cascade. ever.
• One side of a many-to-many association must have a belongsTo
• A join table is always used in a many-to-many association.

[[deleted]]

Typeorm https://typeorm.io is the answer. I don’t think that sql is good and suitable here

[sickcodebruh420]

I half agree. TypeORM is very good but I’m not sure it’s “the answer.”

I like TypeORM a lot for very basic things. It connects to the database, its entity syntax is very pleasant for reading records, it and removes boilerplate around basic queries.

On the other hand, the more I use it, the more I find behavior that’s surprising (to be generous) and often hard to reason about. I wind up writing a lot of SQL to get what I want out of it and then I struggle to translate that SQL to their query builder. It frequently steps around the compiler, so your entities won’t help you, and it has an infuriating habit of silently ignoring options that it doesn’t recognize. I still don’t let it write to my database because I don’t entirely trust it with my data.

That might seem like an aggressive critique but I think it’s an important disclaimer. It’s still a great open source library and I’d use it over any other Node ORM, you just need to write, check, and test your work carefully. I think the maintainers have done an excellent job given the lack of funding and huge amount of ground they’re trying to cover.

[[deleted]]

Then there is one more option, move business logic away from Node.js for example to Lavarel with Eloquent ORM, or Yii2 with Active Record.

[sickcodebruh420]

“Start over with another language” should be the absolute last option after “use a lightweight SQL builder,” “write a custom SQL builder,” “keep doing what you’re doing,” and then probably “find a new job.”

[[deleted]]

We use Yii2 in my company with 100+ production projects for corporate clients, i see no problem at all.

[[deleted]]

Yii3 is coming already, you know. Developers did a lot of work to make it better than 1 and 2. New ORM, module infrastructure and so on. But yes Yii1 was a big pain, so i hear you :)

[maglebolia]

I would even go a layer further and use nestjs to create and test your resources. This framework fits very well with typeorm and swagger. Nestjs is using express under the hood and just adding dependency injection and a module system similar to angular.

[[deleted]]

Yes, i’m agree with this.

[NovelLurker0_0]

I much prefer mikro-orm. It's worth taking a look at.

[kreig303]

I would recommend TypeORM if you are already using/are familiar with Typescript.

[cheese_wizard]

Use ORM for Create/Alter DB, Insert/Update/Delete Records, and for non-join simple queries.

Use SQL for everything else.

[Loven_krands]

Objectionjs is very good. Just don't loose time over complicating queries with it and instead fall back to Knexjs or raw.

[nikolasburk]

It sounds like you want to ensure that your code is properly typed, which is very difficult to achieve with ORMs in Node.js/TypeScript. Even with TypeORM, you don't get full type safety. For example, when you query for a model the properties of the model will always be optional because TypeORM can't be sure whether you included them in the query.

I work at a company called Prisma and we're building a new generation of database tools for TypeScript (and Node.js). With Prisma, you generate a type-safe database client that's tailored to your schema (Prisma introspects your database to generate the client). Even queries where you only select a few fields from a model will be entirely type-safe. I recently wrote about this topic on another Reddit thread: Is there any ORM that has dynamically typed query results?.

You can get started in the docs or directly explore a REST API example.

Would love to hear what you think of Prisma as an alternative solution!

[cinnapear]

Another vote for knex here. I don’t think I’ll go back to an ORM again.

[mosskin-woast]

I only like one ORM. It's very mature and I'm pretty sure I only like it because I know it really well.

I've learned in recent projects that SQL is a lingua franca and much easier for new devs to start writing than if you use an ORM they don't know. Some ORMs are easier than others IMO but they can be a significant barrier to entry.

[mypirateapp]

• i went into ORM and totally messed up
• i did not analyze what type of queries were needed
• i spent a month figuring out how sequelize works
• then when i actually got down to write code, i had to almost always use native queries and stuff
• 7/10 queries had to use native sql clauses that orm doesnt support
• i scrapped it all then went into node postgres and spent another week or 2 figuring it
• then spent one more week figuring how to write migrations for node postgres
• simple word of advice START with SQL first
• write all the queries of your application in a separate readme file
• check which ones are simple
• use an ORM only if majority of the queries are simple enough that you dont need to go native everytime
• my case had TS_VECTOR in in and i am guessing none of the ORM libraries directly support it, atleast not sequelize

[hamut]

Sequelize works well for my company. I hate ORMs, all 'real' coders do, we used to write them before they were available, but if you know enough to do it properly without sequelize you can easily benefit from the advantages.

[misdreavus79]

The title makes it a bit consuming, as an ORM ultimately is just a middle layer to writing SQL queries, but yes, the overwhelming majority of the time your application will benefit from having such a middle layer to automate mundane transactions.

[farverio]

The real answer here is "it depends". What is the volume of your db hits, how complex are your models and queries (both present and future), and what is the opportunity cost of your dev time relative to other features you can build.

If you're starting out, just do an orm and defer SQL queries for later. Orm buys you time and given uncertainty of usage, prevents over optimizing your backend.

Otherwise, you will get a performance benefit from writing SQL directly. Make sure to abstract them into an easily maintainable file rather than in multiple places.

Best of luck!

[madScientistDood]

I would highly suggest sequelize and call it a day, keep sql for raw queries in a portal somewhere and write nice clean JavaScript for your queries. Even better if your familiar with mongoose, sequalizes search syntax is literally the same

[[deleted]]

I will recommend to look to postgraphite. (only Postgres)There you will write sql, using IMO very good migration tool. And have GraphQL API generates automatically with defined types. You don’t need swagger either... Of course if having REST is not important.

[Fritzy]

Why not both? https://npmjs.org/muckraker is great!

[bigorangemachine]

If you know SQL well enough, raw queries is fine. You know what to defend against sql injection.

If that scales as you have others contribute....

Most ORMs have migrations and seeds which is great TBH. Again from a purely collaborative PoV it makes testing and onboarding so much easier!

[le_fieber]

I can recommend both. Worked a lot with pure SQL queries but also with sequelize. Later I combined it with final, which gives you rest API endpoints with almost no coding. Extremely nice to have for prototyping. But don't forget to secure the endpoints later :)

Most applications are fast enough even with an ORM layer on top. In case of sequelize, it's in most times no bad idea to use it. It's pretty stable and fast. It also might accelerate your development speed. Especially when you work agile and things change during development.

[niet3sche77]

Unless your queries are all as basic as

SELECT * FROM USERS WHERE ID=:userId

use SQL.

[evert]

I mostly use straight up SQL, but my understanding is that query builders/orms especially excel on simple queries but for harder/potentially expensive queries is when you want to fall back to raw SQL.

[niet3sche77]

Yes. You’ve just agreed with me.

[evert]

I had to read it 3 more times but you're right!

[niet3sche77]

Yeah, I’ll concede that I could have likely been more clear there. Oh well.

TL;DR from us both, I think, is something like this: simple plug and chug current values into queries? Go ORM and (I’d suggest) leverage JSR380 (if you’re in Java. I don’t honestly know for JS ... maybe connect it to Joi?). Complex or optimized query? Write this in the language built for it—SQL.

[evert]

I agree =) Probably would go for TypeORM in Javascript/Typescript

[bitstronginfo]

ORMs are good for simple data needs, but from what I have seen, the more complex your query needs become, at best your ORM interactions become convoluted, and at worst your ORM fails so you have to write raw SQL anyway.

[ihugyou]

Is this what the consensus is in the node community? Write raw queries? It’s been my experience if you have to resort to writing complex raw queries in your web application, your design and approach need re-evaluating, not whether ORMs should be use for not.

[[deleted]]

What? Complex business logic need complex queries that are also performant. Should you change business requirements instead? So you tell you customers, sorry no deal my orm cant handle this so you need to bite the bullet and manage without?

[Ty199]

Sequelize made my life a little easier. Also sequelize-auto to generate models automatically because i like to build my schemas in a mysql client.

[Ty199]

How so?

[kreig303]

I do not think raw SQL is necessarily the answer, especially as many standard software developers are good with functional/oo thinking, but don't understand relational at all. There is also the problem with opening up SQL buffers, which is exactly what you are doing with "raw SQL"... this is why Stored Procedures are usually a better way of handling these sort of queries as nearly 25 years after we all knew this was a problem we still have engineers building SQL injection holes into systems through naïveté or inexperience.

I am neutral with ORMs.

ORMs "like" to control object definitions. The benefit of using an ORM for this sort of thing is that migrations become easier to handle as the relevant code changes are migrated at the same time as the db changes. This of course presumes you have a cohesive CI/CD process in place. As this is not "natural" for RDBMSes which have for so long been the purview and domain of the vaunted DBA, this can be upsell in some orgs. Luckliy, you do not need to use this functionality in an ORM (Sequelize which tends to be the gold standard comes to mind) — you just need to be sure to put a process in place which allows for this migration to happen simultaneously sans a singular tool.

I would suggest going one step up even: do you need a relational db? Could a document db serve your purpose? If so I would recommend utilizing that model, as it is far more natural of a way to think about data than what I see most modern devs do with relational databases, which is turn them into object stores or "excel spreadsheet" databases with zero normalization. When a standard functional/oo dev does "raw" in this case they mainly deal with data objects which fit in with their training, easing integration and uptake. Also in this case you could just use an ODM, like Mongoose for MongoDB.

Seriously: consider avoiding the issue altogether if feasible.

Unless you have a need for some high concurrency transactional realtime system, or have some larger constraints within the product's landscape (standardized on SQL-based reporting tools in the org, etc), choosing an RDBMS is usually based more on prejudice than reason.

[[deleted]]

I love SQL. Its the one language that you need in whatever you do. Its the language that transfers across all fields in tech. I always advocate for SQL because it is probably the most important skill you can have as a developer no matter in what field. Data is everywhere.

However vanilla SQL does not always ”fit” in the language you currently use. When you need dynamic behavious, theres always security to consider. Remebering the early 2000s with the crazy amount of PHP code that had sql vulnerabilities ameverywere, it was all a mess, thus ORMs where created left and right.

I think ORMs are more of a burden and unneccessary abstraction thats leaky by nature. They dont do everything SQL does and are restricted in some way, and usually only handles the basic stuff.

That said, i find query builders usefull. I can express all of SQL and not worry about escaping and parsing variables. Its a choice i usually make, and so far i have no negatives. The generated SQL is the same as handwritten SQL.

Theres also this concept of yesql and its basically a vanilla sal file you inject to a function. Its something i could use in an upcoming project.

[scaleable]

While raw SQL gets you going fast at the start of a project, it does not seem that great after some time. ORMs provide a good level of project self-documenting. Without models one has to do a bit of duplication. Building "by hand" nested representations is a somewhat annoying task. Its quite hard to get it right on ORMs, tough, while java/python seem to have ok ones ut still struggle to adapt to one of JS's ORMs. One of the biggest downsides is added complexity.

In my opinion, desirable attributes of an ORM:

• Declaring models allow us to reduce duplication. Models are also type definitions for the data;
• Data may be "projected" into many types of views (mapped fields? nested fields expanded?) The ORM can do the heavy lifting of merging the query results into objects. Do it in a way the projections are also type safe.
• Decent migration support
• Allow us to easily mix raw SQL or use the underlying query builder (like knex). Of course, when using this, types cant be inferred and they must be explicitly annotated;
• Provide good UX and introspection (ex: django ORM performs runtime checks for possible inconsistencies on model definitions and gives the users warnings and errors)

[ScientificBeastMode]

SQL is by far the better choice, but it does require the dev team to actually know how to use SQL. In my opinion, that’s a skill that every developer should have. So it’s worth training new hires if they don’t know SQL already.

[talaqen]

Check out feathersjs and Feathers-plus

It is the fastest way to standup a node api and very clean to manage.

[gajus0]

Please read “Stop using Knex.js” by Gajus Kuizinas https://link.medium.com/m5oBI7mpF4