Dynamic variables with node-mysql 'WHERE' statement?

Question

I'm using node-mysql in my node.js server.

Problem is, I'm trying to pass a javascript variable into my WHERE sql statement and it's not picking it up (for obvious reasons). I've looked through node-mysql documentation, including the SET method, however this won't work as the variable needs to come from javascript code as i would like it to be dynamically inputted by the user.

Any ideas? Code below..

api.js

         var mysql = require('mysql');

            var connection = mysql.createConnection({
                host: 'my server',
                user: 'username',
                password: 'password',
                database: 'fruits'
            });
            connection.connect()

      var blueberriesWeight = "100"

            connection.query('SELECT fruit_price FROM fruit WHERE fruit_weight = blueberriesWeight', 
              function(err, result){ 
            console.log(result)
            console.error(err)
            })

Answer 1

There's a few ways according to the docs:

var weight = 100;

connection.query(
  'SELECT fruit_price FROM fruit WHERE fruit_weight=' + connection.escape(weight),
  function(...

and

connection.query(
  'SELECT fruit_price FROM fruit WHERE fruit_weight=?', [weight],
  function(...

though there's a warning that all ? in the query string will be replaced.

Answer 2

Use knex. Link: http://knexjs.org/

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host     : 'my server',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'fruits'
  }
});

var blueberriesWeight = 100;
knex('fruit')
  .select('fruit_price')
  .where({
    fruit_weight: blueberriesWeight
  })
  .asCallback(function(err, rows) {
    //do stuff with the result
  });

This library makes writing javascript SQL query statements fun and easy. You can use callbacks or Promises and it is well maintained.

Should you be interested in a full ORM, check outs knex's big brother, Bookshelf