title: ExpressJs + Passport.js + MySQL Authentication
body: <p>whenever i am trying to register any user through localhost its not creating the database i have tired to run the schema.sql file in mysql its working fine but after that also i am not able to register. i have given my entire code so can anyone help me to solve this issue?</p>
  
  <pre><code> package.json
      {
        "name": "passport_mysql_express_auth",
        "description": "ExpressJs + Passport.js + MySQL Authentication",
        "version": "1.0.0",
        "author": "",
        "main": "app.js",
        "dependencies": {
          "bcrypt-nodejs": "*",
          "body-parser": "*",
          "bookshelf": "0.5",
          "cookie-parser": "*",
          "ejs": "*",
          "express": "4.x",
          "express-session": "*",
          "knex": "^0.10.0",
          "mariasql": "^0.2.6",
          "mysql": "*",
          "passport": "*",
          "passport-local": "*"
        }
      }
  </code></pre>
  
  <hr>
  
  <pre><code>App.js
  // vendor libraries
  var express = require('express');
  var bodyParser = require('body-parser');
  var cookieParser = require('cookie-parser');
  var session = require('express-session');
  var bcrypt = require('bcrypt-nodejs');
  var ejs = require('ejs');
  var path = require('path');
  var passport = require('passport');
  var LocalStrategy = require('passport-local').Strategy;
  
  // custom libraries
  // routes
  var route = require('./route');
  // model
  var Model = require('./model');
  
  var app = express();
  
  passport.use(new LocalStrategy(function(username, password, done) {
     new Model.User({username: username}).fetch().then(function(data) {
        var user = data;
        if(user === null) {
           return done(null, false, {message: 'Invalid username or password'});
        } else {
           user = data.toJSON();
           if(!bcrypt.compareSync(password, user.password)) {
              return done(null, false, {message: 'Invalid username or password'});
           } else {
              return done(null, user);
           }
        }
     });
  }));
  
  passport.serializeUser(function(user, done) {
    done(null, user.username);
  });
  
  passport.deserializeUser(function(username, done) {
     new Model.User({username: username}).fetch().then(function(user) {
        done(null, user);
     });
  });
  
  app.set('port', process.env.PORT || 3000);
  app.set('views', path.join(__dirname, 'views'));
  app.set('view engine', 'ejs');
  
  app.use(cookieParser());
  //app.use(bodyParser());
  app.use(bodyParser.urlencoded({ extended: false }));
  app.use(bodyParser.json());
  //app.use(session({secret: 'secret strategic xxzzz code'}));
  app.use(session({ secret: 'secret strategic xxzzz code', cookie: { maxAge: 60000 }, resave: true, saveUninitialized: true }));
  app.use(passport.initialize());
  app.use(passport.session());
  
  // GET
  app.get('/', route.index);
  
  // signin
  // GET
  app.get('/signin', route.signIn);
  // POST
  app.post('/signin', route.signInPost);
  
  // signup
  // GET
  app.get('/signup', route.signUp);
  // POST
  app.post('/signup', route.signUpPost);
  
  // logout
  // GET
  app.get('/signout', route.signOut);
  
  
  // 404 not found
  app.use(route.notFound404);
  
  var server = app.listen(app.get('port'), function(err) {
     if(err) throw err;
  
     var message = 'Server is running @ http://localhost:' + server.address().port;
     console.log(message);
  });
  </code></pre>
  
  <hr>
  
  <pre><code>db.js
  var Bookshelf = require('bookshelf');
  
  var config = {
     host: 'localhost',  
     port:'3307',// your host
     user: 'root', // your database user
     password: 'yourpassword', // your database password
     database: 'dbUsers',
     charset: 'UTF8_GENERAL_CI'
  };
  
  var DB = Bookshelf.initialize({
     client: 'mysql', 
     connection: config
  });
  
  module.exports.DB = DB;
  </code></pre>
  
  <hr>
  
  <pre><code>model.js
  
  var DB = require('./db').DB;
  
  var User = DB.Model.extend({
     tableName: 'tblUsers',
     idAttribute: 'userId'
  });
  
  module.exports = {
     User: User
  };
  </code></pre>
  
  <hr>
  
  <pre><code>route.js
  
  // vendor library
  var passport = require('passport');
  var bcrypt = require('bcrypt-nodejs');
  
  // custom library
  // model
  var Model = require('./model');
  
  // index
  var index = function(req, res, next) {
     if(!req.isAuthenticated()) {
        res.redirect('/signin');
     } else {
  
        var user = req.user;
  
        if(user !== undefined) {
           user = user.toJSON();
        }
        res.render('index', {title: 'Home', user: user});
     }
  };
  
  // sign in
  // GET
  var signIn = function(req, res, next) {
     if(req.isAuthenticated()) res.redirect('/');
     res.render('signin', {title: 'Sign In'});
  };
  
  // sign in
  // POST
  var signInPost = function(req, res, next) {
     passport.authenticate('local', { successRedirect: '/',
                            failureRedirect: '/signin'}, function(err, user, info) {
        if(err) {
           return res.render('signin', {title: 'Sign In', errorMessage: err.message});
        } 
  
        if(!user) {
           return res.render('signin', {title: 'Sign In', errorMessage: info.message});
        }
        return req.logIn(user, function(err) {
           if(err) {
              return res.render('signin', {title: 'Sign In', errorMessage: err.message});
           } else {
              return res.redirect('/');
           }
        });
     })(req, res, next);
  };
  
  // sign up
  // GET
  var signUp = function(req, res, next) {
     if(req.isAuthenticated()) {
        res.redirect('/');
     } else {
        res.render('signup', {title: 'Sign Up'});
     }
  };
  
  // sign up
  // POST
  var signUpPost = function(req, res, next) {
     var user = req.body;
     var usernamePromise = null;
     usernamePromise = new Model.User({username: user.username}).fetch();
  
     return usernamePromise.then(function(model) {
        if(model) {
           res.render('signup', {title: 'signup', errorMessage: 'username already exists'});
        } else {
  
           var password = user.password;
           var hash = bcrypt.hashSync(password);
  
           var signUpUser = new Model.User({username: user.username, password: hash});
  
           signUpUser.save().then(function(model) {
              // sign in the newly registered user
              signInPost(req, res, next);
           });    
        }
     });
  };
  
  // sign out
  var signOut = function(req, res, next) {
     if(!req.isAuthenticated()) {
        notFound404(req, res, next);
     } else {
        req.logout();
        res.redirect('/signin');
     }
  };
  
  // 404 not found
  var notFound404 = function(req, res, next) {
     res.status(404);
     res.render('404', {title: '404 Not Found'});
  };
  
  // export functions
  
  // index
  module.exports.index = index;
  
  // sigin in
  // GET
  module.exports.signIn = signIn;
  // POST
  module.exports.signInPost = signInPost;
  
  // sign up
  // GET
  module.exports.signUp = signUp;
  // POST
  module.exports.signUpPost = signUpPost;
  
  // sign out
  module.exports.signOut = signOut;
  
  // 404 not found
  module.exports.notFound404 = notFound404;
  </code></pre>
  
  <hr>
  
  <pre><code>schema.sql
  drop database if exists dbUsers;
  
  create database if not exists dbUsers;
  
  use dbUsers;
  
  drop table if exists tblUsers;
  
  create table if not exists tblUsers(
     userId integer primary key auto_increment,
     username varchar(100) unique,
     password varchar(100)
  )engine=innodb;
  </code></pre>
  
  <hr>
  
  <pre><code>index.ejs
  
  &lt;!DOCTYPE html&gt;
  &lt;html lang="en"&gt;
     &lt;head&gt;
        &lt;title&gt;&lt;%= title %&gt;&lt;/title&gt;
     &lt;/head&gt;
     &lt;body&gt;
        &lt;p&gt;
           Welcome &lt;strong&gt;&lt;%= user.username %&gt;&lt;/strong&gt;
           &lt;a href="/signout" title="sign out"&gt;sign out&lt;/a&gt;
        &lt;/p&gt;
     &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <hr>
  
  <pre><code>signin.ejs
  &lt;!DOCTYPE html&gt;
  &lt;html lang="en"&gt;
     &lt;head&gt;
        &lt;title&gt;&lt;%= title %&gt;&lt;/title&gt;
     &lt;/head&gt;
     &lt;body&gt;
        &lt;h2&gt;Sign In Form&lt;/h2&gt;
        &lt;form method="post" action="/signin"&gt;
  
           &lt;% if(typeof(errorMessage) !== 'undefined') {%&gt;
              &lt;span&gt;&lt;%= errorMessage %&gt;&lt;/span&gt;
           &lt;% } %&gt;
           &lt;p&gt;
              &lt;label for="username"&gt;username&lt;/label&gt;
              &lt;input type="text" name="username" id="username"
                 placeholder="username" required="true"/&gt;
           &lt;/p&gt;
           &lt;p&gt;
              &lt;label for="password"&gt;password&lt;/label&gt;
              &lt;input type="text" name="password" id="password"
                 placeholder="password" required="true"/&gt;
           &lt;/p&gt;
           &lt;p&gt;
              &lt;input type="submit" name="signin" id="signin" value="sign in"/&gt; 
              &lt;a href="/signup" title="register"&gt;register&lt;/a&gt;
           &lt;/p&gt;
  
        &lt;/form&gt;
     &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <hr>
  
  <pre><code>signup.ejs
  
  &lt;!DOCTYPE html&gt;
  &lt;html lang="en"&gt;
     &lt;head&gt;
        &lt;title&gt;&lt;%= title %&gt;&lt;/title&gt;
     &lt;/head&gt;
     &lt;body&gt;
        &lt;h2&gt;Register Form&lt;/h2&gt;
        &lt;form method="post" action="/signup"&gt;
           &lt;% if(typeof(errorMessage) !== 'undefined') {%&gt;
           &lt;span&gt;&lt;%= errorMessage %&gt;&lt;/span&gt;
           &lt;% } %&gt;
           &lt;p&gt;
              &lt;label for="username"&gt;username&lt;/label&gt;
              &lt;input type="text" name="username" id="username"
                 placeholder="username" required="true"/&gt;
           &lt;/p&gt;
           &lt;p&gt;
              &lt;label for="password"&gt;password&lt;/label&gt;
              &lt;input type="text" name="password" id="password"
                 placeholder="password" required="true"/&gt;
           &lt;/p&gt;
           &lt;p&gt;
              &lt;input type="submit" name="signup" id="signup" value="register"/&gt;
              &lt;a href="/signin" title="sign in"&gt;sign in&lt;/a&gt;
           &lt;/p&gt;
        &lt;/form&gt;
     &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>