Diesel集成 #

Diesel是Rust中最流行的ORM，提供编译时类型安全的SQL查询。本节将介绍如何在Rocket中集成Diesel。

安装配置 #

添加依赖 #

toml

[dependencies]
rocket = { version = "0.5", features = ["json"] }
rocket_sync_db_pools = { version = "0.1", features = ["diesel_postgres_pool"] }
diesel = { version = "2.1", features = ["postgres", "chrono", "uuid"] }
serde = { version = "1.0", features = ["derive"] }

[dependencies.rocket_sync_db_pools]
version = "0.1"
features = ["diesel_postgres_pool"]

安装Diesel CLI #

bash

cargo install diesel_cli --no-default-features --features postgres

配置数据库 #

toml

[default.databases.postgres]
url = "postgres://user:password@localhost/mydb"

初始化项目 #

创建数据库 #

bash

diesel setup

创建迁移 #

bash

diesel migration generate create_users

迁移文件 #

migrations/xxx_create_users/up.sql:

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

migrations/xxx_create_users/down.sql:

sql

DROP TABLE users;

运行迁移 #

bash

diesel migration run

模型定义 #

schema.rs #

rust

diesel::table! {
    users (id) {
        id -> Int4,
        username -> Varchar,
        email -> Varchar,
        password_hash -> Varchar,
        created_at -> Timestamp,
        updated_at -> Timestamp,
    }
}

模型结构 #

rust

use diesel::prelude::*;
use serde::{Serialize, Deserialize};

#[derive(Debug, Queryable, Serialize)]
pub struct User {
    pub id: i32,
    pub username: String,
    pub email: String,
    pub password_hash: String,
    pub created_at: chrono::NaiveDateTime,
    pub updated_at: chrono::NaiveDateTime,
}

#[derive(Debug, Insertable)]
#[diesel(table_name = crate::schema::users)]
pub struct NewUser {
    pub username: String,
    pub email: String,
    pub password_hash: String,
}

#[derive(Debug, AsChangeset, Deserialize)]
#[diesel(table_name = crate::schema::users)]
pub struct UpdateUser {
    pub username: Option<String>,
    pub email: Option<String>,
}

数据库连接 #

配置连接池 #

rust

#[macro_use] extern crate rocket;

use rocket_sync_db_pools::{database, diesel};

#[database("postgres")]
struct Db(diesel::PgConnection);

#[launch]
fn rocket() -> _ {
    rocket::build()
        .attach(Db::fairing())
        .mount("/api", routes![list_users, get_user, create_user])
}

CRUD操作 #

查询所有 #

rust

use crate::schema::users::dsl::*;

#[get("/users")]
async fn list_users(db: Db) -> Json<Vec<User>> {
    db.run(|conn| {
        users
            .load::<User>(conn)
            .expect("Error loading users")
    }).await.into()
}

查询单个 #

rust

#[get("/users/<user_id>")]
async fn get_user(db: Db, user_id: i32) -> Option<Json<User>> {
    db.run(move |conn| {
        users
            .find(user_id)
            .first::<User>(conn)
            .ok()
    }).await.map(Json)
}

创建 #

rust

use rocket::serde::json::Json;

#[post("/users", format = "json", data = "<new_user>")]
async fn create_user(db: Db, new_user: Json<NewUser>) -> Json<User> {
    db.run(|conn| {
        diesel::insert_into(users)
            .values(&new_user.into_inner())
            .returning((id, username, email, password_hash, created_at, updated_at))
            .get_result(conn)
            .expect("Error creating user")
    }).await.into()
}

更新 #

rust

#[put("/users/<user_id>", format = "json", data = "<update>")]
async fn update_user(
    db: Db,
    user_id: i32,
    update: Json<UpdateUser>,
) -> Option<Json<User>> {
    db.run(move |conn| {
        diesel::update(users.find(user_id))
            .set(&update.into_inner())
            .returning((id, username, email, password_hash, created_at, updated_at))
            .get_result(conn)
            .ok()
    }).await.map(Json)
}

删除 #

rust

use rocket::http::Status;

#[delete("/users/<user_id>")]
async fn delete_user(db: Db, user_id: i32) -> Result<Status, Status> {
    let deleted = db.run(move |conn| {
        diesel::delete(users.find(user_id))
            .execute(conn)
            .unwrap_or(0)
    }).await;
    
    if deleted > 0 {
        Ok(Status::NoContent)
    } else {
        Err(Status::NotFound)
    }
}

复杂查询 #

条件查询 #

rust

#[get("/users/search?<q>")]
async fn search_users(db: Db, q: &str) -> Json<Vec<User>> {
    let pattern = format!("%{}%", q);
    
    db.run(move |conn| {
        users
            .filter(username.like(pattern.clone())
                .or(email.like(pattern)))
            .load::<User>(conn)
            .expect("Error searching users")
    }).await.into()
}

分页查询 #

rust

#[get("/users?<page>&<per_page>")]
async fn list_users_paginated(
    db: Db,
    page: Option<i64>,
    per_page: Option<i64>,
) -> Json<Vec<User>> {
    let page = page.unwrap_or(1);
    let per_page = per_page.unwrap_or(10);
    let offset = (page - 1) * per_page;
    
    db.run(move |conn| {
        users
            .order(created_at.desc())
            .limit(per_page)
            .offset(offset)
            .load::<User>(conn)
            .expect("Error loading users")
    }).await.into()
}

关联查询 #

rust

diesel::table! {
    posts (id) {
        id -> Int4,
        title -> Varchar,
        content -> Text,
        user_id -> Int4,
        created_at -> Timestamp,
    }
}

diesel::joinable!(posts -> users (user_id));

#[derive(Debug, Queryable, Serialize)]
pub struct PostWithUser {
    pub id: i32,
    pub title: String,
    pub content: String,
    pub user_id: i32,
    pub username: String,
}

#[get("/posts")]
async fn list_posts_with_users(db: Db) -> Json<Vec<PostWithUser>> {
    use crate::schema::posts::dsl as p;
    use crate::schema::users::dsl as u;
    
    db.run(|conn| {
        p::posts
            .inner_join(u::users)
            .select((p::id, p::title, p::content, p::user_id, u::username))
            .load::<PostWithUser>(conn)
            .expect("Error loading posts")
    }).await.into()
}

完整示例 #

rust

#[macro_use] extern crate rocket;

use rocket::serde::json::Json;
use rocket::serde::{Serialize, Deserialize};
use rocket_sync_db_pools::{database, diesel};
use diesel::prelude::*;

mod schema {
    diesel::table! {
        users (id) {
            id -> Int4,
            username -> Varchar,
            email -> Varchar,
            created_at -> Timestamp,
        }
    }
}

#[database("postgres")]
struct Db(diesel::PgConnection);

use schema::users::dsl::*;

#[derive(Debug, Queryable, Serialize)]
struct User {
    id: i32,
    username: String,
    email: String,
    created_at: chrono::NaiveDateTime,
}

#[derive(Debug, Insertable, Deserialize)]
#[diesel(table_name = schema::users)]
struct NewUser {
    username: String,
    email: String,
}

#[get("/users")]
async fn list(db: Db) -> Json<Vec<User>> {
    db.run(|c| users.load::<User>(c).unwrap())
        .await.into()
}

#[get("/users/<user_id>")]
async fn get(db: Db, user_id: i32) -> Option<Json<User>> {
    db.run(move |c| users.find(user_id).first::<User>(c).ok())
        .await.map(Json)
}

#[post("/users", format = "json", data = "<user>")]
async fn create(db: Db, user: Json<NewUser>) -> Json<User> {
    db.run(|c| {
        diesel::insert_into(users)
            .values(&user.into_inner())
            .returning((id, username, email, created_at))
            .get_result(c)
            .unwrap()
    }).await.into()
}

#[delete("/users/<user_id>")]
async fn delete(db: Db, user_id: i32) -> &'static str {
    db.run(move |c| {
        diesel::delete(users.find(user_id)).execute(c).unwrap();
    }).await;
    "Deleted"
}

#[launch]
fn rocket() -> _ {
    rocket::build()
        .attach(Db::fairing())
        .mount("/api", routes![list, get, create, delete])
}

下一步 #

掌握了Diesel集成后，让我们继续学习 SQLx集成，了解另一种数据库访问方式。