RUST: Insert postgres record with sqlx

use sqlx::{postgres::PgPool, Result};

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

async fn insert_substance(
    pool: &PgPool,
    cas_number: String,
    ec_number: String,
    ec_name: String,
) -> Result<()> {
    sqlx::query!(
        "INSERT INTO substances (cas_number, ec_number, ec_name) VALUES ($1, $2, $3)",
        cas_number,
        ec_number,
        ec_name
    )
    .execute(pool)
    .await?;

    Ok(())
}

#[tokio::main]
async fn main() -> Result<()> {
    // Replace with your PostgreSQL connection string.
    let database_url = "postgres://user:password@host:port/database";
    let pool = PgPool::connect(database_url).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(())
}