"use prisma use prisma!! it's so good ! I will never go back to anything !!" next week: "prisma's trash and if you're using it you are dumb !!!" Thank you tech youtubers, keep it up lets go.
literally i was thinking the same, the blog author obviously made a wrong design choice but for example if you want to develop a small application in a short time with not much scaling planned the dev experience with prisma is great imo
@@chevi_to I would disagree for Senior dev, I don' t always write my sql, why would I do that when I only need to find by an id ? that what my active record find(id) is for :D. But yeah, Senior dev just will have a higher tendency to write sql to do something that is not just basic.
@@chevi_towell, I ain't middle yet, so I dunno😂😂. But actually as a junior fullstack dev I find sql really ok, it's way easier than other things that I have to do at my job. Probably I will have a moment where I will go "f that shit", buuut i don't really know what should happen for me to change like that.
@chevi_to In my experience the common trajectory for JR. devs is learning ORMs first and basically do this for as long as humanly possible. In fact they learn SQL through referencing their mental map of ORMs and connecting that to SQL.
@@chevi_to 1. Jr. dev: writting raw SQL all the time 2. Mid. dev: avoid SQL in codebase at all cost 3. Senior dev.: write raw SQL when optimizations are needed or very complex queries are involved
This sounds like a company that reaches for the “next new shiny thing™️” The amount of individual technologies mentioned and used in this article was incredible…
Just to add bit of more in-depth info. About transactions in PostgreSQL: in auto-commit mode each INSERT is treated as if it was in a transaction of its own. A PostgreSQL client can start your own transaction manually & commit or revert it manually, but if you don't, you'll have the default behavior. The bad performance of having only one modification (not just INSERTs, also UPDATEs, DELETEs, even schema modifications) in each transaction is well known. The usual way to speed things up is to bulk as many modifications as make sense into a single transaction. The gains are huge. For example, if you need to insert 20M records into a table, prepare the INSERT statement, start a transaction, execute the prepared statement a large number of times (e.g. 1,000 just to pick one), commit the transaction, repeat until everything's inserted. (Don't bulk all 20M into a single transaction if you have other clients that need to modify the same data as modifying stuff will lock that stuff for the remainder of the current transaction's life time, give them a chance to do things by committing from time to time.) About returning results: PostgreSQL doesn't return stuff by default. Doing a basic INSERT the database handle you're working with will only return whether or not the INSERT was successful. If you need some data back from the same insert (usually the primary key), modify your statement to read "INSERT INTO … RETURNING id". That way your program will still run the "execute statement" function on your database/statement handle which still only returns success/error, but your program can immediately run the "fetch results" function on the same handle & get the ID back. You can return other values the same way if you need them. But again, not by default. It's very much "don't pay for what you don't use".
About the "rows read vs rows returned": imagine having a huge table but forgetting to create an index on some non-primary key column you often read (e.g. a table of users with an integer ID as the primary key, but you often need to look up users by their email address). In that case the database doesn't have any idea where on disk the requested row is exactly. All it can do is start from the start of the table & read every row until it finds the requested one. Apart from being slow (and therefore bad for you, the customer) it's also bad for the service provider as these sequential scans generate a huge amount of I/O on their storage devices. I guess that billing you (the developer of the application) for its bad behavior is the only way for them to signal back to you to maybe look into properly indexing your data. This seems similar to how you pay for badly performing algorithms by having to scale your application to more servers & then paying for it.
@@ko-Daegu That's a very valid question. It can be if you don't know what you're doing or aren't careful. The problematic issue here is called "SQL injection": when you build your query you often compare columns to data coming from the user (e.g. selecting the correct entry from the "users" table corresponding to an email address the user wants to use to log in). If you build your query by simply inserting the user-provided value verbatim, the user can then provide malicious input that actually alters the query to really do anything at all. There are two ways to deal with it: quoting (escaping) the value when construct the query or using placeholders. Most if not all database access libraries provide functions for both. You just have to be conscious about this & remember to do this each & every time. Speaking from experience, if you've done it long enough, it isn't that hard. Use a very broad & general rule such as "use placeholders for anything that isn't a hard-coded value" and you'll generally do fine. One advantage of ORMs is that they do take care of this particular type of problem for you very well. That being said, even with ORMs you often find yourself in the situation of writing SQL for certain types of queries that just cannot be expressed easily with ORMs, especially when it comes to reporting, aggregating data, or for high-performance modifications directly in the database (meaning no round trip through the application). Therefore you still need to know how to write safe SQL even if you're using ORMs.
What is there to chat about??? First address the issue of doing the joins manually by actually just combining the results together instead of doing using actual join query then we’ll give you the chance.
Whole article just reads like a dev that went in head first without doing any research and instead of blaming himself he takes it out on a god damn NPM package, lmfao.
The enumlated foreign keys is a fix Prisma did specifically for PlanetScale(vitess). Since they do not allow foreign keys yet prisma's engine depends on it.
@@Danefrak PlanetScale is using MySQL shards/nodes and foreign keys would have bad impact on the performance. Prisma's relation mode is a pretty easy way to work around this. relationMode = "prisma"
I literally did a migration from Mongo to Postgresql as my first very first Epic. It lasted 6 months (very small team) but I learned so much and enjoy the entire process with my Lead dev 😅. Very funny to see prime reading an article about few days after our deployment 😂.
@@ThePrimeTimeagenFor the most part I did discover a few things with this article which I was suspecting when I was reading the logs of the database. However, as you said during the video, Prisma has imo a very good developer experience even tho you write no SQL and Prisma makes you think that you do some cool joins 😂. Lastly, we did configure the connection pool and we had no issue about it with our test and production environment so 🤷♂️.
@@linkersh Project started with MongoDB because of its flexibility and ability to produce quick feature in a very small amount of time (the company was small). However, the app growed and a lot of collections were already emulating what a good relational database was doing but faster and more efficiently. Finally, we had a very good system coupled with GraphQL to build a pipeline given some resolveInfos, but some of our UI components needed a lot of informations and we were reaching at least 200 requests and beyond depending on the amount of stuff to display. I hope that it is at least understandable because english is not my native language 😅.
ORMs.. argh, I remember one legacy project I had to fix. Customer was complaining that their web app was slow and after some digging we found out that the main page generated tad over 100k SQL requests. The ORM basicly pulled most of the database (but just primary keys) on each page load and then generated objects from that and populated their fields by pulling every single field via separate SQL query. Fixed the issue by just writing three SQL queries to pull all required info.
The way planet scale charges is how AWS DynamoDB charges as well, it's based on rows read by the system not rows returned to the user. But in DynamoDB you are able to estimate the number of rows read internally via the amount of capacity units (used to determine level of work done).
All of these issues mirror my real-world experience with both Prisma and Planetscale. I don't understand how JS developers live with the absolutely horrendous performance of their tooling as if it's somehow acceptable, but it's just not.
because of conviennce, as beautifully said in the video. Most of JS developers are, in essence, frontend developers. They're not used to connecting to databases, managing pools and transactions, etc. So, they opt-in for the "shortest" path possible, and continue to be scared from raw SQL lol.
The reason we are considering Prisma was the first two lines - writing schema (and getting type safety in the proj), and performing migrations. Also, it may automate some rest/graphql endpoints. Sad to hear about the underlying performance issues at scale.
I'm currently trying out Kysely and it really works well for me. Manually writing migrations and then regenerating the typings from the resulting database schema. And it's just a query builder after all (with first-class typescript support), meaning performance is pretty much the same as raw SQL.
Drizzle is really great, with just a couple drawbacks that I've encountered so far. One of which is that it won't generate UNIQUE constraints (yet) but it's not a deal-breaker to me.
@@DragonRaider5hey dragon I will blow your mind. Use Prisma as the thing it was intended to be, a schema. Use that to do your migrations and parse the thing into Kysely on build or dev. We use this, this article’s author even uses this. Kysely-prisma, check it out.
We found out the hard way that Prisma and AWS Lambda was a terrible idea given how much code is generated including the GraphQL resolvers and binaries that it puts directly in node_modules (Javascript). We also had to directly delete certain files/folders in node_modules to get the package to fit within the 50mb limit. IMO the trouble with ORMs is that to get the best performance you have to have a deep understanding of SQL in the first place. This goes beyond just adding indexes but understanding the query plan (which can change overtime due to the amount of data being added) and how to alter it as well as understanding deadlocks and lock escalations. I think ORM can be useful for very simple queries, but pass a certain degree of complexity, someone needs to review the resulting SQL which semi-defeats the point of an ORM. Now I prefer a light abstraction over SQL (eg. kysely, drizzle, knex) or raw SQL enclosed in a Typescript function with defined input/output parameters used in conjunction with a library that can generate types from the database (eg. kanel).
As a C# dev, you don't realize what an absolute gift EF Core really is until you find out that this is the state of popular ORMs in other ecosystems. .NET is truly unrivaled in many ways, and data access is one of them.
One of these days Primeagen and these guys will get over their C# allergy and actually try it again. Especially compared to the JS ecosystem it's just so much better. These issues from this video is stuff that's been addressed in EFCore ages ago. The JS ecosystem is just reinventing the wheel every month
@@peanutcelery If you want! Though I think the newest EFCore does well enough that you don't need it. But I don't see a problem using Dapper for particularly hot queries
@@johnhershberg5915 Well said. I occasionally watch these JS-centric channels and I'm constantly like "we've literally solved this problem for years in the C#/.NET world"; and I wonder why Primeagen in particular doesn't pay attention to .NET, it's at least as good as any other platform, and superior in many respects.
True. I wanted to learn a new programming language for backend, my only requirement was an ORM that's on par with EF Core. But without success. Hibernate, GORM, Diesel... none of them can give me a DX like EF Core.
I feel like ORMs are great for 80% of use cases. As long as it has a decent escape hatch for the other 20, I think it's generally worth it. Obviously, if you already know you're gonna do a lot of super custom querying, or need to optimize for maximum performance, raw SQL (or a good builder) is a must. That's not most people, imho.
I suggest it. For schema management. People forget the role of prisma. It’s a schema first and foremost with parseable AST into all languages. That’s the goal. I run all my queries from Kysely which is generated typesafe from Prisma AST. You can also reverse parse back to prisma which makes it an insane tool for CMS automated database management. Prisma as an ORM is beyond stupid. Prisma as a schema manager is the best tool on the market today, yes really. It’s migration tool is unmatched.
@@philheathslegalteam That’s a fair point. I do think Prisma is an amazing tool for defining a schema. That step is independent from querying, so performance is almost a nonexistent concern. You can even map the Prisma names to underlying Postgres “conventions” which make doing raw queries with a tool like Kysely pretty neat. It almost feels like Prisma should come up with their own flavor of a query builder. I would wholeheartedly adopt such a tool if performance was a keystone of its development.
@@philheathslegalteamI thought it was just an orm with a specific schema generator, but what you're saying can make it plausible to use with entity framework omg
Hi, Igal from Kysely (not the creator, Sami is). Regarding type-safety comments in the video. with Kysely, we believe the database should be source of truth. you could still manually define the types if you want to, but we encourage you to use prisma-kysely, kysely-codegen, kanel to autogenerate the types and keep them aligned with databsae state over time. we only introduce features if we can implement them in a type-safe way, and "what you see is what you get" is a core design principle - the API should be as close to SQL as possible. Regarding the "not-feature complete" comment in the article, well we're close to v1, pretty stable and production-ready. if there's something missing it's either intentional (out of scope for a query builder, THIS IS NOT AN ORM), already requested and not top priority, or we haven't thought about it so please submit an issue.
Sometime late last year when I was working on a project, i noticed a significant delay in data fetching so I decided to log my queries, that was when I realized prisma was making 5 different queries for a supposed join query, I had to write raw SQL to solve the problem.
Reminds me when I worked at a company where we spent hours and hours optimizing HUGE LINQ expressions with tons of joins, instead of just writing it in SQL lol
@@ko-Daegu SQL Injections are a solved problem and have been for a long time. All major SQL Databases support parameterized queries at the DB level. This completely eliminates SQL Injection. Don't do string cats and don't do string escapes! Use the databases tools.
I think that they made Prisma so it would "work" with everything. You can't really join in MongoDB for example so the obvious solution is to go "Screw it, how bad can it be if i do the join by hand?". As an added bonus you can get some consistency across the board if for some reason some DB does some weird stuff or doesn't support some kind of operation. A bit sad that there's some performance issues, but in the end I'm sure that not too many people care since not everyone works with enough data & complexity to make it a big issue.
Prisma for migrations, simple queries and mutations. Kysely for Complex Reads and joins. Also don't use serverless. Prisma DX is noticeable when doing advanced relational inserts and updates. Like updating a Blog post, as well as creating or assigning existing categories/tags if they exist, without having to do a whole bunch of checks yourself.
@@thegrumpydeveloperdrizzle requires manually created schemas in a very ugly unreadable fashion. It’s more of an ORM than a query builder like Kysely. And it’s schema management tool is not good. Even if I used Drizzle I’d still put the whole schema in prisma. So I just use prisma-kysely.
Regarding the chart in 14:00, isn't it how every ORM works? There is some object-oriented API (like thi findMany() method), then there is an engine generating actual SQL queries, and then there is the actual database getting those queries. It's not some crazy level of abstraction, it's just the minimum of what you need to go from objects to queries.
Yes, but in other ORMs the client and query builder is usually one piece of software. Not literally a completely separate binary that your JS module makes network requests to. findMany() is normally a pretty simple affair, with little performance impact, that just results in simple SELECT WHERE queries. They will also generate proper SQL to let the DB do the lifting in case of more complex queries, unlike prisma.
6 minutes into the video, and while I want to say skill issue, I also had the same problems, what the actual problem is, Prisma builds the engine (1) based on your current operating system, and lambda uses amazon linux, while most people are probably running builds either on their local machine or ubuntu through some github actions. So you have to include the lambda engine version rhel-1-xx (something like this) which tells prisma to also install engines for that lambda OS. The trick for me was basically to build the code on an AWS codebuild instance running amazon linux and these problems were no longer a thing as prisma only installed the default engine needed, the second problem was in order to build the prisma client, you need the prisma package installed, which is supposed to be dev dependency, so I had to manually remove the prisma folder from the node_modules (I had to do this because I have all my node_modules in a layer). If I was bundling the code with dependencies, the second issue will be non existent Edit As for the second part (Prisma doing internal joins), I recently found about the joins also and now I've been thinking about switching. currently have about 10k users but I'm wondering if it will become to get slower as it grows
Agree 100%. Every ORM I used I regretted somewhere around the 4-month mark into a project, where I suddenly needed to do something that the ORM did not know how to do. Sage words from a tenured co-worker at a big company "We never have time to do it right, but we always have time to do it again." This article reads like the guy who said that. It shocks me that I have to convince employers that modern abstraction layers have gone too far. It's not just databases either. I struggle to understand why frontend development is now treated the same as backend in terms of having an entire package manager and often multiple layers of transpilation. Arguing the benefits of an ORM to me is like a mechanic bragging that they don't have to know how a car engine works to build or repair it.
I guess I’m gonna defend “prisma joins” thingy from one point. Sometimes issuing separate queries to do joins is a desirable approach. I’d argue that prisma doing “joins” in-process is a good thing… in certain cases… In others, its behaviour should be configurable. For e.g. fetching users, each with a sequence of posts. Doing a single query isn’t efficient. You wouldn’t want to load user fields over the network N times - once for every post they have. This is also the default behaviour of elixir’s Ecto’s “prefetch” directive, yet in Ecto it is configurable. I am in the “just write squel” camp. Unfortunately the typesafety story isn’t there with plainstring querries, and as such dialects like prisma/drizzle/keysley come in. I’m not too keen on any of these. Though I kinda like prisma’s QL. It feels just sql-y enough to be familiar, and typescript-y enough to be correct. Feel like the rust’s sqlx is the way to go. Writing sql, which is guaranteed to produce what you want (even if the whole “build-time db querries” thing is questionable). Maybe I should try doing typescript string literal type parsing magic to parse sql and derive types. Not sure. There are a lot of challenges there.
If you have the ability to return the posts as an array then you could explode it client side and not repeat the user fields. Looks like postgres has an array_agg function that does it.
It's striking to me to learn that Prisma doesn't do database level joins, like why say you support relational dbs if you are not using, arguably, the most powerful SQL clause?
@@sburton84joins are not dependent on foreign keys. To be clear, in PlanetScale, you can absolutely index your “foreign key” columns. They just do not have the referential integrity, and cascade ability
@@sburton84i think what they did is they query two tables from a separate query and combine them manually so that it makes them appear to be a “joined” query, that’s crazy.
I have always had a hatred for slow under performing tech stacks. I get not wanting to write tons of code, but saving 30 hours to deliver junk and then spending 300 hours trying to fix the junk and then just writing the code anyways is as bad as it gets.
I use TypeORM at work on a very large MySQL database with complex relationships. It's not perfect but it's pretty damn good. Doesn't require a DSL, doesn't require a special client or engine that requires Rust to compile, it's all JS. I see zero reason to use Prisma over TypeORM
As of when I last used it; TypeORMs maintainers are unable to fix bugs nor merge fixes in a timely manner, and has a lot of weird behaviors due to poor API design; if I were doing it today I would just use Drizzle at this point and write the SQL (with nice types).
We have not seen anything yet. Wait for couple of more years when web space is dominated by NextJS "full stack" bros who jumped over all fundamentals because Vercel had great marketing and dEvElOpEr ExPeRiEnCe
Don't you feel like the author compares mongodb speed to postgres, rather than Mongoose to Prisma? Btw, Prisma supports mongodb as well. Maybe it'd be better to compare it using the same database? We migrated from TypeORM to Prisma, and it feels quite nice. No hacking was involved. Migrations generation is not that great though.
@@sarabwt contrary to what is said here, it was not a downgrade. My opinion only, prove me wrong: 1. API is awesome, much better than typeorm's. 2. not everyone needs such a performance as described in the article. 3. they released new Prisma version recently, which is like 10x faster than the version described in the article. Primagean also reacted to this release. Ultimately it feels like, they guy did some weird migration without prior research, faced multiple issues and now need someone to blame, to feel better.
@@L0wPressure I'm joking a bit with the downgrade comment :D Now afaik as soon as you need joins, Prisma will explode, right? Isn't this a normal use case? Also, what happens when you want to aggregate data for stats? Do you just use a different tool? New version Addresses the start up time, not the database clusterfuck that they have unless I missed something. Also I would argue that APIs are the same if not better in favor of TypeORM, because TypeORM also has a query builder. The only thing that I dislike about TypeORM is how transactions are handled (it makes it hard to reuse the logic), but I also don't see how they could avoid this.
To all of you who want to start using drizzle orm as codeamn or theo or josh said it. It is not mature doesn't have a adapter for the planet scale database with next auth as prisma does nor has as refined docs, unsigned integer or as big of a community . Right after this video was released planet scale introduced the scaler pro plan where you have unlimited read and write which mitigates the cost issue. starting from $39 it can go up to $999 8 vCPU and 32 GB RAM in AWS which much be sufficient for almost all use cases and vast majority of people. If you need more than this you are already a multi million dollar company and that's that. Untill Drizzle goes to version 1 I am waiting and will go with prisma and planetscale.
the planet scale rows read part is accurate. i’m ok with this for OLTP apps. their analysis tools are really good and this lets you isolate queries with a ratio of read/returned rows that is skewed towards rows read. this normally points to an index that is required or where statement that needs to be tweaked and in turn results in faster response time.
"Kysely" actually just means "Query" in Finnish. I couldn't find any info on if the project was started by Finnish people or not though. So could be a sweet sweet coincidence.
The first thing was definitely skill issue. Unless you tell it to add a bunch of other shit, Prisma is only the 12mb. It doesn't have multiple engines. Just the one.
I've inherited a project using Prisma, and I can attest that it is literally the worst I've used several ORMs in several langs over the years and Prisma is by far the shittiest I've seen (for SQL at least, it's possible that it is ok for NoSQL, but I feel like they had already NoSQL support and then they slapped on Postgres support, because ppl asked for it, without adding actual SQL support... next to the join-issue mentioned in the article it also does `distinct` in memory and there official reasoning in the docs is: the other DBs we support can't do it, so we implemented it and just that for Postgres as well... instead of, you know, using the actual DB features... I just hate it so much...)
LOL! I got suggested this as I've been ripping my hair out due to the limit connection pool. If you look up the error you will find a bunch of related issues that have been closed with mostly no resolution. `yarn add pg` here I come!!!
I have used Prisma in a production environment, and while it supports simple lateral joins, it lacks built-in support for other types of joins unless you use raw queries. One downside is that pagination inside a subquery isn't possible, which can be frustrating. In terms of performance, basic queries can be slower. However, Prisma's client and migration tools are incredibly time-saving, ensuring consistent database schemas and simplifying CI/CD processes. Whether Prisma is the right choice depends on the scale of the application. In my opinion, it's not the best fit for large-scale projects.
I think Prisma is great for a totally new and fresh project with unproved value for the business. It's great for MVP. You won't spend much time building the database infrastructure. I prefer to use 3rd party services on that stage because I can build something very fast, prove the concept, and start making money. And when the project starts growing I would start to migrate from these tools to something more efficient. So this decision described in the article was bad right from the start. They need to fire that man who had a final architectural decision because he will inevitably lead them to death in the future. A competent person always tries to choose the right tool for the job.
It might be bad but it was a lesson learnt. You may find that there are some devs in a similar position wanting to do the same thing. Now its been tried and its a flop so thats no longer something to be thought out anymore.
Engineers always say it won’t scale for 20 million users. But the business has to survive till they get there. Cost of development is lesser with such abstract layers.
Your final comment about "Typesaftey" is really on point. It kinda puts the lie that languages like Python/Typescript/Ruby are so great because developers can be super productive in it.
Prisma saved me a lot of times with many projects, but if I was looking for some small optimized script to throw on lambda, I would probably not use it. Anyway, Prisma allows to run raw queries as well whenever it does not handle something directly, BUT - joins are in fact there, automatic types for each model, and it;s kinda db agnostic, and it is possible to connect multiple dbs easily. Very versatile, and I think these guys just had bad devs.
In my experince its really usefull when you have a over engineered db and you dont havw time to rebuild the who product, but then once u get time throw prisma out use csql or something like that
I like Prisma for CRUD apps, however my use case is a better fit I think. - 10 concurrent users max - web app (no lambdas) - Mostly actions on single entities. - I use raw SQL (via the Prisma API) for my (2~ish) join operations. I think this article describes mostely a skill issue. There is a manual transaction api. Where Prisma shines is type safety. If you don't value that, then don't use Prisma.
Oh, Object relational modelling... This is the most intellidumbsense solution of all. You need to fight two fronts: the actual database capabilities and what the "intelligence" of the ORM wants to do.
JUST. USE. KYSELY. Run the full schema in prisma - it’s migration engine is unmatched. Generate from AST into Kysely (prisma-kysely) then use that. No you write as close to SQL as possible.
Postgres + Prisma doesn't have these issues because it doesn't try to emulate foreign keys. We use Prisma in prod with progres, the database has several terabytes of data and more than 1.5 billion rows. Without batching and with postgres autocommit transactions Prisma is able to insert about 17k inserts per second. The prisma engine did complicate our build step as we chose to build the engine for node-alpine, fortunately cargo makes it fairly easy, our builds are large mostly due to npm package bloat, the prisma engine isn't much extra. The migrations tool is pretty good. Better than what typeorm provides IMO. Having to run a migration is inconvenient but so far they have been fairly easy and without much trouble.
Prisma v5 addresses graphql calls and joins performance issues, but still ORM`s IMHO are mostly overkill or do some unknown, uncontrollable magic with a performance hit. I personally settled at query builder for typed database response.
I was awed when we bet our start up on t3 stack. 0 row, planetscale-backed, prisma-powered 2-way joined query took 300ms Feed in some seeding data, now every request takes 800ms. I thought Next.js was the problem until we just use raw SQL queries. Prisma DSL is cool, though.
I implemented a module over primsa for multi tenancy because we REALLY wanted to go with prisma. Still the worst mistake to date but also the most fun thing I did.
Wait, but prisma also lets you write sanitised sql queries ($queryRaw) as well as truly raw ones ($queryRawUnsafe). I was thinking about doing heavy read lifting this way and in other cases don't bother and enjoy the DX... Do raw SQLs through prisma have their own shortcomings too? Though on a second thought types might become a nightmare
Not using joins (by default) is a great performance idea. This is the first thing you get rid of when you run into DB performance problems. But you still need to be able to do them when you have to. That's the main thing with Prisma - no plan B to fine-tune SQL if you really have to. For example, in laravel eloquent you can start with just using it as ORM, then slowly when you hit performance and edge-cases it's easy to start writing raw sql in places you need and you don't need to dump and rewrite everything at once.
@@sck3570 I don't understand then, what is the issue with Prisma if you can go down to SQL any time you need better performance? As far as I know, any other ORM would have similar issues but as long as the SQL route is available it's considered fine.
@@zen-ventzi-marinov From watching this video and reading comments my understanding is that people want an all or nothing tool(solution) they don't want to write SQL at all. otherwise you can use Prisma for almost 90% of your app/site which speeds up development and in 10% where you need optimization you can write raw sql or use query builders like kysely
Why not using joins is a great performance idea? I think I am not following you... Why should I do at the application level something which the underlying db engine has been specifically developed for?
Everyone shitting on Prisma in the comments based off 1 article that in the very first paragraph displays that they may not know wtf they are talking about, having to rewrite their entire codebase twice due to poor judgement... maybe, MAYBE, Prisma isn't quite that bad and maybe you shouldn't take one scorned developer's word as gospel as I'm quite sure, lots of what he says is blatantly inaccurate, misrepresented, or just examples of him trying to fit a square peg in a round hole. While it is true about the joins, so many people are taking for granted that a single query is always the right or better performing choice which is simply not the case. An ORM like Prisma is trying to apply generalized best practices. DB level joins can actually be quite expensive and often, generally, you can increase performance by decomposing DB level joins into multiple smaller single-table queries. This is a common and known technique done by many high performance web sites. While there are cases where a single query is better, generally you gain performance through this decomposition. So Prisma decided to adopt this approach as the default first since in general, it increases performance in most situations. They have single query joins on their roadmap for the cases where it's not better performing and it will be added at some point. For now you can absolutely just hand write a query here and there as needed if you are in a situation where single query join is faster and you need that speed... Also, lots of people saying "this is what JS people put up with"... the Prisma core engine is written in Rust. Generally if you are running into low performing queries with Prisma, you are doing something stupid or misusing it in some way. Prisma is absolutely fine, in fact it's great, for what it's actually trying to be, which is a super DX friendly general purpose ORM. If YOU do something dumb like try to put it in a serverless function, or use idiotic and unnecessarily complex schema's or build data models that require niche and unique specific DB provider features... you're going to have a bad time and that's not Prisma's fault.
But what's funny about ORM is what I've seen is people use ORM for very simple scenarios (select * from X where something) but in some more complex situations they... WRITE NATIVE SQL QUERIES. Shouldn't it be the opposite?! I sort of understand as whenever I work with backend (not very often tho) and I have to get something more complex from the db I much rather use native SQL than trying to figure out how the engine is going to fetch my data, what is the syntax, how it relates to transactions, etc... Or what people do is they perform the logic in the code which is a performance hell.
I often start with the django orm, output it's sql and optimize from there, if someone asks me for an sql query, which is probably the usecase you mean. However, if the app is built around the orm, usually the choices made are catering the orm's strengths or to the program business logic. The problem is, that there is a certain amount of complexity, where an advanced sql query just becomes easier to write, than the ORM version, but it is easier to write it as an afterthought, since the code creating that complexity is usually business logic related. A comparison would be physics equations for different systems put into one giant equation often allows you to compress it for a certain problem field, by eliminating some of the variables, but the underlying hidden principles of all the equations that created it in theory are still valid. This is why even if I optimize a query into an SQL, I keep the pure ORM Version around as long as it is maintainable, as it shows where the complex joins and nested selects came from.
Not using an ORM or query builder, how would you do the following? You need to get a list of records with several fields, and to each of those records you need to get again a small list of records from another table. You could do that with a JOIN, but then it will multiply the columns of the records from the first table and you need to write logic that plucks out the, lets call them parent records, and group their nested records to them. Another option would be to do 2 SELECTs. First you select the first set of records with whatever query condition you use, then you take the IDs of those records and use them on the foreign keys of the nested records in a second SELECT (instead of the LEFT JOIN), and then you again have to group the nested records to their parent records. But it's clearer what fields belong to what table, you don't need to handle name conflicts and don't need to handle the NULL case of the LEFT JOIN etc. So it's simpler to think about. I think ORMs usually do it the 2nd way? If I have to do it manually I do it the 2nd way. It is 2 SELECTs, not 1+N SELECTs. When people don't use ORMs they tend to implement this in 1+N SELECTs, because that is the most straight forward. In that case ORMs improve the performance on this quite a bit.
This happens when you dont know your tools and want to use a gold hammer to hit a nail, at some point you have to admit this whole endeavor was your fault.
used Prisma 2/3 in a job, it was "fine" it had it's slip ups but it was usable, but MAN when you create/edit/delete a migration and you are working with a team in git it is HELL to merge branches and run migrations.
I've written and worked on ORM. There's no such thing as "Next-gen ORM". That usually means the developers didn't bother to study and understand all of the sharp edges of ORM. If the application has to integrate a dozen databases, using an ORM to read data to a normalized model "can" save you a ton of time. There's a big gotcha, you have to know what you're doing or it becomes a nightmare. If your data model doesn't need polymorphic queries, using an ORM is questionable and probably over kill. the docs from prisma tells me the developers don't understand RDBMS or how to compile higher level object mapping to efficient SQL queries that runs on the database server.
I have ran into the same problem with prisma not using joins. However, prisma has a feature that you can enable "relational-joins" and then it works as expected
i work with a client who has a prisma psql setup, the previous devs fucked up and started to write their own migrations apart from prisma, now the prisma migrations wont even work and if we try to infer the db and then migrate we would loose all the data as the migrations are not synced. So now the workflow is prisma for typescript inferences custom migrations for sql
"use prisma use prisma!! it's so good ! I will never go back to anything !!"
next week: "prisma's trash and if you're using it you are dumb !!!"
Thank you tech youtubers, keep it up lets go.
literally i was thinking the same, the blog author obviously made a wrong design choice but for example if you want to develop a small application in a short time with not much scaling planned the dev experience with prisma is great imo
yeah, anytime any new technology comes up
@@dubble_gdo you have any productions apps using prisma? Me neither.
these guys' are generally s*hit looking to make money
I always enjoy hearing about the battles people are willing to endure, just to avoid writing SQL queries 😂 honestly, raw SQL is FINE
This is one of the perfect examples I think of: 1. Jr dev: writting raw sql, Mid dev: Avoid SQL in codebase at all cost, Senior dev: write raw sql.
@@chevi_to I would disagree for Senior dev, I don' t always write my sql, why would I do that when I only need to find by an id ? that what my active record find(id) is for :D. But yeah, Senior dev just will have a higher tendency to write sql to do something that is not just basic.
@@chevi_towell, I ain't middle yet, so I dunno😂😂. But actually as a junior fullstack dev I find sql really ok, it's way easier than other things that I have to do at my job.
Probably I will have a moment where I will go "f that shit", buuut i don't really know what should happen for me to change like that.
@chevi_to In my experience the common trajectory for JR. devs is learning ORMs first and basically do this for as long as humanly possible. In fact they learn SQL through referencing their mental map of ORMs and connecting that to SQL.
@@chevi_to 1. Jr. dev: writting raw SQL all the time
2. Mid. dev: avoid SQL in codebase at all cost
3. Senior dev.: write raw SQL when optimizations are needed or very complex queries are involved
This sounds like a company that reaches for the “next new shiny thing™️”
The amount of individual technologies mentioned and used in this article was incredible…
Exactly.
Exactly! I LOLd at how they ended the blog with "Kelsey not feature complete". Sounds like they're setting themselves up for a future rewrite
This!
Just to add bit of more in-depth info.
About transactions in PostgreSQL: in auto-commit mode each INSERT is treated as if it was in a transaction of its own. A PostgreSQL client can start your own transaction manually & commit or revert it manually, but if you don't, you'll have the default behavior. The bad performance of having only one modification (not just INSERTs, also UPDATEs, DELETEs, even schema modifications) in each transaction is well known. The usual way to speed things up is to bulk as many modifications as make sense into a single transaction. The gains are huge. For example, if you need to insert 20M records into a table, prepare the INSERT statement, start a transaction, execute the prepared statement a large number of times (e.g. 1,000 just to pick one), commit the transaction, repeat until everything's inserted. (Don't bulk all 20M into a single transaction if you have other clients that need to modify the same data as modifying stuff will lock that stuff for the remainder of the current transaction's life time, give them a chance to do things by committing from time to time.)
About returning results: PostgreSQL doesn't return stuff by default. Doing a basic INSERT the database handle you're working with will only return whether or not the INSERT was successful. If you need some data back from the same insert (usually the primary key), modify your statement to read "INSERT INTO … RETURNING id". That way your program will still run the "execute statement" function on your database/statement handle which still only returns success/error, but your program can immediately run the "fetch results" function on the same handle & get the ID back. You can return other values the same way if you need them. But again, not by default. It's very much "don't pay for what you don't use".
About the "rows read vs rows returned": imagine having a huge table but forgetting to create an index on some non-primary key column you often read (e.g. a table of users with an integer ID as the primary key, but you often need to look up users by their email address). In that case the database doesn't have any idea where on disk the requested row is exactly. All it can do is start from the start of the table & read every row until it finds the requested one. Apart from being slow (and therefore bad for you, the customer) it's also bad for the service provider as these sequential scans generate a huge amount of I/O on their storage devices. I guess that billing you (the developer of the application) for its bad behavior is the only way for them to signal back to you to maybe look into properly indexing your data. This seems similar to how you pay for badly performing algorithms by having to scale your application to more servers & then paying for it.
@@mbunkus This makes sense on planet scale pricing
is writing a raw sql worse than using somethign like prisma or orm
security wise ?
@@ko-Daegu That's a very valid question.
It can be if you don't know what you're doing or aren't careful. The problematic issue here is called "SQL injection": when you build your query you often compare columns to data coming from the user (e.g. selecting the correct entry from the "users" table corresponding to an email address the user wants to use to log in). If you build your query by simply inserting the user-provided value verbatim, the user can then provide malicious input that actually alters the query to really do anything at all.
There are two ways to deal with it: quoting (escaping) the value when construct the query or using placeholders. Most if not all database access libraries provide functions for both. You just have to be conscious about this & remember to do this each & every time. Speaking from experience, if you've done it long enough, it isn't that hard. Use a very broad & general rule such as "use placeholders for anything that isn't a hard-coded value" and you'll generally do fine.
One advantage of ORMs is that they do take care of this particular type of problem for you very well. That being said, even with ORMs you often find yourself in the situation of writing SQL for certain types of queries that just cannot be expressed easily with ORMs, especially when it comes to reporting, aggregating data, or for high-performance modifications directly in the database (meaning no round trip through the application). Therefore you still need to know how to write safe SQL even if you're using ORMs.
Wow, returning id was eye opening for me
This is a master class in using tools the wrong way. Would love to chat about the *right* way if y’all are interested ;)
What is there to chat about??? First address the issue of doing the joins manually by actually just combining the results together instead of doing using actual join query then we’ll give you the chance.
cope more js dev, your tool sucks
t3 stack is heresy
@@Notoriousjunior374 Who do you mean by "we"? Speak for yourself, I have nothing against one more video on the topic, this time from Theo.
@t3dotgg please do a follow up video
Wow the egos really need to check themselves
Whole article just reads like a dev that went in head first without doing any research and instead of blaming himself he takes it out on a god damn NPM package, lmfao.
The enumlated foreign keys is a fix Prisma did specifically for PlanetScale(vitess). Since they do not allow foreign keys yet prisma's engine depends on it.
Yes, but the join thing is different. That’s just how prisma works for now.
How do you not allow foreign key
@@Danefrak I said foreign keys but I meant foreign key constraints when speaking to vitess not allowing it.
@@Danefrak PlanetScale is using MySQL shards/nodes and foreign keys would have bad impact on the performance. Prisma's relation mode is a pretty easy way to work around this. relationMode = "prisma"
Lol, this is absolutely the epitome of peak Node "engineering".
I'm glad this elite output of a team of top 2% Node devs is here for us.
I literally did a migration from Mongo to Postgresql as my first very first Epic. It lasted 6 months (very small team) but I learned so much and enjoy the entire process with my Lead dev 😅. Very funny to see prime reading an article about few days after our deployment 😂.
did the article touch a special place in your trauma?
@@ThePrimeTimeagenFor the most part I did discover a few things with this article which I was suspecting when I was reading the logs of the database.
However, as you said during the video, Prisma has imo a very good developer experience even tho you write no SQL and Prisma makes you think that you do some cool joins 😂.
Lastly, we did configure the connection pool and we had no issue about it with our test and production environment so 🤷♂️.
@@enzocecillon1452 What is the reason behind your switch? Performance? Structured data? or something else?
@@linkersh
Project started with MongoDB because of its flexibility and ability to produce quick feature in a very small amount of time (the company was small).
However, the app growed and a lot of collections were already emulating what a good relational database was doing but faster and more efficiently.
Finally, we had a very good system coupled with GraphQL to build a pipeline given some resolveInfos, but some of our UI components needed a lot of informations and we were reaching at least 200 requests and beyond depending on the amount of stuff to display.
I hope that it is at least understandable because english is not my native language 😅.
ORMs.. argh, I remember one legacy project I had to fix. Customer was complaining that their web app was slow and after some digging we found out that the main page generated tad over 100k SQL requests. The ORM basicly pulled most of the database (but just primary keys) on each page load and then generated objects from that and populated their fields by pulling every single field via separate SQL query. Fixed the issue by just writing three SQL queries to pull all required info.
The way planet scale charges is how AWS DynamoDB charges as well, it's based on rows read by the system not rows returned to the user. But in DynamoDB you are able to estimate the number of rows read internally via the amount of capacity units (used to determine level of work done).
All of these issues mirror my real-world experience with both Prisma and Planetscale. I don't understand how JS developers live with the absolutely horrendous performance of their tooling as if it's somehow acceptable, but it's just not.
Because they're used to the absolutely horrendous performance of their language
But think about dEvElOpEr eXpErIeNcE
because of conviennce, as beautifully said in the video. Most of JS developers are, in essence, frontend developers.
They're not used to connecting to databases, managing pools and transactions, etc. So, they opt-in for the "shortest" path possible, and continue to be scared from raw SQL lol.
@@geni15oForgive my language, but that is utter horseshit. ORMs exist and are popular in every modern language.
@@mormantu8561 I agree 🤷♂️
The reason we are considering Prisma was the first two lines - writing schema (and getting type safety in the proj), and performing migrations. Also, it may automate some rest/graphql endpoints. Sad to hear about the underlying performance issues at scale.
I'm currently trying out Kysely and it really works well for me. Manually writing migrations and then regenerating the typings from the resulting database schema. And it's just a query builder after all (with first-class typescript support), meaning performance is pretty much the same as raw SQL.
Drizzle is really great, with just a couple drawbacks that I've encountered so far. One of which is that it won't generate UNIQUE constraints (yet) but it's not a deal-breaker to me.
I take it back, looks like they've fixed that since about a month ago 😅
@@DragonRaider5hey dragon I will blow your mind.
Use Prisma as the thing it was intended to be, a schema. Use that to do your migrations and parse the thing into Kysely on build or dev.
We use this, this article’s author even uses this. Kysely-prisma, check it out.
So glad to watch this video as I build my nextjs application with prisma
We found out the hard way that Prisma and AWS Lambda was a terrible idea given how much code is generated including the GraphQL resolvers and binaries that it puts directly in node_modules (Javascript). We also had to directly delete certain files/folders in node_modules to get the package to fit within the 50mb limit.
IMO the trouble with ORMs is that to get the best performance you have to have a deep understanding of SQL in the first place. This goes beyond just adding indexes but understanding the query plan (which can change overtime due to the amount of data being added) and how to alter it as well as understanding deadlocks and lock escalations. I think ORM can be useful for very simple queries, but pass a certain degree of complexity, someone needs to review the resulting SQL which semi-defeats the point of an ORM.
Now I prefer a light abstraction over SQL (eg. kysely, drizzle, knex) or raw SQL enclosed in a Typescript function with defined input/output parameters used in conjunction with a library that can generate types from the database (eg. kanel).
❤from Kysely. Btw, there's a new kanel-kysely package released this week.
Whatever the topic, thanks Prime for reinvigorating my interest in development. Configured Neovim on my Kali yesterday. Hoping to get blazingly fast ⚡
Imagine using a VC backed ORM...
As a C# dev, you don't realize what an absolute gift EF Core really is until you find out that this is the state of popular ORMs in other ecosystems.
.NET is truly unrivaled in many ways, and data access is one of them.
One of these days Primeagen and these guys will get over their C# allergy and actually try it again. Especially compared to the JS ecosystem it's just so much better. These issues from this video is stuff that's been addressed in EFCore ages ago. The JS ecosystem is just reinventing the wheel every month
Dapper?
@@peanutcelery If you want! Though I think the newest EFCore does well enough that you don't need it. But I don't see a problem using Dapper for particularly hot queries
Prisma is not popular choice, EF is very popular. Prisma is only popular on youtube. In real world prisma is used only in very small apps.
@@johnhershberg5915 Well said. I occasionally watch these JS-centric channels and I'm constantly like "we've literally solved this problem for years in the C#/.NET world"; and I wonder why Primeagen in particular doesn't pay attention to .NET, it's at least as good as any other platform, and superior in many respects.
Entity framework core seems like a dream to use compared to this
EF Core is the best ORM I have seen in any language. And I've tried many.
@@amirhosseinahmadi3706 and for raw sql dapper is a good complimentary to the C# stack
It’s so good. It’s API is just amazing - C# collections.
No one ever got fired or had to rewrite an application because they chose EF Core
True. I wanted to learn a new programming language for backend, my only requirement was an ORM that's on par with EF Core. But without success. Hibernate, GORM, Diesel... none of them can give me a DX like EF Core.
I feel like ORMs are great for 80% of use cases. As long as it has a decent escape hatch for the other 20, I think it's generally worth it.
Obviously, if you already know you're gonna do a lot of super custom querying, or need to optimize for maximum performance, raw SQL (or a good builder) is a must. That's not most people, imho.
I'm a Post Gres Q L guy
Postgressqueal
same here ✋
I love the idea of Prisma, and I think it’s fun to work with. But the downsides definitely are too much to even suggest using it.
I suggest it. For schema management.
People forget the role of prisma. It’s a schema first and foremost with parseable AST into all languages. That’s the goal.
I run all my queries from Kysely which is generated typesafe from Prisma AST.
You can also reverse parse back to prisma which makes it an insane tool for CMS automated database management.
Prisma as an ORM is beyond stupid. Prisma as a schema manager is the best tool on the market today, yes really. It’s migration tool is unmatched.
@@philheathslegalteam That’s a fair point. I do think Prisma is an amazing tool for defining a schema. That step is independent from querying, so performance is almost a nonexistent concern. You can even map the Prisma names to underlying Postgres “conventions” which make doing raw queries with a tool like Kysely pretty neat. It almost feels like Prisma should come up with their own flavor of a query builder. I would wholeheartedly adopt such a tool if performance was a keystone of its development.
@@philheathslegalteamI thought it was just an orm with a specific schema generator, but what you're saying can make it plausible to use with entity framework omg
Hi, Igal from Kysely (not the creator, Sami is).
Regarding type-safety comments in the video. with Kysely, we believe the database should be source of truth. you could still manually define the types if you want to, but we encourage you to use prisma-kysely, kysely-codegen, kanel to autogenerate the types and keep them aligned with databsae state over time. we only introduce features if we can implement them in a type-safe way, and "what you see is what you get" is a core design principle - the API should be as close to SQL as possible.
Regarding the "not-feature complete" comment in the article, well we're close to v1, pretty stable and production-ready. if there's something missing it's either intentional (out of scope for a query builder, THIS IS NOT AN ORM), already requested and not top priority, or we haven't thought about it so please submit an issue.
Sometime late last year when I was working on a project, i noticed a significant delay in data fetching so I decided to log my queries, that was when I realized prisma was making 5 different queries for a supposed join query, I had to write raw SQL to solve the problem.
Reminds me when I worked at a company where we spent hours and hours optimizing HUGE LINQ expressions with tons of joins, instead of just writing it in SQL lol
this mentality is why we have a lot of SQL injections until this day
@@ko-Daegu yeah, definitely ORMs are the only possible fix for SQL injection
@@ko-Daegu SQL Injections are a solved problem and have been for a long time. All major SQL Databases support parameterized queries at the DB level. This completely eliminates SQL Injection. Don't do string cats and don't do string escapes! Use the databases tools.
I think that they made Prisma so it would "work" with everything. You can't really join in MongoDB for example so the obvious solution is to go "Screw it, how bad can it be if i do the join by hand?". As an added bonus you can get some consistency across the board if for some reason some DB does some weird stuff or doesn't support some kind of operation. A bit sad that there's some performance issues, but in the end I'm sure that not too many people care since not everyone works with enough data & complexity to make it a big issue.
These soyboys will do anything but Learn basic SQL and use a query builder
@@electrolyteorb🤔
Prisma for migrations, simple queries and mutations. Kysely for Complex Reads and joins. Also don't use serverless. Prisma DX is noticeable when doing advanced relational inserts and updates. Like updating a Blog post, as well as creating or assigning existing categories/tags if they exist, without having to do a whole bunch of checks yourself.
solid take
Do you work with Prisma in production, and has using Kysely with generated tables via prisma created any issues?
@@sck3570 Yes. Currently running fine on a site with 100K users a month.
Curious your take on drizzle having worked with these other two ts query builders. Was it that it came out later and just not necessary with kysely?
@@thegrumpydeveloperdrizzle requires manually created schemas in a very ugly unreadable fashion.
It’s more of an ORM than a query builder like Kysely. And it’s schema management tool is not good.
Even if I used Drizzle I’d still put the whole schema in prisma. So I just use prisma-kysely.
Regarding the chart in 14:00, isn't it how every ORM works? There is some object-oriented API (like thi findMany() method), then there is an engine generating actual SQL queries, and then there is the actual database getting those queries. It's not some crazy level of abstraction, it's just the minimum of what you need to go from objects to queries.
Yes, but in other ORMs the client and query builder is usually one piece of software. Not literally a completely separate binary that your JS module makes network requests to.
findMany() is normally a pretty simple affair, with little performance impact, that just results in simple SELECT WHERE queries. They will also generate proper SQL to let the DB do the lifting in case of more complex queries, unlike prisma.
i use arch, btw
Kali here
@@manaspatil3996 isn't kali deprecated ?
6 minutes into the video, and while I want to say skill issue, I also had the same problems, what the actual problem is, Prisma builds the engine (1) based on your current operating system, and lambda uses amazon linux, while most people are probably running builds either on their local machine or ubuntu through some github actions. So you have to include the lambda engine version rhel-1-xx (something like this) which tells prisma to also install engines for that lambda OS.
The trick for me was basically to build the code on an AWS codebuild instance running amazon linux and these problems were no longer a thing as prisma only installed the default engine needed, the second problem was in order to build the prisma client, you need the prisma package installed, which is supposed to be dev dependency, so I had to manually remove the prisma folder from the node_modules (I had to do this because I have all my node_modules in a layer). If I was bundling the code with dependencies, the second issue will be non existent
Edit
As for the second part (Prisma doing internal joins), I recently found about the joins also and now I've been thinking about switching. currently have about 10k users but I'm wondering if it will become to get slower as it grows
The author also didn’t know that you can build docker containers to run in AWS lambda. They don’t have a container size limit
Agree 100%. Every ORM I used I regretted somewhere around the 4-month mark into a project, where I suddenly needed to do something that the ORM did not know how to do. Sage words from a tenured co-worker at a big company "We never have time to do it right, but we always have time to do it again." This article reads like the guy who said that. It shocks me that I have to convince employers that modern abstraction layers have gone too far. It's not just databases either. I struggle to understand why frontend development is now treated the same as backend in terms of having an entire package manager and often multiple layers of transpilation. Arguing the benefits of an ORM to me is like a mechanic bragging that they don't have to know how a car engine works to build or repair it.
Yeah we just need an Sql builder with typesafe language support and we can stop there
glad to see Melkey on your stream, saw his videos about his journey with Neovim, he seems like a chill guy
I guess I’m gonna defend “prisma joins” thingy from one point. Sometimes issuing separate queries to do joins is a desirable approach. I’d argue that prisma doing “joins” in-process is a good thing… in certain cases… In others, its behaviour should be configurable.
For e.g. fetching users, each with a sequence of posts. Doing a single query isn’t efficient. You wouldn’t want to load user fields over the network N times - once for every post they have.
This is also the default behaviour of elixir’s Ecto’s “prefetch” directive, yet in Ecto it is configurable.
I am in the “just write squel” camp. Unfortunately the typesafety story isn’t there with plainstring querries, and as such dialects like prisma/drizzle/keysley come in.
I’m not too keen on any of these. Though I kinda like prisma’s QL. It feels just sql-y enough to be familiar, and typescript-y enough to be correct.
Feel like the rust’s sqlx is the way to go. Writing sql, which is guaranteed to produce what you want (even if the whole “build-time db querries” thing is questionable). Maybe I should try doing typescript string literal type parsing magic to parse sql and derive types. Not sure. There are a lot of challenges there.
If you have the ability to return the posts as an array then you could explode it client side and not repeat the user fields. Looks like postgres has an array_agg function that does it.
It's striking to me to learn that Prisma doesn't do database level joins, like why say you support relational dbs if you are not using, arguably, the most powerful SQL clause?
Does it *actually* not do database-level joins, or is that only the case when you choose to use a database that doesn't support foreign keys?
@@sburton84 even db that support foreign keys. It pull tons of db rows and join using "RUST ENGINE"
@@sburton84 It simply never does.
@@sburton84joins are not dependent on foreign keys.
To be clear, in PlanetScale, you can absolutely index your “foreign key” columns. They just do not have the referential integrity, and cascade ability
@@sburton84i think what they did is they query two tables from a separate query and combine them manually so that it makes them appear to be a “joined” query, that’s crazy.
I have always had a hatred for slow under performing tech stacks. I get not wanting to write tons of code, but saving 30 hours to deliver junk and then spending 300 hours trying to fix the junk and then just writing the code anyways is as bad as it gets.
This is why I love the Go community. They love writing SQL.
*love writing SQLi
here fixed it for ya
To me prisma is fkn awesome for getting a product off the ground, not a great thing to convert an existing project with heavy data needs to.
I think that it was obvious the CUD in this context means create, update and delete, so a synonym of mutations commands...
rule1 about databases, always use SQL until you are unable to use it anymore.
I use TypeORM at work on a very large MySQL database with complex relationships. It's not perfect but it's pretty damn good. Doesn't require a DSL, doesn't require a special client or engine that requires Rust to compile, it's all JS.
I see zero reason to use Prisma over TypeORM
As of when I last used it; TypeORMs maintainers are unable to fix bugs nor merge fixes in a timely manner, and has a lot of weird behaviors due to poor API design; if I were doing it today I would just use Drizzle at this point and write the SQL (with nice types).
We have not seen anything yet. Wait for couple of more years when web space is dominated by NextJS "full stack" bros who jumped over all fundamentals because Vercel had great marketing and dEvElOpEr ExPeRiEnCe
Don't you feel like the author compares mongodb speed to postgres, rather than Mongoose to Prisma? Btw, Prisma supports mongodb as well. Maybe it'd be better to compare it using the same database?
We migrated from TypeORM to Prisma, and it feels quite nice. No hacking was involved. Migrations generation is not that great though.
Why did you downgrade to Prisma?
@@sarabwt contrary to what is said here, it was not a downgrade.
My opinion only, prove me wrong:
1. API is awesome, much better than typeorm's.
2. not everyone needs such a performance as described in the article.
3. they released new Prisma version recently, which is like 10x faster than the version described in the article. Primagean also reacted to this release.
Ultimately it feels like, they guy did some weird migration without prior research, faced multiple issues and now need someone to blame, to feel better.
@@L0wPressure I'm joking a bit with the downgrade comment :D
Now afaik as soon as you need joins, Prisma will explode, right? Isn't this a normal use case? Also, what happens when you want to aggregate data for stats? Do you just use a different tool?
New version Addresses the start up time, not the database clusterfuck that they have unless I missed something.
Also I would argue that APIs are the same if not better in favor of TypeORM, because TypeORM also has a query builder.
The only thing that I dislike about TypeORM is how transactions are handled (it makes it hard to reuse the logic), but I also don't see how they could avoid this.
To all of you who want to start using drizzle orm as codeamn or theo or josh said it. It is not mature doesn't have a adapter for the planet scale database with next auth as prisma does nor has as refined docs, unsigned integer or as big of a community .
Right after this video was released planet scale introduced the scaler pro plan where you have unlimited read and write which mitigates the cost issue. starting from $39 it can go up to $999 8 vCPU and 32 GB RAM in AWS which much be sufficient for almost all use cases and vast majority of people. If you need more than this you are already a multi million dollar company and that's that.
Untill Drizzle goes to version 1 I am waiting and will go with prisma and planetscale.
the planet scale rows read part is accurate. i’m ok with this for OLTP apps. their analysis tools are really good and this lets you isolate queries with a ratio of read/returned rows that is skewed towards rows read.
this normally points to an index that is required or where statement that needs to be tweaked and in turn results in faster response time.
I remember those few months when Prisma was the hottest new thing, now EVERYBODY is shitting on it. I fucking love tech 😂
"Kysely" actually just means "Query" in Finnish. I couldn't find any info on if the project was started by Finnish people or not though. So could be a sweet sweet coincidence.
Sami, the creator, is Finnish. :)
As far as I am aware, Turso works in the same way as Planetscale in regards to row reads/writes
The first thing was definitely skill issue.
Unless you tell it to add a bunch of other shit, Prisma is only the 12mb. It doesn't have multiple engines. Just the one.
I've inherited a project using Prisma, and I can attest that it is literally the worst
I've used several ORMs in several langs over the years and Prisma is by far the shittiest I've seen (for SQL at least, it's possible that it is ok for NoSQL, but I feel like they had already NoSQL support and then they slapped on Postgres support, because ppl asked for it, without adding actual SQL support... next to the join-issue mentioned in the article it also does `distinct` in memory and there official reasoning in the docs is: the other DBs we support can't do it, so we implemented it and just that for Postgres as well... instead of, you know, using the actual DB features... I just hate it so much...)
Somehow I agree with every single character in this comment
LOL! I got suggested this as I've been ripping my hair out due to the limit connection pool. If you look up the error you will find a bunch of related issues that have been closed with mostly no resolution. `yarn add pg` here I come!!!
Prisma also doesn't do the `graphql` call to the engine.
And it does a single connection to the query engine not constant new connections.
Turso (your sponsor) has the same kind of pricing model based on black box row reads.
I have used Prisma in a production environment, and while it supports simple lateral joins, it lacks built-in support for other types of joins unless you use raw queries. One downside is that pagination inside a subquery isn't possible, which can be frustrating. In terms of performance, basic queries can be slower. However, Prisma's client and migration tools are incredibly time-saving, ensuring consistent database schemas and simplifying CI/CD processes. Whether Prisma is the right choice depends on the scale of the application. In my opinion, it's not the best fit for large-scale projects.
"we have sed in our build step" I spit my coffee
One former colleague logged DNS queries into a mysql database and broke the (hard) record limit per table. 20M+ records is *nothing* :)
I think Prisma is great for a totally new and fresh project with unproved value for the business. It's great for MVP. You won't spend much time building the database infrastructure. I prefer to use 3rd party services on that stage because I can build something very fast, prove the concept, and start making money. And when the project starts growing I would start to migrate from these tools to something more efficient.
So this decision described in the article was bad right from the start. They need to fire that man who had a final architectural decision because he will inevitably lead them to death in the future. A competent person always tries to choose the right tool for the job.
It might be bad but it was a lesson learnt. You may find that there are some devs in a similar position wanting to do the same thing. Now its been tried and its a flop so thats no longer something to be thought out anymore.
Engineers always say it won’t scale for 20 million users. But the business has to survive till they get there. Cost of development is lesser with such abstract layers.
ORM for CRUD operations and Repository Pattern for other operations with RAW queries or query builder
Remember! You can always go paranoid by default with sequelize :d
Your final comment about "Typesaftey" is really on point. It kinda puts the lie that languages like Python/Typescript/Ruby are so great because developers can be super productive in it.
Prisma saved me a lot of times with many projects, but if I was looking for some small optimized script to throw on lambda, I would probably not use it. Anyway, Prisma allows to run raw queries as well whenever it does not handle something directly, BUT - joins are in fact there, automatic types for each model, and it;s kinda db agnostic, and it is possible to connect multiple dbs easily. Very versatile, and I think these guys just had bad devs.
Bullshit... Utter bullshit
In my experince its really usefull when you have a over engineered db and you dont havw time to rebuild the who product, but then once u get time throw prisma out use csql or something like that
I like Prisma for CRUD apps, however my use case is a better fit I think.
- 10 concurrent users max
- web app (no lambdas)
- Mostly actions on single entities.
- I use raw SQL (via the Prisma API) for my (2~ish) join operations.
I think this article describes mostely a skill issue. There is a manual transaction api. Where Prisma shines is type safety. If you don't value that, then don't use Prisma.
Ayo, Prime and Melkey, the dream team
This is why you use querybuilder when you need specific hard things
All they could have done for migration is to write bunch of SQL to dump data in CSV and let PlanetScale imports those fat CSVs.
Oh, Object relational modelling... This is the most intellidumbsense solution of all. You need to fight two fronts: the actual database capabilities and what the "intelligence" of the ORM wants to do.
JUST. USE. KYSELY.
Run the full schema in prisma - it’s migration engine is unmatched. Generate from AST into Kysely (prisma-kysely) then use that.
No you write as close to SQL as possible.
❤from Kysely.
Postgres + Prisma doesn't have these issues because it doesn't try to emulate foreign keys. We use Prisma in prod with progres, the database has several terabytes of data and more than 1.5 billion rows. Without batching and with postgres autocommit transactions Prisma is able to insert about 17k inserts per second.
The prisma engine did complicate our build step as we chose to build the engine for node-alpine, fortunately cargo makes it fairly easy, our builds are large mostly due to npm package bloat, the prisma engine isn't much extra.
The migrations tool is pretty good. Better than what typeorm provides IMO. Having to run a migration is inconvenient but so far they have been fairly easy and without much trouble.
Interesting that they found this article a few days after Prisma 5.0 came out and solved most of the issues mentioned in this article 😂
Yep. Some of this is definitely old news, the rest just seems like a terrible misunderstanding of what prisma should be used for
prisma still does not do joins in SQL databases, which is mind boggling
@@dzigizord6567 They do, tho? Unless you use the FK emulation.
Prisma v5 addresses graphql calls and joins performance issues, but still ORM`s IMHO are mostly overkill or do some unknown, uncontrollable magic with a performance hit. I personally settled at query builder for typed database response.
I understood from the release note that the new version should increase general performance, but they did not addressed the join issue. Am I right?
We don't read here, we just update and delete, occasionally create.
They actually dropped the graphql communication and went with simply passing a JSON
When Juniors get Senior tasks and then write blog posts about their failure :)
he's the CEO bud
I was awed when we bet our start up on t3 stack.
0 row, planetscale-backed, prisma-powered 2-way joined query took 300ms
Feed in some seeding data, now every request takes 800ms. I thought Next.js was the problem until we just use raw SQL queries.
Prisma DSL is cool, though.
I implemented a module over primsa for multi tenancy because we REALLY wanted to go with prisma. Still the worst mistake to date but also the most fun thing I did.
Funny how the last section is how i build my last project, i use rust to build an api and use prisma for managing db migration
do you use Kysely?
Everytime he says squill I imagine a squirrel lol.
The author should volunteer a code sod submission to TheDailyWTF 😂
Wait, but prisma also lets you write sanitised sql queries ($queryRaw) as well as truly raw ones ($queryRawUnsafe). I was thinking about doing heavy read lifting this way and in other cases don't bother and enjoy the DX... Do raw SQLs through prisma have their own shortcomings too? Though on a second thought types might become a nightmare
3:10 now we can agree that the old java website is a great website
Not using joins (by default) is a great performance idea. This is the first thing you get rid of when you run into DB performance problems. But you still need to be able to do them when you have to. That's the main thing with Prisma - no plan B to fine-tune SQL if you really have to. For example, in laravel eloquent you can start with just using it as ORM, then slowly when you hit performance and edge-cases it's easy to start writing raw sql in places you need and you don't need to dump and rewrite everything at once.
you can do that in prisma too, you can write raw sql in places you need better performance
You have no idea what you’re talking about.
@@sck3570 I don't understand then, what is the issue with Prisma if you can go down to SQL any time you need better performance? As far as I know, any other ORM would have similar issues but as long as the SQL route is available it's considered fine.
@@zen-ventzi-marinov From watching this video and reading comments my understanding is that people want an all or nothing tool(solution) they don't want to write SQL at all. otherwise you can use Prisma for almost 90% of your app/site which speeds up development and in 10% where you need optimization you can write raw sql or use query builders like kysely
Why not using joins is a great performance idea? I think I am not following you... Why should I do at the application level something which the underlying db engine has been specifically developed for?
Everyone shitting on Prisma in the comments based off 1 article that in the very first paragraph displays that they may not know wtf they are talking about, having to rewrite their entire codebase twice due to poor judgement... maybe, MAYBE, Prisma isn't quite that bad and maybe you shouldn't take one scorned developer's word as gospel as I'm quite sure, lots of what he says is blatantly inaccurate, misrepresented, or just examples of him trying to fit a square peg in a round hole.
While it is true about the joins, so many people are taking for granted that a single query is always the right or better performing choice which is simply not the case. An ORM like Prisma is trying to apply generalized best practices. DB level joins can actually be quite expensive and often, generally, you can increase performance by decomposing DB level joins into multiple smaller single-table queries. This is a common and known technique done by many high performance web sites. While there are cases where a single query is better, generally you gain performance through this decomposition. So Prisma decided to adopt this approach as the default first since in general, it increases performance in most situations. They have single query joins on their roadmap for the cases where it's not better performing and it will be added at some point. For now you can absolutely just hand write a query here and there as needed if you are in a situation where single query join is faster and you need that speed...
Also, lots of people saying "this is what JS people put up with"... the Prisma core engine is written in Rust. Generally if you are running into low performing queries with Prisma, you are doing something stupid or misusing it in some way.
Prisma is absolutely fine, in fact it's great, for what it's actually trying to be, which is a super DX friendly general purpose ORM. If YOU do something dumb like try to put it in a serverless function, or use idiotic and unnecessarily complex schema's or build data models that require niche and unique specific DB provider features... you're going to have a bad time and that's not Prisma's fault.
For what prisma does you should use Graphile if you absolutely won't use raw SQL.
It actually generates sane SQL in most cases unlike most ORMs.
7:00 I believe Prisma recommends this for databases that do not support foreign keys.
But what's funny about ORM is what I've seen is people use ORM for very simple scenarios (select * from X where something) but in some more complex situations they... WRITE NATIVE SQL QUERIES. Shouldn't it be the opposite?! I sort of understand as whenever I work with backend (not very often tho) and I have to get something more complex from the db I much rather use native SQL than trying to figure out how the engine is going to fetch my data, what is the syntax, how it relates to transactions, etc... Or what people do is they perform the logic in the code which is a performance hell.
I often start with the django orm, output it's sql and optimize from there, if someone asks me for an sql query, which is probably the usecase you mean.
However, if the app is built around the orm, usually the choices made are catering the orm's strengths or to the program business logic. The problem is, that there is a certain amount of complexity, where an advanced sql query just becomes easier to write, than the ORM version, but it is easier to write it as an afterthought, since the code creating that complexity is usually business logic related. A comparison would be physics equations for different systems put into one giant equation often allows you to compress it for a certain problem field, by eliminating some of the variables, but the underlying hidden principles of all the equations that created it in theory are still valid.
This is why even if I optimize a query into an SQL, I keep the pure ORM Version around as long as it is maintainable, as it shows where the complex joins and nested selects came from.
Not using an ORM or query builder, how would you do the following?
You need to get a list of records with several fields, and to each of those records you need to get again a small list of records from another table.
You could do that with a JOIN, but then it will multiply the columns of the records from the first table and you need to write logic that plucks out the, lets call them parent records, and group their nested records to them.
Another option would be to do 2 SELECTs. First you select the first set of records with whatever query condition you use, then you take the IDs of those records and use them on the foreign keys of the nested records in a second SELECT (instead of the LEFT JOIN), and then you again have to group the nested records to their parent records. But it's clearer what fields belong to what table, you don't need to handle name conflicts and don't need to handle the NULL case of the LEFT JOIN etc. So it's simpler to think about.
I think ORMs usually do it the 2nd way? If I have to do it manually I do it the 2nd way. It is 2 SELECTs, not 1+N SELECTs. When people don't use ORMs they tend to implement this in 1+N SELECTs, because that is the most straight forward. In that case ORMs improve the performance on this quite a bit.
go does have its own version of `any`. You can cast anything to `interface {}`.
Some of the issues are completely solved in Prisma 5.0
IMO, when you have to create a data access layer anyways, an ORM complicates things further.
This is why I dont go overboard with abstractions around databases.
You are fooling yourself if you think that a migration can ever be easy.
Finally somebody saying it loud!
This happens when you dont know your tools and want to use a gold hammer to hit a nail, at some point you have to admit this whole endeavor was your fault.
one of the entertaining video i have ever watched
used Prisma 2/3 in a job, it was "fine" it had it's slip ups but it was usable, but MAN when you create/edit/delete a migration and you are working with a team in git it is HELL to merge branches and run migrations.
I've written and worked on ORM. There's no such thing as "Next-gen ORM". That usually means the developers didn't bother to study and understand all of the sharp edges of ORM. If the application has to integrate a dozen databases, using an ORM to read data to a normalized model "can" save you a ton of time. There's a big gotcha, you have to know what you're doing or it becomes a nightmare.
If your data model doesn't need polymorphic queries, using an ORM is questionable and probably over kill.
the docs from prisma tells me the developers don't understand RDBMS or how to compile higher level object mapping to efficient SQL queries that runs on the database server.
I have ran into the same problem with prisma not using joins. However, prisma has a feature that you can enable "relational-joins" and then it works as expected
don't mind them
SQLAlchemy has been the only ORM that I actually appreciated.
i work with a client who has a prisma psql setup, the previous devs fucked up and started to write their own migrations apart from prisma, now the prisma migrations wont even work and if we try to infer the db and then migrate we would loose all the data as the migrations are not synced. So now the workflow is
prisma for typescript inferences
custom migrations for sql
The S is part of Postgres. SQL was added later