Hacker News new | past | comments | ask | show | jobs | submit login
Prisma – ORM for Node.js and TypeScript (prisma.io)
424 points by janpio on April 21, 2021 | hide | past | favorite | 257 comments



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.

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.

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.

Happy to answer any questions about my experience using it if anyone has any.


To be fair, Sequelize makes itself look like a joke. I normally wouldn't call out an individual library to shit on, because I understand that a lot of work has gone into it.

But I used it for years and it was always buggy. Sometimes options wouldn't work correctly because the authors liked to do that clever JavaScript thing where they write `foo = optional_thing || default_value` even for BOOLEAN options. I always had issues with it not being able to truncate/drop tables correctly that had foreign keys (it didn't attempt to order them intelligently), some of the options weren't compatible with each other even though they were orthogonal (I'm thinking about the snake_casing options and the created_at/updated_at column features), etc, etc.

It just... didn't actually work. Over many versions. I think I used it from late 3.x somewhere through 5.x.

Maybe it's awesome now, but I doubt it.


> I normally wouldn't call out an individual library to shit on, because I understand that a lot of work has gone into it.

I don't think I've ever called out on a library, or not felt truly thankful that it was there to help me. But Sequelize... man...

When I first got into Node, and got Sequelize, after working for years with .NET, Entity Framework, NHibernate and the likes, it just felt like horrible-everything. I've forced myself to use it in some projects, because node is cheap, and I kept thinking that I'm missing something. Some brilliance behind the questionable... everything. No. I can't even bring myself to think about that design mess. Sorry for the rant, Sequelize makes me feel insecure, and little, in the chaos of the Universe.


Same. Honestly, I only have not-nice things to say about Node, JavaScript, and its ecosystem, so I should probably just shut up.

But I was like you- I picked up Node because we had an existing project in it. It only used libraries that had the most "stars" or whatever on NPM and they were all pretty deficient (and some were shockingly slow). But none were so frustrating as Sequelize.


I only have not-nice things to say about Node, JavaScript, and its ecosystem,

The NPM ecosystem can be frustrating and annoying sometimes but today nodejs is a solid server runtime.


Node-the-software-project is a feat of engineering.

But the API, the client language, the ecosystem, and even the idea are bad, IMO.

Bad API example: Tell me how to deal with time zones in Node without pulling in a big third party library. Answer: You can't. So, a "scalable" backend platform can't handle datetimes from different time zones? Neat!

"Well, that's JavaScript's fault" you say. Node certainly has (a few) APIs that don't apply to the frontend, so why not add more of the important ones for backend work?

Or, maybe JavaScript really just doesn't belong on the backend at all...

Not to mention that it's single-threaded, so the answer to scaling up better is to just run more instances. -_-

See? I should've stayed quiet. ;)


> Not to mention that it's single-threaded, so the answer to scaling up better is to just run more instances.

Yeah, this sucks. It's impossible to do any actual processing in javascript since functions that don't return immediately will clog the event loop. Why can't we have javascript code that runs asynchronously and reports completion and results as events? Even browsers seem to have this now. I tracked issue #2133 on GitHub for a long time and nothing materialized.

Not sure if Node.js still lacks this feature. If so then it means Node.js is nothing but an I/O scripting platform. You get events and you make the system copy data from some source to some destination in response. Any sort of actual processing means latency becomes unacceptably high.


Node has worker threads, which are basically the equivalent of Web Workers from browsers, with similar message based API


Wow really? Since when/what version? I have to see it. How did they solve the serialization issue? If I remember correctly that was necessary to pass objects between threads.


I think they became stable in v12. I'm actually not sure about the internal implementation, but from user's point of view you can pass almost [1] any value via a message and you will get a copy on the other side. You can even share memory between threads with SharedArrayBuffer.

[1] https://nodejs.org/api/worker_threads.html#worker_threads_po...


No it's not. Even if they fixed all the bugs which I doubt the API design is bad.


You haven't mentioned the best query builder I've ever used - Elixir's Ecto. I encourage to give both Elixir and Ecto a try as they're worth learning.


Prisma 2 is a delight to use. Gave it a try after typeorm-model-generator’s author suggested using something else than TypeORM. Prisma blows other JS/TS ORM libraries out of the water. It integrates so well with VS Code


> GraphQL N+1 issues are a thing of the past.

Huh, care to explain this one? I’m using Prisma with Apollo Server without doing anything fancy in my resolvers. I just assumed I’m getting N+1 issues but didn’t bother to optimize yet.


in short the separate engine process allows them to combine every findX call you make during one tick of the event loop into a single SQL query, following the dataloader pattern, so you don’t have to implement it yourself. i’m sure @nikolasburk can shed some more light if you’re interested.


This isn't true. Prisma currently only supports batching of findUnique queries: https://www.prisma.io/docs/guides/performance-and-optimizati...

There's an issue open for findMany as well, but it hasn't moved in over a year: https://github.com/prisma/prisma/issues/1477


Is 933 open issues a warning sign? Seems excessive.


If it's optimizing with figuring the calls in a single tick out, one can probably still optimize further by using db specific features, like postgres supports json_agg which let's you not only break n+1 in a way, but also prevents cartesian product explosion with joins


I have a feeling that does not work at all in non-trivial scenarios, e.g. if both composite keys and date range matching are required to resolve a reference.


Could you test this and get back to us with the results?


No need to test really - indeed more complicated cases are not covered by this yet. But happy to look at any Github issues with reproduction of cases that are not working, but could or should work to make your life better.


There are pretty extensive docs on how Prisma optimises n+1 queries: https://www.prisma.io/docs/guides/performance-and-optimizati...


The examples are still for a trivial case of joining one thing to many things. What about joining one thing to many things that join to many things that join to one thing that joins to many things that join to many things that join to many things?


For the interested, here's an article that explains how the dataloader manages to pull this off https://www.mikealche.com/software-development/advanced-prom...


Very interesting, thank you. Is there a way for me to monitor what Prisma is doing in my use case?


using `new PrismaClient({ log: ["query"] });` it'll log all the actual sql queries it's running, so you'll be able to see if queries are being combined


Awesome, thanks!

Turns out Prisma wasn’t optimizing my queries, I think it has to do with me not using the sub query API and instead querying tables seperately (e.g posts.findMany({ where: { owner: user } }))


Pleeeeeease open an issue if you have a simple reproduction for this. That will make sure we will make sure this _does_ work (sooner or later, not committing to a timeline here on HN of course :p).


Having used in the past both Django and Rails ORMs, I totally agree with this. Prisma is awesome, it's been the best experience I've ever had so far with SQL databases.


Laravel has Eloquent and it’s an absolute delight to use. I’ve used Rails and Django, and I always felt as if I was fighting the ORM, where as with Eloquent everything just seems natural.


I am a former PHP developer, absolutely loved Laravel and adored Eloquent as the ideal ORM. I switched to Node/JavaScript in 2015 and have been chasing a good ORM since, nothing could ever compare with Eloquent in Nodeland, I all but gave up and started building my own, in TypeScript to match Eloquent as much as possible.

Then Nikolas Burk reached out to me and did his absolute best to convert me, but I was stuck in my ways, it HAD to behave like Eloquent, or it was not good enough and that DSL layer? No thanks, I don't like it.

Started writing code for my own ORM when I was like "What the heck am I doing? this gets me nowhere" And messaged Nikolas back saying I was dropping everything and planned to give Prisma a real solid try.

I'm so glad I did. I LOVE it now. I consider it a part of my GOAT stack.


:heart:


> GraphQL N+1 issues are a thing of the past.

This one I will believe when I see it. It would be a general solution to cache invalidation.


You can disable caching if that's a problem for your application. But the idea is that DataLoader simply holds onto promises that requested an entity, and fires them all in batches according to a scheduler function. In Node.js, the default is to use scheduler magic (relies on how the event loop works).


Here's a small article that explains how the dataloader pattern works under the hood https://www.mikealche.com/software-development/advanced-prom...


I wish Prisma had built-in Opentelemetry support for tracing and metrics.

They have a whole blog article about why monitoring is important but don't seem come with it: https://www.prisma.io/blog/monitoring-best-practices-monitor...


It's unclear what's the pros are of Prisma compared to TypeORM. I do find it confusing I always thought Prisma was GraphQL related.


> I do find it confusing I always thought Prisma was GraphQL related.

This is a common misconception that stems from our history as a company and being early contributors to the GraphQL ecosystem. With the move to Prisma 2 however, there is no native GraphQL layer in Prisma any more. I've talked about this extensively in a recent livestream on Youtube [1] if you want to learn more :) There's also this article "How Prisma and GraphQL fit together" [2] that explains the historic dimensions of this if you're interested.

> It's unclear what's the pros are of Prisma compared to TypeORM.

