Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We migrated to SQL. Our biggest learning? Don't use Prisma

codedamn.com

Post reply on HN
Print view View original

11–20 of 104 posts

Re: We migrated to SQL. Our biggest learning? Don't use Prisma

#11

Post by leononame »

> Further inspecting, we discovered that your code never makes the actual DB call. Your Prisma code performs a GraphQL network request (?) to the Prisma Rust query engine, which then translates your request into actual DB calls.

I haven't used Prisma past fiddling with it a bit and this is extremely surprising to me. I guess it stems from Prisma's history but to me it's really off-putting and I don't think I'd use it in production. Not that I've got too much against graphql, but shipping a SQL ORM with this in between feels unnecessary imo.

> Every new insert via Prisma opened a database-level transaction (?).

Surely there must be some way to control this? Can a seasoned Prisma veteran here chime in?

Re: We migrated to SQL. Our biggest learning? Don't use Prisma

#13

Post by mijoharas »

> Weird pricing: PlanetScale prices you on row-reads and row-writes per month. What is weird is that row-reads is something that nobody controls. It is the SQL query planner which determines the query plan, which results in how many rows you’re reading internally. Remember that row-reads are not row returns. You can write a wrong query that returns 0 rows but can still do a full table scan of 1M rows and PlanetScale will charge you for it.

Followed by:

> AWS Aurora Serverless v2 Postgres

I hope they don't get an unpleasant shock when they see the IO pricing on their aurora bill (it's per block read/write rather than per row, but that's proportional in the random access case, and you're also beholden to the query planner, so basically the same).

Re: We migrated to SQL. Our biggest learning? Don't use Prisma

#14

Post by spion »

I would modify this to "Don't use Prisma if you're using serverless"

With actual servers and prisma running close to the underlying database, it should be much better.

Regarding using joins, that's not necessarily the best choice either. When using simple flat joins naively, you get a lot of repetition for the more toplevel nodes of the join tree, which eventually adds up to a lot of network traffic and allocation. (TypeORM tends to have this issue, for example)

What you really want are joins that build the JSON on the server. In MSSQL this can be as simple as `FOR JSON`. In Postgres it can get... a bit more involved - Drizzle can do it: https://github.com/drizzle-team/drizzle-orm/releases#:~:text...

This is probably okay, although I do wonder what happens after you hit the compute limits of your database server (i.e. scenarios when you don't use things like planetscale). In those cases (again, non-serverless), Prisma's choice might work better as long as it runs close to the DB. It will still likely be slower than the fancy JSON join, but probably need fewer database resources (depending on how its done).

Re: We migrated to SQL. Our biggest learning? Don't use Prisma

#17

Post by lazy_moderator1 »

francislavoie wrote: post #10
leononame wrote: post #6

Earlier quoted context omitted.

That is a strange take tbh. For me, ensuring data integrity at the database level gives me a lot of peace of mind. Migrations are actually safer and easier because the database will complain if something's wrong, and at the application level I can do an opportunistic insert and if the FK is not valid, I'll get an error, which is really nice if I don't need the related record at all so I don't need to fetch it first.

With foreign keys, I can't use https://github.com/github/gh-ost for zero downtime migrations. Instant deal breaker, because schema changes are inevitable given business evolution.

you can always use views + triggers for a zero downtime migration, this is a non-issue

Re: We migrated to SQL. Our biggest learning? Don't use Prisma

#18

Post by freddref »

CharlieDigital wrote: post #5

Maybe it's just me, but if I'm doing any sort of migration like this, the first thing I do is create a small PoC that emulates the full process to iron out any weird, obvious edge cases and ergonomic issues. In this case, hitting the Lambda size limit would have been the first red flag that triggered a re-evaluation of the whole approach. Understanding the limits of the stack (Prisma + PlanetScale) would have been ob…

I am amazed at the number of engineers I've worked with who jump straight into the full implementation. I've done it myself on a few occasions thinking "how hard can this be".

Building the "toy" first is great.

In my experience, about one third of the time the toy is all you need, you can stop there, and what you were going to build fully would be over-engineering. About one third of the time, building the toy tells you you're taking the unworkable approach as you mentioned. And the other third of the time you can extend the toy.

Re: We migrated to SQL. Our biggest learning? Don't use Prisma

#19

Post by mijoharas »

francislavoie wrote: post #3

IMO foreign key constraints are bad; they make migrations and maintenance a nightmare. It's better to have application-level processes in place to ensure data consistency. I don't agree that "no foreign key support" is a good reason to not use a particular solution. Pricing concerns are valid though.

How do you find fkey constraints making maintenance or migrations a nightmare? I've always found them reasonably pleasant to use on postgres.

Re: We migrated to SQL. Our biggest learning? Don't use Prisma

#20

Post by mijoharas »

francislavoie wrote: post #10
leononame wrote: post #6

Earlier quoted context omitted.

That is a strange take tbh. For me, ensuring data integrity at the database level gives me a lot of peace of mind. Migrations are actually safer and easier because the database will complain if something's wrong, and at the application level I can do an opportunistic insert and if the FK is not valid, I'll get an error, which is really nice if I don't need the related record at all so I don't need to fetch it first.

With foreign keys, I can't use https://github.com/github/gh-ost for zero downtime migrations. Instant deal breaker, because schema changes are inevitable given business evolution.

On postgres at least you can split creating the foreign key, and validating it into separate steps and ensure you have zero downtime Migrations. No idea about MySQL though, so maybe it's different there.
Post reply on HN