使用 Sequelize 快速构建 PostgreSQL 数据的 CRUD 操作

之前写过一个文章《布道API》来介绍API的REST风格及推荐实践，今天开始来构建一个管理系统的API服务，首先需要处理的就是数据存储，本文将结合实际开发总结在 NodeJS 下使用 Sequelize 快速构建 PostgreSQL 数据的 CRUD 操作。

项目源代码：https://github.com/QuintionTang/pretender-service

Sequelize

Sequelize 是一个基于 promise 的 Node.js ORM 工具，它具有强大的事务支持、关联关系、预读和延迟加载、读取复制等功能，支持的数据库包括：PostgreSQL、 MySQL、MariaDB、 SQLite 和 MSSQL。

Sequelize 类是引用 sequlize 模块后获取一个顶级对象，通过它来创建 sequlize 实例，也可以通过该对象来获取模内其它对象的引用，如：Utils工具类、Transaction 事务类等。创建实例后，可以通过实例来创建或定义 Model（模型）、执行查询、同步数据库结构等操作。

官方网站：http://docs.sequelizejs.com/

添加和配置

在安装模块之前，首先安装开发工具Sequelize-CLI

sudo npm install -g sequelize-cli

接下来在项目目录下安装数据存储相关的模块。

npm install  sequelize --save
npm install pg pg-hstore  --save

现在在项目根目录下创建文件.sequelizerc，代码如下：

const path = require('path');

module.exports = {
  "config": path.resolve('./config', 'db.json'),
  "models-path": path.resolve('./models'),
  'seeders-path': path.resolve('./seeders'),
  'migrations-path': path.resolve('./migrations')
};

该文件将告诉 Sequelize 初始化，以生成config、models 到特定目录。接下来，输入命令初始化 Sequelize。

sequelize init

该命令将创建 config/db.json，models/index.js，migrations 和 seeders 目录和文件。命令执行完毕之后打开并编辑 config/db.json 来配置数据库连接信息。

{
    "development": {
        "username": "dbusername",
        "password": "dbpassword",
        "database": "crayon-admin",
        "host": "127.0.0.1",
        "dialect": "postgres",
        "options": {
            "operatorsAliases": false
        },
        "logging": false
    },
    "test": {
        "username": "dbusername",
        "password": "dbpassword",
        "database": "crayon-admin",
        "host": "127.0.0.1",
        "dialect": "postgres"
    },
    "production": {
        "username": "dbusername",
        "password": "dbpassword",
        "database": "crayon-admin",
        "host": "127.0.0.1",
        "dialect": "postgres"
    }
}

目录说明：

• migrations：所有迁移文件，通过sequelize db:migrate 创建相应数据表
• seeders：种子文件，即初始化需要插入到数据库中的数据，运行sequelize db:seed:all

创建 Models 和 Migrations

使用CLI工具Sequelize-CLI 创建 administrators 表

sequelize model:create --name administrators --attributes id:integer,add_time:integer,last_login:integer,username:string,email:string,login_ip:string

执行后会生成两个文件

• /src/migrations/20210803095520-create-administrators.js ：创建数据表脚本，用于数据库初始化。

"use strict";
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable("administrators", {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER,
            },
            id: {
                type: Sequelize.INTEGER,
            },
            add_time: {
                type: Sequelize.INTEGER,
            },
            last_login: {
                type: Sequelize.INTEGER,
            },
            username: {
                type: Sequelize.STRING,
            },
            password: {
                type: Sequelize.STRING,
            },
            email: {
                type: Sequelize.STRING,
            },
            login_ip: {
                type: Sequelize.STRING,
            },
        });
    },
    down: async (queryInterface, Sequelize) => {
        await queryInterface.dropTable("administrators");
    },
};

• /src/models/administrators.js ：生成的model文件

"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
    class administrators extends Model {}
    administrators.init(
        {
            id: {
                type: DataTypes.INTEGER,
                primaryKey: true,
            },
            add_time: DataTypes.INTEGER,
            last_login: DataTypes.INTEGER,
            username: DataTypes.STRING,
            password: DataTypes.STRING,
            email: DataTypes.STRING,
            login_ip: DataTypes.STRING,
        },
        {
            sequelize,
            indexes: [
                {
                    unique: true,
                    fields: ["id"],
                },
            ],
            freezeTableName: true,
            timestamps: false, // 是否自动添加时间戳createAt，updateAt
            modelName: "administrators",
        }
    );
    return administrators;
};

现在执行命令：

sequelize db:migrate

执行成功后将在连接的数据库中创建数据表：administrators 。

创建 seed

seed 用于初始化插入数据，如管理员，在系统运行前需要创建一个默认账号，这些默认账号信息就写在 seed 文件中。创建 seed 命令如下：

sequelize seed:create --name administrator

执行成功后将会在 seeders 文件夹中创建文件，修改代码如下：

"use strict";

module.exports = {
    up: async (queryInterface, Sequelize) => {
        /**
         * Add seed commands here.
         *
         * Example:
         * await queryInterface.bulkInsert('People', [{
         *   name: 'John Doe',
         *   isBetaMember: false
         * }], {});
         */
        await queryInterface.bulkInsert(
            "administrators",
            [
                {
                    id:1,
                    username: "administrators",
                    password: "devpoint",
                    email: "[email protected]",
                    add_time:1627828617,
                    last_time:1627828617
                },
            ],
            {}
        );
    },

    down: async (queryInterface, Sequelize) => {
        /**
         * Add commands to revert seed here.
         *
         * Example:
         * await queryInterface.bulkDelete('People', null, {});
         */
    },
};

