node.js: mysql sequelize ORM in WebStorm 2023.1

mysql:

?
1
2
3
4
5
6
7
8
9
10
select * from tutorials;
# CREATE TABLE IF NOT EXISTS `tutorials` (`id` INTEGER NOT NULL auto_increment , `title` VARCHARuserinfos(255), `description` VARCHAR(255), `published` TINYINT(1), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
insert into tutorials values(1,'geovindu','geovidu',1,'2025-05-04','2025-05-04');
insert into tutorials values(2,'涂聚文','涂聚文',0,'2025-05-04','2025-05-04');
 
 
 
/* CREATE TABLE IF NOT EXISTS `userInfos` (`id` INTEGER NOT NULL auto_increment , `userName` VARCHAR(255), `userPassword` VARCHAR(255), `userIsOk` TINYINT(1), `userMail` VARCHAR(255), `userMobile` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; */
insert into userinfos values(1,'geovindu','涂聚文','bb80af81b49aee29c43f4dd617203363',1,'geovindu@163.com','13824350518','2025-07-09','2025-09-01');
insert into userinfos values(2,'NianshengTu','涂年生','fddd4f3e2be81824e3e8c3224e3f6291',1,'geovindu@163.com','13824350518','2025-07-09','2025-09-01');

　　

 

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/**
 *  dbConfig.js
 *  node 20 vue.js 3.0
 * ide: WebStorm 2023.1
 * mysql 8.0
 * npm install express sequelize mysql2 cors
 * */
 
module.exports = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "geovindu",
  DB: "geovindu",
  dialect: "mysql",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};
 
/*
* http://localhost:8081/api/tutorials
http://localhost:8081/api/tutorials/1
http://localhost:8081/api/tutorials?title=geovindu
http://localhost:8081/api/tutorials/published
* npm install express sequelize mysql2 cors --save
* select * from tutorials;
# 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;
insert into tutorials values(1,'geovindu','geovidu',1,'2025-05-04','2025-05-04');
insert into tutorials values(2,'涂聚文','涂聚文',0,'2025-05-04','2025-05-04');
* */

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/**
 * models/index.js
 *  node 20 vue.js 3.0
 * ide: WebStorm 2023.1
 * mysql 8.0
 * npm install express sequelize mysql2 cors
 * */
 
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;

　　

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/**
 * models/tutorial.model.js
 * node 20 vue.js 3.0
 * ide: WebStorm 2023.1
 * mysql 8.0
 * npm install express sequelize mysql2 cors
 * */
 
 
module.exports = (sequelize, Sequelize) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING
    },
    published: {
      type: Sequelize.BOOLEAN
    }
  });
 
  return Tutorial;
};

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
/**
 *  controllers/tutorial.controller.js
 *  node 20 vue.js 3.0
 * ide: WebStorm 2023.1
 * mysql 8.0
 * npm install express sequelize mysql2 cors
 * */
 
const db = require("../models");
const Tutorial = db.tutorials;
const Op = db.Sequelize.Op;
 
// Create and Save a new Tutorial
exports.create = (req, res) => {
  // Validate request
  if (!req.body.title) {
    res.status(400).send({
      message: "Content can not be empty!"
    });
    return;
  }
 
  // Create a Tutorial
  const tutorial = {
    title: req.body.title,
    description: req.body.description,
    published: req.body.published ? req.body.published : false
  };
 
  // Save Tutorial in the database
  Tutorial.create(tutorial)
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while creating the Tutorial."
      });
    });
};
 
// Retrieve all Tutorials from the database.
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."
      });
    });
};
 
// Find a single Tutorial with an id
exports.findOne = (req, res) => {
  const id = req.params.id;
 
  Tutorial.findByPk(id)
    .then(data => {
      if (data) {
        res.send(data);
      } else {
        res.status(404).send({
          message: `Cannot find Tutorial with id=${id}.`
        });
      }
    })
    .catch(err => {
      res.status(500).send({
        message: "Error retrieving Tutorial with id=" + id
      });
    });
};
 
// Update a Tutorial by the id in the request
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
      });
    });
};
 
// Delete a Tutorial with the specified id in the request
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
      });
    });
};
 
// Delete all Tutorials from the database.
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."
      });
    });
};
 
// find all published Tutorial
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."
      });
    });
};

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
/**
 * routes/tutorial.routes.js
 * node 20 vue.js 3.0
 * ide: WebStorm 2023.1
 * mysql 8.0
 * npm install express sequelize mysql2 cors
 * */
 
 
