I always enjoy hearing about the battles people are willing to endure, just to avoid writing SQL queries 😂 honestly, raw SQL is FINE

@chevi_to

11 ай бұрын

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.

@nekogami87

11 ай бұрын

@@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.

@greentea2430

11 ай бұрын

​@@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.

@alexandrep4913

11 ай бұрын

​@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.

@bluecup25

11 ай бұрын

@@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…

@astronemir

10 ай бұрын

Exactly.

@raulsanchez8460

10 ай бұрын

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

@someguyO2W

4 ай бұрын

This!

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.

"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.

@dubble_g

Ай бұрын

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

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".

@mbunkus

11 ай бұрын

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.

@srujangurram

11 ай бұрын

@@mbunkus This makes sense on planet scale pricing

@ko-Daegu

11 ай бұрын

is writing a raw sql worse than using somethign like prisma or orm security wise ?

@mbunkus

10 ай бұрын

@@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.

@MykolaTheVaultDweller

10 ай бұрын

Wow, returning id was eye opening for me

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.

@philheathslegalteam

11 ай бұрын

Yes, but the join thing is different. That’s just how prisma works for now.

@Danefrak

7 ай бұрын

How do you not allow foreign key

@RikunoFilms

6 ай бұрын

@@Danefrak I said foreign keys but I meant foreign key constraints when speaking to vitess not allowing it.

@attilafuto5611

6 ай бұрын

@@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"

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.

@firstlast-tf3fq

11 ай бұрын

Because they're used to the absolutely horrendous performance of their language

@IvanRandomDude

11 ай бұрын

But think about dEvElOpEr eXpErIeNcE

@geni15o

11 ай бұрын

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.

@mormantu8561

11 ай бұрын

​@@geni15oForgive my language, but that is utter horseshit. ORMs exist and are popular in every modern language.

@geni15o

11 ай бұрын

@@mormantu8561 I agree 🤷‍♂️

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 😂.

@ThePrimeTimeagen

11 ай бұрын

did the article touch a special place in your trauma?

@enzocecillon1452

11 ай бұрын

@@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 🤷‍♂️.

@linker123

10 ай бұрын

@@enzocecillon1452 What is the reason behind your switch? Performance? Structured data? or something else?

@enzocecillon1452

10 ай бұрын

​@@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 😅.

This is a master class in using tools the wrong way. Would love to chat about the *right* way if y’all are interested ;)

@Notoriousjunior374

11 ай бұрын

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.

@tonyhart2744

11 ай бұрын

cope more js dev, your tool sucks t3 stack is heresy

@denysdorokhov6355

11 ай бұрын

@@Notoriousjunior374 ​ Who do you mean by "we"? Speak for yourself, I have nothing against one more video on the topic, this time from Theo.

@davidleslie5415

10 ай бұрын

@t3dotgg please do a follow up video

@richardwatts20

9 ай бұрын

Wow the egos really need to check themselves

glad to see Melkey on your stream, saw his videos about his journey with Neovim, he seems like a chill guy

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.

So glad to watch this video as I build my nextjs application with prisma

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.

@DragonRaider5

11 ай бұрын

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.

@lankymoose1831

11 ай бұрын

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.

@lankymoose1831

11 ай бұрын

I take it back, looks like they've fixed that since about a month ago 😅

@philheathslegalteam

11 ай бұрын

@@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.

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.

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.

@johnhershberg5915

11 ай бұрын

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

11 ай бұрын

Dapper?

@johnhershberg5915

11 ай бұрын

@@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

@RaZziaN1

11 ай бұрын

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.

@amirhosseinahmadi3706

11 ай бұрын

​@@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.

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.

@igalklebanov921

11 ай бұрын

❤from Kysely.

Imagine using a VC backed ORM...

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

11 ай бұрын

this mentality is why we have a lot of SQL injections until this day

@thfsilvab

10 ай бұрын

@@ko-Daegu yeah, definitely ORMs are the only possible fix for SQL injection

@lucass8119

10 ай бұрын

@@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.

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.

Entity framework core seems like a dream to use compared to this

@amirhosseinahmadi3706

11 ай бұрын

EF Core is the best ORM I have seen in any language. And I've tried many.

@arjix8738

11 ай бұрын

@@amirhosseinahmadi3706 and for raw sql dapper is a good complimentary to the C# stack

@kabal911

11 ай бұрын

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

@admintkm

11 ай бұрын

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.

Whatever the topic, thanks Prime for reinvigorating my interest in development. Configured Neovim on my Kali yesterday. Hoping to get blazingly fast ⚡

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?

@sburton84

11 ай бұрын

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?

@imakhlaqXD

11 ай бұрын

@@sburton84 even db that support foreign keys. It pull tons of db rows and join using "RUST ENGINE"

@Vissie2

11 ай бұрын

@@sburton84 It simply never does.

@kabal911

11 ай бұрын

@@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

@Notoriousjunior374

11 ай бұрын

