Prisma: Subscriptions/real-time API support

[schickling]

Unfortunately at the current point of time Prisma 2 doesn't yet support subscriptions (real-time API features). Prisma 1 followed a different architecture which provided support for subscriptions. We're planning to bring back real-time/subscriptions functionality in the future based on a standalone "events engine" (which will use CDC under the hood).

This issue tracks the progress for this feature. Please feel free to share your specific requirements and wishes for this feature. 🙏

[ryanking1809]

For those who need subscriptions in the meantime looks like it's possible to get something set up with graphql-yoga. I have a working pubsub proof of concept based off the prisma2 boilerplate and Ben Awad's video tutorial https://youtu.be/146AypcFvAU . Should be able to get this up and running with redis and websockets to handle subscriptions until the prisma2 version is ready.

https://github.com/ryanking1809/prisma2_subscriptions

[ryanking1809]

I would like to be able to subscribe to events that are not necessarily database events.
Say I wanted to subscribe to another user's mouse movements. Or their cursor in a google docs app. It's not something you want to write to the database on every movement - it is better temporarily stored on something like redis.

I'm not sure if this is outside Prisma's scope, but something you could keep in mind!
Or maybe I could write to a temporary store using Photon 🤔

[mcmar]

@schickling I'm really excited to see this land in Prisma2. I'd love to see a system that can listen to both scalar updates and relation updates
See here: https://github.com/prisma/prisma/issues/146
Also want to make sure that it can both trigger lambdas and trigger updates on each running instance of photon for coordinating multiple servers, using rabbitmq cluster credentials that I can supply in a config file, just like Prisma1 does. That's important for any real-world multi-server subscriptions-over-websocket use case. I could technically use lambdas plus my own pub-sub service to do it, but I think this would be a killer feature that separates Prisma2 from all that pleb software like TypeORM or Sequelize

[sapkra]

Feature Request: Subscribing to relation changes

It should be possible to subscribe to relation changes without using the dummy workaround of Prisma 1.

https://github.com/prisma/prisma/blob/master/docs/1.2/04-Reference/03-Prisma-API/05-Subscriptions.md#subscribing-to-relation-changes

[FredyC]

So with Beta 2.0 out today, what is the status of subscriptions? Sadly documentation seems to be lacking search feature and I wasn't successful with manual lookup.

I remember that missing subscriptions was what driven me away from Prisma in the past. I am starting a new project now, considering Prisma again, but seeing this essential part is missing again in 2.0 makes me really sad.

I can imagine it's not an easy task but after years of experience in the field, I am surprised it's still not available.

[sapkra]

I've recently asked in slack when we can expect to get subscription support. This was the answer by Nikokas Burk (Prisma):

There's no official timeline but I don't except anything to be released in 2020.

Additionally there was recently something added to the Prisma 1 docs.

Prisma 2.0 will eventually have a robust and scalable event system that enables similar funcionality as subscriptions in Prisma 1.

If subscriptions are critical to your application and you regularly experience heavy load that makes subscriptions unreliable, there are alternatives to Prisma such as Hasura with GraphQL subscription support.

[Maxhodges]

@sapkra
our lead developer who has been working for Prisma for a couple of years, says:

subscription is a job that is not part of Prisma2. You can do it yourself if you run a GraphQL server, Prisma has nothing to do with it. They will release a tool for handling them in the future, but that will be an optional utility

So I'm not sure why Prisma would tell you to switch to Hasura when you only need to set up a GraphQL server and continue using Prisma.

our lead dev continues:

if you want subscriptions, then you need a server due to how WebSockets work (unrelated to Prisma).

@schickling can you please correct if this is not the case?

[sapkra]

@Maxhodges What I love on Prisma 1 is that I can just subscribe to database changes and can redirect the connection directly to the subscription of my own graphql server.

To set up subscriptions with Prisma 2 and listen to database changes requires to use another ORM I think, so that I can switch directly to the other one instead of using two simultaniously.

[Sytten]

It really is not the job of an ORM to do this kind of stuff. Under the hood the subscription stuff uses websocket and getting a production ready server at scale with proper security is really not trivial. You usually want to have a separate service (+redis or queue) for that because deployments will kill websocket connections and this is problematic in the context of continuous deployment. I am glad that the prisma dev did not prioritize this feature since it's not really useful for enterprise deployments.

