sql queries within the rust backend

[gabrielcsapo]

I understand that the sql plugin provided just enables migrations. I am having a difficult time configuring sqlx on the rust backend, does anyone have v2 examples of this? The v1 examples are not working for me.

Another question is does having the sqlx rust backend impact mobile application usage?

[gabrielcsapo]

I was able to get this to build

#[tauri::command]
async fn testing_query(app: State<'_, DbInstances>) -> Result<HistoryItem, String> {
    let db_instance = app.inner().lock().await;

    let qry = "SELECT id, entityType, entityId, value, file, timestamp
            FROM history
            ORDER BY timestamp DESC
            LIMIT 1";

    match sqlx::query(&qry)
        .fetch_one(&*db_instance)
        .await
    {
        Ok(row) => Ok(HistoryItem {
            id: row.get("id"),
            entity_type: row.get("entityType"),
            entity_id: row.get("entityId"),
            value: row.get("value"),
            file: row.get("file"),
            timestamp: row.get("timestamp"),
        }),
        Err(e) => Err(e.to_string()),
    }
}

#[derive(Debug, serde::Serialize)]
struct HistoryItem {
    id: i32,
    entity_type: String,
    entity_id: i32,
    value: serde_json::Value,  // Use serde_json::Value to parse JSON
    file: Option<Vec<u8>>,      // BLOB as an optional Vec<u8>
    timestamp: String,
}

but I am getting

state not managed for field `app` on command `testing_query`. You must call `.manage()` before using this command

[gabrielcsapo]

Got it working

#[tauri::command]
async fn testing_query(state_mutex: State<'_, Mutex<Pool<Sqlite>>>,) -> Result<HistoryItem, String> {
    let pool = state_mutex.lock().await;
    let qry = "SELECT id, entityType, entityId, value, file, timestamp
            FROM history
            ORDER BY timestamp DESC
            LIMIT 1";

    match sqlx::query(&qry)
        .fetch_one(&*pool)
        .await
    {
        Ok(row) => Ok(HistoryItem {
            id: row.get("id"),
            entity_type: row.get("entityType"),
            entity_id: row.get("entityId"),
            value: row.get("value"),
            file: row.get("file"),
            timestamp: row.get("timestamp"),
        }),
        Err(e) => Err(e.to_string()),
    }
}

#[derive(Debug, serde::Serialize)]
struct HistoryItem {
    id: i32,
    entity_type: String,
    entity_id: i32,
    value: serde_json::Value,  // Use serde_json::Value to parse JSON
    file: Option<Vec<u8>>,      // BLOB as an optional Vec<u8>
    timestamp: String,
}

...
.setup(|app| {
            tauri::async_runtime::spawn(async move {});
            tauri::async_runtime::block_on(async move {
                let _ = fs::create_dir_all(app.path().app_data_dir().unwrap());
                let db_path = app.path().app_data_dir().unwrap().join("test.db");

                let options = SqliteConnectOptions::new()
                    .filename(db_path)
                    .create_if_missing(true);
                let pool = SqlitePool::connect_with(options).await;
                match pool {
                    Ok(db) => {
                        app.manage(Mutex::new(db.clone()));
                    }
                    Err(e) => {}
                }
            });
            Ok(())
        })
...

[TheToddmeister]

Yoooooink!
Stealing this