Database CRUD - Update & Delete

1. Welcome and Icebreaker:

Have you ever accidentally deleted something important? How did you recover?

2. Outline the Session:

Agenda:

1. Concept Explanation: How to update and delete records using sqlx in Axum.
2. Live Coding: Implement PUT/PATCH and DELETE handlers for database operations.
3. Tasks: Practice by building your own update and delete endpoints.

Learning Outcomes:

• Implement update (PUT/PATCH) and delete (DELETE) operations with a database in Axum.
• Handle errors and return appropriate status codes.

Expectations: Try updating and deleting your own data from the database!

3. Concept Explanation:

Updating and deleting records are essential for maintaining data integrity. Use parameterized queries and handle errors gracefully. Return appropriate status codes (e.g., 404 for not found, 204 for success with no content).

4. Live Coding Demonstration:

Continue with the AppState and items table from the previous lesson. Register .route("/items/{id}", put(update_item).delete(delete_item)) before calling .with_state(state). This example uses runtime-checked queries to avoid requiring a live database during compilation.

use axum::{extract::{State, Path}, http::StatusCode, Json};
use serde::Deserialize;

#[derive(Deserialize)]
struct UpdateItem {
    name: String,
}

async fn update_item(State(state): State<AppState>, Path(id): Path<i32>, Json(payload): Json<UpdateItem>) -> Result<Json<Item>, StatusCode> {
    if payload.name.trim().is_empty() { return Err(StatusCode::BAD_REQUEST); }
    let item = sqlx::query_as::<_, Item>("UPDATE items SET name = $1 WHERE id = $2 RETURNING id, name")
        .bind(&payload.name)
        .bind(id)
        .fetch_optional(&state.db)
        .await
        .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?
        .ok_or(StatusCode::NOT_FOUND)?;
    Ok(Json(item))
}

async fn delete_item(State(state): State<AppState>, Path(id): Path<i32>) -> Result<StatusCode, StatusCode> {
    let result = sqlx::query("DELETE FROM items WHERE id = $1")
        .bind(id)
        .execute(&state.db)
        .await
        .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;
    if result.rows_affected() == 0 { return Err(StatusCode::NOT_FOUND); }
    Ok(StatusCode::NO_CONTENT)
}

Best Practices:

• Return 404 if the record does not exist.
• Use transactions for complex updates.

Common Pitfalls:

• Not checking if the update/delete affected any rows.
• Returning success when nothing was changed.

5. Tasks

• Add a PATCH endpoint for partial updates.
• Return a 204 No Content status for successful deletes.
• Handle errors and return JSON error responses.