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'

What port is your sql server listening on? Most likely it is not 3000 which is what your driver is trying to hit. — NG., Dec 24 '13 at 02:30

score 3 · Answer 1 · answered May 02 '15 at 05:22

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...
});

ztirom · Answer 2 · 2013-12-24T02:54:09.550

1

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
});

edited Dec 24 '13 at 02:54

answered Dec 24 '13 at 02:26

ztirom

4,382
3
28
39

Thanks! How do I figure out what that port is? Or can I literally write that string? – Aaron Clayton-Dunn Dec 24 '13 at 02:29
You sure? He calls `app.set('port', process.env.PORT || 3000);` earlier. – NG. Dec 24 '13 at 02:29
I think you see your port at phpMyAdmin for example or where you started your MySQL server. @AaronClayton-Dunn – ztirom Dec 24 '13 at 02:32
@SB. he started his http with app.get('port'), I don't think it's the same as his MySQL server. But without any errors, I can just guess that there is a problem. – ztirom Dec 24 '13 at 02:34
it's 3306 by default, so you need to use `port: 3306` (or don't specify port at all) – Andrey Sidorov Dec 24 '13 at 02:50
Thanks everyone. What is the best/easiest way to set up a local mysql server? – Aaron Clayton-Dunn Dec 24 '13 at 03:24
This depends a bit of your used OS. On _Mac OS_ I prefer **MAMP** http://www.mamp.info/de/mamp/index.html for testing, on _Linux_ you can follow different (e.g. with **apt-get** install) tutorials like this one http://ariejan.net/2007/12/12/how-to-install-mysql-on-ubuntudebian/ and on a _Windows_ you can look here at **XAMPP** http://www.apachefriends.org/de/xampp.html – ztirom Dec 24 '13 at 10:32

score 1 · Answer 3 · answered Dec 24 '13 at 03:15

You might want to read a few tutorials on MySQL in general before jumping into it with Node, but in case you feel adventurous, here's a very brief tutorial on how I'd go about setting up MySQL on my machine.

I am assuming that you have access to the MySQL shell. If you're using a frontend the process may be different.

After installing MySQL and configuring an account, you'd want to log into the MySQL shell with the command

mysql -u me -p

followed by the password for the MySQL user when prompted.

Next, you'll want to create a database. You'd do this with the command

create database mydatabase;

then

use mydatabase;

Next you'll want to create a table. Run the CREATE TABLE query in the shell to set up a table. You could do this in Node, but then you'd be running the command needlessly every time you started the app.

Now you should be able to connect using node-mysql

var connection = mysql.createConnection({
  user     : 'me',
  password : 'secret',
  host     : 'localhost',
  database : 'mydatabase',
  port     :  3306 // or whatever your mysql port is
});

score 0 · Answer 4 · answered May 07 '15 at 07:39

I was facing same issue so how i solve it...

Mysql Part

1-I have already installed wamp and can access to phpmyadmin.
2-Using phpmyadmin i have created a database and a table say it users(please insert 2,3 rows data using insert menu option) look like 
check 

> http://prntscr.com/72gsxc

3-Make sure wamp is running and mysql service is also running. 
4-Click wamp and mouseover MYSQL and then click my.ini and search there port by default it is 3306.

Node Part



    var pool =  mysql.createPool({
        host     : 'localhost',
        user     : 'root',
        password : '',
        database : 'cricstream_2',
        port     : 3306
    });

    var readTable = 'SELECT * FROM countries';

        pool.getConnection(function(error, connection){ 
          connection.query(readTable, function(err, rows){
            if(err) throw err;
            else { 
                console.log(rows);
            }
          });
         });

score 0 · Answer 5 · answered Jun 10 '15 at 02:27

Do I have to create a database and then create a table?

Yes, you should create the database and the table ahead of time, that is correct.

Is there a way to create a database elsewhere so it doesn't get erased every time I restart the app?

Yes, you typically install MySQL server on a computer, this installation manages a run of databases, which you can remove/backup/duplicate etc, and create new databases. Your NodeJS app would just be making a connection to your MySQL server installation.

How to make node.js and mysql work together?

5 Answers5

Mysql Part

Node Part