Database Connector

The Ably Database Connector (ADBC) sends updates from your database to frontend clients through Ably channels using the outbox pattern. When you update data in your database, you will also need to record the changes in an outbox table as part of the same transaction, which is discussed further in the outbox and nodes tables page. The Database Connector detects the changes in the outbox table and publishes them as messages to specified channels. Client applications using the Models SDK subscribe to these channels to receive updates to data stored locally within models.

The Database Connector can be self-hosted using a Docker container, or hosted by Ably and interacted with via an integration rule.

The Database Connector can be used with an existing database, or a new database. It is designed to be database agnostic, however only PostgreSQL is currently supported.

To configure the Database Connector, you need an Ably API key and the connection details of your database.

An image showing how the Database Connector plays in the LiveSync flow

The hosted Database Connector is available without the need to install a new service on your servers.

You can configure your Database Connector using the Postgres rule in the Integrations tab of the Ably dashboard. The following input fields are available when configuring this integration:

Option Description
URL The URL for your Postgres database, for example postgres://user:[email protected]:5432/your-database-name
Outbox table schema Schema for the outbox table in your database which allows for the reliable publication of an ordered sequence of change event messages over Ably.
Outbox table name Name for the outbox table.
Nodes table schema Schema for the nodes table in your database to allow for operation as a cluster to provide fault tolerance.
Nodes table name Name for the nodes table.
SSL mode Determines the level of protection provided by the SSL connection. Options are: prefer, require, verify-ca, verify-full; default value is prefer.
SSL root certificate Optional. Specifies the SSL certificate authority (CA) certificates. Required if SSL mode is verify-ca or verify-full.
Primary site The primary data center in which to run the integration rule.

You can optionally test your Database Connector is correctly configured using the Curl requests provided in the integration rule of your application in the Ably dashboard.

The Database Connector is available as a service hosted by yourself through Docker.

The self-hosted Database Connector is a Docker container image, which is compatible with any cloud infrastructure platform that can run Docker containers.

The following is the command to pull the Database Connector image:

docker pull ghcr.io/ably-labs/adbc:latest
Copied!

The Database Connector requires some configuration values to run. The following three options are available to override the default values of the ADBC configuration options, such as configuring the environment descriptor (development or production), or setting the log level (debug, info, warn, error, fatal, or panic):

Specify configuration options as environment variables by using the following method:

  • Capitalize each option.
  • Separate each word with underscores.
  • Prefix each option with ADBC_ to denote a namespace and avoid conflicts with other variables.

Set your Ably API key using an environment variable:

docker run -it -e ADBC_ABLY_API_KEY=<loading API key, please wait> ghcr.io/ably-labs/adbc:latest
Demo Only
Copied!

The following is an example of reading the API key from a .env file:

echo “ADBC_ABLY_API_KEY=<loading API key, please wait>” >> adbc.env docker run -it --env-file=adbc.env ghcr.io/ably-labs/adbc:latest
Demo Only
Copied!

Specify configuration options as YAML by using the following method:

  • Use camel case for each option.
  • Name the file adbc.yaml or .adbc.yaml.
  • Host it in the working directory, the $HOME directory, or specify its path using --config.

Set your Ably API key using a YAML file:

ably: apiKey: "<loading API key, please wait>"
Demo Only
Copied!

Ensure the YAML file is accessible to the application by mounting it inside the container:

docker run -it --volume "$(pwd)/adbc.yaml:/adbc.yaml:ro" ghcr.io/ably-labs/adbc:latest
Copied!

Verify the YAML file is mounted inside the container by using the following method:

version: '3' services: adbc: image: ghcr.io/ably-labs/adbc:latest volumes: - ./adbc.yaml:/adbc.yaml:ro # mount yaml config file
Copied!

Specify configuration options using CLI:

  • Use snake case for each option.
  • Prefix each with --.

Set your Ably API key using CLI:

docker run -it ghcr.io/ably-labs/adbc:latest --ably-api-key=<loading API key, please wait>
Demo Only
Copied!

Use --help to view the complete set of configuration options available on the Database Connector:

docker run -it --entrypoint="/adbc" ghcr.io/ably-labs/adbc:latest --help
Copied!

The following table provides descriptions for the most commonly used configuration options:

