node-dal

3.0.2 • Public • Published

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

Known Vulnerabilities

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 oracledb@5.1.0 # 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 (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

Package Sidebar

Install

npm i node-dal

Weekly Downloads

41

Version

3.0.2

License

MIT

Unpacked Size

121 kB

Total Files

24

Last publish

Collaborators

  • marlic