Rust Web Rokcet Series 2 connection database and CURD addition, deletion, modification and query

Original text: https://www.ftls.xyz/posts/rust-rocket2/
Author: macabing sugar

Serial address: https://blog-kkbt.vercel.app/series/rust-web-rokcet/

sqlite database is used. In fact, all kinds of databases are similar. There are already many packages to help us deal with all kinds of details. At present, diesel supports mysql, postgres and sqlite. Functions are generally used when the mouse is moved to the function and displayed in the document. In other words, click Ctrl + to display the source in the left line of the file.

Related documents:

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

Reference writing:

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

Install relevant tools SQLite and diesel_cli

Introduce the installation method under Windows SQLite, and open the download page of SQLite official website.

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

Select Precompiled Binaries for Windows

64 bit machine download sqlite-dll-win64-x64-3370200.zip and sqlite-tools-win32-x86-3370200.zip
32-bit machine download sqlite-dll-win32-x86-3370200.zip and sqlite-tools-win32-x86-3370200.zip

Unzip all the files of the two compressed packages to a directory and execute them in the directory using Microsoft's generation tool

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

Then add the directory to the environment variable, and then open the terminal to execute:

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

Write SQL

Open the project directory and execute

diesel setup
diesel migration generate article

Migrations \ 2022-02-14-114903 generated_ article\up. SQL and migrations22-02-14-114903_article\down.sql
Then write up SQL and down sql

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

-- 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

-- This file should undo anything in `up.sql`

DROP TABLE article;

Create the project root directory, and cargo Toml peer. establish. env file. write in
.env

DATABASE_URL=article.db

Then execute

diesel migration run

Diesel will automatically create an article DB, this is the database we will use later. In addition, there is SRC \ schema RS and diesel Toml file.
Using the database tool, you can see that a table has been successfully created

Add dependency

In cargo Add dependency in toml
Cargo.toml

[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"]

Create rocket toml
Rocket.toml

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

Then in main RS joins the database connection, and the connection pool is enabled by default.

#[macro_use]
extern crate diesel;

use rocket_sync_db_pools::database;

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

Actually, rocket_sync_db_pools::diesel is also available. be similar to

use rocket_sync_db_pools::{database,diesel};

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

But I haven't figured out how to import #[macro_use]. This macro is in SRC \ schema table is used in rs. Let me know if anyone knows. In this way, the dependency of diesel = "1.4.8" should not be introduced.

CURD

Then modify SRC \ main rs，src\module.rs，src\routes.rs .

src\main.rs :

#[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

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 simply wrote CURD. See the document for details. Functions are generally used when the mouse is moved to the function and displayed in the document. In other words, between the lines of the source file displayed by Ctrl + left clicking.

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!"})
}

// Check 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))
}
// Check 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()
}
// increase
#[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))
}

// Change 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(|| ()))
}

// Delete 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(|| ()))
}

// Delete 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(())
}

test

Postman JSON format shared link:

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

If the link fails, please contact me. Only one sound.