Demo sqlx

main.rs

/* Cargo.toml
[package]
name = "sqlxx"
version = "0.1.0"
authors = ["Agus Susilo <smartgdi@gmail.com>"]
edition = "2018"

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

[dependencies]
anyhow = "1.0"
sqlx = { version = "0.3", default-features = false, features = [ "runtime-tokio", "macros", "mysql" ] }
tokio = { version = "0.2", features = ["rt-core", "macros"] }
dotenv = "0.15.0"
*/

#[macro_use]
extern crate sqlx;

use std::env;

use sqlx::MySqlPool;
use sqlx::mysql::MySqlQueryAs;
use dotenv::dotenv;

#[tokio::main]
async fn main() -> anyhow::Result<(), sqlx::Error> {//Result<(), sqlx::Error> {
    dotenv().ok();
    
    let pool = MySqlPool::new(&env::var("DATABASE_URL").unwrap()).await?;
    
    let row: (i64,) = sqlx::query_as("SELECT ?")
        .bind(150_i64)
        .fetch_one(&pool).await?;
        
    println!("row = {}", row.0);

    // let last_id = add_todo(&pool, "sqlx".to_owned(), "Nyoba sqlx".to_owned()).await.expect("error");
    // print!("last_id: {}", last_id);
    // update_todo(&pool, 1, "Bikin Todo App".to_string(), "Bikin todo App".to_string()).await.expect("error");
    list_todos(&pool).await.expect("error");

    let one = view(&pool, 1).await.expect("Error view");
    println!("{:?}", one);


    // let _ = find_where(&pool).await.expect("Error find_where");
    let _ = find_one(&pool).await.expect("Error find_one");

    Ok(())
}

// Tambah todo
async fn add_todo(pool: &MySqlPool, title: String, body: String) -> anyhow::Result<u64> {
    sqlx::query!(r#"INSERT INTO todos(title, body, inserted_at, updated_at) VALUES(?, ?, "2020-04-29 04:38:10", "2020-04-29 04:38:10")"#, title, body)
        .execute(pool)
        .await?;

    let rec: (u64,) = sqlx::query_as("SELECT LAST_INSERT_ID()")
        .fetch_one(pool)
        .await?;

    Ok(rec.0)
}

// List todo
async fn list_todos(pool: &MySqlPool) -> anyhow::Result<()> {
    let recs = sqlx::query!(r#"SELECT id, title, body FROM todos"#)
        .fetch_all(pool)
        .await?;

    println!("id\ttitle\t\tbody");
    for rec in recs {
        println!("{}\t{}\t\t{}", rec.id, &rec.title.unwrap(), &rec.body.unwrap());
    }

    Ok(())
}

// Update todo
async fn update_todo(pool: &MySqlPool, id: i32, title: String, body: String) -> anyhow::Result<bool> {
    let rows_affected = sqlx::query!(r#"UPDATE todos SET title = ?, body = ? WHERE id = ?"#, title, body, id)
    .execute(pool)
    .await?;
    
    Ok(rows_affected > 0)
}

// View
async fn view(pool: &MySqlPool, id: i32) -> anyhow::Result<Todo> {
    let row = sqlx::query!("SELECT id, title FROM todos WHERE id = ?", id)
        .fetch_one(pool)
        .await?;
    
    Ok(Todo { id: row.id as i32, title: row.title.unwrap(), })
}

// async fn transaction_op(pool: &MySqlPool, title: String, body: String) -> anyhow::Result<u64> {
//     let mut tx = pool.begin().await?;
    
//     let rec: (u64, _) = sqlx::query!(r#"INSERT INTO todos(title, body, inserted_at, updated_at) VALUES(?, ?, "2020-04-29 04:38:10", "2020-04-29 04:38:10")"#, title, body)
//         .execute(&mut tx)
//         .await?;

//     Ok(rec.0)
// }

#[derive(sqlx::FromRow, Debug)]
struct Todo {
    id: i32,
    title: String,
}

#[derive(sqlx::FromRow, Debug)]
struct Demo {
    id: u32,
    cnt: i32,
}

async fn find_where(pool: &MySqlPool) -> anyhow::Result<()> {
    let mut conn = pool.acquire().await?;
    let demo = sqlx::query_as::<_, Demo>("SELECT * FROM demo WHERE id = ?")
        .bind(1u32)
        .fetch_one(&mut conn)
        .await?;
    
    println!("{:?}", demo);
    Ok(())
}

async fn find_one(pool: &MySqlPool) -> anyhow::Result<Demo> {
    let mut conn = pool.acquire().await?;
    let demo = sqlx::query_as!(Demo, "SELECT * FROM demo WHERE id = ?", 1i32)
        .fetch_one(&mut conn)
        .await?;
    
    println!("{:?}", demo);
    Ok(demo)
}