node-dal version 3.0.2 (Node.js Database Abstraction Layer)

This is yet another database abstraction layer.

It purpose is to be:

1. Simple
2. Easy and elastic to use
3. Support pagination
4. Well parameterizable
5. Well tested
6. Well documented
7. Callback or Promise style code
8. Easy to extend (adapter writers very welcome)

Supported databases:

• Oracle (oracledb driver)

This library is not:

• ORM

Documentation

Installation

npm install --save node-dal
npm install --save [email protected] # or any other supported db driver

Initialization

var dalFactory = require('node-dal'),
    conf       = require('./config');

    dalFactory('oracledb', conf)
        .then(dal => {
            return dal.querySql('select ...')
        })
        .then(results => {
            console.log(results);
        })
        .catch(err => {
            console.log(err.message);
        });        

Sample config file

module.exports = {
    oracle: {
        connection: {
            user          : "dbuser",
            password      : "dbuserpasswd",
            connectString : "localhost/XE",
            poolMax       : 10,
            poolMin       : 1,
            poolIncrement : 1,
            poolTimeout   : 60
        },
        /*
            For performance reason it is better to set ENV variables: TZ=UTC,
            NLS_DATE_FORMAT='YYYY-MM-DD' instead of below nlsSessionParameters keys.
        */
        nlsSessionParameters: {
            time_zone:       '00:00', // fix for bad date cast by oracledb when read
            nls_date_format: 'yyyy-mm-dd'
        },
        dbVer: '12',
        outFormat: 'object' // array/object
    },
    other: {}
};

IMPORTANT!!!

If you set nlsSessionParameters key in config file, then ALTER SESSION ... will be invoke on every connection fetch from pool (pool.getConnection). Currently oracledb hasn't session tagging support (see issue 258).

For performance reason it is better to set ENV variables: TZ=UTC, NLS_DATE_FORMAT='YYYY-MM-DD' instead of below nlsSessionParameters keys.

Tests

npm test
npm run testperf

Library was successfully tested with:

• DB: Oracle 12c EE
• Node.js: v12.16.1
• OS: Ubuntu 18.04

API

IMPORTANT!!!

All methods parameters could be pass in two ways:

• as a object with proper named keys
• as a list in proper order

For example both below approach are equivalent:

dal.querySql({sql: 'SELECT ...', bind: [15], cb: callback});
dal.querySql('SELECT ...', [15], callback);

If cb (callback) parameter is not provided then function will return Promise.

dal.querySql({sql: 'SELECT ...', bind: [15]})
    .then(results => {
        console.log(results);
    })
    .catch(cb);

dal.querySql('SELECT ...', [15])
    .then(results => {
        console.log(results);
    })
    .catch(cb);

• selectOneRow
• selectOneRowSql
• selectOneValue
• selectOneValueSql
• selectOneClobValue
• selectOneClobValueSql
• selectAllRows
• selectAllRowsSql
• getSqlForSelectAllRows
• getSqlForSelectAllRowsSql
• querySql
• runProcedure
• insert
• insertReturningId
• insertReturningIdSql
• update
• del
• executeTransaction
• getDbConnection
• getDbPool
• getDriver

selectOneRow (tbl:string, [fields:Array|null], where:Array, [opt:object|null], [cb:function])

see params details: fields where opt

Fetch only one record (row) from table or view. Request have to return max one record otherwise error will be thrown.

Examples:

dal.selectOneRow('test_01', null, ['id = ?', 10], (err, result) => {
    if(err) {
        console.error(err.message);
        return;
    }
    console.log(result);
});

// with promise
dal.selectOneRow('test_01', null, ['id = ?', 10])
    .then(result => {
        console.log(result);
    })
    .catch(err => {
        console.error(err.message);
    });

API

selectOneRowSql (sql:string, bind:object|Array, [opt:object|null], [cb:function])

see params details: opt

Fetch only one record (row) from table or view. Request have to return max one record otherwise error will be thrown.

dal.selectOneRowSql("SELECT To_Char(sysdate, 'yyyy-mm-dd') dat FROM dual", [], (err, result) => {
    if(err) {
        console.error(err.message);
        return;
    }
    console.log(result);
});

// with promise
dal.selectOneRowSql("SELECT To_Char(sysdate, 'yyyy-mm-dd') dat FROM dual", [])
    .then(result => {
        console.log(result);
    })
    .catch(err => {
        console.error(err.message);
    });

API

selectOneValue (tbl:string, field:string, where:Array|object, [opt:object|null], [cb:function])

see params details: where opt

Fetch one value of specific field from table or view. Request have to return max one record otherwise error will be thrown.

dal.selectOneValue('test_01', 'text',  ['id = ?', 10], (err, result) => {
    if(err) {
        console.error(err.message);
        return;
    }
    console.log(result);
});

// with promise
dal.selectOneValue('test_01', 'text',  ['id = ?', 10])
    .then(result => {
        console.log(result);
    })
    .catch(err => {
        console.error(err.message);
    });

API

selectOneValueSql (sql:string, bind:object|Array, [opt:object|null], [cb:function])

