18. Working with Continuous Query Notification (CQN)
Continuous Query Notification (CQN) lets node-oracledb applications subscribe to receive notification when changed data is committed to the database, regardless of the user or the application that made the change. For example your application may be interested in knowing if a table used for lookup data has changed so that the application can update a local cache of that table. CQN can invoke a JavaScript method, which can perform the action.
Note
In this release, CQN is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
CQN is suitable for infrequently modified tables. It is recommended to avoid frequent subscription and unsubscription.
By default, CQN requires the database to be able to connect back to the
node-oracledb application for notifications to be received. This
typically means that the machine running node-oracledb needs a fixed IP
address. Note connection.subscribe()
does not verify that this
reverse connection is possible. If there is any problem sending a
notification, then the callback method will not be invoked. The
configuration options can include an
ipAddress and
port on which to listen for
notifications, otherwise the database chooses values.
Alternatively, when using Oracle Database and Oracle client libraries 19.4, or later, subscriptions can set the optional clientInitiated property to true. This makes CQN internally use the same approach as normal connections to the database, and does not require the database to be able to connect back to the application. Since client initiated CQN notifications do not need additional network configuration, they have ease-of-use and security advantages.
To register interest in database changes, the connection must be created
with oracledb.events
mode true. Then the
connection.subscribe()
method is passed an
arbitrary name and an options object that
controls notification. In particular options
contains a valid SQL
query and a JavaScript callback:
const connection = await oracledb.getConnection({
user : "hr",
password : mypw, // mypw contains the hr schema password
connectString : "localhost/FREEPDB1",
events : true
});
function myCallback(message) {
console.log(message);
}
const options = {
sql : `SELECT * FROM mytable`, // query of interest
callback : myCallback // method called by notifications
clientInitiated : true // For Oracle DB & Client 19.4 or later
};
await connection.subscribe('mysub', options);
In this example, whenever a change to mytable
is committed then
myCallback()
is invoked. The callback
message parameter contains
information about the notification.
CQN notification behavior is widely configurable by the subscription options. Choices include specifying what types of SQL should trigger a notification, whether notifications should survive database loss, and control over unsubscription. You can also choose whether notification messages will include ROWIDs of affected rows.
The connection.subscribe()
method may be called multiple times with
the same name
. In this case, the second and subsequent invocations
ignore all options
properties other than
sql and
binds. Instead, the new SQL statement is
registered to the same subscription, and the same JavaScript
notification callback is used. For performance reasons this can be
preferable to creating a new subscription for each query.
You can view information about registrations by querying views such
USER_CHANGE_NOTIFICATION_REGS
table. The REGID
column can be
matched with the value contained in regid
from the connection.subscribe()
callback parameter. In the database view
USER_SUBSCR_REGISTRATIONS
, the REG_ID
column can be matched.
When notifications are no longer required, the subscription name can be
passed to connection.unsubscribe()
.
By default, object-level (previously known as Database Change Notification) occurs and the JavaScript notification method is invoked whenever a database transaction is committed that changes an object the query references, regardless of whether the actual query result changed. However if the subscription option qos is oracledb.SUBSCR_QOS_QUERY then query-level notification occurs. In this mode, the database notifies the application whenever a transaction changes the result of the registered query and commits. For example:
const options = {
sql : `SELECT * FROM mytable WHERE key > 100`, // query of interest
callback : myCallback, // method called by notifications
qos : oracledb.SUBSCR_QOS_QUERY // CQN
};
In this example, if a new key
of 10 was inserted then no
notification would be generated. If a key wth 200
was inserted, then
a notification would occur.
Before using CQN, users must have appropriate permissions, for example:
SQL> CONNECT system
SQL> GRANT CHANGE NOTIFICATION TO hr;
Below is an example of CQN that uses object-level notification and grouped notifications in batches at 10 second intervals. After 60 seconds, the notification callback is unregistered and no more notifications will occur. The quality of service flags indicate ROWIDs should be returned in the callback:
let interval = setInterval(function() {
console.log("waiting...");
}, 5000);
function myCallback(message)
{
console.log("Message type:", message.type);
if (message.type == oracledb.SUBSCR_EVENT_TYPE_DEREG) {
clearInterval(interval);
console.log("Deregistration has taken place...");
return;
}
console.log("Message database name:", message.dbName);
console.log("Message transaction id:", message.txId);
for (const table of message.tables) {
console.log("--> Table Name:", table.name);
console.log("--> Table Operation:", table.operation);
if (table.rows) {
for (const row of table.rows) {
console.log("--> --> Row Rowid:", row.rowid);
console.log("--> --> Row Operation:", row.operation);
console.log(Array(61).join("-"));
}
}
console.log(Array(61).join("="));
}
}
const options = {
sql : `SELECT * FROM mytable`,
callback : myCallback,
timeout : 60,
qos : oracledb.SUBSCR_QOS_ROWIDS,
groupingClass : oracledb.SUBSCR_GROUPING_CLASS_TIME,
groupingValue : 10,
groupingType : oracledb.SUBSCR_GROUPING_TYPE_SUMMARY
};
try {
// This is Node 8 syntax, but can be changed to callbacks
const connection = await oracledb.getConnection({
user : "hr",
password : mypw, // mypw contains the hr schema password
connectString : "localhost/FREEPDB1",
events : true
});
await connection.subscribe('mysub', options);
console.log("Subscription created...");
} catch (err) {
console.error(err);
clearInterval(interval);
}
If two new rows were inserted into the table and then committed, output might be like:
Message type: 6
Message database name: orcl
Message transaction id: <Buffer 06 00 21 00 f5 0a 00 00>
--> Table Name: CJ.MYTABLE
--> Table Operation: 2
--> --> Row Rowid: AAAVH6AAMAAAAHjAAW
--> --> Row Operation: 2
------------------------------------------------------------
--> --> Row Rowid: AAAVH6AAMAAAAHjAAX
--> --> Row Operation: 2
------------------------------------------------------------
Here, the message type 6 corresponds to oracledb.SUBSCR_EVENT_TYPE_OBJ_CHANGE and the row operations of 2 correspond to oracledb.CQN_OPCODE_INSERT.
There are runnable examples in the GitHub examples directory.