oracledb-upsert

UPSERT (insert/update) extension to oracledb.

• Overview
• Usage
• Installation
• API Documentation
• Contributing
• Release History
• License

Overview

This library extends the oracledb connection object with a new upsert function to enable to run UPDATE or INSERT based on the data currently in the DB.

Usage

In order to use this library, you need to extend the main oracledb object as follows:

//load the oracledb library
var oracledb = require('oracledb');

//load the simple oracledb
var SimpleOracleDB = require('simple-oracledb');

//modify the original oracledb library
SimpleOracleDB.extend(oracledb);

//load the extension
require('oracledb-upsert');

//from this point connections fetched via oracledb.getConnection(...) or pool.getConnection(...)
//have access to the UPSERT function.
oracledb.getConnection(function onConnection(error, connection) {
    if (error) {
        //handle error
    } else {
        //work with new capabilities or original oracledb capabilities
        connection.upsert({
            query: 'SELECT ID FROM MY_DATA WHERE ID = :id',
            insert: 'INSERT INTO MY_DATA (ID, NAME) VALUES (:id, :name)',
            update: 'UPDATE MY_DATA SET NAME = :name WHERE ID = :id'
        }, {
            id: 110,
            name: 'new name'
        }, {
            autoCommit: false
        }, function onUpsert(error, results) {
            if (error) {
                //handle error...
            } else {
                console.log('rows affected: ', results.rowsAffected);

                //continue flow...
            }
        });
    }
});

'connection.upsert(sqls, bindParams, [options], [callback]) ⇒ [Promise]'

The UPSERT oracledb extension gets 3 SQL statements. It first queries the database for existing data, based on the output, it either runs INSERT or UPDATE SQL. If it runs the INSERT and it fails on unique constraint, it will also run the UPDATE. The output in the callback is the output of the INSERT/UPDATE operation.

Example

connection.upsert({
  query: 'SELECT ID FROM MY_DATA WHERE ID = :id',
  insert: 'INSERT INTO MY_DATA (ID, NAME) VALUES (:id, :name)',
  update: 'UPDATE MY_DATA SET NAME = :name WHERE ID = :id'
}, {
  id: 110,
  name: 'new name'
}, {
  autoCommit: false
}, function onUpsert(error, results) {
  if (error) {
    //handle error...
  } else {
    console.log('rows affected: ', results.rowsAffected);

    //continue flow...
  }
});

Installation

In order to use this library, just run the following npm install command:

npm install --save oracledb-upsert

This library doesn't define oracledb as a dependency and therefore it is not installed when installing oracledb-upsert. You should define oracledb in your package.json and install it based on the oracledb installation instructions found at: installation guide

API Documentation

See full docs at: API Docs

Contributing

See contributing guide

Release History

| Date | Version | Description | | ----------- | ------- | ----------- | | 2020-05-13 | v2.0.0 | Migrate to github actions and upgrade minimal node version | | 2019-02-08 | v1.2.1 | Maintenance | | 2016-08-05 | v0.0.43 | Added promise support | | 2016-03-04 | v0.0.1 | Initial release. |

License

Developed by Sagie Gur-Ari and licensed under the Apache 2 open source license.