第三章：SQLx 数据模型与迁移系统

3.1 SQLx 特性

SQLx 是一个异步、纯 Rust 的 SQL 驱动，支持 PostgreSQL、MySQL、SQLite。与传统的 ORM 不同，SQLx 让你手写 SQL，同时在编译期检查语法正确性。

核心特性

• 编译期 SQL 检查：通过 sqlx prepare 在编译时验证 SQL 语法
• 异步优先：所有操作都是 async
• 运行时安全：无 ORM 的运行时开销
• 迁移系统：基于 SQL 文件的版本化迁移

3.2 数据实体定义

使用 FromRow derive 宏将查询结果映射到结构体：

// src/models/entity.rs
use chrono::NaiveDateTime;
use serde::{Deserialize, Serialize};
use sqlx::FromRow;

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct User {
    pub id: i64,
    pub username: Option<String>,
    pub nickname: Option<String>,
    #[sqlx(default)]
    pub password: Option<String>,
    pub email: Option<String>,
    pub role: Option<i16>,
    pub ip: Option<String>,
    pub location: Option<String>,
    pub create_time: Option<NaiveDateTime>,
}

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct Article {
    pub id: i64,
    pub title: Option<String>,
    pub content: Option<String>,
    pub category: Option<String>,
    pub tags: Option<String>,      // JSON 数组字符串
    pub status: Option<String>,    // draft / published
    pub digg_count: Option<i32>,
    pub comment_count: Option<i32>,
    pub view_count: Option<i32>,
    pub user_id: Option<i64>,
    pub create_time: Option<NaiveDateTime>,
}

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct Comment {
    pub id: i64,
    pub content: Option<String>,
    pub article_id: Option<i64>,
    pub user_id: Option<i64>,
    pub parent_id: Option<i64>,
    pub root_parent_id: Option<i64>,
    pub digg_count: Option<i32>,
    pub create_time: Option<NaiveDateTime>,
}

字段重命名

当 Rust 字段名与数据库列名不一致时：

pub struct Article {
    pub id: i64,
    pub title: Option<String>,
    #[sqlx(rename = "desc")]
    pub description: Option<String>,
}

3.3 SQL 迁移文件

SQLx 的迁移系统基于目录中的 SQL 文件：

-- migrations/20240101000001_create_all_tables.sql

CREATE TABLE IF NOT EXISTS "user_models" (
    "id" BIGSERIAL PRIMARY KEY,
    "username" VARCHAR(32),
    "nickname" VARCHAR(32),
    "password" VARCHAR(256),
    "email" VARCHAR(128),
    "role" SMALLINT DEFAULT 1,
    "ip" VARCHAR(45),
    "location" VARCHAR(128),
    "create_time" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS "article_models" (
    "id" BIGSERIAL PRIMARY KEY,
    "title" VARCHAR(256),
    "content" TEXT,
    "category" VARCHAR(64),
    "tags" VARCHAR(256),
    "status" VARCHAR(16) DEFAULT 'draft',
    "digg_count" INT DEFAULT 0,
    "comment_count" INT DEFAULT 0,
    "view_count" INT DEFAULT 0,
    "user_id" BIGINT REFERENCES user_models(id),
    "create_time" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS "comment_models" (
    "id" BIGSERIAL PRIMARY KEY,
    "content" TEXT,
    "article_id" BIGINT REFERENCES article_models(id),
    "user_id" BIGINT REFERENCES user_models(id),
    "parent_id" BIGINT,
    "root_parent_id" BIGINT,
    "digg_count" INT DEFAULT 0,
    "create_time" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3.4 编译期 SQL 检查

# 安装 sqlx-cli
cargo install sqlx-cli

# 创建数据库
sqlx database create

# 运行迁移
sqlx migrate run

# 准备编译期检查（需要运行中的数据库）
cargo sqlx prepare

# 编译时验证 SQL
cargo build

3.5 查询示例

// 查询单条
let user = sqlx::query_as::<_, User>(
    "SELECT * FROM user_models WHERE username = $1"
)
.bind(&username)
.fetch_optional(&pool)
.await?;

// 查询多条
let articles = sqlx::query_as::<_, Article>(
    "SELECT * FROM article_models WHERE category = $1 ORDER BY create_time DESC"
)
.bind(&category)
.fetch_all(&pool)
.await?;

// 插入并返回 ID
let result = sqlx::query(
    "INSERT INTO article_models (title, content) VALUES ($1, $2) RETURNING id"
)
.bind(&title)
.bind(&content)
.execute(&pool)
.await?;

let new_id = result.rows_affected();

编译期检查的 SQL

// 使用 query_as! 宏在编译时验证（需要数据库连接）
let users = sqlx::query_as!(
    User,
    "SELECT * FROM user_models WHERE role = $1",
    role
)
.fetch_all(&pool)
.await?;

下一章将实现数据库 CRUD 操作与分页查询。