This is a Node.js driver for CUBRID open-source relational database. node-cubrid is implemented in 100% JavaScript with no external dependency (except the development dependencies). Besides the database specific APIs, the module supplies several helper APIs which are useful to sanitize and validate user input values, format and parametrize SQL statements, etc.
Table of Contents
- Key features
- CHANGELOG
- Installation
- Request flow in node-cubrid
- API Documentation
- More examples
- Running tests
- What's next
- Authors and Contributors
- Special thanks
- Full compatibility with CUBRID 8.4.1+ releases.
- Rich database support: Connect, Query, Fetch, Execute, Execute in batch, Commit, Rollback, etc.
- Support for queries queueing.
- Support for database schema.
- Support for database parameters and transactions.
- Support for LOB (Binary and Character) objects.
- Support for ENUM data types since CUBRID 9+.
- Fully implements the event emitter.
- Extensive tests suite (90% code coverage).
- Full
Promisesupport sincev3.0.0.
Refer to https://github.com/CUBRID/node-cubrid/releases tab.
Since v3.0.0 the driver requires Node >=v4. If you are on an older version of Node,
you can use the previous node-cubrid v2.2.5.
npm install node-cubrid
This will install the latest version available at https:* www.npmjs.com/package/node-cubrid.
Once installed, the module can be accessed by requiring the node-cubrid module:
const CUBRID = require('node-cubrid');
The node-cubrid module exports the following properties and functions:
Helpers: an object which provides a set of helper functions.createConnection()(aliascreateCUBRIDConnection()): a function which returns a new client connection object to work with a user defined CUBRID host and database.createDefaultCUBRIDDemodbConnection(): a function which returns a connection object to work with a local demodb database.
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 most other database systems including MySQL.
If there is a vital need to run queries in parallel, developers can use connection pooling modules. An example with this technique is provided below.
/*
* `createCUBRIDConnection()` function accepts either an object or a list of
* connection parameters. The following list of parameters are supported:
* 1. `hosts`: Can be either:
* - a string representing an IP or a domain name of the CUBRID host
* (without the `http://` part)
* - (since 3.0.0) an array of strings like
* `['host1[:port1]', 'host2[:port2]', ...]`.
*
* When a custom `port` is not specified, the driver will use the
* default `port` specified by the user when creating a connection.
* When a user has not specified the default `port`, it defaults to
* `33000`.
*
* Defaults to `localhost`.
* 2. `port`: a port CUBRID is listening to. Defaults to `33000`.
* 3. `user`: the database username. Defaults to `public`.
* 4. `password`: the database user password. Defaults to an empty string.
* 5. `database`: the name of a database to connect to. Default to `demodb`.
* 7. `connectionTimeout`: the timeout value in milliseconds for the connection.
* If `connectionTimeout = 0`, it will wait until the network socket times out
* itself. Defaults to `0`.
* 8. `maxConnectionRetryCount`: the number of times the connection needs to
* be retried in case of failure. Defaults to `1`. Since `3.0.0`.
* 9. `logger`: a custom logger that implements at least `debug` and `info`
* functions. Defaults to noop (nothing will be logged). For more details
* refer to **Logging** section below. Since `3.0.0`.
*/
// All arguments are optional in which case default values will be set.
var client = CUBRID.createCUBRIDConnection(host, port, user, password, database, connectionTimeout);
* Alias function since version 2.1.0.
var client = CUBRID.createConnection(host, port, user, password, database, connectionTimeout);
// Alternatively, an object of parameters can be passed. Since version 2.1.0.
var client = CUBRID.createConnection(paramsObject);
The following example shows how to create a client by providing an object of connection parameters.
const client = CUBRID.createConnection({
host: host,
port: port,
user: user,
password: password,
database: database,
connectionTimeout: connectionTimeout,
maxConnectionRetryCount: maxConnectionRetryCount,
logger: logger
});
// callback(err) function receives one arguments: the error message if any.
const promise = client.connect(callback);
The code below illustrates a callback style when a function is passed as an argument
to a connect() API which is called after the module receives a response from CUBRID.
const CUBRID = require('node-cubrid');
const dbConf = {
host: 'localhost',
port: 33000,
user: 'public'
password: '',
database: 'demodb'
};
const client = CUBRID.createConnection(dbConf);
client.connect(function (err) {
if (err) {
throw err;
} else {
console.log('connection is established');
client.close(function (err) {
if (err) {
throw err;
} else {
console.log('connection is closed');
}
});
}
});
Alternatively, a Promise style is supported. For example, the above code can be rewritten as:
client
.connect()
.then(() => {
console.log('connection is established');
return client.close();
})
.catch(err => {
throw err;
});
node-cubrid performs an implicit connection whenever necessary. When you execute a query
on a client without explicitly establishing a connection by calling client.connect(), the
driver will automatically establish a connection before executing your query.
There can be several reasons for a connection to fail:
-
Connection timeout:
-
when the host does not respond within the specified time larger than
0, you will receive the following error message:{ [Error: connect ETIMEDOUT] } -
when no timeout value is set or its value is
0, the following timeout error is emitted by the underlying network socket.{ [Error: connect ETIMEDOUT] code: 'ETIMEDOUT', errno: 'ETIMEDOUT', syscall: 'connect' }
-
-
Incorrect hostname:
{ [Error: getaddrinfo ENOTFOUND] code: 'ENOTFOUND', errno: 'ENOTFOUND', syscall: 'getaddrinfo' } -
Incorrect port:
-
When you try to connect to a port listened by an active service other than CUBRID or if a firewall refuses the connection, you will see the following error message:
{ [Error: connect ECONNREFUSED] code: 'ECONNREFUSED', errno: 'ECONNREFUSED', syscall: 'connect' } -
If the port is not listened by any service, then you will see the Connection timeout error as in the first case.
-
The last type of error message you would receive if an incorrect port is provided is:
{ [Error: read ECONNRESET] code: 'ECONNRESET', errno: 'ECONNRESET', syscall: 'read' }
-
// Both functions are available since version 2.0.0.
const timeoutInMilliseconds = client.getConnectionTimeout();
// Set connection timeout in milliseconds.
client.setConnectionTimeout(2000);
// Alternatively, set the connection timeout value at client creation time.
// Available since version 2.1.0.
var client = CUBRID.createConnection(host, port, user, password, database, connectionTimeout);
One of the requests we have got for the 2.0 driver release was to implement a connection timeout feature. Simply said - wait for the connection to the database to complete within the specified number of seconds and eventually throw an error if the timeout occurs.
Obviously, the key thing here was to set the connection timeout at the Node.js socket connection layer level (and not on the consumer level):
this._socket = Net.createConnection(hostInfo.port, hostInfo.host);
this._socket.setNoDelay(true);
this._socket.setTimeout(this.getConnectionTimeout());
In node-cubrid by default the connection timeout value is set to 0, i.e.
the driver will wait long enough until the underlying network socket times out itself.
In this case, according to our observations, the timeout event is emitted in about
75 seconds (purely observational point).
So, if you want or expect the connection to timeout within the specified time, then manually set the timeout value as shown below.
const client = new CUBRIDConnection(dbConf.host, dbConf.port, dbConf.user, dbConf.password, dbConf.database, /* connectionTimeout */ 2000);
// Or at runtime.
client.setConnectionTimeout(2000);
client.connect(function (err) {
if (err) {
console.log(err);
}
client.close();
});
As you see, the timeout is specified in milliseconds 2000, which is 2 seconds.
After 2 seconds, the script will timeout with an error like:
{ [Error: connect ETIMEDOUT] }
// Both functions are available since version 2.0.0.
client.getDatabaseParameter(paramType);
client.setDatabaseParameter(paramType, paramValue);
After connecting to a database, a user can override some global session parameters that will control the behavior queries being executed. For example, isolation level of transactions, the auto-commit behavior, etc.
The complete list of these CUBRID database parameters is defined in the
Constants.js file:
/**
* Define CUBRID Database parameters constants
*/
exports.CCIDbParam = {
CCI_PARAM_ISOLATION_LEVEL : 1,
CCI_PARAM_LOCK_TIMEOUT : 2,
CCI_PARAM_MAX_STRING_LENGTH : 3,
CCI_PARAM_AUTO_COMMIT : 4
};
For each parameter, the CUBRID communication protocol implements a dedicated support for GET and SET operations. Therefore, in order to manipulate them, also a dedicate functionality was needed in the driver and this is what we did in the 2.0 release.
Please note one exception - the CCI_PARAM_MAX_STRING_LENGTH parameter cannot be set
programmatically from code as it is a CUBRID Broker parameter and the client can only query
its current value.
Let’s see some examples. First, let set the value of the ISOLATION_LEVEL parameter:
const CAS = require('./node_modules/node-cubrid/src/constants/CASConstants');
client.connect(function (err) {
// handle error, then...
client.setDatabaseParameter(CAS.CCIDbParam.CCI_PARAM_ISOLATION_LEVEL, CAS.CUBRIDIsolationLevel.TRAN_REP_CLASS_COMMIT_INSTANCE, function (err) {
// handle error
CUBRIDClient.close(function (err) {
// …
});
});
});
And let’s see how we can retrieve the value of the LOCK_TIMEOUT parameter for the current session:
client.connect(function (err) {
client.getDatabaseParameter(CAS.CCIDbParam.CCI_PARAM_LOCK_TIMEOUT, function (err, value) {
// handle error, then...
console.log('LOCK_TIMEOUT is: %s', value);
CUBRIDClient.close(function (err) {
// ...
});
});
});
The output result is:
LOCK_TIMEOUT is: -1
Note: The same value can be obtained also from CUBRID Manager Client:
If you need to change the default values for these parameters, it is highly recommended
to do it immediately after connect ().
// Callback style.
client.query(sql, callback);
client.query(sql, params, callback);
/*
* 1. `sql`: a string representation of a single READ query. Required.
* 2. `params` is an array of parameter values or the value itself
* which a user wants to bind instead of `?` placeholders
* in the `sql` query. If no placeholder is found, the `sql`
* will not be modified. This argument is optional. When
* omitted, `sql` will be sent to the server unmodified. This
* `params` argument is available since version 2.1.0.
* 3. `callback` is a function which will be invoked when the query
* finishes executing. Optional. The `callback` function
* can be omitted in which case users need to handle the
* return value of the `Promise`.
*/
/*
* The `callback(err, result, queryHandle)` function accepts three arguments.
* 1. `err`: an error object if any.
* 2. `result`: a query result object which has the following properties:
* - `ColumnDataTypes`: an array of strings that represent the column data type.
* - `ColumnNames`: an array of strings that represent the column names.
* - `ColumnValues`: an array of arrays that represent rows of records
* each row having one or more columns. Here each column represents a value
* of that column.
* - `RowsCount`: total number of records that match the provided SQL query.
* **Note** that not all records may have been fetched.
* 3. `queryHandle`: an integer ID for the query handle. Used to fetch more data
* or close the query statement in order to release the memory being kept
* on the CUBRID server side.
*/
// Promise style.
const promise = client.query(sql);
const promise = client.query(sql, params);
/*
* A `promise` is resolved with a single `response` object that has the following
* properties described above:
* - result
* - queryHandle
*
* A `promise` is rejected with a single instance of `Error` object.
*/
/*
* Queries queueing: calling `query()` multiple times will result in queuing
* the queries which will be executed sequentially one after another.
*/
client.query(sql);
client.query(sql, params);
// Under the hood, `query()` and other functions that accept `params` all
// call `Helpers._sqlFormat()` function to perform the actual formatting.
// When multiple queries are executed one after another without
// waiting for a callback, the queries
// will be queued and executed sequentially.
Here is an example which executes a simple SELECT query.
const CUBRID = require('node-cubrid');
// Connection is established implicitly.
client.query('SELECT * FROM nation', function (err, result, queryHandle) {
if (err) {
throw err;
} else {
const rows = result.ColumnValues;
const rowsCount = result.RowsCount;
for (let i = 0; i < rowsCount; ++i) {
let columns = rows[i];
for (let j = 0, columnsCount = columns.length; j < columnsCount; ++j) {
console.log(columns[j]);
}
}
// Fetch more data using queryHandle if necessary.
// Refer to examples below.
}
});
The following example shows how to set placeholders and bind values in SELECT queries.
client.query('SELECT * FROM nation WHERE continent = ?', ['Asia'], callback);
Alternatively, the params value can be a literal value.
client.query('SELECT * FROM nation WHERE continent = ?', 'Asia', callback);
If the params value is undefined or null, it will be converted to SQL NULL.
client.query('SELECT * FROM nation WHERE continent IS ?', null, callback);
The Date type values will be converted into CUBRID compatible DATETIME strings.
client.query('SELECT * FROM game WHERE game_date = ?', [new Date('8/28/2004')], callback);
// The query will be
// `SELECT * FROM game WHERE game_date = '8/28/2004 0:0:0.0'`
// Note that as of CUBRID v9.3.0 it does not support time zones.
And finally, everything else will be safely escaped and wrapped in single quotes.
const promise = client
.query('SELECT * FROM nation')
.then(response => {
const result = response.result;
const queryHandle = response.queryHandle;
const rowsCount = result.RowsCount;
const rows = result.ColumnValues;
for (let i = 0; i < rowsCount; ++i) {
let columns = rows[i];
for (let j = 0, columnsCount = columns.length; j < columnsCount; ++j) {
console.log(columns[j]);
}
}
// Do something more.
})
.catch(err => {
// Handle the error.
});
/*
* `fetch()` accepts three arguments:
* 1. `queryHandle`: an integer ID for the query handle obtained from `query()` function.
* 2. `all`: a boolean that tells whether to fetch all available records or just the
* next batch. Defaults to `false`. Since `3.0.0`.
* 2. `callback(err, result, queryHandle)` function accepts the same three arguments
* accepted by the `query()` function.
*/
client.fetch(queryHandle, all, callback);
// Promise style is resolved and rejected similarly like the promise from `query()`.
const promise = client.fetch(queryHandle, all);
client.query('SELECT * FROM nation', function (err, result, queryHandle) {
if (err) {
throw err;
} else {
const rows = result.ColumnValues;
const rowsCount = result.RowsCount;
for (let i = 0; i < rowsCount; ++i) {
let columns = rows[i];
for (let j = 0, columnsCount = columns.length; j < columnsCount; ++j) {
console.log(columns[j]);
}
}
// Fetch more data using the `queryHandle`.
client.fetch(queryHandle, function (err, result, queryHandle) {
// Handle the error and response.
// Note that the fetch does not return all the records
// but only some. So, users need to fetch records in batches.
});
}
});
const promise = client
.query('SELECT * FROM nation')
.then(response => {
const result = response.result;
const queryHandle = response.queryHandle;
const rowsCount = result.RowsCount;
const rows = result.ColumnValues;
for (let i = 0; i < rowsCount; ++i) {
let columns = rows[i];
for (let j = 0, columnsCount = columns.length; j < columnsCount; ++j) {
console.log(columns[j]);
}
}
return client.fetch(queryHandle);
})
.then(response => {
// The same `response` as after `query()`.
})
.catch(err => {
// Handle the error.
});
It is vital to always close all the READ queries when they are no longer necessary.
When you query, CUBRID allocates a part of memory to hold the query statement
information necessary to return values on consecutive fetch() requests. Unless
the connection with the client is disconnected, CUBRID will keep this information
in memory. When not released during the lifetime of the connection, eventual
OOM (Out Of Memory) problems can occur on the server side. This is why when you
are done with the query results, close the queries. If the connection is disconnected,
CUBRID will automatically close all the query statements to free the memory.
/*
* `closeQuery()` accepts two arguments:
* 1. `queryHandle`: an integer ID for the query handle obtained from `query()`
* or the last `fetch()`.
* 2. `callback(err)` function that accepts one argument:
* 1. `err`: an error object if any.
*/
client.closeQuery(queryHandle, callback);
All READ queries must be closed explicitly except when you call queryAll
in which case the driver will close queries for you because there is no more
data that you may possibly request.
client.query(sql, function (err, result, queryHandle) {
const arr = result.ColumnValues;
if (arr.length) {
// Try to fetch more.
} else {
client.closeQuery(queryHandle, function (err) {
// Do something else.
});
}
});
// Promise style.
const promise = client
.query(sql)
.then(response => {
// Do something with response.
return client.closeQuery(response.queryHandle);
})
.then(() => {
})
.catch(err => {
// Handle the error.
});
There is a convenient function provided in case you want to retrieve result set as an array of traditional JSON objects with column names and values as object properties and their corresponding values.
/*
* `queryAsObjects()` calls `query()`, therefore it accepts the same
* arguments as `query()`. Available since `3.0.0`.
* 1. `sql`
* 2. `params`
* 3. `callback(err, result, queryHandle)` is different from the callback passed
* to `query()`. It accepts the same number of arguments, but `result` is no
* longer an object but an array that represents rows of record objects.
* 1. `err`: an error object if any.
* 2. `result`: an array of records each representing a single row object
* where keys are columns names and their values are column values.
* 3. `queryHandle`: an integer ID for the query handle. Used to fetch more data
* or close the query statement in order to release the memory being kept
* on the CUBRID server side.
*/
client.queryAsObjects(sql, params, callback);
// Promise style.
const promise = client.queryAsObjects(sql);
const promise = client.queryAsObjects(sql, params);
Note that the result returned by queryAsObjects is no longer a result object
but an array that represents rows of record objects. Thus, you cannot access the
column meta information via result.ColumnDataTypes, result.ColumnNames or
result.RowsCount. If such information is necessary, call getMetaData(queryHandle)
explained below.
Here is an example which executes a simple SELECT query.
// Connection is established implicitly.
client.queryAsObjects('SELECT * FROM nation', function (err, result, queryHandle) {
if (err) {
throw err;
} else {
// `result` is now an array of row objects.
const rowsCount = result.length;
for (let i = 0; i < rowsCount; ++i) {
const row = rows[i];
console.log(row.code);
console.log(row.name);
console.log(row.continent);
console.log(row.capital);
}
// Do something more.
}
});
const promise = client
.queryAsObjects('SELECT * FROM nation')
.then(response => {
const result = response.result;
const queryHandle = response.queryHandle;
// `result` is now an array of row objects.
const rowsCount = result.length;
for (let i = 0; i < rowsCount; ++i) {
const row = rows[i];
console.log(row.code);
console.log(row.name);
console.log(row.continent);
console.log(row.capital);
}
// Do something more.
})
.catch(err => {
// Handle the error.
});
/*
* `getMetaData()` accepts two arguments:
* 1. `queryHandle`: that is associated with the required query result,
* obtained from the last `query()`.
* 2. `callback(err, result)` accepts two arguments:
* 1. `err`: an error object if any.
* 2. `result`: the same `result` object that is returned when calling
* `query()` function. It provides the same `ColumnDataTypes`,
* `ColumnNames`, `ColumnValues`, and `RowsCount` properties.
*/
// Callback style.
client.getMetaData(queryHandle, callback);
// Promise style.
const promise = client.getMetaData(queryHandle);
There is a convenient function provided in case you want to query all records that match the SQL.
Note that this may potentially result in OOM in your application especially considering that in Node environment a typical process has a limited memory around 1.6GB.
Use this function when you are sure that the result set will fit into your app's memory.
/*
* `queryAll()` calls `query()` and then calls `fetch()` multiple times until
* all records are retrieved, and finally calls `closeQuery()` explicitly to
* close the query for you. Thus, it accepts the same arguments as `query()`.
* Available since `3.0.0`.
* 1. `sql`
* 2. `params`
* 3. `callback(err, result)` function is different from the callback you
* pass to `query()` in the way that in `queryAll` it accepts only
* the error and the result object. No `queryHandle` is returned.
*/
client.queryAll(sql, params, callback);
// Promise style.
const promise = client.queryAll(sql);
const promise = client.queryAll(sql, params);
Here is an example which executes a simple SELECT query.
// Connection is established implicitly.
client.queryAll('SELECT * FROM nation', function (err, result) {
if (err) {
throw err;
} else {
// `rows` now include all records that matched the SQL query.
const rows = result.ColumnValues;
const rowsCount = result.RowsCount;
for (let i = 0; i < rowsCount; ++i) {
let columns = rows[i];
for (let j = 0, columnsCount = columns.length; j < columnsCount; ++j) {
console.log(columns[j]);
}
}
// No need to fetch anything more.
// No need to close the query as it is already closed by `queryAll()`.
}
});
Note once again that there is no need to call closeQuery() after queryAll()
because queryAll() automatically closes the query after all fetch() has been
done.
const promise = client
.queryAll('SELECT * FROM nation')
.then(result => {
const rowsCount = result.RowsCount;
// `rows` now include all records that matched the SQL query.
const rows = result.ColumnValues;
for (let i = 0; i < rowsCount; ++i) {
let columns = rows[i];
for (let j = 0, columnsCount = columns.length; j < columnsCount; ++j) {
console.log(columns[j]);
}
}
// No need to fetch anything more.
// No need to close the query as it is already closed by `queryAll()`.
})
.catch(err => {
// Handle the error.
});
In case you want to retrieve all records as object, there is a function just for that.
/*
* `queryAllAsObjects()` calls `queryAll()`, therefore it accepts the same
* arguments as `queryAll()`. Available since `3.0.0`.
* 1. `sql`
* 2. `params`
* 3. `callback(err, result)` accepts the same number of arguments as `queryAll()`:
* 1. `err`: an error object if any.
* 2. `result`: an array of records each representing a single row object
* where keys are columns names and their values are column values.
*/
client.queryAllAsObjects(sql, params, callback);
// Promise style.
const promise = client.queryAllAsObjects(sql);
const promise = client.queryAllAsObjects(sql, params);
Note that just like with queryAsObjects(), the result returned by
queryAllAsObjects is an array that represents rows of record objects. Likewise,
there is no need to call closeQuery() after queryAllAsObjects() as queryAll()
automatically closes the query after all fetch() has been done.
Here is an example which executes a simple SELECT query.
// Connection is established implicitly.
client.queryAllAsObjects('SELECT * FROM nation', function (err, result) {
if (err) {
throw err;
} else {
// `result` is now an array of all row objects.
const rowsCount = result.length;
for (let i = 0; i < rowsCount; ++i) {
const row = rows[i];
console.log(row.code);
console.log(row.name);
console.log(row.continent);
console.log(row.capital);
}
// Do something more.
}
});
const promise = client
.queryAllAsObjects('SELECT * FROM nation')
.then(result => {
// `result` is now an array of row objects.
const rowsCount = result.length;
for (let i = 0; i < rowsCount; ++i) {
const row = rows[i];
console.log(row.code);
console.log(row.name);
console.log(row.continent);
console.log(row.capital);
}
})
.catch(err => {
// Handle the error.
});
/*
* `execute(sql, params, callback)` function accepts three arguments.
* 1. `sql`: a string which represents a WRITE query or an array
* of strings for batch processing. `sql` must be a string if
* `params` are provided.
* 2. `params` is an array of parameter values or the value itself
* which a user wants to bind instead of `?` placeholders
* in the `sql` query. If no placeholder is found, the `sql`
* will not be modified. This argument is optional. When
* omitted, `sql` will be sent to the server unmodified.
* Available since `3.0.0`.
* 3. `callback(err)`: a function that accepts one argument:
1. `err`: an error object if any.
// Callback style.
client.execute(sql, callback);
client.execute(sql, params, callback);
// Promise style.
const promise = client.execute(sql);
const promise = client.execute(sql, params);
/*
* A `promise` is resolved with no arguments.
* A `promise` is rejected with a single instance of `Error` object.
*/
/*
* Queries queueing: calling `execute()` multiple times will result in queuing
* the queries which will be executed sequentially one after another.
*/
client.execute(sql);
client.execute(sql, params);
After executing WRITE queries there is no need to close the query.
Here is an example which executes a simple INSERT query.
client.execute('INSERT INTO tbl VALUES(1, 2, 3)', function (err) {
// Handle the error;
});
The following example shows how to set placeholders and bind values in INSERT queries.
client.execute('INSERT INTO tbl VALUES(?, ?, ?)', [1, 2, 3], function (err) {
// Handle the error;
});
Alternatively, the params value can be a literal value.
client.execute('INSERT INTO tbl (name) VALUES(?)', 'cubrid', function (err) {
// Handle the error;
});
If the params value is undefined or null, it will be converted to SQL NULL.
client.execute('INSERT INTO tbl (name) VALUES(?)', null, function (err) {
// Handle the error;
});
The Date type values will be converted into CUBRID compatible DATETIME strings.
client.execute('INSERT INTO tbl (d) VALUES(?)', [new Date('8/28/2004')], callback);
// The query will be
// `INSERT INTO tbl (d) VALUES('8/28/2004 0:0:0.0')`
// Note that as of CUBRID v9.3.0 it does not support time zones.
And finally, everything else will be safely escaped and wrapped in single quotes.
batchExecuteNoQuery()
For cases when implicit type casting is insufficient or the result
is not as expected, data types can be specified by calling executeWithTypedParams().
/*
* `executeWithTypedParams()` accepts four arguments.
* 1. `sql`: a string which represents a single WRITE query.
* **Note** not an array of strings.
* 2. `params`: an optional array of parameter values which a user
* wants to bind instead of `?` placeholders in the `sql`
* query. If no placeholder is found, the `sql` will not
* be modified.
* 3. `dataTypes`: an optional array of string values where each
* element represents a data type for the corresponding
* value in `params`. The following data types are supported:
* `char`, `varchar`, `nchar`, `string`, `varnchar`, `short`, `int`,
* `bigint`, `float`, `double`, `monetary`, `numeric`, `date`, `time`,
* `datetime`, `timestamp`, `object`, `bit`, `varbit`, `set`, `multiset`,
* `sequence`, `blob`, `clob`, `resultset`.
* `dataTypes` are required if `params` are specified.
* 4. `callback(err)`: a function that accepts one argument:
1. `err`: an error object if any.
*/
const sql = 'INSERT INTO a VALUES(?, ?, ?, ?)';
const params = [1, 23, 'val', new Date()];
const dataTypes = ['int', 'short', 'varchar', 'datetime'];
client.executeWithTypedParams(sql, params, paramDelimiters, function callback(err) {});
// Promise way.
const promise = client.executeWithTypedParams(sql, params, paramDelimiters);
Note that executeWithTypedParams() does not replace ? placeholders.
The param and dataTypes are sent to CUBRID directly, and the server handles
explicit type casing.
Since node-cubrid version 2.1.0 almost all requests, which initiate a network communication, pass through an internal queue. This includes READ and WRITE queries, close query requests, fetch requests, rollback/commit requests.
Thus, in order to put queries into a queue, all you need to do is call query() / execute()
and their equivalent functions one after another. They will be added into the queue as
they come in (FIFO).
Here is an example.
client.execute('CREATE TABLE tbl_test(id INT)', callback);
client.execute('INSERT INTO tbl_test (id) VALUES (1), (2), (3)', callback);
client.query('SELECT * FROM tbl_test', callback);
client.execute('DROP TABLE tbl_test', callback);
Remember that the callback is optional in which case you should handle the promises.
In case you are interested in checking if the queue is empty, call the following function.
Returns true or false.
client.isQueueEmpty();
The below function will return the number of requests currently in the queue. Remember that this number represents all requests, including READ and WRITE, and fetch, and rollback/commit, etc. Briefly all requests which initiate a network communication, including those which are currently in-flight.
client.getQueueDepth();
This driver fully supports SQL transactions. By default auto_commit mode is set
to true meaning after every WRITE query CUBRID Server will commit the changes to the disk.
You can begin, end, commit, and rollback transactions by calling these functions.
client.beginTransaction(callback);
client.endTransaction(callback);
client.commit(callback);
client.rollback(callback);
// All `callback(err)` functions accept one argument: the error message if any.
beginTransaction() and endTransaction() functions simply call the following
setAutoCommitMode() by specifying a false or true arguments respectively.
client.setAutoCommitMode(boolean, callback);
// `boolean` is a boolean value which represents the auto_commit
// mode you wish to set the current transaction to.
// The `callback(err)` function accepts one argument: the error message if any.
beginTransaction(), endTransaction() and setAutoCommitMode() functions are
idempotent, that is you can call them multiple times and the result will be the same.
Note: Unlike in other DBMS vendor drivers, in node-cubrid when a transaction is
rolled back or committed, the auto_commit mode remains unchanged, i.e. false. This is
according to CUBRID spec. This means that after you commit/rollback the transaction and
you no longer need to execute queries in auto_commit = false mode, explicitly turn the
auto_commit mode to true by calling setAutoCommitMode() function.
Moreover, enabling the auto commit mode by calling setAutoCommitMode() will not
commit the changes automatically. You need to explicitly commit() or rollback() the
changes.
Here is a promise based example that shows how to start and end a transaction.
client
.execute('CREATE TABLE test_tran(id INT)')
.then(() => {
return client.beginTransaction();
})
.then(() => {
return client.execute('INSERT INTO test_tran VALUES(1)');
})
.then(() => {
return client.query('SELECT * FROM test_tran');
})
.then(response => {
assert(response.result.RowsCount === 1);
return client.closeQuery(response.queryHandle);
})
.then(() => {
return client.rollback();
})
.then(() => {
// **Note** that the auto commit mode is still OFF
// even after the `rollback()`.
return client.query('SELECT * FROM test_tran');
})
.then(response => {
assert(response.result.RowsCount === 0);
return client.closeQuery(response.queryHandle);
})
.then(() => {
// We are still inside the same transaction with
// auto commit mode OFF.
return client.execute('DROP TABLE test_tran');
})
.then(() => {
return client.commit();
})
.then(() => {
return client.endTransaction();
})
.then(() => {
return client.query("SELECT COUNT(*) FROM db_class WHERE class_name = 'test_tran'");
})
.then(response => {
assert(response.result.RowsCount === 0);
// Closing a connection will automatically close
// all query statements.
return client.close();
});
There are a few functions that report meta information about the server.
/*
* `getEngineVersion()` accepts one argument:
* 1. `callback(err, version)` accepts two arguments:
* 1. `err`: an error object if any.
* 2. `version`: CUBRID Database version like `9.2.3.0005`.
*/
// Callback style.
client.getEngineVersion(callback);
// Promise style.
const promise = client.getEngineVersion();
/*
* `getActiveHost()` accepts one argument:
* 1. `callback(err, host)` accepts two arguments:
* 1. `err`: an error object if any.
* 2. `host`: an object with `host` and `port` properties that
* represents the server which the client is connected to.
*/
// Callback style.
client.getActiveHost(callback);
// Promise style.
const promise = client.getActiveHost();
/*
* `close()` or its alias `end()` functions accept one argument:
* The alias `end()` function is available since 2.1.0.
* 1. `callback(err)` accepts one argument:
* 1. `err`: an error object if any.
*/
// Callback style.
client.close(callback);
client.end(callback);
// Promise style.
const promise = client.close();
const promise = client.end();
client.close(function (err) {
if (err) {
throw err;
} else {
console.log('connection is closed');
}
});
const promise = client
.connect()
.then(() => {
return client.close();
});
When a connection is closed by calling close() or end(), all pending/queued
requests will be removed from the internal queue. All in-flight queries will be
requested to be closed.
The following errors may be emitted when the application tries to close the connection:
- If closing a connection was unsuccessful, an error message returned by a database is emitted.
To run tests on node-cubrid module:
- Make sure you are using Node
v4or higher. npm installall development dependencies.- Make sure:
- CUBRID Server 8.4.1+ is installed on
localhost. - CUBRID Broker is listening on port
33000. demodbdatabase is running.- Alternatively, edit the test suite connection configurations at
test/testSetup/index.jsand change the connection information.
- CUBRID Server 8.4.1+ is installed on
npm testto start testing.npm run coverageto prepare the code coverage report.
If you do not have CUBRID already running, you can use the publicly available Docker image. In fact this method is recommended as you can spin up multiple versions of CUBRID on the same machine and run tests against all of them at once.
Run the following command to start CUBRID 9.2.3.0005 and its demodb database
inside a Docker container.
docker run -p 33000:33000 --name cubrid lighthopper/cubrid:9.2.3.0005 ./create-start-demodb.sh
Refer to https://github.com/kadishmal/cubrid-docker for other versions.
We intend to continuously improve this driver, by adding more features and improving the existing code base. You are more than welcome to suggest what we should improve or add - please let us know! :)
The authors of this driver are the members of the CUBRID.
We welcome new contributors and hope you will enjoy using and coding with CUBRID! :)
We would like to say thanks to the following people & projects for inspiration, for the code we have (re)used and for doing such a great job for the open-source community!
- https://github.com/caolan/async
- https://github.com/felixge/node-mysql
- https://github.com/jeromeetienne/microcache.js
... Stay tuned for the next great driver release! :)