Database setup

The Ably Database Connector requires two tables in your database: an outbox table and a nodes table.

When starting the Database Connector, you can manually create the required tables using the CLI or automatically.

Use the create tables command to automatically create the required tables in your database. create tables requires the application to use a PostgreSQL user that has been granted CREATE permissions on the desired schemas.

The following is an example command to create the required tables:

docker run -it --entrypoint="/adbc" ghcr.io/ably-labs/adbc:latest create tables

Copy
Copied!

The table names and schemas are specified via the following configuration options.

• ADBC_OUTBOX_TABLE_TABLE_SCHEMA
• ADBC_OUTBOX_TABLE_TABLE_NAME
• ADBC_NODES_TABLE_TABLE_SCHEMA
• ADBC_NODES_TABLE_TABLE_NAME

Use the ADBC_NODES_TABLE_AUTO_CREATE and ADBC_OUTBOX_TABLE_AUTO_CREATE configuration options to set the Database Connector to create the tables at startup, if they don’t already exist.

If ADBC_POLL_FIXED_RATE is set to false (which is the default value), ADBC_OUTBOX_TABLE_AUTO_CREATE will also create a trigger that invokes a function that uses LISTEN/NOTIFY to notify the application when new data is inserted into the outbox table.

To apply migrations to your database, use the following data definition language (DDL):

CREATE TABLE IF NOT EXISTS your_nodes_table (
  id TEXT PRIMARY KEY,
  expiry TIMESTAMP WITHOUT TIME ZONE NOT NULL
);


CREATE TABLE IF NOT EXISTS your_outbox_table (
	sequence_id  serial PRIMARY KEY,
	mutation_id  TEXT NOT NULL,
	channel      TEXT NOT NULL,
	name         TEXT NOT NULL,
	rejected     boolean NOT NULL DEFAULT false,
	data         JSONB,
	headers      JSONB,
	locked_by    TEXT,
	lock_expiry  TIMESTAMP WITHOUT TIME ZONE,
	processed    BOOLEAN NOT NULL DEFAULT false
);

Copy
Copied!

If you are using the default value of false for ADBC_POLL_FIXED_RATE, the application will use a trigger with LISTEN/NOTIFY to only poll for records when the data in the outbox changes. To support this behavior, create a function in the same schema as your outbox table with the same name as the outbox table with a suffix of _notify:

CREATE OR REPLACE FUNCTION YOUR_OUTBOX_TABLE_SCHEMA.YOUR_OUTBOX_TABLE_NAME_notify()
RETURNS trigger AS $$
BEGIN
	PERFORM pg_notify('ably_adbc'::text, ''::text);
	RETURN NULL;
EXCEPTION
	-- ensure this function can never throw an uncaught exception
	WHEN others THEN
		RAISE WARNING 'unexpected error in %s: %%', SQLERRM;
		RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Copy
Copied!

Create a trigger in the same schema as your outbox table with the same name as the outbox table with a suffix of _trigger that invokes the function when new data is inserted into the outbox table. For example:

CREATE TRIGGER your_outbox_table_schema.your_outbox_table_name_trigger
AFTER INSERT ON your_outbox_table_schema.your_outbox_table_name
FOR EACH STATEMENT EXECUTE PROCEDURE
your_outbox_table_schema.your_outbox_table_name_notify();

Copy
Copied!

The Database Connector only needs to interact with the outbox and nodes tables and does not rely on any other state in the database. This simplifies the security model as the Database Connector’s permissions can be locked down to specific tables.

The Database Connector will connect to your PostgreSQL database using the connection details specified in your configuration. The PostgreSQL user used by the Database Connector requires SELECT and DELETE privileges on the outbox and nodes tables.

The following is an example of creating a role with the necessary privileges against each table:

CREATE ROLE YOUR_USER LOGIN PASSWORD 'your_database' VALID UNTIL 'infinity';
GRANT CONNECT ON DATABASE your_database to your_user_USER;
GRANT SELECT ON your_outbox_schema.your_outbox_table to your_user;
GRANT DELETE ON your_outbox_schema.your_outbox_table to your_user;
GRANT SELECT ON your_nodes_schema.your_nodes_table to your_user;
GRANT DELETE ON your_nodes_schema.your_nodes_table to your_user;

