node.js: mysql sequelize ORM in WebStorm 2023.1

mysql:

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');

　　

 

 

/**
 *  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');
* */

　　

/**
 * 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;

　　

 

/**
 * 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;
};

　　

/**
 *  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."
      });
    });
};

　　

/**
 * 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);
};

　　

/**
 * 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}.`);
});

　　

/**
 @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;
};

　　

/**
 @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."
            });
        });
};

　　

/**
 * 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);
};

　　

 

 

 

运行：

 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