Category Archives: NodeJS

How To Connect your Node.js app in Docker to your Oracle Database

In this post I’ll cover how to make a connection from a Node.js application in a Docker container to your Oracle Database in three different scenarios.

  • Typical Database connection.
  • Database inside a different Docker container.
  • Oracle Autonomous Transaction Processing Cloud Database.

Simple Test App

When attempting something new, I find it’s best to make sure all of the pieces that are not part of the new thing, are as simple as possible.

For my examples I will be using a small Node.js application that:

  • Connects to the Oracle Database.
  • Runs a simple query.
  • Prints the results to the console.

Create a new file named dbConfig.js to set the database connection information.

Create a new file named server.js used to test the connection.

Install the node-oracledb driver. (If you have any questions about the node-oracledb driver, you can find the answers here.)

Create environment variables for the connection information.  (Replace my values with yours)

Test the Node.js application.  You should see something similar to the following.

Create a Docker Container for the Node.js app

Create a Dockerfile

Build the image

Now that you have an image you’ll run a container and to connect it to:

Typical Oracle Database
The container will run, execute the query and stop.  You should see something similar to this output.
An Oracle Database inside a Docker Container

In a previous post I walked through how to setup both an ORDS instance and an Oracle XE instance in Docker Containers.  Follow through the section showing how to create an Oracle XE Database in a Docker container.

When you followed the steps in that post you should have also created a Docker Network.

You’ll use that same network to connect from the Node.js container to the Oracle XE container.

First, change your NODE_ORACLEDB_CONNECTIONSTRING environment variable to use the Oracle XE container name.

Now when you run a new docker container you will attach it to the same Docker Network as the Oracle XE container.

The container will run, execute the query and stop.  You should see something similar to this output.

Since both containers are using the same Docker network, you do not need to open the port when you run the Oracle XE container.  This is useful if you’d like to keep your database private inside the Docker environment.

Oracle Autonomous Transaction Processing Cloud Database

After you create an Oracle ATP Cloud Database you’ll need to download the credentials file and extract it into a secure location.

For this database, you will be using an entry in the tnsnames.ora file to make the database connection.  Change the NODE_ORACLEDB_CONNECTIONSTRING environment variable to use the tnsnames entry:

Before you run the container, you need to modify the sqlnet.ora file found in the credentials directory.  Change the wallet location to the value that will be mapped inside the Docker container.

When you run the new container for the node app you will map a volume from the directory where you extracted your credentials file to the internal container directory.  Mapping a volume makes sure that there is not a copy of the credential files inside the Docker container.

Finally, when you run the new container, you will add an additional environment variable defining where the tns admin information is located.

The full command looks like this:

The container will run, execute the query and stop.  You should see something similar to this output.

I used the same docker image for all of the examples

You may have noticed that you only built the Docker image once at the beginning of the post.  The application in the Docker image uses environment variables to connect to the database.  This allows you to run the same application in one or more containers and each container can connect to different Oracle Databases without having to rebuild the image.

Deploy a Node.js application to Oracle Application Container Cloud Service

About Application Container Cloud Service

ACCS provides a pre-configured platform (Platform as a Service or PAAS) where you can quickly deploy and host your applications.  For many of today’s applications, the hosting server is just that, a place to host the application.  Most of the time the only thing an application needs from the server is to have it support the application’s programming language and to provide in and out connections through ports.  Using a PAAS such as ACCS frees you from all of the extra work of configuring and maintaining a server and allows you to focus on perfecting your application.

ACCS supports multiple languages but for this post, I’ll focus on Node.js.

DinoDate

For the examples, I will be deploying the DinoDate application. DinoDate was written as an open source learning application that can be used to demonstrate database concepts with multiple programming languages.  It currently has both Python and NodeJS mid-tier applications and is backed by an Oracle Database.

The following instructions show how to deploy the Node.js version of DinoDate to an Oracle ACCS instance.

If you don’t have access to Oracle Cloud services, you can try the Oracle Cloud with $300 of free credit.

Download/Clone the DinoDate application.

Database

First, you’ll need a database.

Create an Oracle Cloud database or if you already have an Oracle Database, make sure that you can safely create and destroy the DD and DD_NON_EBR schema.

Connect to your database as sys with sysdba and run coreDatabase/dd_master_install.sql.  (Use your password and connect string)

Prepare the DinoDate Application

Download oraclejet.zip (version 4.1.0).  (Current versions as of the time of this post.)

  • Extract the Oracle JET files
  • Run bower install
  • Install the NodeJS modules.  ACCS assumes your deploy package will include all necessary modules.
  • Create a deploy directory.
  • Copy the front end client into the deploy directory.
  • Copy the nodejs application into the deploy directory.
  • Change to the deploy/nodejs directory.
Create the environment variables.  (Replace YourJdbcConnecString with your JDBC connect string.)
Test the application to make sure everything is working.
  • Open your browser to localhost:3000
  • Log in as bob@example.com (any password will work)
  • Open the search tab and execute a search.  I typically search for ‘eat’ it will return several members.

Ctrl-c to stop the node server then switch back to the deploy directory.

Package the Files to Deploy

Create the manifest file: manifest.json

This file declares that we will use Node.js version 8 and provides the command that will be used to start the application.

Create the deployment file: deployment.json

This file includes the environment variables DinoDate needs and sets the ACCS deployment to use 1G of memory and only install 1 instance.

Replace “YourJdbcConnecString” with the JDBC connect string for your database.

Important Note

ACCS is pre-configured to listen on $PORT so we set our application to listen on that port.  Do not attempt to change $PORT.  When ACCS performs its post-deploy check it will open the application using $PORT, if the application is not listening on that port and returns a 404 the deployment will fail and be removed.

Create a zip file with the required DinoDate deploy files.

Important Note

The Node.js platform in ACCS has the oracledb module pre-installed.  If we were to upload the module we just installed it would cause a conflict that would cause the deployment to fail and be removed, so we exclude it from the deployment .zip file.

Deploy to ACCS

In your browser navigate to the Oracle Application Container Cloud Service Console.

Push the Create Application button to open the platform selection panel.

 

Push the Node button to open the application definition panel.

  • Populate [Name] with DinoDate.
  • Click ‘Choose File’ for Archive and select the DinoDateNodeACCS.zip file.
  • Click ‘Choose File’ for Manifest and select the manifest.json file.
  • Click ‘Choose File’ for Deployment Configuration and select the deployment.json file.

You can change the values in the other fields as you’d like, but notice that since we defined “memory”: “1G” and “instances”: “1” in the deployment.json file those values will change automatically.

It’s also possible to include the manifest.json file in the DinoDateNodeACCS.zip file instead of uploading it separately.

Click Create.

It may take several minutes for ACCS to setup the environment and deploy the application.  Once it’s done click on the URL: link to open the application.

Try it out

You can log in with any of the existing users, such as:

  • Bob
    bob@example.com
  • Admin
    admin@example.com

Use any value for the password, the application doesn’t check it.

Click on the Search tab and search for ‘eat’ it should return 6 of the pre-loaded dinosaurs.

Quick Review

  1. Build your application and test it.
  2. Collect the required deployment artifacts and dependencies into a .zip file.
    Reminder: do not include the oracledb module.
  3. Create a manifest.json file that contains at least the required Node.js version and the command used to start your application.
  4. Create a deployment.json file that contains any needed environment variable definitions.  Optionally you can include ACCS environment definitions such as required memory and number of instances.  (This file is optional)
    Reminder: ACCS will use the pre-defined environment variable $PORT.  Make sure your application listens on $PORT.
  5. Use the ACCS service console to upload your 3 files and create your new application.

If you run into any trouble, leave a comment and I’ll be happy to help.

Deploy NodeJS & Python3 applications on an Oracle Cloud Compute instance

DinoDate

DinoDate currently has both Python and NodeJS mid-tier applications and is backed by an Oracle Database.

The following instructions show how to deploy DinoDate to an Oracle Cloud Compute instance.  However, if you just need to deploy a NodeJS or Python application, the same instructions should help you install Node and/or Python 3.

If you don’t have access to an Oracle Database you can try the Oracle Cloud for free.

Database

Download/Clone DinoDate to get the database scripts you’ll need.

Create an Oracle Cloud database.

Connect to your database as sys with sysdba and run coreDatabase/dd_master_install.sql.  (Use your password and connect string)

Compute

Create an Oracle Cloud Compute instance.

Open the ports for our NodeJS and Python apps.

Download and scp the following to your new compute instance.  (Current versions as of the time of this post.)

Open an ssh connection to your compute instance.  (Use your ssh key and the public IP address for your compute instance)

  • Switch to su
  • Update your instance
  • Install some tools we’ll need

Install both Oracle Instant Client files

Install NodeJS 8

  • Install some tools we’ll need
  • Enable the config manager
  • Install Python 3.5
  • Enable Python 3.5
  • Upgrade pip
  • Install the python modules for DinoDate
    • cx_Oracle
    • bottle
  • Exit scl bash:

Exit su

Add the following to your .bash_profile:

  • Create the environment variables  for DinoDate (use the JDBC connect string for your database)
  • Enable Python 3.5.
Re-run .bash_profile
  • Clone DinoDate to your Compute instance
  • Extract the Oracle JET files
  • Run bower install
  • Install the NodeJS modules
  • Use pm2 to start the NodeJS version of DinoDate
    The –watch parameter will restart the application if the files change.
  • (We already installed the Python modules above)
  • Use pm2 to start the NodeJS version of DinoDate
    The –watch parameter will restart the application if the files change.
‘pm2 startup’ will generate the command needed to restart our applications on boot.  The following will extract and execute the command from the generated text.

Try it out

Open a browser and pull up DinoDate:

  • NodeJS
    http://YourComputePublicIP:3000
  • Python
    http://YourComputePublicIP:8088

You can log in with any of the existing users, such as:

  • Bob
    bob@example.com
  • Admin
    admin@example.com

Use any value for the password, the application doesn’t check it.

Click on the Search tab and search for ‘eat’ it should return 6 of the pre-loaded dinosaurs.

If you run into any trouble, leave a comment and I’ll be happy to help.

My ODTUG GeekAThon 2017 Entry

The rules and other information can be found at ODTUG GeekAThon 2017.

Problem

My son Alex attends a school where the students have some ‘bonus features’, or as the school puts it: “Educating Exceptional People”.  There are some students at Alex’s school who sometimes try to wander away.  Obviously, this could be a problem but the school staff is extremely well trained and they keep a close watchful eye on all of the students.  Still, I’d like to try and make their lives a little easier, and the students a little safer.

There are commercial systems available that could notify the administration and/or lock doors when a beacon worn by a student is detected in a hazardous zone, such as leaving the school.  That sounds perfect. There’s just one problem: those systems can be very expensive.

Proposal

Implement a student tracking and door lock automation system that can operate on inexpensive components and open source the software.  I will set up a test environment at my house and my son will test it with me.

Desired Features

  • Central to the whole system is a way to detect a beacon when it enters a specific area such as near an exit door or a faculty-only area.
  • Ability to send notifications.
  • Ability to trigger a physical event such as a door lock or audible alert.
  • Log beacon detection events in a database.
    • Beacon Id.
    • Distance from the scanner.
    • Timestamp.
  • Affordable components.

Initial Idea

After browsing the web for a while I decided I would set up multiple scanners with overlapping zones then use trilateration (I like saying that word) to determine the position of the beacon.

I would set up multiple scanners, measure the distances between them and plug that data into my database.  When a scanner detects a beacon it would use my ORDS service to POST its own id, the beacon id and the calculated distance to the beacon.  On the database, I would use Oracle Spatial queries to determine the location of the beacon.  Finally, I would compare the beacon location to defined zones in my house and trigger the alerts/actions for the zones.

I have a tendency to over-engineer my projects.  I once built a doghouse that weighed close to 200 lbs.  (It was awesome.)

After getting most of this working, I realized that I could achieve the project goals by simply placing a single scanner near each zone and let that scanner initiate the alert actions for its zone.  Sometimes less is more.

Hardware

I already had a bunch of Raspberry Pi so I decided to use a couple of my Pi 3s.  Since I’m always looking for an excuse to buy more toys, I decided to get a Pi Zero W.

I have a z-wave enabled deadbolt and a Z-Stick USB hub that I can control using Home-Assistant.io.  For the audio notification, I’ll push a ‘text to speech’ action to my Sonos speaker.  I can make the Sonos say anything I want, this entertains me a lot, my family… not so much.

Software

  • Raspbian Linux
    • Linux modules
      • bluetooth
      • bluez
      • libbluetooth-dev
      • libudev-dev
  • NodeJS
    • NodeJS Modules
      • bleacon
      • request
  • IFTTT.com
  • Home-Assistant.io
  • Oracle Database
  • Oracle Rest Data Services (ORDS)

The installation instructions are in the GitHub repo.

Database

The beacons are set to transmit every two seconds and can be detected by multiple sensors.  I always like to keep track of my data so of course, I’m pushing it to a database.  I’m using an Oracle Cloud Database with an ORDS (Oracle Rest Data Services) front end to collect the data.  When a Raspberry Pi detects a beacon, it will calculate the distance then POST the data to the database.  The database will automatically record a time-stamp when the record is inserted.

This is included in the current code and it’s what I need to collect the data for the “Initial Idea” section above.

If I decide to implement the feature to track the beacon’s position throughout my house.  I just need to determine the fixed position of each scanner relative to a point in my house and using the data I’m already collecting, run an Oracle Spatial query that defines a circle from each scanner with a radius of the distance to the beacon.  Where the circles overlap is the approximate location of the beacon.  The official term (linked above) is Trilateration, but you can think of it as a Venn Diagram.

How I Deployed the System

If you’d rather, you can watch the video and skip this section.

I configured and positioned three Raspberry Pi through my house.  I put a Pi 3 in the hallway outside of the bedrooms, a Pi 3 near the front door and a Pi Zero W outside on the front porch.

  • The first Raspberry Pi 3 in the hallway is set to trigger an alert when the beacon is approximately 2 meters away. This alert will send a notification through IFTTT* to the app on my phone.
  • The Raspberry Pi 3 near the front door fires an alert when the beacon is approximately 1 meter away.
    This alert has three actions:

    • Send the ‘lock’ command to the deadbolt through the REST interface of Home-Assistant.io using Z-Wave.  Home-Assistant.io and the Z-Wave USB dongle are also installed on this Pi.
    • Set the Sonos volume to max and send ‘Locking the front door’ to the Sonos speaker using the text to speech function in Home-Assistant.io.
    • Send a notification through IFTTT to my cell phone.
  • The Raspberry Pi Zero W outside near the front door will trigger an alert when the beacon is approximately 1 meter away.
    This alert has three actions:

    • Send the ‘unlock’ command to the deadbolt through the REST interface of Home-Assistant.io using Z-Wave.
      (If Alex makes it outside, I want the door unlocked so he can come back in.)
    • Set the Sonos volume to max and send ‘Unlocking the front door’ to the Sonos speaker using the text to speech function in Home-Assistant.io.
    • Send a notification through IFTTT to my cell phone.

*IFTTT can also send a text message but the free tier only allows a limited number of texts to be sent each month. I chose to use notifications through their Android app since they are unlimited and I would have burned through the text quota the first time I forgot to limit how often I send a notification. In a live situation, it could send out multiple texts.

Challenges

I had intended to use OpenHab for the home automation features of the project, but when I built the project there was a bug in the Z-Wave addon that made interacting with the deadbolt more difficult.  I tried out Home-Assistant.io and so far I really like it.  Each application has its own strengths and weaknesses, but they both run on a Raspberry Pi so I may use both for future projects.  I’d like to mention they are both open source which is an added bonus.

The beacon distance tracking is not as accurate as I hoped, but it’s fine for this project.  The signal can be degraded by walls, bodies or other objects being between the beacon and scanner.  To improve the accuracy, I implemented a weighted rolling average function as part of the distance calculation to smooth out some of the spikes.  Deploying more scanners would also greatly improve the accuracy if I implement the position tracking.

Future Improvements

  • Add an Oracle JET front end for configuration and control of the system.
  • Add a map display that can show the beacons live.
  • Change the Alert/Action code to be more generic and provide a mechanism to define them in the front end.
  • Find a small inexpensive wearable BLE beacon or design one with a small rechargeable battery and a 3D printed enclosure.

Final Thoughts

If you decided not to participate in the GeekAThon this year please join in next year.  It is a great way to learn some new skills and have fun at the same time.  I am sure parts of what I described above sound intimidating. But if you’d like to try your hand at this or similar projects, don’t hesitate to contact me for help. And while I can’t speak for the other GeekAThon participants, this year or past years, I am certain they will be eager to help you, too.

This project has been a lot of fun, I learned a lot.  I’m looking forward to next year!

Getting DinoDate Up and Running

You’ve downloaded DinoDate, the premier dating website for dinosaurs. Now lets get it up and running!

Prerequisites

  • Download Logger and extract it to dino-date/coreDatabase
  • Check Logger’s create_user.sql file. If you see an exit statement at the very end , remove it.
  • Install Bower.
  • Install Oracle Instant Client with the sdk modules.

Installation

Database Schemas and Objects
IMPORTANT – Please make sure you’re using a database instance in which you can safely create  schemas named DD, DD_NON_EBR and DD_LOGGER.
  1. Navigate to dino-date/coreDatabase
  2. Run dd_master_install.sql from an account connected “as sysdba”.
  3. First prompt is for the directory into which you extracted Logger, e.g. Logger_3.1.1
  4. Second prompt is what you want to name the schema that holds the Logger database objects.
  5. Third and fourth prompts are for your Tablespace and Temporary Tablespace.
  6. Fourth prompt is for the logger schema password
Common Client

Open a prompt and navigate to dino-date/commonClient

RESTful Tier

Currently there are RESTful APIs written in both NodeJS and Python.  You can choose to run one or both.

NodeJS

Open a prompt and navigate to dino-date/nodejs

Python

Open a prompt and navigate to dino-date/python

Configure your Environment

DinoDate uses environment variables for database connection and port settings.

Create the following environment variables using the correct values for your system.

  • dd_connectString=localhost:1521/orcl
  • dd_user=dd
  • dd_password=dd
  • dd_port=8888
  • dd_python_port=8080
  • dd_node_port=3000

Run DinoDate

NodeJS

Open a prompt and navigate to dino-date/nodejs

DinoDate (NodeJS) will be listening on the dd_node_port port you defined above.

Python

Open a prompt and navigate to dino-date/python

DinoDate (Python) will be listening on the dd_python_port port you defined above.

Running Both

You can run both versions at the same time, provided you specified different ports.

This will allow you to switch between languages by changing the ports and view the code examples specifically for each language.

Please leave a comment if you run into trouble.

WORKAROUND FOR DATABASE BIND ISSUES

Update: Thanks to Chris Jones for letting me know there has been an update to node-oracledb that handles the original problem.

I had originally written this post in November 2015, back then everything ‘didn’t work’ the way I had described it. But the node-oracledb project is very active and shortly after that, they added a feature which allows you to bind out to single value arrays. If you have tried my example code since then you would have noticed that the problem I described actually does work if you follow the comment from Chris Jones.

The point of the post is to demonstrate ways to work around problems that may come up when binding to PL/SQL programs. It could be bind issues like below, or maybe you have a very complex query in your code and you want to use PL/SQL to handle the complex part but still use it as a query.

I’ve updated this post to use a different example that will throw the same error. If/when my new example becomes obsolete due to new features in node-oracledb, just pretend the problem is real and try the solutions.

Using node-oracledb version: 1.9.3

I’ve been working on DinoDate, a demonstration application that shows how to implement the same functionality in different languages, all on top of an Oracle Database foundation. I had the Python code working and it was time to work with JavaScript and NodeJS, using the node-oracledb driver for the first time.

How I learn

When I’m learning something new, I prefer learning from code examples over reading documentation.  I pick up little extra nuggets of knowledge this way since I tend to break things in the examples, which I then have to sort out and fix.

However, I will only bang my head against the wall so many times before I read the docs. At last count, that was 57 times.

The Problem

One of the application examples demonstrates how to use a PL/SQL stored procedure to perform some different searches.  The procedure uses OUT parameters to pass the data back.

Here’s the PL/SQL code (defined in a package):

In Node, I attempted to bind to the out parameters like this:

This had worked several times before in other functions when I returned a single value from an out parameter.  This time, I couldn’t find a single node-oracledb type that would work. Most of them resulted in this error:

So I read the doc, fired up my trusty old search engine and searched, and then did what I should have done right from the start: lean on another member of my Oracle Developer Advocates team:  Dan McGhan.  Who immediately told me “The driver only support scalar types right now.”  Update: The driver also supports arrays of a single string or number.

When you learn by experimenting on code rather than reading the docs, sometimes you miss out on the ‘real words.’  I assumed ‘scalar types’ means a variable that holds a single value opposed to an array.  Just to be sure, I found a decent explanation on Stack Overflow.

Well, game over, right?

Ha! The game is only over when you quit. It was time to explore some options.

Change the PL/SQL procedure to a function
Return the results using JSON.

I considered running the query, building a JSON object from the results and returning them as a string in a clob.

But this doesn’t fit with the way the other application functions work. The database calls return unformatted results which are returned to the API functions and then those functions convert the results to JSON (or other formats if required) and sent back in the response.

Use a ref cursor

This could also be a viable solution and here’s a good example of how to use a ref cursor.

One goal of this demonstration application is that all functionality will be reproduced in multiple languages; Python, Ruby, PHP and others. I am trying to keep the functions as simple as possible. Other languages may not be able to work with a ref cursor.

Pipelined function

I decided to use a pipelined table function.  Table functions are very cool. In combination with the TABLE operator, I can return a collection to the FROM clause of a SELECT, and then have it treated as if it were a table, full of rows and columns. I can apply all the usual SQL SELECT operations on this dataset. Plus, the results are returned unformatted, so a developer should be able to use just about any language  to run a simple query built around this feature.

PL/SQL:

JavaScript:

This works great but what if you can’t change the PL/SQL procedure to a function because it’s used elsewhere?

Add a PL/SQL wrapper function
Now call text_only_wrapper in the JavaScript function above.

More options

Of course, there are other ways to work around the original issue and I’m sure there are better ones than what I’ve chosen.  If you have a better solution, please share it.

Share

Speaking of sharing.  The node-oracledb project is on GitHub under the Apache License, Version 2.0.  If you are able to add support for compound types such as arrays, please submit a pull request.