Node-oracledb: Connection Pool Cache

[dmcghan]

Hi Everyone,

I want to share some details on a new feature I'm working on called the Connection Pool Cache. Here's an overview (from future doc) with some examples:

---

Connection Pool Cache

Node-oracledb has an internal connection pool cache which can be used to simplify
some operations and facilitate sharing pools across modules.

Methods that can affect or use the connection pool cache include:

• oracledb.createPool() - can add a pool to the cache
• oracledb.getPool() - retrieves a pool from the cache (synchronous)
• oracledb.getConnection() - can use a pool in the cache to retrieve connections
• pool.close() - automatically removes the pool from the cache if needed

Pools are added to the cache if a poolAlias property is provided in the
poolAttrs object when invoking oracledb.createPool(). If the cache is
empty and a pool is created without providing a poolAlias, the pool will be
cached using an alias of 'default'. The pool with this alias is used by default
in some methods that utilize the connection pool cache, such as oracledb.getPool()
and oracledb.getConnection().

There can be multiple pools in the cache provided each pool is created with
a unique poolAlias.

Examples using the default pool

Assuming the connection pool cache is empty, following will create a new pool
and cache it using the 'default' alias:

var oracledb = require('oracledb');

oracledb.createPool (
  {
    user: 'hr',
    password: 'welcome',
    connectString: 'localhost/XE'
  },
  function(err, pool) {
    console.log(pool.alias); // default
  }
);

Once cached, the default pool can be retrieved using oracledb.getPool() without
passing the poolAlias parameter:

var oracledb = require('oracledb');
var pool = oracledb.getPool();

pool.getConnection(function(err, conn) {
   // Use connection
});

If the pool is being retrieved only to call pool.getConnection, then the shortcut
oracledb.getConnection may be used instead:

var oracledb = require('oracledb');

oracledb.getConnection(function(err, conn) {
   // Use connection
});

Examples using multiple pools

If the application needs to use more than one pool at a time, unique pool aliases
can be used when creating the pools:

var oracledb = require('oracledb');

var hrPoolPromise = oracledb.createPool({
  poolAlias: 'hr',
  users: 'hr',
  password: 'welcome',
  connectString: 'localhost/XE'
});

var shPoolPromise = oracledb.createPool({
  poolAlias: 'sh',
  user: 'sh',
  password: 'welcome',
  connectString: 'localhost/XE'
});

Promise.all([hrPoolPromise, shPoolPromise])
  .then(function(pools) {
    console.log(pools[0].alias); // hr
    console.log(pools[1].alias); // sh
  })
  .catch(function(err) {
    // handle error
  })

As before, the pools can be retrieved from the cache using oracledb.getPool().
However, the poolAlias parameter must be used to retrieve pools with aliases
other than 'default':

var oracledb = require('oracledb');
var pool = oracledb.getPool('hr'); // or 'sh'

pool.getConnection(function(err, conn) {
   // Use connection
});

The oracledb.getConnection shortcut can also be used with a poolAlias:

var oracledb = require('oracledb');

oracledb.getConnection('hr', function(err, conn) { // or 'sh'
   // Use connection
});

---

Here are some of the pros & cons of this new API:

Pros

• Simplifies sharing pools across modules (no wrapper needed)
• Simplifies getting connections from pools

Cons

• Overloading oracledb.getConnection may be confusing (see table below)

The following table shows the various signatures that can be used when invoking
getConnection and describes how the function will behave as a result.

| Signature | Description |
| --- | --- |
| oracledb.getConnection() | Gets a connection from the default pool, returns a promise. |
| oracledb.getConnection(callback) | Gets a connection from the default pool, invokes the callback. |
| oracledb.getConnection(poolAlias) | Gets a connection from the pool with the specified alias, returns a promise. |
| oracledb.getConnection(poolAlias, callback) | Gets a connection from the pool with the specified alias, invokes the callback. |
| oracledb.getConnection(connAttrs) | Creates a standalone connection, returns a promise. |
| oracledb.getConnection(connAttrs, callback) | Creates a standalone connection, invokes the callback. |

