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

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

This is the second in a 2 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 operating multiple Docker containers.

To hold this blog series to a manageable length and focussed on applications, it doesn’t cover database setup. If you are interested in also operating 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 proven 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 proven 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 counseled. Credential management is mentioned later.

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

$ sudo docker construct -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 operating 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 proven in Part 1, you can use instructions 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 entry 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 pockets for security. Oracle wallets may contain database credentials, or can enable connections over TLS.

Oracle Cloud databases provide wallets that are mandatory for entry. These configure TLS, but don’t contain usernames or passwords. Download the pockets from Oracle Cloud and unzip into a host directory such as /OracleCloud/pockets/. 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/pockets:/usr/lib/oracle/19.5/client64/lib/community/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 proven earlier, your -v option might be:

-v /OracleCloud/pockets:/opt/oracle/instantclient_19_5/community/admin:Z,ro

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

If the Cloud pockets 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, operating containers are attached to the default ‘bridge’ community driver. Bridge networks apply to containers operating on the same Docker daemon host.

User-defined bridges are counseled. The benefits include application isolation, automated 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 community 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 begins 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 community. Below, a container cjones/ic19sp containing SQL*Plus is initiated. 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/[email protected]/orclpdb1

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

$ sqlplus scott/[email protected]/orclpdb1

Docker Host Networks

Another community 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 initiated 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/[email protected]/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 shifting 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.

Part 2 Docker for Oracle Database Applications in Nodejs and

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 lengthy operating 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-keen 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 operating the application.

1617193991 24 Part 2 Docker for Oracle Database Applications in Nodejs and

Application High Availability

As well as utilizing a connection pool, some simple settings can be efficient 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 lengthy TCP timeouts. In cx_Oracle and node-oracledb, an application setting callTimeout is a newer alternative to the latter 2 options.

A sqlnet.ora SQLNET.EXPIRE_TIME setting can be used to forestall 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 routinely 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 sources promptly. In particular, cleanly close all open connections so that the database sources are immediately available for reuse. Otherwise there may be a delay until the database’s dead connection detection causes the database sources to be freed.

The code below shows how to catch indicators 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 instructions. They are mentioned 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 indicators, and whether the application runs as PID 1. Docker instructions 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-journeys” between the application and database. Round-journeys greatly impact performance of the application. Depending on the proximity of your containers to the database, reducing community 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, gaze at using Array DML operations with executeMany() (in node-oracledb) or executemany() (in cx_Oracle). These can, again, reduce round-journeys 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 worthy for very lengthy data but is much slower than working with the data immediately 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 1 piece, then you will probably want to change the default behavior. For Python see ReturnLobsAsStrings.py and for Node.js see lobselect.js.

Not immediately related to round-journeys, but nonetheless significant for data transfers, you may want to gaze 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 mentioned 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

.

Most Popular