现在将 seed 中的数据插入到数据库中，执行一下命令：

sequelize db:seed:all

创建 Services

创建文件夹 services , 文件夹中代码封装与 model 交互的方法，包括所有CRUD（创建，读取，更新和删除）操作，创建 administrators.js ，实现的逻辑为获取账号信息、更新账号信息，代码如下：

const AdministratorsModel = require("../models").administrators;

class AdministratorsService {
    constructor() {}
    async get(username) {
        try {
            const userinfo = await AdministratorsModel.findOne({
                where: { username },
            });
            return userinfo;
        } catch (error) {
            throw error;
        }
    }
    async add(newData) {
        try {
            return await AdministratorsModel.create(newData);
        } catch (error) {
            throw error;
        }
    }
    async del(id) {
        try {
            const isExist = await AdministratorsModel.findOne({
                where: { id: Number(id) },
            });

            if (isExist) {
                const deleted = await AdministratorsModel.destroy({
                    where: { id: Number(id) },
                });
                return deleted;
            }
            return null;
        } catch (error) {
            throw error;
        }
    }
    async update(id, updateData) {
        try {
            const isExist = await AdministratorsModel.findOne({
                where: { id: Number(id) },
            });

            if (isExist) {
                await AdministratorsModel.update(updateData, {
                    where: { id: Number(id) },
                });

                return updateData;
            }
            return null;
        } catch (error) {
            throw error;
        }
    }
}

module.exports = new AdministratorsService();

创建 Controllers

上面创建的 services 文件用于控制器，在控制器文件夹中创建一个名为 administrators.js 的文件, 代码如下:

const administratorsService = require("../services/administrators");
const util = require("../utils");

class AdministratorsController {
    constructor() {}
    async login(req, res) {
        const { username, passowrd } = req.body;

        try {
            const userinfo = await administratorsService.get(username);
            console.log(userinfo);
            if (!userinfo) {
                util.setError(200, 30004, `用户名不存在： ${username}`);
            } else {
                util.setSuccess(200, "登录成功", userinfo);
            }
            return util.send(res);
        } catch (error) {
            util.setError(404, error);
            return util.send(res);
        }
    }
}

module.exports = new AdministratorsController();

创建 Routers

在文件夹 routers 中创建文件 administrators.js 文件，代码如下：

const Router = require("express");
const administratorController = require("../controllers/administrators");

const administratorsRouter = Router();

administratorsRouter.post("/login", administratorController.login);

module.exports = administratorsRouter;

现在来为服务创建接口，项目根目录下创建文件 app.js ，代码如下：

"use strict";
const administratorsRouter = require("./src/routers/administrators");
require("./src/utils/logger.js")(2);
const pjson = require("./package.json");
const os = require("os");
const express = require("express");
const app = express();
const bodyParser = require("body-parser");
const CONFIG = require("./config");

const cookieParser = require("cookie-parser");

function _version(serviceUrl) {
    const serviceInfo = {
        name: os.hostname(),
        os: os.platform(),
        os_v: os.release(),
        version: "v" + pjson.version,
    };
    console.info("   ");
    console.info("   ", serviceInfo.name);
    console.success("   ", serviceInfo.version);
    console.success("   ", serviceUrl);
    console.info("   ");
    console.info("   ");
}

function _isAuth(req) {
    if (req.cookies) {
        return req.cookies.auth;
    } else {
        return false;
    }
}
function _setAuth(res, userinfo) {
    res.cookie("auth", userinfo);
}
function _formatResponse(code, message, data) {
    return Object.assign(
        {
            code: code,
            message: message,
        },
        data
    );
}
const allowDomains = "*"; //如发布需改成：127.0.0.1
app.all(allowDomains, (req, res, next) => {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("origin", "127.0.0.1:4200");
    res.header(
        "Access-Control-Allow-Headers",
        "Origin, X-Requested-With, Content-Type, Accept,application/x-www-form-urlencoded"
    );
    res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS");
    res.header("Content-Type", "application/json;charset=utf-8");
    const noCheckPaths = ["/api/v1/auth/login"];
    if (req.method == "OPTIONS") {
        res.send(200);
    } else {
        if (noCheckPaths.includes(req.path)) {
            next();
        } else {
            const authInfo = _isAuth(req);
            if (authInfo && authInfo.name) {
                next();
            } else {
                res.send(401);
            }
        }
    }
});

app.use(cookieParser());
app.use(bodyParser.json());
app.use(
    bodyParser.urlencoded({
        extended: true,
    })
);
app.use("/api/v1/auth", administratorsRouter);

// 开始运行
const port = process.env.PORT || CONFIG.port;
_version(`running at http://127.0.0.1:${port}`);
app.listen(port);

现在执行命令 node app.js 启动服务，将看到终端效果如下：

至此，完成一个基本的 API 登录服务，还有待完善，后续在迭代中完善。文章涉及的代码在 GitHub 上。