Hi Ben! I have been working for like 1.5 years now and been a grad student for 4 years. I have encountered very few tech creators who produce actual engineering stuff not just tutorials. I really liked your Go vs typescript video too. Only Fireship, Hussain Nasser and Kyle (web dev simplifed) are doing such quality content which sen engineers can watch, as per my knowledge (might be some other too i'm not aware of). You will soon be counted among them. Keep Rocking!
I wish we would just stop overcomplicating database access and lean more heavily on tools like SQLC to just make working with queries way easier and actually viable, while not having ANY issues with complex features. I just open up an interactive shell, build my queries with SQL code, copypaste into sqlc query files, generate the Go code and be done with it. Modify the query, regen, fix compile errors. If I need complex things, copypaste generated Go code, modify directly and go extra raw. It is SO maintainable and easy to have a great understanding of all SQL code that is used throughout a project! Reduce the tooling scope, the complexity is not worth the trouble I think. Those queries are insanely ugly and awkward to write, read, understand and maintain. That said, great video. You've explained the ENTIRE context really well, even if I don't agree with the general concept and scope of ORMs and after some projects, advocate heavily against them while pushing boilerplace-code-generation solutions. Your points were great.
Idk, I personally feel that you can likely get away with having inefficent queries for the vast majority of cases for the sake of getting out a MVP of whatever feature/product that you're trying to ship out. Assuming you have good design where the calls are not strongly coupled together, you should be able to swap out towards raw sql when it's needed. Understanding databases in general however from my experience makes you able to write not only better raw sql queries, but also better code for ORMs.
Definitely agree, its one of the many problems I have found of dev speed/ease VS scalability/performance, its a tricky line to walk and its up to every dev/time to decide where they fall
I think the main issue personally I faced is performance when using ORM on big projects. The other is writing sql queries not offered by ORM something you want to handle in a bit different way.
@@Prime-o8f yep, thats why i don't see any reason to not use an ORM, you are always provided with methods to run raw queries against the db, but with the advantage to map the result to a struct effortlessly
I really like Prisma's DX but they could really improve how they handle relation queries. I had performance issues in the past because, believe it or not, prisma doesn’t use joins. If you have to query something like User+Roles (M:N relation) prisma will run 3 queries in sequence, this can have a huge impact due to latency (3 trips to the database).
Something I have been playing with lately for TS is Kysely, basically a query builder but with fully typed results. Never used it in a real project but seems promising.
Yea I've heard of that as well, also never used it but it looks solid, same for drizzle. I'm personally a big fan of these ORMs that lightly wrap SQL to where it basically becomes type safe SQL
A lot of ORMs do this and in many cases it is faster than the select * from ... approach that many use by default. Data returned from select ... lists repeats all the header data per detail item (say, Customer, InvoiceHeader, InvoiceLines... you will get each invoice line as intended, but the invoice header will repeat for each invoice line and so on. ORMs instead load each row *once* no matter how many references there are to those rows. Now we know you should not *do* "select * from ...", but even if you are picky about columns, the rectangular grid of data returned will repeat itself a lot in 1:N joins. (In the ORM I use, it is actually one trip to the DB as the queries are all bundled in one network send to the DB where each return set builds on the prior. As all but the main table query are against the corresponding PKs, those go really fast.)
I've also found the opposite. Doing joins especially many-to-many increases the row count so exponentially with lot's of duplicate fields within a row. That serializing the result becomes slower than doing 3 sub queries.
I really think that besides ORM are quite useful, many applications with small or medium size do not worth its usage. Also some cases with complex and legacy datasets, which tables do not reflect direct an Entity to be mapped to your app. Agree with you, learn SQL and also Design Patterns like Data Access Objects (DAO) lets the software engenier to choose the best alternative for each situation based on criteria.
ORMs are more of a headache as soon as you need something more complex than "get by id". Btw, just talking about the orm. The other features of sql drivers such as type safety, connection pool and transaction management are absolutely necessary.
There's something about left joins that I find hard to grasp. I haven't found the right mental model for it or haven't practiced it enough. Maybe that's one thing that turns beginners away from working with SQL.
I like to think about it in terms of set-theory (the basics that we learn on school), although it's not the most precise sometimes. Left join is the full set A with the intersection of A and B, so (A - B) ∩ B. Right join is (B - A) ∩ A. Inner Join is A ∩ B, Outer Join is (A ∪ B).
What db viewer are you using? Really liked the look of it, loved the video Ben, been diving into SvelteKit + Go myself these couple weeks and I'm looking foward to this series
Been using GORM this past month. It’s fine. As mentioned in other comments about Prisma, ORMs don’t necessarily write efficient SQL (when it comes to joins and sub selects). I’ve always been told use ORMs for small projects because they don’t scale well. Also, sure you could introduce an error in your SQL…but fix it like any other bug? Usually you write whatever query you need once and that’s it. Still write straight SQL and that’s never an issue
Yea I did not realize how bad many of the ORM performance issues really are, I think its one of the many questions that falls into the development speed/ease VS scaleability, I'll always learn towards dev speed as I spend most of my time in startup/MVP land, but for an enterprise app raw SQL seems to be the way
I still prefer to use an ORM due to ease of use and speed like you said. Over in rust land, the ORM situation looks like more hassle than writing straight SQL.
Basing app on ORMs and using raw SQL where performance matters is the best approach. In project I was in, we had to implement a user notification feature when certain events happened, and these events happened fairly frequently, and to add to that mix the notifications had to be different depending on user's preferences. Using ORM for that was no-go performance-wise. So we've written optimized SQL query that fetched all the data it needed in single query. For cases where notifications would be sent to 50+ users at once, our performance was like 10x of sending a notification to one user using ORM. Sure, rewriting everything to raw SQL would increase performance, but would also dramatically drop maintainability and DX, while giving negligible performance increase in 99% of cases. As always, use the right tool for the job, and the neat thing is you can still use raw SQL queries while using ORM, and using ORM is appropriate for 99% of cases out there. For the rest of them just write raw SQL.
From my experience in a few large codebases, the teams I work with stay away from ORMs. For smaller projects i.e. MVPs, pet projects etc. I’m of the belief that ORM or Raw SQL is fine. I work in the data platform at a large org, and since I’m exposed to the whole realm of data, I am a little biased and think writing the SQL is the best route - regardless of the language you’re using to interface with your DB.
... i would say with any database, first understand schema's and how to use SQL to interface with it before using ORM's. my old say, happy with people doing things with a GUI, but understand how to do it using the CLI, aka use these tools to improvement productivity...
In my company we use TypeORM and so far we have not had any problems. The query builder works fine and there are functions to retrieve the fully generated SQL which will be run against the DB so you can troubleshoot if you built the correct query or not. And if necessary you can just write raw queries yourself. For my personal projects since I use Elixir Phoenix, it comes with Ecto and that is the go-to ORM for elixir projects.
honestly i don't see any reason to not use an ORM, you are always provided with methods to run raw queries against the db if you need it, but with the advantage to map the results to a structs and other types effortlessly, and in my experience i never seen production code without ORMs being used
maybe every developer should try to build an ORM after all, it is sad to still hear arguments like 'it is more efficient and productive to create custom queries than to do it in ORM', because ORM didn't allow to use custom queries ? 😂, and i would love to know from you guys how without orm you are more productive. For me, it is my opinion, ORM is better in performance and productivity, and my ORM (come with builtin cache to avoid multiple fetch from db for the same query for example), it is the fastest and most complete one, dashboard, shell, logs, router, shared bus ;)
orms are useless its just some lazy developers there is no better than writing raw queries + oop for application readability u could just do double quotes for not following sql naming convention and use the camel case
Hi Ben! I have been working for like 1.5 years now and been a grad student for 4 years. I have encountered very few tech creators who produce actual engineering stuff not just tutorials. I really liked your Go vs typescript video too. Only Fireship, Hussain Nasser and Kyle (web dev simplifed) are doing such quality content which sen engineers can watch, as per my knowledge (might be some other too i'm not aware of).
You will soon be counted among them. Keep Rocking!
Glad you are back.🎉
In the beginning you want results. In the end you want control.
I wish we would just stop overcomplicating database access and lean more heavily on tools like SQLC to just make working with queries way easier and actually viable, while not having ANY issues with complex features.
I just open up an interactive shell, build my queries with SQL code, copypaste into sqlc query files, generate the Go code and be done with it. Modify the query, regen, fix compile errors. If I need complex things, copypaste generated Go code, modify directly and go extra raw. It is SO maintainable and easy to have a great understanding of all SQL code that is used throughout a project!
Reduce the tooling scope, the complexity is not worth the trouble I think. Those queries are insanely ugly and awkward to write, read, understand and maintain.
That said, great video. You've explained the ENTIRE context really well, even if I don't agree with the general concept and scope of ORMs and after some projects, advocate heavily against them while pushing boilerplace-code-generation solutions. Your points were great.
Will look into sqlc, I've heard a lot about it from multiple people here, looks very interesting...
Idk, I personally feel that you can likely get away with having inefficent queries for the vast majority of cases for the sake of getting out a MVP of whatever feature/product that you're trying to ship out. Assuming you have good design where the calls are not strongly coupled together, you should be able to swap out towards raw sql when it's needed. Understanding databases in general however from my experience makes you able to write not only better raw sql queries, but also better code for ORMs.
Definitely agree, its one of the many problems I have found of dev speed/ease VS scalability/performance, its a tricky line to walk and its up to every dev/time to decide where they fall
Useful vid - thank you Ben!
I think the main issue personally I faced is performance when using ORM on big projects. The other is writing sql queries not offered by ORM something you want to handle in a bit different way.
ORMs often give you the ability to bypass it and and send raw SQL to the db?
@@Prime-o8f yep, thats why i don't see any reason to not use an ORM, you are always provided with methods to run raw queries against the db, but with the advantage to map the result to a struct effortlessly
I really like Prisma's DX but they could really improve how they handle relation queries.
I had performance issues in the past because, believe it or not, prisma doesn’t use joins. If you have to query something like User+Roles (M:N relation) prisma will run 3 queries in sequence, this can have a huge impact due to latency (3 trips to the database).
Huh, well that is a problem, I guess I can write joins better than prisma XD
Something I have been playing with lately for TS is Kysely, basically a query builder but with fully typed results. Never used it in a real project but seems promising.
Yea I've heard of that as well, also never used it but it looks solid, same for drizzle. I'm personally a big fan of these ORMs that lightly wrap SQL to where it basically becomes type safe SQL
A lot of ORMs do this and in many cases it is faster than the select * from ... approach that many use by default. Data returned from select ... lists repeats all the header data per detail item (say, Customer, InvoiceHeader, InvoiceLines... you will get each invoice line as intended, but the invoice header will repeat for each invoice line and so on. ORMs instead load each row *once* no matter how many references there are to those rows. Now we know you should not *do* "select * from ...", but even if you are picky about columns, the rectangular grid of data returned will repeat itself a lot in 1:N joins. (In the ORM I use, it is actually one trip to the DB as the queries are all bundled in one network send to the DB where each return set builds on the prior. As all but the main table query are against the corresponding PKs, those go really fast.)
I've also found the opposite. Doing joins especially many-to-many increases the row count so exponentially with lot's of duplicate fields within a row. That serializing the result becomes slower than doing 3 sub queries.
I really think that besides ORM are quite useful, many applications with small or medium size do not worth its usage. Also some cases with complex and legacy datasets, which tables do not reflect direct an Entity to be mapped to your app. Agree with you, learn SQL and also Design Patterns like Data Access Objects (DAO) lets the software engenier to choose the best alternative for each situation based on criteria.
ORMs are more of a headache as soon as you need something more complex than "get by id".
Btw, just talking about the orm. The other features of sql drivers such as type safety, connection pool and transaction management are absolutely necessary.
There's something about left joins that I find hard to grasp. I haven't found the right mental model for it or haven't practiced it enough. Maybe that's one thing that turns beginners away from working with SQL.
I like to think about it in terms of set-theory (the basics that we learn on school), although it's not the most precise sometimes. Left join is the full set A with the intersection of A and B, so (A - B) ∩ B. Right join is (B - A) ∩ A. Inner Join is A ∩ B, Outer Join is (A ∪ B).
What db viewer are you using? Really liked the look of it, loved the video Ben, been diving into SvelteKit + Go myself these couple weeks and I'm looking foward to this series
Tableplus, I highly recommend it!
Been using GORM this past month. It’s fine. As mentioned in other comments about Prisma, ORMs don’t necessarily write efficient SQL (when it comes to joins and sub selects). I’ve always been told use ORMs for small projects because they don’t scale well. Also, sure you could introduce an error in your SQL…but fix it like any other bug? Usually you write whatever query you need once and that’s it. Still write straight SQL and that’s never an issue
Yea I did not realize how bad many of the ORM performance issues really are, I think its one of the many questions that falls into the development speed/ease VS scaleability, I'll always learn towards dev speed as I spend most of my time in startup/MVP land, but for an enterprise app raw SQL seems to be the way
I still prefer to use an ORM due to ease of use and speed like you said. Over in rust land, the ORM situation looks like more hassle than writing straight SQL.
Gorm is the worst
Basing app on ORMs and using raw SQL where performance matters is the best approach.
In project I was in, we had to implement a user notification feature when certain events happened, and these events happened fairly frequently, and to add to that mix the notifications had to be different depending on user's preferences. Using ORM for that was no-go performance-wise.
So we've written optimized SQL query that fetched all the data it needed in single query. For cases where notifications would be sent to 50+ users at once, our performance was like 10x of sending a notification to one user using ORM.
Sure, rewriting everything to raw SQL would increase performance, but would also dramatically drop maintainability and DX, while giving negligible performance increase in 99% of cases.
As always, use the right tool for the job, and the neat thing is you can still use raw SQL queries while using ORM, and using ORM is appropriate for 99% of cases out there. For the rest of them just write raw SQL.
Amazing, thank u.
Can you also do a video comparing ORMs, SQLC, and SQLX?
Yup, its on the list, will likely be ent vs sqlc vs pgx (sqlx for postgres)
No reason to use ORMs. Just write raw queries with golangs SQL driver and you get great performance and no need to worry about sql injections.
From my experience in a few large codebases, the teams I work with stay away from ORMs. For smaller projects i.e. MVPs, pet projects etc. I’m of the belief that ORM or Raw SQL is fine.
I work in the data platform at a large org, and since I’m exposed to the whole realm of data, I am a little biased and think writing the SQL is the best route - regardless of the language you’re using to interface with your DB.
eloquent is pretty good, and if you need more you can easily custom query with it
At 0:22 is this a meme about Drizzle being good or are they literally saying Prisma is better than Drizzle?
No its a meme, drizzle is very good lol
I *think* that ent is not a classic "ORM", it appears to be a code generator. GORM is an ORM.
True, for the sake of this video I sort of lump them together as basically just ways to interact with the DB that are not SQL
... i would say with any database, first understand schema's and how to use SQL to interface with it before using ORM's.
my old say, happy with people doing things with a GUI, but understand how to do it using the CLI, aka use these tools to improvement productivity...
Yep, I have the same view for stuff like git, learn the "root" (cli) then use the thing to make it easier
I believe that ORMs are not considered idiomatic Go. It took me a while to adjust, but I tend to agree. sqlc appears to be a good balance.
So then is the preferred method just always to use database/sql?
@@bmdavis419 I *think* something like pgx/sqlc.
ORM / ODMs for your write model.
Raw for your read model.
“More on that” plz
An ORM with a solid query builder would do the trick for me.
in the TS world drizzle is probably one of the best for that orm.drizzle.team/
I love drizzle, I’ve been messing around with it for a while now.
In my company we use TypeORM and so far we have not had any problems. The query builder works fine and there are functions to retrieve the fully generated SQL which will be run against the DB so you can troubleshoot if you built the correct query or not. And if necessary you can just write raw queries yourself.
For my personal projects since I use Elixir Phoenix, it comes with Ecto and that is the go-to ORM for elixir projects.
what is your vscode theme ?
1 month why upload Regular please btw cool background lightings.
honestly i don't see any reason to not use an ORM, you are always provided with methods to run raw queries against the db if you need it, but with the advantage to map the results to a structs and other types effortlessly, and in my experience i never seen production code without ORMs being used
maybe every developer should try to build an ORM after all, it is sad to still hear arguments like 'it is more efficient and productive to create custom queries than to do it in ORM', because ORM didn't allow to use custom queries ? 😂, and i would love to know from you guys how without orm you are more productive. For me, it is my opinion, ORM is better in performance and productivity, and my ORM (come with builtin cache to avoid multiple fetch from db for the same query for example), it is the fastest and most complete one, dashboard, shell, logs, router, shared bus ;)
To avoid screwing up raw SQL, you should unit tests.
you had 69 likes and I made it 70.
If you're asking, yes I am a menace to society
Orm is memory intensive
LSP makes ORMs worth it (most of the time)
Prisma it's 50% slower.
orm is always problematic regardless the size and complexity of the database. raw sql is more preferable.
orms are useless its just some lazy developers there is no better than writing raw queries + oop for application readability u could just do double quotes for not following sql naming convention and use the camel case
I hate ENT...
I don't know what an ORM is
No