25 ms·

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

93 points by mariuz 3y ago

lroal 3y ago
You should really try out RDB, an ORM that I've been working on since 2014. https://rdbjs.org https://rdbjs.org It has a very small fooprint - well suited for Amazon lambda. We now support client over HTTP (in a safe manner), making it accessible in-browser. Key Features: No code generation required Full intellisense, even when mapping tables and relations Powerful filtering - with any, all, none at any level deep Supports JavaScript and TypeScript ESM and CommonJS compatible Succint and concise syntax Works over http
RedShift1 3y ago
Prisma is an offshoot of graphql.cool, which was a "graphql API as a service". It seems to me that was a potentially good and viable business. However they stopped graphql cool and released Prisma, where there's no business model (who's gonna pay for an ORM?). I'm still left wondering why they did that?
- Addono 3y ago
  I presume the services around it, e.g. their products under the Data Platform: - https://www.prisma.io/data-platform/accelerate https://www.prisma.io/data-platform/accelerate and: - https://www.prisma.io/data-platform/proxy https://www.prisma.io/data-platform/proxy Whether it's a viable business is to be seen though. I've been a happy user of Prisma for a while, and so far we've not been running into severe limitations nor need for their additional services. Who knows what the future will bring though.
francislavoie 3y ago
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.
- Closi 3y ago
  Depends how important consistency is - application-level only will mean it can potentially silently fail if there is a bug.
- leononame 3y ago
  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.
  - francislavoie 3y ago
    With foreign keys, I can't use https://github.com/github/gh-ost https://github.com/github/gh-ost for zero downtime migrations. Instant deal breaker, because schema changes are inevitable given business evolution.
    - lazy_moderator1 3y ago
      you can always use views + triggers for a zero downtime migration, this is a non-issue
      - oblio 3y ago
        Can you expand on this? I'm curious about the steps.
        nijave 3y ago
        Sounds like it's basically explained in the gh-ost readme https://github.com/github/gh-ost#how https://github.com/github/gh-ost#how I think it amounts to "use views to decouple access to the table with a fixed interface" and "use triggers for migrating data between tables"
CharlieDigital 3y ago
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 obvious from a few simple toy examples. Seems like this team dove headfirst without even building a simple toy to determine if the choice was actually viable.
- freddref 3y ago
  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.
  - CharlieDigital 3y ago
    I have a directory on my drive called `sandbox` where I basically throw together small toys for anything with some unknown complexity. Anything from how an ORM might model a specific type of relationship (throw together a basic replica with a Docker compose file) to replicating the deployment model (poking AWS Copilot, for example) to testing out some tooling flow (e.g. local build process change). The main thing with a toy model is speed. You can build/deploy/test with a smaller scope and progressively scale it up some reasonable scale of the full thing (whatever you're testing for) and you can iterate your testing faster. But many times, the key issues show up quite early in the process of grokking the toy model.
    - w0m 3y ago
      'scratch' for me. Everything from quick regex tests to full library rewrites tend to start there.
Zealotux 3y ago
Drizzle looks like an interesting alternative to Prisma https://orm.drizzle.team https://orm.drizzle.team You get TS support, tooling, and a very thin layer on top of SQL so little performance hit.
- Jnr 3y ago
  +1 for drizzle. And writing queries with joins is possible. :)
- sccxy 3y ago
  Drizzle is much better in queries, but migrations fail very often. And a lot of mysql bugs are reported in GitHub and waiting for fixes.
leononame 3y ago
> 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?
- jamincan 3y ago
  They have a number of tools to consolidate queries into a single transaction (createMany in this case), or, you can control transactions directly using their $transaction api.
thefilmore 3y ago
I tend to reach for TypeORM. It supports every database under the sun.
- c0n5pir4cy 3y ago
  TypeORM is great and all - but it has its own quirks too and it's not a silver bullet. In their case they would also maybe run into some issues as TypeORM has some initialization cost (building a model in memory when it first connects to the DB etc), some object mapping costs and other complexities (particularly around efficient query generation). While there is nothing stopping it from working in a Lambda this would increase initialization time and cost which both matter since this is for a GraphQL API. I think the solution they've selected (Kysely) is probably a better fit.
mijoharas 3y ago
> 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).
- reedf1 3y ago
  What do they think is executing the table scan? Do they think that the underlying hardware should be free?
- eightnoteight 3y ago
  aurora serverless v2 now supports io optimized mode. which increases the pricing for instances by constant amount but makes the io pricing to 0 https://aws.amazon.com/about-aws/whats-new/2023/05/amazon-aurora-i-o-optimized/ https://aws.amazon.com/about-aws/whats-new/2023/05/amazon-au...
  - mijoharas 3y ago
    Oh, very cool!
