Rust笔记:04-Rust对Postgres增删改查

1.背景

sqlx(v0.6.0) 是 rust 中的一个数据库访问工具.具有以下特点:

  • 异步:原生就支持异步,在并发性高的场合能够得到更好的支持
  • 编译时检查查询(宏支持,需要配置 DATABASE_URL 环境变量):sqlx可以在 cargo build 的时候检查执行sql和响应值
  • 多数据库支持:PostgresSQL,MySql,SqlLite,MSSql,MariaDB
  • 多运行时支持:支持主流 rust 运行时.async-std,tokio,actix,native-tls,rustls
  • 纯rust实现mysql和postgresql 访问驱动程序(sqlite使用了 libsqlite3 C 库)
  • 内置连接池,支持查询缓存

PostgreSQL 是一款高级的企业级开源关系数据库,支持 SQL(关系型)和 JSON(非关系型)查询.它是一个高度稳定的数据库管理系统,依托 20 多年的社区发展,造就了其高水平的故障恢复能力、完整性和正确性.PostgreSQL 可用作很多 Web、移动、地理空间和分析应用程序的主要数据存储或数据仓库.最新主要版本为 PostgreSQL 12.

2.cargo.toml

[package]
name = "kateway"
version = "0.1.0"
edition = "2021"

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

[dependencies]
hyper = { version = "0.14", features = ["full"] }
tokio = { version = "1", features = ["full"] }
radixtree = "0.1.1" # https://crates.io/crates/radixtree
lazy_static = "1.4.0"
trace = "0.1.6"
unicase = "2.6.0"
serde_json = "1.0.81"
serde = "1.0.137"
sqlx = {version = "0.6.0", features = ["postgres","uuid", "sqlite","chrono", "json","runtime-tokio-rustls"]} #https://docs.rs/crate/sqlx/0.6.0/features#runtime-tokio-rustls
anyhow = "1.0.57"

3. 代码

use sqlx::{postgres::{PgPool}};
use std::{ sync::Arc};
use serde::Serialize;
use sqlx::{Result};


#[derive(sqlx::FromRow, Debug, Serialize)]
pub struct App {
    id:String,
    name:String,
    secret_key:String,
    description:String,
    creator_email:String,
}

// CREATE TABLE IF NOT EXISTS apps (
//     id          CHARACTER(128) PRIMARY KEY,
//     name CHARACTER(128)    NOT NULL,
//     secret_key CHARACTER(128)    NOT NULL,
//     creator_email CHARACTER(128)    NOT NULL,
//     description TEXT    NOT NULL
// );
// DATABASE_URL="postgres://postgres:123456@mypg:5432/fateway"




pub struct AppRepo {
    pg_pool: Arc<PgPool>,
}

impl AppRepo {
    pub fn new(pg_pool: PgPool) -> Self {
        Self {
            pg_pool: Arc::new(pg_pool),
        }
    }

    pub async  fn insert(&self, arg: App) -> Result<String> {
        let rec = sqlx::query!(
            r#"INSERT INTO apps ( id, name, secret_key, creator_email, description ) VALUES ( $1, $2, $3, $4, $5 ) RETURNING id"#,
            arg.id,arg.name,arg.secret_key,arg.creator_email,arg.description
        )
        .fetch_one(&*self.pg_pool)
        .await?;
        Ok(rec.id)
    }
    
    pub async  fn update(&self, arg:  App) -> Result<bool> {
        let rows_affected = sqlx::query!(
            r#"UPDATE apps SET  name = $2, secret_key = $3, creator_email = $4, description = $5  WHERE id = $1 "#,
            arg.id,arg.name,arg.secret_key,arg.creator_email,arg.description
        )
        .execute(&*self.pg_pool)
        .await? .rows_affected();
        Ok(rows_affected > 0)
    }
    pub async  fn delete(&self, id:  String ) -> Result<u64> {
        let rows_affected = sqlx::query!(
            r#"DELETE FROM apps WHERE id = $1 "#,
            id
        )
        .execute(&*self.pg_pool)
        .await? .rows_affected();
        Ok(rows_affected)
    }

    pub async  fn list(&self)->Result<Vec<App>> {
        sqlx::query_as::<_, App>("SELECT * FROM apps ORDER BY $1").bind("id").fetch_all(&*self.pg_pool).await
    }

    pub async  fn get(&self, id: String)-> Result<Option<App>>{
         sqlx::query_as!(
            App,    
                    "SELECT * FROM apps WHERE id = $1",id
        ).fetch_optional(&*self.pg_pool).await
    }

}



#[cfg(test)]
mod tests {
    use super::*;

    #[tokio::test]
    async fn test_mocked_add() {


        let pg_url = "postgres://postgres:123456@mypg:5432/fateway";
        let pool = PgPool::connect(pg_url).await.unwrap();


    
        let repo = AppRepo::new(pool);

        let apps = repo.list().await.unwrap();
        println!("{:?}",apps);
        let some_app = App{
            id: "an2y-id-is-ok-91".to_string(),
            name: "eric".to_string(),
            secret_key: "sk".to_string(),
            description: "description".to_string(),
            creator_email:  "neochau@gmail.com".to_string(),
        };
        let id= repo.insert(some_app).await.unwrap();

        repo.get(id).await.unwrap();
    }
}

3.1 &*self.pg_pool

The * operator turns the Arc<T> into T . The & operator borrows that T into &T. So when we put them together, &*self.pg_pool borrows the Arc into &T. Another way of writing that code would be: `let db = self.pg_pool..deref()`

* 操作符把 Arc<T> 装换成 T, & 借用 T 成为 &T, 组合起来 &*self.pg_pool 转换成 PgPool 实例的借用.

The End

线上交流工具: 在你的terminal中输入 ssh $用户@mojotv.cn

在你的terminal中输入 ssh mojotv.cn hn 查看最新 hacknews

目录