Connection to Mysql from NodeJS on Heroku server

Question

ANy idea how can I connect from NodeJs to the Mysql ClearDB on Heroku?

I was able to connect to the ClearDB Mysql runing on Heroku from Navicat, I even created a table called t_users. But I'm having problems connecting from NodeJs from Heroku.

This is my code, it is very simple: everything works find until it tries to connect to MySQL

web.js:

var express = require("express");
var app = express();
app.use(express.logger());

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'us-cdbr-east-04.cleardb.com',
  user     : '',
  password : '',
  database : ''
});

connection.connect();

app.get('/', function(request, response) {
  response.send('Hello World!!!! HOLA MUNDOOOOO!!!');
  connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
      if (err) throw err;

      response.send('The solution is: ', rows[0].solution);
    });
});

var port = process.env.PORT || 5000;
app.listen(port, function() {
  console.log("Listening on " + port);
});

This is what I got when I run on the command line: Heroku config

C:\wamp\www\Nodejs_MySql_Heroku>heroku config
=== frozen-wave-8356 Config Vars
CLEARDB_DATABASE_URL: mysql://b32fa719432e40:[email protected]/heroku_28437b49d76cc53?reconnect=true
PATH:                 bin:node_modules/.bin:/usr/local/bin:/usr/bin:/bin

This is the LOG: http://d.pr/i/cExL

ANy idea how can I connect from NodeJs to the Mysql ClearDB on Heroku? Thanks

Solution 1

Try this. Hope this will help you

mysql://b32fa719432e40:[email protected]/heroku_28437b49d76cc53?reconnect=true

var connection = mysql.createConnection({
  host     : 'us-cdbr-east-04.cleardb.com',
  user     : 'b32fa719432e40',
  password : '87de815a',
  database : 'heroku_28437b49d76cc53'
});

Use this details and connect it in mySQL work bench, and import your localhost db

Solution 2

The base code was ok, I missed some NodeJS code.

I did a video explaining how to connect to MySqlusing NodeJS on a Heroku server, take a look:

http://www.youtube.com/watch?v=2OGHdii_42s

This is the code in case you want to see:

https://github.com/mescalito/MySql-NodeJS-Heroku

Solution 3

createConnections accepts config as well as connectionString.

export function createConnection(connectionUri: string | ConnectionConfig): Connection;

So below solution would work.

var connection = mysql.createConnection('mysql://b32fa719432e40:[email protected]/heroku_28437b49d76cc53?reconnect=true);

or you can set the connection url in environment variable DATABASE_URL

var connection = mysql.createConnection(process.env.DATABASE_URL);

Solution 4

One should need to use pool connection for database connection to handle better mysql conncurrent request as follows

var pool = mysql.createPool({
    connectionLimit : 100,
    host : 'us-cdbr-iron-east-05.cleardb.net',
    user : 'b5837b0f1d3d06',
    password : '9d9ae3d5',
    database : 'heroku_db89e2842543609',
    debug : 'false'
});

It just because pool maintain the connection.release() on its own , so you do not have to bother where you should need to release the connection.

On the other hand you should need to provide user, password and database name as i mentioned in my code. When u get provisioned from heroku then you get a cleardb database name. on clicking clear db database button you will find username and password. and to get database url you have to run the following commands in terminal as follows; heroku login heroku app -all (it shows the app list name ) heroku config --app appname (it will provide the database url).

Rest you would need to worry, just add all dependency into your package.json before pushing to heroku master. After then you will have no problem on deploying nodejs application to heroku server.

View more solutions

Comments

[Nitzan Shaked]

You included user name and password in this question. Quickly change your password.

[Daniel Tam]

Does this only work for node.js or does it work for python as well?