- naiv 3y ago
  I also think that this is the old Planetscale pricing. The new one has unlimited reads (https://planetscale.com/pricing https://planetscale.com/pricing)
- patmorgan23 3y ago
  Row-reads sounds like a perfectly acceptable metric to use for billing. It's basically changing based on disk io. So what if the result of your query is 0, the server has to do a bunch of work to figure out the answer is 0.
spion 3y ago
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=Improved%20Relational%20Queries%20Permormance%20and%20Read%20Usage 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).
- jasfi 3y ago
  Agreed, if you have some sort of limit, then it seems that AWS Lambda has a limitation you could hit with Prisma. Prisma has some support for joins with relations: https://www.prisma.io/docs/concepts/components/prisma-schema/relations https://www.prisma.io/docs/concepts/components/prisma-schema... Also, you need to specifically create a transaction to encompass your Prisma calls to limit them to a transaction. This is the default for PostgreSQL as well.
- estebarb 3y ago
  The joins should be done inside the DB, not in an external server. Any SQL DB should be able to do that.
  - spion 3y ago
    If this is not done carefully, it can also be slow in some situations, even with the right indeces in place. Lets say we have the following slightly contrieved case: organizaiton(id, name, description) -> users(id, name) -> posts(id, content) -> comments(id, userId, content) We want to get one organization with all its users. Lets imagine our org has 100 users, each of which has 100 posts on average, each of which has 100 comments on average. In a naive, flat join, we'll be repeating the organization description column 1 million times, and each post's content about 100 times on average. This can still be problematic without joins, as sending 1 million comment IDs in a query to the DB isn't going to work, so even the multiple queries version will need to do better than that
oblio 3y ago
"We migrated to SQL. Our biggest <<lesson>>? Don't use Prisma". Old man shouting at the literal IT clouds.
- OJFord 3y ago
  Let's take it offline for discussion in the breakout room, ok?
  - oblio 3y ago
    Can you please clarify your ask?
switch007 3y ago
I’ve been tempted to ditch django for typescript in the backend but the sql and migrations story looks a bit … confusing and underwhelming? I feel like typeorm should cover 99% of cases, due to its feature list and age (7 ish years…ancient!), but then you see people often recommending other projects (eg pg_typed). Do people just string things together? Make use of Liquibase? In 2021 it seemed the TypeORM project was badly maintained and quite buggy - https://news.ycombinator.com/item?id=26888369 https://news.ycombinator.com/item?id=26888369 . Is that still the case?
- pritambarhate 3y ago
  We have been using TypeORM for last 2 years now. Found it quite suitable for our use case. Didn't come across any big issue. But the projects we did with it where not very high scale. Less than 5 requests per second. So a single server affair.
porbelm 3y ago
People needing to query huge amounts of data discovering technology that's been developed over the past 45 years for querying huge amounts of data efficiently; news at 11. Seems all these shops starting out with MongoDB or the like always need to do a huge, complicated migration to a /proper/ optimized data store, when they could have started with a sane design in the first place...
- CharlieDigital 3y ago
  > People needing to query huge amounts of data discovering technology that's been developed over the past 45 years for querying huge amounts of data efficiently; news at 11 I think a lot of folks are quick to discount just how scalable a database like Postgres or SQL Server can be (not to mention battle tested). Maybe its because of a lack of DBMS experience as we've move more and more from owning database servers to consuming *-as-a-service. Maybe it's because everyone thinks that they'll have Facebook scale traffic and concerns so they immediately gravitate to technologies designed to solve problems at a different order of magnitude.
  - twh270 3y ago
    Also, SQL is sCaRy. It's "hard to learn", and "we'll need experts to tune the database queries", and "schema migrations are a nightmare", etc. Whereas MongoDB is "simple".
- DonnyV 3y ago
  The problem is when people pick Mongodb thinking they can use it like a SQL database. I've been using Mongodb since v1.6. You have to use it differently then a SQL database. The way you design your schema and code needs to change also. Its just a different way of thinking about a database. When you use Mongodb you treat the db like a dumb black box. You design in data integrity into your code. One of the big reasons you use Mongodb is because you don't need an ORM. Your POCOs are the tables. People that say they lost data while using Mongodb. Were using Mongodb wrong.
- pleoxy 3y ago
  > Seems all these shops starting out with MongoDB or the like always need to do a huge complicated migration First thing to note, I couldn't find any reasoning for the change in the original post. This makes the speculating on it rather pointless, but clearly you are able to fill in the details quite easily somehow. As a counter point. Last four businesses I've worked at all ran on MongoDB. More happenstance than something I've looked for. They are all relatively small businesses and all ran fine, the biggest was ingesting 1-10GB/day of new data from their IOT fleet. So this perspective that everything mongo is fundamentally broken and plain bad is pretty foreign to my real world experience. These shops all liked what they were doing and there was zero talk of scaling issues or changing out the database.
Exuma 3y ago
Wow just one look at this issue was all I needed to see. https://github.com/prisma/prisma/discussions/12715 https://github.com/prisma/prisma/discussions/12715 “Please open an issue if something really is unusable or breaks in any way so we have these concrete cases recorded. In our knowledge Prisma works just fine and returns the data that was requested, just using a different method.” Good lord
- superwalker 3y ago
  I substantially regret using Prisma in production for this and very many other reasons. At least the underlying data is well modeled, just need to rip out the JS library and replace it… but with what?
  - ohthatsnotright 3y ago
    I found MikroORM [0] to be quite reasonable if you're in the TS ecosystem already. It was also easy to do custom, raw queries, and really just felt like it wasn't in the way. [0] https://mikro-orm.io/ https://mikro-orm.io/