module.exports = app => {
  const tutorials = require("../controllers/tutorial.controller.js");
 
  var router = require("express").Router();
 
  // Create a new Tutorial
  router.post("/", tutorials.create);
 
  // Retrieve all Tutorials
  router.get("/", tutorials.findAll);
 
  // Retrieve all published Tutorials
  router.get("/published", tutorials.findAllPublished);
 
  // Retrieve a single Tutorial with id
  router.get("/:id", tutorials.findOne);
 
  // Update a Tutorial with id
  router.put("/:id", tutorials.update);
 
  // Delete a Tutorial with id
  router.delete("/:id", tutorials.delete);
 
  // Delete all Tutorials
  router.delete("/", tutorials.deleteAll);
 
  app.use('/api/tutorials', router);
};

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
/**
 * server.js
 * node 20 vue.js 3.0
 * ide: WebStorm 2023.1
 * mysql 8.0
 * npm install express sequelize mysql2 cors
 * */
 
 
const express = require("express");
const cors = require("cors");
 
const app = express();
 
var corsOptions = {
  origin: "http://localhost:8081"
};
 
app.use(cors(corsOptions));
 
// parse requests of content-type - application/json
app.use(express.json());
 
// parse requests of content-type - application/x-www-form-urlencoded
app.use(express.urlencoded({ extended: true }));
 
const db = require("./app/models");
 
db.sequelize.sync()
  .then(() => {
    console.log("Synced db.");
  })
  .catch((err) => {
    console.log("Failed to sync db: " + err.message);
  });
 
// // drop the table if it already exists
// db.sequelize.sync({ force: true }).then(() => {
//   console.log("Drop and re-sync db.");
// });
 
// simple route
app.get("/", (req, res) => {
  res.json({ message: "Welcome to bezkoder application." });
});
 
require("./app/routes/turorial.routes")(app);
 
