Demo: Oracle Database Continuous Query Notification in Node.js

Native Oracle Database Continuous Query Notification (CQN) code has landed in the node-oracledb master branch on GitHub. If you want to play with it, but don't want to wait for the next binary node-oracledb release, you can compile node-oracledb yourself and play with this demo.

Some of you may already be using CQN via its PL/SQL APIs. The new, native support in node-oracledb makes it all so much nicer. Check out the development documentation for connection.subscribe() and the "user manual." There are a couple of examples cqn1.js and cqn2.js available, too.

CQN allows JavaScript methods to be called when database changes are committed by any transaction. You enable it in your node-oracledb app by registering a SQL query. CQN has two main modes: object-level and query-level. The former sends notifications (i.e. calls your nominated JavaScript method) when changes are made to database objects used in your registered query. The query-level mode only sends notifications when database changes are made that would impact the result set of the query, e.g. the WHERE clause is respected.

If you're not using CQN, then you might wonder when you would. For infrequently updated tables, you can get CQN to generate notifications on any data or table change. I can see how query-level mode might be useful for proactive auditing to send alerts when an unexpected, but valid, value is inserted or deleted from a table. For tables with medium levels of updates, CQN allows groupings of notifications by time, which is a way of reducing load by preventing too many notifications being generated in too short a time span. However, as my colleague Dan McGhan points out, if you know the table is subject to a lot of change, then your apps will be better off simply polling the table and avoiding any CQN overhead. Note that CQN was designed to be used for relatively infrequently updated tables.

Demo App

I've thrown together a little app that uses CQN and Socket.IO to refresh a message notification area on a web page. It's really just a simple smush of the Socket.IO intro example and the node-oracledb CQN examples.

There is a link to all the code in the next section of this post; I'll just show snippets inline here. I'm sure Dan will update his polished "Real-time Data" example soon, but until then, here is my hack code. It uses Node.js 8's async/await style — you can rewrite it if you have an older Node.js version.

One thing about CQN is that the node-oracledb computer must be resolvable by the Database computer; typically, this means having a fixed IP address, which may be an issue with laptops and DHCP. Luckily, plenty of other cases work too. For example, I replaced my Docker web service app with a CQN example and didn't need to do anything with ports or identifying IP addresses. I'll leave you to decide how to run it in your environment. There are CQN options to set the IP address and port to listen on, which may be handy.

