Reading Time: 3 minutes

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 <engine>  add view <engine> support (dust|ejs|hbs|hjs|jade|pug|twig|vash) (defaults to jade)
        --no-view        use static html instead of view engine
    -c, --css <engine>   add stylesheet <engine> support (less|stylus|compass|sass) (defaults to plain css)
        --git            add .gitignore
    -f, --force          force on non-empty directory
    -h, --help           output usage information

      </engine></engine></engine></engine>
    

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