adamzochowski 3y ago
> 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. This is expected pricing. The pricing is based on the load on the db, not based on results. Usually there are two ways working with this: DB supports showing estimated query plan , which explains how query would be ran without running it. Allowing to capture any missing indexes or filtered indexes. DB allows only querying against indexes (this is usually done by no-sql dbs which have flexibility to deny bad queries).
Tade0 3y ago
I kind of want to say "y-yeah" regarding Prisma, because all this information (especially the fact that it creates a transaction every time and downloads those engines) is either available in the documentation or visible upon install/build - you can actually make it point to specific executables instead of downloading each time using environment variables but again - it's in the docs. I picked Prisma because I'm not actually a backend developer and the tradeoffs were okay for my use case. I wish performance was better, but a few indexes here and there went a long way. The only surprising bit for me was peak memory usage - it comes in bursts and can get to hundreds of MB when doing even a simple query. The queries themselves are also kind of out there in terms of complexity but so far I haven't found a reason to rewrite them in raw SQL, since that isn't where our performance bottlenecks are at the moment. The lack of rollbacks is one legitimate pain point and I wish they addressed it.
- mattacular 3y ago
  > The lack of rollbacks Could you elaborate on this part?
  - Tade0 3y ago
    Here's a comment from their team about this: https://github.com/prisma/prisma/discussions/4617#discussioncomment-215868 https://github.com/prisma/prisma/discussions/4617#discussion... Tl;dr it's a design decision, not everyone (including me) agrees.
    - superwalker 3y ago
      [flagged]
- DanielHB 3y ago
  I added prisma to a server I have and I was surprised it was running out of memory when using "prisma migrate deploy" (only migrations, not actually just running prisma client). The server was configured to use only 512mb of RAM though. It seems prisma migrate needs quite a bit of RAM.
danjac 3y ago
From the article there is no detail of what problem they were solving, just "we used tech stack A, we ran into issues, so we went with tech stack B". So I have no idea whether MongoDB was a good choice to begin with, or not, or whether the problem needed a relational database or not. Or for that matter GraphQL or AWS Lambda. I do get the feeling they went YOLO and built their solution on an over-engineered approach with a stack they were unfamiliar with, and when they hit problems, they YOLO'd into another solution without properly evaluating why, which feels like a team of junior developers run amok. They also seemed to not really understand relational databases, and why foreign keys are kind of important when you are choosing a relation database implementation (yes you can do without them, but it's best to understand them first).
- Exuma 3y ago
  The article is cringe yolo for sure With that said I was planning to use prisma and went and looked at issues on GitHub. They’re egregious … and I will not be using prisma The library also looks like it was written in a yolo way as well. My guess is the graphql background has permanently stunted it, as the project manager keeps wanting examples for performance issues instead of realizing the entire fact joins are missing to begin with is idiotic and absurd. So my guess is they’re piecemeal fixing one off issues instead of fixing the problem at its root, probably because prisma code is not designed with that in mind from the beginning
l5870uoo9y 3y ago
One thing that keeps coming up is that SQL equals low productivity. I don't think this is true. I think the culprit is that most developers are using to heavily abstracting SQL using ORMs like Prisma that hides the database and SQL logic. Since building a SQL generator (https://aihelperbot.com https://aihelperbot.com) as a side project, I have become much more proficient in SQL and even though I am also locked into Prisma, I use the `queryRaw` all the time to execute raw SQL queries. You can understand the code without knowing Prisma API. It is more performant. For more complex SQL queries, I use the SQL generator for initial suggestions and adapt if needed. For the next projects I build I want to use the minimal Postgres client (https://github.com/brianc/node-postgres https://github.com/brianc/node-postgres) combined with a lightweight migration library.
- DanielHB 3y ago
  I dunno, 90% of my queries are super simple and prisma does the job pretty well. In my relatively small prisma project I have a grand total of 2 raw queries. Also prisma raw query builder and migrations are really good. I don't see all the hate, you can mix and match both approaches just fine.
- ivanb 3y ago
  Indeed. A query builder is only really useful for dynamic queries and in practice they are few. I would argue that for performance and complexity reasons dynamic queries in real-world applications should be avoided.
  - fabian2k 3y ago
    I'm always surprised when I see this opinion. I think I might be writing fundamentally different kinds of applications because I need dynamic queries all the time. I often need several different kinds of filters for an endpoint that can be combined arbitrarily. Any UI that allows users to filter stuff by various criteria needs some kind of dynamic query building in the end.
    - ivanb 3y ago
jeswin 3y ago
Warning - opinions ahead. I've use a bunch of ORMs (in various languages) over the last two decades. Imho, one stands distinctly above the others - Microsoft's Entity Framework (EF). EF does something fundamentally different, while the rest are all some version of Hibernate with a different API or Programming Language. EF converts your code into SQL with the help of the compiler, and is not purely a runtime affair. This is what makes it relatively more usable. To illustrate, if JS/TS were to approach the problem in a similar way, you'd do this: 1. Allow queries to be written like this: const bangaloreanStudents = await db.users.filter(x => x.city === "Bangalore" && x.age <= 20); 2. Use acorn/espree to parse this into an AST 3. Convert the AST into SQL, hand over to runtime helpers. Doing so will allow queries to be written in the most natural way possible.
- city41 3y ago
  Knex is probably the closest thing js has to this. It's not an orm though, just a query builder.
  - jeswin 3y ago
    Knex is quite different iirc. I was saying that ORMs should make use of parsers and modern language features (such as first class functions) to offer a unified interface to collections in memory and collections in the db. Essentially treating the table as a list on which you can use operators you're already familiar with. By doing so, your core API is timeless. Because you didn't invent an API.
