Real-time Data with Node.js, Socket.IO, and Oracle Database

By February 22, 2015 Uncategorized

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.

Ready to get started??? Have at it!

Implementation 1: Old School, DOM Scripting Goodness
  1. In the server with Node.js installed, open a terminal, navigate to an appropriate directory to start a new project, and enter the following:
  2. 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.
  3. 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.
  4. Return to the terminal in the super-cities-cqn-demo-0.1.0 directory and use Node.js to start the server:
Implementation 2: AngularJS, for the MVC folks
  1. In the server with Node.js installed, open a terminal, navigate to an appropriate directory to start a new project, and enter the following:
  2. 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.
  3. 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.
  4. Return to the terminal in the employees-cqn-demo-0.1.0 directory and use Node.js to start the server:

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.

18 Comments

  • John Scott says:

    Dan,

    Fantastic example of different technologies coming together, brilliant!

    John

  • Hi Dan, we would be interested on your comments about the comments below:

    …but he uses dbms_cq_notification which is for queries. It’s waiting for a change on objects that the query depend upon and call your own custom procedure. The way it handles dependencies looks exactly the same as with result cache. What I’ve seen at a client site is dbms_change_notification, where you register tables, not queries. It seems more logical to me to register tables, but both seems to do the exact same thing.

    • danmcghan says:

      Hi Patrick,

      I believe dbms_change_notification was used in 10g and dbms_cq_notification is used in 11g and 12c. It’s possible to register queries for object change notification (OCN), which is when a transaction changes an object the query references and commits, or query result change notification (QRCN), which is when a transaction changes the result of a query and commits. I’m a bigger fan of QRCN as it’s more specific as to what you want to be notified about.

  • Umer Qaisar says:

    Hi, I want to implement this real time data using oracle on asp.net web application.
    can you guide me with few hints please.

    • danmcghan says:

      Hi Umer,

      I don’t have any asp.net experience, but I would think you could look over the Node.js application to get some ideas. Really, it comes down to using Socket.io. Do you know if there’s some kind of support for Socket.io support for asp.net? You could use Web Sockets without Socket.io, but it’s a lot more complicated…

      Regards,
      Dan

  • Alex says:

    Hi Dan,

    Thank you very much for this example!

    I’ve been playing with your code but couldn’t get it to work properly. Specifically, the data is not updated when editing an employee detail.After spending some time reading the and realize that isAutoCommit has been renamed to autoCommit. Thought you might want to update it in /server/api.js. Or specify the version of node-oracledb in your sample project.

  • paul j says:

    Nice and informative dan.
    Thanks
    Paul j

  • Andre Souza says:

    Hi Dan,

    Great explanation, thank you for taking the time to do it.

    One question: How about the number of simultaneous connections? Let’s say it’s a public applications and can have up to 1k hits per minute.

    Thank you

    • danmcghan says:

      Hi Andre,

      Thanks! Honestly, I haven’t had enough time to do much testing with it. 1k hits a minute seems a bit extreme on the surface, but I suppose that depends on a number of factors, such as the data, the hardware, network, etc.

      Please follow up if you run some tests! 🙂

      Regards,
      Dan

  • Graham Bailey says:

    Great post! I’m just dipping my toes into the water of using Javascript after using Oracle for many years. I have to let you know that I tried the Super Cities example but I found that the Upvote buttons were not having any effect. I managed to trace this down to a change in the node-oracledb driver. From version 0.5 onwards, isAutoCommit has been changed to autoCommit. So line 126 of the config.js file needs to be changed from isAutoCommit: true to autoCommit: true

    • danmcghan says:

      Thanks Graham. Your comment made me realize that someone else had reported this a while ago. I added an alert to the post for future readers.

  • nishtha says:

    Hi Dan,

    Thanks a lot for this article.I am building my first node based application.
    When I downloaded super city demo,I was able to run it successfully.

    But in my application whenever there is any change ,I get below error
    ORA-29973: Unsupported query or operation during change notification registration

  • Mittinpreet Singh says:

    Hi Dan

    i need to prepare one POC using Nodejs , Socket.io and MySQL Database , if possible can you help me out in this Asap.

    Thanks !!!

    BR,
    Mittinpreet Singh

    • danmcghan says:

      Hi Mittinpreet,

      I haven’t used MySQL for a long time. You’ll need to look into its event and communication capabilities to do something similar.

      Here’s a link from a talk I did on this subject last week. Maybe it might help some, but it’s still based on Oracle DB:
      https://www.youtube.com/watch?v=xRJLcLoLBq8

  • sabper says:

    Hi! Dan.

    Greate Post!!

    i hava one question.

    i think cqn similar trigger.

    – cqn can to call api server uri ( ex. http://domain/db)

    – trigger pl/sql can to call to api server uri (ex. http://domain/db)

    What different trigger pl/sql and cqn??

    thank you.

    • danmcghan says:

      A trigger becomes part of a transaction. DML operations already involve disk IO. Adding network IO would be bad. To use a trigger in this way, the notification logic should be moved to a stored procedure. Then the procedure can be invoked in the trigger via a call to dbms_scheduler, which will then run the notification logic via a background process (asynchronous to the transaction). The trigger must use the autonomous transaction pragma for this to work.

      You might run into situations where you don’t have the ability to create a trigger. Maybe it’s a COTS app or something along those lines. In those cases, CQN might be a better option. Also, CQN and AQ provide a grouping capability that is similar to a debounce in JavaScript. This can allow you to reduce the number of round trips (notifications) from the database to the app server when needed. With a trigger, you’d have to recreate this on your own but you get it for free with CQN and AQ.

      You can see a recent talk I gave that covers all of these options on YouTube. Here’s a link to the slides from that talk and here’s a link to the demo app.

      I hope that helps!

Leave a Reply