NodeJS Express+Sequelize实现单个模块的增删改查功能

Express+Sequelize

Express 是一个保持最小规模的灵活的 Node.js Web应用程序开发框架，为 Web 和移动应用程序提供一组强大的功能。

Sequelize是一个基于 promise的 Node.js ORM。

安装：

npm install express@5.1.0
npm install cors@2.8.5
npm install body-parser@2.2.0
npm install sequelize@6.37.7 --save
npm install  mysql2@3.14.2 --save

app.js

const express = require('express');
const app = new express();
const cors = require('cors');
const bodyParser = require('body-parser');

const userRouter = require('./src/router/user.router');
const roleRouter = require('./src/router/role.router');

// 跨域处理
app.use(new cors());

// 使用 express.urlencoded() 中间件
app.use(express.urlencoded({ extended: true }));
// 使用body-parser中间件解析JSON
app.use(bodyParser.json());


app.use('/api/user', userRouter);
app.use('/api/role', roleRouter);

const PORT = 3000;

app.listen(PORT, () => {
    console.log(`Server is running on port ${PORT}.`);
});

src\router\user.router.js

const express = require('express');
const router = new express.Router();
const userService = require('../service/user.service');

// 增加用户
router.post('/add', async function (req, res) {
    const jsonData = req.body;
    const commonMsg = {
        code: 300
    };
    try {
        const user = await userService.addUser(jsonData);
        commonMsg.code = 200;
        commonMsg.data = user;
    } catch (error) {
        console.log(error.message);
        commonMsg.errMsg = error.message;
    } finally {
        res.json(commonMsg);
    }
});

// 删除用户
router.delete('/:id', async (req, res) => {
    // 获取URL中的id参数
    const userId = req.params.id;
    const commonMsg = {
        code: 300
    };
    try {
        const result = await userService.deleteUser(userId);
        commonMsg.code = 200;
        commonMsg.data = result;
    } catch (error) {
        console.log(error.message);
        commonMsg.errMsg = error.message;
    } finally {
        res.json(commonMsg);
    }
});

// 修改用户
router.post('/modify', async (req, res) => {
    const jsonData = req.body;
    const commonMsg = {
        code: 300
    };
    console.log(jsonData);
    try {
        await userService.modifyUser(jsonData);
        commonMsg.code = 200;
    } catch (error) {
        console.log("router log : ", error.message);
        commonMsg.errMsg = error.message;
    } finally {
        res.json(commonMsg);
    }
});

// 查找用户
router.get('/:id', async (req, res) => {
    const userId = req.params.id;
    const commonMsg = {
        code: 300
    };
    try {
        const user = await userService.findUser(userId);
        commonMsg.code = 200;
        commonMsg.data = user;
    } catch (error) {
        console.log(error.message);
        commonMsg.errMsg = error.message;
    } finally {
        res.json(commonMsg);
    }
});

// 列表查询
router.post('/query', async (req, res) => {
    const jsonData = req.body;
    const commonMsg = {
        code: 300
    };
    try {
        const user = await userService.queryUser(jsonData);
        commonMsg.code = 200;
        commonMsg.data = user;
    } catch (error) {
        console.log(error);
        commonMsg.errMsg = error.message;
    } finally {
        res.json(commonMsg);
    }
});

router.post('/page', async (req, res) => {
    const jsonData = req.body;
    const commonMsg = {
        code: 300
    };
    try {
        const user = await userService.pageUser(jsonData);
        commonMsg.code = 200;
        commonMsg.data = user;
    } catch (error) {
        console.log(error);
        commonMsg.errMsg = error.message;
    } finally {
        res.json(commonMsg);
    }
});

module.exports = router;

src\service\user.service.js

const sequelize = require('../common/sequelize');
const User = require('../model/user');