- CharlieDigital 3y ago
  .NET and EF Core is the platform that most teams probably want but .NET and C# can't quite shake it's pre-Core image. It always strikes me as odd that teams can embrace GitHub, TypeScript, and VS Code and then react negatively to C# and .NET being Microsoft platforms. EF Core is perhaps the most powerful, most mature ORM at this point. .NET Web APIs are incredibly productive and performant with really good DX. C# isn't that far off from TypeScript syntactically and structurally. I feel like teams that are "graduating" from TypeScript on Node probably want C# on .NET but end up exploring Go or Rust instead.
ivanb 3y ago
It looks like one poor choice leading to another poor choice. They probably chose Prisma because it may have helped them with GraphQL, whereas they most likely don't need any GraphQL at all. Then they are screwing around with AWS Lambdas and package size, but what is the advantage of the Lambdas if they don't make your deployment easier? Just have a ECS and run whatever you want for better price/performance ratio and much higher performance ceiling. I don't get which of their tools are improving developer productivity or software quality. It looks like every tool stands in their way. Why won't they just learn SQL and use it? What's is so low productivity about it? It's the highest level data manipulation language there is. Now they are bringing Kysely for supposed type safety. Yeah, good luck debugging, optimizing or changing the queries in any way. You'll have to convert from SQL to TypeScript and back whenever you'd want to develop the query in your SQL console. What I see is a bunch of poor choices. Shiny tools standing in the way of getting stuff done.
- ehutch79 3y ago
  Your last sentence nails it. They’ve been sold a bunch of tools by tech influencers. Told that if you don’t use X you’re doing it wrong. They believed the hype. It’s hard to remember that, at this point, most tech influencers are shitposting or are full of shit.
  - dimgl 3y ago
    The thing is that they themselves are tech influencers. So it's not surprising this article is the way it is. https://www.youtube.com/watch?v=J2j1XwZRi30 https://www.youtube.com/watch?v=J2j1XwZRi30
- deleted 3y ago
  [deleted]
dncornholio 3y ago
This article has no depth and seems to me author has made some obvious mistakes in this migration while being totally oblivious. More red flags when the word insane is used, 3 times.
abigail95 3y ago
Lambda only has a 50MB limit if you don't know how to use Lambda or don't have eyes to read the instructions.
tbarn 3y ago
The good news on the PlanetScale side is that foreign key constraint support is coming, so in the future this would no longer be an issue.
dimgl 3y ago
This is a very strange comment section. I can't in good faith recommend Entity Framework to anyone who doesn't want to torpedo their project with ORM problems. Additionally, this article is really poorly written. > Last week, we completed a migration that switched our underlying database from MongoDB to Postgres. Okay cool, but why? MongoDB is a very capable and fast database. > It was a shock finding out that Prisma needs almost a “db” engine layer of its own. Read more about it here: https://www.prisma.io/docs/concepts/components/prisma-engines https://www.prisma.io/docs/concepts/components/prisma-engine... If you did any research on Prisma rather than diving in head-first, you'd realize this is a core part of why Prisma exists. > we discovered that at a low level, Prisma was fetching data from both tables and then combining the result in its “Rust” engine. This was a path for an absolute trash performance. Can you confirm this is actually the case? Can you show some benchmarks re: this claim? Or are you just assuming this is the case? We're using Prisma in production and it is blazing fast.
syspec 3y ago
> Every new insert via Prisma opened a database-level transaction (?). An extremely weird design choice by Prisma. Isn't that standard practice for inserts?
joshstrange 3y ago
I’m using Prisma and PlanetScale and while I’ve been happy with PS, Prisma hasn’t been the best to work with. Especially in a serverless environment the engine size is painful. I’m able to work around it and I have a good amount of headroom still but I’d rather have that space back. For anything other than basic CRUD on a single entity or a few easy relationships I feel the need to write raw SQL. The DSL for Prisma’s ORM (schema is fine) is annoying and I feel like I’m fighting with it more often than not. Even once I have it working I’m not confident that it’s efficient at all, it worries me that the code “looks” simple (again it’s not easy to write in the first place) but isn’t. I started with Aurora and DataApi and switched to Prisma and PlanetScale. I have every intention of staying with PlanetScale but I’ve considered rewriting my codebase to not use Prisma. All the raw queries would be easy enough and I think I’d be happier with a type safe query _builder_ instead of a full ORM but I could be wrong. I’m almost glad Prisma’s aggregate helpers are such trash since it made me just write my own queries instead of standing on my head to make Prisma’s work. One thing I won’t miss is scouring my codebase for .prisma folders that might contain an outdated schema file that the engine is somehow picking up. I even have an npm script that I run to find and delete all but the main one I know is supposed to be there.

