dbtool

help to execute sql query in SQL database, it is just support mysql, mssql, oracle with simple sql in current version.

npm install dbtool
14 downloads in the last week
37 downloads in the last month

DBTool instructions


 

Preparation

Because of this package dependent on oracle, before you install this package, you must install the Oracle instant client first.

For detail, you can click here.

In this place, I just show you how to install Oracle instant client in linux.(from npm oracle package)

1. Install
  1. Instant Client Package - Basic or Basic Lite: All files required to run OCI, OCCI, and JDBC-OCI applications
  2. Instant Client Package - SDK: Additional header files and an example makefile for developing Oracle applications with Instant Client

    Please make sure you download the correct packages for your system architecture, such as 64 bit vs 32 bit Unzip the files 1 and 2 into the same directory, such as /opt/instantclient_11_2 or c:\instantclient_12_1

2. Set up the following environment variables
export OCI_HOME=<directory of Oracle instant client>
export OCI_LIB_DIR=$OCI_HOME
export OCI_INCLUDE_DIR=$OCI_HOME/sdk/include
export OCI_VERSION=<the instant client major version number> # Optional. Default is 11.
cd $OCI_LIB_DIR
ln -s libclntsh.so.11.1 libclntsh.so
ln -s libocci.so.11.1 libocci.so
4. Libaio library is required on Linux systems
  • On Unbuntu/Debian

      sudo apt-get install libaio1
    
  • On Fedora/CentOS/RHEL

      sudo yum install libaio
    
5. Add the shared object files to the ld cache
# Replace /opt/instantclient_11_2/ with wherever you extracted the Basic Lite files to
echo '/opt/instantclient_11_2/' | sudo tee -a /etc/ld.so.conf.d/oracle_instant_client.conf
sudo ldconfig

 

Install DBTool

sudo npm install dbtool

 

Example

1. Make a global common file like this
//common.js
var dbtool = require('dbtool');
var mysqlConfig = {
    dialect  : 'mysql',
    host     : '192.168.1.113',
    port     : 3306,
    database : 'ME',
    user     : 'root',
    password : 'root',
    min  : 0,               //default 0
    max  : 10,              //default 1
    idleTimeoutMillis : 100 //default 100
};
exports.mysqlDBTool = dbtool.createDBTool(mysqlConfig);

////////////////////////////////////////////////////////////////////
var mssqlConfig = {
    dialect  : 'mssql',
    host     : '192.168.1.113',
    port     : 1433,
    database : 'ME',
    user     : 'sa',
    password : 'sa'
};
exports.mssqlDBTool = dbtool.createDBTool(mssqlConfig);

////////////////////////////////////////////////////////////////////
var oracleConfig = {
    dialect  : 'oracle',
    host     : '192.168.1.113',
    port     : 1521,
    database : 'ME',
    user     : 'sys',
    password : 'sys'
};
exports.oracleDBTool = dbtool.createDBTool(oracleConfig);
2. Execute query and get the result
var common = require("./common");

////////////////////////////////////////////////////////////////////
var MysqlDBTool = common.mysqlDBTool;
var options = {
    params: [1],
    sort : {
        id: 'asc',
        colname: 'desc'},
    skip: 50,
    limit: 10
};
MysqlDBTool.executeQuery("select * from ru_dbtool_col where cannull=@@ ", options, function(err, rows){
    if(err) {
        throw err;
    }
    console.dir(rows);
});
MysqlDBTool.executeQuery("select 1 as time", function(err, rows){
    if(err) {
        throw err;
    }
    console.dir(rows);
});

////////////////////////////////////////////////////////////////////
var MssqlDBTool = common.mssqlDBTool;
var options = {
    params: [0],
    sort : {
        id: 'asc'},
    skip: 29,
    limit: 10
};
MssqlDBTool.executeQuery("select * from RU_DBTOOL_COL where PRIMARYKEYFLAG = @@", options, function(err, rows){
    if(err) {
        throw err;
    }
    console.dir(rows);
});
MssqlDBTool.executeQuery("select 1 as one", function(err, rows){
    if(err) {
        throw err;
    }
    console.dir(rows);
});

////////////////////////////////////////////////////////////////////
var oracleDBTool = common.oracleDBTool;
var options = {
    params: ['1'],
    sort : {
        userCode: 'asc',
        userName: 'desc'},
    skip: 19,
    limit: 10
};
oracleDBTool.executeQuery("select * from PM_USER where userflag = @@", options, function(err, rows){
    if(err) {
        throw err;
    }
    console.dir(rows);
});
oracleDBTool.executeQuery("select 1 from dual", function(err, rows){
    if(err) {
        throw err;
    }
    console.dir(rows);
});

 

DBTool Info

The following functions will let you get information about the DBTool status:

// returns number of connection in the dbtool inner pool regardless of
// whether they are free or in use
getPoolSize()

// returns number of using connection in the dbtool inner pool
getBusyConnsCount()

// returns number of unused connection in the dbtool inner pool
getAvailableConnsCount()

// returns number of callers waiting to acquire a connection
getWaitingClientsCount()

 

Remark

1. This package use the connection pool technology and you don't need to care about the release of the connection. Every time you call executeQuery function, it will get a connection from connection pool. Beyond the scope of the callback function, the connection will put back to connection pool automatically.
2. This DBTool unified the paging and scheduling interface, but it is not support sql server 2000.
npm loves you