https rest api - RESTful API with NodeJS/Express mysql

글 수 63

RESTful API with NodeJS/Express mysql

admin

http://webs.co.kr/index.php?document_srl=3315839

2018.05.19 08:39:58 (*.160.88.18)

6784

https://arjunphp.com/creating-restful-api-express-js-node-js-mysql/

Do you want to create a RESTful API with NodeJS/Express Js framework? Here is the tutorial, by reading this tutorial you can create our own REST API application server. Nowadays, it’s quite common to create web and mobile apps that consume data from APIs. This is providing easy decoupling, client or server can be developed by different teams or same.

we are going to use a very popular web framework called Express to create REST APIs.

What is Express js?

Express is a minimalist web framework that was highly inspired by the Sinatra framework from the Ruby language. With this module, you can create anything from small applications to large, complex ones. This framework allows you to build APIs and also to create simple websites.

Here is main advantages of Express:
1. Easily integrate with template engines
2. No database or persistence data storage, you can integrate with any object-relational mapping (ORM) or object data mappers (ODMs).
3. flexible and Robust routing.
4. Minimalist code
5. sophisticated middlewares concept.
6. A huge list of third-party middlewares to integrate

NOTE: You have to install NodeJs and MySQL software.

Database and table

Name your database whatever you want, and run below shown SQL it will create task table.

-- 
-- Table structure for `tasks`
-- 
CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(11) NOT NULL,
  `task` varchar(200) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `tasks` ADD PRIMARY KEY (`id`);
ALTER TABLE `tasks` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Insert some sample data into the tasks table.

INSERT INTO `tasks` (`id`, `task`, `status`, `created_at`) VALUES
(1, 'Find bugs', 1, '2016-04-10 23:50:40'),
(2, 'Review code', 1, '2016-04-10 23:50:40'),
(3, 'Fix bugs', 1, '2016-04-10 23:50:40'),
(4, 'Refactor Code', 1, '2016-04-10 23:50:40'),
(5, 'Push to prod', 1, '2016-04-10 23:50:50');

Setup Node Restful Project

Go to Terminal or Command Line, create a project folder.

$ mkdir express-node-rest-project
$ cd express-node-rest-project

Initialize your node project with below npm command, which will create a file called package.json and it will ask few questions about the project if you want to avoid questions, use — yes flag.

$ npm init --yes

Now install express js framework and MySQL driver with NPM.

$ npm install express --save
$ npm install mysql --save
$ npm install body-parser --save

Here is my final package.json file.

{
  "name": "express-node-rest-project",
  "version": "1.0.0",
  "description": "",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.15.2",
    "mysql": "^2.13.0"
  }
}

We are going to implement following API calls –

Method	URL	Action
GET	/todos	Retrieve all todos
GET	/todos/search/bug	Search for todos with ‘bug’ in their name
GET	/todo/1	Retrieve todo with id == 1
POST	/todo	Add a new todo
PUT	/todo	Update todo with id == 1
DELETE	/todo	Delete todo with id == 1

Create Express Server

Create server.js file, Open it with editor, copy following code. Please read the comments for better understanding.

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
 
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: true
}));
 
 
// default route
app.get('/', function (req, res) {
    return res.send({ error: true, message: 'hello' })
});
 
// port must be set to 8080 because incoming http requests are routed from port 80 to port 8080
app.listen(8080, function () {
    console.log('Node app is running on port 8080');
});

Express server is ready, you can start your server with node server.jscommand, to see output point your browser to http://localhost:8080.

Node MySQL – Database Connection

Update your server.js file with MySQL connection code. Here you have to modify the MySQL database name, host, username and password.

const express = require('express');
const app = express();
const mysql = require('mysql');
...
....
....
 
// connection configurations
const mc = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'node_task_demo'
});
 
// connect to database
mc.connect();
 
// default route
app.get('/', function (req, res) {
.....
.....
.....

Implementing the API calls with express js

Now that we have our express server up and running with the database connection, we need to manage todos in the database.

Getting the Todos list – We are going to create a new route so that when a user hits /todos, it will return a list of all todos in JSON format. Update your server.js with below code.

// Retrieve all todos 
app.get('/todos', function (req, res) {
    mc.query('SELECT * FROM tasks', function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Todos list.' });
    });
});

This function simply return all todos information as you can see in this query, to call this API use this URL http://localhost:8080/todos.

Getting single todo – We are going to create a new route so that when a user hits /todo/{id}, it will return a todo in JSON format.

// Retrieve todo with id 
app.get('/todo/:id', function (req, res) {
 
    let task_id = req.params.id;
 
    if (!task_id) {
        return res.status(400).send({ error: true, message: 'Please provide task_id' });
    }
 
    mc.query('SELECT * FROM tasks where id=?', task_id, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results[0], message: 'Todos list.' });
    });
 
});

This function checks record of given id and returns if found anything, to call this API use this URL http://localhost:8080/todo/1.

Find todo by name – We are going to create a new route so that when a user hits /todos/search/{Query}, it will return a list of all matched todos in JSON format.