93 points by mariuz 3y ago

You should really try out RDB, an ORM that I've been working on since 2014. https://rdbjs.org https://rdbjs.org It has a very small fooprint - well suited for Amazon lambda. We now support client over HTTP (in a safe manner), making it accessible in-browser. Key Features: No code generation required Full intellisense, even when mapping tables and relations Powerful filtering - with any, all, none at any level deep Supports JavaScript and TypeScript ESM and CommonJS compatible Succint and concise syntax Works over http

RedShift1 3y ago

Prisma is an offshoot of graphql.cool, which was a "graphql API as a service". It seems to me that was a potentially good and viable business. However they stopped graphql cool and released Prisma, where there's no business model (who's gonna pay for an ORM?). I'm still left wondering why they did that?

Addono 3y ago
I presume the services around it, e.g. their products under the Data Platform: - https://www.prisma.io/data-platform/accelerate https://www.prisma.io/data-platform/accelerate and: - https://www.prisma.io/data-platform/proxy https://www.prisma.io/data-platform/proxy Whether it's a viable business is to be seen though. I've been a happy user of Prisma for a while, and so far we've not been running into severe limitations nor need for their additional services. Who knows what the future will bring though.

francislavoie 3y ago

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.

Closi 3y ago
Depends how important consistency is - application-level only will mean it can potentially silently fail if there is a bug.
leononame 3y ago
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.
- francislavoie 3y ago
  With foreign keys, I can't use https://github.com/github/gh-ost https://github.com/github/gh-ost for zero downtime migrations. Instant deal breaker, because schema changes are inevitable given business evolution.
  - lazy_moderator1 3y ago
    you can always use views + triggers for a zero downtime migration, this is a non-issue
    - oblio 3y ago
      Can you expand on this? I'm curious about the steps.
      - nijave 3y ago
        Sounds like it's basically explained in the gh-ost readme https://github.com/github/gh-ost#how https://github.com/github/gh-ost#how I think it amounts to "use views to decouple access to the table with a fixed interface" and "use triggers for migrating data between tables"

CharlieDigital 3y ago

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 obvious from a few simple toy examples. Seems like this team dove headfirst without even building a simple toy to determine if the choice was actually viable.

freddref 3y ago
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.
- CharlieDigital 3y ago
  I have a directory on my drive called `sandbox` where I basically throw together small toys for anything with some unknown complexity. Anything from how an ORM might model a specific type of relationship (throw together a basic replica with a Docker compose file) to replicating the deployment model (poking AWS Copilot, for example) to testing out some tooling flow (e.g. local build process change). The main thing with a toy model is speed. You can build/deploy/test with a smaller scope and progressively scale it up some reasonable scale of the full thing (whatever you're testing for) and you can iterate your testing faster. But many times, the key issues show up quite early in the process of grokking the toy model.
  - w0m 3y ago
    'scratch' for me. Everything from quick regex tests to full library rewrites tend to start there.

Zealotux 3y ago

Drizzle looks like an interesting alternative to Prisma https://orm.drizzle.team https://orm.drizzle.team You get TS support, tooling, and a very thin layer on top of SQL so little performance hit.

Jnr 3y ago
+1 for drizzle. And writing queries with joins is possible. :)
sccxy 3y ago
Drizzle is much better in queries, but migrations fail very often. And a lot of mysql bugs are reported in GitHub and waiting for fixes.

leononame 3y ago

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

jamincan 3y ago
They have a number of tools to consolidate queries into a single transaction (createMany in this case), or, you can control transactions directly using their $transaction api.

thefilmore 3y ago

I tend to reach for TypeORM. It supports every database under the sun.

c0n5pir4cy 3y ago
TypeORM is great and all - but it has its own quirks too and it's not a silver bullet. In their case they would also maybe run into some issues as TypeORM has some initialization cost (building a model in memory when it first connects to the DB etc), some object mapping costs and other complexities (particularly around efficient query generation). While there is nothing stopping it from working in a Lambda this would increase initialization time and cost which both matter since this is for a GraphQL API. I think the solution they've selected (Kysely) is probably a better fit.

mijoharas 3y ago

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

reedf1 3y ago
What do they think is executing the table scan? Do they think that the underlying hardware should be free?
eightnoteight 3y ago
aurora serverless v2 now supports io optimized mode. which increases the pricing for instances by constant amount but makes the io pricing to 0 https://aws.amazon.com/about-aws/whats-new/2023/05/amazon-aurora-i-o-optimized/ https://aws.amazon.com/about-aws/whats-new/2023/05/amazon-au...
- mijoharas 3y ago
  Oh, very cool!
naiv 3y ago
I also think that this is the old Planetscale pricing. The new one has unlimited reads (https://planetscale.com/pricing https://planetscale.com/pricing)
patmorgan23 3y ago
Row-reads sounds like a perfectly acceptable metric to use for billing. It's basically changing based on disk io. So what if the result of your query is 0, the server has to do a bunch of work to figure out the answer is 0.

spion 3y ago

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=Improved%20Relational%20Queries%20Permormance%20and%20Read%20Usage 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).

