Table of Contents

• Installation
• Debugging
• Connect
• Defining Schemas
• Schema Validations
• Schema Transforms
• Other Schema Options - readonly - sequenceName
• Initialize
• Creation
• Updating
• Querying - findById(value) - findOne({}) - count({}) - select()
• Model Methods - get - set - unset - toJSON/setFromJSON - destroy - reload
• Model Properties - id - valid - errors
• Transactions
  • Function Style
    • commit()
    • rollback()
  • Promise Style
    • commit()
    • rollback()
  • Extending Models - statics({}) - methods({})
  • Associations and Population
    • hasOne
    • hasMany
    • hasManyThrough
    • hasAndBelongsToMany
  • Raw Connection - Connection - Knex
  • Utilities
  • Other Examples
  • Contributing
  • License

Installation

NOTICE: oracle-sage uses the official node-oracledb module, which is complex to install. Be sure you have followed the official directions to install node-oracledb before installing Sage

Oracle Sage supports Node 6+, you can publish a build to support earlier versions of Node by forking the project and modifying the node target in the .babelrc file

First, you must have node-oracledb installed. You can find the install instructions at their repository here.

After that, you're good to go! Simply do...

$ npm install oracle-sage

var sage = require('oracle-sage');

Debugging

For more verbose outputs you can configure sage.logger, which is an instance of this popular Winston logger. By default the level is set to 0. If you set the level to debug you will get SQL outputs and such.

sage.logger.transports.console.level = 'debug';

Note you can also just drop in your own logger via sage.logger directly:

var sage = require('sage');
var myLogger = require('winston');
sage.logger = myLogger;

We recommend the Winston logger. This is useful if you have a custom logger configuration, for example one that posts to AWS Cloudwatch. It doesn't necessarily have to be winston, but sage uses logger.info logger.warn logger.error logger.debug. So as long as your logger can handle those functions, you are all set.

Connect

var auth = {
  user: "system",
  password: "oracle"
}
sage.connect("127.0.0.1:1521/orcl", auth).then(function() {
  // do something...
});

Defining Schemas

var userSchema = sage.Schema({
  ID: "number",
  CREATED_AT: {
    type: "date",
    format: "MM/DD/YYYY",
  },
  CREATED_DTS: {
    type: "timestamp",
    format: "DD/MMM/YYYY hh:mm:ss a",
  },
  USERNAME: {
    type: "varchar"
    validator: function(value) {
      return /^[a-zA-Z]+$/.test(value); // test only letters
    },
  },
  GENDER: {
    type: "char",
    enum: {
      values: ['M', 'F']
    }
  },
  BIO: "clob"
}, {
  primaryKey: "ID"
})

Supports types:

• raw
• number
• char
• date
• varchar
• clob
• blob

Special features:

• enum
• validators
• transforms

Methods:

• getDefinition(field) - Returns a the definition for a given field

Schema Validations

The following validation properties are supported:

• all types

  • required - do not use this on PK due to a bug for now
  • validator(value) - a custom function validator

• number

  • min
  • max

• varchar

  • minlength
  • maxlength

• clob

  • minlength
  • maxlength

var userSchema = sage.Schema({
  ID: "number",
  USERNAME: {
    required: true,
    type: "varchar",
    maxlength: 12,
    minlength: 4,
    validator: function(value) {
      return /^[a-zA-Z]+$/.test(value); // test only letters
    }
  }
})

Schema Transforms

To access the raw output from node-oracledb and apply your own transformations you can use a Schema transform function. Sage usually returns the raw output from oracle-sage except for fields that oracle-db returns as Buffers or Streams. Sage converts Buffers to uppercase hexadecimal strings and it converts Streams to utf8 strings. If you want more granular control over this conversion then you should use a transform function. transform functions should either return the transformed value or return a promise which resolves with the transformed value.

const commentSchema = new sage.Schema(
  {
    COMMENT_ID: {
      type: 'raw',
      transform: buffer => buffer.toString('utf8')
    },
    LIKE_COUNT: {
      type: 'raw',
    },
    BODY: {
      type: 'blob',
      transform: value => {
        return new Promise(resolve => {
          const chunks = [];
          value.on('data', chunk => {
            chunks.push(chunk.toString());
          });
          value.on('end', () => {
            resolve(`${chunks.join('')} No you may not.`);
          });
        });
      }
    },
  },
  {
    primaryKey: 'COMMENT_ID',
  }
);