I guess you could argue that one benefit is the superior type-safety Prisma provides [3]. Other folks have also called out that they prefer way how data is modeled with Prisma (via the Prisma schema), the migration system as well as the active maintenance, regular releases, the active community, the support and thorough documentation of Prisma. Ultimately it'll come down to your personal preference though which one is the more appropriate for your project :)

[1] https://www.youtube.com/watch?v=hMWMPpy4ta4&list=PLz8Iz-Fnk_...

[2] https://www.prisma.io/blog/prisma-and-graphql-mfl5y2r7t49c

[3] https://www.prisma.io/docs/concepts/more/comparisons/prisma-...


I skipped Prisma when I discovered that you were supposed to toss your schema in a single file. There's some ways to get around this but amazed that this is the official way


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.

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]

[1] https://github.com/sqljoy/sqljoy/blob/master/docs/pages/orm....


> I think the industry is strangely blind to the consequences of this

Well, I think if you're not blind to those things to some degree, you're stuck forever in a best-practices search, which I feel like more often than not ends up implying you have to rewrite your stack.

For example, I do most of my development thru Hasura now. It writes insanely performant SQL queries translated directly from GraphQL. As a data consumer, you only have to worry about the shape of the data you want, and none of the implementation details. There is one endpoint, and you pass it queries against the schema it exposes. Implementation details are abstracted heavily here, but in a "good" separation of concerns way.

I will never hand-write SQL again. Why would I? My front-ends can load everything in my data model expressively and with type safety thanks to graphql-codegen.

Now imagine you read this and realize you work in a completely different method and this answers some of your problems. You may start parallel development, but you certainly can't stop forward progress just to evaluate your stack. So I think the blinders are key to progressing in the short term despite the productivity losses you're taking in the long term, and then when you phrase it like this it is surely no surprise companies behave like that.


> Well, I think if you're not blind to those things to some degree, you're stuck forever in a best-practices search, which I feel like more often than not ends up implying you have to rewrite your stack.

Can you expand on that? The chain of reasoning is probably clear to you, but I can't follow it.

> For example, I do most of my development thru Hasura now. It writes insanely performant SQL queries translated directly from GraphQL. As a data consumer, you only have to worry about the shape of the data you want, and none of the implementation details. There is one endpoint, and you pass it queries against the schema it exposes. Implementation details are abstracted heavily here, but in a "good" separation of concerns way.

I like Hasura a lot, I've advocated it at a previous company I worked for, and after many months of meetings I got it approved to add to our stack - which was then never prioritized, but that's another story.

> I will never hand-write SQL again. Why would I? My front-ends can load everything in my data model expressively and with type safety thanks to graphql-codegen.

I don't think writing GraphQL is superior to writing SQL, and again you have to think not just in terms of GraphQL but also in terms of how Hasura translates that to SQL under the hood. I am slightly biased though, because I'm creating on a framework to query the db using SQL from the frontend, I'm curious how you think that would compare with Hasura: https://github.com/sqljoy/sqljoy

> Now imagine you read this and realize you work in a completely different method and this answers some of your problems. You may start parallel development, but you certainly can't stop forward progress just to evaluate your stack.

I'm not really sure how this connects to my argument about complexity, although I think the link is clear to you, can you expand on that?


> I now believe it's better to use SQL directly than to use an ORM

I reached the same conclusion. Everything is so much simpler that way. I ask the database for the data, the database gives it to me. That's the end of it.

I used to like highly abstract libraries but the truth is they provide the lowest common denominator in features at a huge cost in complexity. Now I'd rather work as closely to the implementation as possible. I feel like I actually understand how stuff works now.


ORMs are great when you have to update large graphs of objects. They are not great for querying. The performance of querying is so unpredictable in an ORM that it's better to handle these read queries by hand. Things that tend to screw up performance are the N+1 query problem and generated SQL causing bad query plans. One needs to get to know one's database query planner in order to get the best performance, and that requires dealing with the raw SQL.


"The ORM saves us time working, but they don't save us time learning." Paraphrasing Joel Spolsky. Also, I hate every single time he is right, which at this point is daily.


PureORM[1] is an implemented thought experiment on what a "pure" object mapping library would look like - where you write regular native SQL and receive back properly structured (nested) pure business objects.

It would contrast against traditional ("stateful") ORMs which use query builders (rather than raw SQL) to return database-aware (rather than pure) objects.

The name pureORM reflects both that it is pure "ORM" (there is no query builder dimension) as well as the purity of the mapped Objects.

[1] https://github.com/craigmichaelmartin/pure-orm


I do agree with the argument that you need to understand both, the ORM and SQL. The really annoying part is that you also need to understand how the ORM translates queries into SQL to some degree.

I find ORMs very convenient and useful for simple queries, and especially for inserts/updates with many-to-many relations. And that is a very large part of a typical application. But you can very quickly meet the limits of an ORM with more complex queries, and I find it easier to just step down to SQL in those cases before learning the more complex parts of the ORM.


I despise ORMs. I don't judge programmers very often, but invariably if there is an ORM, there is a fragile, tightly coupled, difficult-to-maintain project nearby


currently staring at what's left in a large legacy rails/activerecord app figuring out what to do with. the suffering is real.


What obstacles are you hitting?


the active record pattern, as it is commonly used, does not set you up well to lift individual components out of a monolith.


Perhaps you already have ideas on how to solve for this situation, but in case it helps: forcing active record model accesses to go via a layer of indirection can help with untangling this sort of thing: https://kellysutton.com/2019/10/29/taming-large-rails-codeba...

That said, for microservice extraction specifically, a bit of creative metaprogramming/monkey patching of AR to identify & categorize your callers is probably easier.


the data access abstraction layer is absolutely the "right" move. just hard to stomach the amount of work involved that's ultimately kind of throw-away.

the problem with activerecord is most people usually don't put this in place to begin with because the active record pattern makes it easy not to, which creates a massive pile of spaghetti code long term.


to wit: how exactly does Prisma make money?



I see, their long term goal is to make and sell an enterprise data access layer / tooling on to of databases and data sources, like those used inside big tech companies to mediate developer access to data.


I think they don't, yet. I'm sure they have a plan for that, probably some kind of managed cloud service.


I have adopted Prisma in my latest project and I have mixed feelings about it.

- The generated client is top-tier. Fully specified in TypeScript with intelligent types that can be extended by the user.

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

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

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.

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.

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.


Thanks for sharing your thoughts. The lack of Client extensibility is definitely a shortcoming right now. We're thinking of ways to wrap the Client a bit better. Middleware works is a decent workaround, but doesn't fit all these use cases.

On testing, we're working on some documentation right now on how to mock the Prisma Client. I could see us generating a mockable client to help with this.


Spinning up a temporary e.g. postmaster instance for a test off a pre-built data directory (bonus: use zfs or btrfs or similar snapshots to make this even faster) is, IME, while still effectively "completely recreate" also both much more robust than wrap-in-transaction and generally not the slowest thing about your tests (and of course allows reliable parallelisation).

Bit of setup effort but well worth it IME even in situations where your tooling isn't strongly pushing you towards the approach.


While depending on a live DB for testing isn't great, why not just blow away database state after every test by running TRUNCATE / DELETE against all tables? DELETE especially is very fast (couple ms) if you're not inserting large amounts of data during your test runs.


1) You can't run tests in parallel.

2) It is at least an order of magnitude slower than not using a database at all.

Combined this makes for very slow tests. Certainly for larger applications with thousands of tests.

Having said that, I don't think there is a single right answer to the problem of testing applications that use a database and your suggestion still can be a valid solution.


1) Actuallly you can, in multiple ways:

- parallel sessions on one database just like multiple clients accessing your web app at the same time

- create multiple copies of a schema and run different independent testusite legs on each (you just have to pass X-Test-Schema in the HTTP header to select on which schema should the web app operate)

- I have an even better trick up my sleeve for testing an app with a real DB backend, and that's passing X-Test-Force-Time header that allows me to override time under which app and DB operates per-request, so I can test even time dependent behavior, like timeouts, timed queues and events, time dependent DB queries, etc. And in general just have a stable predicatble timestamps in the database for each test run and be able to compare database states at the end of the test run and check the diff for irregularities against the previous test runs, or known good state.

I have to patch postgresql to allow for output of now() current_timestamp, etc. to be overridable per session, but it's absolutely worth it to be able to simulate/control the flow of time across the whole stack.

You can actually do a lot of useful stuff with schemas and search_paths, without disrupting connected clients, and without having to re-create databases, all in a transactional/atomic manner, that is very useful for testing.


If you run postgres, make a schema per test and do a cascading drop to the schema after the test. Name the schema either by the test or with a random string. Same applies for SQL Server and on MySQL you'd do this with databases instead.

Parallelizes quite well when we test the engines at Prisma...


