Bài 51: PostgreSQL Setup Với sqlx

Sau bài học, bạn sẽ:

• Cài PostgreSQL 17 qua docker compose cross-platform (Mac/Linux/Windows).
• Cài sqlx-cli cho migration tooling.
• Hiểu format connection string postgres://user:pass@host:port/db.
• Setup .env file cho DATABASE_URL (sqlx compile-time check yêu cầu).
• Init crate mới shop-db — workspace member thứ 3.
• Hiểu khác biệt sqlx::query! vs sqlx::query_as! vs runtime query().
• Refactor product_service Shop API từ in-memory Vec<Product> sang DB query.

Shop API dùng Docker thay vì cài PostgreSQL native vì 4 lý do thực tế:

• Cross-platform consistent — image y hệt nhau trên macOS, Linux, Windows; loại bỏ class lỗi "works on my machine".
• Version lock chính xác — pin tag postgres:17-alpine, mọi máy dev cùng minor version.
• Cleanup dễ — drop container + volume xóa sạch, không rác trong system Postgres.
• Multi-project isolation — mỗi project DB riêng container riêng port riêng, không xung đột.

Tạo file docker-compose.yml ở root project:

# File: docker-compose.yml (root project)
services:
  postgres:
    image: postgres:17-alpine
    container_name: shop_postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: shop
      POSTGRES_PASSWORD: shop_dev_password
      POSTGRES_DB: shop_dev
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - postgres_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U shop -d shop_dev"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  postgres_data:
    driver: local

Khởi chạy container:

docker compose up -d postgres
docker compose ps                  # status
docker compose logs -f postgres    # follow log

Test connection bằng psql bên trong container:

docker compose exec postgres psql -U shop -d shop_dev -c "SELECT version();"
# PostgreSQL 17.x on x86_64-pc-linux-gnu, ...

Dừng container hoặc cleanup hoàn toàn:

docker compose down       # dừng container, giữ volume
docker compose down -v    # dừng + xóa volume (MẤT toàn bộ data)

Lock decision Shop API B51 vĩnh viễn:

• Image: postgres:17-alpine (size nhỏ ~80MB, security update nhanh).
• User / DB name: shop / shop_dev (dev local), production sẽ tách credential riêng qua secret manager (G19).
• Port: 5432 (default Postgres, dev expose ra host).
• Volume named postgres_data persist data qua restart container.
• healthcheck pg_isready CI/CD ready — docker compose up chờ healthy trước khi expose port.

sqlx-cli + macro sqlx::query!/query_as! đọc DATABASE_URL từ environment để kết nối DB lúc compile + introspect schema. Tạo file .env ở root project:

# File: .env (root project, LOCAL ONLY — KHÔNG commit)
DATABASE_URL=postgres://shop:shop_dev_password@localhost:5432/shop_dev
RUST_LOG=shop_api=debug,sqlx=warn,tower_http=debug

Format connection string chuẩn:

postgres://USER:PASSWORD@HOST:PORT/DATABASE?param1=value1&param2=value2

• Scheme postgres:// hoặc postgresql:// (alias, sqlx accept cả hai).
• URL-encode password nếu có ký tự đặc biệt (@ → %40, : → %3A, / → %2F).
• Query params: sslmode=require ép TLS, application_name=shop_api hiển thị trong pg_stat_activity, connect_timeout=10 giây.

Thêm .env vào .gitignore tránh leak credential:

# File: .gitignore (extend B51)
/target
.env
.env.local
.env.*.local

Tạo .env.example commit vào git làm template cho dev mới onboard:

# File: .env.example (commit vào git)
DATABASE_URL=postgres://shop:CHANGE_ME@localhost:5432/shop_dev
RUST_LOG=shop_api=debug,sqlx=warn,tower_http=debug

Lock decision Shop API B51: .env LOCAL ONLY, KHÔNG commit; .env.example template commit. Production set env qua orchestrator (Docker secrets, Kubernetes ConfigMap + Secret, fly.io secret, Railway variable) — KHÔNG dùng file .env trong production (lock B10 continued).

sqlx-cli là binary command-line tách rời crate sqlx, cung cấp 3 nhóm lệnh:

• sqlx migrate — quản lý migration (add, run, revert, info).
• sqlx database — create/drop database qua connection string.
• sqlx prepare — generate offline query cache .sqlx/ folder cho CI/CD build không cần DB sống.

Install qua cargo install:

cargo install sqlx-cli --no-default-features --features postgres,rustls

Hai flag quan trọng:

• --no-default-features bỏ MySQL + SQLite driver (Shop API chỉ dùng Postgres, tránh tốn dung lượng + build time).
• --features rustls dùng pure-Rust TLS thay native-tls — cross-platform không cần OpenSSL system library.

Verify cài đặt:

sqlx --version
# sqlx-cli 0.8.x

Test database create (noop nếu container đã tạo DB qua POSTGRES_DB environment ở bước 2):

sqlx database create
# database "shop_dev" already exists, skipping

Lệnh đọc DATABASE_URL từ .env tự động (sqlx-cli tích hợp dotenv loader). Nếu không có .env, export biến thủ công: export DATABASE_URL=postgres://....

shop-db là workspace member thứ 3 (sau shop-api binary + shop-common lib từ B10), đóng vai trò PostgreSQL adapter — đóng gói pool config + repository function + migration folder. Tạo crate mới qua cargo:

cargo new --lib crates/shop-db

Cập nhật workspace root Cargo.toml thêm member + dep mới:

# File: Cargo.toml (workspace root, extend B51)
[workspace]
members = ["crates/shop-api", "crates/shop-common", "crates/shop-db"]
resolver = "3"

[workspace.dependencies]
# ... existing deps B10/B19/B41
sqlx = { version = "0.8", features = [
    "postgres",
    "runtime-tokio",
    "tls-rustls",
    "macros",
    "chrono",
    "uuid",
    "rust_decimal",
] }
dotenvy = "0.15"

Tạo crates/shop-db/Cargo.toml:

# File: crates/shop-db/Cargo.toml
[package]
name = "shop-db"
version = "0.1.0"
edition = "2024"

[dependencies]
sqlx = { workspace = true }
tokio = { workspace = true }
anyhow = { workspace = true }
thiserror = { workspace = true }
chrono = { workspace = true }
rust_decimal = { workspace = true }
shop-common = { path = "../shop-common" }

Tạo file entry crates/shop-db/src/lib.rs:

// File: crates/shop-db/src/lib.rs
pub mod pool;
pub mod products;

pub use pool::create_pool;

Tạo crates/shop-db/src/pool.rs chứa factory function tạo connection pool:

// File: crates/shop-db/src/pool.rs
use sqlx::postgres::{PgPool, PgPoolOptions};
use std::time::Duration;

pub async fn create_pool(database_url: &str) -> Result<PgPool, sqlx::Error> {
    PgPoolOptions::new()
        .max_connections(20)
        .min_connections(2)
        .acquire_timeout(Duration::from_secs(5))
        .idle_timeout(Duration::from_secs(60))
        .test_before_acquire(true)
        .connect(database_url)
        .await
}

Giải nghĩa từng config field:

• max_connections(20) cap tối đa connection mở đồng thời. Postgres default max_connections = 100 chia per service — Shop API lấy 20, chừa cho worker (G21), CLI (G29), admin tool kết nối song song.
• min_connections(2) keep warm 2 connection idle, tránh cost handshake lần đầu request → throughput tăng ~15%.
• acquire_timeout(5s) chờ tối đa 5 giây nếu pool full → trả error PoolTimedOut; cao hơn dễ ăn cascade timeout HTTP, thấp hơn flap khi spike.
• idle_timeout(60s) release connection idle quá 60s → tiết kiệm slot Postgres, vẫn giữ min_connections 2 warm.
• test_before_acquire(true) ping (SELECT 1) trước khi cấp connection → tránh stale connection (NAT timeout, Postgres restart). Cost ~1ms/request, đáng đổi cho reliability dev local.

Pool config sẽ tune chi tiết theo workload thật ở B57 (Pool Tuning Sâu) — B51 dùng config sweet-spot startup-grade.