Other Schema Options

readonly

When set on a field, during an update() call, this field will not be serialized into the update even if it was attempted to be changed.

sequenceName

There is a special case for autoincrement where your Oracle database might not be able to use triggers to toggle autoincrement fields (eg. if you use Hibernate). The circumvent this, add a sequenceName property.

sage.Schema({
  ID: {
    type: "number",
    sequenceName: "SAGE_TEST.SEQUENCE_NO_TRIGGER_SEQUENCE_N",
    readonly: true
  }
  ...
}, {
  primaryKey: "ID"
});

Now whenever you issue a create. A nextval will be executed on the sequence during insertion to get the value for the primary key.

Initialize

var userTable = "users"; // use the table name in the database
var User = sage.model(userTable, userSchema);

Creation

User.create({ USERNAME: "example" });

// EXPERIMENTAL. May not work well with complex column like BLOB
User.create([
  { USERNAME: "create" },
  { USERNAME: "many" },
  { USERNAME: "at once" }
], { hasDbmsErrlog: true }); // This options object is optional

Notes:

• In the schema you can set a field to be readonly. This will disable it from being written to on creation.

• Passing an array creates in a single INSERT ALL ... SELECT * FROM DUAL statement

  • There is a flag hasDbmsErrlog that if set true, will not fail the query if a constraint/error is hit.
  • This flag assumes you have an error table for your table, see this stackoverflow issue

• There is a special case for autoincrement where you might not be able to use triggers to toggle autoincrement fields (eg. if you use Hibernate). The circumvent this, add a sequenceName property.

eg.

sage.Schema({
  ID: {
    type: "number",
    sequenceName: "SAGE_TEST.SEQUENCE_NO_TRIGGER_SEQUENCE_N",
    readonly: true
  }
  ...
});

Updating

Updating will only try to save the "dirty" fields. You can only update on schemas where you have defined a primaryKey.

User.findOne({ username: "example" }).then(function(user) {
  user.set("username", "bob");
  user.save().then(function() {
    // do something
  });
})

Querying

A list of current querying options. Note that querying returns models.

findById(value)

Finds model based on value against the schema primary key

findOne({})

Accepts {} which transforms into AND conditions. Returns the first item, and the is ORDERED BY the schema primary key.

Returns null if nothing is found. Otherwise returns a result represented in the model.

User.findOne({ USERNAME: example, GENDER: 'M'}).then(function(resultModel) {
  var user = resultModel;
  user.get('GENDER') // value is "M"
})

count({})

Accepts optional {} which transforms into AND conditions. Returns the count.

User.count({ USERNAME: example }).then(function(count) { ... })
User.count().then(function(count) { ... })

select()

A chainable query builder based off Knex. See Knex for the full API usage.

Note you must use the exec or execWithBindParams to ensure results are returned as a Sage model - using toString then sage.execute will return raw query results The options used with the exec or execWithBindParams methods also support options allowed here: https://github.com/oracle/node-oracledb/blob/master/doc/api.md#execute

Note that right now sage only supports the ARRAY format for knex select if you specify multiple columns. String is OK for single column.

const singleColumnExample = 'USERNAME';
const multipleColumnExample = ['USERNAME', 'PASSWORD'];
// for purposes of example we're assuming we are already in a transaction
const options = { transaction: t };

User
  .select() // same as select('*')
  .where('USERNAME', 'example')
  .limit(1)
  .exec().then(function(resultsAsModels) {
    resultsAsModels[0].get('USERNAME') // value is "example"
  });

User
  .select("USERNAME")
  .limit(1)
  .exec(options).then(function(resultsAsModels) {
    console.log(resultsAsModels);
  });

User
  .select()
  .whereRaw('USERNAME = :userName')
  .execWithBindParams(bindParams, options).then(resultsAsModels => {
    console.log(resultsAsModels);
  });

Model Methods

get

Get a property.

user.get('USERNAME'); // returns "example" (based off above schema)

set

Set a property.