The demo premise is a web page with a message notification area that always shows the five most recent messages in a database table. The messages are being inserted into that table by other apps (I'll just use SQL*Plus to do these inserts) and the web page needs to be updated with them only when there is a change. I'm just using dummy data and random strings:

To see how it fits together, look at this no-expense-spared character graphic showing the four components: SQL*Plus, the database, the browser, and the Node.js app:

    SQL*PLUS:                         DATABASE:
      insert into msgtable  >------->  msgtable  >-------CQN-notification------------------+
      commit                                                                               |
                                                                                           |
    BROWSER:    <-------+             NODE.JS APP:                                         |
      5 Message         |               URL '/' serves index.html                          |
      4 Message         |                                                                  |
      3 Message         |               CQN:                                               |
      2 Message         |                 subscribe to msgtable with callback myCallback   |
      1 Message         |                                                                  |
                        |               myCallback:   <------------------------------------+
                        |                 query msgtable
                        +-----------<     send rows to browser to update the DOM

The app (bottom right) serves the index page to the browser. It connects to the DB and uses CQN to register interest in msgtable. Any data change in the table from SQL*Plus (top left) triggers a CQN notification from the database to the application, and the callback is invoked. This callback queries the table and uses Socket.IO to send the latest records to the browser, which updates the index.html DOM.

The first thing is to get your DBA (i.e. log in as the SYSTEM user) to give you permission to get notifications:

GRANT CHANGE NOTIFICATION TO cj;

We then need a table that our app will get notifications about, and then query to get the latest messages:

CREATE TABLE cj.msgtable (
       k       NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY(START WITH 1),
       message VARCHAR(100)
);

The column K is an Oracle Database 12c identity column that will automatically get a unique number inserted whenever a new message is inserted. In older database versions, you would create a sequence and trigger to do the same.

The little SQL script I use to insert data (and trigger notifications) is:

INSERT INTO msgtable (message) VALUES (DBMS_RANDOM.STRING('A', 10));
COMMIT;

The Node.js app code is more interesting, but not complex. Here is the code that registers the query:

conn = await oracledb.getConnection();
await conn.subscribe('mysub', {
  callback: myCallback,
  sql:      "SELECT * FROM msgtable"
});
console.log("CQN subscription created");

Although CQN has various options to control its behavior, here, I keep it simple — I just want to get notifications when any data change to msgtable is committed.

When the database sends a notifications, the method "myCallback" will get a message, the contents of which will vary depending on the subscription options. Since I know the callback is invoked when any table data has changed, I ignore the message contents and go ahead and query the table. The rows are then stringified and, by the magic of Socket.IO, sent to the web page:

async function myCallback(message) {
  let rows = await getData();                // query the msgtable
  io.emit('message', JSON.stringify(rows));  // update the web page
}

The helper function to query the table is obvious:

async function getData() {
  let sql = `SELECT k, message
             FROM msgtable
             ORDER BY k DESC
             FETCH NEXT :rowcount ROWS ONLY`;
  let binds = [5];  // get 5 most recent messages
  let options = { outFormat: oracledb.OBJECT };

  let conn = await oracledb.getConnection();
  let result = await conn.execute(sql, binds, options);
  await conn.close();
  return result.rows;
}

At the front end, the HTML for the web page contains a "messages" element that is populated by JQuery code when a message is received by Socket.IO:

<ul id="messages"></ul>
<script src="https://cdnjs.cloudflare.com/ajax/libs/socket.io/2.1.1/socket.io.js"></script>
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script>
  $(function () {
    var socket = io();
    socket.on('message', function(msg){
      $('#messages').empty();
      $.each(JSON.parse(msg), function(idx, obj) {
        $('#messages').append($('<li>').text(obj.K + ' ' + obj.MESSAGE));
      });
    });
  });
</script>

You can see that the JSON string received from the app server is parsed and the K and MESSAGE fields of each row object (corresponding to the table columns of the same names) are inserted into the DOM in an unordered list.

That's it.

Demo In Action

To see it in action, extract the code and install the dependencies:

cjones@mdt:~/n/cqn-sockets$ npm install
npm WARN CQN-Socket-Demo@0.0.1 No repository field.
npm WARN CQN-Socket-Demo@0.0.1 No license field.

added 86 packages in 2.065s

I cheated a bit there and didn't show node-oracledb compiling. Once a production release of node-oracledb is made, you should edit the package.json dependency to use its pre-built binaries. Until then, node-oracledb code will be downloaded and compiled - check the instructions for compiling.

Edit server.js and set your database credentials — or set the referenced environment variables:

let dbConfig = {
      user: process.env.NODE_ORACLEDB_USER,
      password: process.env.NODE_ORACLEDB_PASSWORD,
      connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING,
      events: true  // CQN needs events mode
    }

Then, start the app server:

cjones@mdt:~/n/cqn-sockets$ npm start

> CQN-Socket-Demo@0.0.1 start /home/cjones/n/cqn-sockets
> node server.js

CQN subscription created
Listening on http://localhost:3000

Then load http://localhost:3000/ in a browser. Initially, the message pane is blank — I left bootstrapping it as an exercise for the reader.

Start SQL*Plus in a terminal window and create a message:

SQL> INSERT INTO msgtable (message) VALUES (DBMS_RANDOM.STRING('A', 10));
SQL> COMMIT;

Every time data is committed to msgtable, the message list on the web page is automatically updated:

If you don't see messages, review Troubleshooting CQN Registrations. The common problems will be network related: the node-oracledb machine must be resolvable, the port must be open, etc.

Try it out and let us know how you go. Remember you are using development code that just landed, so there may be a few rough edges.