Siah Peih Wee

Aug 17, 20224 min read

Create Backend using MySQL + Express

Updated: Oct 24, 2022

In this tutorial, you will learn how to write a simple backend using MySQL and Express. You may want to preview the code in my Github.

https://github.com/peihwee/node-mysql-example

Setup Development Environment

Install VS Code
Install Node.js
Create Folder & Files Structures

ProjectName
-> backend
----> index.js
----> db.js
-> frontend
----> index.html

Initialize NPM Package

Open "ProjectName" Folder in VSCode
Select "backend" folder > Right Click > Open in Integrated Terminal
Initialize NPM

npm init

Install Express & Postgres

npm i express
npm i mysql2
npm i cors
npm i dotenv
npm i nodemon

express = Express
mysql = MySQL2
cors = Cross Origin Resource Sharing
dotenv = Enviroment Variables
nodemon = Automatically Restart Node Application

Write Codes

Go to index.js in backend folder
Include Express and CORS Modules
Initialize Express and PORT number
Set Express app to show its running and listen to PORT

//////////////////////////////////////////////////////
// INCLUDES
//////////////////////////////////////////////////////
const express = require('express');
const cors = require('cors');

//////////////////////////////////////////////////////
// INIT
//////////////////////////////////////////////////////
const app = express();
const PORT = process.env.PORT || 3000;

//////////////////////////////////////////////////////
// DISPLAY SERVER RUNNING
//////////////////////////////////////////////////////
app.get('/',(req,res)=> {
    res.send(`Server running on port ${PORT}`)
});

app.listen(PORT,()=> {
    console.log(`App listening to port ${PORT}`);
});

Edit package.json

Look for package.json in backend folder
Edit the scripts to

"scripts": {
    "start": "node ./index.js",
    "dev": "nodemon /index.js"
  }

Run Server

Start the server by doing the following in Integrated Terminal

npm start

You should see the following log message in console or terminal

App listening to port 3000

Open Browser > Go to the URL ---> http://localhost:3000
You should see the body of the document showing

Server running on port 3000

Install Postman

Go to Postman to sign up an account
Download the App
Login and click on [ + ] to create a new Workspace

Type in URL in Text Field beside the word GET

GET Request with Postman

For GET, use Params and set the Key and Value

POST Request with Postman

You can use raw JSON [ Recommended ]

For POST, use Body > raw
Then set the Key and Value in JSON formats

Setup Express App

Setup to use CORS
Setup to use JSON

//////////////////////////////////////////////////////
// SETUP APP
//////////////////////////////////////////////////////
app.use(cors());
app.use(express.json());

POST and GET Methods in Express

Create both POST and GET methods
req is Request
res is Response
Set the Routing Path to "/api"

//////////////////////////////////////////////////////
// POST GET METHODS
// http://localhost:3000/api/
// Use Postman to test
//////////////////////////////////////////////////////
app.get('/api', async (req, res, next) => {
    console.log(req.query);

    res.json(req.query);
});

app.post('/api', async (req, res, next) => {
    console.log(req.body);

    res.json(req.body);
});

Test POST in Express

Type in the Key and Value, press Send
You should see the response body.

Test GET in Express

Type in the Key and Value, press Send
You should see the response body.

Setup MySQL DB Instance

Go to PlanetScale
Register an account
Click on Create for New Database

Create Environment Variables File

Copy the URL from your PlanetScale via Connect

Select Connect with Node.js

Select .env file to edit

Add DATABASE_URL, paste the URL you copied from your PlanetScale as the value.

DATABASE_URL=mysql://YourUserName:YourPassword@localHost:5432/YourDatabaseName?ssl={"rejectUnauthorized":true}'

Setup DB Connection

Go to db.js in backend folder
Include Enviroment Variables (dotenv) module
Include MySQL (mysql2) module

//////////////////////////////////////////////////////////////////////////
// INCLUDES
//////////////////////////////////////////////////////////////////////////

/* Include to use .env file */
require('dotenv').config();

const mysql = require('mysql2')

//////////////////////////////////////////////////////////////////////////
// DATABASE_URL extracted from .env file
//////////////////////////////////////////////////////////////////////////
const connection = mysql.createConnection(process.env.DATABASE_URL)
console.log("DB Connected", connection)
module.exports = connection;

Include DB Module

Go to index.js in backend folder
Include the module from db.js to use the Postgres Pool

const connection = require('./db'); //Import from db.js