// set port, listen for requests
const PORT = process.env.PORT || 8081;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}.`);
});

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
/**
 @description
 @author geovindu
 @project vuedemo
 @package
 node 20 vue.js 3.0
 ide: WebStorm 2023.1
 mysql 8.0
 @file userinfo.model.js
 @date Created in 8:42 2024/08/15
 @edate eddit in
 */
 
/**
 *
 * @param {*} sequelize
 * @param {*} Sequelize
 * @returns
 */
module.exports = (sequelize, Sequelize) => {
    const UserInfo = sequelize.define("userInfo", {
        userName: {
            type: Sequelize.STRING
        },
        userReal:{
            type:Sequelize.STRING
        },
        userPassword: {
            type: Sequelize.STRING
        },
        userIsOk: {
            type: Sequelize.BOOLEAN
        },
        userMail:
            {
                type:Sequelize.STRING
            },
        userMobile:
            {
 
                type:Sequelize.STRING
 
            }
    });
 
    return UserInfo;
};

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
/**
 @description WebStorm
 @author geovindu  geovindu
 @project vuedemo
 @package
 node 20 vue.js 3.0
 ide: WebStorm 2023.1
 mysql 8.0
 @file userinfo.controller.js
 @date Created in 8:52 2024/08/15
 @edate eddit in
 */
const db = require("../models");
 
 
const UserInfo = db.userinfos;
const Op = db.Sequelize.Op;
 
/**
 * 添加记录
 * @param {*} req
 * @param {*} res
 * @returns
 */
exports.usercreate = (req, res) => {
    // Validate request
    if (!req.body.title) {
        res.status(400).send({
            message: "Content can not be empty!"
        });
        return;
    }
 
    // Create a Tutorial
    const userInfo = {
        userName: req.body.userName,
        userReal:req.body.userReal,
        userPassword: req.body.userPassword,
        userIsOk: req.body.userIsOk ? req.body.userIsOk : false,
        userMail:req.body.userMail,
        userMobile:req.body.userMobile
    };
 
    // Save Tutorial in the database
    UserInfo.create(userInfo)
        .then(data => {
            res.send(data);
        })
        .catch(err => {
            res.status(500).send({
                message:
                    err.message || "Some error occurred while creating the userinfos."
            });
        });
};
 
/**
 * 查看所有记录
 * @param {*} req
 * @param {*} res
 */
 
exports.userfindAll = (req, res) => {
    const userName = req.query.userName;
    var condition = userName ? { userName: { [Op.like]: `%${userName}%` } } : null;
 
    UserInfo.findAll({ where: condition })
        .then(data => {
            res.send(data);
        })
        .catch(err => {
            res.status(500).send({
                message:
                    err.message || "Some error occurred while retrieving userinfos."
            });
        });
};
 
/**
 * 查找一条记录
 * @param {*} req
 * @param {*} res
 */
exports.userfindOne = (req, res) => {
    const id = req.params.id;
 
    UserInfo.findByPk(id)
        .then(data => {
            if (data) {
                res.send(data);
            } else {
                res.status(404).send({
                    message: `Cannot find userinfos with id=${id}.`
                });
            }
        })
        .catch(err => {
            res.status(500).send({
                message: "Error retrieving userinfos with id=" + id
            });
        });
};
 
 
/**
 * 更新记录
 * @param {*} req
 * @param {*} res
 */
exports.userupdate = (req, res) => {
    const id = req.params.id;
 
    UserInfo.update(req.body, {
        where: { id: id }
    })
        .then(num => {
            if (num == 1) {
                res.send({
                    message: "usrinfos was updated successfully."
                });
            } else {
                res.send({
                    message: `Cannot update userinfos with id=${id}. Maybe userinfos was not found or req.body is empty!`
                });
            }
        })
        .catch(err => {
            res.status(500).send({
                message: "Error updating userinfos with id=" + id
            });
        });
};
 
/**
 * 删除了一条记录
 * @param {*} req
 * @param {*} res
 */
exports.userdeleteid = (req, res) => {
    const id = req.params.id;
 
    UserInfo.destroy({
        where: { id: id }
    })
        .then(num => {
            if (num == 1) {
                res.send({
                    message: "userinfos was deleted successfully!"
                });
            } else {
                res.send({
                    message: `Cannot delete userinfos with id=${id}. Maybe userinfos was not found!`
                });
            }
        })
        .catch(err => {
            res.status(500).send({
                message: "Could not delete userinfos  with id=" + id
            });
        });
};
 
/**
 * 删除所有记录
 * @param {*} req
 * @param {*} res
 */
exports.userdeleteAll = (req, res) => {
    UserInfo.destroy({
        where: {},
        truncate: false
    })
        .then(nums => {
            res.send({ message: `${nums} userinfos were deleted successfully!` });
        })
        .catch(err => {
            res.status(500).send({
                message:
                    err.message || "Some error occurred while removing all userinfos."
            });
        });
};
 
 
 
 
/**
 * 查找有效的会员记录
 * @param {*} req 变量
 * @param {*} res 变量
 */
exports.findAlluserIsOk = (req, res) => {
 
    UserInfo.findAll({ where: { userIsOk: true } })
        .then(data => {
            res.send(data);
        })
        .catch(err => {
            res.status(500).send({
                message:
                    err.message || "Some error occurred while retrieving userinfos."
            });
        });
};

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
/**
 * routes/tutorial.routes.js
 *  node 20 vue.js 3.0
 * ide: WebStorm 2023.1
 * mysql 8.0
 * npm install express sequelize mysql2 cors
 * */
 
 
const tutorials = require("../controllers/tutorial.controller");
const userinfos = require("../controllers/userinfo.controller");
module.exports = app => {
  const tutorials = require("../controllers/tutorial.controller.js");
  const userinfos=require("../controllers/userinfo.controller")
  var router = require("express").Router();
 
  // Create a new Tutorial
  router.post("tutorials/", tutorials.create);
  router.post("userinfos/", userinfos.usercreate);
 
 
  // Retrieve all Tutorials
  router.get("tutorials/", tutorials.findAll);
  router.get("userinfos/", userinfos.userfindAll);
 
 
  // Retrieve all published Tutorials
  router.get("tutorials/published", tutorials.findAllPublished);
  router.get("userinfos/", userinfos.findAlluserIsOk);
 
  // Retrieve a single Tutorial with id
  router.get("tutorials/:id", tutorials.findOne);
  router.get("userinfos/:id", userinfos.userfindOne);
 
 
  // Update a Tutorial with id
  router.put("tutorials/:id", tutorials.update);
  router.put("userinfos/:id", userinfos.userupdate);
 
 
 
  // Delete a Tutorial with id
  router.delete("tutorials/:id", tutorials.delete);
  router.delete("userinfos/:id", userinfos.userdeleteid);
 
  // Delete all Tutorials
  router.delete("tutorials/", tutorials.deleteAll);
  router.delete("userinfos/", tutorials.deleteAll);
 
 
  app.use('/api', router);
};

　　

 

 

 

运行：

?
1
node server.js

　　

 

 

 

 

from:

https://www.prisma.io/dataguide/database-tools/top-nodejs-orms-query-builders-and-database-libraries

 https://www.bezkoder.com/node-js-express-sequelize-mysql/

https://github.com/bezkoder/nodejs-express-sequelize-mysql

posted @   ®Geovin Du Dream Park™  阅读(15)  评论(0)  编辑  收藏  举报
相关博文：
·  node.js: mysql sequelize es6 ORM (updated)
·  node.js: mysql sequelize es6 ORM in vscode
·  Node.js 的学习（四）分别连接MongoDB与MySQL数据库，实现增删查改功能
·  Node.js学习笔记（四）——NodeJS访问MongoDB与MySQL数据库
·  Node.js 的ORM(Sequelize) 的使用
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2013-08-07 Csharp:字符串操作
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示