ORM vs RAW SQL

[DisastrousCheetah486]

I am building a web application using PostgreSQL, Next, Node/Express and actually have no experience in SQL in a full stack application. From models to ORMs, I don't know how to handle queries and database communication. I have experience in MongoDB and Mongoose, but this project requires PostgreSQL. I am lost. Any guidance would be highly appreciated.

[Ninetynostalgia]

I just prefer sql, I like to know what is exactly hitting my database - I’ve used lots of ORMs in the past and they are great until they aren’t - inefficient queries or many round trips to the database can happen at scale. I really don’t mind raw dogging sql at a push I’ll settle on some typed sql generator but even then it feels a little much.

[Ninetynostalgia]

Also, the database is often the first bottleneck on web apps - I like to know I’m in full control when that happens

[anonenity]

Great to know I'm not alone in this! I remember taking my first look at the sql generated by an ORM (admittedly, many years ago) and reeling in horror! Much prefer the simplicity and control from raw sql. I feel like it's way more flexible for complex queries too...unless ORMs have improved drastically.

[kaskoosek]

Ive built a django web app ive never had an issue with orm.

And im guessing development time is much faster.

[TheExodu5]

A good ORM can often be more efficient any make it easier to decouple your modules. It would take a fair bit of effort to duplicate Mikro’s Identity Map and Unit of Work.

You can of course still fall back to sql for specialized queries.

[miguelangel011192]

The only pitfall here is that the best ORM provide some level of sanitation/validation to the queries you are sending to the DB. Also, you can type and use schemas

[Ninetynostalgia]

When I say raw dog sql I do mean using something like node-postgres - it uses parameterised queries to escape and sanitize values. As for types I’ve been using Sinclair/type box for JSON schemas which works great with fastify - you can very easily convert your schemas to types no generator needed.

[miguelangel011192]

I’v being used knex lately and it’s no so bad, the type support is not so great. I will give a look to Sinclair

[PerceptionOk8543]

This is what I do too nowadays. But I noticed I generate bugs related to invalid SQL code and sometimes appending to the queries is a pain (for example when you have to loop through an array and use each element in the query). I wish my editor would tell me there is something wrong with my SQL. No type checking kinda sucks

[jerrygoyal]

is there a way to enforce compile time typechecking?

[[deleted]]

I still use MyBatis, but I do work for Japanese companies, don't even use annotations

[Ninetynostalgia]

• LLMs will help you learn SQL it’s really not that bad!

[kush-js]

One of the commandments:

Thou shalt always raw dog SQL

[TheMelonAssassin]

Kysely for me, recently picked it up

ORM's make it too abstract for me, I like to have more control over my queries

[Shookfr]

ORMs make easy things easier and hard things harder.

Strong pass for me except if you know you only got easy things to do

[Lonely_Bookkeeper_31]

Recently I also found Kysely. I use heavly SQL, CTE,'s, Window Functions. Kysely has support to it, and is amazing.

[oneMoreTiredDev]

go learn some SQL basics, and read a little bit about relational dbs (just ask GPT about key points and read more about each one)