Migration là SQL script versioned đặt trong folder migrations/, sqlx track version đã apply qua bảng nội bộ _sqlx_migrations. Tạo migration đầu tiên qua sqlx migrate add:

sqlx migrate add --source crates/shop-db/migrations create_products
# Creating crates/shop-db/migrations/20260615120000_create_products.sql

Flag --source chỉ folder migration (lock Shop API: crates/shop-db/migrations/). Filename prefix là timestamp YYYYMMDDHHMMSS sqlx tự sinh — sort theo thứ tự chronological.

Edit file SQL vừa tạo:

-- crates/shop-db/migrations/20260615120000_create_products.sql
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    price NUMERIC(15, 2) NOT NULL CHECK (price > 0),
    stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX products_slug_idx ON products(slug);
CREATE INDEX products_created_at_idx ON products(created_at DESC);

Apply migration:

sqlx migrate run --source crates/shop-db/migrations
# Applied 20260615120000/migrate create_products (1.2ms)

Verify bảng đã tạo trong psql:

docker compose exec postgres psql -U shop -d shop_dev -c "\d products"

Type mapping lock B51 vĩnh viễn cho Shop API:

• Rust ProductId(u64) ↔ Postgres BIGSERIAL (id auto-increment 64-bit). sqlx cast i64 ↔ u64 qua newtype implementation B44.
• Rust Money(Decimal) ↔ Postgres NUMERIC(15, 2) — scale 2 cho VND (2 chữ số thập phân), precision 15 max value 999_999_999_999.99 (~1000 tỷ VND, dư cho mọi giá Shop API).
• Rust String ↔ Postgres TEXT — performance trong Postgres là TEXT = VARCHAR = CHAR(N) identical, KHÔNG dùng VARCHAR(N) vì validate length ở app level qua validator crate (lock B41).
• Rust u32 stock ↔ Postgres INT 32-bit signed (Shop API cast safe: stock luôn không âm + max 1 triệu lock B42, fit trong i32 range).
• Rust chrono::DateTime<Utc> ↔ Postgres TIMESTAMPTZ (timestamp with time zone, store UTC + offset).

Lock decision: KHÔNG dùng VARCHAR(N) trong schema — Postgres TEXT không limit length, validate ở app level qua validator attribute #[validate(length(min=3, max=200))] (B41 lock). Lý do: thay limit qua migration tốn DB lock + downtime, sửa attribute Rust chỉ deploy code mới.

sqlx cung cấp 3 cách viết query, mỗi cách có trade-off riêng giữa safety và flexibility:

• sqlx::query! macro — compile-time check SQL syntax + type binding + column type. KHÔNG return struct (trả anonymous record tuple-like access qua field name).
• sqlx::query_as! macro — compile-time check + return struct user-defined (struct field name PHẢI match column name).
• sqlx::query() runtime function — KHÔNG compile-time check, flexible cho dynamic SQL (filter user input, sort by user input, query builder).

Bảng so sánh nhanh:

Function       | Compile check | Return type        | Use case
query!         | YES           | Anonymous record   | INSERT/UPDATE/DELETE
query_as!      | YES           | Custom struct      | SELECT mapped struct
query          | NO            | PgRow (manual map) | Dynamic SQL (search builder)

Compile-time check yêu cầu sqlx kết nối DB sống lúc build qua DATABASE_URL ENV, hoặc đọc offline cache .sqlx/ folder generate bởi sqlx prepare (CI/CD pattern). Macro query!/query_as! introspect schema → type column → so sánh type Rust → compile error nếu mismatch.

Lock decision Shop API B51 vĩnh viễn:

• Static SQL (CRUD đa số) → query! / query_as! — safety tối đa, compile catch bug schema change.
• Dynamic SQL (search filter, sort by user input từ query string) → query() runtime kết hợp QueryBuilder (B59 deep dive G10).
• CI/CD setup sqlx prepare generate .sqlx/ cache commit vào git, CI build không cần DB sống (deep B53).

Tạo crates/shop-db/src/products.rs chứa struct ProductRow map DB row + 4 CRUD function:

// File: crates/shop-db/src/products.rs
use sqlx::PgPool;
use rust_decimal::Decimal;
use chrono::{DateTime, Utc};

