I am in a startup that's using Prisma and it we really wish we had not:
- The query objects can become hard to read with anything more or less complex.
- If you need an unsupported Postgres extension you are out of luck.
- One large file in a schema, impossible to shard.
- We have many apps in a monorepo and they cannot have separate prisma connections cause the schema gets baked into "@prisma/client"
Basically the only thing useful about it are the TS types which is something SQL-builder libraries solve better. Long story short, use Kysely, Prisma provides no value that I see.
"Instead, it sends individual queries and joins the data on the application level. However, this doesn't mean that Prisma's relational queries are per se slower"
Wow, what the fuck.
"Also, this chapter about Query Performance Optimization from the High Performance MySQL book has some great insights. One of the techniques it mentions is JOIN decomposition:
Many high-performance web sites use join decomposition. You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application."
This belief that they can write JavaScript that outperforms decades of bare-metal executed optimisations in mainstream database engines is just astonishing.
> This belief that they can write JavaScript that outperforms decades of bare-metal executed optimisations in mainstream database engines is just astonishing.
In my first job fresh out of uni, I worked with a "senior" backend developer who believed this. He advocated for crusty, bug-ridden ORMs like Sequelize and Prisma (still very early stage back then, so lots more issues than now though I'd still steer well clear of it). Claiming they did "query optimizations". I knew it made zero sense, but also that I wasn't going to be able to convince him.
The application joins are (soon to be were) done in Rust service that's side loaded with the node server.
Also, this is an old quote. Databases didn't all support things like JSON at the time, so joins and subqueries presented an N+1 problem and could balloon data fetch requirements fairly easily. Being a GraphQL-focused ORM originally too, this made some sense.
The default is now being changed and correlated subqueries, JOINs, & JSON aggregation will replace the old approach unless explicitly toggled.
As I understand it the N+1 problem is when you select a set and then perform another query per row in the set. Executing a join is a solution to this, not a cause.
Every ORM is bad. Especially the "any DB" ORMs. Because they trick you into thinking about your data patterns in terms of writing application code, instead of writing code for the database. And most of the time their features and APIs are abstracted in a way that basically means you can only use the least-common-denominator of all the database backends that they can support.
I've sworn off ORMs entirely. My application is a Postgres application first and foremost. I use PG-specific features extensively. Why would I sacrifice all the power that Postgres offers me just for some conveniences in Python, or Ruby, or whatever?
Coming from Javaland to C#, Entity Framework is a breath of fresh air.
The Npgsql driver automatically applies PG-specific tricks without me having to do anything special
The only path I had to do myself is the data ingress point that had some race condition issues, everything else seems to perform pretty well out of the box.
Entity Framework really is such a time saver. The PG adapter makes it a breeze not just with common queries, but also more recent stuff, like working with embeddings for vector search.
Nah. The most prolific backend frameworks are all built on ORMs for good reason. The best ones can deserialize inputs, validate them, place those object directly into the db, retrieve them later as objects, and then serialize them again all from essentially just a schema definition. Just to name a few advantages. Teams that take velocity seriously should use ORMs. As with any library choice you need to carefully vet them though.
The "good reason" is that modern web devs do not consider SQL a core skill, and plain do not understand databases. To be a competing modern web framework you have to include an ORM so these people will consider you.
Trying to explain to a modern web dev that the optimum data storage structure is not the same as the optimum application layer data structure, so you can't just take one and map them across 1:1 to the other, is really painful.
Developing without an ORM is just as quick as developing with one (because the time you save on routine queries you will more than lose on the tricky edge cases that the ORM completely screws up on). But you need to know SQL and databases to do it.
Could this be selection bias? I've never worked with a backend engineer that couldn't write SQL. I've worked on plenty of projects were there were bugs in hand written SQL though.
Basic SQL yeah, probably. Actually knowing SQL and how to write good SQL and understanding the tradeoffs between different query plans (and the tradeoffs between different schemas). That's rare.
And yeah, obviously, there will be bugs in your SQL. And writing good tests for the database layer is always tricky because there's usually some pushback to setting up a database on the CI instance, and so on. It's not simple, but ignoring it by using an ORM doesn't make it simpler, it just means you have less options when it goes wrong.
It makes sense that some kinds of schema optimizations are fairly esoteric, because the services most devs work on don't need them. An ORM is something you import on day one to speed up development; while rearranging tables to remove joins may not be needed (or even justifiable based on metrics) for another five years. I would hope that the ORM is flexible enough to be irrelevant to the trade-off between schemas though. Your comment about not having a database in CI brings back nightmares! Reminds me of how at my first job they had us running SQLite in CI and then shipping to Postgres, which worked about as well as you can imagine haha
On the other hand, ORMs insulate you from database integrity since ORMs have limited access to underlying database features.
In Postgres that usually means you're not locking rows, you're not using upsert, you might not be writing table DDL yourself. It often means you aren't even using database transactions.
While these things might be extraneous fluff for an all-nighter hackathon, you really have to figure out a sweet spot so that data integrity violations aren't killing your velocity when your service's rubber begins hitting the road.
These are important features for a database toolkit to consider. I don't think that it is fair to dismiss an entire category of libraries on the grounds of some implementations being less complete than desired though. If we applied that same standard more generally, then we wouldn't use anything at all, because most software libraries kind of stink.
Fair enough. Do you have a favorite ORM that makes what you feel is a decent set of trade-offs, all things considered?
Admittedly, most of my experience with ORMs was with Ruby on Rails' Active Record + Rails' generated SQL tables + the culture that ensued from it, like large production Rails applications that didn't use a single db transaction (and often no indexes). Though I reckon things could have changed in 15 years.
I can imagine that an ORM might be the best option for most people. It wasn't until I worked at one specific company that I learned how to really use Postgres. Before that, an ORM and its abstractions probably made more sense than expecting me to figure out how to use a database directly on my own.
I've been building one for Go in my free time, but it's not ready for general use. Historically, I've used Django despite being imperfect, because I can just install Wagtail and have a nice admin interface for free. It does have some nice convenience features though and transactions are easy enough. At my day job we use a Java framework with a terrible codegen-based ORM. Laravel has a decent database toolkit if you are into PHP. Unfortunately, excellent database toolkits are rare, and I have historically found myself dipping into SQL frequently. All decent ones will at least allow you to do so though.
SQL Alchemy is pretty good, because it's mostly a sql engine that has an ORM bolted on top of that, and the docs actively try to point users towards using the sql engine rather than using the ORM for everything.
Except active record can barely be considered an ORM IMO. Doing a literal one to one mapping between records and objects is not that impressive. A real data mapper ORM at least gets you true entities that are decoupled from the db. That way you could totally swap out your data layer without affecting your domain layer. Active record leads to big ball of mud architecture.
That's a tradeoff that sometimes makes sense. MICROS~1 SQL Server heavily leans into the 'use specific features extensively', and countless applications on it consist mainly of stored procedures. It does however cause a lock-in that might not be attractive, your customers might be sensitive to what database engine you run their stuff on and then you need to figure out the common ground between two or more alternatives and build your application in that space.
It's not as uncommon as one might think, one of the big products in public sector services where I live offers both SQL Server and Oracle as persistence layer so they can't push logic into stored procedures or similar techniques.
But just sketching out some schemas and booting PostgREST might be good enough for forever, if that's the case, go for it. As for ORM:s, I kind of like how Ecto in Elixir settings does things, it solves a few tedious things like validation and 'hydration', and has a macro DSL for generating SQL with concise expressions.
It's actually even worse than this, many Django applications are straight up Postgres applications. They use Postgres specific bits of the ORM without hesitation. So they're learning these weird ORM incantations instead of just learning the underlying SQL, which would be knowledge you could apply anywhere.
People just hate embedding SQL into other languages. I don't know why.
I don’t understand the hate, the only truly limiting factor for Prisma right now is its poor support for polymorphism, apart from that it has quite good support for complicated index setups, and if you need anything more performant, just drop to typed raw sql queries, it also supports views (materialized or otherwise) out of the box.
I recently wanted to check it out and wrote a small app that had good use of pgvector for embeddings, custom queries with ctes for a few complex edge cases, and it was all quite smooth.
Now it might not be at the level of active record, ecto or sqlalchemy but it was quite decent.
If you know your sql at any point it gave me options to drop down a level of abstraction, but still keep the types so as not to break the abstraction too much for the rest of the code.
I don't hate prisma - it's just a tool - but that's far from the only limiting factor.
I recently looked at migrating a legacy project with basic SQL query generation to a modern ORM. Prisma came up top of course so I tried it.
We use Postgres built-in range types. Prisma does not support these, there's no way to add the type to the ORM. You can add them using "Unsupported", but fields using that aren't available in queries using the ORM, so that's pretty useless.
It also requires a binary to run, which would require different builds for each architecture deployed to. Not a big thing but it was more annoying than just switching the ORM.
That coupled with their attitude to joins - which has truth to it, but it's also short-sighted - eliminated Prisma.
The final decision was to switch to Kysely to do the SQL building and provide type-safe results, which is working well.
Oh, just manual template typing. That's not great. We've done that and it is error-prone. A SQL-generator infers the types from the query, for example.
That's the raw, raw querying. They provide a TypedSQL system where you write SQL and it automatically generated the parameter and return types. It's in the docs.
> It's true that Prisma currently doesn't do JOINs for relational queries. Instead, it sends individual queries and joins the data on the application level.
..........I'm sorry, what? That seems........absurd.
edit: Might as well throw in: I can't stand ORMs, I don't get why people use it, please just write the SQL.
I believe it’s either released now or at least a feature flag (maybe only some systems). It’s absolutely absurd it took so long. I can’t believe it wasn’t the initial implementation.
Funny relevant story: we got an OOM from a query that we used Prisma for. I looked into it - it’s was a simple select distinct. Turns out (I believe it was changed like a year ago, but I’m not positive), event distincts were done in memory! I can’t fathom the decision making there…
> event distincts were done in memory! I can’t fathom the decision making there…
This is one of those situations where I can't tell if they're operating on some kind of deep insight that is way above my experience and I just don't understand it, or if they just made really bad decisions. I just don't get it, it feels so wrong.
> I want to elaborate a bit on the tradeoffs of this decision. The reason Prisma uses this strategy is because in a lot of real-world applications with large datasets, DB-level JOINs can become quite expensive...
> The total cost of executing a complex join is often higher than executing multiple simpler queries. This is why the Prisma query engine currently defaults to multiple simple queries in order to optimise overall throughput of the system.
> But Prisma is designed towards generalized best practices, and in the "real world" with huge tables and hundreds of fields, single queries are not the best approach...
> All that being said, there are of course scenarios where JOINs are a lot more performance than sending individual queries. We know this and that's why we are currently working on enabling JOINs in Prisma Client queries as well You can follow the development on the roadmap.
Though this isn't a complete answer still. Part of it is that Prisma was, at its start, a GraphQL-centric ORM. This comes with its own performance pitfalls, and decomposing joins into separate subqueries with aggregation helped avoid them.
It's a completely ridiculous answer though. They're linking to High Performance MySQL's 2nd edition, which came out in June 2008, and was written for users of MySQL 5.0 running on 2008-era hardware.
My take, as a MySQL expert: that advice is totally irrelevant now, and has been for quite some time. It's just plain wrong in a modern context.
I’m not even sure it was correct for its time? The whole point of an RDBMS is to execute join operations. The only reason I’d suspect an RDBMS to be bad at its one fundamental job, in any point of time, is the N+1 query scenario or multiple left joins with unrelated dependencies, but that’s triggered by bad orm abstractions to begin with
It was absolutely valid advice for its time, but only in the highly specific cases/reasons outlined in the book. The 2nd edition was written by the top Percona folks, who pretty much had more experience scaling databases for large websites than anyone else.
The Prisma answer just does not summarize correctly what the book was saying.
It mainly boiled down to sharding and external caching. Storage and memory were much smaller back then, so there was a lot of sharding and functional partitioning, and major reliance on memcached; all of those are easier if you minimize excessive JOINs.
The query planner in MySQL wasn't great at the time either, and although index hints could help, huge complex queries sometimes performed worse than multiple decomposed simpler queries. But the bigger issue was definitely enabling sharding (cross-shard joins had to be handled at the application level) and enabling external caching (do a simple range scan DB query to get a list of IDs/PKs, then do point lookups in memcached, then finally do point lookups in the DB for any that weren't in memcached).
And, in light of that, the default is changing in a couple of months after the JOIN mode has had a significant period of time being tested in the real world.
It really gives me flashbacks to the early days of mongodb.
Which, frankly, is a good lesson that marketing and docs and hype can make up for any amount of technical failure, and if you live long enough, you can fix the tech issues.
Tbh, I once dabbled in building an ORM myself (in PHP) and I did find that in some situations it was faster to do individual queries and then join in code, to solve the N+1 problem.
Granted I was much worse in my sql knowledge and postgre/mysql had severe limitations in their query planners, so I can see how something like this could have happened. If they support multiple dbs, and even one has this problem, it might be better (for them) to do it application side.
The specific issue was doing a join with a table for a one to many, you get a lot more data from the db than you would normally need, if you do the join the naive way, and if the join is nested you get exponentially more data.
It was faster to do a query for each db separately and then stitch the results.
Now it is easy to solve in pg with nested selects and json aggregation, which pg query planner rewrites to efficient joins, but you still get only the bytes you have requested without duplication.
I disagree. It is probably one of the less terrible ORMs, but it is far from amazing. The object-relational impedance mismatch will always dominate for anything that isn't trivial business. EF works great until you need different views of the model. It does support some kind of view mapping technique, but it's so much boilerplate I fail to see the point.
Dapper + SqlConnection is goldilocks once you get into the nasty edges. Being able to query a result set that always exactly matches your view models is pretty amazing. The idea of the program automagically upgrading and migrating the schemas is something that was interesting to me until I saw what you could accomplish with Visual Studio's SQL Compare tool & RedGate's equivalent. I feel a lot more comfortable running manual schema migrations when working with hosted SQL providers.
> It does support some kind of view mapping technique
Can you call .Select(entity => SomeSmallerModel() { Name = entity.Name }) or something like that to select what you need? If I am understanding your issue correctly.
I also agree that its one of the least worst but there are still things that annoy me.
If you don't do stupid things like requesting everything from the database and then filtering data client side, then no.
We have one application built on .NET 8 (and contemporary EF) with about 2000 tables, and its memory usage is okay. The one problem it has is startup time: EF takes about a minute of 100% CPU load to initialize on every application restart, before it passes execution to the rest of your program. Maybe it is solvable, maybe not, I haven't yet had the time to look into it.
Yeah makes sense. I should have used my language more carefully: I have very little hands-on experience with entity framework, and thus don’t have objective reasons to say it has a “huge” memory footprint.
Does the startup time/CPU usage cost vary depending on the size of the dataset you’re interacting with?
Hmm, it should not be taking this much time unless you're running into an edge case - we have a fairly complex application that needs to pre-load a lot of data from persistence (no EFC though, it's an embedded DB) and over the wire at startup, and it still takes 15 seconds on a rainy day, mostly waiting for requests to finish. If you can gather a profile it will likely shed some light on why this takes place. Worst case, if you have a repro, it's worth to file an issue at https://github.com/dotnet/efcore. When you have time that is.
No. To be clear: I wasn’t trying to say it was bad. Just repeating what I had read in a (fairly old) .net book. Should have chosen my words more carefully.
To be fair, old Entity Framework was on the heavier side. Still much faster than e.g. ActiveRecord but enough for Dapper to be made back then. The gap between them is mostly gone nowadays plus .NET itself has become massively faster and alternate more efficient alternatives got introduced since (Dapper AOT, its main goal is NAOT compatibility but it also uses the opportunity to further streamline the implementation).
But I've found with that you can get better performance in _few_ situations with application level joins than SQL joins when the SQL join is causing a table lock and therefore rather than slower parallel application joins you have sequential MySQL joins. (The lock also prevents other parallel DB queries which is generally the bigger deal than if this endpoint is faster or not).
Although I do reach for the SQL join first but if something is slow then metrics and optimization is necessary.
It is. But wait... it doesn't join the data on the application level of your application. You have to deploy their proxy service which joins the data on the application level.
It's pretty obvious when somebody has only heard of Prisma, but never used it.
- Using `JOIN`s (with correlated subqueries and JSON) has been around for a while now via a `relationLoadStrategy` setting.
- Prisma has a Rust service that does query execution & result aggregation, but this is automatically managed behind the scenes. All you do is run `npx prisma generate` and then run your application.
- They are in the process of removing the Rust layer.
The JOIN setting and the removing of the middleware service are going to be defaults soon, they're just in preview.
They've been saying that for 3 years. We actually had a discount for being an early adopter. But hey its obvious Ive never used it and only heard of it.
The JOIN mode has been in preview for over a year and is slated for GA release within a few months. Which has been on their roadmap.
The removal of the rust service is available in preview for Postgres as of 6.7.[1]
Rewriting significant parts of a complex codebase used by millions is hard, and pushing it to defaults requires prolonged testing periods when the worst case is "major data corruption".
Honestly, everything you say makes me want to stay far from prisma _more_.
All this complexity, additional abstractions and indirections, with all the bugs gootguns and gotchas that come with it... when I could just type "JOIN" instead.
Okay? It's one setting that will be the default in 2 months. And you could always write type-safe SQL manually instead.
i greatly envy y'all having projects where the biggest complexity is... A single setting once, that's clearly documented. We live in very different worlds, apparently.
I didn't mind prisma for managing the schema etc but also seen your linked github issue. I found other people recommend combining Prisma with Kysley. I have only used this in toy projects so take this with a grain of salt.
I'm not the most experienced in huge DBs and can't write anything off, but I've never seen a horizontally sharded DBMS work well, even Citus which allegedly does. There's always been a catch that seems worse than manually doing sharding at a higher level than your DB, not that that's easy either.
I'd argue that horizontally sharded databases can work well, but they do tend to have significant non obvious tradeoffs that can be pretty painful.
There's a handful of companies that have scaled Citus past 1PB for production usage, but the examples I'm aware of all had more engineering to avoid capability or architecture limitations than one might like. I'd love to see someone come back with a fresh approach that covered more use cases effectively.
I can imagine it for some constrained use case, but taking your typical RDBMS that's powering a variety of business logic with complex queries, I dunno.
One interesting tradeoff Postgres and MySQL made for efficiency's sake was making xacts not fully ACID by default; instead they guarantee something that's good enough as long as you keep it in mind. Cockroach and Spanner are fully ACID, but that means even if you used those as a single-node DB, it ought to be slower.
Author here. Yeah, that's not a bad take away either. I've also been really vocal in Primsa issues for all sorts of things. We are about to embark on a big migration away from Prisma and onto Drizzle once the Drizzle team lands 1.0
We will absolutely share our findings when that migration happens!
That just sounds irresponsible. The correct choice for prod isn't "the cool new trendy thing that will solve all our problems once it hits 1.0", the correct choice is "the boring stable thing that has existed long enough for everyone to understand its shortcomings".
I already knew about prisma from the infamous https://github.com/prisma/prisma/discussions/19748