Oracle Database Pub/Sub over REST

In this article, we’ll walk through how to use the Oracle REST Data Services (ORDS) TxEventQ API to produce and consume messages with just a few cURL commands. You’ll learn how to set up an ORDS instance, create topics, consumer groups, and send/receive records—all over HTTPS, without writing a line of PL/SQL.

Let’s get started by spinning up a test environment.

Setting up ORDS

If you’d like to follow along with the examples, set up an ORDS instance for testing — you can use the ADB Free container image on your laptop, Oracle Autonomous Database, or a manual installation. I’ll be using the container-registry.oracle.com/database/adb-free:latest image.

docker run -d \
  -p 1521:1522 \
  -p 1522:1522 \
  -p 8443:8443 \
  -p 27017:27017 \
  -e WORKLOAD_TYPE='ATP' \
  -e WALLET_PASSWORD=Welcome12345 \
  -e ADMIN_PASSWORD=Welcome12345 \
  --cap-add SYS_ADMIN \
  --device /dev/fuse \
  --name adb-free \
  container-registry.oracle.com/database/adb-free:latest

Once you’ve got ORDS up and running, set the following environment variables. We’ll be using these in our client requests to the TxEventQ API:

### Use the commented values if running ORDS with 
### adb-free container image

# https://localhost:8443/ords/
export ORDS_URL=<ORDS URL>
# ADMIN
export DB_USERNAME=<ORDS username>
# Welcome12345
export DB_PASSWORD='<ORDS Password>'
# MY_ATP
export DB_NAME=<Database cluster name/id>

To get your DB name on ADB, use the following query, or retrieve it from the OCI console. Your DB name has the format “****_displayName“:

SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name FROM dual

SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name FROM dual

Create a topic

First, let’s create a topic to produce and consume messages from. The following cURL command creates a topic named ords_test with 10 partitions:

curl -X POST -u "$DB_USERNAME:$DB_PASSWORD" \
    -H 'Content-Type: application/json' \
    "${ORDS_URL}${DB_USERNAME}/_/db-api/stable/database/txeventq/clusters/${DB_NAME}/topics" -d '{
      "topic_name": "ords_test",
      "partitions_count": "10"
}'

We’ll use the ords_test topic in the following examples with a consumer and a producer.

Create a consumer group

Before we create a consumer, we must create a consumer group, as each consumer must be a member of a consumer group. The following cURL command creates a consumer group named my_grp for the ords_test topic:

curl -X POST -u "$DB_USERNAME:$DB_PASSWORD" \
  -H 'Content-Type: application/json' \
  "${ORDS_URL}${DB_USERNAME}/_/db-api/stable/database/txeventq/clusters/${DB_NAME}/consumer-groups/my_grp" -d '{
    "topic_name": "ords_test"
}'

Create a consumer

Using the my_grp consumer group, let’s create a consumer:

curl -X POST -u "$DB_USERNAME:$DB_PASSWORD" \
  "${ORDS_URL}${DB_USERNAME}/_/db-api/stable/database/txeventq/consumers/my_grp"

The response should look something like this — note that your consumer instance ID will be different: {"instance_id":"my_grp_Cons_c27b15cc560a8c2b64500d80d64c594e"}

Save the instance ID, which we’ll use later to consume records.

export CONSUMER_ID=<my consumer instance id>

Produce a record

The following cURL command produces a record with my key as the routing key, and my first record as the value:

curl -X POST -u "$DB_USERNAME:$DB_PASSWORD" \
    -H 'Content-Type: application/json' \
    "${ORDS_URL}${DB_USERNAME}/_/db-api/stable/database/txeventq/topics/ords_test" -d '{
    "records": [
      { "key": "my key", "value": "my first record"}
    ]
}'

It’s worth noting you can produce multiple records using one post, and use different value types, such as binary, instead of a string.

Consume the record

Now that we’ve created a topic, a consumer group with a consumer, and produced a record, we can consume it using our consumer:

curl -X GET -u "$DB_USERNAME:$DB_PASSWORD" \
    "${ORDS_URL}${DB_USERNAME}/_/db-api/stable/database/txeventq/consumers/my_grp/instances/${CONSUMER_ID}/records"

You should see the record “my first record” printed to the console!

andersswanson.dev