Hey totally off-topic maybe as it's not prisma-related/required.

What do you think of using something like msw.io for mocking your endpoints as needed?


> msw.io

The website is https://mswjs.io/


“ Application developers should care about data – not SQL”

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.


Hard disagree. If you’re not leveraging the features of your SQL database to make your life easier, your queries faster, and keep your data consistent & valid, then you probably didn’t need SQL to begin with.

Which, yes, is a common state for many applications to be in. Wresting with SQL while using it very poorly in an effort to act like they’re not.

[edit] disagree with the quotes statement, that is, agree with the post. I worded that poorly.

[edit edit] I've been thinking about what you get from using an RDBMS while avoiding writing or knowing much about SQL or using any of the probably-extremely-nice features of your particular SQL DB (why is everyone always so eager to be DB-agnostic? If you do it right your DB will survive, and greatly ease, several rewrites of your application!) and I'm coming up with:

1) basic locking (I assume you don't want to know how to actually use transactions, beyond what your ORM does automatically, so you're just getting the basics), and

2) some probably-badly-insufficient indices, and

3) an ORM should at least get you a little normalization if you just follow patterns from its docs & examples, I suppose, though you're gonna need to understand some SQL to get much benefit out of it in your DB design and in your use of the DB, so...

An entire RDBMS seems like serious overkill if that's all you're really using.


This statement is certainly provocative (great that it was the first thing picked up here :D) but I'm happy to explain our rationale for this a bit more.

SQL is an impressive technology and has stood the test of time! Yet, we claim that it's not the best tool for application developers who are paid to implement value-adding features for their organizations.

SQL is complex, it's easy to shoot yourself in the foot with and its data model (relational data / tables) is far away from the one application developers have (nested data / objects) when working in JS/TS. Mapping relational data to objects incurs a mental as well as a practical cost!

This is why we believe that in the majority of cases (which for most apps are fairly straightforward CRUD operations) developers shouldn't pay that cost. They should have an API that feels natural and makes them productive. That being said, for the 5% of queries that need certain optimizations, Prisma allows you to drop down to raw SQL and make sure your desired SQL statements are sent to the DB.

I see Prisma somewhat analogous to GraphQL on the frontend, where a similar claim could be: "Frontend developers should care about data, not REST endpoints". GraphQL liberates frontend developers from thinking about where to get their data from and how to assemble it into the structures they need. Prisma does the same by giving application developers a familiar and intuitive API.


> SQL is an impressive technology and has stood the test of time! Yet, we claim that it's not the best tool for application developers who are paid to implement value-adding features for their organizations.

I'm not sure about that. We've recently switched from JavaScript based querying code to mostly raw SQL, and we've reduced our code to about 25% of what it was, and it's much simpler to understand than it was before.

> I see Prisma somewhat analogous to GraphQL on the frontend, where a similar claim could be: "Frontend developers should care about data, not REST endpoints".

I'm not sure about Prisma, but IMO that GraphQL model isn't great. Realistically (for performance, etc) it will make a difference where that data came from. Not for super simple queries, but super-simple queries are super simple to do with REST/SQL anyway. I also feel like this distinction between front-end and back-end developers isn't great.

The GraphQL approach also leads to deployment issue with non-web clients. e.g. it can take a day to get an iOS build released, and there is no way to force users of older clients to update, so this can take months. So fixing a bad query is difficult. With a proper backend you can just swap out the query.


I think the strongest argument for or against an ORM is that it changes the structure of your code, how you reason about your data.

Things like performance, ease of use, reusability, schema generation etc are minor points and not the big issue you need to think about.

Personally I feel that an ORM will lead your project in the wrong direction and your database schema will suffer.

ORMs will usually mix both reading and writing within the same class, but those are not the same thing.

Readers will have different views of your model depending on who they are or what time it is. Writing on the other hand must fulfill your constraints.

Other common mistakes of ORMs is mapping one class with one table, but your data consist of series of relationships, that can’t be explained with a class.

An ORM creates an illusion that your data is actually the class entities in your repository. This can have the effect of creating constraints in your own mental model of your data, thus making it harder to evolve your schema because you are to fixed on how your classes are designed.


Great comment! I’ve been thinking about a tangentially related subject:

TypeScript is like a relational database (and other structural statically typed languages) of your application behavior, allowing you to query the current possible states of your data at any given point in your application code.

I’ve been thinking about this more since TypeScript got template-string generics, and folks have experimented with eg type-checking SQL strings back into application code.

This is the piece that draws me to an ORM: that it brings the “relational model” into application code, NOT the “object” part of an ORM.

Does anyone have any opinions about which language ecosystems create the most effective type-mappings between a RDBMS and application language?

Part of me also wonders if there’s a ton of time lost on mapping between languages like this when the tooling that’s really missing (afaik) is better tooling for producing, consuming, and generally interacting with SQL and db schemas as the data runs through your application pipeline.


> We've recently switched from JavaScript based querying code to mostly raw SQL

Curious what your use case is, because in every codebase I've worked on, people pretty quickly get tired of writing out SELECT * FROM [table] WHERE id=? and UPDATE [table] SET field=? and looping over database cursors all day, and you end up with a half-done, buggy non-ORM.


We're using knex.js, so we're not messing around with database cursors (we get an array of objects), and we don't have to write out the columns for inserts/updates. We also sometimes use the knex query builder for super trivial selects from a single table.

But for anything more complex than that (e.g. SELECT queries joining several tables) we're using knex.raw so the query itself is raw SQL where we're very much thinking in the relational model rather than in terms of objects.


Ahh that makes more sense. My preference has been to use an ORM for your normal CRUD, but write more advanced queries using SQL (or sometimes just write the where clause using SQL, if the select is otherwise simple.) I am not a fan of query builders.


> Yet, we claim that it's not the best tool for application developers who are paid to implement value-adding features for their organizations.

You have two options when marrying RDBMS SQL and OO: either mismanage the relational data so that developers can use a class hierarchy, or stop using a class hierarchy to represent data.

IME, applications come and go. They get rewritten, thrown away, obsoleted. The database, however, is there to stay. Mismanaging the data purely so that the application developers don't have to touch all that icky relational stuff almost always results in more work for less returns.


Prisma does not use a class hierarchy that are mapped directly to tables. Instead we take advantage of structural typing to give you a very smooth developer experience that is faithful to the relational model.


I think SQL gets a lot of undeserved praise that I’m having a difficult time understanding. The only impressive thing about SQL is its prevalence but that’s a pretty poor yardstick unless one thinks that an appeal to popularity is an indicator of quality.

Now let me count the ways in which SQL is bad:

- it composes poorly due to its unwieldy cobolesque syntax

- it is a leaky abstraction revealing a lot of underlying implementation tradeoffs

- it doesn’t properly implement Cobb’s relational model

- it is poorly standardized with a ton of proprietary extensions and alterations present in virtually every implementation

- it is still poorly supported by tools because the model metadata lacks any standard interface to make universal tooling possible

A lot of the praise for SQL is just bandwagon hopping and cargo cult behaviour or a lack of vision by most people of how things could be much better


The great thing about SQL is the relational model. You typically can't compose parts of SQL queries easily from the programming language calling it, but SQL queries themselves compose incredibly flexibly. The result of of a SELECT query is itself a "relation" that can be queried just like a table.

I do wish someone would create a compile-to-sql language that adds variables and basic conditional support. I think T-SQL (SQL Server) has a lot of this, but it's missing in the Open Source world.


> I think T-SQL (SQL Server) has a lot of this, but it's missing in the Open Source world.

Every database with a procedural language based on SQL (Oracle PL/SQL, Postgres PL/pgSQL, etc.) has it, T-SQL just doesn’t segregate procedural and declarative code the way most engines do. Mostly, I think this is a negative for T-SQL, but sometimes segregation means there’s no way to do one-off procedural scripts without making and then calling a procedure; but Postgres, for example, since 9.0 has the DO statement for this use case (which supports any procedural language installed in the DB, but defaults to PL/pgSQL.)


I don't think SQL does compose well. Queries aren't first-class values in SQL.

I can write a query that queries the result of another query, but I can't store a query and then pass that somewhere to query from.


Maybe the WITH statement and CTEs can help you, if I understood correctly your complaint.

Check the example at the beginning of https://www.postgresql.org/docs/13/queries-with.html


What are you comparing it to? What is the better alternative?


SQL isn't the only RDMS language, but it is the one with 50 years of incumbency.

Postgres before it was called 'PostgreSQL', used something called QUEL, which is quite a bit cleaner. There is also Microsoft's LINQ.

A typical programming language can express all that SQL can. In a way, an ORM is a cross-compiler from your programming language to SQL.


> A typical programming language can express all that SQL can. In a way, an ORM is a cross-compiler from your programming language to SQL.

