Using Postgres LISTEN/NOTIFY + Server-Sent Events for Real-Time Web Apps

A recent software development project called for live events to be pushed to the browser. A fortunate combination of tech and project circumstances made it pretty easy. One of these circumstances is that our project uses a PostgreSQL database, which comes with a built-in pub/sub mechanism.

Postgres LISTEN/NOTIFY

LISTEN/NOTIFY is a postgres feature that provides pub/sub behavior over a standard database connection and driver. I’ll illustrate with a pair of TypeScript CLI scripts.

Clients can subscribe to a named channel, and hold a connection open to listen for events:

#!/usr/bin/env pnpm tsx

import pg from "pg";

console.log("Listening for messages on demo_channel. Press Ctrl+C to stop.");

const dbClient = new pg.Client({
  connectionString: process.env.DATABASE_URL,
});
await dbClient.connect();

try {
  await dbClient.query("LISTEN demo_channel");

  dbClient.on("notification", (msg) => {
    const timestamp = new Date().toLocaleTimeString();
    console.log(`[${timestamp}] ${msg.payload || "no payload"}`);
  });

  process.on("SIGINT", async () => {
    console.log("\nClosing connection...");
    await dbClient.query("UNLISTEN demo_channel");
    await dbClient.end();
    process.exit(0);
  });
} catch (err) {
  console.error("Error:", err);
  await dbClient.end();
  process.exit(1);
}

Sending a message is even easier:

#!/usr/bin/env pnpm tsx

import pg from "pg";

const message = process.argv[2] || "Hello from CLI";
const dbClient = new pg.Client({
  connectionString: process.env.DATABASE_URL,
});
await dbClient.connect();

try {
  console.log(`Sending message: "${message}"`);
  await dbClient.query("SELECT pg_notify('demo_channel', $1)", [message]);
  console.log("Message sent!");
} finally {
  await dbClient.end();
}

With these scripts, a couple terminal windows can talk to each other:

So this allows one chunk of server code to tell another when something happened, which is half of the puzzle. But how do we get that down to the browser?

Server-Sent Events

Server-sent events are a web standard for pushing data from a server to a client browser. In short, the client opens a connection to a particular API endpoint, and keeps it open to listen for events. For more on this, see Nick’s post.

In our Next.js web app, such an endpoint can be implemented as a route handler:

export async function GET() {
  const encoder = new TextEncoder();
  let intervalHandle: NodeJS.Timeout | undefined;

  const stream = new ReadableStream({
    async start(controller) {
      intervalHandle = setInterval(() => {
        controller.enqueue(encoder.encode("Hello!\n\n"));
      }, 5000);
    },
    async cancel() {
      if (intervalHandle) {
        clearInterval(intervalHandle);
      }
    },
  });

  return new Response(stream, {
    headers: {
      "Content-Type": "text/event-stream",
      "Cache-Control": "no-cache",
      Connection: "keep-alive",
    },
  });
}

Web clients can use EventSource to listen for events:

useEffect(() => {
  const eventSource = new EventSource("/api/events");

  eventSource.onmessage = (event) => {
    console.log("SSE Message:", event.data);
  };

  eventSource.onerror = (error) => {
    console.error("SSE Error:", error);
    eventSource.close();
  };

  return () => {
    eventSource.close();
  };
}, []);

When this is connected and working, events can be seen in your browser’s dev tools:

Combining them together

The examples above combine nicely into a route handler that listens for database notifications and sends them down as SSE events:

import pg from "pg";

export async function GET() {
  const encoder = new TextEncoder();

  const dbClient = new pg.Client({
    connectionString: process.env.DATABASE_URL,
  });
  await dbClient.connect();

  const _result = await dbClient.query("LISTEN demo_channel");

  const stream = new ReadableStream({
    async start(controller) {
      dbClient.on("notification", (msg) => {
        const message = `data: ${msg.payload || "(no payload)"}\n\n`;
        controller.enqueue(encoder.encode(message));
      });
    },
    async cancel(reason) {
      await dbClient.query("UNLISTEN demo_channel");
      await dbClient.end();
    },
  });

  return new Response(stream, {
    headers: {
      "Content-Type": "text/event-stream",
      "Cache-Control": "no-cache",
      Connection: "keep-alive",
    },
  });
}

With a server action to send notifications and a quick UI, a multi-browser demo comes together:

Tech Considerations

This approach happens to fit our current project nicely, but here are a few considerations to think through for yours:

• Request timeouts – if your host aggressively kills connections that stay open for a long time, you may need to look into configuring more generous limits.
• Scale and server resources. For this approach to work, your server needs to pick up the phone for each connected user, and stay on the line the whole time they’re around. This is fine with our internal business app’s persistent server and very small number of users, but it’d be a different story if you were looking to serve hordes on a serverless host + CDN.
• Database connections. In addition to each user occupying a server connection, they’ll also take up a database connection. Listen/notify works well over a standard, direct TCP `postgres://` connection, but may not over other channels (e.g. pgbouncer transaction pooling or Neon’s https transport).

Try it out!

Example code is available on GitHub at jrr/listen-notify-sse-demo.

For more cool Postgres tricks, see Do You Really Need Redis? How to Get Away with Just PostgreSQL.