Real-Time Databases

In our design, the database only stores the messages. Our server’s code gets each new message to the clients: the POST handler saves the message and then calls sendToRoom.

Some databases can tell us when data changes. A client of the database, such as our server, subscribes to some data, and the database sends it every change to that data as it happens. A database built around this feature is called a real-time database. Firestore, from Google’s Firebase, is one. Supabase, a service built on PostgreSQL, is another. Other databases have a form of it too: MongoDB, for example, calls it change streams.

To send changes as they happen, the database keeps a connection open to each subscriber, the same idea as our WebSockets. Supabase uses a WebSocket. MongoDB uses its ordinary connection and holds each request for more changes until there is something to send, much like long polling.

Our server subscribes

Suppose Courselore’s database were PostgreSQL run by Supabase, with the same tables: chat_rooms and chat_messages. Our server keeps its API and its WebSockets. But instead of calling sendToRoom after each save, it subscribes to new rows in chat_messages once, when it starts:

supabase
  .channel("new-messages")
  .on(
    "postgres_changes",
    {
      event: "INSERT",
      schema: "public",
      table: "chat_messages",
    },
    (change) => {
      const message = change.new;
      sendToRoom(message.room_id, { type: "message.created", message });
    }
  )
  .subscribe();

Each time a row is inserted into chat_messages, the database sends it to our server, and our server sends it on the connections in the room’s set. The POST handler now only saves the message and answers Priya.

This is a sketch. sendToRoom is the same code as before.

What changes in the design

• Saving no longer has to notify. Any code that inserts a message reaches the clients, including code we add later, or another program that writes to the same database. Before, every place that saved a message had to remember to call sendToRoom.
• Several API instances need no channel. Each instance subscribes to the database’s changes and sends each new message on its own connections. The database does the job that the channel between the instances did.

What stays the same: the WebSocket between our server and each browser, the room sets, the pings, and catching up with the REST API. The database tells our server about changes. Our server still has to get them to the browsers.

The cost is a database that does more work, since it sends every change to every subscribed instance, and a design that depends on a database with this feature.