WHERE should be aware that it exists and use and in place.

[devdazed]

Currently if you invoke where multiple times, it will create invalid SQL that has the keyword WHERE multiple times. Given the scenario where any parameter is optional, it is hard to tell when WHERE exists.

function query(params){
  var q = db.query().select('*').from('foo');

  if(params.id){
    q.where('id=' + id);
  }

  if(params.date){
    q.where('date='+date);
  }
}

As you can see, this creates the problem of not knowing if the WHERE clause has been used and it can get pretty messy trying to keep track if you have a lot of variables.

This prototype of Query is one suggestion to get around it, i used ActiveRecord's default way of using AND and letting the user specify if OR is required;

var MySQL = require('db-mysql'),
    where = MySQL.Query.prototype.where;

MySQL.Query.prototype.where = function(){
  var args = Array.prototype.slice.call(arguments);

  if(this.hasWhere){
    MySQL.Query.prototype.and.apply(this, args);
  } else {
    where.apply(this, args);
    this.hasWhere = true;
  }
};

This method can be used in any of the other methods that chain, like GROUP, JOIN and ORDER