Connection Pooling and other Tips for CUBRID Node.js Driver users

In this tutorial, we will go through some more advanced topics:

• Using a connection pool
• Queries with parameters
• Some driver usage tips

But before we start, let’s take a quick look at what has happened since the first CUBRID Node.js 1.0 driver was released in October this year.

The big news is that we have just released a new node-cubrid 1.1 driver and the most important update for this version is that it adds compatibility with two new recent CUBRID engine releases:

• CUBRID 8.4.3 with Database Sharding and API level Load Balancing support
• CUBRID 9.0 beta with 3x Performance Increase and Database Sharding support

At the same time, the driver keeps full backward compatibility with the previous 8.4.1 engine release – so you can use any of the latest engine releases while using a single driver version!

And if you wonder what are the particular differences in the TCP/IP protocol between the various versions and how we implemented support for the different protocols, please take a look at the driver code, and in particular, at the TCP/IP communication packets definitions.

Using a connection pool

Connection pooling is a fundamental concept in software applications development, especially when combined with a database server environment (Figure 1). Using a connection pool provides many advantages to a consumer application, for example:

• Control the server resources usage
• Speed up things (connecting to a database is usually a “heavy” long-time operation)
• Allow for “parallel” tasks execution



Figure 1: Database Connection Pooling.

One special thing to note is that the TCP/IP database communication protocol is synchronous and not parallel-execution aware/capable. This means that if you want to execute two or more queries truly in parallel, you will need to use multiple database connections and the best solution is definitely to go on with a connection pool implementation.

The current release of the CUBRID Node.js driver does not feature (yet) a built-in connections pool, but it supports the best Node.js connection pool application available today – the node-pool.

You can find code example on using node-pool with the driver in Common uses of CUBRID Node.js API with examples blog. In this blog you can learn how to use node-pool to:

• Reuse a database connection
• Limit the number of concurrent connections

In this blog you will find not only a node-pool usage example, but much more! Also, you will find many code examples which will get you started very fast with the CUBRID Node.js driver!

For this tutorial, let’s use a different Node.js connection pool implementation – the node-pooling. And for this example, let’s focus on different goals:

• Limited the number of concurrent connections
• Not keep a connection opened indefinitely; open a connection only when needed.

So how do we use node-pooling with node-cubrid?

First of all – let’s define a pool with:

• A maximum of 2 “active” clients
• A maximum idle time of 30 sec.
• An interval to check for idle clients of 1 sec.
• create and destroy functions which generate a client ID and logs these events

  var conn_idx = 1;
   
  var pool = pooling.createPool({
      checkInterval: 1 * 1000,
      max: 2,
      maxIdleTime: 30*1000,
      name : 'my pool',
      create : function create(callback) {
          var client = new EventEmitter();
          client.id = conn_idx++;
          Helpers.logInfo('Creating pool client id: ' + client.id);
           
          return callback(null, client);
      },
      destroy : function destroy(client) {
          Helpers.logInfo('Destroyed pool client id: ' + client.id);
          client.was = client.id;
          client.id = -1;
      }
  });

Notice that the pool definition doesn't mention anything about CUBRID database connection. Since it is our goal to open a connection only when necessary, we will establish a connection in the actual code as shown below.

Now we will request 3 client connections to the CUBRID demodb database, each one requesting a query execution to a different table: code, nation, game, and, for each one, display the number of rows returned in the result set.

Because we do not want to keep the connection open, the code will close the database connection when the query execution is completed. For simplicity, we will list here the code for just one of these client requests – the other two are absolutely similar:

pool.acquire(function (err, client) {
    var CUBRIDClient = new CUBRIDConnection('localhost', 33000, 'public', '', 'demodb');
 
    CUBRIDClient.connect(function (err) {
        if (err === null) {
            Helpers.logInfo('Database connection acquired for pool client id: ' + client.id);
            Helpers.logInfo('Executing query: select * from code');
 
            CUBRIDClient.query("select * from code", function (err, result, queryHandle) {
                if (err) {
                    Helpers.logError(err);
                }
                else {
                    Helpers.logInfo('Query results rows count for pool client id: ' + client.id + ' is: ' + Result2Array.TotalRowsCount(result));
 
                    CUBRIDClient.closeQuery(queryHandle, function (err) {
                        if (err) {
                            Helpers.logError(err);
                        }
                        else {
                            Helpers.logInfo('Query closed for pool client id: ' + client.id);
 
                            CUBRIDClient.close(function (err) {
                                if (err) {
                                    Helpers.logError(err);
                                }
                                else {
                                    Helpers.logInfo('Connection closed for pool client id: ' + client.id);
 
                                    Helpers.logInfo('Waiting some time before releasing the pool client id: ' + client.id + '...');
 
                                    setTimeout(function () {
                                        Helpers.logInfo('Releasing pool client id: ' + client.id);
                                        pool.release(client);
                                    }, 10 * 1000);
                                }
                            });
                        }
                    });
                }
            });
        }
    });
});

And this is the execution result output, which shows how only 2 clients are initially created, and when the first client job is done, it is “reused” to execute the third query:

Figure 2: Connection pooling using node-pooling.

As you can see, it’s not difficult at all to combine node-cubrid with node-pooling (or node-pool) in order to achieve a connection pool implementation working with a CUBRID database application backend.

One more thing to mention – we are considering implementing a built-in connection pool in the future driver releases to simplify even more the CUBRID Node.js driver usage. Please let us know your feedback and suggestions!

Queries with parameters

