javascript - ExpressJs + Passport.js + MySQL 身份验证

Question

每当我尝试通过本地主机注册任何用户时，它都不会创建数据库，我已经厌倦了在 mysql 中运行 schema.sql 文件，它工作正常，但之后我也无法注册。我已经给出了我的完整代码，所以有人可以帮助我解决这个问题吗？

 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": "*"
      }
    }

<小时/>

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

<小时/>

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;

<小时/>

model.js

var DB = require('./db').DB;

var User = DB.Model.extend({
   tableName: 'tblUsers',
   idAttribute: 'userId'
});

module.exports = {
   User: User
};

<小时/>

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;

<小时/>

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;

<小时/>

index.ejs

<!DOCTYPE html>
<html lang="en">
   <head>
      <title><%= title %></title>
   </head>
   <body>
      <p>
         Welcome <strong><%= user.username %></strong>
         <a href="/signout" title="sign out">sign out</a>
      </p>
   </body>
</html>

<小时/>

signin.ejs
<!DOCTYPE html>
<html lang="en">
   <head>
      <title><%= title %></title>
   </head>
   <body>
      <h2>Sign In Form</h2>
      <form method="post" action="/signin">

         <% if(typeof(errorMessage) !== 'undefined') {%>
            <span><%= errorMessage %></span>
         <% } %>
         <p>
            <label for="username">username</label>
            <input type="text" name="username" id="username"
               placeholder="username" required="true"/>
         </p>
         <p>
            <label for="password">password</label>
            <input type="text" name="password" id="password"
               placeholder="password" required="true"/>
         </p>
         <p>
            <input type="submit" name="signin" id="signin" value="sign in"/> 
            <a href="/signup" title="register">register</a>
         </p>

      </form>
   </body>
</html>

<小时/>

signup.ejs

<!DOCTYPE html>
<html lang="en">
   <head>
      <title><%= title %></title>
   </head>
   <body>
      <h2>Register Form</h2>
      <form method="post" action="/signup">
         <% if(typeof(errorMessage) !== 'undefined') {%>
         <span><%= errorMessage %></span>
         <% } %>
         <p>
            <label for="username">username</label>
            <input type="text" name="username" id="username"
               placeholder="username" required="true"/>
         </p>
         <p>
            <label for="password">password</label>
            <input type="text" name="password" id="password"
               placeholder="password" required="true"/>
         </p>
         <p>
            <input type="submit" name="signup" id="signup" value="register"/>
            <a href="/signin" title="sign in">sign in</a>
         </p>
      </form>
   </body>
</html>

Answer 1

Bookself 与 knex 配合良好，因此首先使用以下命令安装 knex

npm install --save knex

安装后，更改您的 db.js 文件，如下所示

    var knex = require('knex')({
        client: 'mysql',
        connection: {
            host: 'localhost',
            user: 'deen',
            password: 'deen',
            database: 'lttp',
            charset  : 'utf8'
        }
    });

    var Bookshelf = require('bookshelf')(knex);

    module.exports.DB = Bookshelf;

在 package.json 中将 Bookself 版本更改为 *。您的 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": "*",
        "cookie-parser": "*",
        "ejs": "*",
        "express": "4.x",
        "express-session": "*",
        "knex": "*",
        "mariasql": "^0.2.6",
        "mysql": "*",
        "passport": "*",
        "passport-local": "*"
      }
 }