jasfi 3y ago
Agreed, if you have some sort of limit, then it seems that AWS Lambda has a limitation you could hit with Prisma. Prisma has some support for joins with relations: https://www.prisma.io/docs/concepts/components/prisma-schema/relations https://www.prisma.io/docs/concepts/components/prisma-schema... Also, you need to specifically create a transaction to encompass your Prisma calls to limit them to a transaction. This is the default for PostgreSQL as well.
estebarb 3y ago
The joins should be done inside the DB, not in an external server. Any SQL DB should be able to do that.
- spion 3y ago
  If this is not done carefully, it can also be slow in some situations, even with the right indeces in place. Lets say we have the following slightly contrieved case: organizaiton(id, name, description) -> users(id, name) -> posts(id, content) -> comments(id, userId, content) We want to get one organization with all its users. Lets imagine our org has 100 users, each of which has 100 posts on average, each of which has 100 comments on average. In a naive, flat join, we'll be repeating the organization description column 1 million times, and each post's content about 100 times on average. This can still be problematic without joins, as sending 1 million comment IDs in a query to the DB isn't going to work, so even the multiple queries version will need to do better than that

oblio 3y ago

"We migrated to SQL. Our biggest <<lesson>>? Don't use Prisma". Old man shouting at the literal IT clouds.

OJFord 3y ago
Let's take it offline for discussion in the breakout room, ok?
- oblio 3y ago
  Can you please clarify your ask?

switch007 3y ago

I’ve been tempted to ditch django for typescript in the backend but the sql and migrations story looks a bit … confusing and underwhelming? I feel like typeorm should cover 99% of cases, due to its feature list and age (7 ish years…ancient!), but then you see people often recommending other projects (eg pg_typed). Do people just string things together? Make use of Liquibase? In 2021 it seemed the TypeORM project was badly maintained and quite buggy - https://news.ycombinator.com/item?id=26888369 https://news.ycombinator.com/item?id=26888369 . Is that still the case?

pritambarhate 3y ago
We have been using TypeORM for last 2 years now. Found it quite suitable for our use case. Didn't come across any big issue. But the projects we did with it where not very high scale. Less than 5 requests per second. So a single server affair.

porbelm 3y ago

People needing to query huge amounts of data discovering technology that's been developed over the past 45 years for querying huge amounts of data efficiently; news at 11. Seems all these shops starting out with MongoDB or the like always need to do a huge, complicated migration to a /proper/ optimized data store, when they could have started with a sane design in the first place...

CharlieDigital 3y ago
> People needing to query huge amounts of data discovering technology that's been developed over the past 45 years for querying huge amounts of data efficiently; news at 11 I think a lot of folks are quick to discount just how scalable a database like Postgres or SQL Server can be (not to mention battle tested). Maybe its because of a lack of DBMS experience as we've move more and more from owning database servers to consuming *-as-a-service. Maybe it's because everyone thinks that they'll have Facebook scale traffic and concerns so they immediately gravitate to technologies designed to solve problems at a different order of magnitude.
- twh270 3y ago
  Also, SQL is sCaRy. It's "hard to learn", and "we'll need experts to tune the database queries", and "schema migrations are a nightmare", etc. Whereas MongoDB is "simple".
DonnyV 3y ago
The problem is when people pick Mongodb thinking they can use it like a SQL database. I've been using Mongodb since v1.6. You have to use it differently then a SQL database. The way you design your schema and code needs to change also. Its just a different way of thinking about a database. When you use Mongodb you treat the db like a dumb black box. You design in data integrity into your code. One of the big reasons you use Mongodb is because you don't need an ORM. Your POCOs are the tables. People that say they lost data while using Mongodb. Were using Mongodb wrong.
pleoxy 3y ago
> Seems all these shops starting out with MongoDB or the like always need to do a huge complicated migration First thing to note, I couldn't find any reasoning for the change in the original post. This makes the speculating on it rather pointless, but clearly you are able to fill in the details quite easily somehow. As a counter point. Last four businesses I've worked at all ran on MongoDB. More happenstance than something I've looked for. They are all relatively small businesses and all ran fine, the biggest was ingesting 1-10GB/day of new data from their IOT fleet. So this perspective that everything mongo is fundamentally broken and plain bad is pretty foreign to my real world experience. These shops all liked what they were doing and there was zero talk of scaling issues or changing out the database.

Exuma 3y ago

Wow just one look at this issue was all I needed to see. https://github.com/prisma/prisma/discussions/12715 https://github.com/prisma/prisma/discussions/12715 “Please open an issue if something really is unusable or breaks in any way so we have these concrete cases recorded. In our knowledge Prisma works just fine and returns the data that was requested, just using a different method.” Good lord

superwalker 3y ago
I substantially regret using Prisma in production for this and very many other reasons. At least the underlying data is well modeled, just need to rip out the JS library and replace it… but with what?
- ohthatsnotright 3y ago
  I found MikroORM [0] to be quite reasonable if you're in the TS ecosystem already. It was also easy to do custom, raw queries, and really just felt like it wasn't in the way. [0] https://mikro-orm.io/ https://mikro-orm.io/

