Simple ORM

car.js

class Car extends Model {

  /**
   * @constructor
   * @description instantiates a snapshot model
   */
  constructor(make="", model="", mpg=0) {
    super();
    // the name of the table mapped to this object
    this.name = 'car';
    // the model definition
    this.model = {
      'make': make,
      'model': model, 
      'mpg': mpg, 
      // .... 
    }
  }

  /**
   * @method
   * @returns {string} the table name for the model class
   */
  static getName() {
    return 'car';
  }
  
  startEngine() {
    // ... 
  }

model.js

/**
 * Database Service Class
 */

var _ = require('underscore'),
    Promise = require('bluebird'),
    mysql = require("mysql"),
    error = require('./../util/error');

/**
 * @class Model
 * @description data model base class
 */
class Model {

  constructor() {
    this.con = mysql.createConnection({
      host: "localhost",
      database: "database",
      user: "user",
      password: "pass"
    });
    this.model = {};
    this.name = '';
  }

  static getName() {
    return '';
  }

  /**
   * @description inserts the model to the db
   * @return {Promise<Object>} returns model instance if it is successfully committed
   * @param {boolean} ignoreDuplicateEntryError - ignores mysql duplicate entry error
   * @throws {DatabaseError}
   */
  insert(ignoreDuplicateEntryError=true) {
    return new Promise((resolve, reject) => {
      var query = 'INSERT INTO ' + this.con.escapeId(this.name) + ' SET ?';
      this.con.query(query, this.model, (err, result) => {
        if (err) {
          // return the instance if the object is already stored in the db
          if (ignoreDuplicateEntryError && err.code == 'ER_DUP_ENTRY') {
            resolve(this);
          } else {
            reject(new error.DatabaseError(err.code))
          }
        } else {
          resolve(this);
        }
      });
    });
  }

  /**
   * @description commits the model to the db
   * @return {Promise<Object>} returns model instance if it is successfully committed
   * @throws {DatabaseError}
   */
  update() {
    return new Promise((resolve, reject) => {
      var query = 'UPDATE ' + this.con.escapeId(this.name) + ' SET ? WHERE uuid = ?';
      this.con.query(query, [this.model, this.model.uuid], (err, result) => {
        if (err) {
          reject(new error.DatabaseError(err.code))
        } else {
          resolve(this);
        }
      });
    });
  }

  /**
   * @description deletes the model from the db
   * @return {Promise<Object>} returns model instance if it is successfully committed
   * @throws {DatabaseError}
   */
  delete() {
    return new Promise((resolve, reject) => {
      var query = 'DELETE FROM ' + this.con.escapeId(this.name) + 'WHERE uuid = ?';
      this.con.query(query, [this.model.uuid], (err, result) => {
        if (err) {
          reject(new error.DatabaseError(err.code))
        } else {
          resolve(true);
        }
      });
    });
  }

  /**
   * @description generate a properly formated mysql datetimestamp
   * @return {String} properly formatted date string
   */
  static now() {
    return new Date().toISOString().slice(0, 19).replace('T', ' ');
  }

}

module.exports = Model;

query.js

/**
 * Database Service Class
 */

var _ = require('underscore'),
    Promise = require('bluebird'),
    mysql = require("mysql"),
    error = require('./../util/error');

/**
 * @class Query
 * @description query the database
 */
class Query {

  constructor(modelClass) {
    this.con = mysql.createConnection({
      host: "localhost",
      database: "database",
      user: "user",
      password: "pass"
    });

    // the results object
    this.results = [];
    // identifies is the query returns a model instance, or data object
    this.return = 'data';
    // the model class to attach the model to
    this.modelClass = modelClass;
    // query options option defaults
    this.table = modelClass.getName();
    // sql query options
    this.columns = null;
    this.where = null;
    this.orderBy = null;
    this.groupBy = null;
    this.sort = 'ASC';
    this.limit = null;
    this.offset = null;
  }

  /**
   * @description returns object from the database
   * @return {Promise<Array>} returns an array of models found by the query
   */
  exec() {
    return new Promise((resolve, reject) => {
      var query = 'SELECT ';
      // specify columns to select, if provided
      if (this.columns) {
        _.each(this.columns, (column, index) => {
          if (index == 0) {
            query = query + column;
          } else {
            query = query + ', ' + column;
          }
        });
      // if no colunns are provided, select all columns
      } else {
        query = query + '*';
      }

      // identify the table to perform the select against
      query = query + ' FROM ' + this.con.escapeId(this.table);

      // construct appropriate sql clauses
      if (this.where) {
        query = query + ' WHERE '
        _.each(this.where, (value, key) => {
          query = query + this.con.escapeId(key) + ' = ' + this.con.escape(value);
        });
      }
      if (this.groupBy) {
        query = query + ' GROUP BY ' + this.con.escape(this.groupBy);
      }
      if (this.orderBy) {
        query = query + ' ORDER BY ' + this.con.escape(this.orderBy) + ' ' + this.con.escape(this.sort);
      }
      if (this.limit) {
        query = query + ' LIMIT ' + this.con.escape(this.limit)
        if (this.offset) {
          query = query + ',' + this.con.escape(this.offset);
        }
      }

      // execute the query 
      console.log('Executing Query: ', query);
      this.con.query(query, (err, results) => {
        if (err) {
          console.log(err.code);
          reject(new error.DatabaseError(err.code))
        } else {
          if (this.return == 'class') {
            _.each(results, (data, index) => {
                var model = new this.modelClass();
                _.each(data, (value, prop) => {
                  model.model[prop] = data[prop]
                });
                this.results.push(model);
            });
            resolve(this.results);
          } else if (this.return == 'data') {
            this.results = results;
            resolve(results);
          } else {
            reject(new error.DatabaseError('Invalid return type: ' + this.return));
          }
        }
      });
    });
  }

  /**
   * @description set the query return value
   * @param {string} value - either 'model' or 'data'
   */
  setReturn(value) {
    if (value == 'class' || value == 'data') {
      this.return = value;
    }
    return this;
  }

  /**
   * @description set the query columns
   * @param {Array} value - array of column names
   */
  setColumns(value) {
    if (value instanceof Array) {
      this.columns = value;
    }
    return this;
  }

  /**
   * @description add where clause to query
   * @param {object} value - dictionary of columns and values
   */
  setWhere(value) {
    if (typeof value == 'object') {
      this.where = value;
    }
    return this;
  }

  /**
   * @description add an group by clause to the query
   * @param {string} value - column name to group by
   */
  setGroupBy(value) {
    this.groupBy = value;
    return this;
  }

  /**
   * @description add an order by clause to the query
   * @param {string} value - column name to order the results by
   */
  setOrderBy(value) {
    this.orderBy = value;
    return this;
  }

  /**
   * @description add a sort clause to the query
   * @param {string} value - either 'ASC' or 'DESC'
   */
  setSort(value) {
    if (value == 'ASC' || 'DESC') {
      this.sort = value;
    }
    return this;
  }

  /**
   * @description add a limit clause to the query
   * @param {number} value - limit value
   */
  setLimit(value) {
    if (typeof value == 'number') {
      this.limit = value;
    }
    return this;
  }

  /**
   * @description add an offset (page) to the limit
   * @param {string} value - offset value
   */
  setOffset(value) {
    if (typeof value == 'number') {
      this.offset = value;
    }
    return this;
  }

}

module.exports = Query;