Performance Zone is brought to you in partnership with:

I am a developer and project manager of CUBRID open source database. I often interact with CUBRID user community answering to their questions and helping them resolve CUBRID related issues. Beside the CUBRID project, I contribute to various open source projects like Hibernate, CodeIgniter, Yii, etc. Personally I am very interested in big data, scalability, and single page real-time Web apps. I enjoy reading (a lot!) and writing tech articles. I have written over one hundred blogs at http://www.cubrid.org/blog and even more tutorials. Esen is a DZone MVB and is not an employee of DZone and has posted 43 posts at DZone. You can read more from them at their website. View Full User Profile

Common Uses of CUBRID Node.js API with Examples

12.11.2012
| 2369 views |
  • submit to reddit

Recently Node.js has become one of the most favorite tools developers choose to create new Web services or network applications. Some of the reasons are its event-driven and non-blocking I/O architecture which allow developers to create very lightweight, efficient and highly scalable real-time applications that run across distributed servers.

Node.js has been widely adopted by individual developers as well as large corporations such as LinkedIn, Yahoo!, Microsoft, and others. It has become so popular that developers have started writing and publishing so called Node Packaged Modules which further extend the functionality of the Node.js platform. In fact, there are over 17,000 registered modules at https://npmjs.org/ which have been downloaded over 12,000,000 times during the last month only. That popular the Node.js platform is.

node-cubrid

To allow Node.js developers to connect and work with CUBRID Database Server, we have developed the node-cubrid module and published it at NPM.

node-cubrid provides a set of APIs to connect to and query CUBRID databases. Besides the database specific APIs, the module also supplies several helper APIs which are useful to sanitize and validate user input values, format and parameterize SQL statements.

Compatibility

node-cubrid has been developed in pure JavaScript, therefore it has no dependency on any external library. This allows users to develop CUBRID Database based Node.js applications on any Node.js compatible platform such as Linux, Mac OS X, and Windows. For the same reason node-cubrid is designed to work with any version of CUBRID RDBMS. However, for the time being it has been tested only with CUBRID 8.4.1.

This is different from other CUBRID drivers such as PHP/PDO, Python, Perl, Ruby, OLEDB, and ODBC which have dynamic dependency on CUBRID C Internface (CCI). Since CUBRID is available only on Linux and Windows OS, these drivers are also limited to these platforms as well as specific CUBRID versions. However, CUBRID’s Node.js as well as ADO.NET drivers do not have any dependency, therefore can be used on any platform where that particular run-time environment is capable of running on.

Installation

Installing and using node-cubrid is easy. To install, one has to initiate npm install command with node-cubrid module name as an argument in the directory where a Node.js application is located.

npm install node-cubrid

This will install the latest version available at https://npmjs.org/. Once installed, the module can be accessed by requiring the node-cubrid module:

var CUBRID = require('node-cubrid');

The node-cubrid module exports the following properties and functions:

  • Helpers: an object which provides a set of helper functions.
  • Result2Array: an object which provides functions to convert DB result sets into JS arrays.
  • createDefaultCUBRIDDemodbConnection(): a function which returns a connection object to work with a local demodb database.
  • createCUBRIDConnection(): a function which returns a connection object to work with a user defined CUBRID host and database.

Request flow in node-cubrid

The request flow in node-cubrid module looks as illustrated below.

Because node-cubrid is developed to take the full advantage of JavaScript and Node.js programming, when executing a SQL statement in node-cubrid, developers need to listen for an EVENT_QUERY_DATA_AVAILABLE and EVENT_ERROR events, or provide a callback function which will be called once there is a response from the server.

When the request is sent to the server, CUBRID executes it, and returns the response, which can be either a query result set, or the error code. It is by design that CUBRID does not return any identification about the request sender. In other words, in order to associate the response with a request, the driver has to have only one active request which can be the only owner of this response.

For this reason, if a developer wants to execute several queries, they must execute them one after another, i.e. sequentially, NOT in parallel. This is how the communication between the driver and the server is implemented in CUBRID and many other database systems.

If there is a vital need to run queries in parallel, developers can use connection pooling modules. We will explain this technique in the examples below.

Using node-cubrid

Establishing a connection

First, user establishes a connection with a CUBRID server by providing a host name (default: ‘localhost’), the broker port (default: 33000), database username (default: ‘public’), password (default: empty string), and finally the database name (default: ‘demodb’).