Create Table

Create a constant variable CREATE_TABLE_SQL to store the SQL statement
Set the Routing Path to "/api/create_table"
Use the Pool to query using the SQL statement

//////////////////////////////////////////////////////
// SETUP DB
//////////////////////////////////////////////////////
const CREATE_TABLE_SQL = `
    CREATE TABLE messages (
        id INT NOT NULL AUTO_INCREMENT,
        message TEXT NOT NULL,
        PRIMARY KEY (id)
    );
`;

app.post('/api/table', (req, res, next) => {
    
    connection.promise().query(CREATE_TABLE_SQL)
    .then(() => {
         res.send(`Table created`);
    })
    .catch((error) => {
        res.send(error);
    });
});

Test in Postman to create the table using http://localhost:3000/api/table by POST
The response will show "Table created"
If the table was created previously, it will show error.

Drop Table

Create a constant variable DROP_TABLE_SQL to store the SQL statement
Set the Routing Path to "/api/table"
Use the Pool to query using the SQL statement

NOTE: This is important if you want to try create the table again.

//////////////////////////////////////////////////////
// CLEAR DB
//////////////////////////////////////////////////////
const DROP_TABLE_SQL = `
    DROP TABLE IF EXISTS messages;
`;

app.delete('/api/table', (req, res, next) => {
    
    connection.promise().query(DROP_TABLE_SQL)
    .then(() => {
        res.send(`Table dropped`);
    })
    .catch((error) => {
        res.send(error);
    });
});

Test in Postman to create the table using http://localhost:3000/api/table by DELETE
The response will show "Table dropped"

Add Routing Path to INSERT and SELECT

Set the Routing Path to "/api/message"
Use the GET to do SELECT, this is to get all messages in Table
Use the POST to do INSERT, this is to add new message into Table

//////////////////////////////////////////////////////
// POST GET METHODS CONNECTED TO DB
//////////////////////////////////////////////////////
app.get('/api/message', async (req, res, next) => {
    
    try
    {
        console.log(req.query);

        const allMessage = await connection.promise().query("SELECT * FROM messages"); 

        res.json(allMessage[0]);
    }
    catch(error)
    {
        console.error(error);
        res.send(error);
    }
});

app.post('/api/message', async (req, res, next) => {
    try
    {
        console.log(req.body);
        let message = req.body.message;
        console.log("message", message);
        const newInsert = await connection.promise().query("INSERT INTO messages (`message`) VALUES (?)", [message]);

        res.json(newInsert);
    }
    catch(error)
    {
        console.error(error);
        res.send(error);
    }
});

Test POST in Postman

Method 1 (Recommended)

Using raw & JSON
Using the POST, set the Body to { "message" : "Any message you preferred" } .
Once Send, you should see the response.

Method 2

Using the POST, set the Key to "message" and Value to "Any message you preferred".
Once Send, you should see the response.

Test GET in Postman

Using the GET, without any Params.
Once click on Send, you should see the response.
You should see all the previous inserts.

Add Routing Path to UPDATE and SELECT by ID

Set the Routing Path to "/api/message/:id"
Use the GET to do SELECT by ID, this is to get messages in Table by row ID
Use the POST to do INSERT by ID, this is to update message in Table by row ID

app.get('/api/message/:id', (req, res, next) => 
{
    console.log(req.params);
    let id = req.params.id;

    const SQLSTATEMENT = "SELECT * FROM messages WHERE id = ?";
    const VALUES = [id];

    connection.promise().query(SQLSTATEMENT, VALUES)
    .then(([rows,fields]) => {
        console.log(rows);
        res.json(rows);
    })
    .catch((error) => {
        res.send(error);
    });
});

app.put('/api/message/:id', (req, res, next) => 
{
    console.log(req.params);
    let id = req.params.id;

    let message = req.body.message;
    console.log("message", message);

    const SQLSTATEMENT = `
        UPDATE messages 
        SET 
            message = ?
        WHERE
            id = ?
    `;
    const VALUES = [message, id];

    connection.promise().query(SQLSTATEMENT, VALUES)
    .then(([rows,fields]) => {
        console.log(rows);
        res.json(rows);
    })
    .catch((error) => {
        res.send(error);
    });
});

Test SELECT by ID in Postman

Test UPDATE by ID in Postman

Using raw & JSON
Using the POST, set the Body to { "message" : "What you want to update" } .
Once Send, you should see the response.