Handling user login and registration using nodejs and mysql

Image source:Pixabay

Nodejs has seen dramatic increase in adoption in the backend side since it allows javascript usage in backend along with frontend. It is usually advisable to use nosql databases like mongodb,couchbase and aerospike but there might be a need to use mysql databases with nodejs until you successfully migrate your mysql dbs to nosql stack.

In today’s tutorial we are going to create basic email login authentication system using nodejs and mysql.

If you need to set up mysql server on ubuntu and create connection between nodejs and mysql then you can follow the basic tutorial here

The steps are as follows:

First we need to create a new table in database for registering new users.Drop into mysql shell from terminal using following command:

mysql -u root -p
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cloudprint |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> use cloudprint
Database changed

Now that we have selected required database lets create users table in the table by running following command in mysql shell:

CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Exit the mysql shell using following command:

mysql> c
mysql> exit

In the last tutorial we had setup basic connection with mysql in server.js file which we are going to use in this tutorial.First you need to set up directory structure like this in project’s folder:

.
├── node_modules
├── package.json
├── routes
│ └── loginroutes.js
└── server.js

Here routes folder will contain all route handlers for api requests and loginroutes will contain handlers specific to login and registration tasks.

Here is package.json for reference and installing all the dependencies:

{
"name": "backend",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo "Error: no test specified" && exit 1"
},
"author": "",
"license": "ISC",
"dependencies": {
"body-parser": "^1.16.0",
"express": "^4.14.0",
"mysql": "^2.13.0"
}
}

Lets start with server.js code which is as follows:

var express    = require("express");
var login = require('./routes/loginroutes');
var bodyParser = require('body-parser');
var app = express();
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
app.use(function(req, res, next) {
res.header("Access-Control-Allow-Origin", "*");
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
next();
});
var router = express.Router();
// test route
router.get('/', function(req, res) {
res.json({ message: 'welcome to our upload module apis' });
});
//route to handle user registration
router.post('/register',login.register);
router.post('/login',login.login)
app.use('/api', router);
app.listen(5000);

We are using express framework and bodyparser as a middleware on line 6 for parsing incoming requests as json. The access controls allow origin headers on line 9 are essential for our server to serve cross domain requests.We are using built in router from express for handling api requests.I have defined two apis for regsiter and login on line 22 and 23.

Now lets start with handler functions in loginroutes.js:

First we create mysql connection using specifying the database of choice:

var mysql      = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : 'your-password',
database : 'cloudprint'
});
connection.connect(function(err){
if(!err) {
console.log("Database is connected ... nn");
} else {
console.log("Error connecting database ... nn");
}
});

Then we create handler for user registration:

exports.register = function(req,res){
// console.log("req",req.body);
var today = new Date();
var users={
"first_name":req.body.first_name,
"last_name":req.body.last_name,
"email":req.body.email,
"password":req.body.password,
"created":today,
"modified":today
}
connection.query('INSERT INTO users SET ?',users, function (error, results, fields) {
if (error) {
console.log("error ocurred",error);
res.send({
"code":400,
"failed":"error ocurred"
})
}else{
console.log('The solution is: ', results);
res.send({
"code":200,
"success":"user registered sucessfully"
});
}
});
}

Here we run a simple insertion query into mysql for adding new users to database.I have kept datetime for ‘created’ and ‘modified’ as current datetime which can be set as per your needs.

Next is handler for user login and validating user credentials:

exports.login = function(req,res){
var email= req.body.email;
var password = req.body.password;
connection.query('SELECT * FROM users WHERE email = ?',[email], function (error, results, fields) {
if (error) {
// console.log("error ocurred",error);
res.send({
"code":400,
"failed":"error ocurred"
})
}else{
// console.log('The solution is: ', results);
if(results.length >0){
if(results[0].password == password){
res.send({
"code":200,
"success":"login sucessfull"
});
}
else{
res.send({
"code":204,
"success":"Email and password does not match"
});
}
}
else{
res.send({
"code":204,
"success":"Email does not exits"
});
}
}
});
}

First we check if email exists in database and then check password for allowing successful login.

You can use tools like postman from chrome apps for sending post requests to server after starting it using the command:

node server.js

Here is postman request format for user registration and login:

Registration:
url:localhost:5000/api/register
payload:{
"first_name":"use1234",
"last_name":"asdf",
"email":"sm2@example.com",
"password":"pass123"
}
payload type:raw (Application/json)
Login:
url:localhost:5000/api/login
payload:{
"email":"sm2@example.com",
"password":"pass123"
}
payload type:raw (Application/json)

Bonus Tip:

Mysql workbench is a great tool for managing mysql databases in ubuntu. In order to install and run mysql workbench run the following commands:

sudo apt-get install mysql-workbench
mysql-workbench &

Connect Deeper

Kindly mention your feedback in the comments and check out my other tutorials here:Technoetics

Image source:Pixabay

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.