Arel  

• Install

# db features: sqlite|mysql|postgres|mssql
# runtime features: runtime-tokio-native-tls|runtime-tokio-rustls|runtime-async-std-native-tls|runtime-async-std-rustls
arel = { version = "0.1", features = ["sqlite", "runtime-tokio-native-tls"]}

• Demo

use arel::prelude::*;
use chrono::{TimeZone};

#[arel(table_name="users", primary_key="id")]
struct User {
    id: Option<i64>,
    name: String,
    #[arel(table_column_name="type")]
    r#type: Option<i32>,
    #[arel(table_column_name="desc")]
    desc2: String,
    expired_at: chrono::DateTime<chrono::Utc>,
}

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let db_state = arel::visitors::get_or_init_db_state(|| Box::pin(async {
        sqlx::any::AnyPoolOptions::new().max_connections(5).connect("sqlite::memory:").await
    })).await?;

    let sql: String = User::query()
        .r#where(json!({"name": "Tom"}))
        .r#where(json!(["active = ?", true]))
        .where_not(json!({"status": [1, 2, 3]}))
        .where_or(json!({"login": false, "phone": null}))
        .where_between(json!({"age": [18, 35]}))
        .where_range("expired_at", ..=chrono::Utc.ymd(2021, 12, 31).and_hms(23, 59, 59))
        .distinct()
        .into();
    assert_eq!(sql, "SELECT DISTINCT `users`.* FROM `users` WHERE `users`.`name` = 'Tom' AND active = 1 AND `users`.`status` NOT IN (1, 2, 3) AND (`users`.`login` = 0 OR `users`.`phone` IS NULL) AND `users`.`age` BETWEEN 18 AND 35 AND `users`.`expired_at` <= '2021-12-31T23:59:59Z'");

    // query batch vec<User>
    let users = User::query().fetch_all().await?;
    println!("users: {:#?}", users);
    // update batch
    User::update_all(json!({"name": "update_1"})).execute().await?;
    // delete batch
    User::delete_all(json!(["id > ?", 5])).execute().await?;
    
    // query one User
    let user = User::query().fetch_one().await?;
    println!("user: {:#?}", user);
    // create one
    let mut user = User::new();
    user.set_name("lily".to_string()).save().await?;
    println!("user: {:#?}", user);
    // update one
    let mut user = User::query().fetch_one().await?;
    user.set_name("Tom".to_string()).save().await?;
    println!("user: {:#?}", user);
    // delete one
    let mut user = User::query().fetch_one().await?;
    let result = user.delete().await?;
    println!("{:?}", result);

    // Transaction Support 
    User::with_transaction(|tx| Box::pin(async {
        let mut u1 = User::query().fetch_one_with_executor(&mut *tx).await?;
        let mut u2 = User::query().fetch_last_with_executor(&mut *tx).await?;
        u1.set_name("tx1".to_string()).save_with_executor(&mut *tx).await?;
        u2.set_name("tx2".to_string()).save_with_executor(&mut *tx).await?;
        Ok(None)
    })).await?;

    // With Lock In Transaction Support
    let mut u1 = User::query().fetch_one().await?;
    let mut u2 = User::query().fetch_one().await?;
    u1.clone().with_lock(|tx| Box::pin(async move {
        u1.set_name("with_lock1".to_string()).save_with_executor(&mut *tx).await?;
        u2.set_name("with_lock2".to_string()).save_with_executor(&mut *tx).await?;
        Ok(None)
    })).await?;
    
    Ok(())
}

Query

