外观
关系型数据库
基于 MySQL / MariaDB + Knex.js 的数据层，提供 ORM 查询构建、数据库迁移和模型定义。
概述
数据层使用 Knex.js 作为 SQL 查询构建器和迁移工具，所有表结构通过代码定义，支持迁移版本管理。
模型定义
模型文件位于 src/models/，使用 PascalCase 命名：
typescript
import { createTableIfNotExist } from "#utils/DatabaseUtils";
// create email code table
export async function createTableEmailCodeIfNotExist(trx: Transaction): Promise<TReturn<boolean>> {
return await createTableIfNotExist({
trx,
tableName: "email_code",
disableId: true,
disableCreatedAt: true,
createTable: (table) => {
table.string("email", 255).notNullable().primary();
table.string("code", 6).notNullable();
},
afterCreated: async () => {
// do something after created table
},
});
}1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
数据库迁移
迁移文件位于 migrations/ 目录：
text
migrations/
├── 20251105141026_create_balance_tables.mts
├── 20251201083015_create_article_tables.mts
└── ...1
2
3
4
2
3
4
迁移文件示例1 (migrations/20260301000000_add_urgent_and_important_to_todo.mts)
typescript
import type { Knex } from "knex";
export const up = async (knex: Knex) => {
await knex.schema.alterTable("todo", (table) => {
table.integer("urgent", 1).notNullable().defaultTo(0).comment("紧急程度：0-不紧急，1-紧急");
table.integer("important", 1).notNullable().defaultTo(1).comment("重要程度：0-不重要，1-重要");
});
// 迁移现有数据：type = "todo" → urgent = 1 (紧急)，type = "goal" → urgent = 0 (不紧急)
await knex("todo").where("type", "todo").update({ urgent: 1 });
await knex("todo").where("type", "goal").update({ urgent: 0 });
// 所有记录默认 important = 1 (重要)
await knex("todo").update({ important: 1 });
// 删除不再需要的 type 字段
await knex.schema.alterTable("todo", (table) => {
table.dropColumn("type");
});
};
export const down = async (knex: Knex) => {
// 恢复 type 字段
await knex.schema.alterTable("todo", (table) => {
table.enum("type", ["todo", "goal"]).notNullable().defaultTo("todo").comment("类型");
});
// 从 urgent 恢复 type
await knex("todo").where("urgent", 1).update({ type: "todo" });
await knex("todo").where("urgent", 0).update({ type: "goal" });
// 删除新字段
await knex.schema.alterTable("todo", (table) => {
table.dropColumn("urgent");
table.dropColumn("important");
});
};1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
迁移文件示例2 (migrations/20260214132709_add_idx_project_id_client_time_user_id_to_action.mts)
typescript
import type { Knex } from "knex";
export async function up(knex: Knex): Promise<void> {
await knex.schema.alterTable("b_action", (table) => {
table.index(["project_id"], "idx_project_id");
table.index(["client_time"], "idx_client_time");
table.index(["user_id"], "idx_user_id");
});
}
export async function down(knex: Knex): Promise<void> {
await knex.schema.alterTable("b_action", (table) => {
table.dropIndex(["project_id"], "idx_project_id");
table.dropIndex(["client_time"], "idx_client_time");
table.dropIndex(["user_id"], "idx_user_id");
});
}1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
迁移文件示例3 (migrations/20260117000000_add_affiliate_link_to_nav_website.mts)
typescript
import type { Knex } from "knex";
export async function up(knex: Knex): Promise<void> {
// 检查字段是否已存在
const hasAffiliateLinkColumn = await knex.schema.hasColumn("nav_website", "affiliate_link");
if (!hasAffiliateLinkColumn) {
await knex.schema.alterTable("nav_website", (table) => {
table.string("affiliate_link", 500).comment("推广链接，优先于网站URL使用").after("icon_url");
});
}
}
export async function down(knex: Knex): Promise<void> {
// 检查字段是否存在
const hasAffiliateLinkColumn = await knex.schema.hasColumn("nav_website", "affiliate_link");
if (hasAffiliateLinkColumn) {
await knex.schema.alterTable("nav_website", (table) => {
table.dropColumn("affiliate_link");
});
}
}1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
迁移文件示例4 (migrations/20260127000000_add_file_url_and_md5_to_album_tables.mts)
typescript
import type { Knex } from "knex";
export async function up(knex: Knex): Promise<void> {
await knex.transaction(async (trx) => {
// 为 album_file 表添加 file_url 和 file_md5 字段，并删除 file_id 字段
await trx.schema.alterTable("album_file", (table) => {
table.string("file_url", 500).nullable().comment("文件URL");
table.string("file_md5", 32).nullable().comment("文件MD5，用于去重");
table.dropColumn("file_id");
});
// 为 video_collection_file 表添加 file_url 和 file_md5 字段，并删除 file_id 字段
await trx.schema.alterTable("video_collection_file", (table) => {
table.string("file_url", 500).nullable().comment("文件URL");
table.string("file_md5", 32).nullable().comment("文件MD5，用于去重");
table.dropColumn("file_id");
});
});
}
export async function down(knex: Knex): Promise<void> {
await knex.transaction(async (trx) => {
// 从 album_file 表删除 file_url 和 file_md5 字段，并重新添加 file_id 字段
await trx.schema.alterTable("album_file", (table) => {
table.dropColumn("file_url");
table.dropColumn("file_md5");
table
.integer("file_id")
.unsigned()
.notNullable()
.references("id")
.inTable("file")
.onDelete("CASCADE")
.comment("文件ID，关联file表");
});
// 从 video_collection_file 表删除 file_url 和 file_md5 字段，并重新添加 file_id 字段
await trx.schema.alterTable("video_collection_file", (table) => {
table.dropColumn("file_url");
table.dropColumn("file_md5");
table
.integer("file_id")
.unsigned()
.notNullable()
.references("id")
.inTable("file")
.onDelete("CASCADE")
.comment("文件ID，关联file表");
});
});
}1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
命名规范：YYYYMMDDHHMMSS_description.mts
注意，不要手动创建迁移文件，应该使用下面的命令来创建
常用命令：
bash
# 生成迁移文件
npx knex migrate:make add_starred_to_paper_summarizer_conversation --knexfile knexfile.mts --env production -x mts --stub node_modules/knex/lib/migrations/migrate/stub/ts.stub1
2
2
bash
# 运行数据库迁移
cross-env MODE=int NODE_ENV=production npx knex migrate:latest --knexfile knexfile.mts --env production1
2
2
bash
# 回滚迁移
cross-env MODE=int NODE_ENV=production npx knex migrate:rollback --knexfile knexfile.mts --env production1
2
2
bash
# 解锁
cross-env MODE=int NODE_ENV=production npx knex migrate:unlock --knexfile knexfile.mts --env production1
2
2
查询示例
typescript
export const getMessagesByConversationId = async (
payload: ServiceParamsGetMessageList,
): Promise<TReturn<{ list: PaperSummarizerMessage[]; total: number }>> => {
try {
const { userId, conversationId, pageNo = 1, pageSize = 50 } = payload;
// 验证对话是否存在
const conversation = (await knex("paper_summarizer_conversation")
.where({ id: conversationId, user_id: userId })
.first()) as PaperSummarizerConversation | undefined;
if (!conversation) {
return [new Error("对话不存在"), undefined];
}
const limit = pageSize;
const offset = (pageNo - 1) * limit;
let query = knex("paper_summarizer_message")
.where({ conversation_id: conversationId, user_id: userId })
.select("*")
.orderBy("created_at", "asc");
const [list, totalResult] = await Promise.all([
query.clone().limit(limit).offset(offset),
query.clone().clearSelect().count("id as count").first(),
]);
const total = totalResult ? Number(totalResult.count) : 0;
return [
null,
{
list: list as PaperSummarizerMessage[],
total,
},
];
} catch (err) {
logger.error("Error getting paper summarizer messages:", err);
return [getError(err), undefined];
}
};1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
相关文件
src/models/ — 数据模型所在目录
migrations/ — 迁移文件所在目录
src/utils/DatabaseUtils.mts — 数据库工具函数
mts
import KnexFactory from "knex";
import { getError } from "nsuite";
import knexConfig from "#root/knexfile";
import { logger } from "#utils/LogUtils";
import { NODE_ENV } from "#utils/ConstantUtils";
const mysqlConfig = knexConfig["production"];
logger.info("using mysqlConfig.host", mysqlConfig.connection.host);
export const knex: Knex = KnexFactory({
...mysqlConfig,
});
const SHOULD_PRINT_SQL = false;
knex
.on("query", (query) => {
if (SHOULD_PRINT_SQL && NODE_ENV === "development") {
logger.info(`[MYSQL QUERY]: ${JSON.stringify({ sql: query.sql, bindings: query.bindings })}`);
}
})
.on("query-response", (response, query) => {
if (SHOULD_PRINT_SQL && NODE_ENV === "development") {
logger.info(
`[MYSQL RESPONSE]: ${JSON.stringify({ sql: query.sql, bindings: query.bindings, response })}`,
);
}
})
.on("query-error", (error, query) => {
logger.error(
`[MYSQL ERROR]: ${JSON.stringify({ sql: query.sql, bindings: query.bindings, errorMessage: error?.message || "Unknown error" })}`,
);
});
export const createTableIfNotExist = async (
params: ParamsCreateTableIfNotExist,
): Promise<TReturn<boolean>> => {
const {
trx,
tableName,
createTable,
afterCreated,
disableId,
disableCreatedAt,
disableUpdatedAt,
} = params;
try {
const exists = await trx.schema.hasTable(tableName);
if (exists) {
logger.debug(`Table ${tableName} already exists.`);
return [null, false];
}
logger.debug(`Table ${tableName} does not exist. Creating...`);
await trx.schema.createTable(tableName, (table) => {
if (!disableId) {
table.increments("id").primary().comment("Primary key");
}
if (!disableCreatedAt) {
table.timestamp("created_at").defaultTo(trx.fn.now());
}
if (!disableUpdatedAt) {
table.specificType(
"updated_at",
"timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
);
}
createTable(table);
});
logger.debug(`Table ${tableName} created.`);
if (typeof afterCreated === "function") {
await afterCreated(trx);
}
return [null, true];
} catch (err) {
logger.error(`Error creating table ${tableName}:`, err);
return [getError(err), undefined];
}
};1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
knexfile.mts — Knex 配置文件
mts
import type { Knex } from "knex";
import {
NODE_ENV,
MYSQL_HOST,
MYSQL_PORT,
MYSQL_USER,
MYSQL_PASSWORD,
MYSQL_DATABASE,
MYSQL_CHARSET,
} from "#utils/ConstantUtils";
import { logger } from "#utils/LogUtils";
// Reference: https://knexjs.org/guide/#configuration-options
const config: {
[key: string]: Knex.Config & { connection: Knex.MySql2ConnectionConfig };
} = {
production: {
debug: false,
client: "mysql2",
connection: {
host: MYSQL_HOST,
port: MYSQL_PORT,
user: MYSQL_USER,
password: MYSQL_PASSWORD,
database: MYSQL_DATABASE,
charset: MYSQL_CHARSET,
},
acquireConnectionTimeout: NODE_ENV === "development" ? 30_000 : 20_000,
asyncStackTraces: NODE_ENV === "development",
pool: {
// 连接池配置优化：适配 1核2G 服务器
min: 2,
max: 8, // 从 10 降到 8，避免连接数过多
acquireTimeoutMillis: NODE_ENV === "development" ? 30_000 : 15_000, // 获取连接超时
createTimeoutMillis: 30_000, // 创建连接超时
destroyTimeoutMillis: 5_000, // 销毁连接超时
idleTimeoutMillis: 10_000, // 从 30s 降到 10s，更快回收空闲连接
reapIntervalMillis: 1_000, // 检查空闲连接间隔
createRetryIntervalMillis: 200, // 创建连接重试间隔
propagateCreateError: true, // 传播创建错误，便于排查问题
afterCreate: afterCreatePool,
},
compileSqlOnError: NODE_ENV === "development",
log: {
warn(message) {
logger.warn(`[KNEX WARN]`, message);
},
error(message) {
logger.error(`[KNEX ERROR]`, message);
},
deprecate(message) {
logger.warn(`[KNEX DEPRECATE]`, message);
},
debug(message) {
logger.warn(`[KNEX DEBUG]`, message);
},
},
migrations: {
tableName: "knex_migrations",
directory: "./migrations",
// Generated migration extension
extension: "mts",
loadExtensions: [".mts"],
},
},
};
function afterCreatePool(conn: MySqlConnection, done: DoneFuncAfterCreate) {
conn.query('SET time_zone="+08:00";', function (err) {
// if err is not falsy, connection is discarded from pool
// if connection aquire was triggered by a query the error is passed to query promise
if (err) {
logger.error(`[KNEX POOL CREATED ERROR]`, err);
} else {
logger.info(`[KNEX POOL CREATED SUCCESS]`);
}
done(err, conn);
});
}
export default config;1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81