Here are the app.js, users.js and user.js routes of a node application I am building. As you can see I am connecting to the SQL DB in each route, and this is not ideal. I have tried passing the connection to different routes at least 3 different ways based on examples online. I have failed multiple times.
Please advise as to the best way to pass this connection. I am sure there are other errors in my code, so anywhere I can improve I would appreciate feedback. It is currently working, kinda. You can sign up login with auth and submit a bid working version here. But the server times out and the app crashes a lot.
APP.js
var express = require('express');
var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');
var exphbs = require('express-handlebars');
var expressValidator = require('express-validator');
var flash = require('connect-flash');
var session = require('express-session');
var passport = require('passport');
var LocalStrategy = require('passport-local'),Strategy;
var mongo = require('mongodb');
async = require('async');
SchemaObject = require('node-schema-object');
var app = express();
// This should make our db available in all views..
/* wrapped our database object into every request .*/
app.use(function(req,res,next){
//req.conn = conn;
req.app = app;
//req.db = db;
// handle_database(req,res);
next();
});
var routes = require('./routes/index');
var users = require('./routes/users');
var classes = require('./routes/classes');
var bids = require('./routes/bids');
//var students = require('./routes/students');
var clients = require('./routes/clients');
//var reviews = require('./routes/reviews');
var instructors = require('./routes/instructors');
var companys = require('./routes/companys');
//var instructors = require('./routes/instructors');
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.engine('handlebars', exphbs({defaultLayout: 'layout'}));
app.set('view engine', 'handlebars');
// uncomment after placing your favicon in /public
//app.use(favicon(__dirname + '/public/favicon.ico'));
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
// Express Session
app.use(session({
secret: 'secret',
saveUninitialized: true,
resave: true
}));
// Passport
app.use(passport.initialize());
app.use(passport.session());
// Express Validator
app.use(expressValidator({
errorFormatter: function(param, msg, value) {
var namespace = param.split('.')
, root = namespace.shift()
, formParam = root;
while(namespace.length) {
formParam += '[' + namespace.shift() + ']';
}
return {
param : formParam,
msg : msg,
value : value
};
}
}));
// Connect-Flash
app.use(flash());
// Global Vars
app.use(function (req, res, next) {
res.locals.messages = require('express-messages')(req, res);
if(req.url == '/'){
res.locals.isHome = true;
}
next();
});
// Makes the user object global in all views
app.get('*', function(req, res, next) {
app.use(flash());
// put user into res.locals for easy access from templates
res.locals.user = req.user || null;
if(req.user){
res.locals.type = req.user.type;
if(req.user.type == 'company'){
res.locals.isCompany = true;
}
if(req.user.type == 'client'){
res.locals.isClient = true;
}
}
next();
});
app.use('/', routes);
app.use('/users', users);
app.use('/classes', classes);
app.use('/bids', bids);
//app.use('/students', students);
app.use('/clients', clients);
//app.use('/reviews', reviews);
app.use('/instructors', instructors);
app.use('/companys', companys);
// catch 404 and forward to error handler
app.use(function(req, res, next) {
var err = new Error('Not Found');
err.status = 404;
next(err);
});
// error handlers
// development error handler
// will print stacktrace
if (app.get('env') === 'development') {
app.use(function(err, req, res, next) {
res.status(err.status || 500);
res.render('error', {
message: err.message,
error: err
});
});
}
// production error handler
// no stacktraces leaked to user
app.use(function(err, req, res, next) {
res.status(err.status || 500);
res.render('error', {
message: err.message,
error: {}
});
});
module.exports = app;
users.js route
var express = require('express');
var router = express.Router();
var passport = require('passport');
var LocalStrategy = require('passport-local').Strategy;
var db = require('../database');
var returnedPw;
var flash = require('connect-flash');
var session = require('express-session');
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'nunyabusiness',
user : '',
password : '',
database : ''
});
// Include User Model
var User = require('../models/user');
// Include Student Model
var Client = require('../models/client');
// Include Instructor Model
var Company = require('../models/company');
router.get('/signup', function(req, res, next) {
res.render('users/signup');
});
router.post('/signup', function(req, res, next){
// Get Form Values
console.log('starting post and making new user');
var first_name = req.body.first_name;
var last_name = req.body.last_name;
var street_address = req.body.street_address;
var city = req.body.city;
var state = req.body.state;
var zip = req.body.zip;
var email = req.body.email;
var username = req.body.username;
var password = req.body.password;
var password2 = req.body.password2;
var type = req.body.type;
// Form Field Validation
req.checkBody('first_name', 'First name field is required').notEmpty();
req.checkBody('last_name', 'Last name field is required').notEmpty();
req.checkBody('email', 'Email field is required').notEmpty();
req.checkBody('email', 'Email must be a valid email address').isEmail();
req.checkBody('username', 'Username field is required').notEmpty();
req.checkBody('password', 'Password field is required').notEmpty();
req.checkBody('password2', 'Passwords do not match').equals(req.body.password);
var errors = req.validationErrors();
if(errors){
res.render('users/signup', {
errors: errors,
first_name: first_name,
last_name: last_name,
street_address: street_address,
city: city,
state: state,
zip: zip,
email: email,
username: username,
password: password,
password2: password2
});
} else {
var newUser = new User({
email: email,
username:username,
password: password,
type: type
});
console.log('calling post to database file to receive new user:' + newUser)
// THIS IS WHERE WE ARE POSTING THE NEW USER TO THE DATABASE!!!
//db.registerUsers(newUser);
User.saveUser(newUser);
var newClient = new Client({
first_name: first_name,
last_name: last_name,
address: [{
street_address: street_address,
city: city,
state: state,
zip: zip
}],
email: email,
username:username
});
if(type == 'client'){
//User.saveClient(newUser, newClient, function(err, user){
// console.log('Client created');
///}); works but replacing w sql
} else {
var newCompany = new Company({
first_name: first_name,
last_name: last_name,
address: [{
street_address: street_address,
city: city,
state: state,
zip: zip
}],
email: email,
username:username
});
console.log('calling sql save..');
//db.postUsers(newUser, newClient);
}
req.flash('success','User added');
res.redirect('/');
}
});
<!--//// -USER AUTH SECTION- \\\\--!><!--//// -USER AUTH SECTION- \\\\--!><!--//// -USER AUTH SECTION- \\\\--!>
passport.serializeUser(function(user, done) {
done(null, user);
});
passport.deserializeUser(function(user, done) {
done(null, user);
});
router.post('/login',passport.authenticate('local',{failureRedirect:'/', failureFlash:'Wrong Username or Password'}), function(req, res){
req.flash('success','You are now logged in');
// it appears that the pipes re crossed and the type is being set as the username. need to check our model and post
var usertype = req.user.type;
// res.redirect('/'/*+usertype+'s/classes' */);
console.log(user + 'hail Mary');
res.redirect('/' /*, { "results": user } */ );
});
passport.use(new LocalStrategy(
function(username, password, done ) {
console.log('in users the username is ' + username)
connection.query('SELECT password FROM t_user WHERE username = ?', username, function(err, user) {
console.log(user +'got user')
parseResults(user, done);
});// end query
function parseResults(user, done) {
Object.keys(user)[0];
var key = Object.keys(user)[0];
user[key];
var storedPw = user[key];
for(var i in storedPw){
returnedPw = storedPw[i];
}
console.log('returnedPw is defined here ' +returnedPw);
movePw(returnedPw, done);
}// end function
var candidatePassword = password;
function movePw (returnedPw, done) {
if (returnedPw ) {
User.comparePassword(candidatePassword, returnedPw, function(err, isMatch) {
if (err) return done(err);
if(isMatch) {
user = username;
return done(null, user);
} else {
console.log('Invalid Password');
// Success Message
req.flash('failureFlash','User Access Denied. False Password');
return done(null, false, { message: 'Invalid password' });
}
});
}
else {console.log('return PW not defined')}
}
}// end outer function ?
));//end passport
// Log User Out
router.get('/logout', function(req, res){
req.logout();
// Success Message
req.flash('success', "You have logged out");
res.redirect('/');
});
function ensureAuthenticated(req, res, next) {
if (req.isAuthenticated()) {
return next();
}
res.redirect('/')
}
module.exports = router;
User.js
var SchemaObject = require('node-schema-object');
var db = require('../database');
var connection = require('express-myconnection');
var mysql = require('mysql');
var bcrypt = require('bcryptjs');
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'sm',
user : 'an',
password : '',
database : 't'
});
// User Schema
var user = new SchemaObject({
email: {
type: String
},
username: {
type: String
},
password:{
type:String,
bcrypt: true
},
type:{
type:String
}
});
var User = module.exports = user;
// Fetch All Classes
module.exports.getUserById = function(id, callback){
User.findById(id, callback);
}
// #1 IN THE LOGIN PROCESS THIS IS FIRST FUNCTIO CALLED
module.exports.getUserByUsername = function(username){
console.log('in our user model, attempting to fetch user...')
console.log('connected as id ' + connection.threadId);
console.log('returning username function...')
console.log('making sure we got the username ' + username)
var query = {username: username};
connection.query('SELECT ? FROM t_user',query, function(err, results, query) {
if (err) throw err;
if(!err) {
console.log('um ok, heres your query results i guess.. ' + results)
indexPage.receiveResults(results);
}
});
};
// Recieve Results
exports.useReceiveResults = function (results) {
res.render('index', { "results": results });
};
// Save Client
module.exports.saveClient = function(newUser, newClient, callback){
bcrypt.hash(newUser.password, 10, function(err, hash){
if(err) throw errl
// Set hash
newUser.password = hash;
console.log('Client is being saved looking for db');
async.parallel([newUser.save, newClient.save], callback);
return;
});
}
// Save User
module.exports.saveUser = function(newUser, callback){
bcrypt.hash(newUser.password, 10, function(err, hash){
if(err) throw errl
// Set hash
newUser.password = hash;
console.log('Client is being saved looking for db');
//async.parallel([newUser.save, newClient.save], callback);
connection.query('INSERT INTO t_user SET ?',newUser, function(err, results, newUser) {
//connection.release();
if(err) console.log(err + 'darnit');
if(!err) console.log('data recieved by database...');
});
return;
});
}
// Save Company
module.exports.saveCompany= function(newUser, newCompany, callback){
bcrypt.hash(newUser.password, 10, function(err, hash){
if(err) throw errl
// Set hash
newUser.password = hash;
console.log('Company is being saved');
async.parallel([newUser.save, newCompany.save], callback);
});
}
// Compare password
module.exports.comparePassword = function(candidatePassword, returnedPw, callback){
bcrypt.compare(candidatePassword, returnedPw, function(err, isMatch){
if(err) throw err;
callback(null, isMatch);
});
}
