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

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.

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

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

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.

You should really try out RDB, an ORM that I've been working on since 2014.

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

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.

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.

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

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

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) 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) combined with a lightweight migration library.

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

Wow just one look at this issue was all I needed to see. 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

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

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?

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

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 . Is that still the case?

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.

"We migrated to SQL. Our biggest <<lesson>>? Don't use Prisma".

Old man shouting at the literal IT clouds.

Drizzle looks like an interesting alternative to Prisma https://orm.drizzle.team

You get TS support, tooling, and a very thin layer on top of SQL so little performance hit.

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.

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.

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

> Every new insert via Prisma opened a database-level transaction (?). An extremely weird design choice by Prisma.

Isn't that standard practice for inserts?

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

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.