Backend Database - Mardens-Inc/Pricing-App GitHub Wiki

use database_common_lib::database_connection::{DatabaseConnectionData, create_pool};
use sqlx::MySqlPool;

// Create pool from configuration
let data = DatabaseConnectionData::from_file("dev-server.json")?;
let pool = create_pool(&data).await?;

// Pool is shared across all workers via web::Data
HttpServer::new(move || {
    App::new()
        .app_data(web::Data::new(pool.clone()))
})

MySqlPoolOptions::new()
    .max_connections(5)  // Adjust based on load
    .connect(&connection_string)
    .await?

// inventory_db.rs - Creating inventory table
pub async fn create(id: u64, headers: Vec<String>, data: &DatabaseConnectionData) -> Result<()> {
    let pool = create_pool(data).await?;

    let mut query = format!(
        "CREATE TABLE IF NOT EXISTS `{}` (id INT AUTO_INCREMENT PRIMARY KEY, ",
        id  // Table name is the location ID
    );

    for header in headers {
        query.push_str(&format!("`{}` TEXT DEFAULT NULL, ", header));
    }

    query.push_str(
        "`date` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, \
         `last_modified_date` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL);"
    );

    pool.execute(query.as_str()).await?;
    Ok(())
}

use serde_hash::hashids::{encode_single, decode_single};

// Configuration (in serde_hash library)
// Salt: "pricing-app"
// Min length: 16 characters
// Alphabet: URL-safe characters

// Encoding (database → API)
let hashed_id = encode_single(123);
// Result: "x7J8kLm9N2pQr4Tv"

// Decoding (API → database)
let db_id = decode_single("x7J8kLm9N2pQr4Tv")?;
// Result: 123

// Endpoint receives hashed ID
#[get("/api/list/{id}")]
pub async fn get_location(
    pool: web::Data<MySqlPool>,
    id: web::Path<String>,  // Hashed ID string
) -> Result<impl Responder> {
    // 1. Decode at entry point
    let db_id = decode_single(&id)?;

    // 2. Use raw ID for database operations
    let location = list_db::get_by_id(&pool, db_id).await?;

    // 3. Response struct re-encodes ID via serde
    Ok(web::Json(location))
}

#[derive(Serialize)]
pub struct Location {
    #[serde(serialize_with = "serde_hash::hashids::serialize")]
    pub id: u64,  // Automatically encoded to string in JSON

    pub name: String,
    pub location: String,
}

// JSON response: {"id": "x7J8kLm9N2pQr4Tv", "name": "...", "location": "..."}

sqlx::query("INSERT INTO locations (name, location, po, vendor, department, image) VALUES (?, ?, ?, ?, ?, ?)")
    .bind(&name)
    .bind(&location)
    .bind(&po)
    .bind(&vendor)
    .bind(&department)
    .bind(&image)
    .execute(&pool)
    .await?;

let location = sqlx::query_as::<_, Location>(
    "SELECT * FROM locations WHERE id = ? LIMIT 1"
)
.bind(id)
.fetch_one(&pool)
.await?;

let locations = sqlx::query_as::<_, Location>(
    "SELECT * FROM locations ORDER BY post_date DESC"
)
.fetch_all(&pool)
.await?;

sqlx::query("UPDATE locations SET name = ?, location = ? WHERE id = ?")
    .bind(&name)
    .bind(&location)
    .bind(id)
    .execute(&pool)
    .await?;

sqlx::query("DELETE FROM locations WHERE id = ?")
    .bind(id)
    .execute(&pool)
    .await?;

// Building dynamic query
let mut query = format!("SELECT * FROM `{}`", location_id);
let mut conditions = Vec::new();
let mut params = Vec::new();

// Add search conditions
if let Some(search_term) = search {
    for column in columns {
        conditions.push(format!("`{}` LIKE ?", column));
        params.push(format!("%{}%", search_term));
    }
}

if !conditions.is_empty() {
    query.push_str(" WHERE ");
    query.push_str(&conditions.join(" OR "));
}

// Bind parameters dynamically
let mut sql_query = sqlx::query(&query);
for param in &params {
    sql_query = sql_query.bind(param);
}

let rows = sql_query.fetch_all(&pool).await?;

let mut tx = pool.begin().await?;

sqlx::query("INSERT INTO locations (...) VALUES (...)")
    .bind(...)
    .execute(&mut *tx)
    .await?;

sqlx::query("INSERT INTO history (...) VALUES (...)")
    .bind(...)
    .execute(&mut *tx)
    .await?;

tx.commit().await?;

use serde_json::Value;

// Storing JSON
let diff_json = serde_json::to_string(&diff)?;
sqlx::query("INSERT INTO history (diff) VALUES (?)")
    .bind(&diff_json)
    .execute(&pool)
    .await?;

// Reading JSON
let row = sqlx::query("SELECT diff FROM history WHERE id = ?")
    .bind(id)
    .fetch_one(&pool)
    .await?;

let diff: Value = row.try_get("diff")?;

// Called during server startup
pub async fn initialize(pool: &MySqlPool) -> Result<()> {
    pool.execute(
        r#"CREATE TABLE IF NOT EXISTS locations (
            ...
        )"#
    ).await?;

    // Safe migration: Add column if missing
    let _ = pool.execute(
        "ALTER TABLE locations ADD COLUMN IF NOT EXISTS visible BOOLEAN DEFAULT TRUE"
    ).await;

    Ok(())
}

// Always limit results when possible
sqlx::query("SELECT * FROM locations LIMIT 100")
    .fetch_all(&pool)
    .await?;

// For pagination, use LIMIT with OFFSET
sqlx::query("SELECT * FROM `{}` LIMIT ? OFFSET ?")
    .bind(limit)
    .bind(offset)
    .fetch_all(&pool)
    .await?;

// For large offsets, consider keyset pagination instead

// Specify only needed columns
sqlx::query("SELECT id, name, location FROM locations")
    .fetch_all(&pool)
    .await?;

// Adjust based on server capacity and load
MySqlPoolOptions::new()
    .max_connections(10)        // More for high traffic
    .min_connections(2)         // Keep connections warm
    .acquire_timeout(Duration::from_secs(5))
    .connect(&url)
    .await?;

use stopwatch::Stopwatch;

let sw = Stopwatch::start_new();
let result = query.fetch_all(&pool).await?;
let elapsed = sw.elapsed_ms();

if elapsed > 1000 {
    log::warn!("Slow query: {}ms - {}", elapsed, query_string);
}

inventory::inventory_db::initialize(&pool).await?;