I tried 8 different Postgres ORMs
ฝัง
- เผยแพร่เมื่อ 24 พ.ค. 2023
- Let's compare 8 ways to work with SQL databases in a JavaScript project like Node.js or Next.js. Analyze the pros and cons of libraries and ORMs that can run Postgres queries in a fullstack framework.
#sql #javascript #webdevelopment
Learn more in full Next 13 Course fireship.io/courses/nextjs/
- pg github.com/brianc/node-postgres
- postgres.js github.com/porsager/postgres
- knex github.com/knex/knex
- kysely github.com/kysely-org/kysely
- sequelize github.com/sequelize/sequelize
- typeorm github.com/typeorm/typeorm
- prisma github.com/prisma/prisma
- drizzle github.com/drizzle-team/drizz...
The real ORM is the friends we made along the way -- Our Real Mates
Nice sql comment at the end there. Very explanatory.
It's about the vulnerabilities we made along the way
eval(node run test)
And Tables We Dropped
I made friends with ChatGPT along the way
0:20 (S-Q-L), 0:31 (Squeal), 0:52 (Sequel) He pronounced SQL all three different ways so everyone is happy 😂
Or everyone angry
don't tell that to @ThePrimagean, clearly only one of those is truly correct
Squirrel gang
gold
I just wanted to point that out. My brain was like "dafuq am I hearing?!?" 🤣
I watched this video again to remember the differences between certain libraries and ORMs so I made a list:
1. 1:30 pg
2. 3:24 postgres.js
3. 4:11 knex
4. 5:20 kysely
5. 6:13 sequelize
6. 7:11 typeorm
7. 7:55 prisma
8. 8:51 drizzle-orm
May this be helpful to someone else as well.
+1, need those youtube chapters
The timing of this video is impeccable. Been spending the last few days looking into these ORMs
same, and I'm learning towards drizzle
same x2
Same x3
just write SQL
Been using sequelize and typeorm recently
MikroORM should be on this list. Has all the benefits of an ORM, but lets you easily fall back to a Knex-like query builder when needed. And crucially, it's much better maintained than Sequelize or TypeORM
yep, really a shame that mikro orm is not well known, its superb
It’s amazing. Like the best thing ever. The only sad thing is that there’s like one maintainer/creator. The guy is amazing ❤
I agree! And MikroORM works great with MongoDB too.
Definetely agree
Yes, B4nan is a superhero
Breaking world record for most useful information per second of video each time you post someting. Respect, Sir!
This video is so well timed, I was literally transitioning from Firestore to Postgres database with a project just now.
Bro same 😂
This guy has the most useful content, without wasting any time.
Awesome job Jeff. Thanks for creating such a concise and entertaining video.
I was less inclined to watch this earlier but, hey man, you rocks. Lots of clear and concise stuff in the video.
Rails, Django, Laravel, and Phoenix developers:
Yay!! We don't have to deal with this JavaScript madness.
then they face problems in scaling
Hours of research without real outcome and then one video and I know what to choose.
You're my favorite TH-camr for a reason. 😋
I can understand the Web dev community generally straying away from writing raw SQL, but as an analyst moving to Javascript from having written primarily SQL for the past 6yrs it can be a bit frustrating that the whole ecosystem is based on trying not to do what I'm most comfortable doing - it feels like my mad SQL skills are being somewhat nullified!
Great vid Jeff, I haven't seen postgres js before - I will defo be using it.
I'm not an analyst but I used to do a lot of raw sql and still find it easier to me than using ORMs especially for complex queries where (sub-queries, CTE, aggregation with OVER clause and maybe make use of sql variables, procedures, functions and temp tables) is needed.
Simply I'm more comfortable with SQL and it's easier for my to translate my ideas directly into what the Database can understand natively.
I feel you
You should give Kysely a try. We focus on 1:1* and have CTEs, window functions, etc. But also type-safety and autocompletion.
@@igalklebanov921 Looks nice and intuitive for one with average sql background,
I'll give it a try once I have a chance.
Thanks
Programmers hate being embarrassed. That's why they go to orms. It allows them to ignore the holes in their skill set while being able to goldplate over things for no reason to feel important. 100% ego.
@@matthewrutter8343 Maybe you have a point regarding the skill holes, but maybe it's the other way around as I myself find it very hard to memorize ORM methods, in the same time I can easily do what I want using raw SQL,
this caused me some embarrassment in a project I was a member of, so to the others this was a hole in my skills.
I'm a programmer with bad memory😎
Awesome video! And amazing that you're giving a free consultation, people should flock for that!
MikroORM is awesome and deserve more love. It does what all these ORM do and is battle tested, fast, and well maintained.
I have been looking into MikroORM and confused why it doesn't get much attention and review by the community.
Thank you. Perfect timing, that exactly what i'm looking for.
lol, the ending was perfect :D well done Mr Fireship
Great video. I'd enjoy an overview of ORMs in other languages too, such as SqlAlchemy, Entity Framework, etc.
Snuck in a video in response to the codedam issues with Prisma. Like your approach here - mentioning all pros and cons, going over each option. Great work!
The two reasons to use an ORM such as Prisma and TypeORM is so you get types for your code and so you don't have to update every single query when you update a column to a table.
In my experience with anything beyond a todo list both of those fall apart and have a lot of oversights that are a pain in the ass.
@@furycorp I agree. It's more of a "pick your poison" issue with orms vs querybuilders vs sql clients
although I would argue the last one is the less scaleable by far
prisma is okey for hobby simple projects but will fall out of hands when it gets complex with logic. Also there is no native joins.
prisma won't scale up
TypeORM is the only ORM that actually makes sense because Java uses a similar pattern and Java is Holy.
I'm currently using pg + postrgrator for migrations + sql-ts to generate types from DB. Works like charm. Type checking of sql is done by my IDE (intellij) anyway.
When using pg, run your migrations and then use schemats to dynamically generate typescript types from the db itself for great type safety.
Thank you man. This is really helpful.
A video about correct ways to hande migrations for multiple teams would be very handy!
use rails/laravel/django
everyone can do their own migrations & it rarely conflicts
Hey there fireship, sequelize doesn't support typescript but there's a new sequelize-typescript that does, it would've been nice if you did that.
Sequelize does actually support Typescript if you look through the docs, but it's annoying to set up and mostly scuffed in my experience. It's not an easy drop in.
@@Ke5o it was so scuffed that they need to re-write most of the features when releasing v7
Have a lot of projects in production. Some of our codebase accesses MySql, PG and SQLite, so Knex is our definitive tool. Also it handles transactions like a charm.
Absolutely love intro!!!!
If you need advanced postgresql like views, materialized views, PostGIS
I would recommend to use pg, choosing the right ORM depends on your project requirements so you must study first what features you will need and do research for the best of your needs
this is gonna be very useful for my typescript api, living and leaning...
sequelize has been my go to for small project for about 5 years.
Thank you so much for this was struggling to choose an ORM to use till now
I prefer writing raw sql queries. Orm tend to make simple things simpler and hard things harder
You should try Kysely. Its all about trying to be 1:1* to compiled SQL (WYSIWYG design principle) and aims at supporting advanced functionality ORMs just don't bother going into or can't.
I agree with you. Also debugging raw queries is much easier as you copy the sql string and execute it manually.
Absolutely. Plain SQL with prepared statements all the way. ORMs solve one problem while making a giant headache of everything else, other than maybe migrations. I don't know why the most widely adopted approach to a vulnerability was to abstract away the entire language.
@@MinibossMakaque I will never fucking understand the insanity of ORM. Literally makes everything worse, a useless abstraction.
Except when you have tons and tons of complex queries and that sometimes occupy and entire file
Drizzle for the next Project.
Man That Ending !!! Super
Love the ending :)
The issues that you've mentioned using raw SQL queries can be solved by using SafeQL. Zero abstractions, zero runtime code.
Yes, please. The best ORM is no ORM 🙂👍
Out of every ORM I've ever used, my favorite experience was using Ecto [Elixir programming language]. Note that this language, and also ORM, have a pretty steep learning curve, so it can seem obtuse at first. Other ORMs I've used include Django, SQL Alchemy, ActiveRecord, and a couple JavaScript ones.
Quarkus reactive is pretty sweet. But for sheer performance Go and Elixir libraries seem unbeatable.
First 20 seconds of this video, the story of my life! :D
Great job making these videos fun, so we can all have a bit of a laugh and not take ourselves too seriously, especially when it comes to the technology stack used.
I've also made a video on this topic on my channel recently.
Best ORM is either no ORM or one that auto-generates the access layer based on the structure and types you've already defined in the database. Anything in between is just excess heat and trauma.
I love the "Data Suppository". :D
bests one mentioned at 9:45 ~ saved you some time !
I also like what Supabase did with their new CLI, although not exactly an ORM. It generates typescript types for you based on the tables that you make inside of the dashboard, which you use with the SDK to make safe queries. One of the easiest ways to get a great DX with SQL in my opinion.
After experience with most of orms, Objection has the best developer experience with great performance
Have you tried Kysely? koskimas is the author behind both of them. :)
Typeorm doesn't give full type safety. Even when you pass selective columns in select option for find method, the return type will still be array of entity and not array of those selective columns.
in my opinion they are all good options and I would just look at what saves me the most time and works good with typescript.
So I usually go with prisma :)
Having said that if you are just a beginner you might wanna go with the orm's that you have to use raw SQL so you know how everything works.
prisma won't scale up
@@ooogabooga5111 hey what about drizzle
@@ooogabooga5111 How is that so? I use prisma on applications with 3M MAU. Loads pretty fast for everyone
I’m interested in buying your full Next 13 course, just wondering, does it teach Next 13 for those that already know Next 12 and below, or is it for people completely new to next. I am the latter and found a lot of tutorials tend to assume your coming from previous versions of next so am feeling a bit lost. Thanks
Long live typesafe query builders (aka Kysley and Drizzle ORM)
yassssssssss
The first 20 seconds are the most accurate stuff ever. I start with MSSQL and then switched to MongoDB and I was like "Yeah this is the best, I will never go back to sql" yeah but ... years later I'm now working only with MySql and I like it way more then mongo ... Currently using sequelize and the work is so easy to do.
For Postgres PgTyped is an awesome project. You write bare SQL and it typeschecks agains the databse and generates query methods that are comletely typesafe, even with complex joins or recursive queries.
This is how god intended us to use databases. Its so simple, so powerful, such superior performance. It's just amazing how long this approach took to surface, and how little known it is.
damn. I just learned about PgTyped, thanks to this comment. it is a gods gift.
Nice to have Eloquent ORM baked right in the Laravel Framework providing all of these and more features out of the box.
Using intellij sql intellisense in the code is way better than every orm can ever been.
🤮
well damn.
ORMs are cancer
That snoop dogg line was absolute gold.
I was gonna try typeORM a year ago, but found many articles warning not to use it because it wasn't maintained and had lot of issues. I have tried sequelize, it's great but it needs a lot of setup and it doesn't fully support typescript. I was gonna try Prisma recently, but then someone said that it had issues too with the Rust engine and that there being too much "overhead" and that it was bad for joins. Not to mention that your code would be third party dependant, as Jeff stated.
Would really like a video about the underlying structure and flow in ORMs and their tradeoffs, not just about syntax. Appreciate your work :)
Prisma only gives issues with the Rust backend if you plan on deploying it on a lambda function or using serverless in general. And you can still solve all of these issues, it just requires more work and it's not "out of the box".
Typeorm is actively maintained at the moment. There was a year or so where things were stable, but since v0.3 it's been fairly regularly updated.
the only issue with Prisma is when using server less. thats the only problem. if you're not using server less then you're perfectly fine.
Been using sequelize, and typeorm and typeorm is the most comfortable to me
At my dayjob the application teams write raw SQL queries because they can't replace the ancient ORM that came with the framework. Some developer wrote an abstraction library over the database connector that is actually quite nice. You construct a Query object. For example new GetUserById(id); And then do $q->result($db); which yields you the User object you were looking for. Or null.
Enjoying using Prisma along with Redwood JS at the moment, but the lack of support for PostGIS and spatial types is a bit of a drawback at the moment. Hopefully that support comes soon.
Joist is also interesting. It really focusses on great Typescript and lazy loading support while also automatically solving n+1 problems.
@@beyondfireship.. lmao
I really wish you mentioned joist, it’s TS backed and has facebooks data loader baked in at its core, allowing you to bundle similar queries together each event loop tick to reduce amount of queries you make to the db!
just what i needed
being a laravel developer feels like a ghoast or a stranger when you watch these videos
After 15 years of backend development (on the JVM though), I'll take a query builder over an ORM every time.
Also: Migrations "down" are usually not worth your time (how to roll back a dropped column or table anyway?)
Eloquent is not that bad tbf
Same. I used Spring Data JPA back in the day until I discovered jOOq.
I'm developing a webapp and used MongoDB Atlas for it and Mongoose makes my life easier. But then I realized I'm better off with a relational database because there's a lot of relational data on my backend. I chose Postgres and studied Sequalize and I like how Sequalize is very similar to Mongoose. However, I also came to the conclusion that Supabase will make my project easier to maintain. So I signed for Supabase only to notice that there's no ORM for it. Everything is interfaced through the Supabase API. To manipulate data before they get stored in the database, you would need to write database functions, edge functions and triggers. Creating schemas, constraints, indexes, and RLS policies need to be written in SQL (though some of these can be done through the UI). Supabase was supposed to simplify a lot of stuff, but I'm finding it time-consuming to set up a lot of things. Why can't it be as easy a Sequalize?
Supabase is a normal Postgress db. You can use any orm you want with it
@@foreach1 You can only use these ORMs if you have a server-side middle layer between the client and supabase. But obviously if you do that, then why not just use a self-hosted Postgres cluster? We use Supabase for the ability to remove the server-side middle layer. So it's just Supabase and client-side. And you can't really use any ORM for any of these sides. I actually like that approach and I can see it being easier to maintain in the long run. But damn the set up is hard. Migration is hard. Supabase docs are garbage. And Supabase tutorials on TH-cam doesn't really cover the very specific database needs/designs that I have. Though I'm pretty sure if I study this more for several more days I can finally get the hang of it.
oh my god ..mongodb..........
Alright, now just to figure out how to shove a frickin' matress into my backend so my project won't fail. Thanks for the advice!
that clifhanger man, still waiting for the final result :D :D lol
Prisma internally has large overhead because of all these abstraction layers, a regular SELECT query took over 100 ms while an identical query in TypeORM took 10 ms or so
Lol the first bell curve is literally me right now. I'm heading down the other side.
I can just tell you are a boyscast listener by some of your phrases and I love it.
Hi! I just wanted to add another perspective regarding ORMs. My experience is that they should be considered anti-pattern and I can outline the reasons why:
1. they won't excuse developers from having to understand good database design and proper querying and, most often, introduce N+1 problem through that naivete
2. they encourage direct table access with what amounts to adhoc SQL which ties the hands of database developers when the need arises to reorganize data for scale and performance
3. most relational databases offer things like functions, stored-procs and prepared statements which parameterize queries to solve things like:
a. combating sql injection
b. providing an access layer tier above base tables which:
i. provides API-like access and
ii. grants the freedom to re-org base tables as needed
iii. simplified access-level authorization
Those are the main points (there are others). Whether an ORM is used or not, there is no escape from getting to know your serialization (storage) layer well. You should be using functions or stored-procs as access points to your database regardless. Which then begs the question, why do you even need an ORM?
My conclusion is that as long as you use raw SQL with the chosen ORM's raw method, you will have control over the performance. However, when you start using their innerJoin built-in methods, you may encounter performance issues. Nonetheless, using raw SQL for complex queries defeats the purpose of using an ORM. This raises the question of which ORM to use that provides a good migration tool and a well-defined schema with types. I believe ORMs are suitable for simple projects, but when it comes to large projects with complex queries and performance optimization requirements, they may not be ideal. Therefore, you will most like be using the ORM for defining schemas and migrations and writing raw SQL for most queries.
You should try a query builder like Kysely instead of going raw SQL in complex queries. We're trying to be 1:1* with compiled SQL and go deeper than ORMs usually do - as long as it can be implemented in a type-safe way.
Great troll ending. Love it.
Is the one and only .... that works for you best !
“joist-ts” is an awesome option for graphql + Postgres - has dataloader built in so any graph queries are N+1 safe. Reminds me a lot of ActiveRecord for Rails
I personally love supabase‘s approach best - you have a GUI to create and update tables or columns
Which one would you guys recommend if I am planning to connect to multiple different user defined database during runtime?
Might check Mikro-orm as well.
Lool man really knows his audience... also was that knex huge security problem patched
Prisma for migrations (ddl), Kyseley for interactions (dml)
❤from Kysely.
The worst thing of being a developer is choosing the right stack. IDK why we treat it like a marriage
As long as you’re not so responsible (like marriage), it won’t be the worst thing, you can’t just choose and then leave the company when you think it’s a good fit 😎
We treat it like a marriage bcz it is legit a huge commitment lol
Because when you are making products for clients (internal or external), changing something like your tool for querying the database is not a simple thing, and there's a lot of anxiety around "making the right choice" right await.
Although most tools have pros and cons. If you pick a querybuilder and then you see yourself limited by it, an orm will have limitations in other departments. I agree we should just build but the issue is real lol
i knew that one was the best! i told yall
Jeff And Ozzyman are my most favourite Australians ❤️
I worked extensively in one of the ORM that’s Eloquent which Laravel uses which is a PHP framework
Been playing with Prima and GORM (Go). The amount of time I’ve spent setting them up and tweaking them instead of just working on my MVP with the few CRUD statements I actually need is…not great. I guess I like the idea cause for some reason I think I need to be able to change my database on a whim.
Entity framework + LINQ, saves you a ton
Laravel's built in ORM, Eloquent is pretty good too!
I'm convinced this is the kind of stuff Fireship does for fun when he's bored.
DTD lmfao, you are the funniest guy in CS
That @Entity based schema defining is also done in Android Room Db
❤ from Kysely.
Hello!!!
What do you think of the fairly new Typescript ORM DeepKit.
Also could you benchmark the performance of these Typescript ORM??? 🙏
I'm currently interested in Orchid Orm.
prisma is the GOATEST
ORMs are one of those things that aren't worth the trouble in the long run.
They can be nice in a quick prototype, but for an app that lives for at least a few years, you'll inevitably start bumping into performance issues or weird ORM behaviour that costs a lot of time to resolve.
The first time I encountered an ORM I thought it was magic, but after 15+ years in the business, I no longer find them worth the trouble. Writing raw SQL isn't that hard and as long as you use parameterized statements, much less likely to bite you in the end.
Save your future self the headache and start with a low level library.
"With great abstraction, comes great dependency."
- Uncle Ben
LOOOOVE Prisma!
Now you owe us a dedicated MikroORM video.
When the nextjs 13 course is ready, please add it to udemy too
Great! make a video about F# or EF Core
0:30 he said it properly. Squeal. ❤
Prisma also support MongoDB which is a huge W considering it also supports types (which are basically strictly typed jsonb embedded in the documents, making denormalization much nicer)
typeorm is a buggy piece of software. I was in a projects with typeorm and i don't want to repeat that experience
@@vladislavstepanov7591 typeorm is a library. But I generally agree yeah it's not a good ORM (I also have experience with it). You need to be aware of a ton of gotchas while using it.
0:38 using libraries & ORMs to access SQL to:
* get IDE language server completions
* migrations
* connect to db
* handle security
* madelling relationships in data