[TheMrZZ]

@Sytten Any idea / tutorial on how to get started with that? I was gonna use Hasura for subscriptions, but if you tell me it's not _that_ easy, I'd like some hints on where to head.

[FredyC]

@TheMrZZ, On the contrary, I think Hasura took the right direction allowing for good use of subscriptions. I've chosen Hasura myself and loving it so far. I mean, I don't care about enterprise deployments (yet), but I am leaving that concern to future self if it turns out to be a problem.

[Sytten]

I have not done it yet for my projects, but any solution based on websocket is bound to be bad IMO if you have CI/CD because every time you restart the server it will kill your connections and that will happen very often if you push on every code merge. Usually you want to have a separate service that doesn't change very often to handle the connections and push to that service using a redis or a queue. Another way would be to use a managed service like pusher (which is most likely the route I will take because to cost of build vs buy is not good enough to justify building it).

[homerjam]

I spent a while getting prisma 1 setup with subscriptions and auth/tokens
over websockets and was happy with it. Then prisma 2 forced a rethink. I
went with the pusher route based on a recommendation from the vercel
platform and honestly it's way simpler and just as performant. I wish I
hadn't wasted time on subscriptions now. I made an example repo if anyone
is interested.

On Wed, 6 May 2020, 18:37 Émile Fugulin, [e-mail] wrote:

I have not done it yet for my projects, but any solution based on
websocket is bound to be bad IMO if you have CI/CD because every time you
restart the server it will kill your connections and that will happen very
often if you push on every code merge. Usually you want to have a separate
service that doesn't change very often to handle the connections and push
to that service using a redis or a queue. Another way would be to use a
managed service like pusher (which is most likely the route I will take
because to cost of build vs buy is not good enough to justify building it).

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/prisma/prisma/issues/298#issuecomment-624788631, or
unsubscribe
https://github.com/notifications/unsubscribe-auth/AAIBYGNH7YTZYICNESLBTXLRQGN35ANCNFSM4IIEGJJQ
.

[FredyC]

because every time you restart the server it will kill your connections

If you set up your client to reconnect and query the latest data when that happens, it shouldn't be a problem at all. Of course, it depends on how critical are those realtime updates for your app and how quickly can server turn over.

Also, keep in mind that it's unlikely you will be redeploying DB on every commit with the established data model. And even if you need to modify DB in some way, it's always better to do it as planned maintenance and inform users about it.

Unfortunately, Prisma 2 is currently recommending to do a subscription on your business logic side which means exactly the problem you are trying to avoid. On the other hand with Hasura having subscription on DB level it's unlikely to be a problem.

[MaxmaxmaximusGitHub]

Absolutely the whole backend should be reactive by default. declarative and reactive. like ReactJS, but on the server. frameworks like Prisma should increase the level of abstraction and add something like reactiveQuery. the reactive backend does not work slower, but FASTER than the usual backend if a sufficiently fine grain of reactivity is made. IMHO

like this:

function apiMethod() {
  let user = prisma.user.get({id})
  if(!user.admin) return null
  return prisma.user.getAll()
}

prisma.autorun(apiMethod, function(result){
   // result
})

and this function autostart when hes dependend changed, using selectors setters and getters prisma will detect depending fields, and if dependents CAN be change, prisma recall the function. like in Meteor framework. for example, if we make the mutation user.admin = true, then the dependency has changed, and the reactive function will be recall just in case.

Using these basic principles of reactivity, people will be able to build a declarative and reactive backend that will not slow down. since all the optimizations for them will be done by Redis and Prisma

Based on this, it will be possible to build GraphQL live queries and whatever.

we can create a class Tracker like this:

Tracker.addDependencyType("selector", class SQLSelectorDependency {
  constructor (dependSelector) {
    this.dependSelector = dependSelector
  }

  maybeChange (sqlMutation){
     // check if sqlMutation CAN change this.dependSelector  result
  }
})


// reactive tracker
tracker = new Tracker()

// on reactive function calls we register dependinigs
tracker.depend("selector", "SQL SELECTOR CODE")


// on mutations
tracker.checkMaybeChange("selector", "SQL MUTATION CODE")

