Rust Web Rokcet 系列 2 连接数据库 和 CURD 增删改查

系列 - Rust Web Rokcet
警告
本文最后更新于 2022-02-15,文中内容可能已过时。

使用的是 SQLite 数据库,实际上各种数据库都大同小异。已经有很多包帮助我们处理各种细节。目前 diesel 支持mysql,postgres和sqlite。函数使用方法一般都在鼠标移动到函数上,显示出的文档中。换句话说,在 Ctrl+左键 点击进去显示的源文件的字里行间之中。

相关文档:

https://rocket.rs/v0.5-rc/guide/state/#databases

参考写法:

https://github.com/SergioBenitez/Rocket/tree/v0.5-rc/examples/databases

介绍一下 Windows SQLite 下安装方法 ,打开 SQLite 官网 的下载页。

https://www.sqlite.org/download.html

选择Precompiled Binaries for Windows

64位机下载sqlite-dll-win64-x64-3370200.zipsqlite-tools-win32-x86-3370200.zip 32位机下载sqlite-dll-win32-x86-3370200.zipsqlite-tools-win32-x86-3370200.zip

解压这两个压缩包所有文件到一个目录,并使用 微软的 生成工具 ,在目录下执行

1
lib /def:sqlite3.def /machine:X64 /out:sqlite3.lib

然后将目录加入环境变量,然后打开终端执行:

1
cargo install diesel_cli --no-default-features --features sqlite

打开 项目目录,执行

1
2
diesel setup
diesel migration generate article

生成了migrations\2022-02-14-114903_article\up.sql和migrations\2022-02-14-114903_article\down.sql 然后写 up.sql 和 down.sql

migrations\2022-02-11-063903_product\up.sql

1
2
3
4
5
6
7
8
-- Your SQL goes here
CREATE TABLE article (
 id INTEGER NOT NULL PRIMARY KEY,
 title Text NOT NULL,
 author Text NOT NULL,
 content Text NOT NULL,
 created_at Text NOT NULL
)

migrations\2022-02-14-114903_article\down.sql

1
2
3
-- This file should undo anything in `up.sql`

DROP TABLE article;

创建项目根目录,和Cargo.toml同级。创建 .env 文件。写入 .env

1
DATABASE_URL=article.db

然后执行

1
diesel migration run

diesel会自动创建一个article.db,这就是我们之后会使用到的数据库了。除此之外,还有src\schema.rs和 diesel.toml文件。 使用数据库工具,可以看到已经成功创建了一个表

https://cdn.ftls.xyz/images/2022/02/20220214200151.png
创建数据表

在Cargo.toml中添加依赖 Cargo.toml

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
[package]
name = "teach_used"
version = "0.1.0"
edition = "2021"
authors = ["Kkbt <snowandocean@foxmail.com>"]

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
rocket = { version = "0.5.0-rc.1",features = ["json"]}
diesel = "1.4.8"

[dependencies.rocket_sync_db_pools]
version = "0.1.0-rc.1"
default-features = false
features = ["diesel_sqlite_pool"]

创建Rocket.toml Rocket.toml

1
2
[global.databases]
sqlite_main = { url = "article.db" }

然后在 main.rs 加入数据库连接,默认开启连接池。

1
2
3
4
5
6
7
#[macro_use]
extern crate diesel;

use rocket_sync_db_pools::database;

#[database("sqlite_main")]
pub struct MainDbConn(diesel::SqliteConnection);

其实 rocket_sync_db_pools::diesel 也是可用的。类似于

1
2
3
4
use rocket_sync_db_pools::{database,diesel};

#[database("sqlite_main")]
pub struct MainDbConn(diesel::SqliteConnection);

但是我还没弄明白如何导入 #[macro_use] ,这个宏在 src\schema.rs 中 table 用到了。有人知道的话知会我一声。这样的应该不用引入 diesel = “1.4.8” 依赖了。

然后修改 src\main.rs,src\module.rs,src\routes.rs 。

src\main.rs :

 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
#[macro_use]
extern crate rocket;

#[macro_use]
extern crate diesel;

mod module;
mod routes;
mod schema;

use rocket_sync_db_pools::database;
use routes::*;

