How to use SQL with Node.JS

Question

So,I'm fairly new to the whole backend world, and I'm now coding with node.js. I have a project that I have to store a users with a few fields. But I don't know how to connect to the database with node and make queries.

And,I have the WAMP stack, because I used to code PHP (very litle), can I use its MySQL server for node.js too? Or do I need to go to the MySQL site,download and install it?

Answer 1

Rather than Sequelize, if you just need to do a little SQL access, you might be happier just using the mysql javascript driver directly.

npm install mysql

You should be able to use your existing mysql server just fine, provided you have access to it from the machine you are putting your node app on (access = network access and your mysql account access control settings.

A very simple example of usage is:

var mysql      = require('mysql');
var connection = mysql.createConnection({
    host     : 'locahost',
    user     : 'foo',
    password : 'bar',
    database : 'test'
});

// the callback inside connect is called when the connection is good
connection.connect(function(err){
    var sql = "select 'Joe' as name from dual";

    connection.query(sql, function(err, rows, fields) {
        if (err) return console.log(err);
        //  you need to end your connection inside here.
        connection.end();
        console.log(rows[0].name);
    });
});

The sql above could be any valid sql. If you want to use variable substitution, insert ? into your query and pass an array of substitutions as the second parameter to connection.query, for example:

var sql='update customer set name=? where id=?';
connection.query(sql,[req.body.name,req.body.id], function(...

You might find this question I answered helpful regarding the general structure. Please note the heavy use of callbacks. The inevitable rookie moves involve not having the callback structure correct, thereby causing portions of your code to run before necessary things have happened (i.e. your code is trying to run a query before it actually has established the database connection).

If you are going to do more with MySQL then some sort of abstracted ORM library might make more sense for you. Be aware of the performance implications though - some of them are fairly heavyweight. See this article as an example.

Hope this helps. If you have trouble, post a question, with the code causing problems, and you'll get pretty timely answers here.

Answer 2

There are a number of ways to interface with MySQL from NodeJS, you just use a framework or driver that supports it.

One popular example is SequelizeJS which supports several different databases, MySQL included.