and in this way, users can add custom dependencies, as well as manually call them, for example, if a message comes from Redis. thus, even if the user interacts with the database not through Prisma, the Prisma reactivity system will feel it. in reactive systems, you can perform pure reactive functions at any time, just in case, and for optimization we can compare the previous result and the new one, and if they are the same, do not send data to the client. just like React doesn't update DOM =) This is an example concept explaining the overall architecture, in this way Prisma will support reactive work with the database. This killerfeature will make everyone's brain =)

[MaxmaxmaximusGitHub]

hmm, I thought it’s not necessary to integrate such functionality into the core of Prisma, it can be implement as a wrapper, hmmmmmmmm. I think I'll do it) I will report on progress here

[johhansantana]

I think using something like firebase just to ping your front end that the data has been updated would suffice and be an alternative in some situations?

[MaxmaxmaximusGitHub]

@johhansantana reactivity knows exactly when the data has been updated, but polly makes the ddos ​​of the server constant requests, without using intelligent heuristics, unlike reactivity. why do you think polly is more effective than reactivity?

[johhansantana]

@johhansantana reactivity knows exactly when the data has been updated, but polly makes the ddos ​​of the server constant requests, without using intelligent heuristics, unlike reactivity. why do you think polly is more effective than reactivity?

I didn’t really said that it’s more effective, just a quick alternative if you really needed the real-time without much work. But there should definitely be an easier way to create subscriptions though.

[MaxmaxmaximusGitHub]

I found! graphql server https://hasura.io already supports scalable realtime subscriptions =)

its like SQL but graphql to PostgreSQL, and it have subscriptions on data change

[FredyC]

@MaxmaxmaximusGitHub I think you shouldn't be discussing other products in Prisma repo, especially with off-topic questions. Hasura has Discord server, go there for questions.

That said I switched to Hasura as well because of subscriptions. Maybe Prisma can learn a thing or two from them how to approach that? It's open-source after all.

[homerjam]

Absolutely the whole backend should be reactive by default. declarative and reactive. like ReactJS, but on the server. frameworks like Prisma should increase the level of abstraction and add something like reactiveQuery. the reactive backend does not work slower, but FASTER than the usual backend if a sufficiently fine grain of reactivity is made. IMHO

like this:

function apiMethod() {
  let user = prisma.user.get({id})
  if(!user.admin) return null
  return prisma.user.getAll()
}

prisma.autorun(apiMethod, function(result){
   // result
})

and this function autostart when hes dependend changed, using selectors setters and getters prisma will detect depending fields, and if dependents CAN be change, prisma recall the function. like in Meteor framework. for example, if we make the mutation user.admin = true, then the dependency has changed, and the reactive function will be recall just in case.

Using these basic principles of reactivity, people will be able to build a declarative and reactive backend that will not slow down. since all the optimizations for them will be done by Redis and Prisma

Based on this, it will be possible to build GraphQL live queries and whatever.

we can create a class Tracker like this:

Tracker.addDependencyType("selector", class SQLSelectorDependency {
  constructor (dependSelector) {
    this.dependSelector = dependSelector
  }

  maybeChange (sqlMutation){
     // check if sqlMutation CAN change this.dependSelector  result
  }
})


// reactive tracker
tracker = new Tracker()

// on reactive function calls we register dependinigs
tracker.depend("selector", "SQL SELECTOR CODE")


// on mutations
tracker.checkMaybeChange("selector", "SQL MUTATION CODE")

and in this way, users can add custom dependencies, as well as manually call them, for example, if a message comes from Redis. thus, even if the user interacts with the database not through Prisma, the Prisma reactivity system will feel it. in reactive systems, you can perform pure reactive functions at any time, just in case, and for optimization we can compare the previous result and the new one, and if they are the same, do not send data to the client. just like React doesn't update DOM =) This is an example concept explaining the overall architecture, in this way Prisma will support reactive work with the database. This killerfeature will make everyone's brain =)

Doesn't this sort of reactivity rely on a single, always on server?

[MaxmaxmaximusGitHub]

@FredyC I do not DISCUSS another product here, I searched and found a solution, and shared it with other people.

That said I switched to Hasura as well because of subscriptions. Maybe Prisma can learn a thing or two from them how to approach that? It's open-source after all.

+++ I am pushing this

@homerjam

Doesn't this sort of reactivity rely on a single, always on server?

Redis, or PostgreSQL triggers can call tracker.checkMaybeChange("selector", "SQL MUTATION CODE") on every subscribed servers

[homerjam]

