Lesson 11: SQLx & Database Integration

Rust: From Basics to Advanced

Part 3: Async Rust & Web Development

xdev.asia

1. SQLx Setup

[dependencies]
sqlx = { version = "0.7", features = ["runtime-tokio", "postgres", "uuid", "chrono"] }

# Cài SQLx CLI
cargo install sqlx-cli --no-default-features --features postgres

# Tạo database
sqlx database create

# Tạo migration
sqlx migrate add create_products_table

-- migrations/001_create_products_table.sql
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

2. CRUD Operations

use sqlx::{PgPool, FromRow};
use uuid::Uuid;
use chrono::{DateTime, Utc};

#[derive(Debug, FromRow, Serialize)]
struct Product {
    id: Uuid,
    name: String,
    price: rust_decimal::Decimal,
    description: Option<String>,
    created_at: DateTime<Utc>,
}

struct ProductRepository {
    pool: PgPool,
}

impl ProductRepository {
    // Compile-time verified query
    async fn find_all(&self, limit: i64, offset: i64) -> Result<Vec<Product>, sqlx::Error> {
        sqlx::query_as!(
            Product,
            "SELECT id, name, price, description, created_at FROM products ORDER BY created_at DESC LIMIT $1 OFFSET $2",
            limit, offset
        )
        .fetch_all(&self.pool)
        .await
    }

    async fn find_by_id(&self, id: Uuid) -> Result<Option<Product>, sqlx::Error> {
        sqlx::query_as!(Product, "SELECT * FROM products WHERE id = $1", id)
            .fetch_optional(&self.pool)
            .await
    }

    async fn create(&self, name: &str, price: Decimal, description: Option<&str>) -> Result<Product, sqlx::Error> {
        sqlx::query_as!(
            Product,
            "INSERT INTO products (name, price, description) VALUES ($1, $2, $3) RETURNING *",
            name, price, description
        )
        .fetch_one(&self.pool)
        .await
    }

    async fn delete(&self, id: Uuid) -> Result<bool, sqlx::Error> {
        let result = sqlx::query!("DELETE FROM products WHERE id = $1", id)
            .execute(&self.pool)
            .await?;
        Ok(result.rows_affected() > 0)
    }
}

3. Transactions

async fn transfer_stock(
    pool: &PgPool,
    from_id: Uuid,
    to_id: Uuid,
    quantity: i32,
) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;

    sqlx::query!("UPDATE inventory SET stock = stock - $1 WHERE product_id = $2", quantity, from_id)
        .execute(&mut *tx).await?;

    sqlx::query!("UPDATE inventory SET stock = stock + $1 WHERE product_id = $2", quantity, to_id)
        .execute(&mut *tx).await?;

    tx.commit().await?;
    Ok(())
}

4. Connection Pool

use sqlx::postgres::PgPoolOptions;

let pool = PgPoolOptions::new()
    .max_connections(20)
    .min_connections(5)
    .acquire_timeout(Duration::from_secs(5))
    .idle_timeout(Duration::from_secs(600))
    .connect(&database_url)
    .await?;

// Run migrations
sqlx::migrate!("./migrations").run(&pool).await?;

Next article: Authentication & Authorization.