conn.connect(function (err) {
    if (err) {
        throw err.message;
    }
    else{
        console.log('connection is established');
        
        conn.close(function () {
            console.log('connection is closed');
        });
    }
});

The above code illustrates a callback style when a function is passed as an argument to a connect() API which is called if the connection has been successfully established. Alternatively, developers can write applications based on an event-based coding style. For example, the above code can be rewritten as:

conn.connect();

conn.on(conn.EVENT_ERROR, function (err) {
    throw err.message;
});

conn.on(conn.EVENT_CONNECTED, function () {
    // connection is established
    conn.close();
});

conn.on(conn.EVENT_CONNECTION_CLOSED, function () {
    // connection is closed
});

If you prefer the event-based coding style, refer to the Driver Event model wiki page to learn more about other events node-cubrid emits for certain API calls.

Executing queries

Once connected, users can start executing SQL queries. There are several APIs you can use to execute queries in node-cubrid:

  1. query(sql, callback);
  2. queryWithParams(sql, arrParamsValues, arrDelimiters, callback);
  3. execute(sql, callback);
  4. executeWithParams(sql, arrParamsValues, arrDelimiters, callback);
  5. batchExecuteNoQuery(sqls, callback);

Eventually all of the above APIs execute given SQL queries. The difference is that query* APIs return data records while *execute* APIs do not return any record. So basically, you would use query* with SELECT queries while *execute* with INSERT/UPDATE/DELETE queries.

Executing queries with parameters

queryWithParams() and executeWithParams() APIs allow developers to bind values to parameterized SQL queries. Though “binding” in node-cubrid does not infer a communication with the server, the module merely replaces all ? placeholders with the given arrParamsValues values which are wrapped with arrDelimiters delimeters. Thus, you can bind values as follows:

var code = 15214,
    sql = 'SELECT * FROM athlete WHERE code = ?';

conn.queryWithParams(sql, [code], [], function (err, result, queryHandle) {
     // check the error first then use the result
});

The same can be done with non-result SQL statements like:

var host_year = 2008,
    host_nation = 'China',
    host_city = 'Beijing',
    opening_date = '08-08-2008',
    closing_date = '08-24-2008',
    sql = 'INSERT INTO olympic (host_year, host_nation, host_city, opening_date, closing_date) VALUES (?, ?, ?, ?, ?)';

conn.executeWithParams(sql, [host_year, host_nation, host_city, opening_date, closing_date], ["", "'", "'", "'", "'"], function (err) {
     // check the error first
});

If you need to insert multiple records at once in the form of VALUES (...), (...), ..., you can use helper functions to manually populate ? placeholders with values as shown below.

var sql = 'INSERT INTO olympic (host_year, host_nation, host_city, opening_date, closing_date) VALUES ',
  partialSQL = '(?, ?, ?, ?, ?)',
  data = [{...}, {...}, {...}],
  values = [];

data.forEach(function (r) {
  var valuesSQL = CUBRID.Helpers._sqlFormat(
    partialSQL,
    [r.host_year, r.host_nation, r.host_city, r.opening_date, r.closing_date],
    ["", "'", "'", "'", "'"]
  );
  
  values.push(valuesSQL);
});

sql += values.join(',');

conn.execute(sql, function (err) {
     // check the error first
});

Fetching more data

Sometimes, when quering a database, it happens that the results set is quite large that it has to be retrieve in multiple steps. Below you can see how to keep fetching more data until all data is retrieved.

var sql = 'SELECT * FROM participant';

conn.query(sql, function (err, result, queryHandle) {
  // assuming no error is returned
// the following outputs 916
    console.log(CUBRID.Result2Array.TotalRowsCount(result));
    
    function outputResults (err, result, queryHandle) {
      if (result) {
        // 309 records are in the first results set
          // 315 records are in the second results set
          // 292 records are in the third results set
            console.log(CUBRID.Result2Array.RowsArray(result).length);

            // try to fetch more data
            conn.fetch(queryHandle, outputResults);
        }
        else{
          // no more result, close this query handle
            conn.closeQuery(queryHandle, function (err) {
                conn.close(function () {
                    console.log('connection closed');
                });
            });
        }
    }
    
    outputResults(err, result, queryHandle);
});

The above are the APIs developers will use most of the time.

Using a connection pool manager

