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