const userService = {

    // 增加操作 
    async addUser(data) {
        try {
            const newUser = new User(data);
            await newUser.save();
            return newUser;
        } catch (error) {
            console.log(error.message);
            throw new Error("添加用户错误: " + error.message);
        }
    },

    // 删除操作
    async deleteUser(id) {
        let user = null;
        try {
            user = await User.findOne({ where: { id: id } });
            if (user) {
                await User.destroy({
                    where: { id: id }
                });
            }
        } catch (error) {
            throw new Error("删除用户错误: " + error.message);
        } finally {
            return user;
        }
    },

    // 修改操作
    async modifyUser(data) {
        const transaction = await sequelize.transaction()
        try {
            const user = await User.findOne({ where: { id: data.id } });
            if (user) {
                await User.update(data, { where: { id: data.id } }, { transaction: transaction });
                await transaction.commit();
            }
        } catch (error) {
            console.log("service log : ", error.message);
            transaction.rollback();
            throw new Error("修改用户错误: " + error.message);
        }
    },

    // 单个查询
    async findUser(id) {
        let user = null;
        try {
            user = await User.findOne({ where: { id: id } });
        } catch (error) {
            console.log("service log : ", error.message);
            throw new Error("查询单个用户错误：" + error.message);
        } finally {
            return user;
        }
    },

    // 列表查询
    async queryUser(data) {
        let useList = null;
        try {
            useList = await User.findAll({ where: data });
        } catch (error) {
            console.log("service log : ", error.message);
            throw new Error("列表查询用户错误: " + error.message);
        } finally {
            return useList;
        }
    },

    // 分页查询
    async pageUser(data) {
        let resultData = {};
        try {
            const currentPage = data.currentPage;
            // 每页显示的记录数
            const pageSize = data.pageSize;
            // 计算offset
            const offset = (currentPage - 1) * pageSize;
            if (data.currentPage) {
                delete data.currentPage
            }
            if (data.pageSize) {
                delete data.pageSize
            }
            const options = {
                where: data,
                // 确保结果按ID升序排列，以便正确使用游标
                order: [['id', 'ASC']],
                offset: offset,
                limit: pageSize,
            };
            const result = await User.findAndCountAll(options);
            resultData.currentPage = currentPage;
            resultData.pageSize = pageSize;
            resultData.data = result.rows;
            resultData.total = result.count;
        } catch (error) {
            console.log("service log : ", error.message);
            throw new Error("分页查询用户错误: " + error.message);
        } finally {
            return resultData;
        }
    },

};

module.exports = userService;

src\model\user.js

const { Sequelize } = require('sequelize');
const sequelize = require('../common/sequelize');

// define  User  是 id，表示唯一
const User = sequelize.define('User', {
    id: {
        type: Sequelize.INTEGER, // 类型
        primaryKey: true, // 主键
        autoIncrement: true, // 是否允许自增
        comment: '主键'
    },
    name: {
        type: Sequelize.STRING(50),
        allowNull: false, // 是否允许为空
        unique: true, // 唯一
        comment: '用户名称'
    },
    age: {
        type: Sequelize.INTEGER,
        allowNull: false, // 是否允许为空
        comment: '年龄'
    },
    email: {
        type: Sequelize.STRING,
        comment: '用户邮箱'
    },
    status: {
        type: Sequelize.INTEGER,
        defaultValue: 1,
        comment: '用户状态'
    }
}, {
    sequelize,  // 传递连接实例
    tableName: 't_user', // 对应的数据库表
    comment: '用户表',      
    timestamps: false
});
module.exports = User;

src\common\sequelize.js

const { Sequelize } = require('sequelize');
// sequelize 实例
const sequelize = new Sequelize('test', 'root', '123456', {
  host: 'localhost',
  dialect: 'mysql',
  // 连接池
  pool: {
    max: 5, // 连接池中最大连接数量
    min: 0, // 连接池中最小连接数量
    acquire: 30000,
    idle: 10000  // 如果一个线程 10 秒钟内没有被使用过的话，那么就释放线程
  },
  logging: true // SQL日志
});

// 测试数据库连接
(async () => {
  try {
    await sequelize.authenticate();
    console.log('数据库连接成功');
  } catch (error) {
    console.error('数据库连接失败', error);
  }
})();

module.exports = sequelize;