/ sql

Run Oracle SQL with Node.js

I've had to deal with an Oracle database recently, and like most enterprise-level tooling, I've found it lacking. The tools Oracle provides to access their data are the bare minimum they need to be functional. Companies don't buy enterprise software for their developer, they buy it for their operational needs (among other things).

Luckily, Oracle released a interop library for Node.js last year. I jumped on using that right away. It's hard to pass on the idea of having the entire Node ecosystem available to you. Installing the package was a bit trickier than just npm install oracledb. It needs 3 different SDK files and bevy of other build dependencies.

Once you get it up and going, working with the database was surprsingly easy. Here's what I cooked up for executing SQL from a file. Run the script with -f <sql_file>.

const oracledb = require('oracledb');
const fs = require('fs');
const path = require('path');
const argv = require('minimist')(process.argv.slice(2));
const dbConfig = {
  user          : "hr",
  password      : "welcome",
  connectString : "localhost/orcl",
};

const sqlFile = argv.f;

const handleErr = callback => (err, res) => {
  if (err) {
    console.error(err.message);
    return;
  }
  callback(res);
};

const runSql = connection => {
  const filename = path.join(__dirname, sqlFile);
  const sql = fs.readFileSync(filename, 'utf8');
  connection.execute(sql, {}, handleErr(result => {
    console.log('ran:', filename);
  }));
};

oracledb.getConnection({
  user: dbConfig.user,
  password: dbConfig.password,
  connectString: dbConfig.connectString
}, handleErr(runSql));