Option Description
ADBC_ABLY_API_KEY Your Ably API key.
ADBC_POSTGRES_CONNECTION_URI The full connection URI of your Postgres database.
ADBC_POSTGRES_HOST Your Postgres database host name as an alternative to providing the CONNECTION_URI.
ADBC_POSTGRES_PORT Your Postgres database port number as an alternative to providing the CONNECTION_URI.
ADBC_POSTGRES_DATABASE Your Postgres database name as an alternative to providing the CONNECTION_URI.
ADBC_POSTGRES_USER Your Postgres database user as an alternative to providing the CONNECTION_URI.
ADBC_POSTGRES_PASSWORD Your Postgres database user password as an alternative to providing the ADBC_POSTGRES_CONNECTION_URI.
--config Can only be specified as a CLI flag and enables you to override the path to a YAML configuration file.
ADBC_ENV An environment descriptor (either development or production). development pretty-prints log output with logging enabled at debug level and above. production logs output in JSON format with logging enabled at info level and above.
ADBC_LOG_LEVEL Specifies the log level to use (one of: debug, info, warn, error, fatal, panic) and overrides any presets from ADBC_ENV.
ADBC_OUTBOX_TABLE_TABLE_SCHEMA Configures the database schema of the outbox table.
ADBC_OUTBOX_TABLE_TABLE_NAME Configures the name of the outbox table.
ADBC_OUTBOX_TABLE_AUTO_CREATE Configures the application to create the outbox table if it doesn’t already exist on startup.
ADBC_NODES_TABLE_TABLE_SCHEMA Configures the database schema of the nodes table.
ADBC_NODES_TABLE_TABLE_NAME Configures the name of the nodes table.
ADBC_NODES_TABLE_AUTO_CREATE configures the application to create the nodes table if it doesn’t already exist on startup.
ADBC_HEALTH_ADDRESS Configures the TCP address for the server to listen on in the form host:port.
ADBC_POLL_FIXED_RATE If true, the application polls the outbox table at a fixed rate given by ADBC_POLL_INTERVAL (default 1 second). If false, the application uses a trigger with LISTEN/NOTIFY to poll for records only when the data in the outbox changes.

Use Docker Compose to use an existing PostgreSQL instance with the Database Connector. By default, Docker Compose automatically generates a PostgreSQL instance for you.

Docker Compose sets up and runs a local development environment. You can create and start your PostgreSQL database and an instance of the Database Connector on your local machine.

To begin, create a docker-compose.yml file with the following contents:

version: '3' services: adbc: image: ghcr.io/ably-labs/adbc:latest env_file: - adbc.env # load config from env file # Uncomment below if you want to load config from your adbc.yaml file, # which takes precedence over config from the env. # volumes: # - ./adbc.yaml:/adbc.yaml:ro # mount yaml config file depends_on: postgres: condition: service_healthy networks: - adbc_network postgres: image: postgres:11-alpine ports: - 5432:5432 environment: POSTGRES_USER: postgres POSTGRES_PASSWORD: postgres POSTGRES_DB: postgres healthcheck: test: ["CMD", "pg_isready", "-q", "-d", "postgres", "-U", "postgres"] interval: 2s retries: 30 networks: - adbc_network volumes: - adbc_postgres_data:/var/lib/postgresql/data volumes: adbc_postgres_data: networks: adbc_network:
Copied!

There are three options to configure the Database Connector. The following example solely demonstrates environment variables.

Create an adbc.env file with, at a minimum, the following configuration options:

ADBC_ABLY_API_KEY=<loading API key, please wait> ADBC_POSTGRES_CONNECTION_URI=postgres://postgres:postgres@postgres:5432/postgres ADBC_OUTBOX_TABLE_AUTO_CREATE=true ADBC_NODES_TABLE_AUTO_CREATE=true
Demo Only
Copied!

The docker-compose.yml will load this configuration into the adbc container as environment variables.

Run docker compose up to build and run the Docker containers:

docker compose up --build
Copied!

Ping the Database Connector health endpoint to verify everything works correctly.

curl localhost:2259/health
Copied!

The response should look similar to the following:

{"ably":{"status":"up"},"nodes_table":{"status":"up"},"outbox_table":{"status":"up"},"postgres":{"status":"up"}}
Copied!

Verify your connection to the Ably network now you have a PostgreSQL database and Database Connector instance running. The following examples show how to write a record to the outbox table and confirm its publication.

Subscribe to a channel named foo using Server-Sent Events:

curl -s -u "<loading API key, please wait>" "https://realtime.ably.io/sse?channel=foo&v=1.1"
Demo Only
Copied!

Add a record to the outbox table in your PostgreSQL database. Use the psql tool in the Postgres container to execute an SQL statement against the database:

docker exec -it -u postgres adbc-postgres \ psql postgres://postgres:postgres@postgres:5432/postgres \ -c "INSERT INTO outbox (mutation_id, name, channel, data, headers) \ VALUES ('1', 'test', 'foo', '{}', '{}');"
Copied!

The Database Connector detects and publishes newly inserted records as messages to the specified channel in the record.

You will receive a response similar to the following, indicating the successful receipt of the event over your SSE connection to Ably:

id: 108GsR8ewBVHhJ@1700069266489-0 event: message data: {"id":"1","connectionId":"CaqkrZ2N_0","timestamp":1700069266050,"encoding":"json","extras":{"headers":{"x-ably-models-event-uuid":"1"}},"channel":"foo","data":"{}","name":"test"}
Copied!

When running the Database Connector with a new database, you can use any Postgres provider. To enable you to get set up quickly, this documentation uses Railway. Railway is a deployment platform where you can provision infrastructure, develop with that infrastructure locally, and then deploy to the cloud. Railway provides a PostgreSQL database service that allows you to provision and connect to a PostgreSQL database with zero configuration.