Redis, or PostgreSQL triggers can call tracker.checkMaybeChange("selector", "SQL MUTATION CODE") on every subscribed servers

Yes, but as you've noted it requires always on servers to be subscribed (both for the Redis event and for the client websocket). This is why in a serverless environment it's non trivial to provide true graphql subscriptions. It was also my first thought but as you need an intermediary for Redis etc I ended up going with the Pusher option. It's simpler, and easier to scale.

[hatchli]

I spent a while getting prisma 1 setup with subscriptions and auth/tokens over websockets and was happy with it. Then prisma 2 forced a rethink. I went with the pusher route based on a recommendation from the vercel platform and honestly it's way simpler and just as performant. I wish I hadn't wasted time on subscriptions now. I made an example repo if anyone is interested.
…
On Wed, 6 May 2020, 18:37 Émile Fugulin, @.*> wrote: I have not done it yet for my projects, but any solution based on websocket is bound to be bad IMO if you have CI/CD because every time you restart the server it will kill your connections and that will happen very often if you push on every code merge. Usually you want to have a separate service that doesn't change very often to handle the connections and push to that service using a redis or a queue. Another way would be to use a managed service like pusher (which is most likely the route I will take because to cost of build vs buy is not good enough to justify building it). — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#298 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAIBYGNH7YTZYICNESLBTXLRQGN35ANCNFSM4IIEGJJQ .

I'd love to look it over!

[homerjam]

I'd love to look it over!

@hatchli https://github.com/homerjam/prisma2-example-yoga

[MaxmaxmaximusGitHub]

@homerjam https://hasura.io has reactive hooks, if data in db changes, hashura go to special link (link can call AWS lambda function or something)

but any solution based on websocket is bound to be bad IMO if you have CI/CD because every time you restart the server it will kill your connections

lol, just, reconnect websockets. websockets based solutions are the most advanced and easiest to implement, these are the best solutions possible. they allow you to optimize the load on the server and the client’s network, and add reactive content delivery, reconnect themselves when the connection is disconnected, etc.

[aakashkag]

Hello Prisma Team :)
Thanks for awesome project.
Is there any plan or timeline to implement this feature.

[BruceSuperProgramer]

Really love to see your team to implement this feature.

[kinglouie]

Could you guys stop spamming this issue, I'm getting notifications for everyone asking for this feature here, just +1 the issue instead.

[giva9712]

+1

[chris-aeviator]

@schickling Will anything happen on this feature-request, given it's >1 year old and alot of other tools have this out of the box.

The >250 people indicating their interest in this feature should at least get the discussion back to track, also I am running prisma in a production environment and also question how future proof prisma is going to be in terms of community support.

[thebiglabasky]

Hi @chris-aeviator

While this is indeed an old issue and a much-requested one, we decided to first strengthen our core and provide a reliable experience by making drastic improvements on migrations (with a first milestone planned in the coming month) and adding support for core querying capabilities, catering to most fundamental needs before getting on more advanced use cases such as these ones.
You can find more on our roadmap: https://pris.ly/roadmap

This unfortunately pushes this one further down the road (6+ months).
We are reevaluating our priorities on a regular basis. If these were to evolve favorably for this issue, we'll be posting an update.

Thanks

[chris-aeviator]

@thebiglabasky thank you for the transparent and honest answer! Even though it will take longer I count that as great support :) :beers:

[Hebilicious]

One approach that I would like to mention is the one used by supabase.
It works by listening to the postgres replication (instead of relying on NOTIFY), then it broadcast the changes over websockets.
Even though it's postgres only, I believe a similar approach could work with sqlite and mysql.
The API is very straightforward, here's the example from the supabase/realtime readme :

import { Socket } = '@supabase/realtime-js'

var socket = new Socket(process.env.REALTIME_URL)
socket.connect()

// Listen to all changes to user ID 99
var allChanges = this.socket.channel('realtime:public:users:id.eq.99')
  .join()
  .on('*', payload => { console.log('Update received!', payload) })

// Listen to only INSERTS on the 'users' table in the 'public' schema
var allChanges = this.socket.channel('realtime:public:users')
  .join()
  .on('INSERT', payload => { console.log('Update received!', payload) })

// Listen to all changes in the database
let allChanges = this.socket.channel('realtime:*')
  .join()
  .on('*', payload => { console.log('Update received!', payload) })

