Have ideas to improve npm?Join in the discussion! »

    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

    Install

    npm i node-dal

    DownloadsWeekly Downloads

    39

    Version

    3.0.2

    License

    MIT

    Unpacked Size

    121 kB

    Total Files

    24

    Last publish

    Collaborators

    • avatar