Create a free Railway account, install the Railway CLI and log in:

npm i -g @railway/cli railway login
Copied!

Create a new Railway project and link it to your project directory:

railway init # Enter a project name, e.g., "adbc-test" railway link
Copied!

Add the PostgreSQL plugin to your project:

railway add --plugin postgresql
Copied!

Create a new Dockerfile using the Database Connector as the base image. Railway requires a Dockerfile to define the application for deployment:

echo "FROM ghcr.io/ably-labs/adbc:latest" > Dockerfile
Copied!

Use railway up to deploy your application. This command will build the Database Connector container image from the Dockerfile in your project root and deploy it to your Railway project:

railway up --detach
Copied!

Open your project in the Railway console. Your Database Connector will crash until further configuration is added:

railway open
Copied!

Select the adbc-test service, then navigate to VariablesRAW Editor. Paste the following variables into this section:

ADBC_ABLY_API_KEY=<loading API key, please wait> ADBC_POSTGRES_CONNECTION_URI=${{Postgres.DATABASE_URL}} ADBC_OUTBOX_TABLE_AUTO_CREATE=true ADBC_NODES_TABLE_AUTO_CREATE=true
Demo Only
Copied!

Railway will restart the adbc-test service with the newly applied configuration.

Verify your connection to the Ably network now you have a PostgreSQL database and Database Connector instance running. The following examples show how to write a record to the outbox table and confirm its publication.

Subscribe to a channel named foo using Server-Sent Events:

curl -s -u "<loading API key, please wait>" "https://realtime.ably.io/sse?channel=foo&v=1.1"
Demo Only
Copied!

Add a record to the outbox table in your PostgreSQL database. Use the Railway CLI to execute an SQL statement against the database:

railway connect postgres railway=# INSERT INTO outbox (mutation_id, name, channel, data, headers) VALUES ('1', 'test', 'foo', '{}', '{}');
Copied!

The Database Connector detects and publishes newly inserted records as messages to the specified channel in the record.

You will receive a response similar to the following, indicating the successful receipt of the event over your SSE connection to Ably:

id: 108GsR8ewBVHhJ@1700069266489-0 event: message data: {"id":"1","connectionId":"CaqkrZ2N_0","timestamp":1700069266050,"encoding":"json","extras":{"headers":{"x-ably-models-event-uuid":"1"}},"channel":"foo","data":"{}","name":"test"}
Copied!

The Database Connector exposes an HTTP server that can be used to monitor its health and metrics.

The server listens on the port defined by the ADBC_HEALTH_ADDRESS configuration option and defaults to port 2259.

The Database Connector exposes an HTTP endpoint on /health that returns a JSON containing the status of:

  • Connectivity to the Ably service.
  • Connectivity to the PostgreSQL database.
  • Access to the nodes table, determined by executing SELECT 1 FROM nodes.
  • Access to the outbox table, determined by executing SELECT 1 FROM outbox.

Internally, the Database Connector periodically refreshes the health status for each target according to the interval defined by the ADBC_HEALTH_REFRESH_INTERVAL configuration option.

The following is an example of a healthy response from the endpoint:

curl localhost:2259/health { "ably":{ "status":"up" }, "nodes_table":{ "status":"up" }, "outbox_table":{ "status":"up" }, "postgres":{ "status":"up" } }
Copied!

The Database Connector exposes an HTTP endpoint on /metrics that implements a Prometheus metrics endpoint that can be used to monitor the following metrics:

Metric Type Description
ably_pending_acks gauge Number of pending messages waiting to be acknowledged by Ably.
nodes_table_entries gauge Number of entries in the nodes table.
nodes_table_errors counter Number of errors querying the nodes table.
outbox_table_entries gauge Number of entries in the outbox table.
outbox_table_errors counter Number of errors querying the outbox table.
promhttp_metric_handler_errors_total counter Total number of internal errors encountered by the promhttp metric handler.

The following is an example response from the metrics endpoint:

curl localhost:2259/metrics # HELP ably_pending_acks Number of pending messages waiting to be acknowledged by Ably # TYPE ably_pending_acks gauge ably_pending_acks 0 # HELP nodes_table_entries Number of entries in the nodes table # TYPE nodes_table_entries gauge nodes_table_entries 1 # HELP nodes_table_errors Number of errors querying the nodes table # TYPE nodes_table_errors counter nodes_table_errors 0 # HELP outbox_table_entries Number of entries in the outbox table # TYPE outbox_table_entries gauge outbox_table_entries 1 # HELP outbox_table_errors Number of errors querying the outbox table # TYPE outbox_table_errors counter outbox_table_errors 0 # HELP promhttp_metric_handler_errors_total Total number of internal errors encountered by the promhttp metric handler. # TYPE promhttp_metric_handler_errors_total counter promhttp_metric_handler_errors_total{cause="encoding"} 0 promhttp_metric_handler_errors_total{cause="gathering"} 0
Copied!
Ably hosted Database Connector