Migration Lesson: Don't Use Prisma | Prime Reacts
HTML-код
- Опубликовано: 16 июл 2023
- Recorded live on twitch, GET IN
/ theprimeagen
Article: codedamn.com/news/product/don...
Mel, "The Milk", key: / melkeydev
MY MAIN YT CHANNEL: Has well edited engineering videos
/ theprimeagen
Discord
/ discord
Have something for me to read or react to?: / theprimeagenreact 
Наука
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
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 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!
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
"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
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.
glad to see Melkey on your stream, saw his videos about his journey with Neovim, he seems like a chill guy
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).
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?
@@linker123
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 😅.
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"
So glad to watch this video as I build my nextjs application with prisma
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.
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 🤷♂️
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.
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.
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.
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.
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.
Whatever the topic, thanks Prime for reinvigorating my interest in development. Configured Neovim on my Kali yesterday. Hoping to get blazingly fast ⚡
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.
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.
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
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.
It's really weird. Because the ORM advocates will claim it lets you connect to a dozen different databases. But we never actually want to do that.
And what's the benefit of not learning SQL if you have to learn their proprietary replacement for it? Which becomes far worse as soon as you have to debug anything, you have to learn the guts of how / why their proprietary replacement translates to SQL.
Yeah we just need an Sql builder with typesafe language support and we can stop there
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.
It's that 80% of software that's largely invisible. Most software is endless business logic and enterprise apps written by contractors of varying but usually limited skill levels. ORMs and other enterprisey frameworks (dependency injection, etc) are a reasonable fit for those vast sprawling messes.
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.
As far as I am aware, Turso works in the same way as Planetscale in regards to row reads/writes
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
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
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
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.
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.
ORM for CRUD operations and Repository Pattern for other operations with RAW queries or query builder
having interest in programming with limited knowledge, so writing raw sql like in nextjs project. do you need a library or anything to do that?
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.
i use arch, btw
Kali here
@@manaspatil3996 isn't kali deprecated ?
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.
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.
Is this article old? I ran an sql audit on one of the joins in postgres + prisma, and the join happens in the db
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.
Turso (your sponsor) has the same kind of pricing model based on black box row reads.
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.
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 am relatively new in backend coming from frontend, so I like orms over sql builders and so on. And I really wonder how orm migration tool can be called good when it is only about schema migration (opposite to data migration) and there is no option to write custom migrations but with sql… isn’t it the whole point of orm to avoid sql? Typeorm/sequalize both allow you to write migrations with code, why not to do it with prisma?
Imagine using a VC backed ORM...
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.
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.
In PostgreSQL and SQLite you can get the entire record back when doing an INSERT, UPDATE, or DELETE by adding RETURNING * at the end (I think MySQL can also do that with a different syntax? Don't know). The cool thing about this is that it is atomic without needing a transaction (like how you would need with INSERT/UPDATE+SELECT). But still, other ORMs give you that as an *option* and not just force it always (I don't see a way to not get the updated record in Prisma). Is there?
Side note: There is a "library" engine option for Prisma where the backend isn't run as an extra server(!?!?!), but just loaded as a native library. However, that option had segmentation faults in certain versions if Prisma+NodeJS because Prisma linked a different version OpenSSL as NodeJS did and Prisma didn't use appropriate flags to dlopen(). It's fixed now... but I saw a similar bug opened for very specific platforms (ARM IIRC).
Fun fact: in Drizzle ORM, after running INSERT, you don't get the inserted row by default, you need to add .returning()
@@mieszkogulinski168 Yes, many ORMs do that. For some it's .insertGet() or similar.
"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. :)
Hey Prime! Which database Netflix is using for Analytics? Is it Apache Druid?
What would you use in Node to connect to postgres and run raw sql ? pg, postgre.js or something else ? What would you use for migrations and seeding ?
Also another thing I don’t get about prisma is why do I need to write schema so than I can generate my db/types?
Like I do get the schema value in GraphQL since it has runtime impact e.g. it links up all the separate scalars and directives that you written in code
But what does prisma schema do in runtime? Nothing, so why not to generate (virtually) schema from code like typeorm does?
At first it was funny to me to work with prisma and gql since you basically run two codegens and than map one types to another. But than I understood that prisma schema alongside with its codegen is just redundant 🤷♂️
7:00 I believe Prisma recommends this for databases that do not support foreign keys.
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
Why not use docker image backed AWS lambda functions to overcome the AWS lambda size limit?
I think that it was obvious the CUD in this context means create, update and delete, so a synonym of mutations commands...
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🤔
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.
Ayo, Prime and Melkey, the dream team
IMO, when you have to create a data access layer anyways, an ORM complicates things further.
go does have its own version of `any`. You can cast anything to `interface {}`.
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 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.
They actually dropped the graphql communication and went with simply passing a JSON
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
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.
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'm a Post Gres Q L guy
Postgressqueal
same here ✋
Okay hold up, so this shit with the no joins in prisma keeps coming up. I tried using an include and turned on verbose logging and tada- I see a join in the SQL logs. What am I missing then, can you give me a source on this idea of no joins in prisma 10:40 or is this a planet scale / mongo only thing? Seems to work as expected with postgres for me. Joins and all.
I've been learning sql on datacamp, I don't think I'll ever go back to an orm. My only concern is making my db secure
This is why I love the Go community. They love writing SQL.
*love writing SQLi
here fixed it for ya
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!!!
So what should we use? Any better alternatives here?
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 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).
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.
When Juniors get Senior tasks and then write blog posts about their failure :)
he's the CEO bud
@@night23412 Still accurate.
rule1 about databases, always use SQL until you are unable to use it anymore.
Man I was just implementing Prisma on a new project. Guess I'm switching before shit hits the fan lol
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.
I love the prisma schema and migrations. this sounds like I will be doing something similar to their end result
Kysely-Prisma. It’s a library and it will let you use prisma at what it’s good at. Schema management.
"we have sed in our build step" I spit my coffee
i can see its use in an org that doesn’t have a data lake and wants data lake lite but this seems to not be very friendly for an operational use case. more for big data and modeling
I remember those few months when Prisma was the hottest new thing, now EVERYBODY is shitting on it. I fucking love tech 😂
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?
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.
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.
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
One former colleague logged DNS queries into a mysql database and broke the (hard) record limit per table. 20M+ records is *nothing* :)
I never understood why people use ORM. Raw SQL is simple enough. Tried Prisma in T3 stack... gave up as soon as I realized how joins are done. Ugh. Trying Drizzle now as it produces a single SQL query there. Why am I teying ORMs even if I love raw SQL? 1) migrations 2) type "safety" / IDE autocompletion
The term "mongoose orm" might be a sign they don't know what they are doing 😂
Using Prisma is the second sign
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.
the whole article seems ambiqious. they clearly mentioned in the prisma doc that relation type foreignkey is not supported with planetscale, so you have to use prisma client emulate.
We don't read here, we just update and delete, occasionally create.
Y'all should've used Prisma v1 where you used GraphQL to query your DB. My first company was using Prisma v1 and it was hell to use and we never fully migrated off it while I was there.
Finally somebody saying it loud!
I don't even know how to use any of the ORMs. I mainly write backend in Go and Rust, and I always use raw SQL strings, and I thought that is the majority of the people. Seems like I am wrong.
7:31 how do you spell the planetscale alternative he mentions?
turso.tech
@@ThePrimeTimeagen thx
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.
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.