Nine Pounds of Mayonnaise

    oracle-sage

    1.1.5 • Public • Published

    sagee

    Oracle Sage

    Promise driven OracleDB object modeling for node.js

    Table of Contents

    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("mrchess@example.com").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:

    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.

    Keywords

    none

    Install

    npm i oracle-sage

    DownloadsWeekly Downloads

    26

    Version

    1.1.5

    License

    MIT

    Unpacked Size

    79.6 kB

    Total Files

    20

    Last publish

    Collaborators

    • himrc
    • soundlogic