loopback-connector - npm Package Compare versions

Comparing version 1.2.1 to 2.0.0

CHANGES.md

@@ -0,1 +1,13 @@
[phone], Version 2.0.0
+=========================
+
+ * Upgrade deps (Raymond Feng)
+
+ * Make sure invalid fields are filtered out (Raymond Feng)
+
+ * Refactor base and sql connector (Raymond Feng)
+
+ * Update README.md (Paulo McNally)
+
+
 2015-01-28, Version 1.2.1
@@ -2,0 +14,0 @@ =========================

index.js

 exports.Connector = require('./lib/connector');
-exports.SqlConnector = require('./lib/sql');
+// Set up SqlConnector as an alias to SQLConnector
+exports.SQLConnector = exports.SqlConnector = require('./lib/sql');
+exports.ParameterizedSQL = exports.SQLConnector.ParameterizedSQL;

lib/connector.js

@@ -0,5 +1,7 @@
+var debug = require('debug')('loopback:connector');
+
 module.exports = Connector;
 
 /**
- * Base class for LooopBack connector. This is more a collection of useful
+ * Base class for LoopBack connector. This is more a collection of useful
  * methods for connectors than a super class
@@ -21,2 +23,11 @@ * @constructor
 /**
+ * Check if the connector is for a relational DB
+ * @returns {Boolean} true for relational DB
+ */
+Connector.prototype.isRelational = function() {
+  return this.isRelational ||
+    (this.getTypes().indexOf('rdbms') !== -1);
+};
+
+/**
  * Get types associated with the connector
@@ -31,5 +42,7 @@ * @returns {String[]} The types for the connector
  * Get the default data type for ID
+ * @param prop Property definition
  * @returns {Function} The default type for ID
  */
