RUST: Insert sqlite record with sqlx

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

#[derive(Debug)]
struct Substance {
    cas_number: String,
    ec_number: String,
    ec_name: String,
}

async fn insert_substance(
    pool: &SqlitePool,
    cas_number: String,
    ec_number: String,
    ec_name: String,
) -> Result<()> {
    // Execute an SQL INSERT statement.
    sqlx::query!(
        "INSERT INTO substances (cas_number, ec_number, ec_name) VALUES (?, ?, ?)",
        cas_number,
        ec_number,
        ec_name
    )
    .execute(pool) // Execute the query against the provided SQLite pool.
    .await?; // Await the result of the asynchronous operation and handle potential errors.

    Ok(()) // Return Ok if the insertion was successful.
}

#[tokio::main]
async fn main() -> Result<()> {
    // Connect to an in-memory SQLite database for demonstration. Replace with your database path.
    let pool = SqlitePool::connect("sqlite::memory:").await?;

    // Create the 'substances' table if it doesn't exist.
    sqlx::query!(
        "CREATE TABLE IF NOT EXISTS substances (
            cas_number TEXT PRIMARY KEY,
            ec_number TEXT,
            ec_name TEXT
        )"
    )
    .execute(&pool)
    .await?;

    // Example usage: insert a new substance record.
    insert_substance(&pool, "123-45-6".to_string(), "204-628-9".to_string(), "Ethanol".to_string()).await?;

    // Example usage: insert another new substance record.
    insert_substance(&pool, "75-09-2".to_string(), "200-838-9".to_string(), "Dichloromethane".to_string()).await?;

    // Verify the insertion by querying the database.
    let substances: Vec<Substance> = sqlx::query_as!(
        Substance,
        "SELECT cas_number, ec_number, ec_name FROM substances"
    )
    .fetch_all(&pool)
    .await?;

    println!("Substances in the database: {:?}", substances);

    Ok(())
}