
Prisma 2.0 – Type-safe and auto-generated database client - Sujan
https://www.prisma.io/blog/n0v98rzc8br1/
======
hbrundage
Prisma's architecture seems novel and ... a little strange to me. It works by
running a Rust engine as a subprocess and then communicating with the engine
from JS land over a non-spec compliant GraphQL API. The engine holds the
actual databae connection pool and does all the SQL generation and data
marshalling. See [https://www.prisma.io/docs/reference/tools-and-
interfaces/pr...](https://www.prisma.io/docs/reference/tools-and-
interfaces/prisma-client/query-engine/) for more info on this arrangement.

It has some weird ramifications though:

\- when they go to implement a new feature (like recently added JSON column
support) they have to implement it on both sides which can cause bugs like
this:
[https://github.com/prisma/prisma/issues/2432](https://github.com/prisma/prisma/issues/2432)

\- they're a little limited to the semantics of GraphQL based RPC, which
namely excludes any stateful stuff like arbitrary START TRANSACTION; blocks
that might or might not commit. See [https://github.com/prisma/prisma-client-
js/issues/349](https://github.com/prisma/prisma-client-js/issues/349) for more
info on that

\- they don't run everywhere JavaScript runs like the browser or Cloudflare
Workers (unless there's something fancy that compiles the engine to WASM I'm
not aware of)

I wonder if their intention is to re-use the engine between different JS
processes for caching / sharding or something like that, or to add Prisma
clients in other languages. Why create the indirection?

I do like Prisma's type safety compared to the pure TypeScript alternatives
like TypeORM and MikroORM -- it's really good at typing the results of
specific queries and preventing you from accessing stuff that wasn't
explicitly loaded. The style of the query language is the cleanest I've seen
out of the three as well IMO.

Edit: I think node modules can install arbitrary binaries to some serverless
JS runtimes, not sure specifically about Cloudflare but I know their dev tool
bundles JS using webpack, which would exclude other binaries from
node_modules.

~~~
kristiandupont
I generate Typescript types from my database
([https://github.com/kristiandupont/kanel](https://github.com/kristiandupont/kanel))
which gives me type safety on back- and frontend without relying on an ORM. I
am curious about Prisma but I don't see any advantage to it from my quick
skimming.

~~~
olso
What do you use to write the queries? Some query builder?

~~~
kristiandupont
I use a home made library on top of Knex that is not open source yet. I will
extract it from the Submotion code and release it as well, but I am not sure
when.

------
snowoutside
I empathize with the frustration that this library is trying to solve. It's
pretty nifty too! Ultimately, however, I don't believe this is the correct
approach.

The problem with this tool, like every other multi-SQL-flavor-SQL ORM and
query builder, is that it requires users to learn yet another language. In
addition to Node.js and SQL, users need to learn the Prisma query language.
This is not trival, and users that are already accustomed to working with SQL
will need to relearn PrismaSQL.

I think the best approach to this problem is a single-SQL-flavor query builder
that attempts to match SQL as closely as possibly while adding in the niceties
of being able to pass in JavaScript objects instead of raw SQL strings. Lets
be honest: raw SQL-in-JS is no fun.

This would lead to PostgreSQL, MYSQL, SQLite, etc-specific query builders.
Knex is close, but it ultimately doesn't work for most because it's missing
some language-specific features (e.g. ON CONFLICT DO UPDATE). While this
doesn't exactly meet the type safety benefits of Prisma, the benefits in ease
of use and feature-parity of a language-specific query builder far outweigh
the difficulties of learning a new query language like Prisma.

~~~
ctrlplusb
I get where you are coming from, but from your comment I am guessing you have
yet to try Prisma.

I'll speak to my personal experience...

I became allergic to ORMs after experiencing much of the pain that you
describe. Like you, I quickly found ORMs were simply an additional domain
language / abstraction over my database that provided more pain that
usefulness. Every time I wanted to make I change to the code I had to wade
through tons of docs and/or stackoverflow posts by other frustrated users. If
I wanted type safety I had to express and maintain types/decoders/encoders
myself. Huge pain, and things always got stale leading to a massive mistrust
in my data layers.

Prisma doesn't feel like those experiences. Their schema-first, client code
gen approach works surprisingly well. Using the generated API feels really
intuitive, and TypeScript is there the whole time providing guidance and
autocomplete for me. The object tree query syntax is quite refreshing compared
to the builder pattern approach taken by the alternatives. I always found the
builder pattern overwhelming and often a guessing game at how to compose them.

I think Prisma doesn't try to be too clever with their data API. They solve
the 99% in a manner that is simple and convenient, for everything else you
have the raw query API - much like other solutions.

I'd suggest giving it a try. You may like it.

~~~
JamesBarney
That sounds awful, and completely different from my experience with ORMs. My
experience has almost exclusively been entity framework, which despite having
some warts (rank over partition queries are impossible) has been a very
pleasant experience.

One advantage is the additional domain language is also the language of
array/list manipulation, and not having to maintain any encoders/decoders (I
honestly don't know what these are).

~~~
nicoburns
Interesting, coming from Eloquent ORM (PHP), I _hated_ Entity Framework. It
seemed to want to do far too much clever stuff (like save an entire object
graph at once), and didn't have nearly as many escape hatches as I'm
accustomed to (Eloquent will let you inject raw SQL nearly anywhere). I've had
positive experiences like ORMs, but only when they are thin layers over the
underlying SQL.

~~~
JamesBarney
What was the problem with raw SQL you ran into with Entity Framework?

Entity framework follows the unit of work pattern from gang of four, and if I
remember correctly eloquent follows the active record pattern.

When I started I found the active record pattern much more intuitive, but I
prefer the unit of work pattern now. Mostly because I think unit of work works
better with transactions/constraints then active record pattern.

------
hn_reddit_human
If I understand correctly, transactions are currently not supported for doing
any advanced business logic beyond building CRUD:
[https://www.prisma.io/docs/reference/tools-and-
interfaces/pr...](https://www.prisma.io/docs/reference/tools-and-
interfaces/prisma-client/transactions#future-transaction-support-in-prisma-
client)

Also, no mention of aggregations, or if someone could point me to it?

As far as typed query builders go, even though things like JOOQ are simply
amazing, but I fear that the query building approach has not really caught on
for database access and people seem to prefer "object oriented" methods like
ORMs.

Any comments from HN folks about why that is?

~~~
ssijak
People are scared of SQL or they want to use their objects for data access,
business logic and views at the same time (leading to bad architecture and
headaches), or they come from Nosql world, or they want a shiny new tool, or
they do not understand features of their used db well, or because “everybody
is using ORM zyz”... Just from the top of my head, there are probably more.

------
flybayer
I'm super excited about Prisma 2! I feel like we finally have a database
client that (1) is/will be very powerful and (2) is very approachable and easy
for beginners to learn.

It certainly doesn't replace the need to know some SQL, but it does delay that
which is great for so many people.

I'm definitely using this instead of any ORM for every project I can.

I really love having the fully typed interface for Typescript. Both for static
type checking and for code completion in your editor.

We are using Prisma 2 as the default database client for Blitz.js [1] which
results in a super nice stack. Especially because the Prisma DB types flow all
the way into your React components.

[1] [https://blitzjs.com](https://blitzjs.com)

~~~
narrationbox
What are your thoughts on RedwoodJS?

[https://github.com/redwoodjs/redwood](https://github.com/redwoodjs/redwood)

~~~
flybayer
Great alternative if you don't care about Next.js and you absolutely want to
use GraphQL.

------
nikolasburk
For those of you who prefer talks over blog posts to learn about new tools, I
recently gave a talk introducing Prisma 2.0 and demoed how you can use it to
build a REST API _and_ a GraphQL API with a PostgreSQL database.

You can find the full recording here:
[https://www.youtube.com/watch?v=AnJxKWQG_fM](https://www.youtube.com/watch?v=AnJxKWQG_fM)

------
enahs-sf
Not sure I 100% agree with their problem statement.

> the problem: Working with databases is difficult

Working with databases is a relatively solved problem. You can access them
from just about any language on any platform. A more accurate statement would
be: choosing the right access method to work with databases is difficult.

~~~
ogre_codes
> A more accurate statement would be: choosing the right access method to work
> with databases is difficult.

For me, it's the fiddly bit where you interface between the programming
language and the database is a PITA.

~~~
nikolasburk
This is exactly how it's meant in the article btw!

------
rodolphoarruda
"The problem: Working with databases is difficult"

This makes me think of all brainstorming sessions I attended in my life with
people asking over and over again, not convincing themselves with most
answers: "Ok, guys, seriously now, what problem do we think we solving here?"

~~~
nikolasburk
Scarily close to reality :D

~~~
sorenbs
:-P

------
nahtnam
Been using Prisma for a while. Nothing better than typing `await prisma.`,
hitting ctrl+space, and having it autofill everything for you! :)

------
benatkin
I'm really excited about Prisma and how it's contributing to the Node.js
ecosystem. Two new full-stack frameworks, Redwood and Blitz, are based on it,
and it's prominently mentioned in their READMEs.
[https://github.com/redwoodjs/redwood](https://github.com/redwoodjs/redwood)
[https://github.com/blitz-js/blitz](https://github.com/blitz-js/blitz)

As a status announcement, this isn't quite as exciting as it sounds because
migrations are still "experimental". Still great to see!

------
gsvclass
For those who rather work in GO checkout Super Graph its an automatic a
GraphQL to SQL compiler. It works as a library or a standalone service. Also
supports variety of auth schemes like Rails cookies, JWT, Firebase, etc. Super
Graph auto-learns your database schemas and relationships.
[https://github.com/dosco/super-graph](https://github.com/dosco/super-graph)

~~~
newusertoday
this looks great do you know if anyone is using it in production?

~~~
gsvclass
Yup a few startups mostly using the standalone version. One even runs it
alongside their Rails app on Google Cloud Run to add a high-performance
graphql api to an existing Rails app. I'm speaking to a couple people who want
to use it within their traditional GO REST API to query the database instead
of using an ORM.

------
jamil7
I don't have my head in backend too much these days and haven't kept up with
all of these solutions. It seems like Hasura is the more popular option right
now? or do they fit a slightly different space?

~~~
agrippanux
Having used both, I would pick Hasura for any project going forward. Time to
productivity is so much faster with Hasura and “it just works”. Prisma, at
least when I was using it, was introducing breaking changes regularly and
whipsawing between major design choices.

~~~
hokumguru
I mean... this just now is the production-ready release. Everything for the
past year on prisma2 has specifically been labeled as unstable. I wouldn't rag
on them too hard for having breaking changes while in an alpha/beta phase.

------
jhanschoo
I found Prisma 2.0 good for prototyping a service serving a GraphQL-compliant
API. One of the features not mentioned here is that it supports a rudimentary
form of database query batching (see: [https://github.com/prisma/prisma-
client-js/issues/153](https://github.com/prisma/prisma-client-js/issues/153)
), and there seems to be interest in improving it.

This helps one with solving the nplusone problem to some extent without having
to maintain code specifically for DataLoader + some orm / custom query code.
Comparatively, code via the Prisma Client API is usually straightforward and
succinct.

------
davedx
Does it have migrations yet?

I'd like to give Prisma a try, but if there's no sane way to change my schema
(part of my daily backend workflow) then it's less interesting, no matter how
nice the API is. For now I'll stick with Sequelize.

~~~
dirkc
I'm very glad to see work being done on db bindings, but until migrations
reach the level of django/active record, I won't be using postgres + node.js
seriously (again).

~~~
albertoperdomo
I'm with the product team at Prisma.

Prisma Migrate is different from ActiveRecord migrations (which are very
familiar with) because the DB schema is state-based. The Prisma schema file
acts as source of truth and the DB schema will be migrated to match it.

Can you elaborate on what you would perceive as reaching the level of
Django/ActiveRecord? I'd be interesting in specific aspects/items.

~~~
dirkc
That sounds great! I'm not super familiar with ActiveRecord, but I use Django
migrations regularly. For me the things that stand out are

1\. a declarative model - ie. defining the db schema rather than the
migrations

2\. auto generated migrations with the ability to customize

3\. integration with tools for deployment and testing

You probably have a much better idea of the landscape, but reach out to Andrew
Godwin [1], he wrote south and then rewrote it to become django migrations.

[1] - [https://www.aeracode.org/](https://www.aeracode.org/)

------
ogre_codes
I love the idea of a type safe interface to a database, but I'll pass on
learning yet another DSL. Seems like inevitably you get to a certain
complexity and the DSL just falls apart and you wind up writing SQL
regardless. Then you end up with half your queries written in one language
(SQL) and the other half in the ORM DSL. SQL isn't that hard and if you are
using a SQL database, you can't really escape knowing the concepts behind SQL
relationships regardless which is the tricky bit.

So, bring on type safe access, but don't make me learn yet another DSL which
only works 70% of the time.

~~~
albertgao
the DSL is only for table management, and pretty similar to GraphQL type
definition

The ORM layer is not a DSL but some nicely done JS/TS functions

~~~
ogre_codes
> The ORM layer is not a DSL but some nicely done JS/TS functions

You are splitting hairs here as far as I'm concerned. You need to learn the an
API so you can do 70% of your queries. Then you need to learn SQL so you can
do the other 30% of your queries and actually understand how to design a
database. The queries that the "nicely done JS/TS functions" are replacing are
almost always the simplest, most basic queries. Do you really need a special
query language to say `select * from widgets`?

The big problem with every ORM layer is you are essentially learning a
disposable language. Every ORM says it's the best way to Query ever, and yet
here we are, 5000 ORMs later and SQL is still an essential skill for
developers.

I know... "This time it's different!".

------
andy_ppp
The magic is great! Until it stops working in production...

------
TLadd
I've been using the beta for the past couple months on a new project along
with @nexus/schema to build a GraphQL server. This hits a sweet spot for me
where I'm not having to manually duplicate a bunch of information in my
GraphQL schema that's easily derivable from my database schema, but I still
have the freedom to implement custom resolvers and use Prisma directly (or
whatever else) when I need to. It's a good stack for building a GraphQL server
around a Postgres db.

The main problems I've run into have been around utilizing standard postgres
naming patterns (snake case for tables and fields instead of camelcase) and
mapping the names in the prisma schema. Ran into a handful of bugs related to
having these mappings that have all been fixed since. It still requires a
post-introspect step to add the mappings, but that's not too big of a deal.
Ideally the introspection would be able to handle database-specific
conventions.

Couple of other things I've run into that already have github issues:

\- It would be great if along with the create/connect options on relationships
for nested writes there was also an upsert.

\- Better transaction support beyond just nested writes would be great and
probably a requirement for a lot of apps. Thankfully, my server is relatively
simple right now so banking a bit on prisma improving as my app grows in
complexity.

~~~
nikolasburk
Thanks so much for sharing your experiences with Prisma!

> The main problems I've run into have been around utilizing standard postgres
> naming patterns (snake case for tables and fields instead of camelcase) and
> mapping the names in the prisma schema. Ran into a handful of bugs related
> to having these mappings that have all been fixed since.

Better re-introspection flows are indeed very much on our radar and something
that we want to tackle soon! Would be great if you could leave a comment with
your use case on GitHub [1], so we can make sure to address it properly when
planning and prioritizing new features! :)

> Better transaction support beyond just nested writes would be great and
> probably a requirement for a lot of apps.

Same here! It would be really helpful for us if you could share some details
about your use cases for transactions in the feature request [2] so that we
can incorporate them in our planning and design of the feature!

[1]
[https://github.com/prisma/prisma/issues/2425](https://github.com/prisma/prisma/issues/2425)

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

------
dang
A thread on this from a couple months ago:
[https://news.ycombinator.com/item?id=22739121](https://news.ycombinator.com/item?id=22739121)

------
mesaframe
It's funny how suddenly everyone is moving towards types. A couple of years
back which was frowned upon. And was seen as anti productive.

~~~
marcosdumay
If by "a couple" you mean 10 to 15, then yes, that's correct.

Look at the modern type systems we have around and try to see how they are
different from what was mainstream by that time.

~~~
mesaframe
I am not talking about the state of type system. But, how they were projected
as one. Especially when dynamic languages were getting popular.

------
visopsys
Any to solve the SQL long string problem? I hate having to write another
custom SQL migration script beside Prisma migration script.

For your reference:
[https://github.com/prisma/prisma/discussions/2138](https://github.com/prisma/prisma/discussions/2138)

------
olso
So with Prisma 2.0 I could replace
[https://www.npmjs.com/package/schemats](https://www.npmjs.com/package/schemats)
\+ [https://sqorn.org/](https://sqorn.org/) ?

------
wiremine
I have a lot of experience with ORMs (the Django one in particular). I'm
having a hard time finding a quick overview of how Prisma's model is
different. Are there any good overviews of the similarities and differences?

~~~
2color
Daniel from the Prisma team here.

Both Prisma and ORMs abstract away from SQL and let you "think in objects".
However, how they do that is different.

With ORMs, you typically map tables to model classes With Prisma, the focus is
on queries and structural typing; queries return plain objects that are fully
typed based on the query.

For a broader comparison with ORMs, check out the documentation page about why
Prisma is not an ORM: [https://www.prisma.io/docs/understand-prisma/prisma-in-
your-...](https://www.prisma.io/docs/understand-prisma/prisma-in-your-
stack/is-prisma-an-orm)

~~~
wiremine
Cool, thank you very much!

------
OJFord
I happened across Prisma recently while looking for a (more) declarative way
of managing SQL migrations. It's far from complete, but a nice feature.

------
volkk
i'm not a node dev, but am seeing the activerecord (from rails) inspired
ideology behind this. while activerecord certainly has its downsides, its
awesome upsides/ideas should definitely be reused

