SQLiteCloud Pub/Sub System

Publish/Subscribe (Pub/Sub) is a messaging pattern that enables asynchronous communication between multiple applications. In the context of SQLiteCloud, Pub/Sub provides a robust way to deliver real-time updates or custom messages to subscribed clients when data changes or explicit notifications are issued.

This feature is particularly useful for building reactive applications, synchronizing distributed systems, and enabling event-driven architectures around your SQLite databases.

Core Concepts

Publishers

Publishers are entities that send messages or notifications. In SQLiteCloud, a publisher can:

• Modify a database (triggering automatic Pub/Sub events on commit).
• Explicitly send a message using the NOTIFY command, even without making changes to the database.

Any client with write access—such as a web server, mobile app, or background process—can act as a publisher.

Subscribers

Subscribers are clients that listen for messages or data change events. They can subscribe to:

• A channel representing a database table (to receive change events).
• A named message channel (for general-purpose messages).

Subscribers will receive all messages published on the channels they subscribe to.

Channels

Channels are the communication endpoints used for Pub/Sub messaging. A channel can be:

• A database table name, used to deliver change notifications.
• A custom channel name, used to send arbitrary messages.

Channels are not bound to any database entity unless explicitly tied to a table.

Benefits of Pub/Sub in SQLiteCloud

• Real-time Updates Instantly notify subscribers when data changes. Useful for dashboards, live feeds, or collaborative apps.

• Scalability One publisher can broadcast to many subscribers with minimal overhead on the database.

• Message Filtering Subscribers can choose specific channels, reducing unnecessary data traffic.

• Fault Tolerance Notifications are delivered reliably. If a subscriber or publisher disconnects, the system continues to function without losing messages.

Payload Format

All Pub/Sub messages in SQLiteCloud are delivered as JSON objects. The structure of the payload depends on the type of event:

1. NOTIFY Message Payload

Sent explicitly by clients using the NOTIFY command.

{
  "sender": "UUID",
  "channel": "name",
  "channel_type": "MESSAGE",
  "payload": "Message content here"
}

• sender: UUID of the client that sent the message.
• channel: Target channel name.
• channel_type: Always "MESSAGE" for this type.
• payload: Optional message content.

2. Database Table Change Payload

Generated automatically when a transaction modifies a subscribed table. Triggered at COMMIT time and may include multiple row operations.

{
  "sender": "UUID",
  "channel": "tablename",
  "channel_type": "TABLE",
  "sqlite_pk_name": ["id", "col1"],
  "payload": [
    {
      "sqlite_type": "INSERT",
      "id": 12,
      "col1": "value1",
      "col2": 3.14
    },
    {
      "sqlite_type": "DELETE",
      "sqlite_pk_value": [13]
    },
    {
      "sqlite_type": "UPDATE",
      "id": 15,
      "col1": "newvalue",
      "col2": 0.0,
      "sqlite_pk_value": [14]
    }
  ]
}

Field Descriptions:

• sender: UUID of the client initiating the change, or 0 if triggered by the server.

• channel: Table name where the change occurred.

• channel_type: "TABLE".

• sqlite_pk_name: Array of primary key column names for the table.

• payload: Array of individual row operations.

  • sqlite_type: "INSERT", "UPDATE", or "DELETE".
  • sqlite_pk_value: Previous primary key values (used in DELETE or UPDATE).
  • Other keys represent column values (for INSERT and UPDATE).

Tip: If a client is subscribed to a channel and also publishes to it, it will receive its own notifications. Use the sender UUID to filter out self-generated events if needed.

Example SQL Usage

> USE DATABASE test.sqlite
OK

> GET SQL foo
CREATE TABLE "foo" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "col1" TEXT,
  "col2" TEXT
)

> LISTEN TABLE foo
OK

Example Event Payloads

DELETE

DELETE FROM foo WHERE id=14;

{
  "sender": "b7a92805-ef82-4ad1-8c2f-92da6df6b1d5",
  "channel": "foo",
  "channel_type": "TABLE",
  "sqlite_pk_name": ["id"],
  "payload": [{
    "sqlite_type": "DELETE",
    "sqlite_pk_value": [14]
  }]
}

INSERT

INSERT INTO foo(col1, col2) VALUES ('test100', 'test101');

{
  "sender": "b7a92805-ef82-4ad1-8c2f-92da6df6b1d5",
  "channel": "foo",
  "channel_type": "TABLE",
  "sqlite_pk_name": ["id"],
  "payload": [{
    "sqlite_type": "INSERT",
    "id": 15,
    "col1": "test100",
    "col2": "test101"
  }]
}

UPDATE (Primary Key Changed)

UPDATE foo SET id=14, col1='test200' WHERE id=15;

{
  "sender": "b7a92805-ef82-4ad1-8c2f-92da6df6b1d5",
  "channel": "foo",
  "channel_type": "TABLE",
  "sqlite_pk_name": ["id"],
  "payload": [
    {
      "sqlite_type": "DELETE",
      "sqlite_pk_value": [15]
    },
    {
      "sqlite_type": "INSERT",
      "id": 14,
      "col1": "test200",
      "col2": "test101"
    }
  ]
}

Summary

SQLiteCloud’s Pub/Sub system enables:

• Real-time data sync across applications.
• Lightweight messaging between distributed components.
• Fine-grained, reliable notifications with minimal overhead.

By leveraging Pub/Sub, developers can build responsive, event-driven applications that scale seamlessly and remain in sync with the database state.

Client Library Examples

import { Database } from '@sqlitecloud/drivers'
import { PubSub, PUBSUB_ENTITY_TYPE } from '@sqlitecloud/drivers/lib/drivers/pubsub'

let database = new Database('sqlitecloud://user:password@xxx.sqlite.cloud:8860/chinook.sqlite')
// or use sqlitecloud://xxx.sqlite.cloud:8860?apikey=xxxxxxx

const pubSub: PubSub = await database.getPubSub()

await pubSub.listen(PUBSUB_ENTITY_TYPE.TABLE, 'albums', (error, results, data) => {
  if (results) {
    // Changes on albums table will be received here as JSON object
    console.log('Received message:', results)
  }
})

await database.sql`INSERT INTO albums (Title, ArtistId) values ('Brand new song', 1)`

// Stop listening changes on the table
await pubSub.unlisten(PUBSUB_ENTITY_TYPE.TABLE, 'albums')