-Connector.prototype.getDefaultIdType = function() {
+Connector.prototype.getDefaultIdType = function(prop) {
+  /*jshint unused:false */
   return String;
@@ -43,6 +56,7 @@ };
  * @property {Function} defaultIdType The default id type
- * @property {Boolean} [isRelational] If the connector represents a relational database
+ * @property {Boolean} [isRelational] If the connector represents a relational
+ * database
  * @property {Object} schemaForSettings The schema for settings object
  */
-Connector.prototype.getMedadata = function () {
+Connector.prototype.getMetadata = function() {
   if (!this._metadata) {
@@ -52,3 +66,3 @@ this._metadata = {
       defaultIdType: this.getDefaultIdType(),
-      isRelational: this.isRelational || (this.getTypes().indexOf('rdbms') !== -1),
+      isRelational: this.isRelational(),
       schemaForSettings: {}
@@ -62,12 +76,50 @@ };
  * Execute a command with given parameters
- * @param {String} command The command such as SQL
- * @param {Object[]} [params] An array of parameters
+ * @param {String|Object} command The command such as SQL
+ * @param {*[]} [params] An array of parameter values
+ * @param {Object} [options] Options object
  * @param {Function} [callback] The callback function
  */
-Connector.prototype.execute = function (command, params, callback) {
-  /*jshint unused:false */
-  throw new Error('query method should be declared in connector');
+Connector.prototype.execute = function(command, params, options, callback) {
+  throw new Error('execute() must be implemented by the connector');
 };
 
 /**
+ * Get the model definition by name
+ * @param {String} modelName The model name
+ * @returns {ModelDefinition} The model definition
+ */
+Connector.prototype.getModelDefinition = function(modelName) {
+  return this._models[modelName];
+};
+
+/**
+ * Get connector specific settings for a given model, for example,
+ * ```
+ * {
+ *   "postgresql": {
+ *     "schema": "xyz"
+ *   }
+ * }
+ * ```
+ *
+ * @param {String} modelName Model name
+ * @returns {Object} The connector specific settings
+ */
+Connector.prototype.getConnectorSpecificSettings = function(modelName) {
+  var settings = this.getModelDefinition(modelName).settings || {};
+  return settings[this.name];
+};
+
+/**
+ * Get model property definition
+ * @param {String} modelName Model name
+ * @param {String} propName Property name
+ * @returns {Object} Property definition
+ */
+Connector.prototype.getPropertyDefinition = function(modelName, propName) {
+  var model = this.getModelDefinition(modelName);
+  return model && model.properties[propName];
+};
+
+/**
  * Look up the data source by model name
@@ -77,6 +129,6 @@ * @param {String} model The model name
  */
-Connector.prototype.getDataSource = function (model) {
-  var m = this._models[model];
+Connector.prototype.getDataSource = function(model) {
+  var m = this.getModelDefinition(model);
   if (!m) {
-    console.trace('Model not found: ' + model);
+    debug('Model not found: ' + model);
   }
@@ -91,3 +143,3 @@ return m && m.model.dataSource;
  */
-Connector.prototype.idName = function (model) {
+Connector.prototype.idName = function(model) {
   return this.getDataSource(model).idName(model);
@@ -101,3 +153,3 @@ };
  */
-Connector.prototype.idNames = function (model) {
+Connector.prototype.idNames = function(model) {
   return this.getDataSource(model).idNames(model);
@@ -113,4 +165,4 @@ };
  */
-Connector.prototype.id = function (model, prop) {
-  var p = this._models[model].properties[prop];
+Connector.prototype.id = function(model, prop) {
+  var p = this.getModelDefinition(model).properties[prop];
   return p && p.id;
@@ -123,6 +175,4 @@ };
  */
-Connector.prototype.define = function (modelDefinition) {
-  if (!modelDefinition.settings) {
-    modelDefinition.settings = {};
-  }
+Connector.prototype.define = function(modelDefinition) {
+  modelDefinition.settings = modelDefinition.settings || {};
   this._models[modelDefinition.model.modelName] = modelDefinition;
@@ -135,14 +185,18 @@ };
  * @param {String} propertyName The property name
- * @param {Object} propertyDefinition The object for property metadata
+ * @param {Object} propertyDefinition The object for property definition
  */
-Connector.prototype.defineProperty = function (model, propertyName, propertyDefinition) {
-  this._models[model].properties[propertyName] = propertyDefinition;
-};
+Connector.prototype.defineProperty = function(model, propertyName, propertyDefinition) {
+    var modelDef = this.getModelDefinition(model);
+    modelDef.properties[propertyName] = propertyDefinition;
+  };
 
 /**
  * Disconnect from the connector
+ * @param {Function} [cb] Callback function
  */
 Connector.prototype.disconnect = function disconnect(cb) {
   // NO-OP
-  if (cb) process.nextTick(cb);
+  if (cb) {
+    process.nextTick(cb);
+  }
 };
@@ -157,3 +211,3 @@
  */
-Connector.prototype.getIdValue = function (model, data) {
+Connector.prototype.getIdValue = function(model, data) {
   return data && data[this.idName(model)];
@@ -169,3 +223,3 @@ };
  */
-Connector.prototype.setIdValue = function (model, data, value) {
+Connector.prototype.setIdValue = function(model, data, value) {
   if (data) {
@@ -176,8 +230,61 @@ data[this.idName(model)] = value;
 
-Connector.prototype.getType = function () {
-  return this.type;
+/**
+ * Test if a property is nullable
+ * @param {Object} prop The property definition
+ * @returns {boolean} true if nullable
+ */
+Connector.prototype.isNullable = function(prop) {
+  if (prop.required || prop.id) {
+    return false;
+  }
+  if (prop.nullable || prop['null'] || prop.allowNull) {
+    return true;
+  }
+  if (prop.nullable === false || prop['null'] === false ||
+    prop.allowNull === false) {
+    return false;
+  }
+  return true;
 };
 
+/**
+ * Return the DataAccessObject interface implemented by the connector
+ * @returns {Object} An object containing all methods implemented by the
+ * connector that can be mixed into the model class. It should be considered as
+ * the interface.
+ */
+Connector.prototype.getDataAccessObject = function() {
+  return this.DataAccessObject;
+};
 
+/*!
+ * Define aliases to a prototype method/property
+ * @param {Function} cls The class that owns the method/property
+ * @param {String} methodOrPropertyName The official property method/property name
+ * @param {String|String[]} aliases Aliases to the official property/method
+ */
+Connector.defineAliases = function(cls, methodOrPropertyName, aliases) {
+  if (typeof aliases === 'string') {
+    aliases = [aliases];
+  }
+  if (Array.isArray(aliases)) {
+    aliases.forEach(function(alias) {
+      if (typeof alias === 'string') {
+        Object.defineProperty(cls, alias, {
+          get: function() {
+            return this[methodOrPropertyName];
+          }
+        });
+      }
+    });
+  }
+};
 
+/**
+ * `command()` and `query()` are aliases to `execute()`
+ */
+Connector.defineAliases(Connector.prototype, 'execute', ['command', 'query']);
 
+
+
+

lib/sql.js

@@ -5,15 +5,25 @@ var util = require('util');
 var Connector = require('./connector');
+var debug = require('debug')('loopback:connector:sql');
+var ParameterizedSQL = require('./parameterized-sql');
 
-module.exports = SqlConnector;
+module.exports = SQLConnector;
 
 /**
- * Base class for connectors that are backed by relational databases/SQL
+ * Base class for connectors that connect to relational databases using SQL
  * @class
  */
-function SqlConnector() {
+function SQLConnector() {
+  // Call the super constructor
   Connector.apply(this, [].slice.call(arguments));
 }
 
-util.inherits(SqlConnector, Connector);
+// Inherit from the base Connector
+util.inherits(SQLConnector, Connector);
 
+// Export ParameterizedSQL
+SQLConnector.ParameterizedSQL = ParameterizedSQL;
+
+// The generic placeholder
+var PLACEHOLDER = SQLConnector.PLACEHOLDER = ParameterizedSQL.PLACEHOLDER;
+
 /**
@@ -23,53 +33,114 @@ * Set the relational property to indicate the backend is a relational DB
  */
-SqlConnector.prototype.relational = true;
+SQLConnector.prototype.relational = true;
 
 /**
+ * Invoke a prototype method on the super class
+ * @param {String} methodName Method name
+ */
+SQLConnector.prototype.invokeSuper = function(methodName) {
+  var args = [].slice.call(arguments, 1);
+  var superMethod = this.constructor.super_.prototype[methodName];
+  return superMethod.apply(this, args);
+};
+
+/**
  * Get types associated with the connector
  * Returns {String[]} The types for the connector
  */
-SqlConnector.prototype.getTypes = function() {
+SQLConnector.prototype.getTypes = function() {
   return ['db', 'rdbms', 'sql'];
 };
 
-/*!
+/**
  * Get the default data type for ID
+ * @param prop Property definition
  * Returns {Function}
  */
-SqlConnector.prototype.getDefaultIdType = function() {
+SQLConnector.prototype.getDefaultIdType = function(prop) {
+  /*jshint unused:false */
   return Number;
 };
 
-SqlConnector.prototype.query = function () {
-  throw new Error('query method should be declared in connector');
+/**
+ * Get the default database schema name
+ * @returns {string} The default schema name, such as 'public' or 'dbo'
+ */
+SQLConnector.prototype.getDefaultSchemaName = function() {
+  return '';
 };
 
-SqlConnector.prototype.command = function (sql, params, callback) {
-  return this.query(sql, params, callback);
+/**
+ * Get the database schema name for the given model. The schema name can be
+ * customized at model settings or connector configuration level as `schema` or
+ * `schemaName`. For example,
+ *
+ * ```json
+ * "Customer": {
+ *   "name": "Customer",
+ *   "mysql": {
+ *     "schema": "MYDB",
+ *     "table": "CUSTOMER"
+ *   }
+ * }
+ * ```
+ *
+ * @param {String} model The model name
+ * @returns {String} The database schema name
+ */
+SQLConnector.prototype.schema = function(model) {
+  // Check if there is a 'schema' property for connector
+  var dbMeta = this.getConnectorSpecificSettings(model);
+  var schemaName = (dbMeta && (dbMeta.schema || dbMeta.schemaName)) ||
+    (this.settings.schema || this.settings.schemaName) ||
+    this.getDefaultSchemaName();
+  return schemaName;
 };
 
-SqlConnector.prototype.queryOne = function (sql, callback) {
-  return this.query(sql, function (err, data) {
-    if (err) {
-      return callback(err);
-    }
-    callback(err, data && data[0]);
-  });
-};
-
 /**
- * Get the table name for a given model.
+ * Get the table name for the given model. The table name can be customized
+ * at model settings as `table` or `tableName`. For example,
+ *
+ * ```json
+ * "Customer": {
+ *   "name": "Customer",
+ *   "mysql": {
+ *     "table": "CUSTOMER"
+ *   }
+ * }
+ * ```
+ *
  * Returns the table name (String).
  * @param {String} model The model name
  */
-SqlConnector.prototype.table = function (model) {
-  var name = this.getDataSource(model).tableName(model);
-  var dbName = this.dbName;
-  if (typeof dbName === 'function') {
-    name = dbName(name);
+SQLConnector.prototype.table = function(model) {
+  var dbMeta = this.getConnectorSpecificSettings(model);
+  var tableName;
+  if (dbMeta) {
+    tableName = dbMeta.table || dbMeta.tableName;
+    if (tableName) {
+      // Explicit table name, return as-is
+      return tableName;
+    }
   }
-  return name;
+  tableName = model;
+  if (typeof this.dbName === 'function') {
+    tableName = this.dbName(tableName);
+  }
+  return tableName;
 };
 
 /**
- * Get the column name for given model property
+ * Get the column name for the given model property. The column name can be
+ * customized at the model property definition level as `column` or
+ * `columnName`. For example,
+ *
+ * ```json
+ * "name": {
+ *   "type": "string",
+ *   "mysql": {
+ *     "column": "NAME"
+ *   }
+ * }
+ * ```
+ *
  * @param {String} model The model name
@@ -79,13 +150,21 @@ * @param {String} property The property name
  */
-SqlConnector.prototype.column = function (model, property) {
-  var name = this.getDataSource(model).columnName(model, property);
-  var dbName = this.dbName;
-  if (typeof dbName === 'function') {
-    name = dbName(name);
+SQLConnector.prototype.column = function(model, property) {
+  var prop = this.getPropertyDefinition(model, property);
+  var columnName;
+  if (prop && prop[this.name]) {
+    columnName = prop[this.name].column || prop[this.name].columnName;
+    if (columnName) {
+      // Explicit column name, return as-is
+      return columnName;
+    }
   }
-  return name;
+  columnName = property;
+  if (typeof this.dbName === 'function') {
+    columnName = this.dbName(columnName);
+  }
+  return columnName;
 };
 
 /**
- * Get the column name for given model property
+ * Get the column metadata for the given model property
  * @param {String} model The model name
@@ -95,3 +174,3 @@ * @param {String} property The property name
  */
-SqlConnector.prototype.columnMetadata = function (model, property) {
+SQLConnector.prototype.columnMetadata = function(model, property) {
   return this.getDataSource(model).columnMetadata(model, property);
@@ -101,3 +180,3 @@ };
 /**
- * Get the corresponding property name for a given column name
+ * Get the corresponding property name for the given column name
  * @param {String} model The model name
@@ -107,4 +186,4 @@ * @param {String} column The column name
  */
-SqlConnector.prototype.propertyName = function (model, column) {
-  var props = this._models[model].properties;
+SQLConnector.prototype.propertyName = function(model, column) {
+  var props = this.getModelDefinition(model).properties;
   for (var p in props) {
@@ -121,5 +200,5 @@ if (this.column(model, p) === column) {
  * @param {String} model The model name
- * @returns {String} The column name
+ * @returns {String} The id column name
  */
-SqlConnector.prototype.idColumn = function (model) {
+SQLConnector.prototype.idColumn = function(model) {
   var name = this.getDataSource(model).idColumnName(model);
@@ -138,16 +217,7 @@ var dbName = this.dbName;
  */
-SqlConnector.prototype.idColumnEscaped = function (model) {
-  return this.escapeName(this.getDataSource(model).idColumnName(model));
+SQLConnector.prototype.idColumnEscaped = function(model) {
+  return this.escapeName(this.idColumn(model));
 };
 
 /**
- * Escape the name for the underlying database
- * @param {String} name The name
- */
-SqlConnector.prototype.escapeName = function (name) {
-  /*jshint unused:false */
-  throw new Error('escapeName method should be declared in connector');
-};
-
-/**
  * Get the escaped table name
@@ -157,3 +227,3 @@ * @param {String} model The model name
  */
-SqlConnector.prototype.tableEscaped = function (model) {
+SQLConnector.prototype.tableEscaped = function(model) {
   return this.escapeName(this.table(model));
@@ -168,7 +238,14 @@ };
  */
-SqlConnector.prototype.columnEscaped = function (model, property) {
+SQLConnector.prototype.columnEscaped = function(model, property) {
   return this.escapeName(this.column(model, property));
 };
 
-function isIdValuePresent(idValue, callback, returningNull) {
+/*!
+ * Check if id value is set
+ * @param idValue
+ * @param cb
+ * @param returningNull
+ * @returns {boolean}
+ */
+function isIdValuePresent(idValue, cb, returningNull) {
   try {
@@ -178,4 +255,4 @@ assert(idValue !== null && idValue !== undefined, 'id value is required');
   } catch (err) {
-    process.nextTick(function () {
-      if(callback) callback(returningNull ? null: err);
+    process.nextTick(function() {
+      if (cb) cb(returningNull ? null : err);
     });
@@ -185,24 +262,200 @@ return false;
 }
+
 /**
- * Save the model instance into the backend store
+ * Convert the id value to the form required by database column
  * @param {String} model The model name
+ * @param {*} idValue The id property value
+ * @returns {*} The escaped id column value
+ */
+SQLConnector.prototype.idColumnValue = function(model, idValue) {
+  var idProp = this.getDataSource(model).idProperty(model);
+  if (typeof this.toColumnValue === 'function') {
+    return this.toColumnValue(idProp, idValue);
+  } else {
+    return idValue;
+  }
+};
+
+/**
+ * Replace `?` with connector specific placeholders. For example,
+ *
+ * ```
+ * {sql: 'SELECT * FROM CUSTOMER WHERE NAME=?', params: ['John']}
+ * ==>
+ * {sql: 'SELECT * FROM CUSTOMER WHERE NAME=:1', params: ['John']}
+ * ```
+ * *LIMITATION*: We don't handle the ? inside escaped values, for example,
+ * `SELECT * FROM CUSTOMER WHERE NAME='J?hn'` will not be parameterized
+ * correctly.
+ *
+ * @param {ParameterizedSQL|Object} ps Parameterized SQL
+ * @returns {ParameterizedSQL} Parameterized SQL with the connector specific
+ * placeholders
+ */
+SQLConnector.prototype.parameterize = function(ps) {
+  ps = new ParameterizedSQL(ps);
+
+  // The value is parameterized, for example
+  // {sql: 'to_point(?,?)', values: [1, 2]}
+  var parts = ps.sql.split(PLACEHOLDER);
+  var clause = [];
+  for (var j = 0, m = parts.length; j < m; j++) {
+    // Replace ? with the keyed placeholder, such as :5
+    clause.push(parts[j]);
+    if (j !== parts.length - 1) {
+      clause.push(this.getPlaceholderForValue(j + 1));
+    }
+  }
+  ps.sql = clause.join('');
+  return ps;
+};
+
+/**
+ * Build the the `INSERT INTO` statement
+ * @param {String} model The model name
+ * @param {Object} fields Fields to be inserted
+ * @param {Object} options Options object
+ * @returns {ParameterizedSQL}
+ */
+SQLConnector.prototype.buildInsertInto = function(model, fields, options) {
+  var stmt = new ParameterizedSQL('INSERT INTO ' + this.tableEscaped(model));
+  var columnNames = fields.names.join(',');
+  if (columnNames) {
+    stmt.merge('(' + columnNames + ')', '');
+  }
+  return stmt;
+};
+
+/**
+ * Build the clause to return id values after insert
+ * @param {String} model The model name
+ * @param {Object} data The model data object
+ * @param {Object} options Options object
+ * @returns {string}
+ */
+SQLConnector.prototype.buildInsertReturning = function(model, data, options) {
+  return '';
+};
+
+/**
+ * Build the clause for default values if the fields is empty
+ * @param {String} model The model name
+ * @param {Object} data The model data object
+ * @param {Object} options Options object
+ * @returns {string} 'DEFAULT VALUES'
+ */
+SQLConnector.prototype.buildInsertDefaultValues = function(model, data, options) {
+  return 'VALUES()';
+};
+
+/**
+ * Build INSERT SQL statement
+ * @param {String} model The model name
+ * @param {Object} data The model data object
+ * @param {Object} options The options object
+ * @returns {string} The INSERT SQL statement
+ */
+SQLConnector.prototype.buildInsert = function(model, data, options) {
+  var fields = this.buildFields(model, data);
+  var insertStmt = this.buildInsertInto(model, fields, options);
+  var columnValues = fields.columnValues;
+  var fieldNames = fields.names;
+  if (fieldNames.length) {
+    var values = ParameterizedSQL.join(columnValues, ',');
+    values.sql = 'VALUES(' + values.sql + ')';
+    insertStmt.merge(values);
+  } else {
+    insertStmt.merge(this.buildInsertDefaultValues(model, data, options));
+  }
+  var returning = this.buildInsertReturning(model, data, options);
+  if (returning) {
+    insertStmt.merge(returning);
+  }
+  return this.parameterize(insertStmt);
+};
+
+/**
+ * Execute a SQL statement with given parameters.
+ *
+ * @param {String} sql The SQL statement
+ * @param {*[]} [params] An array of parameter values
+ * @param {Object} [options] Options object
+ * @param {Function} [callback] The callback function
+ */
+SQLConnector.prototype.execute = function(sql, params, options, callback) {
+  assert(typeof sql === 'string', 'sql must be a string');
+  if (typeof params === 'function' && options === undefined &&
+    callback === undefined) {
+    // execute(sql, callback)
+    options = {};
+    callback = params;
+    params = [];
+  } else if (typeof options === 'function' && callback === undefined) {
+    // execute(sql, params, callback)
+    callback = options;
+    options = {};
+  }
+  params = params || [];
+  options = options || {};
+  assert(Array.isArray(params), 'params must be an array');
+  assert(typeof options === 'object', 'options must be an object');
+  assert(typeof callback === 'function', 'callback must be a function');
+
+  var self = this;
+  if (!this.dataSource.connected) {
+    return this.dataSource.once('connected', function() {
+      self.execute(sql, params, options, callback);
+    });
+  }
+  this.executeSQL(sql, params, options, callback);
+};
+
+/**
+ * Create the data model in MySQL
+ *
+ * @param {String} model The model name
  * @param {Object} data The model instance data
- * @param {Function} callback The callback function
+ * @param {Object} options Options object
+ * @param {Function} [callback] The callback function
  */
-SqlConnector.prototype.save = function (model, data, callback) {
-  var idName = this.getDataSource(model).idName(model);
+SQLConnector.prototype.create = function(model, data, options, callback) {
+  var self = this;
+  var stmt = this.buildInsert(model, data, options);
+  this.execute(stmt.sql, stmt.params, options, function(err, info) {
+    if (err) {
+      callback(err);
+    } else {
+      var insertedId = self.getInsertedId(model, info);
+      callback(err, insertedId);
+    }
+  });
+};
+
+/**
+ * Save the model instance into the database
+ * @param {String} model The model name
+ * @param {Object} data The model instance data
+ * @param {Object} options Options object
+ * @param {Function} cb The callback function
+ */
+SQLConnector.prototype.save = function(model, data, options, cb) {
+  var idName = this.idName(model);
   var idValue = data[idName];
 
-  if (!isIdValuePresent(idValue, callback)) {
+  if (!isIdValuePresent(idValue, cb)) {
     return;
   }
 
-  idValue = this._escapeIdValue(model, idValue);
-  var sql = 'UPDATE ' + this.tableEscaped(model) + ' SET ' +
-    this.toFields(model, data) +
-    ' WHERE ' + this.idColumnEscaped(model) + ' = ' + idValue;
+  var where = {};
+  where[idName] = idValue;
 
-  this.query(sql, function (err, result) {
-    if (callback) callback(err, result);
-  });
+  var updateStmt = new ParameterizedSQL('UPDATE ' + this.tableEscaped(model));
+  updateStmt.merge(this.buildFieldsForUpdate(model, data));
+  var whereStmt = this.buildWhere(model, where);
+  updateStmt.merge(whereStmt);
+  updateStmt = this.parameterize(updateStmt);
+  this.execute(updateStmt.sql, updateStmt.params, options,
+    function(err, result) {
+      if (cb) cb(err, result);
+    });
 };
@@ -214,19 +467,31 @@
  * @param {*} id The id value
- * @param {Function} callback The callback function
+ * @param {Object} options Options object
+ * @param {Function} cb The callback function
  */
-SqlConnector.prototype.exists = function (model, id, callback) {
-  if (!isIdValuePresent(id, callback, true)) {
+SQLConnector.prototype.exists = function(model, id, options, cb) {
+  if (!isIdValuePresent(id, cb, true)) {
     return;
   }
-  var sql = 'SELECT 1 FROM ' +
-    this.tableEscaped(model) + ' WHERE ' +
-      this.idColumnEscaped(model) + ' = ' + this._escapeIdValue(model, id) +
-      ' LIMIT 1';
+  var idName = this.idName(model);
+  var where = {};
+  where[idName] = id;
+  var selectStmt = new ParameterizedSQL(
+    'SELECT 1 FROM ' + this.tableEscaped(model) +
+    ' WHERE ' + this.idColumnEscaped(model)
+  );
+  selectStmt.merge(this.buildWhere(model, where));
 
-  this.query(sql, function (err, data) {
-    if (!callback) return;
+  selectStmt = this.applyPagination(model, selectStmt, {
+    limit: 1,
+    offset: 0,
+    order: [idName]
+  });
+  selectStmt = this.parameterize(selectStmt);
+
+  this.execute(selectStmt.sql, selectStmt.params, options, function(err, data) {
+    if (!cb) return;
     if (err) {
-      callback(err);
+      cb(err);
     } else {
-      callback(null, data.length >= 1);
+      cb(null, data.length >= 1);
     }
@@ -237,134 +502,654 @@ });
 /**
- * Find a model instance by id
+ * ATM, this method is not used by loopback-datasource-juggler dao, which
+ * maps `destroy` to `destroyAll` with a `where` filter that includes the `id`
+ * instead.
+ *
+ * Delete a model instance by id value
  * @param {String} model The model name
  * @param {*} id The id value
- * @param {Function} callback The callback function
+ * @param {Object} options Options object
+ * @param {Function} cb The callback function
+ * @private
  */
-SqlConnector.prototype.find = function find(model, id, callback) {
-  if (!isIdValuePresent(id, callback, true)) {
+SQLConnector.prototype.destroy = function(model, id, options, cb) {
+  if (!isIdValuePresent(id, cb, true)) {
     return;
   }
+  var idName = this.idName(model);
+  var where = {};
+  where[idName] = id;
+  this.destroyAll(model, where, options, cb);
+};
+// Alias to `destroy`. Juggler checks `destroy` only.
+Connector.defineAliases(SQLConnector.prototype, 'destroy',
+  ['delete', 'deleteById', 'destroyById']);
+
+/**
+ * Build the `DELETE FROM` SQL statement
+ * @param {String} model The model name
+ * @param {Object} where The where object
+ * @param {Object} options Options object
+ * @returns {ParameterizedSQL} The SQL DELETE FROM statement
+ */
+SQLConnector.prototype.buildDelete = function(model, where, options) {
+  var deleteStmt = new ParameterizedSQL('DELETE FROM ' +
+    this.tableEscaped(model));
+  deleteStmt.merge(this.buildWhere(model, where));
+  return this.parameterize(deleteStmt);
+};
+
+/**
+ * Delete all matching model instances
+ *
+ * @param {String} model The model name
+ * @param {Object} where The where object
+ * @param {Object} options The options object
+ * @param {Function} cb The callback function
+ */
+SQLConnector.prototype.destroyAll = function(model, where, options, cb) {
   var self = this;
-  var idQuery = this.idColumnEscaped(model) + ' = ' + this._escapeIdValue(model, id);
-  var sql = 'SELECT * FROM ' +
-    this.tableEscaped(model) + ' WHERE ' + idQuery + ' LIMIT 1';
-
-  this.query(sql, function (err, data) {
-    var result =  (data && data.length >= 1) ? data[0] : null;
-    if (callback) callback(err, self.fromDatabase(model, result));
+  var stmt = this.buildDelete(model, where, options);
+  this.execute(stmt.sql, stmt.params, options, function(err, info) {
+    var affectedRows = self.getCountForAffectedRows(model, info);
+    if (cb) {
+      cb(err, {count: affectedRows});
+    }
   });
 };
+// Alias to `destroyAll`. Juggler checks `destroyAll` only.
+Connector.defineAliases(SQLConnector.prototype, 'destroyAll', ['deleteAll']);
 
 /**
- * Delete a model instance by id value
+ * ATM, this method is not used by loopback-datasource-juggler dao, which
+ * maps `updateAttributes` to `update` with a `where` filter that includes the
+ * `id` instead.
+ *
+ * Update attributes for a given model instance
  * @param {String} model The model name
  * @param {*} id The id value
- * @param {Function} callback The callback function
+ * @param {Object} data The model data instance containing all properties to
+ * be updated
+ * @param {Object} options Options object
+ * @param {Function} cb The callback function
+ * @private
  */
-SqlConnector.prototype.delete =
-SqlConnector.prototype.destroy = function destroy(model, id, callback) {
-  if (!isIdValuePresent(id, callback, true)) {
+SQLConnector.prototype.updateAttributes = function(model, id, data, options, cb) {
+  if (!isIdValuePresent(id, cb)) {
     return;
   }
-  var sql = 'DELETE FROM ' + this.tableEscaped(model) + ' WHERE ' +
-    this.idColumnEscaped(model) + ' = ' + this._escapeIdValue(model, id);
+  var idName = this.idName(model);
+  delete data[idName];
+  var where = {};
+  where[idName] = id;
+  this.updateAll(model, where, data, options, cb);
+};
 
-  this.command(sql, function (err, result) {
-    if (callback) callback(err, result);
+/**
+ * Build the UPDATE statement
+ * @param {String} model The model name
+ * @param {Object} where The where object
+ * @param {Object} data The data to be changed
+ * @param {Object} options The options object
+ * @param {Function} cb The callback function
+ * @returns {ParameterizedSQL} The UPDATE SQL statement
+ */
+SQLConnector.prototype.buildUpdate = function(model, where, data, options) {
+  var fields = this.buildFieldsForUpdate(model, data);
+  var updateClause = new ParameterizedSQL('UPDATE ' + this.tableEscaped(model));
+  var whereClause = this.buildWhere(model, where);
+  updateClause.merge([fields, whereClause]);
+  return this.parameterize(updateClause);
+};
+
+/**
+ * Update all instances that match the where clause with the given data
+ * @param {String} model The model name
+ * @param {Object} where The where object
+ * @param {Object} data The property/value object representing changes
+ * to be made
+ * @param {Object} options The options object
+ * @param {Function} cb The callback function
+ */
+SQLConnector.prototype.update = function(model, where, data, options, cb) {
+  var self = this;
+  var stmt = this.buildUpdate(model, where, data, options);
+  this.execute(stmt.sql, stmt.params, options, function(err, info) {
+    var affectedRows = self.getCountForAffectedRows(model, info);
+    if (cb) {
+      cb(err, {count: affectedRows});
+    }
   });
 };
+// Alias to `update`. Juggler checks `update` only.
+Connector.defineAliases(SQLConnector.prototype, 'update', ['updateAll']);
 
-SqlConnector.prototype._escapeIdValue = function(model, idValue) {
-  var idProp = this.getDataSource(model).idProperty(model);
-  if(typeof this.toDatabase === 'function') {
-    return this.toDatabase(idProp, idValue);
+/**
+ * Build the SQL WHERE clause for the where object
+ * @param {string} model Model name
+ * @param {object} where An object for the where conditions
+ * @returns {ParameterizedSQL} The SQL WHERE clause
+ */
+SQLConnector.prototype.buildWhere = function(model, where) {
+  var whereClause = this._buildWhere(model, where);
+  if (whereClause.sql) {
+    whereClause.sql = 'WHERE ' + whereClause.sql;
+  }
+  return whereClause;
+};
+
+/**
+ * Build SQL expression
+ * @param {String} columnName Escaped column name
+ * @param {String} operator SQL operator
+ * @param {*} columnValue Column value
+ * @param {*} propertyValue Property value
+ * @returns {ParameterizedSQL} The SQL expression
+ */
+SQLConnector.prototype.buildExpression = function(columnName, operator, columnValue, propertyValue) {
+  function buildClause(columnValue, separator, grouping) {
+    var values = [];
+    for (var i = 0, n = columnValue.length; i < n; i++) {
+      if (columnValue instanceof ParameterizedSQL) {
+        values.push(columnValue[i]);
+      } else {
+        values.push(new ParameterizedSQL(PLACEHOLDER, [columnValue[i]]));
+      }
+    }
+    separator = separator || ',';
+    var clause = ParameterizedSQL.join(values, separator);
+    if (grouping) {
+      clause.sql = '(' + clause.sql + ')';
+    }
+    return clause;
+  }
+
+  var sqlExp = columnName;
+  var clause;
+  if (columnValue instanceof ParameterizedSQL) {
+    clause = columnValue;
   } else {
-    if(idProp.type === Number) {
-      return idValue;
+    clause = new ParameterizedSQL(PLACEHOLDER, [columnValue]);
+  }
+  switch (operator) {
+    case 'gt':
+      sqlExp += '>';
+      break;
+    case 'gte':
+      sqlExp += '>=';
+      break;
+    case 'lt':
+      sqlExp += '<';
+      break;
+    case 'lte':
+      sqlExp += '<=';
+      break;
+    case 'between':
+      sqlExp += ' BETWEEN ';
+      clause = buildClause(columnValue, ' AND ', false);
+      break;
+    case 'inq':
+      sqlExp += ' IN ';
+      clause = buildClause(columnValue, ',', true);
+      break;
+    case 'nin':
+      sqlExp += ' NOT IN ';
+      clause = buildClause(columnValue, ',', true);
+      break;
+    case 'neq':
+      if (columnValue == null) {
+        return new ParameterizedSQL(sqlExp + ' IS NOT NULL');
+      }
+      sqlExp += '!=';
+      break;
+    case 'like':
+      sqlExp += ' LIKE ';
+      break;
+    case 'nlike':
+      sqlExp += ' NOT LIKE ';
+      break;
+  }
+  var stmt = ParameterizedSQL.join([sqlExp, clause], '');
+  return stmt;
+};
+
+/*!
+ * @param model
+ * @param where
+ * @returns {ParameterizedSQL}
+ * @private
+ */
+SQLConnector.prototype._buildWhere = function(model, where) {
+  if (!where) {
+    return new ParameterizedSQL('');
+  }
+  if (typeof where !== 'object' || Array.isArray(where)) {
+    debug('Invalid value for where: %j', where);
+    return new ParameterizedSQL('');
+  }
+  var self = this;
+  var props = self.getModelDefinition(model).properties;
+
+  var whereStmts = [];
+  for (var key in where) {
+    var stmt = new ParameterizedSQL('', []);
+    // Handle and/or operators
+    if (key === 'and' || key === 'or') {
+      var branches = [];
+      var branchParams = [];
+      var clauses = where[key];
+      if (Array.isArray(clauses)) {
+        for (var i = 0, n = clauses.length; i < n; i++) {
+          var stmtForClause = self._buildWhere(model, clauses[i]);
+          stmtForClause.sql = '(' + stmtForClause.sql + ')';
+          branchParams = branchParams.concat(stmtForClause.params);
+          branches.push(stmtForClause.sql);
+        }
+        stmt.merge({
+          sql: branches.join(' ' + key.toUpperCase() + ' '),
+          params: branchParams
+        });
+        whereStmts.push(stmt);
+        continue;
+      }
+      // The value is not an array, fall back to regular fields
+    }
+    var columnName = self.columnEscaped(model, key);
+    var expression = where[key];
+    var columnValue;
+    var sqlExp;
+    if (expression === null || expression === undefined) {
+      stmt.merge(columnName + ' IS NULL');
+    } else if (expression &&
+      (typeof expression === 'object' && !Array.isArray(expression))) {
+      var operator = Object.keys(expression)[0];
+      // Get the expression without the operator
+      expression = expression[operator];
+      if (operator === 'inq' || operator === 'nin' || operator === 'between') {
+        columnValue = [];
+        if (Array.isArray(expression)) {
+          // Column value is a list
+          for (var j = 0, m = expression.length; j < m; j++) {
+            columnValue.push(this.toColumnValue(props[key], expression[j]));
+          }
+        } else {
+          columnValue.push(this.toColumnValue(props[key], expression));
+        }
+        if (operator === 'between') {
+          // BETWEEN v1 AND v2
+          var v1 = columnValue[0] === undefined ? null : columnValue[0];
+          var v2 = columnValue[1] === undefined ? null : columnValue[1];
+          columnValue = [v1, v2];
+        } else {
+          // IN (v1,v2,v3) or NOT IN (v1,v2,v3)
+          if (columnValue.length === 0) {
+            if (operator === 'inq') {
+              columnValue = [null];
+            } else {
+              // nin () is true
+              continue;
+            }
+          }
+        }
+      } else {
+        columnValue = this.toColumnValue(props[key], expression);
+      }
+      sqlExp = self.buildExpression(
+        columnName, operator, columnValue, props[key]);
+      stmt.merge(sqlExp);
     } else {
-      return '\'' + idValue + '\'';
+      // The expression is the field value, not a condition
+      columnValue = self.toColumnValue(props[key], expression);
+      if (columnValue === null) {
+        stmt.merge(columnName + ' IS NULL');
+      } else {
+        if (columnValue instanceof ParameterizedSQL) {
+          stmt.merge(columnName + '=').merge(columnValue);
+        } else {
+          stmt.merge({
+            sql: columnName + '=?',
+            params: [columnValue]
+          });
+        }
+      }
     }
+    whereStmts.push(stmt);
   }
+  var params = [];
+  var sqls = [];
+  for (var k = 0, s = whereStmts.length; k < s; k++) {
+    sqls.push(whereStmts[k].sql);
+    params = params.concat(whereStmts[k].params);
+  }
+  var whereStmt = new ParameterizedSQL({
+    sql: sqls.join(' AND '),
+    params: params
+  });
+  return whereStmt;
 };
 
-function buildWhere(self, model, where) {
-  if (typeof self.buildWhere === 'function') {
-    return self.buildWhere(model, where);
-  } else {
-    var props = self._models[model].properties;
-    var cs = [];
-    Object.keys(where || {}).forEach(function (key) {
-      var keyEscaped = self.columnEscaped(model, key);
-      if (where[key] === null) {
-        cs.push(keyEscaped + ' IS NULL');
-      } else {
-        cs.push(keyEscaped + ' = ' + self.toDatabase(props[key], where[key]));
+/**
+ * Build the ORDER BY clause
+ * @param {string} model Model name
+ * @param {string[]} order An array of sorting criteria
+ * @returns {string} The ORDER BY clause
+ */
+SQLConnector.prototype.buildOrderBy = function(model, order) {
+  if (!order) {
+    return '';
+  }
+  var self = this;
+  if (typeof order === 'string') {
+    order = [order];
+  }
+  var clauses = [];
+  for (var i = 0, n = order.length; i < n; i++) {
+    var t = order[i].split(/[\s,]+/);
+    if (t.length === 1) {
+      clauses.push(self.columnEscaped(model, order[i]));
+    } else {
+      clauses.push(self.columnEscaped(model, t[0]) + ' ' + t[1]);
+    }
+  }
+  return 'ORDER BY ' + clauses.join(',');
+};
+
+/**
+ * Build an array of fields for the database operation
+ * @param {String} model Model name
+ * @param {Object} data Model data object
+ * @param {Boolean} excludeIds Exclude id properties or not, default to false
+ * @returns {{names: Array, values: Array, properties: Array}}
+ */
+SQLConnector.prototype.buildFields = function(model, data, excludeIds) {
+  var fields = {
+    names: [], // field names
+    columnValues: [], // an array of ParameterizedSQL
+    properties: [] // model properties
+  };
+  var props = this.getModelDefinition(model).properties;
+  var keys = Object.keys(data);
+  for (var i = 0, n = keys.length; i < n; i++) {
+    var key = keys[i];
+    var p = props[key];
+    if (excludeIds && p.id) {
+      continue;
+    }
+    if (p) {
+      var k = this.columnEscaped(model, key);
+      var v = this.toColumnValue(p, data[key]);
+      if (v !== undefined) {
+        fields.names.push(k);
+        if (v instanceof ParameterizedSQL) {
+          fields.columnValues.push(v);
+        } else {
+          fields.columnValues.push(new ParameterizedSQL(PLACEHOLDER, [v]));
+        }
+        fields.properties.push(p);
       }
-    });
-    return cs.length ? ' WHERE ' + cs.join(' AND ') : '';
+    }
   }
-}
+  return fields;
+};
 
 /**
- * Delete all model instances
- *
- * @param {String} model The model name
- * @param {Function} callback The callback function
+ * Build the SET clause for database update
+ * @param {String} model Model na
+ * @param {Object} data The model data object
+ * @param {Boolean} excludeIds Exclude id properties or not, default to true
+ * @returns {string} The list of fields for update
  */
-SqlConnector.prototype.deleteAll =
-  SqlConnector.prototype.destroyAll = function destroyAll(model, where, callback) {
-    this.command('DELETE FROM ' + this.tableEscaped(model) +
-      buildWhere(this, model, where), function (err, result) {
-      if (callback) {
-        callback(err, result);
+SQLConnector.prototype.buildFieldsForUpdate = function(model, data, excludeIds) {
+  if (excludeIds === undefined) {
+    excludeIds = true;
+  }
+  var fields = this.buildFields(model, data, excludeIds);
+  var columns = new ParameterizedSQL('');
+  for (var i = 0, n = fields.names.length; i < n; i++) {
+    var clause = ParameterizedSQL.append(fields.names[i],
+      fields.columnValues[i], '=');
+    columns.merge(clause, ',');
+  }
+  columns.sql = 'SET ' + columns.sql;
+  return columns;
+};
+
+/**
+ * Build a list of escaped column names for the given model and fields filter
+ * @param {string} model Model name
+ * @param {object} filter The filter object
+ * @returns {string} Comma separated string of escaped column names
+ */
+SQLConnector.prototype.buildColumnNames = function(model, filter) {
+  var fieldsFilter = filter && filter.fields;
+  var cols = this.getModelDefinition(model).properties;
+  if (!cols) {
+    return '*';
+  }
+  var self = this;
+  var keys = Object.keys(cols);
+  if (Array.isArray(fieldsFilter) && fieldsFilter.length > 0) {
+    // Not empty array, including all the fields that are valid properties
+    keys = fieldsFilter.filter(function(f) {
+      return cols[f];
+    });
+  } else if ('object' === typeof fieldsFilter &&
+    Object.keys(fieldsFilter).length > 0) {
+    // { field1: boolean, field2: boolean ... }
+    var included = [];
+    var excluded = [];
+    keys.forEach(function(k) {
+      if (fieldsFilter[k]) {
+        included.push(k);
+      } else if ((k in fieldsFilter) && !fieldsFilter[k]) {
+        excluded.push(k);
       }
     });
-  };
+    if (included.length > 0) {
+      keys = included;
+    } else if (excluded.length > 0) {
+      excluded.forEach(function(e) {
+        var index = keys.indexOf(e);
+        keys.splice(index, 1);
+      });
+    }
+  }
+  var names = keys.map(function(c) {
+    return self.columnEscaped(model, c);
+  });
+  return names.join(',');
+};
 
 /**
- * Count all model instances by the where filter
+ * Build a SQL SELECT statement
+ * @param {String} model Model name
+ * @param {Object} filter Filter object
+ * @param {Object} options Options object
+ * @returns {ParameterizedSQL} Statement object {sql: ..., params: [...]}
+ */
+SQLConnector.prototype.buildSelect = function(model, filter, options) {
+  if (!filter.order) {
+    var idNames = this.idNames(model);
+    if (idNames && idNames.length) {
+      filter.order = idNames;
+    }
+  }
+
+  var selectStmt = new ParameterizedSQL('SELECT ' +
+    this.buildColumnNames(model, filter) +
+    ' FROM ' + this.tableEscaped(model)
+  );
+
+  if (filter) {
+
+    if (filter.where) {
+      var whereStmt = this.buildWhere(model, filter.where);
+      selectStmt.merge(whereStmt);
+    }
+
+    if (filter.order) {
+      selectStmt.merge(this.buildOrderBy(model, filter.order));
+    }
+
+    if (filter.limit || filter.skip || filter.offset) {
+      selectStmt = this.applyPagination(
+        model, selectStmt, filter);
+    }
+
+  }
+  return this.parameterize(selectStmt);
+};
+
+/**
+ * Transform the row data into a model data object
+ * @param {string} model Model name
+ * @param {object} rowData An object representing the row data from DB
+ * @returns {object} Model data object
+ */
+SQLConnector.prototype.fromRow = SQLConnector.prototype.fromDatabase = function(model, rowData) {
+  if (rowData == null) {
+    return rowData;
+  }
+  var props = this.getModelDefinition(model).properties;
+  var data = {};
+  for (var p in props) {
+    var columnName = this.column(model, p);
+    // Load properties from the row
+    var columnValue = this.fromColumnValue(props[p], rowData[columnName]);
+    if (columnValue !== undefined) {
+      data[p] = columnValue;
+    }
+  }
+  return data;
+};
+
+/**
+ * Find matching model instances by the filter
  *
+ * Please also note the name `all` is confusing. `Model.find` is to find all
+ * matching instances while `Model.findById` is to find an instance by id. On
+ * the other hand, `Connector.prototype.all` implements `Model.find` while
+ * `Connector.prototype.find` implements `Model.findById` due to the `bad`
+ * naming convention we inherited from juggling-db.
+ *
  * @param {String} model The model name
- * @param {Function} callback The callback function
- * @param {Object} where The where clause
+ * @param {Object} filter The filter
+ * @param {Function} [cb] The cb function
  */
-SqlConnector.prototype.count = function count(model, callback, where) {
+SQLConnector.prototype.all = function find(model, filter, options, cb) {
   var self = this;
-
-  var whereClause = buildWhere(self, model, where);
-  this.queryOne('SELECT count(*) as cnt FROM ' +
-    this.tableEscaped(model) + ' ' + whereClause, function (err, res) {
+  // Order by id if no order is specified
+  filter = filter || {};
+  var stmt = this.buildSelect(model, filter, options);
+  this.execute(stmt.sql, stmt.params, options, function(err, data) {
     if (err) {
-      return callback(err);
+      return cb(err, []);
     }
-    callback(err, res && res.cnt);
+
+    var objs = data.map(function(obj) {
+      return self.fromRow(model, obj);
+    });
+    if (filter && filter.include) {
+      self.getModelDefinition(model).model.include(objs, filter.include, cb);
+    } else {
+      cb(null, objs);
+    }
   });
 };
+// Alias to `all`. Juggler checks `all` only.
+Connector.defineAliases(SQLConnector.prototype, 'all', ['findAll']);
 
 /**
- * Update attributes for a given model instance
- * @param {String} model The model name
+ * ATM, this method is not used by loopback-datasource-juggler dao, which
+ * maps `findById` to `find` with a `where` filter that includes the `id`
+ * instead.
+ *
+ * Please also note the name `find` is confusing. `Model.find` is to find all
+ * matching instances while `Model.findById` is to find an instance by id. On
+ * the other hand, `Connector.prototype.find` is for `findById` and
+ * `Connector.prototype.all` is for `find` due the `bad` convention used by
+ * juggling-db.
+ *
+ * Find by id
+ * @param {String} model The Model name
  * @param {*} id The id value
- * @param {Object} data The model data instance containing all properties to be updated
+ * @param {Object} options The options object
  * @param {Function} cb The callback function
+ * @private
  */
-SqlConnector.prototype.updateAttributes = function updateAttributes(model, id, data, cb) {
-  if (!isIdValuePresent(id, cb)) {
+SQLConnector.prototype.find = function(model, id, options, cb) {
+  if (id == null) {
+    process.nextTick(function() {
+      var err = new Error('id value is required');
+      if (cb) {
+        cb(err);
+      }
+    });
     return;
   }
-  var idName = this.getDataSource(model).idName(model);
-  delete data[idName];
   var where = {};
+  var idName = this.idName(model);
   where[idName] = id;
-  this.updateAll(model, where, data, cb);
+
+  var filter = {limit: 1, offset: 0, order: idName, where: where};
+  return this.all(model, filter, options, function(err, results) {
+    cb(err, (results && results[0]) || null);
+  });
 };
+// Alias to `find`. Juggler checks `findById` only.
+Connector.defineAliases(SQLConnector.prototype, 'find', ['findById']);
 
 /**
- * Disconnect from the connector
+ * Count all model instances by the where filter
+ *
+ * @param {String} model The model name
+ * @param {Object} where The where object
+ * @param {Object} options The options object
+ * @param {Function} cb The callback function
  */
-SqlConnector.prototype.disconnect = function disconnect() {
-  // No operation
+SQLConnector.prototype.count = function(model, where, options, cb) {
+  if (typeof where === 'function') {
+    // Backward compatibility for 1.x style signature:
+    // count(model, cb, where)
+    var tmp = options;
+    cb = where;
+    where = tmp;
+  }
+
+  var stmt = new ParameterizedSQL('SELECT count(*) as "cnt" FROM ' +
+    this.tableEscaped(model));
+  stmt = stmt.merge(this.buildWhere(model, where));
+  stmt = this.parameterize(stmt);
+  this.execute(stmt.sql, stmt.params,
+    function(err, res) {
+      if (err) {
+        return cb(err);
+      }
+      var c = (res && res[0] && res[0].cnt) || 0;
+      // Some drivers return count as a string to contain bigint
+      // See https://github.com/brianc/node-postgres/pull/427
+      cb(err, Number(c));
+    });
 };
 
 /**
+ * Drop the table for the given model from the database
+ * @param {String} model The model name
+ * @param {Function} [cb] The callback function
+ */
+SQLConnector.prototype.dropTable = function(model, cb) {
+  this.execute('DROP TABLE IF EXISTS ' + this.tableEscaped(model), cb);
+};
+
+/**
+ * Create the table for the given model
+ * @param {String} model The model name
+ * @param {Function} [cb] The callback function
+ */
+SQLConnector.prototype.createTable = function(model, cb) {
+  var sql = 'CREATE TABLE ' + this.tableEscaped(model) +
+    ' (\n  ' + this.buildColumnDefinitions(model) + '\n)';
+  this.execute(sql, cb);
+};
+
+/**
  * Recreate the tables for the given models
@@ -375,3 +1160,3 @@ * @param {[String]|String} [models] A model name or an array of model names,
  */
-SqlConnector.prototype.automigrate = function (models, cb) {
+SQLConnector.prototype.automigrate = function(models, cb) {
   var self = this;
@@ -394,4 +1179,4 @@
   var invalidModels = models.filter(function(m) {
-      return !(m in self._models);
-    });
+    return !(m in self._models);
+  });
   if (invalidModels.length) {
@@ -422,40 +1207,139 @@ return process.nextTick(function() {
 /**
- * Drop the table for the given model from the database
- * @param {String} model The model name
- * @param {Function} [cb] The callback function
+ * Serialize an object into JSON string or other primitive types so that it
+ * can be saved into a RDB column
+ * @param {Object} obj The object value
+ * @returns {*}
  */
-SqlConnector.prototype.dropTable = function (model, cb) {
-  this.command('DROP TABLE IF EXISTS ' + this.tableEscaped(model), cb);
+SQLConnector.prototype.serializeObject = function(obj) {
+  var val;
+  if (obj && typeof obj.toJSON === 'function') {
+    obj = obj.toJSON();
+  }
+  if (typeof obj !== 'string') {
+    val = JSON.stringify(obj);
+  } else {
+    val = obj;
+  }
+  return val;
 };
 
+/*!
+ * @param obj
+ */
+SQLConnector.prototype.escapeObject = function(obj) {
+  var val = this.serializeObject(obj);
+  return this.escapeValue(val);
+};
+
 /**
- * Create the table for the given model
- * @param {String} model The model name
- * @param {Function} [cb] The callback function
+ * The following _abstract_ methods have to be implemented by connectors that
+ * extend from SQLConnector to reuse the base implementations of CRUD methods
+ * from SQLConnector
  */
 
-SqlConnector.prototype.createTable = function (model, cb) {
-  this.command('CREATE TABLE ' + this.tableEscaped(model) +
-    ' (\n  ' + this.propertiesSQL(model) + '\n)', cb);
+/**
+ * Converts a model property value into the form required by the
+ * database column. The result should be one of following forms:
+ *
+ * - {sql: "point(?,?)", params:[10,20]}
+ * - {sql: "'John'", params: []}
+ * - "John"
+ *
+ * @param {Object} propertyDef Model property definition
+ * @param {*} value Model property value
+ * @returns {ParameterizedSQL|*} Database column value.
+ *
+ */
+SQLConnector.prototype.toColumnValue = function(propertyDef, value) {
+  throw new Error('toColumnValue() must be implemented by the connector');
 };
 
 /**
- * Update all instances that match the where clause with the given data
+ * Convert the data from database column to model property
+ * @param {object} propertyDef Model property definition
+ * @param {*) value Column value
+ * @returns {*} Model property value
+ */
+SQLConnector.prototype.fromColumnValue = function(propertyDef, value) {
+  throw new Error('fromColumnValue() must be implemented by the connector');
+};
+
+/**
+ * Escape the name for the underlying database
+ * @param {String} name The name
+ * @returns {String} An escaped name for SQL
+ */
+SQLConnector.prototype.escapeName = function(name) {
+  throw new Error('escapeName() must be implemented by the connector');
+};
+
+/**
+ * Escape the name for the underlying database
+ * @param {String} value The value to be escaped
+ * @returns {*} An escaped value for SQL
+ */
+SQLConnector.prototype.escapeValue = function(value) {
+  throw new Error('escapeValue() must be implemented by the connector');
+};
+
+/**
+ * Get the place holder in SQL for identifiers, such as ??
+ * @param {String} key Optional key, such as 1 or id
+ * @returns {String} The place holder
+ */
+SQLConnector.prototype.getPlaceholderForIdentifier = function(key) {
+  throw new Error('getPlaceholderForIdentifier() must be implemented by the connector');
+};
+
+/**
+ * Get the place holder in SQL for values, such as :1 or ?
+ * @param {String} key Optional key, such as 1 or id
+ * @returns {String} The place holder
+ */
+SQLConnector.prototype.getPlaceholderForValue = function(key) {
+  throw new Error('getPlaceholderForValue() must be implemented by the connector');
+};
+
+/**
+ * Build a new SQL statement with pagination support by wrapping the given sql
  * @param {String} model The model name
- * @param {Object} data The property/value object representing changes to be made
- * @param {Function} callback The callback function
+ * @param {ParameterizedSQL} stmt The sql statement
+ * @param {Number} limit The maximum number of records to be fetched
+ * @param {Number} offset The offset to start fetching records
+ * @param {String[]} order The sorting criteria
  */
-SqlConnector.prototype.update =
-  SqlConnector.prototype.updateAll = function (model, where, data, callback) {
-    var whereClause = buildWhere(this, model, where);
+SQLConnector.prototype.applyPagination = function(model, stmt, filter) {
+  throw new Error('applyPagination() must be implemented by the connector');
+};
 
-    var sql = 'UPDATE ' + this.tableEscaped(model) + ' SET ' +
-      this.toFields(model, data) + ' ' + whereClause;
+/**
+ * Parse the result for SQL UPDATE/DELETE/INSERT for the number of rows
+ * affected
+ * @param {String} model Model name
+ * @param {Object} info Status object
+ * @returns {Number} Number of rows affected
+ */
+SQLConnector.prototype.getCountForAffectedRows = function(model, info) {
+  throw new Error('getCountForAffectedRows() must be implemented by the connector');
+};
 
-    this.query(sql, function (err, result) {
-      if (callback) {
-        callback(err, result);
-      }
-    });
-  };
+/**
+ * Parse the result for SQL INSERT for newly inserted id
+ * @param {String} model Model name
+ * @param {Object} info The status object from driver
+ * @returns {*} The inserted id value
+ */
+SQLConnector.prototype.getInsertedId = function(model, info) {
+  throw new Error('getInsertedId() must be implemented by the connector');
+};
 
+/**
+ * Execute a SQL statement with given parameters
+ * @param {String} sql The SQL statement
+ * @param {*[]} [params] An array of parameter values
+ * @param {Object} [options] Options object
+ * @param {Function} [callback] The callback function
+ */
+SQLConnector.prototype.executeSQL = function(sql, params, options, callback) {
+  throw new Error('executeSQL() must be implemented by the connector');
+};

package.json

 {
   "name": "loopback-connector",
-  "version": "1.2.1",
+  "version": "2.0.0",
   "description": "Building blocks for LoopBack connectors",
@@ -24,10 +24,11 @@ "keywords": [
   "dependencies": {
-    "async": "^0.9.0"
+    "async": "^0.9.0",
+    "debug": "^2.1.3"
   },
   "devDependencies": {
-    "chai": "~1.9.2",
-    "jshint": "^2.6.0",
-    "loopback-datasource-juggler": "^2.0.0",
-    "mocha": "^1.19.0"
+    "chai": "~2.3.0",
+    "jshint": "^2.7.0",
+    "loopback-datasource-juggler": "^2.27.0",
+    "mocha": "^2.2.4"
   }
 }

README.md

@@ -7,3 +7,3 @@ # LoopBack Connector
 **For full documentation, see the official StrongLoop documentation**:
- * [Data sources and connectors](http://docs.strongloop.com/display/DOC/Data+sources+and+connectors)
+ * [Data sources and connectors](http://docs.strongloop.com/display/public/LB/Database+connectors)
 
@@ -10,0 +10,0 @@ ## Installation

test/connectors/test-sql-connector.js

@@ -5,8 +5,8 @@ /*
 var util = require('util');
-var SqlConnector = require('../../lib/sql');
+var SQLConnector = require('../../lib/sql');
 
 exports.initialize = function initializeDataSource(dataSource, callback) {
   process.nextTick(function() {
-    if(callback) {
-      var connector = new TestConnector();
+    if (callback) {
+      var connector = new TestConnector(dataSource.settings);
       connector.dataSource = dataSource;
@@ -19,9 +19,62 @@ dataSource.connector = connector;
 
-function TestConnector() {
-  SqlConnector.apply(this, [].slice.call(arguments));
+function TestConnector(settings) {
+  SQLConnector.call(this, 'testdb', settings);
   this._tables = {};
 }
 
-util.inherits(TestConnector, SqlConnector);
+util.inherits(TestConnector, SQLConnector);
 
+TestConnector.prototype.escapeName = function(name) {
+  return '`' + name + '`';
+};
+
+TestConnector.prototype.dbName = function(name) {
+  return name.toUpperCase();
+};
+
+TestConnector.prototype.getPlaceholderForValue = function(key) {
+  return '$' + key;
+};
+
+TestConnector.prototype.escapeValue = function(value) {
+  if (typeof value === 'number' || typeof value === 'boolean') {
+    return value;
+  }
+  if (typeof value === 'string') {
+    return "'" + value + "'";
+  }
+  if (value == null) {
+    return 'NULL';
+  }
+  if (typeof value === 'object') {
+    return String(value);
+  }
+  return value;
+};
+
+TestConnector.prototype.toColumnValue = function(prop, val) {
+  return val;
+};
+
+TestConnector.prototype._buildLimit = function(model, limit, offset) {
+  if (isNaN(limit)) {
+    limit = 0;
+  }
+  if (isNaN(offset)) {
+    offset = 0;
+  }
+  if (!limit && !offset) {
+    return '';
+  }
+  return 'LIMIT ' + (offset ? (offset + ',' + limit) : limit);
+};
+
+TestConnector.prototype.applyPagination =
+  function(model, stmt, filter) {
+    /*jshint unused:false */
+    var limitClause = this._buildLimit(model, filter.limit,
+      filter.offset || filter.skip);
+    return stmt.merge(limitClause);
+  };
+
 TestConnector.prototype.dropTable = function(model, cb) {
@@ -52,1 +105,5 @@ var err;
 };
+
+TestConnector.prototype.executeSQL = function(sql, params, options, callback) {
+  callback(null, []);
+};

test/smoke.test.js

@@ -12,2 +12,20 @@ var assert = require('assert');
   });
+
+  it('exports SQLConnector', function() {
+    assert(connector.SQLConnector);
+  });
+
+  it('creates aliases to Connector.prototype.execute', function() {
+    assert.equal(connector.Connector.prototype.execute,
+      connector.Connector.prototype.query);
+    assert.equal(connector.Connector.prototype.execute,
+      connector.Connector.prototype.command);
+  });
+
+  it('creates aliases to SQLConnector.prototype.execute', function() {
+    assert.equal(connector.SQLConnector.prototype.execute,
+      connector.SQLConnector.prototype.query);
+    assert.equal(connector.SQLConnector.prototype.execute,
+      connector.SQLConnector.prototype.command);
+  });
 });

New alerts

Major refactor

Supply chain risk

Package has recently undergone a major refactor. It may be unstable or indicate significant internal changes. Use caution when updating to versions that include significant changes.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

339139

increased by561.71%

Number of package files

20

increased by42.86%

Lines of code

2031

increased by216.36%

Worsened metrics

Dependency count

2

increased by100%

Number of medium supply chain risk alerts

1

increased byInfinity%

Dependency changes

• + Addeddebug@^2.1.3

• + Addeddebug@2.6.9(transitive)

• + Addedms@2.0.0(transitive)