So what do you think? I welcome your thoughts!

[bchr02]

I like the implementation and the various signatures for invoking getConnection makes perfect sense and is easy to understand. Good job!

[sagiegurari]

I wanted to comment on this one, just forgot.

first of all, I think its a very good idea, with minor exceptions

• if poolAlias is not provided, never cache that pool. don't assume for the user that he wants to cache it. it will be a mess if he creates 2 pools and doesn't provide an alias for both and than does getConnection without alias and doesn't know what to except as each pool might connect to a different DB.
• if getConnection goes to the pool cache, it should always have an alias. again, I think we should not assume default here. So i think we should not have signature: oracledb.getConnection() without args.

Another thing that might really help in some cases, is having the createPoolSync function (there was a pull request for that a long time ago).
That way someone can write a module like this

module.exports = oracledb.createPoolSync({....});

and use that anywhere in the app as follows

var pool = require('./app-pool.js');
pool.getConnection({....}).then(......);

that should make things a bit simpler.

[cjbj]

Thanks @sagiegurari. Needless to say we had a lot of discussion. We will definitely "count your vote" on the API and we will re-discuss the design.

We (well..., I) had a desire to implicitly create a pool when the first connection is established - however this could be messy because credential management becomes an issue. I wanted to encourage pool use!

With the current design, our thinking was that applications most commonly have just a single pool, so oracledb.getConnection() without args would be a most used. Other options would be used by experienced programmers.

I agree that cutting down options simplifies understanding of the whole system and makes it black-and-white what is happening. This would be at the expense of a more complex common case. Would the scenario of your first bullet point be discovered during development and therefore not be a serious problem?

[sagiegurari]

ya I agree with you that most use cases would be single db pool so that might be actually help many people.

How do you suggest handling pool creation without alias if another pool is already cached with 'default' or if someone creates a pool with an alias already cached (same thing actually)? If the result would be an error in pool creation callback than i think the api suggested is good and would be understandable for users.

As for pool usage, I couldn't agree more. I'm not sure why people skip the pools and use connections directly. But based on the issues opened it seems that many just ignore pools.

[cjbj]

For backwards compat, multiple pools can be created successfully without having poolAlias set. The first one is cached using the alias 'default'. Subsequent pools (without poolAlias) are not cached.

For pools created with poolAlias set (and therefore cached using that alias), a createPool with a duplicate alias name will cause an error to be thrown and the second pool trying to use that alias is not created.

That's the plan. Comments welcome.

[sagiegurari]

so the oracledb.getConnection() would return based on which pool was created first.
and that's the problem i have with the api basically.

[dmcghan]

I think the current API does a good job of handling the following:

• Provides a simplified API for single pools (the common use case)
• Provides flexibility for multiple pools
• Maintains backward compatibility

Folks currently using 1 pool can start to adopt the new API without any issues. For folks currently using more than 1 pool, they can upgrade safely as they wouldn't be using the pool cache without updating their code. The recommendation for those folks would be to specify poolAliases in the createPool statements and then use those aliases in calls to getPool and getConnection.

That should prevent any issues and confusion, no?

Are there any recommendations to improve the API while still meeting those goals?

[sagiegurari]

I agree that 1 pool is the most common use case, so ya the api is clear for that.
I guess good examples in the docs should resolve any issues for multi pools.
I know that we use multiple dbs so I have a concern, but guess it is more rare.

[cjbj]

This is "live" now in node-oracledb 1.11 - take a look: https://blogs.oracle.com/opal/entry/node_oracledb_1_11_released

[Eduard0x6F]

Thanks so much! This helped me solve some problems on my code.

[cjbj]

For reference, the doc is at https://oracle.github.io/node-oracledb/doc/api.html#connpoolcache