@@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.

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).

@igalklebanov921

11 ай бұрын

❤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

I think that it was obvious the CUD in this context means create, update and delete, so a synonym of mutations commands...

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.

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.

@philheathslegalteam

11 ай бұрын

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.

@dylan-roberts

11 ай бұрын

@@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.

@jkf16m96

11 ай бұрын

​@@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

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.

@doyouwantsli9680

4 ай бұрын

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

@manaspatil3996

11 ай бұрын

Kali here

@RaZziaN1

11 ай бұрын

@@manaspatil3996 isn't kali deprecated ?

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.

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.

@tonyhart2744

11 ай бұрын

Yeah we just need an Sql builder with typesafe language support and we can stop there

ORM for CRUD operations and Repository Pattern for other operations with RAW queries or query builder

As far as I am aware, Turso works in the same way as Planetscale in regards to row reads/writes

Ayo, Prime and Melkey, the dream team

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

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 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

11 ай бұрын

Why did you downgrade to Prisma?

@L0wPressure

11 ай бұрын

@@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.

@sarabwt

11 ай бұрын

@@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.

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.

@ThePrimeTimeagen

11 ай бұрын

solid take

@sck3570

11 ай бұрын

Do you work with Prisma in production, and has using Kysely with generated tables via prisma created any issues?

@riyaadhabrahams

11 ай бұрын

​@@sck3570 Yes. Currently running fine on a site with 100K users a month.

@thegrumpydeveloper

11 ай бұрын

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?

@philheathslegalteam

11 ай бұрын

@@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.

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

"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.

@igalklebanov921

11 ай бұрын

Sami, the creator, is Finnish. :)

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.

@electrolyteorb

7 ай бұрын

These soyboys will do anything but Learn basic SQL and use a query builder

@adeidara9955

4 ай бұрын

@@electrolyteorb🤔

Is this article old? I ran an sql audit on one of the joins in postgres + prisma, and the join happens in the db

Turso (your sponsor) has the same kind of pricing model based on black box row reads.

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?

rule1 about databases, always use SQL until you are unable to use it anymore.

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

Hey Prime! Which database Netflix is using for Analytics? Is it Apache Druid?

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?

go does have its own version of `any`. You can cast anything to `interface {}`.

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 ?

I'm a Post Gres Q L guy

@ficolas2

11 ай бұрын

Postgressqueal

@abanoubha

11 ай бұрын

same here ✋

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 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

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 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.

One former colleague logged DNS queries into a mysql database and broke the (hard) record limit per table. 20M+ records is *nothing* :)

This is why you use querybuilder when you need specific hard things

We don't read here, we just update and delete, occasionally create.

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.

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 🤷‍♂️

This is why I love the Go community. They love writing SQL.

@ko-Daegu

11 ай бұрын

*love writing SQLi here fixed it for ya

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.

@jorgehabib3933

11 ай бұрын

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?

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.

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.

"we have sed in our build step" I spit my coffee

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.

@alexischicoine2072

11 ай бұрын

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.

I love the prisma schema and migrations. this sounds like I will be doing something similar to their end result

@philheathslegalteam

11 ай бұрын

Kysely-Prisma. It’s a library and it will let you use prisma at what it’s good at. Schema management.

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 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

IMO, when you have to create a data access layer anyways, an ORM complicates things further.

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.

So what should we use? Any better alternatives here?

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

@igalklebanov921

11 ай бұрын

do you use 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.

Why not use docker image backed AWS lambda functions to overcome the AWS lambda size limit?

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.

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 😂

@dogmonster2

10 ай бұрын

Yep. Some of this is definitely old news, the rest just seems like a terrible misunderstanding of what prisma should be used for

@dzigizord6567

10 ай бұрын

prisma still does not do joins in SQL databases, which is mind boggling

@ruoibeishi8356

10 ай бұрын

@@dzigizord6567 They do, tho? Unless you use the FK emulation.

7:00 I believe Prisma recommends this for databases that do not support foreign keys.

They actually dropped the graphql communication and went with simply passing a JSON

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.

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.

@RogerValor

10 ай бұрын

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.

3:10 now we can agree that the old java website is a great website

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.

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).

@mieszkogulinski168

5 ай бұрын

Fun fact: in Drizzle ORM, after running INSERT, you don't get the inserted row by default, you need to add .returning()

@blenderpanzi

5 ай бұрын

@@mieszkogulinski168 Yes, many ORMs do that. For some it's .insertGet() or similar.

Man I was just implementing Prisma on a new project. Guess I'm switching before shit hits the fan lol

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.

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

@osdiab

6 ай бұрын

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).

Finally somebody saying it loud!

Some of the issues are completely solved in Prisma 5.0

I used prisma a while back and bailed after a while as I didn't enjoy the seemingly unnecessary layer of abstraction.

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

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.

Everytime he says squill I imagine a squirrel lol.

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

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.

Remember! You can always go paranoid by default with sequelize :d

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.