Real-Time Data With Node.js, Socket.IO, and Oracle Database

Real-time data is an increasingly popular topic these days. The basic idea is simple: When data in the database changes, any clients viewing the data should see the changes immediately, or in real-time. Gmail and Twitter are great examples of applications that make use of real-time data. Users of these applications don't need to refresh the page to see new email or tweets; instead, new data is pushed to the clients when it becomes available.

Originally, the only way to simulate real-time data in web applications was via some sort of polling from the browser to the web server. This was often complex and difficult to scale. Enter WebSocket, a protocol that provides two-way communication between the browser and web server in a highly efficient manner. The most common way to work with WebSocket is via Socket.IO, a JavaScript library that abstracts away some of the underlying complexity and provides better cross-browser support than native APIs.

So, with Node.js and Socket.IO, we can push changes in data from the web server to the client, but how can we push changes from Oracle Database to the web server? There are really two parts to the problem: detecting changes and then alerting the web server. While I wasn't quite sure how I'd go about implementing the change detection, I had a pretty good idea of how I could alert the Web server of changes: UTL_HTTP.

UTL_HTTP is one of several PL/SQL packages that allow Oracle Database to communicate over a network (others include UTL_TCP, UTL_SMTP, and UTL_MAIL). Since the requirement was to talk to a web server, UTL_HTTP was a natural fit.

That just left the small problem of detecting changes to data. At first I thought about creating DML triggers combined with Advanced Queuing but that seemed overly complex. Luckily, while searching for a solution, I stumbled upon Oracle Database's Continuous Query Notification (CQN). As I read the description in the documentation, I knew I'd hit the jackpot:

Continuous Query Notification (CQN) lets an application register queries with the database for either object change notification (the default) or query result change notification. . . . If a query is registered for query result change notification (QRCN), the database notifies the application whenever a transaction changes the result of the query and commits.

Query Result Change Notification, yes, please! How had I not heard of this before? At any rate, the feature completed the list of ingredients I needed to create an end-to-end, real-time data proof of concept — I just needed to get coding!

I ended up doing two basic implementations. The first was the simplest, just to demonstrate the moving pieces in as simple a manner as possible. After that I did a more "fully-baked" implementation using AngularJS in the front-end. The prerequisites for both are the same:

• Oracle Database 11g or 12c. If using Oracle 12c the database cannot use the multitenant architecture, as QRCN is not yet supported. I plan to create some content on installing Oracle Enterprise Linux 7 and Oracle XE in VirtualBox, but if you'd like to get an XE database up now for this POC then check out OraOpenSource's Oracle XE & APEX project on GitHub.
• Node.js and the Oracle Database Node.js Driver installed globally. Check out Up and Running With Node.js and Oracle for instructions on how to set up Node.js and the Oracle driver. Unfortunately, the database included with the VM cannot be used in this POC but the VM could be used as the web server.
• Bower installed globally in Node.js. Bower is similar to NPM only it was made for front-end dependencies.

Update: Versions 0.5.0 and higher of the driver changed the execute option "isAutoCommit" to "autoCommit". If you're testing with one of these later versions (highly likely), please update the code to reflect the change.

Ready to get started??? Have at it!

Implementation 1: Old School, DOM Scripting Goodness

In the server with Node.js installed, open a terminal, navigate to an appropriate directory to start a new project, and enter the following:

curl --remote-name https://jsao.io/super-cities-cqn-demo-0.1.0.zip
unzip super-cities-cqn-demo-0.1.0.zip
cd super-cities-cqn-demo-0.1.0/
npm install
bower install

Use your favorite text editor to modify the file named dbconfig.js so that it has the correct information to connect to your database. If you are not going to use the HR schema then you’ll need to modify the files in the sql-scripts directory to reflect the correct schema before executing them in the next step.

Open your favorite tool to work with Oracle Database (I recommend SQL Developer) and run the scripts found in super-cities-cqn-demo-0.1.0/sql-scripts. First, run 1-run-as-sys.sql and then run 2-run-as-hr.sql. Be sure to update the IP address in 2-run-as-hr.sql to the correct IP address for the Node.js server.

Return to the terminal in the super-cities-cqn-demo-0.1.0 directory and use Node.js to start the server:

node server.js

Implementation 2: AngularJS, for the MVC Folks

In the server with Node.js installed, open a terminal, navigate to an appropriate directory to start a new project, and enter the following:

curl --remote-name https://jsao.io/employees-cqn-demo-0.1.0.zip
unzip employees-cqn-demo-0.1.0.zip
cd employees-cqn-demo-0.1.0/
npm install
bower install

Use your favorite text editor to modify the file named dbconfig.js so that it has the correct information to connect to your database. If you are not going to use the HR schema then you’ll need to modify the files in the sql-scripts directory to reflect the correct schema before executing them in the next step.

Open your favorite tool to work with Oracle Database (I recommend SQL Developer) and run the scripts found in employees-cqn-demo-0.1.0/sql-scripts. First, run 1-run-as-sys.sql and then run 2-run-as-hr.sql. Be sure to update the IP address in 2-run-as-hr.sql to the correct IP address for the Node.js server.

Return to the terminal in the employees-cqn-demo-0.1.0 directory and use Node.js to start the server:

node server.js

This is really only the tip of the iceberg. A lot of thought would need to be put into any real implementation to figure out the best implementation, which would vary depending on the exact use case. Is it feasible to push data to the web server rather than just alerting it about a change? How much data could realistically be cached in web server and what's the best way to do it? There are lots of questions to ask and research.

I hope this gets the wheels turning for some folks out there. I'd love to hear from you if you put this technology to use. I plan to do more research on my end as well and in the future, I hope to produce more content that explores Oracle's Continuous Query Notification.