Postgraphile also implements it's live queries feature with a similar approach.

Maybe prisma could implement something inspired by this.
We can imagine the prisma client exposing a pubsub interface, so that you can write a graphQL server resolver like this :

const resolvers = {
  Subscription: {
    postAdded: {
      subscribe: () => prisma.$pubsub.subscribe([{model: "Post", operations: ["create"]}}]),
    }
  }
};

Then for the ability to publish custom "events", maybe something IPC based like, graphql-postgres-subscriptions ?

However even though it looks neat, there's a few caveats, for example it doesn't change the fact that websockets and FaaS don't play well together.
It's important to mention than overall, prisma currently works well with those existing tools I mentioned, and you can combine all those libraries and build a nice postgres powered graphQL server with prisma, nexus-schema, subapase/realtime, apollo server and graphql-postgres-subscriptions.

This raises the question : Should prisma be a standalone tool that transform any datasource it talks to into a websocket/realtime application ?
Even though I really want to simplify the workflow of building such applications, the more I think about it, the more I think this should be a separate plugin/project and not part of prisma core. Now I'm not saying this shouldn't be tackled at some point by prisma, but there's plenty of things that takes priority here (A lot of important features are missing when it comes to DB access/migrations/modeling data ...).
Maybe this should be a project a little bit like Nexus ? @thebiglabasky Any indication of the direction you guys are considering regarding this?
I agree with @homerjam approach here, websockets aren't trivial and a service like pusher provides you with a good compromise (If someone knows why AWS still doesn't have a pusher service, please let me know).

GraphQL specific thoughts :
If you're using graphQL, you can get creative these days : you get out of the box graphQL subscriptions with Hasura and Postgraphile, so you can combine those with prisma. You can build your main graphQL service with Prisma + Nexus/Schema + Apollo, have a secondary service that use Hasura/Postgraphile, then you can use something like GraphQL mesh (or Apollo federation) to build a "gateway" graphQL server, that you can then expose to your other services/clients.

Alternatives :
You can consider having a separated/non prisma service that deal with realtime things only.
There's a lot of alternatives databases/server/services that offer realtime features out of the box. I've mentioned Hasura, Postgraphile and Supabase, but you can consider rethink DB (I thought the project died-off but they dropped 2.4.1 earlier this month), or parse server.
Regarding commercial products, pusher is fantastic, but you can also build your own websocket/socket.io server and run it with something like fargate, or you can go with the popular google firebase and aws appsync to name a few.

[thebiglabasky]

Thanks @Hebilicious for sharing these elaborate thoughts!

Maybe this should be a project a little bit like Nexus ? @thebiglabasky Any indication of the direction you guys are considering regarding this?

As you correctly mention there are multiple parts to all this, one about listening to changes happening at the database level, which Prisma could eventually consider looking at, and another on the GraphQL layer. But as you _also_ correctly pointed out, we first need to have core needs properly covered (atomic operations, better alternatives to long-running transactions support, improved JSON filtering, group by support...). This means that we are unlikely to do anything about real-time API before some time (beyond 6+ months timeframe).

Running this as a side project would be complex as we're currently already spread across a few different things (migrate, studio, more core client features, and new database connectors).
I don't see us opening yet another track as it would come at the expense of one of the above.

We are making good progress on these though! So when revisiting the themes based on this progress (we do so every 2-3 months and share progress on the roadmap, we'll reconsider these use cases for sure.

[dfrho]

Thanks for tracking, and for a great library. My use case would be for real time chat. Will try the graphql-yoga workaround in the meantime.

[kamathln]

I loved the way they have tackled this in SailsJS. If you want the server to be scalable, yet stateless, just use redis as the liason - and it is optional. You could use Websockets directly from SailsJS or use redis as liason at config time. ( Refer Scaling Sails.js )

[NikoMontana]

Oh man.. I can´t believe this. Seems that I have to go with Entity Framwork and C# again for the backend.

[pie6k]

My opinion about serverless: while it's great and I use it a lot, I would be totally fine to have real-time DB that doesn't work with serverless.

Any sort of sockets etc "by definition" are long-living, so for me it would be trivial to understand and accept that if I want real-time, I need a server.

[guerrerocarlos]

AWS Lambda has support for websockets, connections are long-lived and every message is processed by a serverless function. I don't see the problem in using subscriptions with Prisma and a serverless backend.