node-cubrid does not provide connection pool manager. However, at some point developers may want to execute multiple queries at the same time. In such cases, users can use generic-pool, also known as node-pool, as a pool manager for CUBRID connections.

To install generic-pool type the following in the terminal.

npm install generic-pool

The following example shows how to configure generic-pool to create and destroy CUBRID connections.

var poolModule = require('generic-pool');
var pool = poolModule.Pool({
    name     : 'CUBRID',
    // you can limit this pool to create maximum 10 connections
    max      : 10,
    // destroy the connection if it's idle for 30 seconds
    idleTimeoutMillis : 30000,
    log : true ,
    create   : function(callback) {
        var conn = CUBRID.createCUBRIDConnection('localhost', 33000, 'dba', 'password', 'demodb');
        conn.connect(function (err) {
          callback(err, conn);
        });
    },
    destroy  : function(con) {
      conn.close();
    }
});

Then, the connection pool manager can be used in your application as follows.

pool.acquire(function(err, conn) {
    if (err) {
        // handle error - this is generally the err from your
        // factory.create function  
    }
    else {
        conn.query("select * from foo", function() {
            // once done querying, return the object back to pool
            pool.release(conn);
        });
    }
});

Using node-cubrid with async module

node-cubrid module provides ActionQueuehelper module which provides the waterfall functionality of async module. You can use ActionQueue as follows:

CUBRID.ActionQueue.enqueue([
    function (cb) {
      conn.connect(cb);
    },

    function (cb) {
      conn.getEngineVersion(cb);
    },

    function (engineVersion, cb) {
      console.log('Engine version is: ' + engineVersion);
      conn.query('select * from code', cb);
    },

    function (result, queryHandle, cb) {
      console.log('Query result rows count: ' + Result2Array.TotalRowsCount(result));
      console.log('Query results:');
      var arr = Result2Array.RowsArray(result);

      for (var k = 0; k < arr.length; k++) {
        console.log(arr[k].toString());
      }
      
      conn.closeQuery(queryHandle, cb);
      console.log('Query closed.');
    },

    function (cb) {
      conn.close(cb);
      console.log('Connection closed.');
    }
  ],

  function (err) {
    if (err == null) {
      console.log('Program closed.');
    } else {
      throw err.message;
    }
  }
);

The above is identical to async’s waterfall function shown below.

async.waterfall([
    function (cb) {
      conn.connect(cb);
    },

    function (cb) {
      conn.getEngineVersion(cb);
    },

    function (engineVersion, cb) {
      console.log('Engine version is: ' + engineVersion);
      conn.query('select * from code', cb);
    },

    function (result, queryHandle, cb) {
      console.log('Query result rows count: ' + Result2Array.TotalRowsCount(result));
      console.log('Query results:');
      var arr = Result2Array.RowsArray(result);
      
      for (var k = 0; k < arr.length; k++) {
        console.log(arr[k].toString());
      }
      
      conn.closeQuery(queryHandle, cb);
      console.log('Query closed.');
    },

    function (cb) {
      conn.close(cb);
      console.log('Connection closed.');
    }
  ],

  function (err) {
    if (err == null) {
      console.log('Program closed.');
    } else {
      throw err.message;
    }
  }
);

Roadmap

At the time of writing this artile node-cubrid version 1.0.1 stable was the latest release. In the future version we plan to improve node-cubrid a lot to make it more convenient for developers to code. For example, developers will be able to bind values with a single object parameter. Its properties and their values will serve as column names and values in the SQL statement. Very convenient which also increases the code readability.

We will also add new APIs to retrieve values set for server configuration parameters. As of version 1.0.1 node-cubrid does not return the number of affected rows after having executed write queries. This will also be implemented. In addition to this, there will be APIs to obtain table schema information which will be very benefitial for ORM developers.

Besides these, in the upcoming version node-cubrid will allow to connect to a CUBRID Server using a connection URL, the same API we already provide in all other drivers. This will allow to pass a list of alternative hosts for broker level failover, specify the query timeout duration, etc.

We plan to add many new functionality to node-cubrid. If you have a specific request, please create an issue in CUBRID JIRA issue tracker, or let us know by IRC, Twitter, or Facebook. We will be glad to review your request. If you have specific questions about CUBRID or node-cubrid module, you can ask at our Q&A site.




Published at DZone with permission of Esen Sagynov, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)