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.
1 2 3 4 5 6 7 8 |
module.exports = { user : process.env.NODE_ORACLEDB_USER, password : process.env.NODE_ORACLEDB_PASSWORD, connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING, poolMax: 2, poolMin: 2, poolIncrement: 0 }; |
Create a new file named server.js
used to test the connection.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
const oracledb = require('oracledb'); const config = require('./dbConfig.js'); async function runTest() { let conn; try { conn = await oracledb.getConnection(config); const result = await conn.execute( 'select current_timestamp from dual' ); console.log(result); } catch (err) { console.error(err); } finally { if (conn) { try { await conn.close(); } catch (err) { console.error(err); } } } } runTest(); |
Install the node-oracledb driver. (If you have any questions about the node-oracledb driver, you can find the answers here.)
1 |
npm install oracledb -s |
Create environment variables for the connection information. (Replace my values with yours)
1 2 3 |
export NODE_ORACLEDB_USER=NotMyRealUser export NODE_ORACLEDB_PASSWORD=NotMyRealPassword export NODE_ORACLEDB_CONNECTIONSTRING=192.168.0.44:1521/NotMyRealServiceName |
Test the Node.js application. You should see something similar to the following.
1 2 3 4 5 |
@OraBlaineOS:SimpleApp$ node server.js { metaData: [ { name: 'CURRENT_TIMESTAMP' } ], rows: [ [ 2019-08-20T17:50:38.615Z ] ] } |
Create a Docker Container for the Node.js app
Create a Dockerfile
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
FROM oraclelinux:7-slim # Create app directory WORKDIR /usr/src/app # Copy the .js files from your host machine into the new app directory ADD *.js ./ # Update Oracle Linux # Install Node.js # Install the Oracle Instant Client # Check that Node.js and NPM installed correctly # Install the OracleDB driver RUN yum update -y && \ yum install -y oracle-release-el7 && \ yum install -y oracle-nodejs-release-el7 && \ yum install -y --disablerepo=ol7_developer_EPEL nodejs && \ yum install -y oracle-instantclient19.3-basic.x86_64 && \ yum clean all && \ node --version && \ npm --version && \ npm install oracledb && \ echo Installed CMD ["node", "server.js"] |
Build the image
1 |
docker build --no-cache --force-rm=true -t node_example . |
Now that you have an image you’ll run a container and to connect it to:
Typical Oracle Database
1 2 3 4 5 6 |
docker run \ --name typical_node_example -it \ -e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \ -e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \ -e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \ node_example |
1 2 |
{ metaData: [ { name: 'CURRENT_TIMESTAMP' } ], rows: [ [ 2019-08-20T18:15:23.615Z ] ] } |
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.
1 |
docker network create OrdsXeNet |
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.
1 |
export NODE_ORACLEDB_CONNECTIONSTRING=oracleXe:1521/xepdb1 |
Now when you run a new docker container you will attach it to the same Docker Network as the Oracle XE container.
1 2 3 4 5 6 7 |
docker run \ --name docker_network_node_example -it \ --network=OrdsXeNet \ -e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \ -e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \ -e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \ node_example |
The container will run, execute the query and stop. You should see something similar to this output.
1 2 |
{ metaData: [ { name: 'CURRENT_TIMESTAMP' } ], rows: [ [ 2019-08-27T16:42:16.122Z ] ] } |
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:
1 |
export NODE_ORACLEDB_CONNECTIONSTRING=projects_tp |
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.
1 |
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/wallet/"))) |
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.
1 |
-v /my/host/machine/credentials/directory:/wallet |
Finally, when you run the new container, you will add an additional environment variable defining where the tns admin information is located.
1 |
-e TNS_ADMIN=/wallet |
The full command looks like this:
1 2 3 4 5 6 7 8 9 |
docker run \ --name cloud_atp_node_example -it \ -v /my/host/machine/credentials/directory:/wallet \ -e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \ -e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \ -e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \ -e TNS_ADMIN=/wallet \ -p 3000:3000 \ node_example |
The container will run, execute the query and stop. You should see something similar to this output.
1 2 |
{ metaData: [ { name: 'CURRENT_TIMESTAMP' } ], rows: [ [ 2019-08-27T18:21:26.298Z ] ] } |
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.
Hi,
I’m trying to connect Autonomous Database with Oracle functions in python.
I’m struggling with it, since there is no one post anything about this.
If you have any idea about it, could just email me?
Thanks a lot
Tracy
Howdy Tracy,
Try this article I wrote for Oracle Magazine.
https://blogs.oracle.com/oraclemagazine/getting-started-with-autonomous
it’s much better to use –env-file=env.list to prevent password leaks through the command-line history or top or ps.
I will check it out. Thank you
Hello,
Thank you very much for this. I am able to build the docker image in my local macOs and successfully connect to the oracle database. However, there is an issue while building the Azure DevOps build pipeline. Error encountered while building the same image as below :
Error getting repository data for ol7_developer_EPEL, repository not found
[0mThe command '/bin/sh -c yum update -y && yum install -y ca-certificates && yum install -y oracle-release-el7 && yum install -y oracle-nodejs-release-el7 && yum install -y --disablerepo=ol7_developer_EPEL nodejs && yum install -y oracle-instantclient19.3-basic.x86_64 && yum clean all && node --version && npm --version && npm install express && npm install oracledb && update-ca-trust && echo Installed' returned a non-zero code: 1
##[error]/usr/bin/docker failed with return code: 1
It would be really great if you can help for the same. Kindly let me know if I can provide further information. Thank you.
Are you using Oracle Linux 7 in Azure?
Dear Bcarter,
Sorry for the late response. I didn’t notice your message or I missed to choose the option to notify follow-up comments. I am not using Oracle Linux 7 in Azure. I am just building docker image locally and pushing it to the Azure Container Registry. However, suddenly it is not allowed to build the image locally and encounter the same: ‘Error getting repository data for ol7_developer_EPEL, repository not found’.
Hi Ashok,
Did you figure this one out?
I am getting the same issue not on Azure but on my local environment using linux ubuntu 20.04LTS?
This post was written using Oracle Linux 7. I have not tried other operating systems. ol7_developer_EPEL is a Oracle Linux 7 repo (ol7_).
However, since that command is disabling the repo you can try taking it out and just installing node.
yum install -y nodejs
You may get an error on the next step. yum install -y oracle-instantclient19.3-basic.x86_64
If so, you’ll need to look up how to install the oracle instant client for your OS.
Once you have those installed, I believe the rest of the examples should work.
Yes thank you taking that out resolved it and I have built and ran the docker image no problem.
Hi bcarter,
Thank you very much for this. It helped me a lot! I was having a hard time to connect my Node app to Oracle in a Docker image and now it works!