Two really cool Node MySQL tips

Node MySQL is a great traditional alternative to mongo and all the jazz youngins are using. One important advice - never use + to concatenate queries unless you know what you're doing.

1. Always escape using ? as placeholders

Queries are usually written as:

connection.query('SELECT * FROM foo WHERE bar = baz', function(err, results) {  
    // ...
});

If you want to check against a custom property, don't do this.

connection.query('SELECT * FROM foo WHERE bar = ' + someVariable, function(err, results) {  
    // ...
});

Instead,

connection.query('SELECT * FROM foo WHERE bar = ?', [someVariable], function(err, results) {  
    // ...
});

You can use multiple ? like so:

connection.query('SELECT * FROM foo WHERE ? = ?', [someProperty, someValue], function(err, results) {  
    // ...
});

2. Use the SET ? syntax

Node MySQL converts objects from { a: 'b' } to a = 'b' when escaped. Insertions with objects is thus easy:

var user = { id: 42, name: "Namanyay Goel" };  
connection.query('INSERT INTO users SET ?`, user, function(err, result) {  
    // ... 
}); 

Then you never have to do this


Learn more about Node MySQL's escaping

Discuss on Twitter