九、用 Sqlx 访问数据库

九、用 Sqlx 访问数据库
　　
　　前一节，我们学习了如何在 Rust 项目中集成 Sqlx，本节我们继续完善该工程，以看看如何用 Sqlx 来具体的访问数据库。
 

1、目录结构

.
├── Cargo.lock
├── Cargo.toml
├── README.md
├── config.yaml
└── src
    ├── boot
    │   ├── db.rs
    │   └── mod.rs
    ├── main.rs
    └── module
        ├── mod.rs
        └── user
            ├── api.rs
            ├── bs.rs
            ├── dao.rs
            ├── mod.rs
            └── model.rs

2、完善工程

1）module/user/model.rs

use chrono::NaiveDateTime;
use serde::{Deserialize, Serialize};
use sqlx::FromRow;
use validator::Validate;

#[derive(Debug, Validate, Serialize, Deserialize, FromRow)]	// 增加了 sqlx::FromRow
pub struct User {
    pub id: Option<i32>,	// 改为了 i32
    #[validate(length(max = 50, message = "username must be less than 50 chars."))]
    pub username: String,
    #[validate(length(min = 6, message = "password must be more than 6 chars."))]
    pub password: String,
    #[validate(length(max = 255, message = "username must be less than 255 chars."))]
    pub avatar: Option<String>,
    #[validate(length(max = 80, message = "username must be less than 80 chars."))]
    pub email: Option<String>,
    pub created_at: Option<NaiveDateTime>,
}

2）module/user/dao.rs

use std::vec::Vec;

use sqlx::Done;

use crate::boot::db;
use crate::module::user::model::User;

/**
 * 测试接口: 查 列表
 */
pub async fn list() -> Vec<User> {
    let pool = db::get_pool().unwrap();
    sqlx::query_as::<_, User>("select id, email, username, password, avatar, created_at from users")
        .fetch_all(pool).await.unwrap()
}

/**
 * 测试接口: 增
 */
pub async fn create(user: User) -> u64 {
    let pool = db::get_pool().unwrap();
    sqlx::query("insert into users(email, username, password, avatar) values($1, $2, $3, $4)")
        .bind(&user.email).bind(&user.username).bind(&user.password)
        .bind(&user.avatar).execute(pool).await.unwrap().rows_affected()
    // TODO: 1.最后插入的 主键 值; 2.最终使用 雪花 多数据中心算法
}

/**
 * 测试接口: 查
 */
pub async fn show(id: i32) -> User {
    let pool = db::get_pool().unwrap();
    sqlx::query_as::<_, User>("select * from users where id = $1")
        .bind(id).fetch_one(pool).await.unwrap()
}

/**
 * 测试接口: 改
 */
pub async fn update(id: i32, user: User) -> u64 {
    let pool = db::get_pool().unwrap();
    let pg_done = sqlx::query("update users set password = $1 where id = $2")
        .bind(&user.password).bind(id.clone()).execute(pool).await.unwrap();
    println!("Hello {}! id: {}. result: {:?}", user.username, id, pg_done.rows_affected());
    return pg_done.rows_affected();
}

/**
 * 测试接口: 删
 */
pub async fn delete(id: i32) -> u64 {
    let pool = db::get_pool().unwrap();
    sqlx::query("delete from users where id = $1").bind(id).execute(pool).await.unwrap().rows_affected()
}

 　　关于 sql 中的占位符，PG 是用 $1，$2，$3 … ；而 MySQL 是用 ?，?，? … 。

3）module/user/bs.rs

use crate::module::user;
use crate::module::user::model::User;
// use chrono::Local;

/**
 * 测试接口: 查 列表
 */
pub async fn list() -> Vec<User> {
    let user = user::dao::list().await;
    return user
}

/**
 * 测试接口: 增
 */
pub async fn create(user: User) -> u64 {
//    user.created_at = Some(Local::now().naive_utc());
    user::dao::create(user).await
}

/**
 * 测试接口: 查
 */
