Building Simple REST API with Express.js and MySQL
Tags: javascript, webdev, node
Published: 2020-04-16 01:45:02

Express.js is node.js web application which working as backend, operate some database, and so on. In this article we will learn how to make REST API.

![Expressjs](https://www.maxrooted.com/panduan-membangun-rest-api-expressjs-mysql/cover.png)

## REST API Express and MySQL

Requirements to keep following this tutorial, assume that you already know the basic level working with express.js and also SQL Language.

### Creating project directory

```bash
mkdir simple-api
cd simple-api
```
> **Pro Tip !**
> Actually, you can scaffold or initialize express' project with express-generator npm package.

### Generate package.json

```bash
npm init
```

### Installing Express.js and its Dependencies

```bash
npm install express --save
```
```bash
npm install cors body-parser mysql --save
```

### Creating `index.js`

Now what we should do is to create file `index.js` in our project's root directory. and write/paste the following code below in this file.

```bash
const express = require('express'),
  app = express(),
  cors = require('cors'),
  bodyParser = require('body-parser');

// make server object that contain port property and the value for our server.
var server = {
  port: 4040
};

// use the modules
app.use(cors())
app.use(bodyParser.json());

// starting the server
app.listen( server.port , () => console.log(`Server started, listening port: ${server.port}`));
```

to ensure, that the code work properly. you can test it.

```bash
node index.js
```
**output:**
```bash
Server started, listening on port: 4040
```

### Preparing The Database

```mysql
CREATE DATABASE simpleapi;
USE simpleapi;
```

and then, make new table called `users`.

```mysql
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY DEFAULT NULL,
name VARCHAR(125),
gender VARCHAR(25)
);
```

after that, connect your database.

```javascript
const express = require('express'),
  app = express(),
  mysql = require('mysql'), // import mysql module
  cors = require('cors'),
  bodyParser = require('body-parser');

// setup database
db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'simpleapi'
})

// make server object that contain port property and the value for our server.
var server = {
  port: 4040
};
......

```

Next, create new directory called in your project's root directory `routes` and put new file named `users.js`.

```bash
$ mkdir routes
$ tree -I node_modules
.
├── index.js
├── package.json
├── package-lock.json
└── routes

1 directory, 3 files
$ touch routes/users.js
```

Paste this code in `users.js`

```javascript
const express = require('express'),
  router = express.Router();

// get user lists
router.get('/list', function(req, res) {
  let sql = `SELECT * FROM users`;
  db.query(sql, function(err, data, fields) {
    if (err) throw err;
    res.json({
      status: 200,
      data,
      message: "User lists retrieved successfully"
    })
  })
});

// create new user
router.post('/new', function(req, res) {
  let sql = `INSERT INTO users(name, gender) VALUES (?)`;
  let values = [
    req.body.name,
    req.body.gender
  ];
  db.query(sql, [values], function(err, data, fields) {
    if (err) throw err;
    res.json({
      status: 200,
      message: "New user added successfully"
    })
  })
});

module.exports = router;
```

and edit file `index.js` as below.

```javascript
....
// routers
const usersRouter = require('./routes/users');
// use the modules
app.use(cors())
app.use(bodyParser.json());
// use router
app.use('/users', usersRouter);

app.listen( server.port , () => console.log(`Server started, listening on port: ${server.port}`));
```

### Trying to make a new user with `curl`.

first of all, you must have curl installed on your machine.
```bash
curl -X POST \
 http://localhost:4040/users/new \
 -H "Content-Type: application/json" \
 -d '{"name":"Nurahmat", "gender":"male"}'
```
the response will be.
```bash
{"status":200,"message":"New user added successfully"}
```

to confirm that this user available, we will access created router `http://localhost:4040/users/list`.

![Router users list](https://www.maxrooted.com/panduan-membangun-rest-api-expressjs-mysql/router_users_list.png)

Gotcha ! a user with name **Nurahmat** available !.

## Add New User Through Form Input

Previously, we have demonstrate adding new user through `curl` now , how about if we do the same thing graphically, I mean through your browser with form input? Let's get started !

### Creating Form Input Page
```html
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Tambah User Baru</title>
</head>
<body>
 <form action="/users/new" method="POST">
  <input type="text" name="name" placeholder="Nama">
  <label>Gender:</label>
  <label for="male1">
  <input id="male1" type="checkbox" name="gender" value="male">
  Male
  </label>
  <label for="female1">
  <input id="female1" type="checkbox" name="gender" value="female">
  Female
  </label>
  <button type="submit">Add</button>
 </form>
</body>
</html>
```

Loading `path` node.js module.
```javascript
const express = require('express'),
  path = require('path') // add path module,
  app = express(),
  cors = require('cors'),
  mysql = require('mysql'),
  bodyParser = require('body-parser');
```

Creating new route that provide **form input page** and activate urlencoded parser (index.js)
```javascript
....
app.use(cors())
app.use(bodyParser.json());
app.use(express.json())
app.use(express.urlencoded({extended: true})) // parsing incoming requests with urlencoded based body-parser

// use router
app.use('/users', usersRouter);
// router user input
app.get('/', function(req, res) {
  res.sendFile(path.resolve(__dirname,'views') + '/input.html');
});
...
```

if we access the route, `localhost:4040/`

![Form input page](https://www.maxrooted.com/panduan-membangun-rest-api-expressjs-mysql/route_user_input.png)

Now, let's try to add new user.

![Adding new user with form input](https://www.maxrooted.com/panduan-membangun-rest-api-expressjs-mysql/test_add_user.gif)

and then ! what was happened ?! can you tell me ! Hope you enjoying this article, and tell me there's more proper way for how to create Rest API with express.js and MySQL because I also a beginner, like you...

Thanks,
reference: [Maxrooted](https://www.maxrooted.com/panduan-membangun-rest-api-expressjs-mysql/)