SQL has always been a sort a magical, black-box in that you have very limited control over the query planner, and have to hint at it to do the right thing. (I guess `EXPLAIN` allows you to peak in the box a little)

I found [Apache Calcite](https://calcite.apache.org/docs/algebra.html) rather interesting. It provides the primitives that a DBMS is built from.

I would be interested to compare how PSQL is built in comparison. I would love a more layered/pluggable database that everyone can build off of. I think FoundationDB had this approach.


LINQ is pretty much a clone of SQL. Its nice, I agree, but do you think its materially different?

SQL could benefit from being language-integrated, yes. Very few languages can model relational algebra types though, if thats a thing you care about (it certainly is for me).


An ORM API on the other hand is completely meaningless adhoc "whatever works". It doesn't have grounding in anything.


I'm making no statements about ORMs. I'm just picking apart the mindless "SQL is the bee's knees" mantra so prevalent on HN.


Would be interested in hearing what database and query language you think are better?


I think Datalog as implemented by Datomic has a much better syntax though I don't like Datomic per se.


Oh don’t get me wrong - ORMs have their place and do enable higher agility. They do seem magical the first time you encounter them.

What I object to is the blanket “shouldn’t care about sql” statement because that’s what empowers people to use the ORM indiscriminately without understanding what’s under it (an understanding for which SQL is relevant) and then it’s the non-value-adding developers’ job to come in and untangle the mess, usually could have been avoided by dropping down one level, looking at the SQL that was generated (or maybe analyzing the query plan - again kind of hard if you don’t understand SQL) and realizing the ORM is doing something crazy.


Exactly. ORMs, especially powerful and mature ones like DjangoORM or Active Record, can help with productivity and maintainability and for more complex use cases you have the chance to switch to raw SQL; However, it’s important for developers to know what’s happening under the hood and to know what will happen if they use a certain feature of an ORM. I can’t even count the number of times that when one of my coworkers and I tried to fix a performance issue, and after digging deep into queries and mechanics of the ORM, we’ve realized how ORM heed so much complexity from them and, how a certain data structure design and coding in a certain way can result in an inefficient data flow.


Actually nested objects kinda suck even for frontend devs, if you're trying to keep things in sync efficiently.

Often times it's much better to be able to lookup some object by ID of the entity from some Map, than consuming endpoints returning some crazy nested partial data for the current view.

Depends on how much your app relies on client side caching and incremental sync of data.


As soon as you need a lot of complex data all displayed in one place plus high performance, you're gonna find you need a list, not a hierarchy. You need to be able to treat the data coming in like a stream, not to traverse anything. Read the row, maybe do some state-machine stuff to decide how to treat it, then drop all that on the floor and move to the next row.

This is more-true the less efficient the language is that you're using. I see people screw this up when writing e.g. dashboards that source their data from SQL, while leaning on an ORM, all the time, and it always kills performance (talking tens of seconds to minutes for things that should take a single-digit count of seconds, at worst).

What's good at turning complex relational data into a list, and fast? Yep, SQL.


That too. Though I used efficiently in a sense of minimizing the communication between client/server by normalizing data and just sending changes.

Which is easier if you keep data normalized on the client side too, and look up related objects in a Map when needed, instead of keeping multiple copies of objects representing the same entity everywhere in your client code in some/vasrious denormalized forms.

It would actually be very nice for some use cases if I had a SQL interface to local data on the client side too, so that I can query/join them up arbitrarily as needed from what's loaded up to client storage. That's unpleasant to do in the browser with current platform APIs.

Basically I want WebSQL back in some form, instead of this IndexedDB thing. :)


> if you're trying to keep things in sync efficiently

I think we are all abusing GraphQL in this sense. Caching GraphQL data is a world of hurt.

The easiest way to sync is if your data is in the same model as the way it is stored, which is normalized if using SQL. Even when we do normalize most people will represent a 1-M as an array of foreign keys: `posts.comments = [1, 2, 3]` when in the db we use a foreign key on the `comment` entity: `comment.post_id = 1`. This causes such headaches for optimistic UI updates. It's a mess.

We should be using GraphQL to retrieve data and then render it directly.

Ultimately, I think we should be implementing our GraphQL API client-side against a local SQL DB that acts as a pass-through cache. Then your entire app runs offline and instantly respond to queries eliminating the need for a separate client-side cache, and you don't have to worry about about data model mismatch anymore.

> Depends on how much

I think all apps want this - unless they are SSRing with <100ms on every action.


Ha :) Yeah, I just replied in a similar vein in the other reply to my original comment. Agreed!


I keep sayin' it - SQL in the browser!

I'd love to get rid of all these client-side state management libraries and just drop an SQL DB in there and allow components to subscribe to changes to queries on record updates, and then sync it to a cloud DB.

One interesting problem I came across is how to know when your local DB has enough records to be able to run a given query. E.g. If your team has 100K tasks in their cloud DB, and you run `SELECT * FROM tasks WHERE tasks.assignee = user.id`, and then another query of `SELECT * FROM tasks WHERE tasks.assignee = user.id AND tasks.completed = true`, the second task is a subset of the first, so it doesn't need to hit the server except to check if any updates were made to the tasks table. Then apply this to all queries including ones with joins. Actually some fun relational algebra can be used figure it out - one of the perks of working with SQL. The problem is actually similar to how to [incrementally maintain a materialized view](https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...) which Postgres actually does not support (Oracle does).


If everything is simple, then so are the SQL queries.


I agree.

That statement is so wrong. All ORMs suffer from leaky abstraction. And good ORMs would never hide that fact, nor would they try to deny native access to the database and force a all-or-nothing principle onto the developer.

If "application developers" care about data that is stored in a relational database, they have to care about the database itself and the access patterns (SQL).

ORMs _only_ provide convenience! They cannot substitute knowledge about the underlying technologies. And they cannot hide complexity, but shift it to the ORM.


I agree, and find the reluctance to care about SQL interesting.

Sure, SQL is not perfect, and has it’s flaws. But instead of learning an established, declarative language designed for data access, there are developers that would much rather learn new tools and libraries to avoid it. If you need to use another programming language for data access, you’re off looking for another ORM. Or the next, better ORM of the year comes out.

There are stored procedures at my work that are more than 20 years old. They don’t need to change when the application code calling it is uplifted.

It’s not that much different than a modern React dev not wanting to understand how a plain HTML form works.


Right. Data management is a hard problem. Just look at SQL query planners, they are insane - but they are not perfect, and they only work on the premises you provide (schemas, indexes, memory, etc). The idea that you could introduce a library that fixes all data management issues for you is not well considered.


Yeah, ORMs were marketed as "if your database changes, you don't have to change anything!". What about if your programming language changes? You have to learn an entirely new ORM each time. What about all of the people who have to read your code? Which ORM's do they know? Everyone knows SQL.


> What about if your programming language changes?

Very rarely does an organization completely rewrite their products in completely different languages. And if they do, I'd hope they take the due diligence to investigate the tools they plan to switch to and make sure they are a good fit.

> You have to learn an entirely new ORM each time.

You have to learn an entirely new everything each time. ORM's aren't anything different. New language = new paradigm, new routing, new templating, new everything.

> What about all of the people who have to read your code?

What about them?

> Which ORM's do they know?

I'd assume that the team would have conversations to gather information on what ORM's everyone knows and decide which is best for their team. I don't see how this is an issue. Do you just randomly pull people into your company and just like, let them wander aimlessly?

> Everyone knows SQL.

No they don't. And if they did, a lot of stuff isn't huge massive complicated apps, most are basic CRUD apps where ORM's do all the lifting for 98% of the stuff needed done. A lot of us haven't touched SQL in so long that we couldn't construct a simple SELECT statement if we tried. I know because I wrote SQL from 2000-2014, but then tried to write a simple SELECT statement in a `pg` CLI to fetch some stuff and completely forgot how to do it.


The statement is true, IMO. The problem is that ORMs don't ever solve the problem (and I don't believe they CAN).

We should only have to care about data and not SQL. But the reality is that we very much do have to care about SQL.


I agree. Clean and efficient abstractions (oxymoron?) to handle data management just doesn't exist. ORMs are great until they're not, at which point you do need to understand SQL and idiosyncrasies of the underlying relational database.

I tend to prefer avoiding ORMs as a preference because of this but unfortunately, development expectations in the current culture don't pad time enough to do this, so you're often reliant on an ORM and wait until things break before someone paying the bills will be swayed to allow developers to go in and clean things up. The "wait until it breaks to go fix and redesign everything" mentality isn't unique to ORMs though, it plagues any abstraction, framework, library, whatever that gets enough done to supply something mostly functional in a shorter time scale.


Not just this - I'm currently working on an application built by people with no understanding of databases beyond "they're kind of like key value stores". No attention is given to transactions, stale updates, locking mechanisms or anything more complicated than the equivalent of SELECT and UPDATE. It is an HTTP service running on many threads, so the problem is only amplified.