adamzochowski 3y ago

> 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. This is expected pricing. The pricing is based on the load on the db, not based on results. Usually there are two ways working with this: DB supports showing estimated query plan , which explains how query would be ran without running it. Allowing to capture any missing indexes or filtered indexes. DB allows only querying against indexes (this is usually done by no-sql dbs which have flexibility to deny bad queries).

Tade0 3y ago

I kind of want to say "y-yeah" regarding Prisma, because all this information (especially the fact that it creates a transaction every time and downloads those engines) is either available in the documentation or visible upon install/build - you can actually make it point to specific executables instead of downloading each time using environment variables but again - it's in the docs. I picked Prisma because I'm not actually a backend developer and the tradeoffs were okay for my use case. I wish performance was better, but a few indexes here and there went a long way. The only surprising bit for me was peak memory usage - it comes in bursts and can get to hundreds of MB when doing even a simple query. The queries themselves are also kind of out there in terms of complexity but so far I haven't found a reason to rewrite them in raw SQL, since that isn't where our performance bottlenecks are at the moment. The lack of rollbacks is one legitimate pain point and I wish they addressed it.

mattacular 3y ago
> The lack of rollbacks Could you elaborate on this part?
- Tade0 3y ago
  Here's a comment from their team about this: https://github.com/prisma/prisma/discussions/4617#discussioncomment-215868 https://github.com/prisma/prisma/discussions/4617#discussion... Tl;dr it's a design decision, not everyone (including me) agrees.
  - superwalker 3y ago
    [flagged]
DanielHB 3y ago
I added prisma to a server I have and I was surprised it was running out of memory when using "prisma migrate deploy" (only migrations, not actually just running prisma client). The server was configured to use only 512mb of RAM though. It seems prisma migrate needs quite a bit of RAM.

danjac 3y ago

From the article there is no detail of what problem they were solving, just "we used tech stack A, we ran into issues, so we went with tech stack B". So I have no idea whether MongoDB was a good choice to begin with, or not, or whether the problem needed a relational database or not. Or for that matter GraphQL or AWS Lambda. I do get the feeling they went YOLO and built their solution on an over-engineered approach with a stack they were unfamiliar with, and when they hit problems, they YOLO'd into another solution without properly evaluating why, which feels like a team of junior developers run amok. They also seemed to not really understand relational databases, and why foreign keys are kind of important when you are choosing a relation database implementation (yes you can do without them, but it's best to understand them first).

Exuma 3y ago
The article is cringe yolo for sure With that said I was planning to use prisma and went and looked at issues on GitHub. They’re egregious … and I will not be using prisma The library also looks like it was written in a yolo way as well. My guess is the graphql background has permanently stunted it, as the project manager keeps wanting examples for performance issues instead of realizing the entire fact joins are missing to begin with is idiotic and absurd. So my guess is they’re piecemeal fixing one off issues instead of fixing the problem at its root, probably because prisma code is not designed with that in mind from the beginning

l5870uoo9y 3y ago

One thing that keeps coming up is that SQL equals low productivity. I don't think this is true. I think the culprit is that most developers are using to heavily abstracting SQL using ORMs like Prisma that hides the database and SQL logic. Since building a SQL generator (https://aihelperbot.com https://aihelperbot.com) as a side project, I have become much more proficient in SQL and even though I am also locked into Prisma, I use the `queryRaw` all the time to execute raw SQL queries. You can understand the code without knowing Prisma API. It is more performant. For more complex SQL queries, I use the SQL generator for initial suggestions and adapt if needed. For the next projects I build I want to use the minimal Postgres client (https://github.com/brianc/node-postgres https://github.com/brianc/node-postgres) combined with a lightweight migration library.

DanielHB 3y ago
I dunno, 90% of my queries are super simple and prisma does the job pretty well. In my relatively small prisma project I have a grand total of 2 raw queries. Also prisma raw query builder and migrations are really good. I don't see all the hate, you can mix and match both approaches just fine.
ivanb 3y ago
Indeed. A query builder is only really useful for dynamic queries and in practice they are few. I would argue that for performance and complexity reasons dynamic queries in real-world applications should be avoided.
- fabian2k 3y ago
  I'm always surprised when I see this opinion. I think I might be writing fundamentally different kinds of applications because I need dynamic queries all the time. I often need several different kinds of filters for an endpoint that can be combined arbitrarily. Any UI that allows users to filter stuff by various criteria needs some kind of dynamic query building in the end.
  - ivanb 3y ago

jeswin 3y ago

Warning - opinions ahead. I've use a bunch of ORMs (in various languages) over the last two decades. Imho, one stands distinctly above the others - Microsoft's Entity Framework (EF). EF does something fundamentally different, while the rest are all some version of Hibernate with a different API or Programming Language. EF converts your code into SQL with the help of the compiler, and is not purely a runtime affair. This is what makes it relatively more usable. To illustrate, if JS/TS were to approach the problem in a similar way, you'd do this: 1. Allow queries to be written like this: const bangaloreanStudents = await db.users.filter(x => x.city === "Bangalore" && x.age <= 20); 2. Use acorn/espree to parse this into an AST 3. Convert the AST into SQL, hand over to runtime helpers. Doing so will allow queries to be written in the most natural way possible.