One of the important features a driver needs to implement is giving the users the ability to run queries using parameters. Please note that using parameters not only helps to support flexible coding, but also helps with the server side queries execution optimization (see Queries Execution Plan).

The node-cubrid driver implements two functions that support parameters:

Usually, when we discuss about query parameters, we refer only to parameters specified in query (WHERE) conditions – for example:

CUBRID Node.js driver takes query parameters concept one step further!

You can specify as parameters anything you want, including table and column names. For example, you can do this:

1	SELECT* FROM? WHERE? = ? OR? LIKE?

Obviously, so much flexibility comes with a price, and the “price” is that you will need to specify the correct delimiters for each parameter, because the SQL delimiters can be different, from one data type to another:

For example, let’s consider the following very generic query with parameters:

And let’s suppose what we want to execute is:

Figure 3: A sample SQL and query results in CUBRID Manager.

Then, the parameters delimiters we need to use are:

And this means that this is how we will call the node-cubridqueryWithParams function:

var sql = 'SELECT * FROM ? WHERE ? LIKE ? AND LENGTH(?) > ?';
 
var arrValues = ['nation', 'code', 'A%', 'capital', '5'];
 
var arrDelimiters = ['`', '', '\'', '', ''];
 
function errorHandler(err) {
    throw err.message;
}
 
CUBRIDClient.connect(function (err) {
    if (err) {
        errorHandler(err);
    }
    else {
        CUBRIDClient.queryWithParams(sql, arrValues, arrDelimiters, function (err, result, queryHandle) {
            if (err) {
                errorHandler(err);
            }
            else {
                assert(Result2Array.TotalRowsCount(result) === 12);
 
                CUBRIDClient.closeQuery(queryHandle, function (err) {
                    ...

The execution result will be:

Figure 4: Results of executing a query with parameters in node-cubrid.

Remember – When using SQL statements with parameters, you need to:

• Use ? for each parameter placeholder
• Specify the delimiters for each parameter as a JavaScript array in sequential order.

As a side note, you can also specify delimiters as hard-coded in the SQL query definitions – for example:

If you choose to do this, then the parameters you need to provide will obviously be always empty string values.

Some driver usage tips

Querying database schema

All CUBRID drivers provide, in various degrees, support for querying a database schema, which is giving the client the ability to get information about the CUBRID database objects:

• Tables
• Views
• Stored procedures
• Indexes
• Etc.

In node-cubrid, so far we are providing a built-in support for:

• Tables
• Views

And this is how you do it:

CUBRIDClient.getSchema(CUBRIDClient.SCHEMA_TABLE, callback);
CUBRIDClient.getSchema(CUBRIDClient.SCHEMA_VIEW, callback);

The results are:

Figure 5: SCHEMA_TABLE results.

Figure 6: SCHEMA_VIEW results.

However, you can easily extend the built-in support by simply querying the schema catalog tables and views! The schema catalog will “tell” you about columns, users etc. All you need to do is to write appropriate SELECT queries, and the CUBRID ADO.NET Driver implementation will show you how to do it!

Tip: There is a nice open-source tool available for CUBRID – CUBRID Database Schema – which will show you, in a very friendly way, a CUBRID database schema content. An online demo is available at http://cubdbsch.cubrid.org/.

Stay tuned for the next driver release – 2.0 – which will feature extended schema support:

• Tables and Views columns/attributes schema
• Users and users’ access rights
• Indexes and foreign keys
• etc.

Querying without explicit connect

Did you know that you can do this?

var CUBRIDConnection = require('./node_modules/node-cubrid/src/CUBRIDConnection');
 
var CUBRIDClient = new CUBRIDConnection();
 
CUBRIDClient.query('select * from nation', function (err, result, queryHandle) {
....

As you can see, there is no connect() call – just direct query. And it works! Why?

The reason is that the driver automatically opens a connection for you in the background with default parameters, if you have not explicitly done that yourself. This means that by simply tweaking the default connect values (just edit the node-cubrid/src/CUBRIDConnection.js file) you can skip the connect() prerequisite and directly execute queries (and not only queries, but batch execute also works!). With default configurations it will connect to demodb database on localhost with dba user and an empty password.

BTW, if you call connection close() before closing the open queries using closeQuery(), don’t worry! The driver automatically takes care of closing all open query handles.

Getting the LAST INSERT ID

One of the most common patterns in database applications is to get the LAST INSERT ID, after performing database INSERT statement(s).

Depending on the driver used, there are various ways to support this functionality. For example, if you used CUBRID PHP Driver, you would use the function called cubrid_insert_id(). But how do you do this in node-cubrid?

node-cubrid 1.0 version does not provide yet a dedicated support for retrieving the LAST INSERT ID value(s). However, there is a quite simple workaround – use the built-in SQL function - LAST_INSERT_ID(). Let’s see how to do that:

CUBRIDClient.batchExecuteNoQuery([
    'drop table if exists node_test',
    'create table node_test(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY)'
], function (err) {
    CUBRIDClient.batchExecuteNoQuery([
        'insert into node_test values(NULL)'
    ], function (err) {
        CUBRIDClient.query('select LAST_INSERT_ID()', function (err, result, queryHandle) {
            ...

Links & Resources

• CUBRID Node.js driver home page
• node-cubrid documentation
• node-cubrid Online Wiki
• node-cubrid tutorials and examples
• General Node.js resources
• http://howtonode.org/
• http://nodejs.org/community/
• http://en.wikipedia.org/wiki/Node.js
• CUBRID API Blog