X

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

Part 2: Docker for Oracle Database Applications in Node.js and Python

Christopher Jones
Senior Principal Product Manager

This is the second in a two part series on using Docker for Oracle Database applications.

The examples are for Node.js and Python, but the concepts are also useful for the many other languages whose database APIs are implemented on top of Oracle Call Interface; these other languages include C, PHP, Go, Ruby, Rust, Erlang, Julia, Nim, and Haskell.

Creating Docker Containers that Connect to Oracle Database

Introduction

This post shows how to connect to an existing database from an application in Docker. It also gives some application best practices useful when running multiple Docker containers.

To keep this blog series to a manageable length and focussed on applications, it doesn't cover database setup. If you are interested in also running Oracle Database in a container, see Oracle DB and Docker Best Practices and the Oracle Database Dockerfiles on GitHub.

An Application Container that connects to Oracle Database

All the steps to set up the application stack were shown in Part 1. Now we just need to install an application that connects to Oracle Database. This example is a Python cx_Oracle application based on the image cjones/python shown in Part 1. That image has Python, the cx_Oracle driver, and Oracle Instant Client installed. For the new image, we add a simple application that connects to your existing Oracle Database and queries the date. The new Dockerfile is:

FROM cjones/python

WORKDIR /myapp

ADD query.py /myapp

CMD exec python3.6 query.py

The simple Python app is:

# query.py

import cx_Oracle

connection = cx_Oracle.connect("cj", "welcome", "myhost.example.com/orclpdb1")

cursor = connection.cursor()
cursor.execute("select systimestamp from dual")
r, = cursor.fetchone()
print(r)

Hard coding passwords like this is not recommended. Credential management is discussed later.

With the Dockerfile and query.py files in ~/docker/pythonquery/, we can build an image:

$ sudo docker build -t cjones/pythonquery ~/docker/pythonquery/

and then run a container from it:

$ sudo docker run --name pythonquery cjones/pythonquery
2019-09-04 08:39:22.955499

The application executes the query and displays the queried timestamp. After running a container, the docker ps command can be used to show the status:

$ sudo docker ps -a
CONTAINER ID  IMAGE              COMMAND                    CREATED     STATUS                    NAMES
e9074760444  cjones/pythonquery  "/bin/sh -c 'exec py. . ."  6 secs ago  Exited (0) 2 seconds ago  pythonquery

As shown in Part 1, you can use commands like docker start and docker rm to re-run or remove the container.

Wallets and Credentials

Environment Variables

If you hard code database credentials in applications, then anyone who has access to the image will always connect to your database - and will be able to see the password.

One way to avoid this is to pass credentials into the container at run time using environment variables. On the Docker host you can create a file such as envfile.txt with the variables and their values:

APP_USER=cj
APP_PASSWORD=mytopsecretpassword
APP_CONNECTIONSTRING=myhost/orclpdb1

Your application can then reference the variables. For example, in Python:

import cx_Oracle
import os

un = os.environ.get('APP_USER')
pw = os.environ.get('APP_PASSWORD')
cs = os.environ.get('APP_CONNECTIONSTRING')
connection = cx_Oracle.connect(un, pw, cs)

. . .

At run time you use --env-file to pass the environment variables into the container, for example:

$ sudo docker run --name pythonquery \
        --env-file ~/docker/pythonquery/envfile.txt cjones/pythonquery

Wallets

Consider using a wallet for security. Oracle wallets may contain database credentials, or can enable connections over TLS.

Oracle Cloud databases provide wallets that are mandatory for access. These configure TLS, but don't contain usernames or passwords. Download the wallet from Oracle Cloud and unzip into a host directory such as /OracleCloud/wallet/. For cx_Oracle and node-oracledb, you can remove all files except cwallet.sso, sqlnet.ora, and tnsnames.ora.

When you run the client container you can mount the directory as a volume. For example:

$ sudo docker run --name pythonquery \
        --env-file ~/docker/pythonquery/envfile.txt \
        -v /OracleCloud/wallet:/usr/lib/oracle/19.5/client64/lib/network/admin:Z,ro \
        cjones/pythonquery

