DEV Community

Nurofsun
Nurofsun

Posted on

Express MySQL Building Simple REST API with Express.js and MySQL

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

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

mkdir simple-api
cd simple-api
Enter fullscreen mode Exit fullscreen mode

Pro Tip !
Actually, you can scaffold or initialize express' project with express-generator npm package.

Generate package.json

npm init
Enter fullscreen mode Exit fullscreen mode

Installing Express.js and its Dependencies

npm install express --save
Enter fullscreen mode Exit fullscreen mode
npm install cors body-parser mysql --save
Enter fullscreen mode Exit fullscreen mode

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.

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

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

node index.js
Enter fullscreen mode Exit fullscreen mode

output:

Server started, listening on port: 4040
Enter fullscreen mode Exit fullscreen mode

Preparing The Database

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

and then, make new table called users.

CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY DEFAULT NULL,
name VARCHAR(125),
gender VARCHAR(25)
);
Enter fullscreen mode Exit fullscreen mode

after that, connect your database.

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
};
......

Enter fullscreen mode Exit fullscreen mode

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

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

1 directory, 3 files
$ touch routes/users.js
Enter fullscreen mode Exit fullscreen mode

Paste this code in users.js

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

and edit file index.js as below.

....
// 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}`));
Enter fullscreen mode Exit fullscreen mode

Trying to make a new user with curl.

first of all, you must have curl installed on your machine.

curl -X POST \
 http://localhost:4040/users/new \
 -H "Content-Type: application/json" \
 -d '{"name":"Nurahmat", "gender":"male"}'
Enter fullscreen mode Exit fullscreen mode

the response will be.

{"status":200,"message":"New user added successfully"}
Enter fullscreen mode Exit fullscreen mode

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

Router users list

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

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

Loading path node.js module.

const express = require('express'),
  path = require('path') // add path module,
  app = express(),
  cors = require('cors'),
  mysql = require('mysql'),
  bodyParser = require('body-parser');
Enter fullscreen mode Exit fullscreen mode

Creating new route that provide form input page and activate urlencoded parser (index.js)

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

if we access the route, localhost:4040/

Form input page

Now, let's try to add new user.

Adding new user with form input

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

Discussion (2)

Collapse
akapesh profile image
akapesh

@nurofsun this has really helped alot but i used a different approach with curl to achieve a similar output check it out
curl -v -u " : " "localhost:4040/users/new" -H "Content-Type:application/json" -d '{"name":"Pesh","gender":"female"}'
i am new to curl so thank you for introducing it to me. Kudos..

Collapse
santulanchaubey profile image
Santulan Chaubey

The code is wonderful. Learned a lot.
I am getting following error when I post data. Can you help please?

TypeError: Cannot read property 'hitpoint' of undefined
at C:\Users\Santulan.DESKTOP-N72FI38\Documents\ReactApps\sample\routes\users.js:38:28
at Layer.handle as handle_request
at next (C:\Users\Santulan.DESKTOP-N72FI38\node_modules\express\lib\router\route.js:137:13)
at Route.dispatch (C:\Users\Santulan.DESKTOP-N72FI38\node_modules\express\lib\router\route.js:112:3)
at Layer.handle as handle_request
at C:\Users\Santulan.DESKTOP-N72FI38\node_modules\express\lib\router\index.js:281:22
at Function.process_params (C:\Users\Santulan.DESKTOP-N72FI38\node_modules\express\lib\router\index.js:335:12)
at next (C:\Users\Santulan.DESKTOP-N72FI38\node_modules\express\lib\router\index.js:275:10)
at Function.handle (C:\Users\Santulan.DESKTOP-N72FI38\node_modules\express\lib\router\index.js:174:3)
at router (C:\Users\Santulan.DESKTOP-N72FI38\node_modules\express\lib\router\index.js:47:12)