This article is inspired from my previous article using the code from Auth0 for JWT authentication server which users are in memory array. Now I want to rewrite by using MySQL to store users and another purpose is helping beginner of Node.js can get start with Node.js + Express + MySQL.
If you don't know how to start a Node.js application, I recommend you read the popular article "Node.js Applications with VS Code" in VS Code. Assuming that you already read that article, we will start Node.js application with Express Generator as the following steps:
#express mysql-jwt-auth
#cd mysql-jwt-auth
#npm install
#code .
The last command will open mysql-jwt-auth application in VS Code for editing. You will have generated codes like below picture:
OK, now we'll add some necessary modules into this app:
#npm i cors --save
#npm i lodash --save
#npm i jsonwebtoken --save
#npm i express-jwt --save
#npm i mysql --save
After that, open app.js file and add below lines:
var cors = require('cors'); //after the line var bodyParser = require('body-parser');
app.use(cors()); //after the line app.use(logger('dev'));
Change the line:
app.use('/users', users); ==> app.use(users);
Open your MySQL database and create table users and table quotes as the following:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`email` varchar(50) NOT NULL,
PRIMARY KEY (`id`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `users`(`id`,`username`,`password`,`email`) VALUES (1,'gonto','gonto','gonto@me.com');
CREATE TABLE `quotes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` varchar(150) NOT NULL,
`private` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Add db.js file into root folder (same level with app.js):
var mysql = require('mysql');
var pool = null;
exports.connect = function() {
pool = mysql.createPool({
host : 'myhost',
user : 'myuser',
password : 'mypassword',
database : 'mydb'
});
}
exports.get = function() {
return pool;
}
Add below lines to bin/www file (after line: var http = require('http')):
var db = require('../db');
db.connect();
Now we have the db ready for using. Add new file config.json into root folder with content below:
{
"secretKey": "don't share this key"
}
The above file will keep a secret key for encoding user token after logged in. Open routes/user.js file and replace by new code below:
var express = require('express'),
_ = require('lodash'),
config = require('../config'),
jwt = require('jsonwebtoken')
db = require('../db');
var app = module.exports = express.Router();
var secretKey = "don't share this key";
function createToken(user) {
return jwt.sign(_.omit(user, 'password'), config.secretKey, { expiresIn: 60*60*5 });
}
function getUserDB(username, done) {
db.get().query('SELECT * FROM users WHERE username = ? LIMIT 1', [username], function(err, rows, fields) {
if (err) throw err;
done(rows[0]);
});
}
app.post('/user/create', function(req, res) {
if (!req.body.username || !req.body.password) {
return res.status(400).send("You must send the username and the password");
}
getUserDB(req.body.username, function(user){
if(!user) {
user = {
username: req.body.username,
password: req.body.password,
email: req.body.email
};
db.get().query('INSERT INTO users SET ?', [user], function(err, result){
if (err) throw err;
newUser = {
id: result.insertId,
username: user.username,
password: user.password,
email: user.email
};
res.status(201).send({
id_token: createToken(newUser)
});
});
}
else res.status(400).send("A user with that username already exists");
});
});
app.post('/user/login', function(req, res) {
if (!req.body.username || !req.body.password) {
return res.status(400).send("You must send the username and the password");
}
getUserDB(req.body.username, function(user){
if (!user) {
return res.status(401).send("The username is not existing");
}
if (user.password !== req.body.password) {
return res.status(401).send("The username or password don't match");
}
res.status(201).send({
id_token: createToken(user)
});
});
});
app.get('/user/check/:username', function(req, res) {
if (!req.params.username) {
return res.status(400).send("You must send a username");
}
getUserDB(req.params.username, function(user){
if(!user) res.status(201).send({username: "OK"});
else res.status(400).send("A user with that username already exists");
});
});
At this time, we can use above code for authenticating user. Key the command npm start and use Postman tool to check what the server response. Here is my example:
That's fine, so we are going to add routes/quotes.js file to illustrate for anonymous request and authenticated request in reading quotes. This file has simple content as below:
var express = require('express'),
jwt = require('express-jwt'),
config = require('../config'),
db = require('../db');
var app = module.exports = express.Router();
var jwtCheck = jwt({
secret: config.secretKey
});
function getPublicQuotesDB(done){
db.get().query('SELECT * FROM quotes WHERE private=0', function(err, rows) {
if (err) throw err;
done(rows);
});
}
function getPrivateQuotesDB(done){
db.get().query('SELECT * FROM quotes WHERE private=1', function(err, rows) {
if (err) throw err;
done(rows);
});
}
app.get('/api/public/quote', function(req, res) {
getPublicQuotesDB(function(result) {
res.status(200).send(result);
});
});
app.use('/api/private', jwtCheck);
app.get('/api/private/quote', function(req, res) {
getPrivateQuotesDB(function(result) {
res.status(200).send(result);
});
});
Don't forget to add below lines to app.js file to include above quotes.js:
var quotes = require('./routes/quotes');
app.use(quotes);
OK, let use Postman tool to check if the api gets quotes working as expected. Here are my results:
As you can see, request /api/public/quote works fine. But you will get a complain "No authorization token was found" if you request /api/private/quote. When you add Authorization header with your token for it, it runs ok soon (last picture).
That's all for tonight. Bye!
You can find source code on my GitHub.
Open your MySQL database and create table users and table quotes as the following:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`email` varchar(50) NOT NULL,
PRIMARY KEY (`id`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `users`(`id`,`username`,`password`,`email`) VALUES (1,'gonto','gonto','gonto@me.com');
CREATE TABLE `quotes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` varchar(150) NOT NULL,
`private` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Add db.js file into root folder (same level with app.js):
var mysql = require('mysql');
var pool = null;
exports.connect = function() {
pool = mysql.createPool({
host : 'myhost',
user : 'myuser',
password : 'mypassword',
database : 'mydb'
});
}
exports.get = function() {
return pool;
}
Add below lines to bin/www file (after line: var http = require('http')):
var db = require('../db');
db.connect();
Now we have the db ready for using. Add new file config.json into root folder with content below:
{
"secretKey": "don't share this key"
}
The above file will keep a secret key for encoding user token after logged in. Open routes/user.js file and replace by new code below:
var express = require('express'),
_ = require('lodash'),
config = require('../config'),
jwt = require('jsonwebtoken')
db = require('../db');
var app = module.exports = express.Router();
var secretKey = "don't share this key";
function createToken(user) {
return jwt.sign(_.omit(user, 'password'), config.secretKey, { expiresIn: 60*60*5 });
}
function getUserDB(username, done) {
db.get().query('SELECT * FROM users WHERE username = ? LIMIT 1', [username], function(err, rows, fields) {
if (err) throw err;
done(rows[0]);
});
}
app.post('/user/create', function(req, res) {
if (!req.body.username || !req.body.password) {
return res.status(400).send("You must send the username and the password");
}
getUserDB(req.body.username, function(user){
if(!user) {
user = {
username: req.body.username,
password: req.body.password,
email: req.body.email
};
db.get().query('INSERT INTO users SET ?', [user], function(err, result){
if (err) throw err;
newUser = {
id: result.insertId,
username: user.username,
password: user.password,
email: user.email
};
res.status(201).send({
id_token: createToken(newUser)
});
});
}
else res.status(400).send("A user with that username already exists");
});
});
app.post('/user/login', function(req, res) {
if (!req.body.username || !req.body.password) {
return res.status(400).send("You must send the username and the password");
}
getUserDB(req.body.username, function(user){
if (!user) {
return res.status(401).send("The username is not existing");
}
if (user.password !== req.body.password) {
return res.status(401).send("The username or password don't match");
}
res.status(201).send({
id_token: createToken(user)
});
});
});
app.get('/user/check/:username', function(req, res) {
if (!req.params.username) {
return res.status(400).send("You must send a username");
}
getUserDB(req.params.username, function(user){
if(!user) res.status(201).send({username: "OK"});
else res.status(400).send("A user with that username already exists");
});
});
At this time, we can use above code for authenticating user. Key the command npm start and use Postman tool to check what the server response. Here is my example:
That's fine, so we are going to add routes/quotes.js file to illustrate for anonymous request and authenticated request in reading quotes. This file has simple content as below:
var express = require('express'),
jwt = require('express-jwt'),
config = require('../config'),
db = require('../db');
var app = module.exports = express.Router();
var jwtCheck = jwt({
secret: config.secretKey
});
function getPublicQuotesDB(done){
db.get().query('SELECT * FROM quotes WHERE private=0', function(err, rows) {
if (err) throw err;
done(rows);
});
}
function getPrivateQuotesDB(done){
db.get().query('SELECT * FROM quotes WHERE private=1', function(err, rows) {
if (err) throw err;
done(rows);
});
}
app.get('/api/public/quote', function(req, res) {
getPublicQuotesDB(function(result) {
res.status(200).send(result);
});
});
app.use('/api/private', jwtCheck);
app.get('/api/private/quote', function(req, res) {
getPrivateQuotesDB(function(result) {
res.status(200).send(result);
});
});
Don't forget to add below lines to app.js file to include above quotes.js:
var quotes = require('./routes/quotes');
app.use(quotes);
OK, let use Postman tool to check if the api gets quotes working as expected. Here are my results:
As you can see, request /api/public/quote works fine. But you will get a complain "No authorization token was found" if you request /api/private/quote. When you add Authorization header with your token for it, it runs ok soon (last picture).
That's all for tonight. Bye!
You can find source code on my GitHub.
