操作数据库

本章内容将介绍如何在单应用项目中使用数据库查询。

你需要掌握 MySQL 基本知识，至少需要一个已经安装好的 MySQL 服务，并可以正常使用。

安装 MySQL 模块

yarn add @zenweb/mysql

配置 MySQL 模块

修改 src/index.ts 配置 MySQL 模块

src/index.ts

import { create } from "zenweb";
import modMySQL from "@zenweb/mysql";

create()

.setup(modMySQL({
  // 配置数据库连接池
  pools: {
    // MySQL 模块支持一个应用中同时使用多个实例
    MASTER: { // 实例名称
      host: '127.0.0.1', // 数据库地址
      port: 3306, // 数据库端口
      user: 'root', // 数据库用户名
      password: '', // 数据库密码
      database: 'test', // 数据库名称
      charset: 'utf8mb4', // 数据库默认字符集
      timezone: '+08:00', // 数据库时区
      connectionLimit: 100, // 单应用数据库连接数上限
    },
  }
}))

.start();

关于实例名称

在上述配置项中 MASTER 为数据库实例名称，他只是作为使用多个实例时区分不同实例而定义，如果只是单纯使用 SQL 查询本身并没有什么特殊含义。 但是如果需要 ORM 的自动读写分离支持则需要指定名称为主库 MASTER 从库为 SLAVE* * 星号代表自编号或者其他名称，但是必须为 SLAVE 开头。

连接 MySQL 创建表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `unreads` int(11) NOT NULL DEFAULT 0 COMMENT '未读消息数',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from_user_id` int(11) NOT NULL,
  `to_user_id` int(11) NOT NULL,
  `content` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

使用 SQL 查询

创建文件 src/controller/database.ts

src/controller/database.ts

import { mapping, Context, QueryHelper } from "zenweb";

export class DatabaseController {
  /**
   * 读区数据
   */
  @mapping({ path: '/db/get' })
  async get(ctx: Context) {
    const result = await ctx.mysql.query('SELECT * FROM `user`');
    return { result };
  }

  /**
   * 插入数据
   */
  @mapping({ path: '/db/create' })
  async create(ctx: Context, qh: QueryHelper) {
    const data = qh.get({
      name: "!trim1",
      birthday: "!date",
    });
    const result = await ctx.mysql.query('INSERT INTO `user` (`name`, `birthday`) VALUES (?, ?)', [
      data.name, data.birthday
    ]);
    return { result };
  }
}

访问 http://127.0.0.1:7001/create?name=Bob&birthday=1987-9-20 插入数据后再访问 http://127.0.0.1:7001/get 查看结果

使用事务

对于简单的增删改查使用 query() 方法即可完成，借助 nodejs 的异步特性，你可以简单的去除 await 来进行后台查询。

然而我们在业务中会经常用到数据库的事务功能，比如给用户发消息的同时需要更新未读消息数。

自动事务

src/controller/database.ts

export class DatabaseController {
  /**
   * 自动事务
   */
  @mapping({ path: '/db/tx1' })
  async autoTransaction(ctx: Context, qh: QueryHelper) {
    const data = qh.get({
      from_user_id: "!int",
      to_user_id: "!int",
      content: "!trim1",
    });
    // 开启数据库事务
    await ctx.mysql.transaction(async tx => {
      // 插入消息
      await tx.query('INSERT INTO `message` (`from_user_id`, `to_user_id`, `content`) VALUES (?, ?, ?)', [
        data.from_user_id, data.to_user_id, data.content
      ]);
      // 更新未读消息数
      await tx.query('UPDATE `user` SET `unreads` = `unreads` + 1 WHERE `id` = ?', [
        data.to_user_id
      ]);
    });
    return 'ok';
  }
}

使用 transaction() 回调函数即可开启一个自动事务，事物开启成功后会回调业务代码并传入参数 tx 作为本次事务的数据库线程。 在回调函数中使用 tx 数据库线程处理需要被事务化的查询，在没有问题的情况下事务会自动提交，如果需要回滚直接在回调函数中抛出异常即可。

手动事务

对于一些特殊情况也可以使用手动事务来控制事务的提交与回滚。如下代码：

src/controller/database.ts

export class DatabaseController {
  /**
   * 手动事务
   */
  @mapping({ path: '/db/tx2' })
  async manualTransaction(ctx: Context, qh: QueryHelper) {
    const data = qh.get({
      from_user_id: "!int",
      to_user_id: "!int",
      content: "!trim1",
    });
    // 使用自动连接池取得一个数据库连接线程
    await ctx.mysql.auto(async conn => {
      // 开启当前线程事务
      await conn.beginTransaction();
      try {
        // 插入消息
        await conn.query('INSERT INTO `message` (`from_user_id`, `to_user_id`, `content`) VALUES (?, ?, ?)', [
          data.from_user_id, data.to_user_id, data.content
        ]);
        // 更新未读消息数
        await conn.query('UPDATE `user` SET `unreads` = `unreads` + 1 WHERE `id` = ?', [
          data.to_user_id
        ]);
        // 提交事务
        await conn.commit();
      } catch {
        // 发生异常 - 回滚事务
        await conn.rollback();
      }
    });
    return 'ok';
  }
}