pub struct ProductRow {
    pub id: i64,
    pub name: String,
    pub slug: String,
    pub price: Decimal,
    pub stock: i32,
    pub description: Option<String>,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

pub async fn list_products(
    pool: &PgPool,
    limit: i64,
    offset: i64,
) -> Result<Vec<ProductRow>, sqlx::Error> {
    sqlx::query_as!(
        ProductRow,
        r#"
        SELECT id, name, slug, price, stock, description, created_at, updated_at
        FROM products
        ORDER BY created_at DESC
        LIMIT $1 OFFSET $2
        "#,
        limit,
        offset
    )
    .fetch_all(pool)
    .await
}

pub async fn count_products(pool: &PgPool) -> Result<i64, sqlx::Error> {
    let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM products")
        .fetch_one(pool)
        .await?;
    Ok(row.0)
}

pub async fn create_product(
    pool: &PgPool,
    name: &str,
    slug: &str,
    price: Decimal,
    stock: i32,
    description: Option<&str>,
) -> Result<ProductRow, sqlx::Error> {
    sqlx::query_as!(
        ProductRow,
        r#"
        INSERT INTO products (name, slug, price, stock, description)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING id, name, slug, price, stock, description, created_at, updated_at
        "#,
        name,
        slug,
        price,
        stock,
        description
    )
    .fetch_one(pool)
    .await
}

pub async fn find_by_slug(
    pool: &PgPool,
    slug: &str,
) -> Result<Option<ProductRow>, sqlx::Error> {
    sqlx::query_as!(
        ProductRow,
        r#"
        SELECT id, name, slug, price, stock, description, created_at, updated_at
        FROM products
        WHERE slug = $1
        "#,
        slug
    )
    .fetch_optional(pool)
    .await
}

4 function pattern chuẩn cho mọi entity Shop API tương lai (Order, Cart, User, Category — G7+):

• list_xxx(pool, limit, offset) paginated SELECT với ORDER BY created_at DESC.
• count_xxx(pool) SELECT COUNT(*) cho pagination metadata.
• create_xxx(pool, ...) INSERT + RETURNING * tiết kiệm round-trip.
• find_by_xxx(pool, key) SELECT WHERE với fetch_optional trả Option<T>.

Update crates/shop-api/src/state.rs thêm field db: PgPool:

// File: crates/shop-api/src/state.rs (extend B51)
use sqlx::PgPool;
use std::sync::Arc;
use shop_common::config::AppConfig;

#[derive(Clone)]
pub struct AppState {
    pub db: PgPool,
    pub config: Arc<AppConfig>,
}

impl AppState {
    pub fn new(db: PgPool, config: AppConfig) -> Self {
        Self {
            db,
            config: Arc::new(config),
        }
    }
}

PgPool nội bộ là Arc<SharedPool> — clone-cheap (atomic increment) — share OK cross handler qua State<AppState> extractor.

Update crates/shop-api/src/main.rs wire pool + apply migration on startup:

// File: crates/shop-api/src/main.rs (extend B51)
use shop_db::create_pool;
use shop_common::config::AppConfig;

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    dotenvy::dotenv().ok();
    tracing_subscriber::fmt::init();

    let database_url = std::env::var("DATABASE_URL")
        .expect("DATABASE_URL must be set in .env");

    let db = create_pool(&database_url).await?;
    tracing::info!("connected to PostgreSQL");

    // Apply migrations on startup (dev only — production tách lệnh riêng B52)
    sqlx::migrate!("../shop-db/migrations").run(&db).await?;
    tracing::info!("migrations applied");

    let config = AppConfig::from_env()?;
    let state = AppState::new(db, config);

    let app = build_router(state);
    let listener = tokio::net::TcpListener::bind("0.0.0.0:3000").await?;
    tracing::info!("listening on {}", listener.local_addr()?);
    axum::serve(listener, app).await?;

    Ok(())
}

Update crates/shop-api/Cargo.toml thêm dep:

