数据库设计

创建/定义表

数据库各个表的Schema定义在 src/models/*.mts 中。我们使用 knex 这个 sql builder 来定义库表结构。以 news 表为例：

import { createTableIfNotExist } from "#scripts/DatabaseUtils";

// 创建新闻表
export async function createTableNewsIfNotExist(): Promise<void> {
  await createTableIfNotExist({
    tableName: "news",
    createTable: (table) => {
      table.string("title", 255).notNullable().comment("新闻标题");
      table.text("content_md").notNullable().comment("Markdown格式的新闻内容");
      table.text("content_html").comment("转换后的HTML格式内容");
      table.text("summary").comment("摘要文本");
      table
        .enum("status", ["draft", "published", "deleted"])
        .defaultTo("draft")
        .comment("状态（草稿、已发布、已下线）");
      table.integer("views").defaultTo(0).comment("浏览量");
      table.string("thumbnail", 255).comment("封面图片链接");
    },
  });
}

上面的代码定义了一个 news 表，显示地定义了以下字段：

• title: 新闻标题，字符串类型，最大长度255，不允许为空。
• content_md: 新闻内容，Markdown格式，文本类型，不允许为空。
• content_html: 转换后的HTML格式内容，文本类型。
• summary: 摘要文本，文本类型。
• status: 新闻状态，枚举类型，包含 "draft"（草稿）、"published"（已发布）、"deleted"（已下线），默认值为 "draft"。
• views: 浏览量，整数类型，默认值为0。
• thumbnail: 封面图片链接，字符串类型，最大长度255。

提示

createTableIfNotExist 方法默认会自动帮你定义 id、created_at, updated_at 字段，不要再显示定义。

如果你不需要这个默认行为，可以通过传入 disableId、disableCreatedAt 或 disableUpdatedAt 字段来覆盖默认行为。

async function createTableIfNotExist(
  params: ParamsCreateTableIfNotExist,
): Promise<void>;

interface ParamsCreateTableIfNotExist {
  tableName: string;
  createTable: (table: import("knex").Knex.TableBuilder) => void;
  disableId?: boolean; // 是否禁用默认的 id 主键列，默认为 false
  disableCreatedAt?: boolean; // 是否禁用默认的 created_at 列，默认为 false
  disableUpdatedAt?: boolean; // 是否禁用默认的 updated_at 列，默认为 false
}

数据表迁移

使用 Knex.js 来给已经存在的表添加字段，你可以通过编写一个迁移（migration）来实现。迁移是一种对数据库模式进行版本控制的方法，它允许你以一种安全且可重复的方式修改数据库结构。下面是一个简单的步骤指南，帮助你为 user 表添加新字段：

1. 创建一个新的迁移文件

首先，你需要创建一个新的迁移文件。可以通过运行以下命令来生成一个新的迁移文件：

npx knex migrate:make add_new_fields_to_user_table --knexfile knexfile.mts --env production -x mts --stub node_modules/knex/lib/migrations/migrate/stub/ts.stub

这将为你创建一个新的迁移文件在你的 migrations 目录下。

2. 编辑迁移文件

打开刚创建的迁移文件，并编辑 up 方法来添加新的字段到 user 表中。例如，如果你想添加一个名为 age 的整数类型字段和一个名为 status 的字符串类型字段，可以这样做：

exports.up = function (knex) {
  return knex.schema.table("user", function (table) {
    table.integer("age"); // 添加 age 字段
    table.string("status"); // 添加 status 字段
  });
};

exports.down = function (knex) {
  return knex.schema.table("user", function (table) {
    table.dropColumn("age"); // 移除 age 字段
    table.dropColumn("status"); // 移除 status 字段
  });
};

up 方法定义了如何应用更改，而 down 方法则定义了如何回滚这些更改。这是非常有用的，以防你需要撤销所做的更改。

3. 运行迁移

完成迁移文件的编辑后，你可以通过运行以下命令来执行迁移并更新数据库：

npx knex migrate:latest --knexfile knexfile.mts --env production

这将会根据你编写的迁移文件对数据库进行相应的修改。

注意事项

• 在对生产数据库进行任何更改之前，请确保你在测试环境中进行了充分的测试。
• 如果你的表中有大量数据，或者你正在添加带有默认值或非空约束的新字段，可能需要特别小心，因为这可能会导致长时间的锁定或数据丢失风险。在这种情况下，考虑先添加字段而不设置默认值，然后再根据需求更新现有记录的值，最后如果需要的话再添加非空约束。

在 Knex 中，回滚（rollback）迁移指的是撤销最近一次或多次执行的迁移操作，恢复数据库到之前的状态。回滚是通过执行 down 方法中定义的操作来实现的，这个方法与 up 方法相对应，定义了如何撤销迁移所带来的更改。

如何执行回滚

1. 回滚最近一次迁移

   如果你想撤销最近一次运行的迁移，可以使用以下命令：

   npx knex migrate:rollback

   这个命令会执行最近一次迁移对应的 down 方法，从而撤销该次迁移的所有更改。

2. 回滚到指定的迁移版本

   如果需要回滚到某个特定的迁移版本，你可以使用 --to 参数指定目标迁移文件的时间戳或名称：

   npx knex migrate:rollback --to <timestamp_or_migration_name>

3. 回滚所有迁移

   要一次性回滚所有迁移，可以重复调用 migrate:rollback 直到所有的迁移都被撤销。不过，Knex 本身没有直接提供一次性回滚所有迁移的命令。你可以编写一个简单的脚本循环执行 migrate:rollback 直到没有更多迁移可回滚为止。

确保正确的 down 方法

为了能够成功回滚，确保你的每个迁移文件都正确实现了 down 方法。例如，如果你的 up 方法中添加了新字段并重命名了旧字段，那么 down 方法应该做相反的操作：删除新字段，并根据需要重新创建旧字段。这里是一个示例：

exports.up = function (knex) {
  return knex.schema
    .table("user", function (table) {
      table.string("newField1");
      table.string("newField2");
    })
    .then(() => {
      return knex.raw(
        "UPDATE user SET newField1 = oldField1, newField2 = oldField2",
      );
    })
    .then(() => {
      return knex.schema.table("user", function (table) {
        table.dropColumn("oldField1");
        table.dropColumn("oldField2");
      });
    });
};

exports.down = function (knex) {
  return knex.schema
    .table("user", function (table) {
      table.string("oldField1");
      table.string("oldField2");
    })
    .then(() => {
      return knex.raw(
        "UPDATE user SET oldField1 = newField1, oldField2 = newField2",
      );
    })
    .then(() => {
      return knex.schema.table("user", function (table) {
        table.dropColumn("newField1");
        table.dropColumn("newField2");
      });
    });
};

注意事项

• 数据一致性：在进行回滚时，特别是涉及到数据移动的操作（如字段重命名），请务必小心以确保数据的一致性和完整性。

• 备份数据：在执行回滚操作前，建议对数据库进行完整备份，以防出现任何意外错误导致的数据丢失。

通过遵循上述步骤和注意事项，你可以有效地管理和回滚数据库迁移，保持数据库结构和数据的安全与稳定。