DEV Community

Cover image for A Simple Express Server and MySQL Database
Oshan Upreti
Oshan Upreti

Posted on • Updated on • Originally published at oshanoshu.github.io

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
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Now, let's create a database.

CREATE DATABASE Restaurant;
USE Restaurant;
Enter fullscreen mode Exit fullscreen mode

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
)
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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",
    }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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}
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Postman Screenshot of GET request
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)
Enter fullscreen mode Exit fullscreen mode

Let's see if this works using Postman.

Postman Screenshot of POST request

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)
Enter fullscreen mode Exit fullscreen mode

Postman Screenshot of Post request

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:

Oldest comments (4)

Collapse
 
boiledsteak profile image
boiledsteak

lolol 'desire to learn'

Collapse
 
oshanoshu profile image
Oshan Upreti

desire to learn

Collapse
 
darkain profile image
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.

Collapse
 
oshanoshu profile image
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 :)