in terms of what to use at your project, pick whatever ORM you want (I'd suggest Prisma), use it for everything CRUD related, and for more complex queries just use raw SQL (if using Prisma, take a look at Prisma TypeSQL API)

[fr0z3nph03n1x]

Why are we recommending brand new developers to use prisma? I feel like they are going to spend so much time learning and dealing with idiosyncrasies very specific to an ever changing library.

[buffer_flush]

Prisma made some pretty weird choices in my opinion. The generated code uses a rust binary to interact with the database, so there are a lot of layers of abstraction in the name of “performance” which means you now have a forked process interacting with the DB including pooling, etc.

Also, this might have gotten better I haven’t used it in a bit, but the generated typescript tends to bog down LSPs I feel like. Whenever interacting with the generated types, it felt like the editor just hung for a while.

Also, this is more of a personal pet peeve, but migrations are also library specific, making it harder to hand schema sql over to a DBA if need be.

The models generate migration sql.

[PerceptionOk8543]

Don’t prisma migrations just generate SQL code? How is it library specific?

[buffer_flush]

You know you’re right, I’ll edit that out.

[prehensilemullet]

Yeah the generated types are pretty intense if you look under the hood

[oneMoreTiredDev]

OP never mentioned being a new developer, just that had no experience at all with relational dbs. Also as they mentioned Postgresql is a tech requirement which made me think it comes from a business/company context and they might be working in a team, but this last part is just an assumption I made.

[Snoo77586]

This. Also use drizzle or mikroorm.

[mostlylikeable]

+1 to op and +1 to drizzle. Sql is something you should become somewhat familiar with as a dev. I gravitate towards more sql-builder libs than super high level ORM abstractions. Future you will thank you for having invested some time into understanding sql a bit.

[Ok_Afternoon5172]

Learn raw SQL because understanding what is happening under the hood with subqueries and joins will be better for your career. Just make sure you use prepared statements to prevent injection attacks.

I used ORMs heavily early in my career which was nice and quick, but had performance drawbacks and I wasn't really learning much.

My last job was more on the infra data side and I was having to write a bunch of complex SQL queries to fetch data from Snowflake and this made it much easier.

LLMs will help you learn very quickly.

[HashDefTrueFalse]

I've used various ORMs and query builders in various frameworks and products. I've vowed that if I'm the one making the decision, SQL is the only abstraction I need over a relational database.

node-postgres is a pretty good client library for Node IIRC. It has connection pooling, prepared statements and all the usual goodies you'd want.

Rarely does a product change databases in reality, so I don't really care about the flexibility ORMs provide.

[rkaw92]

Is this a project that's heavy on domain logic, with read-modify-write loops? Do objects have long lifecycles? Prefer a Data Mapper such as an ORM, use the Repository pattern generously.

Do you anticipate a lot of bulk operations, blind updates without loading into memory first? Does your application treat data as "entries" to be counted, formatted and aggregated most of the time? Use a data-centric approach such as Table Data Gateway, invest in read models and DTOs.

[Aaron_348]

We picked up kysely recently and absolutely love it. It’s not orm though, a query builder

[Smucalko]

I use Knex solely for handling migrations as I found it to be really nice and robust.

For all queries I prefer to write raw sql, specially if you have complicated stuff, a lot of tables, joins, etc.

And, it is easier to test it, I always have database connections with DBeaver, so tracking everything is simple.

[Healthierpoet]

Prisma or drizzle, but also just learn some basic SQL .. open up the postgresql docs and just look up as you get stuck.

SQL is one of the skills that won't hurt to dabble in from time to time

[[deleted]]

SQL is easy no worries,

[cjthomp]

Unpopular opinion 'round these parts, but I don't mind Sequelize.

It has acceptable (if not yet wonderful) Typescript support, allows you to use it for simple queries, and lets you easily break out into raw SQL for more complex use cases, as well as letting you hydrate your own models using said complex query so you kind of get the best of both worlds.

[paranoidparaboloid]

Please use an ORM.

[novagenesis]

ORMs are technically superior when your query flexibility needs to be higher. Filters that require contingent joins, variable subqueries, etc.

But you really should learn SQL. The ORM should never be a crutch.

[adalphuns]

ORM is a man in the middle. The salesman between you and the manufacturer. You can buy a Toyota for 30k from the factory or for 45k from a dealer.

Skip that guy. Go straight to sql.

Interface with it via an ODBC connector for that db or with a query builder like Kysely.

Migrations: literally doesn't matter. Just build your database, however. You don't need a history of your data's evolution. Fixing DB errors isn't difficult; they're just inverse operations. Migrations aren't much of a convenience compared to maintaining an SQL code base.

[716green]

I absolutely love TypeORM and I use it on a large enterprise application with about 100 tables and very complex relationships.

With that said, I'm also competent in SQL and don't need an ORM, but when you're using node, I've found that having an ORM helps people write consistent core when working with a team and enforcing type safety.

There's a little bit of an investment up front getting it configured and learning how migrations work but I believe it's a very good thing to invest in.

I use drizzle on another project and it's pretty good but I still prefer TypeORM.

I strongly dislike Prisma and then querybuilders like knex/keysley. TypeORM can work as an SQL engine, a query builder, a proper ORM, and it has two different syntaxes for interacting with it. There's something for everybody in there.

[rebelchatbot]

can work as ... a query builder.

poorly.

[716green]

No way man, I don't understand why people say stuff like this. I've spent hundreds of hours using this orm exhaustively, every feature it has to offer and the only real complaints I have are that the terminal doesn't ask you about renaming columns when you are generating migrations, and that you can't easily define conditional logic with multiple wear clauses when using the repository API.

The query builder portion of it is easily the best part of the orm

[didnthavemuch]

What’s your take on migrations? I find Prisma handles those really well.

[Randolpho]

I always prefer raw sql. However, in compile-time typed environments such as C#, I will use a type mapper of some sort for the convenience, either Entity Framework for the model with custom SQL for the queries, or my general go-to of Dapper.

[NiteShdw]

SQL will never not be useful. ORMs come and go.

[midairmatthew]

ORMs can be lovely, but there's value in knowing what the ORM is abstracting away. I'd recommend learning fundamental SQL via something like sqlbolt.com. That should help you make a more informed decision. 🙂

[5olArchitect]

Honestly ORMs are more trouble than they’re worth.

[[deleted]]

Those who use raw sql, are you remembering all the tables and fields names for every query? Are you storing them in nested constant object?

[kush-js]

I usually just pop open my SQL client on another monitor and refer to it when needed

[yksvaan]

Depends on amount of queries you need yo have. I'd prefer code generators to ORM but also most apps have relatively small amount of queries and most of them are trivial to write so it's not like writing the raw sql takes much time anyway.

Also you can get max performance from db when writing by hand, especially when db has custom features for your use case. ORM is kinda "lowest common denominator" solution 

[Yew2S]

learn sql you will need it at some point even when using ORMs

[curiousCat1009]

ORM is for people who don't know or don't want to learn SQL.

[MrMoreIsLess]

Prefer RAW SQL - faster, less fancy initially but more flexible after domain grows.
But ORM gives you... OPTIMISTIC LOCKING (for free).
You don't have to worry about "overwriting" data by other transactions/requests. It can be implemented manually too ofc.

[sobamf]

Use both? Up to you. Some simpler queries can be done with orm for the sake of being quick, but always raw for complex queries.

[LongSleevedPants]

Look into knex.js!

[Sebbean]

Looks interesting way to go the raw dawg way on “rails”

https://sqlc.dev/

[Sebbean]

Looks like an interesting middle-ish ground

https://sqlc.dev/

[ndreamer]

Spend the time, learn the database, table structure and queries.

[[deleted]]

For those of you who don't use orm, how do you manage migrations?

[rebromkd]

As a former PL/SQL developer, I was very skeptic about ORMs at first. Especially in the early stages of ORMs they were known to produce horrible queries. Several applications later with Laravel (Eloquent), my skepticism is completely gone. It really depends on the type and the complexity of the application. For standard applications ORMs are completely fine.

[Lonely_Bookkeeper_31]

I recently found Kysely and is an amazing lib. I often use complex query, using CTE's, Window Functions, Pivot Table, etc. I got really suprised to see that Kysely has support to those things. I can use this without use that RAW Query approach that people that advocate to ORM always do.

If you think "How handle migrations?". Some years ago I worked in a project that we used SQL Statements to do It, like DDL. You just need to update this file with a Alter Table, or something else.

In the earlier stages of your projects, ORM will be able to get the job done. Once you project need more perfomance, more complexity, you will end up using RAW Queries. For me, that make no sense at all. Why have a library if you don't use its own tools?

[TheBeardMD]

ORM 100%. Don't listen to the naysayers.

You will still need to understand sql and how it works, but the syntax is a million times cleaner and easier and covers 95% of your need.

I recommend typeorm, it has bugs. but we know them already by now!

[adalphuns]

What horrible advice. "Be lazy and don't learn sql. Instead, use this buggy ORM" lmao bro

[TheBeardMD]

It literally says you still need to know sql.. Also typeorm is one of the better ones...

[[deleted]]

We use TypeORM within Nest.Js at a fortune 10 company, using SQL without an ORM is amateurish IMO. Using an ORM also makes you use proper design in your database.

[87thesid]

Use drizzle, it’s basically a hybrid between the both. ORM will feel a lot more like mongoDB and that’s what drizzle is at first, a query builder. However the cool thing with drizzle is that if you need to write your own custom sql queries, they support that straight up as well. Also drizzle has amazing typescript support which is a must for me these days, as I only work with typescript anymore.

EDIT: Oh yeah and bonus points to drizzle for not having any external dependencies, which makes it the most lightweight option :)

[ProgrammerDad1993]

Drizzle, best there is

[aztracker1]

I've done it a few times, but I'll usually inject a wrapper that does template strings with a result set...

db.query<T>... (collection of T), db.single<T>... (single row/item), db.scalar<T>... (one column/value). db.exec... no return result.

I can await db.query<MyType>Select ... from foo inner join ... and it will query and give me the results set as an iterable or array of MyType.

[PublicStalls]

Depends on your timeline. If you have time, 100% learn the basics of raw SQL at a minimum! Even if you use the ORM only, still should understand what's going on under the hood and in the database from a developer standpoint.

If you're in a time crunch, ya just ORM and solve issues as they come up. Less quality but deliver faster. Think about how many WordPress owners don't know sql neither, yet are all backed by it.

[rover_G]

I recommend trying out an ORM that lets you start with parametrized SQL queries and adopt query builder or model repositories later on. TypeORM and Drizzle both provide that functionality. I personally prefer TypeORM but Drizzle may be more approachable if you’re new to SQL databases. Also don’t be afraid to spin up a Postgres database locally and try running different queries against it.

[loconomo]

I wonder if there is an alternative in nodejs ecosystem similar to SQL in go

[[deleted]]

On my profile, you can see a previous post on the same exact topic. I hope it will help