node.js - Getting data from MySql through express -

i've set database on heroku , i've created table called users 2 records, , i'm trying data node application through express.

i've set connection in app.js file:

// connect heroku database var mysql = require('mysql'); var connection = mysql.createconnection({   host     : 'us-cdbr-iron-***-**.cleardb.net',   user     : 'bfe4***0ede74',   password : '6742****',   database : 'heroku_****ee0f0e9102' }); 

i have routes folder index.js file:

var express = require('express'); var router = express.router();  /* home page. */ router.get('/', function(req, res, next) {   // res.render('layout', { title: 'movieseat' });    connection.connect();      connection.query('select * `users` `first_name` = "kees"', function (error, results, fields) {       // error error if 1 occurred during query       // results contain results of query       // fields contain information returned results fields (if any)       console.log(results);     });    connection.end(); });  module.exports = router; 

in index route i'm trying serve record of first_name kees. when visit host following error:

connection not defined

referenceerror: connection not defined

so looks connection has no reference in route file, in webstorm ide when ctrl + click on connection app.js file define connection. how reference connection in route file?

also when uncomment following line in index.js route file:

res.render('layout', { title: 'movieseat' }); 

i error:

error: can't set headers after sent. propper way request data , render jade template?

the second error because somewhere you're calling res.send() or res.end() or res.render() already, don't realize it. check middleware , on make sure you're not doing so.

the first issue because you're neither exporting connection object connection file, nor requiring in router file. have explicitly require module in order have reference it.

// connect heroku database var mysql = require('mysql'); var connection = mysql.createconnection({   host     : 'us-cdbr-iron-***-**.cleardb.net',   user     : 'bfe4***0ede74',   password : '6742****',   database : 'heroku_****ee0f0e9102' }); module.exports = connection; 

note in case, have same connection, isn't great scalability. have @ connection pooling , consider exporting method gets connection rather passing around global object.

then in router:

var express = require('express'); var router = express.router(); var connection = require('./path/to/your/connection.js');  /* home page. */ router.get('/', function(req, res, next) {   // res.render('layout', { title: 'movieseat' });    connection.connect();      connection.query('select * `users` `first_name` = "kees"', function (error, results, fields) {       // error error if 1 occurred during query       // results contain results of query       // fields contain information returned results fields (if any)       console.log(results);     });    connection.end(); });  module.exports = router;