Copy
Copied!

The Database Connector utilizes the outbox pattern to send realtime, application-defined change events via Ably’s global edge messaging platform. The outbox pattern allows for the reliable publication of an ordered sequence of change event messages over Ably, providing exactly-once delivery guarantees. This pattern involves the use of an outbox table that you create in your database.

The Database Connector automatically monitors new records written to the outbox table and publishes them as messages over channels.

When your backend server processes a request to update data in your database, it should also write a corresponding change event to the outbox table within the same transaction as the underlying mutation. The Database Connector then publishes the change event, written to the outbox, to the channel in the exact order that the underlying change to the data is committed to the database. Furthermore, the Database Connector automatically deletes records from the table once the records are successfully processed.

This example adds a comment and logs the event in an outbox:

BEGIN;
-- mutate your data, e.g.:
INSERT INTO comments (comment) VALUES ('New comment!');
-- write change event to outbox, e.g.:
INSERT INTO outbox (mutation_id, channel, name, data) VALUES ('my-mutation-id', 'posts:123', 'addComment', 'New comment!');
COMMIT;

Copy
Copied!

The following describes the columns in the outbox table:

mutation_id

is the identifier for the mutation.

channel

is the channel to deliver the mutation, in this case, posts:123.

name

indicates a comment is added.

data

is the actual content or details of the mutation.

For more information, see outbox table schema.

Clients using the Models SDK observe changes to the database state by consuming a stream of ordered database change events. An update to the data in the database must happen atomically, with the corresponding change event broadcast to all clients, so that they see a consistent view of the data.

The Database Connector uses an outbox table to capture change events. When you update data in the database, you write a change event record to the outbox in the same database transaction as the update. This process ensures that change event records are written atomically to the outbox with the underlying update in the correct order.

The Database Connector automatically retries failed publishes and only marks records as processed in the outbox after successfully publishing them. It uses idempotent publishing to ensure that messages are delivered without duplication, exactly-once.

An additional benefit of the outbox pattern is that it decouples the underlying database schema from the change event schema published over Ably. This separation lets you adapt your database schema and data models without altering the code of your subscribing applications.

With explicit outbox writes, you maintain complete control over which data changes trigger publishes, enabling you to synchronize only the changes required by your application.

You have complete control over the channels where change events are published:

• You can distribute change notifications across various channels according to the requirements of your application.
• You have data access control via channel capabilities.

The following table describes the schema of the outbox table in your database:

The Database Connector uses a poll-on-change strategy to query for new records to process when they are inserted into the outbox. This is the strategy used with the ADBC_POLL_FIXED_RATE configuration option set to false. This is achieved through the use of a trigger configured on the outbox table and is the default behavior. The trigger invokes a function which uses NOTIFY to broadcast a notification to the Database Connector, which it receives using LISTEN.

Internally, the Database Connector debounces notifications within a window determined by the ADBC_POLL_INTERVAL configuration option.

This approach avoids imposing additional load on the database due to polling when there are no new records to process.

Alternatively, you can configure the Database Connector to periodically poll for new records with an interval determined by the ADBC_POLL_INTERVAL configuration option by setting ADBC_POLL_FIXED_RATE to true.

The Database Connector can operate as a cluster to provide fault tolerance. Work is automatically re-distributed across available nodes in the event of failures.

To partition outbox records and process them across available Database Connector nodes, each node must know about all other available nodes in the cluster.

The nodes table must also exist in your database, but as an application developer, you do not need to interact with this table directly.

Nodes discover one another using a nodes table that contains a row for each node in the cluster.

When a node starts up, it generates a unique ID and is added to the nodes table. When the node shuts down, it removes itself from the table.

Each node in the table includes an expiry timestamp, set to a time in the future according to the ADBC_HEARTBEAT_TIMEOUT configuration option. Periodically, each node sends a heartbeat to the database by updating its expiry timestamp in the nodes table. It will eventually expire if a node cannot communicate with the database. The Database Connector automatically removes expired nodes from the table.

The following table describes the schema of the nodes table in your database: