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


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


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

  Usage: express [options] [dir]


        --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:
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()");

Deploy on Heroku

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

The source code
Photo by Daniel Lerman on Unsplash

nodejs express mysql dev