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.

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:

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

Copy
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):

• Environment variables
• YAML
• Command line flags

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

API Key:
Demo Only
Copy
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

API Key:
Demo Only
Copy
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>"

API Key:
Demo Only
Copy
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

Copy
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

Copy
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>

API Key:
Demo Only
Copy
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

Copy
Copied!

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

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:

Copy
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

API Key:
Demo Only
Copy
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

Copy
Copied!

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

curl localhost:2259/health

Copy
Copied!

The response should look similar to the following:

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

Copy
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"

API Key:
Demo Only
Copy
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', '{}', '{}');"

Copy
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"}

Copy
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

Copy
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

Copy
Copied!

Add the PostgreSQL plugin to your project:

railway add --plugin postgresql

Copy
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

Copy
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

Copy
Copied!

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

railway open

Copy
Copied!

Select the adbc-test service, then navigate to Variables → RAW 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

API Key:
Demo Only
Copy
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"

API Key:
Demo Only
Copy
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', '{}', '{}');

Copy
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"}

Copy
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"
   }
}

Copy
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:

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

Copy
Copied!