A Simple Express Server and MySQL Database

Let's create a MySQL database and create an Express server to expose its data via API endpoints.

Prerequisites:

1. Desire to learn
2. Simple Command Line Usage
3. MySQL server installed in your device
4. Some knowledge of JavaScript

For installing MySQL server on your system, go to this My SQL Installation Guide, and follow according to your system requirements. I use OpenSUSE Tumbleweed, and it shouldn't matter what system you use.

Let's login with your MySQL username and password.

mysql -u root -p

It's better to create a new user other than root to handle your database. So, let's create a new user:

CREATE USER 'oshan'@'localhost' IDENTIFIED BY 'oshan';
GRANT ALL ON *.* TO 'oshan'@'oshan';

And, we granted privileges to user oshan to create a database and all kinds of stuff. Now, login with the new user you created.

mysql -u oshan -p

Now, let's create a database.

CREATE DATABASE Restaurant;
USE Restaurant;

Now, let's create a table called Menu.

CREATE TABLE Menu
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    foodname VARCHAR(30) NOT NULL,
    price DECIMAL(13, 2) NOT NULL,
    description VARCHAR(255),
    availability BOOLEAN
)

Let's populate the table with some values:

Insert into Menu (foodname, price, description, availability) values ("Chicken Sandwich", 5.99, "Sandwich with Chicken and Pickel on the side", true);
Insert into Menu (foodname, price, description, availability) values ("Chicken Fried Rice", 7.99, "Fried Rice with chicken bits, comes with a complimentary
drink", true);

Now, let's create an Express server to communicate with the database. We can set up the initial files as follows:

touch .env index.js package.json config.js

We need to set up the environment variables for the server. So, in the .env file, we state the following variables. These variables are the configurations for your MySQL database which we will use later to connect to a database.

PORT=11000
DBHOST=localhost
DBUSERNAME=oshan
DBPASSWORD=oshan

Now, in package.json add the following JSON.

{
    "name": "restaurant_server",
    "version": "1.0.0",
    "description": "Serves Menu for restaurant",
    "main": "index.js",
    "engines": {
        "node" : "14.x"
    },
    "scripts": {
        "start": "node index.js",
    }
}

Let's install some dependencies. This will further populate the package.json. If you are curious to see what dependencies were installed, you can go to package.json.

npm install dotenv mysql express nodemon

We add nodemon in the scripts in package.json so that we don't have to manually restart the server every time we make some changes in our code.

"start:auto" : "nodemon index.js"

Let's set up the connection to the database in config.js.

const dotenv = require("dotenv");
dotenv.config();

const mysql = require('mysql');
let connection;
try {
    connection = mysql.createConnection({
        host: process.env.DBHOST,
        user: process.env.DBUSERNAME,
        password: process.env.DBPASSWORD,
        database: process.env.DBNAME
    });
} catch (error) {
    console.log("We got an error");
}

module.exports = {connection}

Once we do this, let's create our app that would act as a server to expose our database as API endpoints.

const dotenv = require("dotenv");

//this is the connection to the database 
const {connection} = require("./config");
const express = require("express");
const bodyParser = require('body-parser')

const app = express();

//body-parser helps parse the request body in POST request
app.use(bodyParser.json())
app.use(bodyParser.urlencoded({extended: true}))

//you can call your environment variables after this 
dotenv.config();

app.listen(process.env.PORT||11000,() =>{
    console.log("Server is running")
})

module.exports = app;

Now, let's run our server nodemon run start:auto.First, test if our GET request can be made successfully, It should return the two menu items we inserted earlier. You can use an app like Postman or you can simply go to your browser and enter the URL for the GET request. In our case:

const getMenuItems = (request, response) => {
    connection.query("SELECT * FROM Menu", 
    (error, results) => {
        if(error)
            throw error;
        response.status(200).json(results);
    })
}

app.route("/menuItems")
.get(getMenuItems)

Now, lets run our server nodemon run start:auto.First, test if our GET request can be made succesfully, It should return the two menu items we inserted earlier. You can use app like Postman or you can simply go to your browser and enter the url for GET request. In our case:

localhost:11000/menuItems

Now, let's try to add something into the database using the POST request. We'll pass our value as JSON and this app will populate the database for us. We didn't mention id because we have set the id to auto_increment while we created the table.

//Add this to index.js
const postMenuItems = (request, response) => {
    const {foodname, price, discussion, availability} = request.body;
    connection.query("INSERT INTO Menu(foodname, price, discussion, availability) VALUES (?,?,?,?) ", 
    [foodname, price, availability],
    (error, results) => {
        if(error)
            throw error;
        response.status(201).json({"Menu Item Added":results.affectedRows});
    })
}

//route for accessing the endpoint
app.route("/menuItems")
.post(getMenuItems)

Let's see if this works using Postman.

And, it works like a charm. Still, if you want to check, you can make a GET request and see if your recent entry is returned.

Now, let's do a DELETE request to wrap this thing. We'll pass the id of the menu item we want to delete as a parameter via URL. And, make sure you absolutely want to delete the data because you'll be deleting it from the database.

//Add this to index.js

const deleteMenuItems = (request, response) => {
    const id = request.params.id;
    connection.query("Delete from Menu where id = ?", 
    [id],
    (error, results) => {
        if(error)
            throw error;
        response.status(201).json({"Menu Item Deleted":results.affectedRows});
    })
}

//route for accessing this endpoint
app.route("/menuItems/:id")
.delete(deleteMenuItems)

Again you can make the GET request to see if the changes are reflected.

EXTRA:

You can use this method for most SQL databases. All you need to do is simply tweak in config.js.

NEXT: We'll learn some "good to know" Linux/Unix Commands.

You can also find this on my website: Oshan Upreti

For complete code:

oshanoshu / Blog_References

Comments

[Vincent Milum Jr]

"It's better to create a new user other than root to handle your database."

While great in theory, in the tutorial, you gave this new user the exact same permissions as root, effectively making them another root user, only under a different name.

[Oshan Upreti]

That’s astute observation. That certainly contradicts the earlier statement. But, I have written that we have gave this user all kinds of privileges on the following line.
I’ll add a line for the way not to give user all kind of permissions.
Thanks for reading :)

[boiledsteak]

lolol 'desire to learn'

[Oshan Upreti]