This example mounts the directory to the default location for the Oracle Instant Client 19.5 RPM inside the container. This means that the files will be used by cx_Oracle and node-oracledb (and by other C-based drivers) that use that Instant Client installation.

If you use the Instant Client Zip file as shown earlier, your -v option might be:

-v /OracleCloud/wallet:/opt/oracle/instantclient_19_5/network/admin:Z,ro

The Z option is needed when the container has SELinux enabled.

If the Cloud wallet zip's tnsnames.ora contains an alias for oracletestdb_medium, then your envfile.txt could be like:

APP_USER=cj
APP_PASSWORD=mytopsecretpassword
APP_CONNECTIONSTRING=oracletestdb_medium

Networking

By default, running containers are attached to the default 'bridge' network driver. Bridge networks apply to containers running on the same Docker daemon host.

User-defined bridges are recommended. The benefits include application isolation, automatic DNS resolution between containers, and the ability to tailor configurations to the container requirements. Containers can detach and attach to bridges dynamically

User-defined Bridges

To create a user-defined bridge:

$ sudo docker network create --driver bridge oracle-net

If you have a Docker database image called oracle/database, you might run it like:

$ sudo docker run -d --name oracledb --net oracle-net -p 1521:1521 oracle/database

This starts the container as a daemon, names it "oracledb", attaches to the named bridge, and maps Oracle Database's default port 1521 to the same port inside the database container.

An application container can use the same network. Below, a container cjones/ic19sp containing SQL*Plus is started. It runs and opens a Bash shell terminal, as described in Part 1.

$ sudo docker run --name myclient --net oracle-net -ti cjones/ic19sp /bin/bash

From the client container prompt (or from applications in any container using the same bridge), you can connect to the database using the hostname "oracledb" in the connection string:

$ sqlplus scott/tiger@oracledb/orclpdb1

If you have SQL*Plus on the host, you could use:

$ sqlplus scott/tiger@localhost/orclpdb1

Docker Host Networks

Another network option is a Docker Host Network. With this, a container shares the host's networking namespace. At run time, any "-p" or "-P" options are ignored. The database and application containers could be started like:

$ sudo docker run -d --name oracledb --net host oracle/database

$ sudo docker run --name oracleclient --net host -ti oracle/ic19sp /bin/bash

Connection from the client container prompt, or the Docker host prompt, would use "localhost" as the hostname of the database:

$ sqlplus scott/tiger@localhost/orclpdb1

Smart Application Design

Good application design is beneficial to performance and reliability, particularly if your application scales out across multiple containers.

The tips below discuss common best practices. If you are moving to Docker, you may additionally want review your application architecture; perhaps micro services using Oracle Advanced Queuing message passing are the way to go.

Connection pooling

A general application recommendation is to use an Oracle connection pool. Pools are obviously useful when an application handles multiple concurrent user tasks, allowing already established connections to be reused. This avoids the cost of creating and destroying connections for each use. See here for cx_Oracle pool documentation and here for node-oracledb pool documentation.

As well as the performance benefits, connection pools help your applications have a usable connection when you need it. Pools have internal connection validity checks. In multi-node database system they provide connection load balancing and run time load balancing. They have the best support for high availability features, particularly for planned downtime.

A connection pool can also be useful for long running applications that need a single connection readily available but doesn't always use it. In this case you can create a connection pool with a single connection, and make sure to get and release the connection to the pool at the point of use.

Keep connection pools small with a fixed size. Dynamically growing connection pools lead to connection storms and can cause applications to hit database capacity errors when the pool grows under user load. Oversized pools are often counter-productive.

In general, configure firewalls not to be over-eager in expiring connections, since connections pools are designed to be an available resource and you don't want database connection storms in a morning peak login period. Similarly disable resource mangers or user profiles that limit connection times.

If your application is scaled out across multiple containers, then review whether connection sharing on the database tier will also be necessary. Database Resident Connection Pooling (DRCP) is useful if the database host does not have enough memory to handle all concurrently open connections. It is a way to share the database server processes that handle user connections, regardless of the container, process, or host that is running the application.

Application High Availability

As well as utilizing a connection pool, some simple settings can be effective in improving application reliability and predictability.

