超光速
文章 检索 链接 关于
超光速

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

恐咖兵糖 avatar  收录于  类别 Rust 和 系列 Rust Web Rokcet
     约 2181 字  预计阅读 5 分钟    次阅读     条评论  
系列 - Rust Web Rokcet

使用的是 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.zip 和sqlite-tools-win32-x86-3370200.zip
32位机下载sqlite-dll-win32-x86-3370200.zip和sqlite-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 <0@ftls.xyz>"]

# 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 分享链接:

https://www.getpostman.com/collections/c89ec512876818f18757

如果链接失效了，请只会一声。或 teach.postman_collection.json 如下

  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
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154

{
	"info": {
		"_postman_id": "709bf03a-bdd4-4b98-afac-b01ae46d2b65",
		"name": "teach",
		"schema": "https://schema.getpostman.com/json/collection/v2.0.0/collection.json",
		"_exporter_id": "16599952"
	},
	"item": [
		{
			"name": "http://127.0.0.1:8000/",
			"request": {
				"method": "GET",
				"header": [],
				"url": "http://127.0.0.1:8000/"
			},
			"response": []
		},
		{
			"name": "获取所有文章",
			"request": {
				"method": "GET",
				"header": [],
				"url": "http://127.0.0.1:8000/article"
			},
			"response": []
		},
		{
			"name": "增加文章",
			"request": {
				"method": "POST",
				"header": [],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"title\": \"a title\",\r\n    \"author\": \"恐咖兵糖\",\r\n    \"content\": \"dasdaadas\",\r\n    \"created_at\": \"2022-02-14 \"\r\n}",
					"options": {
						"raw": {
							"language": "json"
						}
					}
				},
				"url": "http://127.0.0.1:8000/article"
			},
			"response": []
		},
		{
			"name": "获取文章 by id",
			"request": {
				"method": "GET",
				"header": [],
				"url": "http://127.0.0.1:8000/article/2"
			},
			"response": []
		},
		{
			"name": "删除文章 by id",
			"request": {
				"method": "DELETE",
				"header": [],
				"url": "http://127.0.0.1:8000/article/3"
			},
			"response": []
		},
		{
			"name": "更新文章",
			"request": {
				"method": "PUT",
				"header": [],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"title\": \"a title\",\r\n    \"author\": \"恐咖兵糖\",\r\n    \"content\": \"222ssss2\",\r\n    \"created_at\": \"2022-02-14 \"\r\n}",
					"options": {
						"raw": {
							"language": "json"
						}
					}
				},
				"url": "http://127.0.0.1:8000/article/4"
			},
			"response": []
		},
		{
			"name": "删除所有文章",
			"request": {
				"method": "DELETE",
				"header": [],
				"url": "http://127.0.0.1:8000/article/all"
			},
			"response": []
		},
		{
			"name": "获取token",
			"request": {
				"method": "POST",
				"header": [],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"id\": 0,\r\n    \"key\": \"oR66T*W8y4VaXkh#rTjeZ$$Rby$NCy!nJX\"\r\n}",
					"options": {
						"raw": {
							"language": "json"
						}
					}
				},
				"url": "http://127.0.0.1:8000/token"
			},
			"response": []
		},
		{
			"name": "token 测试",
			"event": [
				{
					"listen": "prerequest",
					"script": {
						"exec": [
							"const gettoken = {\r",
							"    url: ' http://127.0.0.1:8000/token',\r",
							"    method: \"POST\",\r",
							"    header: 'Content-Type: application/json',\r",
							"    body: {\r",
							"        mode: 'raw', \r",
							"    raw: JSON.stringify({\"id\": 0, \"key\": \"oR66T*W8y4VaXkh#rTjeZ$$Rby$NCy!nJX\"}) //要将JSON对象转为文本发送\r",
							"\r",
							"    }\r",
							"}\r",
							"pm.sendRequest(gettoken, function (err, response) {\r",
							"    console.log(response.json().token);\r",
							"    pm.collectionVariables.set(\"token\",response.json().token);\r",
							"});"
						],
						"type": "text/javascript"
					}
				}
			],
			"request": {
				"method": "GET",
				"header": [
					{
						"key": "Authorization",
						"value": "Bearer {{token}}",
						"type": "default"
					}
				],
				"url": "http://127.0.0.1:8000/token/test"
			},
			"response": []
		}
	],
	"variable": [
		{
			"key": "token",
			"value": ""
		}
	]
}
更新于 2024-03-05  481dd95
阅读原始文档 | 查看源代码 | 编辑此页 | 报告问题
 Rust, Web, Rocket
返回 | 主页