Rust Axum chevron_right Database CRUD - Create & Read

Database CRUD - Create & Read

calendar_today schedule 2 min read

1. Welcome and Icebreaker:

What’s the most useful data you’ve ever stored in a database?

2. Outline the Session:

Agenda:

  1. Concept Explanation: How to create and read records using sqlx in Axum.
  2. Live Coding: Implement POST and GET handlers for database operations.
  3. Tasks: Practice by building your own CRUD endpoints.

Learning Outcomes:

  • Implement create (POST) and read (GET) operations with a database in Axum.
  • Handle database errors gracefully.

Expectations: Try creating and reading your own data from the database!

3. Concept Explanation:

CRUD stands for Create, Read, Update, Delete. In this lesson, you’ll focus on creating new records (POST) and reading them (GET) using sqlx in Axum. Use serde for data serialization and deserialization.

4. Live Coding Demonstration:

Use the PostgreSQL setup from the previous lesson. First run CREATE TABLE items (id SERIAL PRIMARY KEY, name TEXT NOT NULL);. To avoid requiring a live database at compile time, these examples use sqlx::query/query_as with FromRow; query!/query_as! require DATABASE_URL and a matching schema at build time (or prepared offline metadata).

use axum::{extract::State, http::StatusCode, Json, Router, routing::{get, post}};
use serde::{Deserialize, Serialize};
use sqlx::{FromRow, PgPool};

#[derive(Serialize, FromRow)]
struct Item {
    id: i32,
    name: String,
}

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

async fn create_item(State(state): State<AppState>, Json(item): Json<NewItem>) -> Result<(StatusCode, Json<Item>), StatusCode> {
    if item.name.trim().is_empty() { return Err(StatusCode::BAD_REQUEST); }
    let created = sqlx::query_as::<_, Item>("INSERT INTO items (name) VALUES ($1) RETURNING id, name")
        .bind(&item.name)
        .fetch_one(&state.db)
        .await
        .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;
    Ok((StatusCode::CREATED, Json(created)))
}

async fn get_items(State(state): State<AppState>) -> Result<Json<Vec<Item>>, StatusCode> {
    let items = sqlx::query_as::<_, Item>("SELECT id, name FROM items ORDER BY id")
        .fetch_all(&state.db)
        .await
        .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;
    Ok(Json(items))
}

#[derive(Clone)]
struct AppState { db: PgPool }

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let db = PgPool::connect(&std::env::var("DATABASE_URL")?).await?;
    let app = Router::new()
        .route("/items", post(create_item).get(get_items))
        .with_state(AppState { db });
    let listener = tokio::net::TcpListener::bind("127.0.0.1:3000").await?;
    axum::serve(listener, app).await?;
    Ok(())
}

Best Practices:

  • Use parameterized queries to prevent SQL injection.
  • Handle errors and return appropriate responses.

Common Pitfalls:

  • Not handling database errors.
  • Forgetting to serialize/deserialize structs.

5. Tasks

  • Add a GET endpoint to fetch a single item by ID.
  • Return a JSON error if the item is not found.
  • Add input validation for the create endpoint.