// Search for todos with ‘bug’ in their name
app.get('/todos/search/:keyword', function (req, res) {
    let keyword = req.params.keyword;
    mc.query("SELECT * FROM tasks WHERE task LIKE ? ", ['%' + keyword + '%'], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Todos search list.' });
    });
});

This function search in database for your given query, to call this API use this URL http://localhost:8080/todos/search/bug

Add todo – We are going to create a new route so that when a user sends a post request to /todo with required data, app will add a new record to the database.

// Add a new todo  
app.post('/todo', function (req, res) {
 
    let task = req.body.task;
 
    if (!task) {
        return res.status(400).send({ error:true, message: 'Please provide task' });
    }
 
    mc.query("INSERT INTO tasks SET ? ", { task: task }, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'New task has been created successfully.' });
    });
});

This API accepts post request and insert submitted data in your database. To call this API use this URL http://localhost:8080/todo

Delete Task – We are going to create a new route so that when a user sends a delete request to /todo/{id}, the app will delete a record from the database.

//  Delete todo
app.delete('/todo', function (req, res) {
 
    let task_id = req.body.task_id;
 
    if (!task_id) {
        return res.status(400).send({ error: true, message: 'Please provide task_id' });
    }
    mc.query('DELETE FROM tasks WHERE id = ?', [task_id], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Task has been updated successfully.' });
    });
});    

Update Task – We are going to create a new route so that when a user sends a put request to /todo/{id} with required data, the app will update a record based on match parameter in the database.

//  Update todo with id
app.put('/todo', function (req, res) {
 
    let task_id = req.body.task_id;
    let task = req.body.task;
 
    if (!task_id || !task) {
        return res.status(400).send({ error: task, message: 'Please provide task and task_id' });
    }
 
    mc.query("UPDATE tasks SET task = ? WHERE id = ?", [task, task_id], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Task has been updated successfully.' });
    });
});

This API accept put request and updates submitted data in your database. To call this API use this URL http://localhost:8080/todo/{id}

Here is the complete server.js file.

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const mysql = require('mysql');
 
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: true
}));
 
// connection configurations
const mc = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'node_task_demo'
});
 
// connect to database
mc.connect();
 
// default route
app.get('/', function (req, res) {
    return res.send({ error: true, message: 'hello' })
});
 
// Retrieve all todos 
app.get('/todos', function (req, res) {
    mc.query('SELECT * FROM tasks', function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Todos list.' });
    });
});
 
// Search for todos with ‘bug’ in their name
app.get('/todos/search/:keyword', function (req, res) {
    let keyword = req.params.keyword;
    mc.query("SELECT * FROM tasks WHERE task LIKE ? ", ['%' + keyword + '%'], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Todos search list.' });
    });
});
 
// Retrieve todo with id 
app.get('/todo/:id', function (req, res) {
 
    let task_id = req.params.id;
 
    mc.query('SELECT * FROM tasks where id=?', task_id, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results[0], message: 'Todos list.' });
    });
 
});
 
// Add a new todo  
app.post('/todo', function (req, res) {
 
    let task = req.body.task;
 
    if (!task) {
        return res.status(400).send({ error:true, message: 'Please provide task' });
    }
 
    mc.query("INSERT INTO tasks SET ? ", { task: task }, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'New task has been created successfully.' });
    });
});
 
//  Update todo with id
app.put('/todo', function (req, res) {
 
    let task_id = req.body.task_id;
    let task = req.body.task;
 
    if (!task_id || !task) {
        return res.status(400).send({ error: task, message: 'Please provide task and task_id' });
    }
 
    mc.query("UPDATE tasks SET task = ? WHERE id = ?", [task, task_id], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Task has been updated successfully.' });
    });
});
 
//  Delete todo
app.delete('/todo/:id', function (req, res) {
 
    let task_id = req.params.id;
 
    mc.query('DELETE FROM tasks WHERE id = ?', [task_id], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Task has been updated successfully.' });
    });
 
});
 
// all other requests redirect to 404
app.all("*", function (req, res, next) {
    return res.send('page not found');
    next();
});
 
// port must be set to 8080 because incoming http requests are routed from port 80 to port 8080
app.listen(8080, function () {
    console.log('Node app is running on port 8080');
});
 
// allows "grunt dev" to create a development server with livereload
module.exports = app;

Download Code

I hope you like this Post, Please feel free to comment below, your suggestion and problems if you face - we are here to solve your problem

이 게시물을...

