How to make node.js and mysql work together?

Question

Thanks in advance for your help!

I am making a node/express app and I want to use a mysql database. But I can't seem to connect to the database.

I know I'm supposed to use the node-mysql module (https://github.com/felixge/node-mysql), but I must be doing it wrong.

I'm completely new to this. Do I have to create a database and then create a table? Is there a way to create a database elsewhere so it doesn't get erased every time I restart the app?

Here's my code. Can someone answer the questions above and tell me what I'm doing wrong below? Thanks!

var express = require('express'),
  routes = require('./routes'),
  user = require('./routes/user'),
  http = require('http'),
  io = require('socket.io'),
  path = require('path');

var app = express();
var server = http.createServer(app);
sio = io.listen(server);

app.configure(function(){
  app.set('port', process.env.PORT || 3000);
  app.set('views', __dirname + '/views');
  app.set('view engine', 'jade');
  app.use(express.favicon());
  app.use(express.logger('dev'));
  app.use(express.bodyParser());
  app.use(express.methodOverride());
  app.use(app.router);
  app.use(require('stylus').middleware(__dirname + '/public'));
  app.use(express.static(path.join(__dirname, 'public')));
});

app.configure('development', function(){
  app.use(express.errorHandler());
});

server.listen(app.get('port'), function(){
  console.log("Express server listening on port " + app.get('port'));
});


var mysql      = require('mysql');
var connection = mysql.createConnection({
  user     : 'me',
  password : 'secret',
  host     : 'localhost',
  port     : app.get('port')
});

connection.connect();

connection.query('CREATE TABLE tesTable (integer int, textfield VARCHAR(100), PRIMARY KEY(integer))',
                  function(err, result){
                      if(err) {
                          console.log(err);
                      } else {
                          console.log("Table testTable Created");
                      }
                  });

By the way, in package.json, I listed

"mysql": "2.0.0-rc2" 

as a dependency and did

'npm install'

Answer 1

You can just create your tables in the database as you normally do and it will be persistent enough not to be deleted anytime you restart your app. You can connect to your database by using the following code if you want:

    var mysql = require('mysql');

app.use( connection(mysql, {
    host: 'myhost',
    user: 'user_name',
    password: 'password',
    port: 3306,          //port mysql
    database: 'database_name',
    multipleStatements: 'true'  //false by default

}, 'pool'));

req.getConnection(function(err, connection) {
    connection.query("SELECT * FROM `table_name`;",function (error,row){
        if(!error){
                       //do something.....
        }
        else console.log("Error : "+err);
        });
    //do something else...
});

Answer 2

My first suggestion, besides the question about any errors, is that you should try this

var mysql      = require('mysql');
var connection = mysql.createConnection({
  user     : 'me',
  password : 'secret',
  host     : 'localhost',
  port     : your_MySQL_port
});

The port: app.get('port') in your given example returns your http server port, but not the port of your MySQL server.

Check https://github.com/felixge/node-mysql#connection-options at 'port'.

To get your MySQL port to insert in your_MySQL_port on Linux or Mac OS, just open a terminal an type:

ps ax | grep mysqld

as result you will see something like --port=1234 in the generated output. In this case 1234 is your_MySQL_port.

In this exmaple your code should look like:

var mysql      = require('mysql');
var connection = mysql.createConnection({
  user     : 'me',
  password : 'secret',
  host     : 'localhost',
  port     : 1234
});