easy-oracledb

A simple way to connect in oracle database and execute querys. This module uses node-oracledb v3.1.2.

Oracle instant client available here.

If you have any question or issue, feel free to ask.

Table of content

• Installation
  • Usage
• Functions
  • config(dbConfig)
  • testConnection()
  • readSQL(file)
  • getData(sql, params)
  • exec(sql, params)
• Examples
  • Configure and test connection
  • Get customers
  • Get customers by registration date
  • Insert customer
• Changelog

Installation

via npm:

npm i -S easy-oracledb

Usage

const db = require('easy-oracledb')

Functions

config(dbConfig)

• {string} dbConfig - an object javascript {user, pass, conn}

• {string} user - db username

• {string} pass - db password

• {string} conn - db connection string hots:port/dbname

First step configure database connection:

db.config({
    user: 'master', 
    pass: 'masterkey', 
    conn: '10.254.0.2:1521/dbname'
})

testConnection()

asynchronous function

Connection can be tested:

await db.testConnection()
    .then(result => console.log(result))
    .catch(err => console.log(err))

returns true if successfully connected or error message.

readSQL(file)

Read an existing .sql file as string

• {string} file - path to file

let sql = db.readSQL('./getCustomers.sql')

getData(sql, params)

asynchronous function

Used only for select statements.

• {string} sql - a sql string

• {array} params - an array of parameters (optional)

await db.getData(sql, [param1, param2, ...])
    .then(result => console.log(result))
    .catch(err => console.log(err))

Result:

[ 
    { 
        "COD": "330248",
        "NAME": "Rouchele",
        "E_MAIL": "[email protected]" 
    },
    { 
        "COD": "330256",
        "NAME": "John",
        "E_MAIL": "[email protected]" 
    } 
]

exec(sql, params)

asynchronous function

Used to insert, update and delete statements.

• {string} sql - a sql string

• {array} params - an array of parameters (optional)

await db.exec(sql, [param1, param2, ...])
    .then(result => console.log(result))
    .catch(err => console.log(err))

Returns number of rows affected:

1

Examples

Configure and test connection

const db = require('easy-oracledb')

db.config({
    user: 'master', 
    pass: 'masterkey', 
    conn: '10.254.0.2:1521/dbname'
})

async function testConn() {
    await db.testConnection()
        .then(result => console.log('Successfully Connected!'))
        .catch(err => console.log(err))
}

Get customers

async function getCustomers() {    
    let sql = await db.readSQL('./getCustomers.sql')

    await db.getData(sql)
        .then(result => console.log(result))
        .catch(err => console.log(err))
}

Get customers by registration date

async function getCustomersByRegDate() {

    // SELECT COD, NAME, EMAIL FROM CUSTOMERS WHERE REG_DATE BETWEEN :DATE1 AND DATE2'
    let sql = await db.readSQL('./getCustomersByRegDate.sql')

    let param = ['02/08/2019', '06/08/2019']

    await db.getData(sql, param)
        .then(result => console.log(result))
        .catch(err => console.log(err))
}

Insert customer

async function addNewCustomerWithParameter() {

    // 'INSERT INTO CUSTOMERS(COD, NAME, EMAIL, REG_DATE) 
    // VALUES (:COD, :NAME, :EMAIL, :REG_DATE)'
    let sql = await db.readSQL('./insertCustomer.sql')
     
    let params = ['1', 'John', '[email protected]', '08/07/2019']

    await db.exec(sql, params)
        .then(result => console.log(result))
        .catch(err => console.log(err))
}

Changelog

see the update notes at CHANGELOG.

Copyright ® 2019 Victor Gianvechio