city41 3y ago
Knex is probably the closest thing js has to this. It's not an orm though, just a query builder.
- jeswin 3y ago
  Knex is quite different iirc. I was saying that ORMs should make use of parsers and modern language features (such as first class functions) to offer a unified interface to collections in memory and collections in the db. Essentially treating the table as a list on which you can use operators you're already familiar with. By doing so, your core API is timeless. Because you didn't invent an API.
CharlieDigital 3y ago
.NET and EF Core is the platform that most teams probably want but .NET and C# can't quite shake it's pre-Core image. It always strikes me as odd that teams can embrace GitHub, TypeScript, and VS Code and then react negatively to C# and .NET being Microsoft platforms. EF Core is perhaps the most powerful, most mature ORM at this point. .NET Web APIs are incredibly productive and performant with really good DX. C# isn't that far off from TypeScript syntactically and structurally. I feel like teams that are "graduating" from TypeScript on Node probably want C# on .NET but end up exploring Go or Rust instead.

ivanb 3y ago

It looks like one poor choice leading to another poor choice. They probably chose Prisma because it may have helped them with GraphQL, whereas they most likely don't need any GraphQL at all. Then they are screwing around with AWS Lambdas and package size, but what is the advantage of the Lambdas if they don't make your deployment easier? Just have a ECS and run whatever you want for better price/performance ratio and much higher performance ceiling. I don't get which of their tools are improving developer productivity or software quality. It looks like every tool stands in their way. Why won't they just learn SQL and use it? What's is so low productivity about it? It's the highest level data manipulation language there is. Now they are bringing Kysely for supposed type safety. Yeah, good luck debugging, optimizing or changing the queries in any way. You'll have to convert from SQL to TypeScript and back whenever you'd want to develop the query in your SQL console. What I see is a bunch of poor choices. Shiny tools standing in the way of getting stuff done.

ehutch79 3y ago
Your last sentence nails it. They’ve been sold a bunch of tools by tech influencers. Told that if you don’t use X you’re doing it wrong. They believed the hype. It’s hard to remember that, at this point, most tech influencers are shitposting or are full of shit.
- dimgl 3y ago
  The thing is that they themselves are tech influencers. So it's not surprising this article is the way it is. https://www.youtube.com/watch?v=J2j1XwZRi30 https://www.youtube.com/watch?v=J2j1XwZRi30
deleted 3y ago
[deleted]

dncornholio 3y ago

This article has no depth and seems to me author has made some obvious mistakes in this migration while being totally oblivious. More red flags when the word insane is used, 3 times.

abigail95 3y ago

Lambda only has a 50MB limit if you don't know how to use Lambda or don't have eyes to read the instructions.

tbarn 3y ago

The good news on the PlanetScale side is that foreign key constraint support is coming, so in the future this would no longer be an issue.

dimgl 3y ago

This is a very strange comment section. I can't in good faith recommend Entity Framework to anyone who doesn't want to torpedo their project with ORM problems. Additionally, this article is really poorly written. > Last week, we completed a migration that switched our underlying database from MongoDB to Postgres. Okay cool, but why? MongoDB is a very capable and fast database. > It was a shock finding out that Prisma needs almost a “db” engine layer of its own. Read more about it here: https://www.prisma.io/docs/concepts/components/prisma-engines https://www.prisma.io/docs/concepts/components/prisma-engine... If you did any research on Prisma rather than diving in head-first, you'd realize this is a core part of why Prisma exists. > we discovered that at a low level, Prisma was fetching data from both tables and then combining the result in its “Rust” engine. This was a path for an absolute trash performance. Can you confirm this is actually the case? Can you show some benchmarks re: this claim? Or are you just assuming this is the case? We're using Prisma in production and it is blazing fast.

syspec 3y ago

> Every new insert via Prisma opened a database-level transaction (?). An extremely weird design choice by Prisma. Isn't that standard practice for inserts?

joshstrange 3y ago

I’m using Prisma and PlanetScale and while I’ve been happy with PS, Prisma hasn’t been the best to work with. Especially in a serverless environment the engine size is painful. I’m able to work around it and I have a good amount of headroom still but I’d rather have that space back. For anything other than basic CRUD on a single entity or a few easy relationships I feel the need to write raw SQL. The DSL for Prisma’s ORM (schema is fine) is annoying and I feel like I’m fighting with it more often than not. Even once I have it working I’m not confident that it’s efficient at all, it worries me that the code “looks” simple (again it’s not easy to write in the first place) but isn’t. I started with Aurora and DataApi and switched to Prisma and PlanetScale. I have every intention of staying with PlanetScale but I’ve considered rewriting my codebase to not use Prisma. All the raw queries would be easy enough and I think I’d be happier with a type safe query _builder_ instead of a full ORM but I could be wrong. I’m almost glad Prisma’s aggregate helpers are such trash since it made me just write my own queries instead of standing on my head to make Prisma’s work. One thing I won’t miss is scouring my codebase for .prisma folders that might contain an outdated schema file that the engine is somehow picking up. I even have an npm script that I run to find and delete all but the main one I know is supposed to be there.