번호	제목	글쓴이	날짜	조회 수
63	[SSL]HTTPS통신을 위한 SSL인증서 발급하기(OpenSSL) 원리까지 충실하게 설명잘됨	admin	2020-02-13	10080
62	인증 기관에서 발급한 SSL 인증서 설치 및 사용설명	admin	2020-02-13	5319
61	iOS NSURLSession Example (HTTP GET, POST, Background Downlads )	admin	2019-06-04	7462
60	iOS Tutorial - Part 26 - HttpRequest POST, GET (NSURLConnection)1	admin	2019-05-20	6176
59	Simple http post example in Objective-C?	admin	2019-05-20	5695
58	리눅스 CentOS 6.5 SSL 구축 방법	admin	2018-06-01	7714
57	무료 SSL 인증서 SSL For Free	admin	2018-05-26	7762
56	도메인클럽 m 또는 www등의 서브도메인(a레코드) 추가는 어디서 할 수 있나요?	admin	2018-05-25	6998
55	Certificate Installation : Node.js in Linux	admin	2018-05-25	6294
54	online-csr-and-key-generator CSR 온라인 제너레이터 생성 만들기 Private key	admin	2018-05-25	6426
53	COMODO SSL www.gogetssl.com namecheap.com SSL 인증서 구매 서버에 적용 순서	admin	2018-05-25	9423
52	인증서 취소 How to cancel an SSL certificate? www.namecheap.com	admin	2018-05-25	8197
51	Android SSL 프로그램 공인인증서 사설 인증서 ROOTCA Self-signed 인증서 에러 원인	admin	2018-05-25	7022
50	Retrofit is one of the most popular HTTP Client Library for Android 간결하고 요점정리	admin	2018-05-23	7130
49	SSL 프로그래밍 참고	admin	2018-05-23	6374
48	HTTPS 및 SSL을 사용한 보안 구글 문서	admin	2018-05-23	9968
47	Consuming APIs with Retrofit	admin	2018-05-22	8419
46	COMODO SSL Analyzer ip 도메인 모두 가능합니다	admin	2018-05-22	6502
45	OpenSSL 로 ROOT CA 생성 및 SSL 인증서 발급 순서 Self Signed Certificate	admin	2018-05-22	7319
44	Android에 루트 CA 설치	admin	2018-05-22	8409
43	How to update OpenSSL on Debian testing	admin	2018-05-22	6959
42	신뢰되지 않는 인증서를 사용하여 SSL 구성	admin	2018-05-22	11434
41	OpenSSL tips and common commands	admin	2018-05-22	7154
40	How to get FREE SSL Certificate for Website (HTTPS) 인증서 무료로 받기	admin	2018-05-22	6260
39	retropit	admin	2018-05-20	6424
38	SSL test code	admin	2018-05-20	6910
37	HttpsURLConnection в ASyncTask https	admin	2018-05-19	6511
36	protected String doInBackground(String... strings) {	admin	2018-05-19	6392
35	Using Google Spread sheet as DataBase Part -2	admin	2018-05-19	6893
34	Android: HTTPS (SSL) connection using HttpsURLConnection	admin	2018-05-19	7108
33	HttpsUrlConnection, you can refer to my following sample code good	admin	2018-05-19	6435
32	how to use HttpsUrlConnection instead of DefaultHttpClient	admin	2018-05-19	6367
31	Https simple get request	admin	2018-05-19	6467
30	Base64 encoded value of [API-key]:[API-Secret] appending the "Basic " string in start.	admin	2018-05-19	7759
29	Class: https.Server	admin	2018-05-19	6932
28	HTTP2 server push in depth with node.js	admin	2018-05-19	7245
27	HSTS forces the client (browser accessing your server) to direct all traffic through HTTPS	admin	2018-05-19	6540
26	https ssl node js real code	admin	2018-05-19	6712
25	https 및 openssl 키값 decoding 확인 ssl tls	admin	2018-05-19	6620
24	TLS/SSL Concepts nodejs how to	admin	2018-05-19	7119
23	HTTPS Authorized Certs with Node.js	admin	2018-05-19	7300
22	How to Use SSL/TLS with Node.js Related Topics	admin	2018-05-19	7244
21	openssl website	admin	2018-05-19	6278
20	node js HTTPS server and client	admin	2018-05-19	7259
	RESTful API with NodeJS/Express mysql	admin	2018-05-19	6784
18	Build a Rest API for Node & Mysql 2018 JWT	admin	2018-05-19	7071
17	Using SSL with Express 4 and Node.js	admin	2018-05-19	6969
16	https node js rest api express	admin	2018-05-19	6298
15	9 FREE Useful Online SSL/TLS Certificate Tools	admin	2018-05-19	7975
14	Do you know where your app’s secrets are?	admin	2018-05-19	7090
13	node js rest with express	admin	2018-05-19	7151
12	simple HTTPS JSON REST server using node.js	admin	2018-05-19	6334
11	5 Ways to Make HTTP Requests in Node.js	admin	2018-05-19	23244
10	express() detail easy doc	admin	2018-05-19	19245
9	express https simple example	admin	2018-05-19	6189
8	Online CSR and Key Generator	admin	2018-05-19	6312
7	SSL Converter checker	admin	2018-05-19	7086
6	here is a complete working example. rest api https	admin	2018-05-19	6232
5	node-rest-client	admin	2018-05-19	6993
4	Rules for REST API URI Design	admin	2018-05-19	7377
3	RESTful API Authentication Basics	admin	2018-05-19	7701
2	Benefits For REST APIs with HTTP/2 HTTP/1.x vs HTTP/2	admin	2018-05-19	60992
1	HTTPS, Redis, FCM, EC2 Setup 키생성 인증서 요청서 openssl 이용 상세한설명	admin	2018-05-19	5864

쓰기

첫 페이지 1 끝 페이지

Coding