let sql = User::query().to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users`");
let sql = User::query().select(json!(["name", "age"])).to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.`name`, `users`.`age` FROM `users`");

// distinct
let sql = User::query().distinct().to_sql_string().unwrap();
assert_eq!(sql, "SELECT DISTINCT `users`.* FROM `users`");
// count
let sql = User::query().count().to_sql_string().unwrap();
assert_eq!(sql, "SELECT COUNT(`users`.*) FROM `users`");
// sum
let sql = User::query().sum("price").to_sql_string().unwrap();
assert_eq!(sql, "SELECT SUM(`users`.`price`) FROM `users`");
// avg
let sql = User::query().avg("price").to_sql_string().unwrap();
assert_eq!(sql, "SELECT AVG(`users`.`price`) FROM `users`");
// min
let sql = User::query().min("price").to_sql_string().unwrap();
assert_eq!(sql, "SELECT MIN(`users`.`price`) FROM `users`");
// max
let sql = User::query().max("price").to_sql_string().unwrap();
assert_eq!(sql, "SELECT MAX(`users`.`price`) FROM `users`");

let sql = User::query()
.r#where(json!({"name": "Tom"}))
.r#where(json!(["active = ?", true]))
.to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` WHERE `users`.`name` = 'Tom' AND (active = 1)");
// where_not
let sql = User::query()
.r#where_not(json!({"name": "Tom", "status": [1, 2, 3]}))
.r#where(json!(["active = ?", true]))
.to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` WHERE `users`.`name` != 'Tom' AND `users`.`status` NOT IN (1, 2, 3) AND (active = 1)");
// range
let sql = User::query().where_range("age", 18..25).to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` WHERE (`users`.`age` >= 18 AND `users`.`age` < 25)");
// range_between
let sql = User::query().where_range_between("age", 18..25).to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` WHERE `users`.`age` BETWEEN 18 AND 25");

let sql = User::query()
.joins(json!("left join orders on users.id = orders.user_id"))
.r#where(json!({"name": "Tom"}))
.to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` left join orders on users.id = orders.user_id WHERE `users`.`name` = 'Tom'");

let sql = User::lock().r#where(json!({"x": 1})).to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` WHERE `users`.`x` = 1 FOR UPDATE");

let sql = User::query().group(json!(["name", "email"])).group(json!("age")).to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` GROUP BY `users`.`name`, `users`.`email`, age");

let sql = User::query().group(json!("age"))
    .having_not(json!({"x": 1}))
    .having(json!(["y > ?", 2]))
    .having_range("z", 18..)
    .to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` GROUP BY age HAVING `users`.`x` != 1 AND y > 2 AND `users`.`z` >= 18");

let sql = User::query().order(json!({
            "name": "desc"
        })).order(json!("age ASC")).to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` ORDER BY `users`.`name` DESC, age ASC");

let sql = User::query().limit(10).to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` LIMIT 10");
let sql = User::query().offset(10).to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` OFFSET 10");
let sql = User::query().paginate(5, 10).to_sql_string().unwrap();
assert_eq!(sql, "SELECT `users`.* FROM `users` LIMIT 10 OFFSET 40");

Insert

let sql = User::create(json!({
            "name": "Tom",
            "age": 18,
        })).to_sql_string().unwrap();
assert_eq!(sql, "INSERT INTO `users` (`age`, `name`) VALUES (18, 'Tom')");

Update

let sql = User::update_all(json!({
                "name": "Tom"
            })).r#where(json!({
                "x": 1
            })).to_sql_string().unwrap();
assert_eq!(sql, "UPDATE `users` SET `users`.`name` = 'Tom' WHERE `users`.`x` = 1");

Delete

let sql = User::delete_all(json!({
            "name": "Tom",
            "age": 18,
        })).order(json!("id desc")).offset(1).limit(5).to_sql_string().unwrap();
assert_eq!(sql, "DELETE FROM `users` WHERE `users`.`age` = 18 AND `users`.`name` = 'Tom' ORDER BY id desc LIMIT 5 OFFSET 1");

Transaction

let mut u1 = User::query().fetch_one().await?;
// if u1 should move to closure, please use with_transaction replaced, (prevent clone u1)
let mut u1 = u1.clone().with_lock(|tx| Box::pin(async move {
    u1.set_desc2("with_lock1".to_string());
    u1.save_with_executor(&mut *tx).await?;
    Ok(Some(u1))
})).await?.unwrap();
println!("{:?}", u1);

let tx = User::transaction_start().await?;
let u1 = User::transaction_auto_commit(|tx| Box::pin(async move {
    u1.lock_self_with_executor(tx).await?;
    u1.set_desc2("with_lock1".to_string());
    u1.save_with_executor(&mut *tx).await?;
    Ok(Some(u1))
}), tx).await?.unwrap();
println!("{:?}", u1);

let mut u1 = User::query().fetch_one().await?;
let u1 = User::with_transaction(|tx| Box::pin(async move {
    u1.lock_self_with_executor(tx).await?;
    let mut u2 = User::query().fetch_last_with_executor(&mut *tx).await?;
    u1.set_desc2("tx1".to_string());
    u2.set_desc2("tx2".to_string());
    u1.save_with_executor(&mut *tx).await?;
    u2.save_with_executor(&mut *tx).await?;
    Ok(Some(u1))
})).await?.unwrap();
println!("{:?}", u1);

If you wanna support Optimistic Lock please provide locking_column attribute

Association

Supports: belongs_to, has_one, has_many, has_and_belongs_to_many

look at test code