An application-side sqlnet.ora file can have settings like SQLNET.OUTBOUND_CONNECT_TIMEOUT, SQLNET.RECV_TIMEOUT, or SQLNET.SEND_TIMEOUT to bound the amount of time the application will wait for responses from the database service. These aid dead-server detection, and stop the application appearing to hang while waiting on very long TCP timeouts. In cx_Oracle and node-oracledb, an application setting callTimeout is a newer alternative to the latter two options.

A sqlnet.ora SQLNET.EXPIRE_TIME setting can be used to prevent firewalls from terminating idle pooled connections. It can also aid detection of a terminated remote database server. With Oracle Client 19c, you can use EXPIRE_TIME in the Easy Connect Plus connection string.

On systems that drop (or in-line) out-of-band breaks, you may want to add DISABLE_OOB=ON to your sqlnet.ora file. This setting is automatically configured when you have 19c client libraries and database.

Other Oracle Database High Availability features can be taken advantage of by applications, such as Application Continuity.

Terminating Applications

When containers are terminated, make them clean up resources promptly. In particular, cleanly close all open connections so that the database resources are immediately available for reuse. Otherwise there may be a delay until the database's dead connection detection causes the database resources to be freed.

The code below shows how to catch signals and gracefully terminate connection pools in Node.js. It initiates shutdown of the default pool, blocking new connection requests but allowing existing users of connections to remain operational for 10 seconds before the pool is forcefully closed:

process
  .once('SIGTERM', closePoolAndExit)
  .once('SIGINT',  closePoolAndExit);

async function closePoolAndExit() {
  try {
    await oracledb.getPool().close(10);  // 10 second drain time
    console.log('Pool closed');
    process.exit(0);
  } catch(err) {
    console.error(err.message);
    process.exit(1);
  }
}

Dockerfiles provides several syntaxes for invoking applications - the Dockerfile CMD and ENTRYPOINT commands. They are discussed in the Dockerfile documentation. Signal handling can be affected by whether the application is ultimately invoked as a sub-shell and not able to receive signals, and whether the application runs as PID 1. Docker commands like docker stop or docker kill will have an impact. Review the current Docker documentation for the best practices.

For my simple examples in this series, I used CMD with an exec, but you may prefer something like ENTRYPOINT ["node", "index.js"]. You may also want to run the container with the --init option.

Reduce Round-Trips

Along with the strong recommendation to use bind variables, a main application goal is to reduce "round-trips" between the application and database. Round-trips greatly impact performance of the application. Depending on the proximity of your containers to the database, reducing network costs is critical.

For multi-row queries, tune settings like oracledb.fetchArraySize (in node-oracledb) and Cursor.arraysize (in cx_Oracle) so that more data is fetched with each round-trip. For queries that return a single row, set the options to 1 so that efficient use of memory is made.

For multi-row data inserts, look at using Array DML operations with executeMany() (in node-oracledb) or executemany() (in cx_Oracle). These can, again, reduce round-trips and give significant performance benefits.

When dealing with Oracle LOB data types, the default behavior is often to use LOB locators to stream data. This is great for very long data but is much slower than working with the data directly as a single string or buffer. If your data is less than 1 GB, and your containers have enough memory to hold the data in one piece, then you will probably want to change the default behavior. For Python see ReturnLobsAsStrings.py and for Node.js see lobselect.js.

Not directly related to round-trips, but still significant for data transfers, you may want to look at tuning the Oracle Net Session Data Unit (SDU), and the send and receive socket buffer SEND_BUF_SIZE and RECV_BUF_SIZE settings to maximize Oracle Net throughput. These, and other Oracle Net options, are discussed in the Oracle Net Services Oracle OpenWorld presentation.

Summary

Containers are valuable for developing and deploying applications. The use of images and layering lets container changes be made repeatedly and with minimal development cost. Application deployment is easy. Docker also opens up architectural possibilities, for example by making it easy to write micro services that scale out, or that communicate via message passing.

References

Join the discussion

Comments ( 1 )
  • Vijeesh Ks Sunday, May 3, 2020
    Thank u...It helped me a lot...problem with oracle is u spend money then u get the help... But u nailed it .... Thanks again...
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.