If you're going to use a SQL database, you need to understand what it is and what rules to obey to get its benefits. Hiding that complexity away makes people think they're writing a fast, concurrent high-availability (TM) service while it's actually a ball of mud that sometimes gets into the right shape.


While some people are hard lined enough on their anti-ORM stance that it sort of becomes weird, I agree with you that my beef with ORMs comes from being burned before a couple of times by a super inefficient aggregation ActiveRecord did on GROUP BY queries that it 1. not only took a really long time to figure out why a particular page in our app was loading slow but 2. we ended up having to write raw SQL to fix it.

I think the answer of whether to use one depends on the type and load/volume of app you're working with combined with the dynamics, size, and skill level of your team(s). I'm extremely comfortable writing, profiling, query planning, and debugging SQL queries. Others aren't, and therefore having an ORM to query data in the DB with the syntax of the language you're using in your projects makes way more sense, if nothing other in order to speed your team up.


Not sure when you used ActiveRecord, but slow query logging helps a lot to identify these issues. I also appreciate that in the docs for ActiveRecord they make it very clear that jumping to raw SQL is absolutely fine and normal, and they make the interface for doing that very nice.


I have yet to find the person who, being comfortable with both raw SQL and a particular ORM, to, when faced with the choice of learning a new ORM or using raw SQL, reach for the new ORM. To be honest, I haven't met in person anyone who reached for the familiar ORM, either, for anything complex in a team environment, but that's a more aggressive statement.

