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.
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
Pro Tip !
Actually, you can scaffold or initialize express' project with express-generator npm package.
Generate package.json
npm init
Installing Express.js and its Dependencies
npm install express --save
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.
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.
node index.js
output:
Server started, listening on port: 4040
Preparing The Database
CREATE DATABASE simpleapi;
USE simpleapi;
and then, make new table called users.
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY DEFAULT NULL,
name VARCHAR(125),
gender VARCHAR(25)
);
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
};
......
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
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;
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}`));
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"}'
the response will be.
{"status":200,"message":"New user added successfully"}
to confirm that this user available, we will access created router http://localhost:4040/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>
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');
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');
});
...
if we access the route, localhost:4040/
Now, let's try to add new user.
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
Top comments (2)
@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..
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)