A Simple Express Server and MySQL Database
Tags: javascript, programming, webdev, mysql
Published: 2021-02-09 21:04:44

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](https://dev.mysql.com/doc/mysql-getting-started/en/#mysql-getting-started-installing), 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:  
```sql
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. 
```sql
mysql -u oshan -p
```
Now, let's create a database.  
```sql
CREATE DATABASE Restaurant;
USE Restaurant;
```
Now, let's create a table called Menu. 
```sql
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:

```sql
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.

```javascript
PORT=11000
DBHOST=localhost
DBUSERNAME=oshan
DBPASSWORD=oshan
```
Now, in package.json add the following JSON.

```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. 
```json
"start:auto" : "nodemon index.js"
```

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

```javascript
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.

```javascript
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:

```javascript
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
```
![Postman Screenshot of GET request](https://dev-to-uploads.s3.amazonaws.com/i/vapokexu33fgjeob1eqz.png)
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.

```javascript
//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.   
![Postman Screenshot of POST request](https://dev-to-uploads.s3.amazonaws.com/i/hdmx34dajlcvpqaj1sm6.png)

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.

```javascript
//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)
```
![Postman Screenshot of Post request](https://dev-to-uploads.s3.amazonaws.com/i/vf23sfg7ua9sxe9qkbdx.png)

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](https://oshanoshu.github.io) 

For complete code: 
{% github oshanoshu/Blog_References no-readme %}