# File: crates/shop-api/Cargo.toml (extend B51)
[dependencies]
# ... existing deps
shop-db = { path = "../shop-db" }
sqlx = { workspace = true }
dotenvy = { workspace = true }

Handler list_products + create_product ở crates/shop-api/src/routes/products.rs sẽ refactor sang dùng shop_db::products::* ở B52 — B51 chỉ wire infrastructure pool + migration on startup, KHÔNG chạm logic handler.

Khởi động container + chạy app:

docker compose up -d postgres
cargo run -p shop-api
# connected to PostgreSQL
# migrations applied
# listening on 0.0.0.0:3000

Insert product test qua psql cho data sống:

docker compose exec postgres psql -U shop -d shop_dev -c \
  "INSERT INTO products (name, slug, price, stock) VALUES ('iPhone 15', 'iphone-15', 25000000.00, 10);"
# INSERT 0 1

Verify row trong DB:

-- chạy trong psql session
SELECT id, name, slug, price, stock, created_at FROM products;
--  id |   name   |   slug   |    price    | stock |          created_at
-- ----+----------+----------+-------------+-------+-------------------------------
--   1 | iPhone 15| iphone-15| 25000000.00 |    10 | 2026-06-15 12:30:15.234+00

Verify HTTP endpoint (handler list_products vẫn skeleton vec![] in-memory ở B51 — refactor sang shop_db ở B52):

curl http://localhost:3000/api/v1/products
# {"items":[],"total":0,"page":1,"size":20,"hasNext":false}
# Note: B51 mới setup DB pool — handler chuyển sang shop_db ở B52

Verify pool log trong RUST_LOG=sqlx=info:

RUST_LOG=sqlx=info,shop_api=debug cargo run -p shop-api
# sqlx::pool: pool established min=2 max=20
# shop_api: listening on 0.0.0.0:3000

Suggested commit: B51: PostgreSQL 17 docker-compose + sqlx-cli + shop-db crate init + products migration.

• PostgreSQL 17 alpine qua Docker Compose — cross-platform, version lock, cleanup dễ, multi-project isolation.
• shop / shop_dev / port 5432 lock connection dev local; production tách credential qua secret manager (G19).
• .env local only (.gitignore), .env.example template commit cho onboarding.
• sqlx-cli install với feature postgres,rustls — skip MySQL/SQLite, skip OpenSSL native.
• Crate shop-db init lần đầu — workspace member thứ 3 (sau shop-api, shop-common).
• Pool config: max(20), min(2), acquire_timeout(5s), idle_timeout(60s), test_before_acquire(true).
• First migration create_products: BIGSERIAL + NUMERIC(15, 2) + TEXT + INT + TIMESTAMPTZ + 2 index.
• Type mapping lock B51: Money(Decimal) ↔ NUMERIC(15, 2), ProductId(u64) ↔ BIGSERIAL, Rust String ↔ TEXT (KHÔNG VARCHAR).
• 3 cách query: query! / query_as! (compile-time check), query() runtime cho dynamic SQL.
• AppState.db: PgPool — clone-cheap (Arc internal) share OK cross handler.
• Migration on startup dev only — production tách lệnh riêng (B52 deep).
• File path lock: crates/shop-db/{src/lib.rs, src/pool.rs, src/products.rs, migrations/}.

Tự trả lời, đáp án ở cuối:

1. Tại sao Shop API chọn Docker Compose thay vì cài PostgreSQL native? Liệt kê 3 lý do.
2. Connection string postgres://... có những phần nào? Cho ví dụ kèm SSL param + application_name.
3. Pool config max(20) + min(2) — tại sao không để max(100)? Postgres default max_connections là bao nhiêu?
4. sqlx::query! macro vs sqlx::query() runtime — khi nào chọn cái nào? Trade-off compile-check vs flexibility.
5. Tại sao map Money sang NUMERIC(15, 2) thay BIGINT cents? Trade-off chính xác vs performance + storage.

Bài 52: sqlx Migration System Sâu — chi tiết sqlx migration: up-only vs reversible, naming convention timestamp, embed migration into binary với migrate!() macro, transaction-wrapped migration, rollback strategy production, refactor handler product_service Shop API hoàn chỉnh dùng shop_db.