#[database("sqlite_main")]
pub struct MainDbConn(diesel::SqliteConnection);

#[launch]
fn rocket() -> _ {
    rocket::build()
        // database
        .attach(MainDbConn::fairing())
        .mount("/", routes![index])
        // add api
        .mount("/", routes![get_all_articles, get_article_by_id])
        .mount("/", routes![post_article, put_article])
        .mount("/", routes![delete_all_articles, delete_article])
}
 

src\module.rs

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
use crate::schema::article;
use diesel::Insertable;
use rocket::serde::{Deserialize, Serialize};

#[derive(Serialize, Deserialize, Clone, Debug, Queryable, Insertable)]
#[serde(crate = "rocket::serde")]
#[table_name = "article"]
pub struct Article {
    pub id: i32,
    pub title: String,
    pub author: String,
    pub content: String,
    pub created_at: String,
}
#[derive(Debug, Serialize, Deserialize, Queryable, Clone, Insertable, AsChangeset)]
#[serde(crate = "rocket::serde")]
#[table_name = "article"]
pub struct PostArticle {
    pub title: String,
    pub author: String,
    pub content: String,
    pub created_at: String,
}

src\routes.rs 简单写了 CURD ,具体可以看文档。函数使用方法一般都在鼠标移动到函数上,显示出的文档中。换句话说,在 Ctrl+左键 点击进去显示的源文件的字里行间之中。

 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
82
83
84
85
86
use diesel::{prelude::*, QueryDsl, RunQueryDsl};
use rocket::serde::json::{serde_json::json, Json, Value};
use rocket::{delete, get, post, put, response::status::Created, response::Debug};

use crate::module::{Article, PostArticle};
use crate::schema::article;
use crate::MainDbConn;

type Result<T, E = Debug<diesel::result::Error>> = std::result::Result<T, E>;

#[get("/")]
pub fn index() -> Value {
    json!({"kkbt":"Hello, world!"})
}

// 查 all
#[get("/article")]
pub async fn get_all_articles(db: MainDbConn) -> Result<Json<Vec<Article>>> {
    let all = db
        .run(move |conn| article::table.load::<Article>(conn))
        .await?;

    Ok(Json(all))
}
// 查 by id
#[get("/article/<in_id>")]
pub async fn get_article_by_id(db: MainDbConn, in_id: i32) -> Option<Json<Article>> {
    db.run(move |conn| article::table.filter(article::id.eq(in_id)).first(conn))
        .await
        .map(Json)
        .ok()
}
// 增
#[post("/article", format = "json", data = "<in_article>")]
pub async fn post_article(
    db: MainDbConn,
    in_article: Json<PostArticle>,
) -> Result<Created<Json<PostArticle>>> {
    let article_in = in_article.clone();
    db.run(move |conn| {
        diesel::insert_into(article::table)
            .values(&article_in)
            .execute(conn)
    })
    .await?;
    Ok(Created::new("/").body(in_article))
}

// 改 by id
#[put("/article/<in_id>", format = "json", data = "<in_article>")]
pub async fn put_article(
    db: MainDbConn,
    in_id: i32,
    in_article: Json<PostArticle>,
) -> Result<Option<()>> {
    let affected = db
        .run(move |conn| {
            diesel::update(article::table.filter(article::id.eq(in_id)))
                .set(in_article.into_inner())
                .execute(conn)
        })
        .await?;
    Ok((affected == 1).then(|| ()))
}

// 删 by id
#[delete("/article/<in_id>")]
pub async fn delete_article(db: MainDbConn, in_id: i32) -> Result<Option<()>> {
    let affected = db
        .run(move |conn| {
            diesel::delete(article::table)
                .filter(article::id.eq(&in_id))
                .execute(conn)
        })
        .await?;
    Ok((affected == 1).then(|| ()))
}

// 删 all
#[delete("/article/all")]
pub async fn delete_all_articles(db: MainDbConn) -> Result<()> {
    db.run(move |conn| diesel::delete(article::table).execute(conn))
        .await?;

    Ok(())
}

Postman JSON 格式共享链接:

https://www.getpostman.com/collections/46d782fa07ef766822f6

如果链接失效了,请联系我。只会一声。