see params details: opt

Fetch one value of specific field from table or view. Request have to return max one record otherwise error will be thrown.

dal.selectOneValueSql('SELECT text FROM test_01 WHERE id=:0', [10], (err, result) => {
    if(err) {
        console.error(err.message);
        return;
    }
    console.log(result);
});

// with promise
dal.selectOneValueSql('SELECT text FROM test_01 WHERE id=:0', [10])
    .then(result => {
        console.log(result);
    })
    .catch(err => {
        console.error(err.message);
    });

API

selectOneClobValue (tbl:string, field:string, bind:object|Array, [opt:object|null], [cb:function])

see params details: opt

Only for Oracle driver.

dal.selectOneClobValue('test_01', 'text_clob', ['id = ?', 10], (err, result) => {
    if(err) {
        console.error(err.message);
        return;
    }
    console.log(result);
});

// with promise
dal.selectOneClobValue('test_01', 'text_clob', ['id = ?', 10])
    .then(result => {
        console.log(result);
    })
    .catch(err => {
        console.error(err.message);
    });

API

selectOneClobValueSql (sql:string, bind:object|Array, [opt:object|null], [cb:function])

see params details: opt

Only for Oracle driver.

dal.selectOneClobValueSql('SELECT text_clob FROM test_01 WHERE id=:0', [10], function(err, result) {
    if(err) {
        console.error(err.message);
        return;
    }
    console.log(result);
});

// with promise
dal.selectOneClobValueSql('SELECT text_clob FROM test_01 WHERE id=:0', [10])
    .then(result => {
        console.log(result);
    })
    .catch(err => {
        console.error(err.message);
    });

API

selectAllRows (tbl:string, [fields:Array|null], [where:Array|object|null], [order:Array|string|null], [opt:object|null], [cb:function])

see params details: fields where order opt

dal.selectAllRows('test_01', null, null, null, { outFormat: 'array', limit:10, page:5 }, function(err, result) {
    if(err) {
        console.error(err.message);
        return;
    }

    console.log(result);
});

// with promise
dal.selectAllRows('test_01', null, null, null, { outFormat: 'array', limit:10, page:5 })
    .then(result => {
        console.log(result);
    })
    .catch(err => {
        console.error(err.message);
    });

API

selectAllRowsSql (sql:string, bind:object|Array, [opt:object|null], [cb:function])

see params details: opt

dal.selectAllRowsSql('SELECT * FROM test WHERE col_a = :0 AND col_b = :1', [1, 'T'], function(err, result) {
    if(err) {
        console.error(err.message);
        return;
    }

    console.log(result);
});

// with promise
dal.selectAllRowsSql('SELECT * FROM test WHERE col_a = :0 AND col_b = :1', [1, 'T'])
    .then(result => {
        console.log(result);
    })
    .catch(err => {
        console.error(err.message);
    });

API

getSqlForSelectAllRows (tbl:string, [fields:Array|null], [where:Array|object|null], [order:Array|string|null], [opt:object|null])

see params details: fields where order opt

const { sql } = dal.getSqlForSelectAllRows( { tbl: 'test_01', opt: { outFormat: 'array', limit:10, page:5 } });

API

getSqlForSelectAllRowsSql (sql:string, bind:object|Array, [opt:object|null])

see params details: opt

const { sql } = dal.getSqlForSelectAllRowsSql('SELECT * FROM test WHERE col_a = :0 AND col_b = :1', [1, 'T']);

API

querySql (sql:string, [bind:object|Array], [opt:object|null], [cb:function])

see params details: opt

Invoke SQL queries like: UPDATE, INSERT, DELETE, DROP, ALTER etc...

dal.querySql('DROP TABLE test_01', [], done);

API

runProcedure (procName:string, bind:object|Array, [opt:object|null], [cb:function])

see params details: opt

Invoke stored procedure with parameters.

var bindvars = {
    i:  'Chris',  // bind type is determined from the data type
    i2: { fn: 'To_Date(?, \'yyyymmdd\')', bind: '20151023' },
    io: { val: 'Jones', dir : dal.BIND_INOUT },
    o:  { type: dal.NUMBER, dir : dal.BIND_OUT }
};
dal.runProcedure('procedure01', bindvars, function(err, result) {
    if(err) {
        console.error(err.message);
        return;
    }

    console.log(result);
});

// with promise
dal.runProcedure('procedure01', bindvars)
    .then(result => {
        console.log(result);
    })
    .catch(err => {
        console.error(err.message);
    });

Invoke stored procedure and grab dbmsOutput

dal.runProcedure('procedure02', {}, {dbmsOutput: true}, function(err, result) {
    if(err) {
        console.error(err.message);
        return;
    }

    console.log(result);
});

API

insert (tbl:string, data:object, [opt:object|null], [cb:function])

see params details: data opt

dal.insert('test_01', {id: 999, text: 'simple'}, function(err, result) {
    if(err) {
        console.error(err.message);
        return;
    }

    console.log(result);
});

API

