sqlite / sqlx + async

#rust #database #sql

I am working on geolocation bot. This bot should send to users nearest showplaces in Tashkent. I created some demo DB using Python and Django. And I have an old telegram bot that sends nearest cafes in Tbilisi. But it doesn’t use any DB just a hardcoded catalog. So I spend an evening to try Rust crates sqlite and sqlx. I’ve already tested both of them with my bot: both works. Here are few lines of code to show how it works with different libraries.

sqlx

[dependencies]
anyhow = "1.0"
tokio = { version = "1", features = ["full"] }
sqlx = { version = "0.6", features = ["sqlite", "runtime-tokio-native-tls"] }

use sqlx::{sqlite::SqlitePool, Row};

struct Museums {
    name: String,
    summ: String,
    sche: String,
    pric: String,
}

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let content = database().await;

    for i in content {
        println!("{}\nWork hours: {}\nTicket price = {}\n\n{}\n", i.name, i.sche, i.pric, i.summ)
    }

    Ok(())
}
#[allow(unused)]
async fn database() -> Vec<Museums> {
    let mut museums: Vec<Museums> = vec![];

    let pool = match SqlitePool::connect("db.sqlite3").await {
        Ok(it) => it,
        Err(err) => return museums,
    };

    let base = sqlx::query("SELECT * FROM catalog_museum").fetch_all(&pool).await;
    for i in base.unwrap() {
        let temp_struct = Museums {
            name: i.get(1),
            summ: i.get(2),
            sche: i.get(3),
            pric: i.get(4),
        };
        museums.push(temp_struct)
     }

    museums
}

sqlite

[dependencies]
anyhow = "1.0"
tokio = { version = "1", features = ["full"] }
sqlite = "0.30.4"

use sqlite::State;

struct Museums {
    name: String,
    summ: String,
    sche: String,
    pric: String,
}

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let content = database().await;
    for i in content {
        println!("{}\nWork hours: {}\nTicket price = {}\n\n{}\n", i.name, i.sche, i.pric, i.summ)
    }

    Ok(())
}

async fn database() -> Vec<Museums> {
    let connection = sqlite::open("db.sqlite3").unwrap();
    let query = "SELECT * FROM catalog_museum";
    let mut statement = connection.prepare(query).unwrap();

    let mut museums: Vec<Museums> = vec![];

    while let Ok(State::Row) = statement.next() {
        let temp_sctruct = Museums {
            name: statement.read::<String, _>("name").unwrap(),
            summ: statement.read::<String, _>("summary").unwrap(),
            sche: statement.read::<String, _>("schedule").unwrap(),
            pric: statement.read::<String, _>("price").unwrap(),
        };
        museums.push(temp_sctruct)
    }

    museums
}