Express,Sequelize和MySQL的Node.js Rest API示例
Node.js Rest CRUD API概述示例视频创建Node.js应用建立Express Web服务器配置MySQL数据库并进行序列化初始化Sequelize定义Sequelize模型创建控制器创建一个新对象检索对象(有条件)检索单个对象更新对象删除对象删除所有对象按条件查找所有对象 定义路由测试API结论进一步阅读源代码本文翻译自Node.js Rest APIs example with Express, Sequelize & MySQL
最新修改:9月29号 bezkoder Node.js
Express是Node.js最受欢迎的Web框架之一,支持路由,中间件,视图系统…Sequelize是基于Promise的Node.js ORM,它支持Postgres,MySQL,SQL Server的方言。在本教程中,我将 向您展示如何使用Express,Sequelize和MySQL数据库来构建Node.js Restful CRUD API。
您应该首先在机器上安装MySQL。 可以在以下位置找到安装说明:
Official MySQL installation manual
相关文章:
Build Node.js Rest APIs with Express & MySQL (without Sequelize)Node.js: Upload/Import Excel file data into MySQL DatabaseNode.js: Upload CSV file data into MySQL Database
全栈:
Vue.js + Node.js + Express + MySQL exampleVue.js + Node.js + Express + MongoDB exampleAngular + Node.js Express + MySQL exampleReact + Node.js + Express + MySQL example
安全性:Node.js – JWT Authentication & Authorization example
部署:Deploying/Hosting Node.js app on Heroku with MySQL database
Node.js Rest CRUD API概述
我们将构建Rest Apis,它可以创建,检索,更新,删除和按标题查找教程。
首先,我们从Express Web服务器开始。 接下来,我们为MySQL数据库添加配置,使用Sequelize创建Tutorial
模型,编写控制器。 然后,我们定义用于处理所有CRUD操作(包括自定义查找程序)的路由。
下表概述了将要导出的Rest API:
最后,我们将使用Postman测试Rest Apis。
这是我们的项目结构:
示例视频
这是我们的与MySQL数据库一起运行的Node.js Express Sequelize应用程序演示,并通过Postman测试Rest Apis。
创建Node.js应用
首先,我们创建目录:
$ mkdir nodejs-express-sequelize-mysql$ cd nodejs-express-sequelize-mysql
接下来,我们使用package.json文件初始化Node.js App:
npm initname: (nodejs-express-sequelize-mysql) version: (1.0.0) description: Node.js Rest Apis with Express, Sequelize & MySQL.entry point: (index.js) server.jstest command: git repository: keywords: nodejs, express, sequelize, mysql, rest, apiauthor: bezkoderlicense: (ISC)Is this ok? (yes) yes
我们需要安装必要的模块:express
,sequelize
,mysql2
和body-parser
。
运行命令:
npm install express sequelize mysql2 body-parser cors --save
package.json文件应如下所示:
{"name": "nodejs-express-sequelize-mysql","version": "1.0.0","description": "Node.js Rest Apis with Express, Sequelize & MySQL","main": "server.js","scripts": {"test": "echo \"Error: no test specified\" && exit 1"},"keywords": ["nodejs","express","rest","api","sequelize","mysql"],"author": "bezkoder","license": "ISC","dependencies": {"body-parser": "^1.19.0","cors": "^2.8.5","express": "^4.17.1","mysql2": "^2.0.2","sequelize": "^5.21.2"}}
建立Express Web服务器
在根目录下,创建一个新的server.js
文件:
const express = require("express");const bodyParser = require("body-parser");const cors = require("cors");const app = express();var corsOptions = {origin: "http://localhost:8081"};app.use(cors(corsOptions));// parse requests of content-type - application/jsonapp.use(bodyParser.json());// parse requests of content-type - application/x-www-form-urlencodedapp.use(bodyParser.urlencoded({extended: true }));// simple routeapp.get("/", (req, res) => {res.json({message: "Welcome to bezkoder application." });});// set port, listen for requestsconst PORT = process.env.PORT || 8080;app.listen(PORT, () => {console.log(`Server is running on port ${PORT}.`);});
我们要做的是:
导入express
,body-parser
和cors
模块:
Express用于构建Rest APIbody-parser
有助于解析请求并创建req.body
对象cors
提供了Express中间件,以使CORS具有多种选择。
创建一个Express应用,然后使用app.use()
方法添加body-parser
和cors
中间件。 请注意,我们设置了origin:http:// localhost:8081
。
定义一个易于测试的GET路由。
在端口8080上侦听传入请求。
现在,使用以下命令运行该应用:node server.js
。
使用URLhttp:// localhost:8080/
打开浏览器,您将看到:
是的,第一步已经完成。 在下一节中,我们将与Sequelize一起协作。
配置MySQL数据库并进行序列化
在app文件夹中,我们创建一个单独的config文件夹,然后使用db.config.js文件进行配置,如下所示:
module.exports = {HOST: "localhost",USER: "root",PASSWORD: "123456",DB: "testdb",dialect: "mysql",pool: {max: 5,min: 0,acquire: 30000,idle: 10000}};
前面5个参数用于MySQL连接。
pool
是可选的,它将用于Sequelize连接池配置:
- max:池中的最大连接数- min:池中的最小连接数- idle:连接释放之前可以空闲的最长时间(以毫秒为单位)- acquire:该池将在抛出错误之前尝试获取连接的最长时间(以毫秒为单位)
有关更多详细信息,请访问API Reference for the Sequelize constructor。
初始化Sequelize
我们将在下一步包含模型的app/models
文件夹中初始化Sequelize。
现在,使用以下代码创建app/models/index.js
:
const dbConfig = require("../config/db.config.js");const Sequelize = require("sequelize");const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {host: dbConfig.HOST,dialect: dbConfig.dialect,operatorsAliases: false,pool: {max: dbConfig.pool.max,min: dbConfig.pool.min,acquire: dbConfig.pool.acquire,idle: dbConfig.pool.idle}});const db = {};db.Sequelize = Sequelize;db.sequelize = sequelize;db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);module.exports = db;
不要忘记在server.js
中调用sync()
方法
...const app = express();app.use(...);const db = require("./app/models");db.sequelize.sync();...
在开发中,您可能需要删除现有表并重新同步数据库。 只需使用force:true即可,如下代码:
db.sequelize.sync({force: true }).then(() => {console.log("Drop and re-sync db.");});
定义Sequelize模型
在models文件夹中,像这样创建tutorial.model.js文件:
module.exports = (sequelize, Sequelize) => {const Tutorial = sequelize.define("tutorial", {title: {type: Sequelize.STRING},description: {type: Sequelize.STRING},published: {type: Sequelize.BOOLEAN}});return Tutorial;};
该Sequelize模型表示MySQL数据库中的tutorials
表。 这些列将自动生成:id, title, description, published, createdAt, updatedAt.
初始化Sequelize之后,我们无需编写CRUD函数,Sequelize支持所有这些功能:
创建一个新的教程:create(object)通过id查找教程:findByPk(id)获取所有教程:findAll()通过id:update(data, where: { id: id })删除教程:destroy(where: { id: id })删除所有教程:destroy(where:{})按标题查找所有教程:findAll({ where: { title: … } })
这些功能将在我们的控制器中使用。
我们可以通过为每个教程添加评论来改进示例。 这是一对多关系,我为此编写了一个教程:
Sequelize Associations: One-to-Many example – Node.js, MySQL
或者,您可以为每个教程添加标签,并将教程添加到标签(多对多关系):
Sequelize Many-to-Many Association example with Node.js & MySQL
创建控制器
在app/controllers
文件夹中,让我们使用以下CRUD函数创建tutorial.controller.js:
createfindAllfindOneupdatedeletedeleteAllfindAllPublised
const db = require("../models");const Tutorial = db.tutorials;const Op = db.Sequelize.Op;// Create and Save a new Tutorialexports.create = (req, res) => {};// Retrieve all Tutorials from the database.exports.findAll = (req, res) => {};// Find a single Tutorial with an idexports.findOne = (req, res) => {};// Update a Tutorial by the id in the requestexports.update = (req, res) => {};// Delete a Tutorial with the specified id in the requestexports.delete = (req, res) => {};// Delete all Tutorials from the database.exports.deleteAll = (req, res) => {};// Find all published Tutorialsexports.findAllPublished = (req, res) => {};
让我们实现这些功能。
创建一个新对象
创建并保存一个新教程:
exports.create = (req, res) => {// Validate requestif (!req.body.title) {res.status(400).send({message: "Content can not be empty!"});return;}// Create a Tutorialconst tutorial = {title: req.body.title,description: req.body.description,published: req.body.published ? req.body.published : false};// Save Tutorial in the databaseTutorial.create(tutorial).then(data => {res.send(data);}).catch(err => {res.status(500).send({message:err.message || "Some error occurred while creating the Tutorial."});});};
检索对象(有条件)
从数据库中检索所有教程/按标题查找:
exports.findAll = (req, res) => {const title = req.query.title;var condition = title ? {title: {[Op.like]: `%${title}%` } } : null;Tutorial.findAll({where: condition }).then(data => {res.send(data);}).catch(err => {res.status(500).send({message:err.message || "Some error occurred while retrieving tutorials."});});};
我们使用req.query.title
从Request中获取查询字符串,并将其作为findAll()
方法的条件。
检索单个对象
查找具有ID的单个教程:
exports.findOne = (req, res) => {const id = req.params.id;Tutorial.findByPk(id).then(data => {res.send(data);}).catch(err => {res.status(500).send({message: "Error retrieving Tutorial with id=" + id});});};
更新对象
更新由请求中的id
标识的教程:
exports.update = (req, res) => {const id = req.params.id;Tutorial.update(req.body, {where: {id: id }}).then(num => {if (num == 1) {res.send({message: "Tutorial was updated successfully."});} else {res.send({message: `Cannot update Tutorial with id=${id}. Maybe Tutorial was not found or req.body is empty!`});}}).catch(err => {res.status(500).send({message: "Error updating Tutorial with id=" + id});});};
删除对象
删除具有指定id
的教程:
exports.delete = (req, res) => {const id = req.params.id;Tutorial.destroy({where: {id: id }}).then(num => {if (num == 1) {res.send({message: "Tutorial was deleted successfully!"});} else {res.send({message: `Cannot delete Tutorial with id=${id}. Maybe Tutorial was not found!`});}}).catch(err => {res.status(500).send({message: "Could not delete Tutorial with id=" + id});});};
删除所有对象
从数据库中删除所有教程:
exports.deleteAll = (req, res) => {Tutorial.destroy({where: {},truncate: false}).then(nums => {res.send({message: `${nums} Tutorials were deleted successfully!` });}).catch(err => {res.status(500).send({message:err.message || "Some error occurred while removing all tutorials."});});};
按条件查找所有对象
查找所有published = true
的教程:
exports.findAllPublished = (req, res) => {Tutorial.findAll({where: {published: true } }).then(data => {res.send(data);}).catch(err => {res.status(500).send({message:err.message || "Some error occurred while retrieving tutorials."});});};
可以对该控制器进行一些修改以返回分页响应:
{"totalItems": 8,"tutorials": [...],"totalPages": 3,"currentPage": 1}
您可以在以下位置找到更多详细信息:
Server side Pagination in Node.js with Sequelize and MySQL
定义路由
当客户端使用HTTP请求(GET,POST,PUT,DELETE)发送对端点的请求时,我们需要通过设置路由来确定服务器的响应方式。
这些是我们的路由:
/api/tutorials
:GET,POST,DELETE/api/tutorials/:id
:GET,PUT,DELETE/api/tutorials/published
:GET
在app/routes
文件夹中创建一个turorial.routes.js
,其内容如下:
module.exports = app => {const tutorials = require("../controllers/tutorial.controller.js");var router = require("express").Router();// Create a new Tutorialrouter.post("/", tutorials.create);// Retrieve all Tutorialsrouter.get("/", tutorials.findAll);// Retrieve all published Tutorialsrouter.get("/published", tutorials.findAllPublished);// Retrieve a single Tutorial with idrouter.get("/:id", tutorials.findOne);// Update a Tutorial with idrouter.put("/:id", tutorials.update);// Delete a Tutorial with idrouter.delete("/:id", tutorials.delete);// Delete all Tutorialsrouter.delete("/", tutorials.deleteAll);app.use('/api/tutorials', router);};
您可以看到我们使用了/controllers/tutorial.controller.js
中的控制器。
我们还需要在server.js
中包含路由(在app.listen()
之前):
...require("./app/routes/turorial.routes")(app);// set port, listen for requestsconst PORT = ...;app.listen(...);
测试API
使用以下命令运行我们的Node.js应用程序:node server.js
。
控制台显示:
Server is running on port 8080.Executing (default): DROP TABLE IF EXISTS `tutorials`;Executing (default): CREATE TABLE IF NOT EXISTS `tutorials` (`id` INTEGER NOT NULL auto_increment , `title` VARCHAR(255), `description` VARCHAR(255), `published` TINYINT(1), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;Executing (default): SHOW INDEX FROM `tutorials`Drop and re-sync db.
使用Postman,我们将测试以上所有的Apis。
1、使用POST/tutorials
Api创建一个新教程
创建一些新教程后,您可以检查MySQL表:
mysql> select * from tutorials;+----+-------------------+-------------------+-----------+---------------------+---------------------+| id | title | description | published | createdAt | updatedAt |+----+-------------------+-------------------+-----------+---------------------+---------------------+| 1 | JS: Node Tut #1 | Tut#1 Description | 0 | -12-13 01:13:57 | -12-13 01:13:57 || 2 | JS: Node Tut #2 | Tut#2 Description | 0 | -12-13 01:16:08 | -12-13 01:16:08 || 3 | JS: Vue Tut #3 | Tut#3 Description | 0 | -12-13 01:16:24 | -12-13 01:16:24 || 4 | Vue Tut #4 | Tut#4 Description | 0 | -12-13 01:16:48 | -12-13 01:16:48 || 5 | Node & Vue Tut #5 | Tut#5 Description | 0 | -12-13 01:16:58 | -12-13 01:16:58 |+----+-------------------+-------------------+-----------+---------------------+---------------------+
2、使用GET /tutorials
Api检索所有的教程
3、使用GET /tutorials/:id
Api根据id查询单个教程
4、使用`PUT /tutorials/:id’ Api更新教程
在更新某些行后,请查看tutorials
表:
mysql> select * from tutorials;+----+-------------------+-------------------+-----------+---------------------+---------------------+| id | title | description | published | createdAt | updatedAt |+----+-------------------+-------------------+-----------+---------------------+---------------------+| 1 | JS: Node Tut #1 | Tut#1 Description | 0 | -12-13 01:13:57 | -12-13 01:13:57 || 2 | JS: Node Tut #2 | Tut#2 Description | 0 | -12-13 01:16:08 | -12-13 01:16:08 || 3 | JS: Vue Tut #3 | Tut#3 Description | 1 | -12-13 01:16:24 | -12-13 01:22:51 || 4 | Vue Tut #4 | Tut#4 Description | 1 | -12-13 01:16:48 | -12-13 01:25:28 || 5 | Node & Vue Tut #5 | Tut#5 Description | 1 | -12-13 01:16:58 | -12-13 01:25:30 |+----+-------------------+-------------------+-----------+---------------------+---------------------+
5、使用GET /tutorials?title=node
查找标题包含’node’的所有教程
6、使用GET /tutorials/published
Api查询所有已发布的教程
7、使用’DELETE /tutorials/:idApi删除一个教程 ![Delete a Tutorial](https://img-/2008192646838.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NjZjE5ODgxMDMw,size_16,color_FFFFFF,t_70#pic_center) id = 2的教程已从
tutorials`表中删除:
mysql> select * from tutorials;+----+-------------------+-------------------+-----------+---------------------+---------------------+| id | title | description | published | createdAt | updatedAt |+----+-------------------+-------------------+-----------+---------------------+---------------------+| 1 | JS: Node Tut #1 | Tut#1 Description | 0 | -12-13 01:13:57 | -12-13 01:13:57 || 3 | JS: Vue Tut #3 | Tut#3 Description | 1 | -12-13 01:16:24 | -12-13 01:22:51 || 4 | Vue Tut #4 | Tut#4 Description | 1 | -12-13 01:16:48 | -12-13 01:25:28 || 5 | Node & Vue Tut #5 | Tut#5 Description | 1 | -12-13 01:16:58 | -12-13 01:25:30 |+----+-------------------+-------------------+-----------+---------------------+---------------------+
8、使用’DELETE /tutorialsApi删除所有的教程 ![Delete all Tutorials](https://img-/2008192823367.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NjZjE5ODgxMDMw,size_16,color_FFFFFF,t_70#pic_center) 现在在
tutorials`表中没有一行数据了:
mysql> SELECT * FROM tutorials;Empty set (0.00 sec)
结论
今天,我们已经学习了如何使用Express Web服务器创建Node.js Rest Apis。 我们还知道添加MySQL数据库和Sequelize配置,创建Sequelize模型,编写控制器以及定义用于处理所有CRUD操作的路由的方法。
您可以在下一个教程中找到更多有趣的东西:
Server side Pagination in Node.js with Sequelize and MySQL
返回分页数据作为响应:
{"totalItems": 8,"tutorials": [...],"totalPages": 3,"currentPage": 1}
Deploying/Hosting Node.js app on Heroku with MySQL database
或者您可以将图像保存到MySQL数据库:Upload/store images in MySQL using Node.js, Express & Multer
学习愉快! 再见。
进一步阅读
Express.js Routing/package/express/package/body-parser/package/mysql2Tutorials and Guides for Sequelize v5将教程数据从文件上传到MySQL数据库表:
- Node.js: Upload Excel file data into MySQL Database
- Node.js: Upload CSV file data into MySQL Database
源代码
您可以在Github上找到此示例的完整源代码。
如果要为每个教程添加评论。 它是一对多关联,有一个有关该关系的教程:Sequelize Associations: One-to-Many example – Node.js, MySQL
或者,您可以为每个教程添加标签,并将教程添加到标签(多对多关系):
Sequelize Many-to-Many Association example with Node.js & MySQL