pub async fn show(id: i32) -> User {
    user::dao::show(id).await
}

/**
 * 测试接口: 改
 */
pub async fn update(id: i32, user: User) -> u64 {
    user::dao::update(id, user).await
}

/**
 * 测试接口: 删
 */
pub async fn delete(id: i32) -> u64 {
    user::dao::delete(id).await
}

4）module/user/mod.rs

pub mod api;
pub mod bs;		// 改了这里
pub mod dao;	// 改了这里
pub mod model;

5）module/user/api.rs

use actix_web::{delete, get, HttpResponse, post, put, Responder, web};
use actix_web::web::Json;

use crate::module::user::model::User;
use crate::module::user;

/**
 * 测试接口: 增
 */
#[get("/user/list")]
pub async fn list() -> impl Responder {
    let users = user::bs::list().await;
    HttpResponse::Ok().json(users)
}

/**
 * 测试接口: 增
 */
#[post("/user")]
pub async fn create(user: Json<User>) -> impl Responder {
    let rows = user::bs::create(user.0).await;
    HttpResponse::Ok().json(rows)
}

/**
 * 测试接口: 查
 */
#[get("/user/{id}")]
pub async fn show(web::Path(id): web::Path<i32>) -> impl Responder {
    let user = user::bs::show(id).await;
    HttpResponse::Ok().json(user)
}

/**
 * 测试接口: 改
 */
#[put("/user/{id}")]
pub async fn update(web::Path(id): web::Path<i32>, user: Json<User>) -> impl Responder {
    let rows = user::bs::update(id, user.into_inner()).await;
    HttpResponse::Ok().json(rows)
}

/**
 * 测试接口: 删
 */
#[delete("/user/{id}")]
pub async fn delete(web::Path(id): web::Path<i32>) -> impl Responder {
    let rows = user::bs::delete(id).await;
    HttpResponse::Ok().json(rows)
}

6）module/mod.rs

pub mod user;

pub mod handler {
    use actix_web::dev::HttpServiceFactory;
    use actix_web::web;

    use crate::module::user;

    pub fn api_routes() -> impl HttpServiceFactory {
        web::scope("")
            .service(user::api::list)	// 这里增加了一项
            .service(user::api::create)
            .service(user::api::show)
            .service(user::api::update)
            .service(user::api::delete)
    }
}

 　　补充以上源码文件后，可以尝试编译和启动项目：

 $ cargo run
    Finished dev [unoptimized + debuginfo] target(s) in 0.25s
     Running `target/debug/rust-demo`
datasource: postgres://postgres:postgres@192.168.24.251:5432/postgres
       min: 5
       max: 15

3、建测试表

create table users (
    id         serial                              not null constraint users_pkey primary key,
    email      text                                not null,
    username   text                                not null,
    password   text                                not null,
    avatar     text      default ''::text          not null,
    created_at timestamp default CURRENT_TIMESTAMP not null,
    constraint users_email_username_key unique (email, username)
);
alter table users owner to postgres;

4、访问接口

 　　工程的运行和建表都没有问题后，我们来尝试访问接口。

Post => /user

curl --location --request POST 'http://127.0.0.1:8080/user' \
--header 'Content-Type: application/json' \
--data-raw '{
	"email": "xugy3@126.com",
	"username": "xugy4",
	"password": "123456",
	"avatar": "https://avatars3.githubusercontent.com/u/13329376?s=460&v=4"
}'

Get => /user/list

curl --location --request GET 'http://127.0.0.1:8080/user/list'

Get => /user/{id}

curl --location --request GET 'http://127.0.0.1:8080/user/1'

Put => /user/{id}

curl --location --request PUT 'http://127.0.0.1:8080/user/2' \
--header 'Content-Type: application/json' \
--data-raw '{
	"username": "xugy",
	"password": "1234567"
}'

Delete => /user/{id}

curl --location --request DELETE 'http://127.0.0.1:8080/user/2'

 
　　好的，Rust 用 Sqlx 访问数据库的 demo 也完活 ！～