insertReturningId (tbl:string, data:object, sequence:string, [opt:object|null], [cb:function])

see params details: data opt

Invoke INSERT operation with unique ID fetched from sequence and returns that ID (no SQL version).

dal.insertReturningId('test_01', {id: {type:'pk'}, text: 'test11'}, 'test_01_sid', function(err, result) {
    if(err) {
        console.error(err.message);
        return;
    }

    console.log(result);
});

API

insertReturningIdSql (sql:string, bind:object|Array, sequence:string, [opt:object|null], [cb:function])

see params details: opt

Invoke INSERT operation with unique ID fetched from sequence and returns that ID (SQL version).

dal.insertReturningIdSql('INSERT INTO test_01 (id, text) VALUES (:0, :1)', [{type:'pk'},'test10'], 'test_01_sid', function(err, result) {
    if(err) {
        console.error(err.message);
        return;
    }

    console.log(result);
});

API

update (tbl:string, data:object, where:Array|object, [opt:object|null], [cb:function])

see params details: where data opt

Invoke UPDATE on specified table. Only fields in given data parameter object (simple key:value) will be modified for rows selected by given where parameter.

dal.update('test_01', {text: 'test11-modified'}, ['id = ?', 11], function(err, result) {
    if(err) {
        console.error(err.message);
        return;
    }

    console.log(result);
});

API

del (tbl:string, where:Array|object, [opt:object|null], [cb:function])

see params details: where opt

Delete record or records.

dal.del('test_01', ['id = ?', 999], function(err, result) {
    if(err) {
        console.error(err.message);
        return;
    }

    console.log(result);
});

API

executeTransaction (sqlBindArray:Array, [opt:object], [cb:function])

see params details: opt

Execute simple transaction. Either all queries from array will be succesful perform or none of them.

It could be used for multi DDL instructions but in such case transaction won't be work.

const sqlBindArray = [
    ['INSERT INTO test_01 VALUES (:0, :1)', [131, 'T01']],
    ['UPDATE test_01 SET text = :0 WHERE id = :1', ['T02', 131]],
    ['UPDATE test_01 SET text = :0 WHERE id = :1', ['AAB', 124]],
    ['DELETE FROM test_01 WHERE id = :0', [131]]
];

dal.executeTransaction(sqlBindArray, function(err, results) {
    if(err) {
        done(err);
        return;
    }

    assert.equal(results.length, 4);
    done();
});

API

getDbConnection ([cb:function])

Get connection from pool to perform operation using origin db driver methods.

let _result, _conn;
dal.getDbConnection()
    .then(connection => {
        _conn = connection;
        return connection.execute(sql, bind, { outFormat: dal.OBJECT });
    })
    .then(result => {
        _result = result;
        return _conn.release();
    })
    .then(() => {
        cb(null, _result);
    })
    .catch(cb);

API

getDbPool()

Get orgin connection pool (one from driver or generic pool if driver hasn't pool').

const dbPool = dal.getDbPool();

API

getDriver()

Get orgin db driver object.

const driver = dal.getDriver();

API

Method parameters

fields

selected fields:

const fields = ['field1', 'field2', 'field3'];

all fields:

const fields = null;

only one field:

const fields = 'fieldX';

API

where

as a array:

const where = [
   [ 'field LIKE ?', '%ola%' ], // operator AND is default
   [ 'field2 IS NULL', null, 'OR' ],
   {
       type: 'AND',
       nested: [
           [ 'field3 = ?', 5 ],
           [ 'field5 BETWEEN ? AND ?', [3, 4], 'OR' ]
       ]
   }
]

as a object (only AND clouse and equity (=) operator):

const where = {
    "field1": 100,
    "field2": "abc"
}

API

data

const data = {
    field1: { type: 'sequence', name: 'seq_name' },
    field2: "value1",
    field3: { type: 'function', name: 'fn_name' },
    field4: "value2",
    field5: { name: 'SYSDATE' }
}

API

order

const order_v1 = ['field1', ['field2', 'DESC']];
const order_v2 = ['field1', 'field2 DESC'];

API

opt

const conn = await dal.getDbConnection();

const opt = {
    outFormat: 'array', // return results as Array instead of object (object like JSON is default behavior for this library)
    limit: 10,          // enable pagination and sets row number per page, also adds to results field "n__" (or last in array) with current row number
    page: 5,            // page number to fetch,
    totalCount: true,   // adds to results field "c__" (or last in array) with all query rows count (summarize all records in all pages for given query)
    fetchClobs: true,   // auto fetch all data for CLOB-s (works with:  selectOneRow, selectOneRowSql, selectAllRows and selectAllRowsSql)
    sessionCtx: [{      // automatically sets session context attributes values of current connection
        ctxProcedureName: 'set_ctx_node_dal',
        ctxAttribute: 'current_id',
        ctxValue: '10'
    }],
    connection: conn,   // pass connection to reuse, this connection will not be release after query execute so You have to release it manually!
    dbmsOutput: true    // only for runProcedure - fetch all DBMS_OUTPUT.PUT_LINE from procedure and put that string as last callback argument
}

API