I mention this because to my mind it reinforces my belief that learning an ORM (which I've done in the past, and then never used again) isn't worth the effort; learning SQL is, if for no other reason than it's portable (and you'll have to learn it anyway when the ORM's abstraction leaks).


The answers is both. Devs cannot work on a system (involving "data") without knowing about that data/how it works/how it is structured and how to store and retrieve it. Pretending you can do one without the other leads to a boatload of issues in the medium and long term.


if you define yourself as a developer do yourself a favour and learn SQL. It's probably much easier than whatever programming language you use and will avoid you many headaches.


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.

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 on the RedwoodJS core team and can 100% confirm early performance issues. That's no longer been the case recently. I've seen bulk operations perform just fine and even data pipeline use cases. Now the issue is DB performance in a serverless infra, which needs extra set up and config to perform consistently and at scale.


> they have an "engine-core" package that downloads a binary server behind the scenes and runs it on a free port in the background

O_____O


Yup, would not touch Prisma with a 10 foot pole


That extra layer gives them flexibility to combine queries and cache


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?

  - 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
FYI - This is after fairly brief research. Not guaranteed 100% accurate.


Afaik many of these issues are why the company i worked for ended up giving up on Prisma. We ended up switching to Objection.js.

The biggest issue was the transactions which was a non starter for us. It wasn't very helpful when after explaining our use case and being told "we are doing it wrong" in the GH discussions, and instead were told to write rollback code manually instead of using transactions was a very poor answer.


I can clarify one point, relative to migration rollbacks. We indeed chose not to implement down migrations as they are in most other migration tools. Down migrations are useful in two scenarios: in development, when you are iterating on a migration or switching branches, and when deploying, when something goes wrong.

- In development, we think we already have a better solution. Migrate will tell you when there is a discrepancy between your migrations and the actual schema of your dev database, and offer to resolve it for you.

- In production, currently, we will diagnose the problem for you. But indeed, rollbacks are manual: you use `migrate resolve` to mark the migration as rolled back or forward, but the action of rolling back is manual. So I would say it _is_ supported, not just as convenient and automated as the rest of the workflows. Down migrations are somewhat rare in real production scenarios, and we are looking into better ways to help users recover from failed migrations.


This sounds like you aren't able to easily rollback a release that had migrations


No question that there are cases where down migrations are a solution that works. In my experience though, these cases are more limited than you might think. There are a lot of presuppositions that need to hold for a down migration to "just work":

- The migration is reversible in the first place. It did not drop or irreversibly alter anything (table/column) the previous version of the application code was using.

- The up migration ran to the end, it did not fail at some step in the middle.

- The down migration actually works. Are your down migrations tested?

- You have a small enough data set that the rollback will not take hours/bring down your application by locking tables.

There are two major avenues for migration tools to be more helpful when a deployment fails:

- Give a short path to recovery that you can take without messing things up even more in a panic scenario

- Guide you towards patterns that can make deploying and recovering from bad deployment painless, i.e. forward-only thinking, expand-and-contract pattern, etc.

We're looking into how we can best help in these areas. It could very well mean we'll have down migrations (we're hearing users who want them, and we definitely want these concerns addressed).


So future features notwithstanding, is the typical Prisma workflow that if a migration failed during a production deploy the developer would have to manually work out how to fix it while the application is down?


As of now there is no strong opinionation in the tool — you could absolutely maintain a set of down migrations to recover from bad deployments next to your regular migrations, and apply the relevant one (manually, admittedly) in case of problem.


Take a backup before you do any prod migrations.


Sure we do that before every release as well. But a rollback is a much less invasive surgical fix compared to a full database restore. You're down while the new db instance spins up and any writes in the meantime will be lost.

You can also test migrations against a restored prod database snapshot but again there's no guarantee some incompatible data hasn't been inserted in the meantime.


The first thing I've looked at on your site is how migrations work. Because honestly, I think that's one of the best things about Django. They just got it right, and as you say, not many other tools get close.

I wonder if you have looked at how it works. Because they have put in something like a decade to make it work and it's very powerful and a joy to use.

Down migrations are indeed very useful and important once you get used to it. First and foremost they give you a very strong confidence in changing your schema. The last time I told someone who I helped with django to "always write the reverse migration" was yesterday.

No way you can automatically resolve the discrepancies you can get with branched development. Partially because you can use migrations to migrate the data not just to update the schema. It's pretty simple as long as we're just thinking about adding a few tables or renaming columns. You just hammer the schema into whatever the expected format is according to the migrations on that branch. But even that can go wrong: what if I introduced a NOT NULL constraint on a column in one of the branches and I want to switch over? Say my migration did set a default value to deal with it. Hammering won't help here.

The thing is that doing the way Django does it is not that hard (assuming you want to write a migration engine anyway). Maybe you've already looked at it, but just for the record:

- they don't use SQL for the migration files, but python (would be Typescript in your case). This is what they generate. - the python files contain the schema change operations encoded as python objects (e.g. `RenameField` when a field gets renamed and thus the column has to be renamed too, etc.). - they generate the SQL to apply from these objects

Now since the migration files themselves are built of python objects representing the needed changes, it's easy for them to have both a forward and the backward migration for each operation. Now you could say that it doesn't allow for customization, but they have two special operations. One is for running arbitrary SQL (called RunSQL (takes two params: one string for the forward and one for the backward migration) and one is for arbitrary python code (called RunPython, takes two functions as arguments: one for the forward and one for the backward migration).

One would usually use RunSQL to do the tricky things that the migration tool can't (e.g. add db constraints not supported by the ORM) and RunPython to do data migrations (when you actually need to move data around due to a schema change). And thanks to the above architecture you can actually use the ORM in the migration files to do these data migrations. Of course, you can't just import your models from your code because they will have already evolved if you replay older migrations (e.g. to set up a new db or to run tests). But because the changes are encoded as python objects, they can be replayed in the memory and the actual state valid at the time of writing the migration can be reconstructed.

And when you are creating a new migration after changing your model you are actually comparing your model to the result of this in-memory replay and not the db. Which is great for a number of reasons.


Yep we looked at Django ORM as an inspiration. I unfortunately don't have the bandwidth right now to write a lengthy thoughtful response, but quickly on a few points:

- The replaying of the migrations history is exactly what we do, but not in-memory, rather directly on a temporary (shadow) database. It's a tradeoff, but it lets us be a lot more accurate and be more accurate, since we know exactly what migrations will do, rather than guessing from an abstract representation outside of the db.

- I wrote a message on the why of no down migrations above. It's temporary — we want something at least as good, which may be just (optional) down migrations.

- The discrepancy resolving in our case is mainly about detecting that schemas don't match, and how, rather than actually migrating them (we recommend hard resets + seeding in a lot of cases in development), so data is not as much of an issue.


Well, of course I don't know about the internals, but having used Django migrations for a decade now (it used to be a standalone solution called "South" back then), I haven't really run into any inaccuracies and can't really imagine how those could happen. As far as I can see, the main difference is that they are storing and intermediate format (that they can map to SQL unambigously) while you immediately generate the SQL.

Django doesn't try (too hard) to validate your model against the actual DB schema. Because why would it? You either ran all the migrations and then it matches or you didn't and then you have to. (Unless you write your own migrations and screw them up. But that's rare and you can catch it with testing.) While your focus then seems to be to check if the schema (whatever is there in the db) matches the model definition. Based on my experience (as a user) this latter is not really something that I need help with.

Data is actually an issue in development and hard resets + (re)seeding is pretty inconvenient compared to what django provides. E.g. in my current project we're using a db snapshot that we've pulled from production about two years ago (after thorough anonymization, of course). We initialize new dev environments and then it gets migrated. It probably takes about half a minute to run as opposed to about 2 seconds of back migrating 2-3 steps.


It makes a lot of sense. I have a fair amount of Rails experience with ActiveRecord, and it was also my impression that the database schema drifting in development is rarely a problem, but I now think it's a bit of a fuzzy feeling and discrepancies definitely sneak in. The main sources of drift in development would be 1. switching branches, and more generally version control with collaborators, 2. iterating on/editing of migrations, 3. manual fiddling with the database

One assumption with Prisma Migrate is that since we are an abstraction of the database, and support many of them, we'll never cover 100% of the features (e.g. check constraints, triggers, stored procedures, etc.), so we have to take the database as the source of truth and let users define what we don't represent in the Prisma Schema. On SQL databases, we let you write raw SQL migrations for example, so you have full control if you need it.


@nikolasburk can you comment? Before compiling this list I was seriously considering using Prisma to kick off a big upcoming project. In the spirit of being open-minded I'd really like to know if I'm fundamentally misunderstanding Prisma capabilities.


This is a pretty extensive list! Are all of these points are full blockers for you or are there individual points that you care more about while others might be rather "nice to have"? If most of them are real blockers, you probably shouldn't use Prisma [1], and that's ok :)

Prisma certainly is not perfect and whether you should use it depends on your project and individual requirements. What I can tell you is that we are shipping releases [2] with new features and improvements every two weeks. We are also very eager to learn about more use cases that people want to accomplish with Prisma. The best way to bring these to our attention is by commenting on existing GitHub issues and creating new ones if the one for your use case doesn't exist yet. This helps us prioritze and implement these new features. We also have a roadmap [3] where you can see all the features that we are currently working on.

Hope that helps for now!

[1] https://www.prisma.io/docs/concepts/overview/should-you-use-...

[2] https://github.com/prisma/prisma/releases

[3] http://pris.ly/roadmap


Thanks for getting back to me!

Most of these are pretty important to us. I did try to put lower priority items near the bottom of the list. However, I did omit several nice-to-haves already. e.g. ActiveRecord's ability to diff changes after an update (ActiveModel::Dirty)

I wouldn't say any of them are complete full blockers by themselves but cumulatively yes they prevent us from considering Prisma for most projects.


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.

A drop-in replacement for PrismaClient for tests would have been wonderful.


Could you elaborate on why you find a real DB overkill for testing purposes?

Typically you would have a set of unit tests which test your code only and use mocks for external dependencies, in this case you would mock Prisma.

Tests involving DB state are not unit tests anymore and require a bit more setup but most frameworks provide a test harness/runner to quickly spin up a part of your app with dependencies resolved (instead of mocked) so you can call controller/handler/service methods directly and verify their behavior.

The boilerplate for this should mostly be limited to switching out an environment variable for the database connection and maybe setup some fixtures. Is this not possible in Prisma?


Thanks for this feedback. We're very aware of this problem and are are actually working on a guide for working with Jest right now.

It's a tricky problem because our use of the type-system is quite complex.

I quite like your idea of a test mode or maybe a mock client that we generate.


I wonder if its possible to start a transaction at the beginning of each test and then rollback. That way we just need a db to run but it will never really be written to.

Or maybe just invest in this: https://github.com/oguimbal/pg-mem/issues/27 He has already put a lot of effort into it and I think with just a bit more support he can support Prisma


> I quite like your idea of a test mode or maybe a mock client that we generate.

Yes please!! :)


Isn't that pretty much always the dilemma with database stuff and testing?

On the one hand, you can wrap your DB operations in some kind of mockable interface(s), and then test your business logic.

On the other hand, you really need to test that the actual SQL ops work anyway- what if you screwed up a foreign key constraint?- that wouldn't show up in your business logic tests or your in-memory SQLite or whatever.


using tmpfs... it'll help a bit (I used mysql-on-tmpfs before)


I'm sure a lot of hard work went into this, so I mean no offense here when I give my honest feelings.

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.

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.

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.

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.


It took me all of 1 hour to learn how to use prisma. It’s not as hard as you make it seem.

They have a vs code extension for editing the prisma schema.

It’s not hard to see that you make fields “String” @unique, etc.

Just make one model and that’s 80% of anything you ever need.

Want to make a relation? Make a new model

model Posts { users User[] }

Hit save, it adds the required code to relate it to the User model.

Run the generate command. Make a client instance.

Create something: db.NameOfModel.create()

And as you’re typing, typescript is auto completing it.

There’s barely anything else to learn. And it’s a quick doc search away.

Don’t hate on it because you love sql, it’s a great tool.


>It took me all of 1 hour to learn how to use prisma.

That is not how this works at all. You don't know what you don't know until you need "how do I do this query in prisma". You then spend an hour trying to figure it out, and maybe it isn't possible, then on to the next thing. It takes more than an hour to learn SQL, so if it takes you an hour to learn Prisma, you are missing something.


I’ve had to write complicated queries with prisma.

In fact. One that prisma didn’t support.

Ordering by the count of a relationship, and returning the few highest counts.

So I added a GitHub issue that is being tracked.

And what did I do for now?

I wrote the SQL using prisma’ raw query function.

You can learn SQL and use an ORM.

Things don’t always have to be one vs the other


Have you even looked at Prisma's docs?

I would rather forget everything about Prisma the second I wake up in the morning and have to read their docs on how to do everything all over again, every single day, than try to understand what the heck is going on in SQL's docs.


Yeah exactly.

Most queries you can figure out just from typescript auto complete too...

Majority of queries in an app are basic relationships and crud actions... doesn’t take endless hours figuring this out with prisma.

If it did, we wouldn’t use it.


> It took me all of 1 hour to learn how to use prisma. It’s not as hard as you make it seem.

That's because the ORM cannot do a lot. Unless you're doing extremely simple SQL queries it will not handle what you want it to do. Now what? Drop into RAW sql and lose all type safety and you are on your own.

No thanks, I started using Prisma for https://listifi.app and switched it over to knex.js because the second I hit a query that Prisma couldn't handle I was on my own. Knex on the other hand could handle everything because it's a simple query builder.


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.

https://jawj.github.io/zapatos/


Fully agree, Zapatos is super nice – and so are similar tools like pgtyped and Slonik. We actually mention all these in our docs here: https://www.prisma.io/docs/concepts/overview/should-you-use-...

I think the main difference of these libraries compared to Prisma is the level of abstraction. If you want to work with SQL and you appreciate type-safety, then all of these tools will be fabulous options for you! If you want to work on a somewhat higher level of abstraction but still have full type-safety, Prisma will be more appropriate.


PgTyped is similar, and it's superior to any other data access layer I've ever used. Ever.


Have you compared PgTyped to Zapatos (I haven't yet).


Not yet, but I will at some point. I don't really have any pain points with pgTyped yet.


> prioritis[ing] ease of switching to another database tomorrow over effective use of this database today, are a source of misery.

hah. This looks cool, thanks for sharing!


We tried converting from Sequelize to Prisma and learned transactions were not fully supported... Can't build an app without transactions


Is this for real? And what are people building that wouldn't need this?


Transactions are supported in Prisma, see this guide in our docs [1].

I guess the post refers to our opinionated stance on "long-running transactions" which Prisma indeed does not at the moment. The best resources to learn about this are on GitHub [2] and our blog [3].

[1] https://www.prisma.io/docs/guides/performance-and-optimizati...

[2] https://github.com/prisma/prisma/issues/1844

[3] https://www.prisma.io/blog/how-prisma-supports-transactions-...


I'm not a fan of ORM, never been and never will be. https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...

But my gosh "Application developers should care about data – not SQL"


That line triggered me as well. I have built hundreds of applications on top of databases w/ sql and nosql, and from that experience— the notion that a single ORM abstraction somehow ameliorates the burden of caring about how your queries are written is alarming to me.


From the web page: "Application developers should care about data – not SQL"

That is fundamentally wrong - or at least - it is very limiting.

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.

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.

If your domain model is simple and will remain simple in the future, 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.

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.

Prisma is the first time it all just makes sense.

The code generation, auto migrating without any code, automatic crud resolvers with nexus, the list goes on.

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.

Also FoalTS is a nice small framework for building rest endpoints with TypeScript, and recently landed @prisma support/documentation besides typeorm.

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.

[1] https://github.com/adelsz/pgtyped


pgtyped is awesome! We mention it along with similar tools like Slonik and Zapatos in our docs here: https://www.prisma.io/docs/concepts/overview/should-you-use-...


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.

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

https://github.com/prisma/prisma/issues/4998#issuecomment-76...


Past related threads:

Prisma Raises $12M Series A - https://news.ycombinator.com/item?id=23651605 - June 2020 (111 comments)

Prisma 2.0 – Type-safe and auto-generated database client - https://news.ycombinator.com/item?id=23466834 - June 2020 (107 comments)

Prisma 2.0 Beta: Type-safe Database Access - https://news.ycombinator.com/item?id=22739121 - March 2020 (122 comments)

Comparing Database Types - https://news.ycombinator.com/item?id=21060866 - Sept 2019 (170 comments)

Prisma – Database tools for modern application development - https://news.ycombinator.com/item?id=19598287 - April 2019 (83 comments)

Show HN: Prisma – Turn Any Database into a GraphQL API - https://news.ycombinator.com/item?id=17076202 - May 2018 (16 comments)

Prisma Cloud – A GraphQL Database Platform - https://news.ycombinator.com/item?id=16529450 - March 2018 (9 comments)

Prisma – An open-source GraphQL API layer for your database - https://news.ycombinator.com/item?id=16160803 - Jan 2018 (34 comments)

Prisma: Turn your database into a real time GraphQL API - https://news.ycombinator.com/item?id=16159874 - Jan 2018 (9 comments)


Nice, thank you for digging these up!


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 https://github.com/adelsz/pgtyped where you have raw sql files / template literals and it will create types for parameters and results.


Pgtyped made sql+typescript a pleasure.


New idiom - "Well that was a complete ORM"

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


Even as an ORM author this made me laugh.

ORMs are useful. ORMs aren't always the right answer. ORMs that don't let you mix and match so you can bypass any given layer of abstraction as required annoy the pants off me ... usually even more than anti-ORM zealouts annoy the pants off me.

So, yeah, joke appreciated :D


I used Prisma with TypeScript for rapid prototyping. It did what it said and did it well. I would highly recommend giving this a try.


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.

Edit: After quickly going through the intro, I prefer MikroORM, it's simpler.


I've tried Prisma, TypeORM and MikroORM and I'm certainly the most impressed with MikroORM.

It's simple and the use of identity map within a unit of work, tied to a request context (for example), is just brilliant.

The handling of relations (with configurable loading strategies) and other features such as Embeddables and Filters make it a real joy to use.

And if at any point I can't achieve something with MikroORM itself (which generally in my case is calling a PostgreSQL function), I can easily grab the underlying Query Builder from Knex.

It also has the tooling to generate a schema from your Entities, or generate Entities from an existing schema - so it's easy to get started on something new or existing.


Can someone compare to Hasura please? What has been your experience?


I use Hasura and generally like it, it certainly has some sharp edges, but overall has worked. I think Hasura has much richer GraphQL support, which I pair with gqless[0] to get what I think is a much nicer client side / ORM experience overall.

Other upsides are a much better RBAC column/row permissions, migrations with up/down, a better admin UI.

[0] https://gqless.com


What sharp edges are you referring to?

I find that with new tech, I'm more interested in what it can't do rather than what it can.


Hard to remember them all now, but definitely had a number of bugs I've ran into, most able to be worked around. For example a recent one was a huge slowdown in a simple query that should be optimized[0], still have no great solution for it or attached to any roadmap.

One big one is at some point you need caching on a query level, and for that you have to purchase enterprise. I suppose they need to make money somehow, and it's a smart level to do it because it's almost impossible to not need caching at some fairly early stage. It would be nice if they let you test it out without "Contact Sales", and while there may be some way to wrap it from above, I don't think it's very easy.

A meta critique would be the project seems to have less momentum than Prisma. Releases aren't as frequent or large, and I'd have much preferred they double down on PG and keep adding query abilities, performance, etc, instead of expanding database engines.

[0] https://github.com/hasura/graphql-engine/issues/5745


Yikes!

That sounds like it has "vendor lock-in" written all over it.


Yea, but it’s a lot better than what’s out there, as far as I know, even with Postgraphile having progressed a lot.. and it is open source.


I came here with the same question.

Hasura is really awesome, so far as I have used it.

Now I gotta look into prisma

I almost wonder if they aren't mutually exclusive. With hasura, you need to run your own server anyways, for auth or special cases. Maybe that server could run prisma? Let hasura do the migrations, and prisma track it?


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

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.

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:

There is no shortcut. And we don't need one.

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.

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

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.


Meanwhile I just want to write my migrations in plain SQL. Why do we care so much about hiding SQL, it’s easy.


I've found this a very helpful summary of TypeScript SQL libraries: https://phiresky.github.io/blog/2020/sql-libs-for-typescript...


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

Food for thought if prisma.io team is watching.

[0] https://www.paloaltonetworks.com/company/press/2019/palo-alt...

[1] https://blog.paloaltonetworks.com/2019/11/cloud-prisma-cloud...


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.

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 composed. I couldn't find any examples. e.g.

  WHERE id IN (<subquery>)
if I built up <subquery> using the ORM.

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.

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?

Checked here for window function support. https://www.prisma.io/docs/concepts/components/prisma-client...


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.

https://github.com/dosco/graphjin


I dont have much to add technically, I just want to show my gratitude, it has been very helpful for one of my projects.


Is it a good suggestion to use prisma as a beginner other than using some low level query builders like knex.js?


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?



Easy to miss in the long blog post:

> Prisma currently supports PostgreSQL, MySQL, SQLite, SQL Server (Preview). A connector for MongoDB is in the works, sign up for the Early Access program here (https://prisma103696.typeform.com/to/FriDuIeM).


How are they going to make money?


We've explained this in the blog post here: https://www.prisma.io/blog/prisma-the-complete-orm-inw24qjea...

I think this quote summarizes our plans nicely: Prisma's vision is to democratize the custom data access layer used by companies like Facebook, Twitter and Airbnb and make it available to development teams and organizations of all sizes.

The open-source ORM we're launching today will of course remain open-source and we'll keep investing into it since it's be the foundation for the commercial tools that folks will be able to use on top.


How does “democratize the custom data access layer” make money?


I have no idea what this means either. Sounds like a bunch of buzzwords about nothing.


It means that Prisma will provide a data access layer (we call it "application data platform" [1]) similar to the custom data access layers built by big companies [2] (e.g. TAO by Facebook or Strato by Twitter) that enables application developers to better access their databases.

We've explained that in the blog post here in the "Open-source, and beyond"-section [3].

Does that help? :)

[1] https://imgur.com/O1lwo0v.png

[2] https://imgur.com/Hb9VOWN.png

[3] https://www.prisma.io/blog/prisma-the-complete-orm-inw24qjea...


I think we all appreciate the links but unless I'm missing something, no, it doesn't help -- what's the plan to make money? That blog post doesn't mention anything along those lines at all.


They probably won't, will end up being sold to a bigger co and the tool will be transferred to the "community" for maintenance. See RethinkDB.


I don't think they will.


Have been experimenting with mammoth [1] for postgresql and am quite happy with how close it is to actual SQL.

[1] https://github.com/Ff00ff/mammoth


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.


Hey, Nikolas from Prisma here!

Can you elaborate what exactly you mean with "GraphQL integration"? You might be referring to Prisma 1 which was a "GraphQL layer for your database". Prisma 2 (which we are referring to with this article) doesn't have a native GraphQL integration any more :)

Prisma is very different from Sequelize in various ways (see our docs [1]):

- Your data model lives in the Prisma schema

- Database access doesn't happen via model instances but via the Prisma Client API [2] which always returns plain JS objects making your queries much easier to reason about - Prisma Migrate auto-generates migrations based on your data model (and lets you customize these migrations when needed)

- Prisma comes with Prisma Studio out of the box – a modern database GUI

- Prisma is not a community-project but built by a VC-funded company

– We care a lot about our community, documentation, and providing support (something we've seen lots of developers complain about with other ORMs)

[1] https://www.prisma.io/docs/concepts/more/comparisons/prisma-...

[2] https://www.prisma.io/client


>Prisma is not a community-project but built by a VC-funded company

Thanks for clarifying this. I know there tend to be a kneejerk reaction towards VC-funded software projects. How do you guys plan to make money on this?


We've explained this in the blog post here: https://www.prisma.io/blog/prisma-the-complete-orm-inw24qjea...

I think this quote summarizes our plans nicely: Prisma's vision is to democratize the custom data access layer used by companies like Facebook, Twitter and Airbnb and make it available to development teams and organizations of all sizes.

The open-source ORM we're launching today will of course remain open-source and we'll keep investing into it since it's be the foundation for the commercial tools that folks will be able to use on top.


I’ve read the post you linked and I still don’t know how you plan to make money. Also, the blurb you copy pasted it tells me nothing in a clear way.


I am a noob in these matters which one is advised if one is to start a GraphQL API from the beginning

- use DBs that directly provide GraphQL endpoints such as Fauna, Upstash, etc. - use an ORM with more traditional DBs.

What are the pros and cons of each?


I prefer having the database as a single source of truth and use something like [Hasura](https://nhost.io) to generate the GraphQL API.


I second this, Hasura is a solid choice.


I tried out nhost a few weeks ago and it was great!


There is value in both, at Fauna we provide GraphQL out the box. Using Fauna directly would eliminate an indirection and is probably slightly more efficient. However, if there is a GraphQL layer like Prisma in between you could essentially change to any database with less impact on your application. This is tremendously interesting for people who develop frameworks, using prisma gives them the advantage of supporting multiple databases immediately. Or for application developers it could allow you to move from a non-scalable database to a scalable database once it becomes necessary or simply just switch databases if the database maintenance is causing you grief. I'm for one looking forward to Prisma supporting Fauna since if the interface is the same, there are even less reasons not to choose a scalable managed database instead of managing your own db :). And I would say that Prismas interface is quite great!

Note: the performance impact does depend heavily on whether your database maps well on ORMs. Traditional databases have an impedance missmatch when it comes to translating tables to an objet format. Graph databases or the way Fauna works (documents with relations and map/reduce-like joins) map well on ORMs so the performance impact would be small.


How is Prisma's support (if any) of Mongo? I use it in combination of Postgres for prototyping and I'm wondering if they play well together.



I'm just about to start working on DB layer for our app with TypeScript. Any other alternatives to prisma you guys prefer and why?


(haven't tried Prisma)

TypeORM -> classic ORM, the "default" choice in node, but not very mature compared to what exists in Java or things like Django ORM. In my experience it's not an amazing lib, transaction management is a bit painful, I've seen a couple bugs happen.

Knex -> it works but not sure why you'd use that rather than TypeORM

MikroORM -> not a lot of experience, one of my colleagues really likes it, read the principles behind it and see if that talks to you as it's a bit opinionated

Slonik -> Never used it but probably what I'd set up on a new project. Like many people I think that query builders are BS and I'd rather use SQL: Slonik makes that very easy while giving you the usual getOne/getMany utilities

I'd like to try Prisma though, I've heard good things


TypeORM is poorly maintained (the lead author had a breakdown and appears to be inactive, and failed to delegate ownership to others), and is riddled with bad abstractions, poor design choices and an enormous pile of game-breaking bugs that make the TypeScript types unsafe and it’s usage clunky and dangerous. I would absolutely use knex, which works, over TypeORM, which routinely doesn’t.


Interesting -- I actually haven't had as many problems as you have with TypeORM -- it's just mostly worked. Then again, maybe I'm a rare case, because I basically ignore the ORM side and use a little repository pattern, the query builder, and write my own migrations in SQL (as in all the migrations are `await queryRunner.query(...);`).

It's been excellent for me -- I ignore the bad abstractions, have a good underlying database (postgres), and drop down to parametrized raw SQL whenever I need to, it's held up extraordinarily well I think.

Could you expand on some specific issues/frustrations? Just curious about the side I'm clearly not seeing.


This is the one upside I’ve had of using TypeORM: I find its query abstractions so unreliable and surprising that it’s forced me to get better at SQL!


And once you're good at SQL, it will never let you down. Query builders + reasonably abstracted libraries for the repetitive things (the repository pattern in TypeORM gets me just about all the CRUD I need), and query building/raw queries for the rest.

The things that the DB can do (I'm thinking about postgres) are amazing, and you just aren't going to get a chance to use a lot of it really from a lowest common denominator ORM. Yeah, you probably don't need it -- but then again why not just use a tool like Postgrest from the start?


Lmao. This happened to me too no joke


Yeah my issues are from trying to actually use the advertised functionality of the library. Issues include migrations being generated incorrectly, lots of footguns (for example it’s extraordinarily easy to instead of deleting a particular row, to delete your whole table, and their typescript typings are too general to make it clear what you’re doing), the pattern of making instances of relations that lack all the fields that are required in the DB leads to the type system becoming unreliable (fields that should always be present in the DB, may not be in JS), transformer functionality has a ton of bugs as well. I’m just scratching the surface, it’s telling that there are 1500+ unresolved issues on the repo and 200+ unmerged PRs.

The downside for me about dropping into raw SQL is that it makes much of the usefulness of an ORM in being able to refactor your schema and stuff disappear, because the type system and instrumentation can’t auto-refactor a raw SQL string. But I agree that I’d take that over relying on TypeORMs bugginess.


Thanks for sharing this insight, it makes a lot more sense to me now, saving this (in my brain) for the counter argument to TypeORM.

> I’m just scratching the surface, it’s telling that there are 1500+ unresolved issues on the repo and 200+ unmerged PRs.

So just a side note on this, I judge repositories based on teh ratio of closed to unresolved (and whether they have a bot that auto-closes issues). 200+ unmerged PRs is bad -- that maintainer needs help. I've interacted with the main maintainer before a long time ago and I had a good impression, but they probably weren't ready for how big their project got (how can anyone be) and they clearly didn't charge enough to make it a joy to work on their project. Money isn't the main motivator for everyone, but it would enable hiring people, or taking vacations as appropriate, etc.


Yeah, a lot of people have been saying that on the pinned mega-issue re the future of TypeORM - but the maintainer doesn't seem to have done much to fundraise nor pass on leadership to others. This seems to be moving slightly - some contributions are slowly trickling in. But at this point I think alternatives like Prisma will just eat the pie.


This matches my team’s experience with TypeORM. It’s full of bad surprises. We use it exclusively with a read-only account because we don’t trust it to change our data under any circumstance. Our current plan is to move to MikroORM, Prisma 2, or just give up on Node completely.


Just as a side-note, we have migration guide from Prisma to TypeORM in our docs: https://www.prisma.io/docs/guides/migrate-to-prisma/migrate-...


I think you meant from TypeORM to Prisma


My preferred DB layer is Objection.js[0] which is simply an ORM-layer on top of knex[1], with official TypeScript support.

[0] https://vincit.github.io/objection.js/

[1] https://knexjs.org/


Hey, Nikolas from Prisma here.

There are quite a few other ORMs for working with DBs in TypeScript (none of them providing the same type-safety guarantees that Prisma does though [1]).

In fact, we laid them out on a docs page [2] in case you want to learn which tool might be appropriate for your use case.

[1] https://www.prisma.io/docs/concepts/more/comparisons/prisma-...

[2] https://www.prisma.io/docs/concepts/overview/should-you-use-...


Thanks, that's really useful.


I suggest to not add a layer. Stay close of the SQL request, you can simply have a function that will fulfill an access pattern. No need for ORM.


Yeah, I have no problem with minimum ORM (prepared statements type of thing) - That's why I am asking the question, Prisma seems a bit too much. Usually I just use simple ORM helpers (get by primary key for example) and anything complex is raw sql.

What I am looking for is ideally something that helps with type saftey and seeds/migrations/schema creation.


I have been using Flyway with Node.js. Works all right and you write everything (migrations, schema, seeds) in SQL. It does make working with SQL quite straightforward and non-magical.

The thing is of course that with very complex queries you'll end up making quite intricate string building stuff which may not be very pretty. I myself am curious to find out if Prisma can make that part go away.


I'm wondering at the moment whether Knex (solid query builder and migration) and Objection.js would not do this trick. That combo seems to give me json validation which is nice and seemingly both a way to handle entities and graphs in an efficient way.


https://jawj.github.io/zapatos/ (no built-in migration support, though)



Try Knex!


There are many discussions on reddit about Prisma vs TypeORM in particular. For example here: https://www.reddit.com/r/node/comments/l3xu6w/is_prisma_a_vi...

There is also a good discussion going on here: https://www.reddit.com/r/node/comments/l00fit/what_do_you_th...


MikroORM. I think it's the best ORM for TypeScript.


If you want an orm that encourages you to use sql instead of abstracting it, you can look at my library: https://github.com/Seb-C/kiss-orm


I love knex and simply write small functions and roughly keep one file/folder per table. So: user.create(db, { name: .. })


TypeORM or MikroORM, any of those should be fine


What is the business model for them? It seems that an ORM in and of itself is not sustainable, especially one that is free.



I think its unfortunate that Prisma is only server-side due to its Rust-based native DB module.

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.

Without a consistent model, we are forced to write so much additional code to manage caching, optimistic UI updates, and offline capabilities.

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.

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.

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.

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.

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.

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.

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.

I think we are all still in search of the right state management solution and it would be cool if Prisma did it!

[1]: https://docs.github.com/en/graphql/overview/explorer


What's the most recommended .net equivalent


orm bad




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: