X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

A node-oracledb Web Service in Docker

Christopher Jones
Senior Principal Product Manager

This post shows how to run a node-oracledb application in a Docker Container. For bonus points, the application connects to an Oracle Database running in a second container.

The steps are the 'show notes' from a recent talk at Oracle Code.

The demo app is a simple Express web service that accepts REST calls.

 

DOCKER

Oracle Docker images are available from https://store.docker.com/ and also mirrored on https://container-registry.oracle.com

If you're not familiar with Docker, it helps to know basic terminology:

  • Images: Collection of software to be run as a container. Images are immutable. Changes to an image require a new image build.

  • Registry: Place to store and download images.

  • Container: A lightweight standalone, executable piece of software that includes everything required to run it on a host. Containers are spun up from images. Containers are non-persistent. Once a container is deleted, all files inside that container are gone.

  • Docker engine: The software engine running containers.

  • Volumes: Place to persist data outside the container.

CONFIGURE A DOCKER HOST

For my host, I used Oracle Linux 7, which has the ol7_latest and ol7_uekr4  channels already enabled.

  • Install the Docker engine as the root user by running 'sudo su -', or prefix each command with 'sudo':

    # yum-config-manager --enable ol7_addons
    # yum install docker-engine
    # systemctl enable docker
    # systemctl start docker
    

DOWNLOAD INSTANT CLIENT AND DATABASE DOCKER IMAGES

  • Sign in to the container registry https://container-registry.oracle.com/ with your (free) Oracle "single sign-on" (SSO) credentials.

  • Accept the license on the container registry.

  • On your OL7 Docker host, log in to the registry. Remember to run Docker commands as 'root':

    # docker login container-registry.oracle.com
    

    This prompts for your Oracle SSO credentials.

  • Get the Oracle Database and Oracle Instant Client images:

    # docker pull container-registry.oracle.com/database/enterprise:12.2.0.1
    # docker pull container-registry.oracle.com/database/instantclient:12.2.0.1
    

    This can take a while. For testing, you may want to pull the smaller, 'slim' version of the database. [Update: Oracle Instant Client 18.3 can be downloaded without clickthrough meaning you can build images easily with this Dockerfile. Since it is based on the Oracle Linux 'slim' image, the container size is considerably smaller.]

  • View the installed images with:

    # docker images
    REPOSITORY                                           TAG       IMAGE ID      CREATED       SIZE
    container-registry.oracle.com/database/enterprise    12.2.0.1  12a359cd0528  7 months ago  3.44GB
    container-registry.oracle.com/database/instantclient 12.2.0.1  fda46de41de3  7 months ago  407MB
    

CREATE A DATABASE CONTAINER FROM THE DATABASE IMAGE

  • Start the database container:

    # docker run -d --name demodb -P container-registry.oracle.com/database/enterprise:12.2.0.1
    

    The '-P' option maps the ports used, allowing access to the database from outside the container.

  • Check for its health and wait until it shows 'healthy'

    # docker ps
    CONTAINER ID  IMAGE                COMMAND                STATUS               PORTS                        NAMES
    9596bc2345d3  [...]/database/[...] "/bin/sh -c '/bin/..." Up 3 hours (healthy) ...->1521/tcp, ...->5500/tcp demodb
    
  • Find the database container's IP address:
    # docker inspect -f "{{ .NetworkSettings.IPAddress }}" demodb
    

    You will use this IP in database connect strings in your applications.

  • You can stop and start the container as desired:

    # docker stop demodb
    # docker start demodb
    

    The data is persistent as long as the container exists. Use 'docker ps --all' to show all containers, running or not.

CREATE A NEW SCHEMA

  • Create a SQL file called createschema.sql:

    SET ECHO ON
    
    ALTER SESSION SET CONTAINER=orclpdb1;
    DROP USER scott CASCADE;
    CREATE USER scott IDENTIFIED BY tiger;
    GRANT CONNECT, RESOURCE TO scott;
    ALTER USER scott QUOTA UNLIMITED ON USERS;
    
    DROP TABLE scott.bananas;
    CREATE TABLE scott.bananas (shipment VARCHAR2(4000) CHECK (shipment IS JSON));
    
    INSERT INTO scott.bananas VALUES ('{ "farmer": "Gita", "ripeness": "All Green", "kilograms": 100 }');
    INSERT INTO scott.bananas VALUES ('{ "farmer": "Ravi", "ripeness": "Full Yellow", "kilograms": 90 }');
    INSERT INTO scott.bananas VALUES ('{ "farmer": "Mindy", "ripeness": "More Yellow than Green", "kilograms": 92 }');
    
    COMMIT;
    
    EXIT
    

    For this demo, you can see I used the Oracle Database 12.1.0.2 JSON data type.

  • Execute createschema.sql in your favorite tool, such as SQL*Plus.

    In my case I actually ran SQL*Plus on my Docker host machine. Cheating a bit on giving details here, I had downloaded the Instant Client Basic and SQL*Plus packages and unzipped as shown in the the Instant Client instructions. I then set my shell to use the SQL*Plus binary:

    # export LD_LIBRARY_PATH=/home/cjones/instantclient_12_2
    # export PATH=/home/cjones/instantclient_12_2:$PATH
    

    Using the database IP address as shown earlier you can now run the script in SQL*Plus against the container database. In my environment the database IP was 172.17.0.2:

    # sqlplus -l sys/Oradoc_db1@172.17.0.2/orclpdb1.localdomain as sysdba @createschema.sql
    

    The database password and service name shown are the defaults in the image.

CREATE A NODE.JS IMAGE

Let's add Node.js to the Instant Client image.

  • Create a sub-directory nodejs-scripts

    # mkdir nodejs-scripts
    
  • Create a new file 'nodejs-scripts/Dockerfile'. This is the 'recipe' for building a Docker image. Here Node.js is added to the Instant Client image to create a new image usable by any Node.js application. The Node.js 8 package for Oracle Linux is handy.

    The Dockerfile should contain:

    FROM container-registry.oracle.com/database/instantclient:12.2.0.1
    
    ADD ol7_developer_nodejs8.repo /etc/yum.repos.d/ol7_developer_nodejs8.repo
    
    RUN echo proxy=http://my-proxy.example.com:80 >> /etc/yum.conf
    
    RUN yum -y update && \
        rm -rf /var/cache/yum && \
        yum -y install nodejs
    

    The FROM line shows that we base our new image on the Instant Client image.

    If you are not behind a proxy, you can omit the proxy line. Or change the line to use your proxy.

    For quick testing, you may want to omit the 'yum -y update' command.

  • The Dockerfile ADD command copies 'ol7_developer_nodejs8.repo' from the host file system into the image's file system. Create 'nodejs-scripts/ol7_developer_nodejs8.repo' containing:

    [ol7_developer_nodejs8]
    name=Oracle Linux $releasever Node.js 8 Packages for Development and test ($basearch)
    baseurl=https://yum.oracle.com/repo/OracleLinux/OL7/developer_nodejs8/$basearch/
    gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
    gpgcheck=1
    enabled=1
    
  • Now the new image with Oracle Instant Client and Node.js 8 can be built using this Dockerfile:

    docker build -t cjones/nodejs-image nodejs-scripts
    

    The 'cjones/nodejs-image' is the image name, not a directory path.

  • You can see the new image has been created:

    # docker images
    REPOSITORY                                            TAG       IMAGE ID      CREATED         SIZE
    cjones/nodejs-image                                   latest    e048b739bb63  29 minutes ago  1.51GB
    container-registry.oracle.com/database/enterprise     12.2.0.1  12a359cd0528  7 months ago    3.44GB
    container-registry.oracle.com/database/instantclient  12.2.0.1  fda46de41de3  7 months ago    407MB
    

CREATE A NODE.JS DEMO IMAGE

The new Node.js image is refined by installing our demo application. This creates another new image that we can later run whenever we want to use the application.

  • Create a sub-directory 'ws-demo-scripts':

    # mkdir ws-demo-scripts
    
  • Create a new file 'ws-demo-scripts/Dockerfile' containing:

    FROM cjones/nodejs-image
    
    ENV https_proxy=http://my-proxy.example.com:80
    
    WORKDIR workdir
    
    COPY package.json package.json
    COPY server.js server.js
    RUN npm install
    
    CMD ["npm", "start"]
    

    The first line shows the new image should be based on the Node.js image 'cjones/nodejs-image' created in the section above.

    Again, adjust the proxy line as needed by your network.

    You can see the Dockerfile copies two files from our host file system into the image. These files are shown below.

    When the image is created, the RUN command will install the Node.js dependencies from package.json.

    When a container starts, the CMD action is taken, which runs 'npm start', in turn invoking the 'main' target in package.json. Looking below to the package.json content, you can see this means 'node server.js' is run.

  • Create a file 'ws-demo-scripts/package.json' containing:

    {
      "name": "banana-farmer",
      "version": "1.0.0",
      "description": "RESTful API using Node.js Express Oracle DB",
      "main": "server.js",
      "author": "Oracle",
      "license": "Apache",
      "dependencies": {
        "body-parser": "^1.18.2",
        "express": "^4.16.0",
        "oracledb": "^2.2.0"
      }
    }
    

    As obvious, this application installs the body-parser module, the node-oracledb module, and also express. This demo is an Express web service application. And yes, it is a Banana Farmer web service.

    The default run target of package.json is the application file 'server.js'.

  • Create the application file 'ws-demo-scripts/server.js' containing the contents from here.

    The demo application is just this one file.

  • Build the demo image:

    # docker build -t cjones/ws-demo ws-demo-scripts
    

    We now have our fourth image which contains our runnable application:

    # docker images
    REPOSITORY                                            TAG       IMAGE ID      CREATED         SIZE
    cjones/ws-demo                                        latest    31cbe6d2ea4e  21 seconds ago  1.51GB
    cjones/nodejs-image                                   latest    e048b739bb63  29 minutes ago  1.51GB
    container-registry.oracle.com/database/enterprise     12.2.0.1  12a359cd0528  7 months ago    3.44GB
    container-registry.oracle.com/database/instantclient  12.2.0.1  fda46de41de3  7 months ago    407MB
    

DEMO APPLICATION OVERVIEW

The Banana Farmer scenario is that shipments of bananas from farmers are recorded. They can have a farmer name, ripeness, and weight. Shipments can be inserted, queried, updated or deleted.

Let's look at a couple of snippets from ws-demo-scripts/server.js.

A connection helper creates a pool of database connections:

oracledb.createPool({
      user: process.env.NODE_ORACLEDB_USER,
      password: process.env.NODE_ORACLEDB_PASSWORD,
      connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING
    }, . . .

The credentials are taken from environment variables. When we run the app container we will pass value for those environment variables into the container.

The application has Express routes for REST GET, POST, PUT and DELETE calls. The code to handle a GET request looks like:

// HTTP method: GET
// URI        : /bananas/FARMER
// Get the banana shipment for FARMER
app.get('/bananas/:FARMER', function (req, res) {
  doGetConnection(res, function(err, connection) {
    if (err)
      return;
    connection.execute(
      "SELECT b.shipment FROM bananas b WHERE b.shipment.farmer = :f",
      { f: req.params.FARMER },
      function (err, result) {
        if (err) {
          res.set('Content-Type', 'application/json');
          res.status(500).send(JSON.stringify({
            status: 500,
            message: "Error getting the farmer's profile",
            detailed_message: err.message
          }));
        } else if (result.rows.length < 1) {
          res.set('Content-Type', 'application/json');
          res.status(404).send(JSON.stringify({
            status: 404,
            message: "Farmer doesn't exist",
            detailed_message: ""
          }));
        } else {
          res.contentType('application/json');
          res.status(200).send(JSON.stringify(result.rows));
        }
        doRelease(connection, "GET /bananas/" + req.params.FARMER);
      });
  });
});

Express makes it easy. It handles the routing to this code when a GET request with the URL '/bananas/<name>' e.g. '/bananas/Gita' is called. This simply binds the URL route parameter containing the farmer’s name into the SELECT statement. Binding is important for security and scalability, as you know. The SQL syntax used is the JSON 'dot' notation of Oracle Database 12.2 but it could be rewritten to work with 12.1.0.2.

The bulk of the code is error handling, looking after the cases where there was a processing error or no rows returned. It sends back HTTP status codes 500 or 404, respectively.

The success code path sends back the query output 'result.rows' as a JSON string, with the HTTP success status code 200.

START THE DEMO CONTAINER

Let's run the application.

  • Create a file 'ws-demo-scripts/envfile.list' with the credentials for the application. Use the IP address of your database container found with the 'docker inspect' command shown previously. In my environment, the database IP address was '172.17.0.2'

    NODE_ORACLEDB_USER=scott
    NODE_ORACLEDB_PASSWORD=tiger
    NODE_ORACLEDB_CONNECTIONSTRING=172.17.0.2/orclpdb1.localdomain
    
  • Start the Node.js web service container

    # docker run -d --name nodejs -P --env-file ws-demo-scripts/envfile.list cjones/ws-demo
    

STATUS CHECK

  • To recap what's happened, the Docker images are:

    # docker images
    REPOSITORY                                            TAG       IMAGE ID      CREATED         SIZE
    cjones/ws-demo                                        latest    25caede29b17  12 minutes ago  1.51GB
    cjones/nodejs-image                                   latest    138f2b76ffe7  13 minutes ago  1.51GB
    container-registry.oracle.com/database/enterprise     12.2.0.1  12a359cd0528  7 months ago    3.44GB
    container-registry.oracle.com/database/instantclient  12.2.0.1  fda46de41de3  7 months ago    407MB
    

    Two base images were downloaded, An image with Node.js was created from the Instant Client image. Finally a fourth image 'cjones/ws-demo' with the Node.js, Instant Client and the application code was created.

  • We have started database ('demodb') and application containers ('nodejs'):

    # docker ps
    CONTAINER ID  IMAGE                COMMAND                STATUS               PORTS                        NAMES
    2924e1225290  cjones/ws-demo       ”npm start"            Up 3 hours                                        nodejs
    9596bc2345d3  [...]/database/[...] "/bin/sh -c '/bin/..." Up 3 hours (healthy) ...->1521/tcp, ...->5500/tcp demodb
    

    We found the IP address of the database container, and knew (by reading the container registry documentation) the default credentials of the SYS user.

    We created a schema SCOTT on the database, with a table containing some JSON data.

    An application container was started, with the database application credentials and connection string specified in an environment file outside the container.

SUBMIT REST REQUESTS

Now we can call our application, and it will access the database.

  • Install the browser extension HttpRequester (in Firefox) or Postman (in Chrome).

  • Find the IP of the demo web service container:

    # docker inspect -f "{{ .NetworkSettings.IPAddress }}" nodejs
    

    In my environment, it was '172.17.0.3'. Use this with the port (3000) and various endpoints (e.g. '/bananas/<farmer>') defined in server.js for REST requests.

  • In the HttpRequester or Postman extensions you can make various REST calls.

    Get all shipments:

    GET
    http://172.17.0.3:3000/bananas
    

    Get one farmer's shipment(s):

    GET
    http://172.17.0.3:3000/bananas/Gita
    

    New data:

    POST
    http://172.17.0.3:3000/bananas
    
    {
        "farmer" : "CJ",
        "ripeness" : "Light Green",
        "kilograms" : 50
    }
    

    Update data:

    PUT
    http://172.17.0.3:3000/bananas/CJ
    
    {
        "farmer" : "CJ",
        "ripeness" : "50% Green, 50% Yellow",
        "kilograms" : 45
    }
    

    Here's a screenshot of HttpRequester in action doing a GET request to get all banana shipments. On the left, the red boxes show the URL for the '/bananas' endpoint was executed as a GET request. On the right, the response shows the success HTTP status code of 200 and the returned data from the request:

    Screenshot of HttpRequester

When you are finished with the containers you can stop them:

# docker stop demodb
# docker stop nodejs

If you haven't tried Docker yet, now is the perfect time! Containers make deployment and development easy. Oracle's Docker images let you get started with Oracle products very quickly.

If you want to learn more about Node.js and REST, check out the series Creating a REST API with Node.js and Oracle Database.

And finally, if you've read this far, you can download all the files used from here.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services