user.set('USERNAME', 'alice');
user.set({ 'USERNAME': 'alice', 'GENDER': 'F');

unset

Sets the attribute value to undefined. Does NOT delete the attribute.

user.unset('USERNAME') // username is now undefined

toJSON/setFromJSON

Sends a lowercased version to client, and will set from a JSON and convert all key fields to uppercase. This are two useful things because OracleDBs are typically uppercase, yet client work is usually lowercase.

user.toJSON() // outputs json with uppercased keys
user.setFromJSON() // will set props based and will uppercase the keys

destroy

Delete the record from database

user.destroy().then(function(){});

reload

Reload the record from database

user.reload().then(function(){ ... });

Model Properties

id

Quick way to see the primary key ID of a model.

user.id // Whatever the primary key value is set to

valid

user.set('USERNAME', 12345);
user.valid // false
user.set('USERNAME', 'example');
user.valid // true

errors

user.errors // []
user.set({'USERNAME': 12345, GENDER: 'xyz');
user.valid // false
user.errors // ['USERNAME fails validator', 'GENDER is not in enum']

Transactions

Create a sage transaction to perform several operations before commit.

You can create transactions either invoking as a Promise, or by passing down a function.

Function Style

Returns a Promise. In this style, commit and rollback resolves the promise. When you use this style as you are forced to apply a commit() or rollback() in order to resolve the promise.

commit()

Commits the transaction and resolves the transaction promise.

rollback()

Rollback the transaction and resolves the transaction promise.

sage.transaction(function(t) {
  User.create({ username: "demo" }, { transaction: t }).then(function() {
    t.commit(); // Resolves the promise
  }).catch(function(err) {
    t.rollback();
  });
}).then(function() {
  // transaction done!
});

Promise Style

The Promise style is available in the event you need a slightly different syntax. In this style commit and rollback will return promises. Be careful using this syntax because you may forget to call commit or rollback, which will leave a connection open.

The sage transaction concept is identical to the SequelizeJS Unmanaged transaction.

commit()

Commits the transaction. Returns a promise.

rollback()

Rollback the transaction. Returns a promise.

return sage.transaction()
  .then((t) => {
    return User.create({ username: "demo" }, { transaction: t })
      .then(() => User.select('*').exec({ transaction: t })) // Example using select
      .then((results) => User.findById('1', { transaction: t}))
      .then((user) => {
        user.set('username', 'Bob')
        return user.save({ transaction: t});
      })
      .then(() => t.commit())
      .catch((err) => {
        return t.rollback()
          .then(() => {
            throw(err);
          });
      });
});

Extending Models

You can add methods both on the constructor and instances of a model.

statics({})

Add functions directly to the constructor.

var User = sage.model("user");
User.statics({
  findByEmail: function(email) {
    return new Promise(function(resolve, reject) {
      User.findOne({ email: email }).then(function(result) {
        resolve(result);
      });
    });
  }
})

User.findByEmail("[email protected]").then(...)

methods({})

Add functions directly to an instance.

var User = sage.model("user");
User.methods({
  fullname: function() {
    return(this.get('first') + this.get('last'));
  }
})

user = new User({ first: "Mr", last: "chess" });
user.fullname(); // Mrchess

Associations and Population

• Associations and naming conventions are Rails inspired.
• You must .populate() a model in order to load the associations.
• Saving will only save the original schema, and does not impact associations.

Supports:

• hasOne
• hasMany
• hasManyThrough
• hasAndBelongsToMany

The following examples satisfies the displayed database designs. The pictures are from rails so the field types in the pictures are not the exact Oracles equivilant.

hasOne

var supplierSchema = new sage.Schema({
  id: "number",
  name: "varchar"
  // Note this that you can really call this whatever you want. account, accounts, meta, whatever.
  account: {
    type: "association",
    joinType: "hasOne",
    joinsWith: "accounts",
    foreignKeys: {
      mine: "id",
      theirs: "supplier_id"
    },
    model: 'accounts'
}, {
  primaryKey: "id"
});

var accountSchema = new sage.Schema({
  id: "number",
  supplier_id: "number",
  account_number: "varchar"
});

hasMany

var customersSchema = new sage.Schema({
  id: "number",
  name: "varchar"
  orders: {
    type: "association",
    joinType: "hasMany",
    joinsWith: "orders",
    foreignKeys: {
      mine: "id",
      theirs: "customer_id"
    },
    model: 'orders'
}, {
  primaryKey: "id"
});

var ordersSchema = new sage.Schema({
  id: "number",
  customer_id: "number",
  order_date: {
    type: "date",
  format: "MM/DD/YYYY"
  }
});

hasManyThrough

var physicianSchema = new sage.Schema({
  id: "number",
  name: "varchar"
  patients: {
    type: "association",
    joinType: "hasManyThrough",
    joinTable: "appointments",
    joinsWith: "patients",
    foreignKeys: { // foreign keys in the association table
      mine: 'physician_id',
      theirs: 'patient_id'
    },
    model: 'patients' // what model to cast in to when results are returned
  }
}, {
  primaryKey: "id"
});

// It is not necessary to put the association here unless you want to populate
// physicians on a patient model
var patientSchema = new sage.Schema({
  id: "number",
  name: "varchar"
}, {
  primaryKey: "id"
})

// Create the models
var Physician = sage.model("physicians", physicianSchema);
var Patient = sage.model("patients", patientSchema);

// Example usage
Physician.findById(1).then(function(physician) {
  physician.populate().then(function() {
    physician.get('patients').length; // value would be how ever many patients were returned
    var patient = physician.get('patients')[0]; // get the first patient
    patient.get('name') // return patient name
  })
})

hasAndBelongsToMany

var assemblySchema = new sage.Schema({
  id: "number",
  name: "varchar"
  parts: {
    type: "association",
    joinType: "hasAndBelongsToMany",
    joinTable: "assemblies_parts",
    joinsWith: "parts",
    foreignKeys: { // foreign keys in the association table
      mine: 'assembly_id',
      theirs: 'part_id'
    },
    model: 'parts' // what model to cast in to when results are returned
  }
}, {
  primaryKey: "id"
});

// It is not necessary to put the association here unless you want to populate
// assemblies on a parts model
var partsSchema = new sage.Schema({
  id: "number",
  part_number: "varchar"
}, {
  primaryKey: "id"
})

// Create the models
var Assembly = sage.model("assemblies", assemblySchema);
var Part = sage.model("parts", partsSchema);

// Example usage
Assembly.findById(1).then(function(assemblyModel) {
  assemblyModel.populate().then(function() {
    assemblyModel.get('parts'); // array of Part models
  })
)}

Raw Connection

Connection

You can directly access a node-oracledb connection.execute() from the pool at:

sage.execute(sql, bindParams, options)
  .then(resultsAsOracleResultObject => {
    console.log('Do something.')
  });

This is a direct exposure of: https://github.com/oracle/node-oracledb/blob/master/doc/api.md#-42-connection-methods

Don't worry to release the connection, as the sage execute wrapper handles that for you.

Knex

Knex is directly exposed in sage as well through sage.knex. See Knex for the full API usage.

Knex is strictly used for query building. You can use it with the raw connection. For example:

var query = sage.knex.select().from('user').toString();
sage.execute(query).then(function(response) {
  console.log(response)
});

Don't worry to release the connection, as the sage execute wrapper handles that for you.

See Knex for the full API usage.

Utilities

There is a useful utility called sage.util.resultToJSON(result).

You can pass in a result (and optional Schema if using transform functions) from a sage.execute() and it will transform the native oracle results into a JSON array that you can pass down to your model. eg.

const someSQL = 'SELECT * FROM USER';
sage.execute(someSQL)
  .then(result => sage.util.resultToJSON(result))
  .then(resultsAsJson => {
    const users = _.map(resultsAsJson, (user) => {
      return new User(user);
    });
  });

Other Examples

Basic example of some common functionality.

var user;

User.create({USERNAME: "example"}).then(function() {
  return User.findOne({USERNAME: "example"});
}).then(function(resultModel) {
  user = resultModel;
  user.get('USERNAME'); // example
  user.set('USERNAME', 'alice');
  return user.save();
}).then(function() {
  user.get('USERNAME'); // alice
});

Contributing

The tests suite assumes you have a local Oracle 11g database set up with the following information:

Hostname: localhost
Port: 1521
Service name: xe
Username: SAGE_TEST
Password: oracle

You can install a VM here. https://blogs.oracle.com/opal/entry/the_easiest_way_to_enable

License

The MIT License (MIT)

Copyright (c) 2016 Foundation Medicine, Inc.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.