Create simple API using ExpressJS and MySQL [Part 1]

Hi, I am Ade a Web Designer and  Founder at Gees Studio. Now I would like to share how to create a simple RestAPI starter kit  with ExpressJS and MySQL.

Getting Start

To start all you need are:

  1. NodeJS
  2. MySQL Database
  3. Visual Studio Code or Other Text Editor for JS
  4. Command Prompt for Windows or Terminal on MacOS/Linux

In this sample project I’m using some node package to start, and here’s the package list:

  1. ExpressJS (Install command: npm install express)
  2. Body Parser (Install command: npm install body-parser)
  3. Dot Env (Install command: npm install dot-env)
  4. Json Web Token (Install command: npm install jsonwebtoken)
  5. Sequelize (Install command: npm install sequelize sequelize-cli)
  6. Nodemon (Install command: npm install nodemon)
  7. Node Mailer (Install command: npm install nodemailer)
  8. BcryptJS (Install command: npm install bcryptjs)

1. Setup

First step, you need to create a project directory, then run npm init to create the package.json

After package.json created, now we can create the app.js then we also need to create .env to put any configuration needed later , this is use to call everything inside your project and will be the first file to execute. And here’s how directory structure looks like for this project.

After the directory created, now we can create a database and table user for this project, and here’s the table structure.

Next we create the app.js file and write down the code below. In the app.js we import Express itself and Body Parser.

var express = require('express'),
    app = express(),
    port = process.env.APP_PORT || 3000,
    bodyParser = require('body-parser');

app.use(bodyParser.urlencoded({ extended: true }));

console.log('Learning RESTful API server started on: ' + port);

2. Models

Now we have the directory structure and MySQL database. The next step is creating the models using sequelize cli. All we need to do is init the sequalize inside the src directory with  the following command

npx sequelize-cli init

in the command prompt/terminal. This is will create models, migration and config directory also config.json file inside config directory.
Open the config.json file and setup the database connection that match to your MySQL server configuration.

After the finis with the setup we can start to generate models for table we have. In this case we are only use User table. There’s a two way to generate models first we can use sequelize-cli or we can write the code itself. Because even-though  with sequelize-cli we also need to describe all the field we have in the command prompt/terminal but the benefit is you can undo the migration. So for now we use the sequelize-cli to generate our model, here’s the command

npx sequelize-cli model:generate --name User --attributes username:string,email:string,phone_number:string,password_hash:string,auth_key:string,confirmed_at:integer,uncorfimed_email:string,blocked_at:integer,registration_api:string,created_at:integer,updated_at:integer,flags:integer,last_login_at:integer,status:integer,type_user:integer

Make sure there’s no space after –attributes params other ways it will make an error. 

After that command run successfully now we have the models 😁. 

3. Controllers

After the models created, now we can start to create the controller. In this case we need User and Auth controllers, and in user controller we need action to register and login. So what we need in this controller are BcryptJS, Json Web token (JWT) and Node Mailer. Let’s me explain about the function,BcryptJS is use to encrypt the password before its saved to table

var passwordHash = bcrypt.hashSync(req.body.password,8);

, and also to compare the password from the post data while login, userDB.password_hash, function(err, result){

And JWT is used to create token to secure every request to our API later.

const accessToken=jwt.sign({user:userDB},process.env.ACCESS_TOKEN,{expiresIn: “5h”,})

and Node Mailer use to send email from the server. First we can start with register function in the controller User.js. First setup all the import that we need (All example of this part you can take a look in the github link in the end of this tutorial),

‘use strict’;
var response = require(‘../responses/index’);
const DateFormat = require(‘../classes/DateFormat’);
var nodemailer=require(‘nodemailer’);
var models = require(‘../models’);
const { Op } = require(“sequelize”);
const bcrypt = require(‘bcryptjs’);
const jwt = require(‘jsonwebtoken’);

and after we added the require package now we can add the register function and login function. 

exports.login = function(req, res) {
    // find credential
    var username = req.body.username;
    var password = req.body.password;
    if(typeof username != 'undefined' && typeof password != 'undefined'){
            where: {
              [Op.or]: [
                { username: username },
                { email: username },
                { phone_number: username }
          }).then(function (userDB) {
  , userDB.password_hash, function(err, result){
                if (!result) {
                    response.error('Invalid user or Password', res)
                } else {
                    const accessToken=jwt.sign({user:userDB},process.env.ACCESS_TOKEN,{expiresIn:  "5h",})
                    console.log('token : '+ accessToken);
                    var arr={'user':userDB,'status':result,'token': accessToken}
                    res.cookie('token', accessToken)
                    response.success(arr, res);
            }) ;
            }).catch(function(err) {
                response.error('Invalid user or Password', res)
    } else {
        response.error('Invalid user or Password', res)

exports.createUsers = function(req, res) {
    let date = new DateFormat().timestampNow();
    var username = req.body.username;
    var email =;
    var passwordHash = bcrypt.hashSync(req.body.password,8);
    var phoneNumber =;
    var createAt=date;
    var updatedAt=date;
    var type=2;
    var status=0;
    var ip=req.headers['x-forwarded-for'] || req.connection.remoteAddress;
    var authKey=bcrypt.hashSync(,2)
        username: username, 
        email: email, 
    }).then(async function (result) {
        console.log(process.env.EMAIL+' '+process.env.PASS_EMAIL)
        var transporter = nodemailer.createTransport({
            host: "",
            port: 465,
            auth: {
              user: process.env.EMAIL,
              pass: process.env.PASS_EMAIL
            secure: true,
            var host="http://localhost:3000";
            var textMessage='<h1>Registration Success</h1><br><p>Click this link to verify your account</p><p><a href="'+host+'/users/verification/'+email+'/'+ authKey +'">Verify Account</a></p>'
            console.log(process.env.FROM_NAME+' <'+process.env.EMAIL+'>');

            var mailOptions = {
                from: process.env.FROM_NAME+' <'+process.env.EMAIL+'>',
                to: username + '<'+email+'>',
                subject: 'Verify Account',
                html: textMessage
            await transporter.sendMail(mailOptions, function(error, info){
            if (error) {
                response.error(error, res);
            } else {
                response.success(info, res);
                console.log('Email sent: ' + info);   
    }).catch(function(err) {
        response.error(err, res)

Now we have the controller User.js and we still need controller Auth.js, this one to protect unauthorised request and validate every token send to our API.

'use strict';
const jwt = require('jsonwebtoken');
var response = require('../responses/index');
const verifyToken = (req, res, next) => {
    const bearerHeader=req.headers['authorization'];
    if(typeof bearerHeader !=='undefined'){
            const bearer=bearerHeader.split(' ');
            const bearerToken=bearer[1];
            var token = bearerToken;
        response.forbidden('Forbidden', res)
    jwt.verify(token,process.env.ACCESS_TOKEN, (err,authData)=>{
            response.forbidden('Forbidden', res)
module.exports = {

Now we have the User.js and Auth.js. So what next is how to route all request to the controllers.

3. Route

In this case, we create index.js in the route directory and users.js as well. Index.js use to manage or combine every route we have, and in index.js we will import every route we have, for example now we have User.js and It will be import in the Index.js

'use strict';

var express     = require('express'),
    router      = express.Router(),
    userController = require('../controllers/User'),
    Auth = require('../controllers/Auth');'/users/login', userController.login);'/users/register', userController.createUsers);

router.get('/users/verification/:email/:key', userController.verifyUser);


module.exports = router;        
'use strict';

var express = require('express'),
    Users = require('./Users.js'),
    restRoutes = express.Router()
restRoutes.use('/', Users)

module.exports = restRoutes;

In the users.js we import the controller and the route the declare url to specific controller actions. There’s we also need the method for each route, like post, get, put or delete.

One more thing to is we need to import this route to app.js. Add this code to app.js before app.listen()

var routes = require(‘./src/routes/’);

Now app.js will use the route we have. 

The last thin we need to create the response file. This is will return all the request. Add index.js in the response directory.


'use strict';

exports.success = function(values, res) {
  var data = {
      'status': 200,
      'values': values

exports.forbidden = function(values, res) {
  var data = {
      'status': 403,
      'values': values

exports.error = function(values, res) {
    var data = {
        'status': 500,
        'values': values

Now all set and start with a test, In the case we use Postman as the tools. Run the project by execute

npm start

Open post man an do a test. Here’s the expected result.

Well, after we make a test and above is the result, now you can try and here’s the github repository.

Thank You and see you in Part 2 about using bearer token.

Leave a Comment

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

Scroll to Top