I LOVE Prisma. I’ve used Django, SQLAlchemy, Sequelize, Knex, and TypeORM in the past. all had rough edges that continually frustrated me or didn’t provide the functionality i needed.<p>Prisma is different. It’s absolutely got rough edges, but the extremely strong type safety makes Sequelize look like a joke. The query engine itself, written in rust, combines and optimizes queries inside every tick of the event loop so GraphQL N+1 issues are a thing of the past.<p>Also, the team and community behind it are amazing! I never thought having an active dev community behind an ORM would be important, but as the author of Sequelize-Typescript was forced to abandon it late last year and the author of TypeORM was also pretty much absent, Prisma was a breath of fresh air. I REALLY hope they can find a way to build a sustainable business out of it. Support packages, feature development contracts, something to keep them financially incentivized to keep making it better.<p>Happy to answer any questions about my experience using it if anyone has any.
I don't like ORMs, but Prisma is one of the better ones. It avoids a lot of shortcomings of popular ORMs. I hope they find a way to make it work as a company. I've been following them over the years from the beginning of their journey through various pivots.<p>To use an ORM effectively you must learn both the API of the ORM and SQL. It does not absolve you from learning SQL, which I think was one of the unstated attractions to junior developers. Then you have to map between the API of the ORM and how it translates that to SQL under the hood. Then you have to understand how it handles caching and sessions and how that all works under the hood. This has been the source of so much complexity and so many bugs over the years that I no longer think the benefits are worth the cost. As always, there are exceptions and caveats, but I now believe it's better to use SQL directly than to use an ORM. In general there is too much complexity in all the layers of abstraction in software development these days, and I think the industry is strangely blind to the consequences of this. Complexity is death to software, and it should not be taken on lightly. The entire craft of a software engineer boils down to eliminating complexity and simplifying problems, the better you can do that, the more productive an engineer you will be.[1]<p>[1] <a href="https://github.com/sqljoy/sqljoy/blob/master/docs/pages/orm.md" rel="nofollow">https://github.com/sqljoy/sqljoy/blob/master/docs/pages/orm....</a>
I have adopted Prisma in my latest project and I have mixed feelings about it.<p>- The generated client is top-tier. Fully specified in TypeScript with intelligent types that can be extended by the user.<p>- The schema language is great. It provides a cohesive experience that can fully express your database structure, and it also provides a migration framework to manage that structure's evolution.<p>- There's no hook for implementing access control at the model level, so you end up needing to create a higher-level API around the base queries to implement these yourself, which is a fairly large investment. I'd love to see a pair of functions that can modify the query before it gets sent to the Prisma engine to add extra query values, and a second function that can filter models before they are returned to the caller.<p>Where Prisma falls short is in testing. The testing story in Prisma is that you can either use a live database, or you can completely stub out all calls to Prisma in your application. The latter means you end up writing a bunch of tests for implementation rather than for behavior, or you end up manually writing an in-memory database that fits the Prisma API. The former means that you need to completely recreate your database after every test case.<p>The discussions on the Github seem to indicate that the old "wrap the test in a transaction" trick is forbidden, and Prisma seems architecturally set up to ensure that you can't even hack this behavior in.<p>All in all, we probably won't switch away from it, but I will continue to look for a better way to test my endpoints.
“ Application developers should care about data – not SQL”<p>Anyone who has been bitten by an ORM generating inefficient SQL (and subsequent having to learn and care about SQL) knows the above not to be true.
Over a year ago, I was investigating using Prisma to be the ORM for a GraphQL API of a Postgres database. When doing a proof-of-concept, I discovered that under the hood @prisma/client was spinning up it's own GraphQL server that it would send requests to in order to generate SQL to send to postgres. This extra middleware layer between my frontend code and postgres generated some pretty poor performing queries that took 50% longer to complete than the queries generated by using Hasura as our whole GraphQL API.<p>A quick glance at the @prisma/client code makes it seem like this pattern is still the case, since they have an "engine-core" package that downloads a binary server behind the scenes and runs it on a free port in the background.
I'm completely perplexed at some of the functionality that's absent in Prisma? I'm coming from the Rails/Django world for reference. Can anyone help me understand if I'm out in left field or does this technology only cover basic use cases?<p><pre><code> - No supported way to do a case-insensitive sorting. https://github.com/prisma/prisma/issues/5068
- Can’t sort by an aggregate value like user’s post count. https://github.com/prisma/prisma/issues/3821
- Can’t control between inner/left join.
- Can’t do subqueries.
- Migration rollbacks are experimental maybe unsupported now? At least I only see mention in Github issues and not in the docs.
- Transactions appear to expect a series of queries? It doesn’t look like you can execute any app code during a transaction?
- No support for pessimistic row locking e.g. SELECT… FOR UPDATE ?
- No way to mixin raw query partials like `where('name ILIKE ?')`. You either need to write the whole query raw or not.
- Validations are done at the database level.
- Complex validations seem tricky to write in this format
- No built-in way to make clean user-facing validation messages.
- You can’t check that a model instance is valid without just trying to insert it into the database
- The official documented validation example has you connecting via psql and adding a constraint?
- So following the offical example my validations aren’t documented in the codebase via a model or a migration?
- Also they don’t have a validation example documented if you’re using MySQL instead of Postgres?
- Cascading deletes are handled the same way as validations. As in Prisma basically does nothing other than document how to implement it yourself outside of the library.
- No model methods. I guess that's not a surprised because it's "not an ORM". A model really is just a data mapping? Anyways it seems like you would end up rolling your own wrapper around this and there's no recommendations on standardized architecture.
- No callbacks. These have been controversial at times so are teams using Prisma writing something akin to "services" instead?
- Syntax nitpick but one of these is vulnerable to a SQL injection and it seems really easy for a new developer to get mixed up?
- prisma.$queryRaw(`SELECT \* FROM User WHERE email = ${email}`);
- prisma.$queryRaw`SELECT \* FROM User WHERE email = ${email}`;
- No way of batch loading like Active Records’s find_in_batches / find_each. All objects are just loaded into memory?
- No way of hooking into queries for instrumentation. e.g. ActiveSupport::Notifications.subscribe
</code></pre>
FYI - This is after fairly brief research. Not guaranteed 100% accurate.
I’ve been using Prisma for a while and I quite like it. Best ORM for TypeScript I’ve used.
My biggest issue with it is testability.
Sure, I can mock the Prisma client in tests with Jest or something, but if I want to test state I pretty much have to reimplement an in-memory DB using JS mocks.
I can also connect to a real DB made for testing but it’s quite overkill, I’m usually not interested in testing Prisma itself, just my own code.
I wish Prisma had test mode, where you could replace the client with a temporary in-memory SQLite DB without writing tons of boilerplate.<p>A drop-in replacement for PrismaClient for tests would have been wonderful.
I'm sure a lot of hard work went into this, so I mean no offense here when I give my honest feelings.<p>The big differences between v1 and v2 make me uneasy. I am reminded of the constant API churn of React-Router, or the complete change from AngularJS to Angular. This approach makes me very hesitant to learn the library, because I am afraid of having the choice of painful upgrade or a dead dependency down the road.<p>I am also wary of the library being VC-funded, because I am afraid of what kinds of features will be held at ransom down the line once they need to start making money.
I am over ORMs. I am looking at the documentation for Prisma Client at the moment, and I just think to myself "yet another ORM I have to learn". I have learnt so many ORMs, it is just stupid. SQL is SQL. I wish I had spent all that time further developing my SQL skills rather than learning the latest ORMs API.<p>For my latest large project, I just used SQL and I am happy. All I did was add some code to make sure the snake case was converted to camel case. Things like returning arrays of child relations can all be done in SQL these days with things like json_agg and json_build_object and so on.<p>Half of my SQL on the other hand doesn't even map to an ORM. Where is the insert ... select ... where not exists ... These things aren't even possible in the ORM concept model.
IMO any discussion about Node/TypeScript data access tools deserves a reference to Zapatos, which sort of is the anti-Prisma. It has all the type safety with none of the ORM (and it's Postgres-specific, for better and worse). I think it's got one of the best designed APIs I've come across.<p><a href="https://jawj.github.io/zapatos/" rel="nofollow">https://jawj.github.io/zapatos/</a>
I'm not a fan of ORM, never been and never will be. <a href="https://en.wikipedia.org/wiki/Object%E2%80%93relational_impedance_mismatch" rel="nofollow">https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...</a><p>But my gosh "Application developers should care about data – not SQL"
From the web page: "Application developers should care about data – not SQL"<p>That is fundamentally wrong - or at least - it is very limiting.<p>Think very hard about adopting a framework that tries to shield you from SQL.
Because at the end of the day, you will be looking at SELECTS and INSERTS in your logs because you are wondering why things are so slow or why your memory usage skyrockets.<p>It looks like Prisma allows you to go to raw SQL if you must, yet you won't have a good time using some of PostgreSQL more advanced features.<p>If your domain model is simple and <i>will remain simple in the future</i>, then - by all means - use a builder that creates queries from your domain model. Use a single-source-of-truth approach that is usable from the back-end and front-end.<p>In other cases, where your domain model changes over time, be very careful when choosing an ORM but extra double careful in picking the right data store.
For years node had pretty bad libraries for interacting with databases.<p>Prisma is the first time it all just makes sense.<p>The code generation, auto migrating without any code, automatic crud resolvers with nexus, the list goes on.<p>After using it, I don’t know how you can use any other language for backend. It would just take you much longer and require so much extra work. Nothing like prisma exists in other languages.
Oh also, Blitz.js is a nextjs fullstack framework that uses and autogenerates prisma code to work with.<p>Also FoalTS is a nice small framework for building rest endpoints with TypeScript, and recently landed @prisma support/documentation besides typeorm.<p>Just random fyi, hope someone finds it useful if they wan't to try prisma in a more batteries included package.
For those of you that want typed SQL queries check out pgtyped[1]. Using it in production and it is great for writing arbitrary SQL queries and getting full typescript support.<p>[1] <a href="https://github.com/adelsz/pgtyped" rel="nofollow">https://github.com/adelsz/pgtyped</a>
My team experimented with Prisma, and we ended up deciding to continue using raw SQL. Each of us has 10-20 years of experience writing SQL for PostgreSQL, so it wasn't a surprise that we decided against using an ORM yet again. But Prisma is probably the best ORM for Node.js that we've tried.<p>For anyone who uses PostgreSQL and is interested in Prisma, check out the following message, which has a section that includes a list of features that were deemed out-of-scope (e.g., bulk upserts):<p><a href="https://github.com/prisma/prisma/issues/4998#issuecomment-766742955" rel="nofollow">https://github.com/prisma/prisma/issues/4998#issuecomment-76...</a>
I know prism adds soo much more than just data access for your project, but for just replacing ORMs I think I really prefer the approach of <a href="https://github.com/adelsz/pgtyped" rel="nofollow">https://github.com/adelsz/pgtyped</a> where you have raw sql files / template literals and it will create types for parameters and results.
New idiom - "Well that was a complete ORM"<p>(sorry just a joke, ORMs do have their place and this looks useful. I'd think of Prisma as a lightweight ORM because it doesn't try to do "Business Rules" as they are known. But then, trying to do Business Rules will generally turn everything into a nightmare anyway so maybe lightweight ORM is the way to go)
How does it compare to MikroORM? I was researching different ORM options and almost wanted to give up until I discovered MikroORM, which is simply great.<p>Edit: After quickly going through the intro, I prefer MikroORM, it's simpler.
I have mixed feelings about Prisma. I do think it might be the best general-purpose ORM for relational databases in the Node landscape, and support for Typescript with autogenerated interfaces is <i>amazing</i>. But it seems to fall short in some ways as well (my experience with ORMs in the past is limited to ActiveRecord and Mongoose, both of which I haven't touched in years). I've only been using Prisma for ~2 months, for an internal application on which I'm the sole developer, and I'll keep using it. But given a new project and freedom to choose, there's a very good chance I'd go with another ORM depending on the use case.<p>One of the things that is most perplexing to me is the inability to autogenerate a seed file from existing data, to reseed a new database. This would be incredibly helpful for testing and working with staging data. For example, we have a staging database and staging server, and want to be able to reseed the database with some data set, but don't want to write all the data inserts by hand. I'm sure there are many ways to avoid doing this (db-specific export/import, scripting the inserts in a loop over a collection of arrays of values which you've curated by hand, or in our case just copying the sqlite db file). Still, Prisma's story around seeding and data fixtures in general currently leaves a lot to be desired.<p>There have also been many rough edges I've run into around shaping results (getting category names which have been applied to all blog posts by a given user, for example), and applying schema changes, but I'm not willing to rule out user error / inexperience for many of these
We've been using Prisma in RedwoodJS since the early days. It's a great ORM that they're constantly improving, I'm super excited for the future!
I've been hunting for the perfect solution to reduce the boilerplate in the "database-backend-frondent stack" for years and came to the conclusion:<p>There is no shortcut. And we don't need one.<p>For most non trivial applications - when I used an ORM or other abstractions over SQL - there came the point at which I needed to dive quite deep into the workings of the ORM. However, learning how your DB and SQL works is much better spend time than learning the complexities of an ORM.<p>Equally important: The time saved upfront diminishes over the lifecycle of the project. Often (not always) figuring stuff out about the abstraction costs much more time and energy than writing the boilerplate. Typing out the boilerplate is boring, that's why we hate it so much. Figuring something new out? Time flies ...<p>For my projects I've got a template for basic CRUD operations including a frontend store. Full control, easy to use and understand and only a couple of minutes to fill in the field names for a new database table or view.
I'd still take Hibernate and Entity Framework over any of the Node ORMs I've seen, including Prisma. The former usually have the feature you need to generate the queries you want, but you just haven't read enough of the docs to understand how to do so. The latter straight up lack the functionality and lack sufficient docs.
I've found this a very helpful summary of TypeScript SQL libraries: <a href="https://phiresky.github.io/blog/2020/sql-libs-for-typescript/" rel="nofollow">https://phiresky.github.io/blog/2020/sql-libs-for-typescript...</a>
Maybe it's too late to change the name. When Palo Alto Networks acquired Twistlock (Container Scanning/Security products) 2 years ago [0], they folded it in & renamed it to Prisma Cloud [1].<p>Food for thought if prisma.io team is watching.<p>[0] <a href="https://www.paloaltonetworks.com/company/press/2019/palo-alto-networks-completes-acquisition-of-twistlock" rel="nofollow">https://www.paloaltonetworks.com/company/press/2019/palo-alt...</a><p>[1] <a href="https://blog.paloaltonetworks.com/2019/11/cloud-prisma-cloud-compute-edition/" rel="nofollow">https://blog.paloaltonetworks.com/2019/11/cloud-prisma-cloud...</a>
The only time I have had to query a database using JS/TS is in React Native + SQLite. However, as far as I can tell, Prisma has no support for this stack, so TypeORM remains the only practical choice here - rough edges and all.<p>I would love to try Prisma otherwise.
I use Mikro-ORM not Prisma but I couldn't help but use an ORM in my project. Dealing with Mongodb schemas and queries directly, and modelling associations between entities, hiding certain fields on select, selectively populating joined @ManyToOne properties and all that DB stuff, was a nightmare to do by hand. Changed everything to ORM and all my problems were solved. Annotated entities, annotated properties, queries, joins, hidden fields, @OnCreate triggers etc, everything nicely handled for me. So yeah, I love ORMs. Have yet to find something nicer than .NET Entity Framework + LINQ though. That stuff is amazing.
Prisma looks quite good but what held me back is the out-of-process "engine" that handles all queries and talks to the DB. I would imagine all this inter-process communication adds a decent amount of overhead.
ORMs can be a pain. Setting up SQL schemas and managing migrations is also slow and tedious. Prisma won me over when my friend integrated it while we were working on SpaceTraders.io - things have mostly been going well. Main issue was not getting the transaction support needed for read / compute / write locks. Solved our needs for today with Redis, but we came very close to regretting the Prisma choice. Other than that the DX is amazing and the tooling is great. I think there is a lot of promise and I'm excited as they develop more advanced DB escape hatches.
I was reading through the 'where' cases to see how complex queries are <i>composed</i>. I couldn't find any examples. e.g.<p><pre><code> WHERE id IN (<subquery>)
</code></pre>
if I built up <subquery> using the ORM.<p>The other uncommon thing I look for is eliminating N+1 not just based on a given query and related entities, but given a starting collection and loading their related entities and further related entities.<p>Icing is if it can do all the above while making intermediate results asynchronously available as it works through it all.
How's the support for window functions? One thing I like about ActiveRecord is it's lazily evaluated and you compose queries. You can do p = Post.where(published: true); if x; p.where(x: true). Is that supported?<p>Checked here for window function support.
<a href="https://www.prisma.io/docs/concepts/components/prisma-client/aggregation-grouping-summarizing" rel="nofollow">https://www.prisma.io/docs/concepts/components/prisma-client...</a>
Prisma is singlehandedly making my work life easier and better every day, can’t fault it much at all. Really lovely experience and the public slack community is super helpful too!
GraphJin is very similar but in Go. Also you can either embed the whole service as a library or just the GraphQL to SQL compiler. Another advantage of GraphJin is that the you write the queries in pure GraphQL not a library specific API.<p><a href="https://github.com/dosco/graphjin" rel="nofollow">https://github.com/dosco/graphjin</a>
Used it once, it is nice(-ish) for simple use cases, but unfortunately it doesn't support working with multiple postgres schemas, which is surprising given that many complex apps use them to namespace their DBs. It was a total showstopper for us and we're back to writing raw sql for now
A few months ago I spent a week testing all of the available ORMs for JS/TS, and Prisma came out on top. That said, Prisma isn't exceptionally good in any way, it's just not as bad as the alternatives; Mikro-ORM, TypeORM, Sequelize etc.
I don't have much experience with node.js, so this question might sound silly. I don't see anything about database support in the blog post. Is Prisma enough to work with a PostgreSQL database from node.js? If not, what else am I missing?
Have been experimenting with mammoth [1] for postgresql and am quite happy with how close it is to actual SQL.<p>[1] <a href="https://github.com/Ff00ff/mammoth" rel="nofollow">https://github.com/Ff00ff/mammoth</a>
Hmm, how does this compare to sequelize? Seems like the only reason to use prisma is the GraphQL integration. If anyone has used both please share. I've being mainly using sequelize and have being quite happy with it.
I am a noob in these matters which one is advised if one is to start a GraphQL API from the beginning<p>- use DBs that directly provide GraphQL endpoints such as Fauna, Upstash, etc.
- use an ORM with more traditional DBs.<p>What are the pros and cons of each?
I think its unfortunate that Prisma is only server-side due to its Rust-based native DB module.<p>I think we can achieve a huge simplification in the industry if we develop a universal state-management solution that runs in the frontend and backend.<p>Without a consistent model, we are forced to write so much additional code to manage caching, optimistic UI updates, and offline capabilities.<p>Current RDBMS' are not ideal (most data is better represented as a graph), but because the relational model is so widely used in the backend, they are a good candidate for a universal data store on frontend and backend. And it is possible to represent graph-like structures in RDBMS' too, but it makes SQL querying very unfriendly.<p>GraphQL is an ideal language for declaratively specifying what data you need for the UI layer. If you doubt this, play around with the [Github GraphQL API][1]. It's an incredible experience to be able to explore and pluck exactly what you need. SQL and REST don't come close to this experience.<p>But GraphQL is essentially providing us a nicer way to write SQL compared to using JOINs, and to output it in a nested format for convenient rendering with nice typing. But it lacks a huge amount of flexibility.<p>So we should keep GraphQL as our API, but we should implement it client-side against a data model similar to what we use in our cloud db: SQL, and then do real-time syncing or use it as the model for our server-cache.<p>An added benefit is we don't have to worry anymore about what is local-state and what is server-persisted, or worry about choosing a good state management solution client-side.<p>The necessary pieces to make this happen don't exist at the moment, but I would have liked to use Prisma as the DB API for in-browser SQLite in the beginning.<p>I think there is then the opportunity to go full-stack and offer developers a client-side GraphQL library that talks to an in-browser SQL DB and to end the constant turnover in state-management libraries. I think every frontend developer is overwhelmed at this point by the proliferation of these libraries, and the simplicity they go seeking in things like "just use React.Context api", don't offer them what they will eventually need which is optimistic UI updates, offline modes, real-time collaboration, local-first experiences, etc.<p>I think we are all still in search of the right state management solution and it would be cool if Prisma did it!<p>[1]: <a href="https://docs.github.com/en/graphql/overview/explorer" rel="nofollow">https://docs.github.com/en/graphql/overview/explorer</a>