First implementation - Discussions

[Cauen]

Hello there, although this and https://github.com/prisma/prisma/issues/905 are both very old and popular issues, apparently they won't be in the Prisma team's sights in the near future.

Fortunately, I think it is possible to make a simple yet good solution (at least for Postgres) by combining prisma custom generator + auto generated triggers with pg_notify + prisma extension

I won't be able to implement it at the moment, but I'll drop the idea and I'd be very happy to hear your opinion (I will create a repository and an issue for anyone who wants to discuss without polluting this issue)

The idea

1. We install the custom generator: pnpm i prisma-generator-postgres-realtime
2. We configure some settings (like this generator):

   {
   "migrationsFolderPath": "./prisma/migrations",
   "extensionPath": "./src/realtime/prismaExtension.ts",
   "realtimeConfigPath": "./src/realtime/configs.json",
   // maybe other options like: excludeModels: ["User"]
   }

• migrationsFolderPath: is to enable this generator to automatically migrations to create custom triggers like this:

• CREATE OR REPLACE FUNCTION notify_event_trigger() RETURNS TRIGGER AS $$ BEGIN -- Convert the NEW row to JSON and send it as a notification PERFORM pg_notify('event_trigger', row_to_json(NEW)::text); RETURN NEW; END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER usertableupdate AFTER INSERT OR UPDATE OR DELETE ON "Document" FOR EACH ROW EXECUTE FUNCTION notify_event_trigger();

- extensionPath: is to enable generator to create custom extension to us use later...
- realtimeConfigPath: is to generator keep a record of what's already done and avoid re-creating migrations twice

3. On [prisma generate](https://www.prisma.io/docs/orm/prisma-client/setup-and-configuration/generating-prisma-client): we can check the realtimeConfigPath and create a custom migration (if was the case) and create the custom extension
4. With migrations created, we need to re-execute the migrate command and the database will start to send pg_notify on every change... 
5. Extension will make it easy to subscribe: ([maybe enriched data](https://www.cybertec-postgresql.com/en/tracking-changes-in-postgresql/))
```ts
// Extension usage
import { realtimeExtension } from '@/realtime/prismaExtension'

export const prisma = new PrismaClient
  .$extends(realtimeExtension)

// client level https://www.prisma.io/docs/orm/prisma-client/client-extensions/client
prisma.subscribe(({ timestamp, operation, model, newData, oldData, user }) => {
  console.log({ operationDoneDirectlyFromDatabase: `${operation} in ${model}` })
})

// model level https://www.prisma.io/docs/orm/prisma-client/client-extensions/model
prisma.user.subscribe(...)

So the code under the extension will perform operations like this:

// Code under extension
const client = new Client({
  connectionString: process.env.DATABASE_URL,
});

client
    .connect()
    .then(() => {
        console.log("Connected to PostgreSQL database");
        client
            .query("LISTEN event_trigger")
            .then(() => console.log("Listening for table changes"))
            .catch((error) =>
                console.error("Error listening for table changes:", error),
            );
    })
    .catch((error) =>
        console.error("Error connecting to PostgreSQL database:", error),
    );

client.on("notification", async (notification) => {
    console.log("Received notification:", notification);
});

In this way we can:

1. Keep simple to maintain real-time database
2. Type-safe data usage

I would love to hear counterpoints and opinions here.

[hongkongkiwi]

There seems to be code in your repo now and this comment doesn't seem to match the readme, e.g. you don't mention the postgres notify setup in the README.

Are the setup instructions still the same as above?

[Cauen]

Hello @hongkongkiwi

you don't mention the postgres notify setup in the README.

You dont need to setup this, the extension will perform this :D

[hongkongkiwi]

Fantastic! I'm very excited about this extension, really great work.