Node.js Express Mysql stack deployed on Heroku

How to build a rest API from zero using Node.js, Express and MySQL


Oct 09, 2021
featured image

Introduction

Let's build a Rest API from scratch that uses a MySQL database. We are going to use Node.js and Express as a framework to generate some scaffolding for our Rest API

Setup

Install Express globally to use the out of the box generator
      
        sudo npm install express-generator -g
      
    
Once install it, let see the option from the help option
      
        express -h
      
    
Output:
      

  Usage: express [options] [dir]

  Options:

        --version        output the version number
    -e, --ejs            add ejs engine support
        --pug            add pug engine support
        --hbs            add handlebars engine support
    -H, --hogan          add hogan.js engine support
    -v, --view   add view  support (dust|ejs|hbs|hjs|jade|pug|twig|vash) (defaults to jade)
        --no-view        use static html instead of view engine
    -c, --css    add stylesheet  support (less|stylus|compass|sass) (defaults to plain css)
        --git            add .gitignore
    -f, --force          force on non-empty directory
    -h, --help           output usage information

      
    
Since we want a Rest API, let's generate it without a view
      
express --no-view
      
    
Get all dependencies and start the server
      
npm install
npm start
      
    

Open browser at http://localhost:3000/ to see the default implementation

Now let's install a module to deal with MySQL connection and Nodemon to restart our server right after modifying the code

      
npm install mysql2
npm install nodemon
      
    

Add the script

      
"start:dev": "nodemon ./bin/www"
      
    
And now run the server like this
      
npm run start:dev
      
    
Install dotenv-safe to deal with environment variable vars
      
node install dotenv-safe
      
    
And add this line at the top of the file app.js
      
require("dotenv-safe").config({ silent: true });
      
    

Database setup

Following dotenv convention, create a file called .env.example with this content:
      
NODE_ENV=development
LOG_LEVEL=debug
MYSQL_DATABASE=***
MYSQL_HOST=***
MYSQL_USERNAME=***
MYSQL_PASSWORD=***
MYSQL_PORT=3306
MYSQL_MAX_POOL_SIZE=10
      
    
This file must be versioned

Now copy the file .env.example and rename it to .env

This file must NOT be versioned since it contains all our secrets for local development. So add it to your .gitignore file

Let see later the values to be used when Heroku configuration comes in

Create a file called database.js

      

const mysql = require("mysql2");
const pool = mysql.createPool({
  database: process.env.MYSQL_DATABASE,
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USERNAME,
  password: process.env.MYSQL_PASSWORD,
  port: Number(process.env.MYSQL_PORT),
  connectionLimit: Number(process.env.MYSQL_MAX_POOL_SIZE),
});

const query = (query, values = []) => {
  return new Promise((resolve, reject) => {
    pool.query(query, values, (err, results) => {
      if (err) reject(err);
      else resolve(results);
    });
  });
};

module.exports.query = query;

      
    
Modify the index router thus
      
router.get("/", async (req, res, next) => {
  // const result = await db.query("SELECT 1 + 1");
  const result = await db.query("SELECT NOW()");
  res.json(result);
});
      
    

Deploy on Heroku

Crreate a new App Click on Open App in Heroku and you should see something like this

The source code

https://github.com/andrescanavesi/node-js-express-mysql-heroku
Photo by Daniel Lerman on Unsplash

nodejs express mysql dev