Postgres with Drizzle · Chapter 24 of 40

The watchtower

By the end of this chapter you can have Postgres tell your code the moment something happens, instead of asking it again and again. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Database Powers
  • 5 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.

Why you need this

Meera wants new posts and friend requests to appear in members' feeds straight away. Today every open browser tab asks the server every few seconds whether anything is new, and the server asks the database. With thousands of members online, that is thousands of queries a minute, and nearly all of them answer "nothing".

Asking less often makes the feed slow; asking more often makes the database busier. Postgres can turn it around: code that cares says LISTEN, and whoever changes something sends a notification that Postgres pushes to every listener.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    sendNotification: send a message on a channel

    Write sendNotification(channel, payload). Both are strings. It sends a notification with that payload on that channel, using pg_notify with the two values as query parameters.

    Return an object with sent: the value true.

  2. Task 2

    listenAndReceive: hear your own message

    Write listenAndReceive(channel, messageToSend). Both are strings. On a client of its own from the pool, it listens on channel, sends messageToSend on that channel through the pool, and waits for the notification to arrive. Then it stops listening and releases the client.

    Return an object with channel and payload: the channel name and the payload of the notification it received. The check uses chat_channel and ping from test.

  3. Task 3

    createNotifyTrigger: announce every new post

    Write createNotifyTrigger(). It creates (or replaces) a trigger function named notify_new_post() that sends a notification on the channel new_post whose payload is a JSON text with the new post's id, content and author_id, and a trigger named trg_notify_new_post that runs it after every insert, for each row of posts.

    Return an object with triggerCreated: the value true. Afterwards: trg_notify_new_post exists on posts.

  4. Task 4

    getNotificationChannels: the channels being listened on

    Write getNotificationChannels(). It asks Postgres which channels the connection that runs the query is listening on, with pg_listening_channels().

    Return an array of channel names: plain strings, not row objects. Through the pool this is usually an empty array.

  5. Task 5

    jsonPayloadNotification: send an object as JSON

    Write jsonPayloadNotification(channel, data). channel is a string and data an object. It turns data into a JSON string and sends it as the payload on channel with pg_notify.

    Return an object with sent (the value true) and payload (the JSON string that was sent, not the object). The check parses it and expects userId 42 and action login.

What you will learn

  • pg_notify(channel, payload): send a text message on a named channel.
  • LISTEN on a client of its own, and the notification event.
  • A trigger that notifies: announce every new row automatically.
  • pg_listening_channels(): the channels this connection listens on.

More in Database Powers

Build The watchtower yourself

MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.