Rust dan Database: SQLx, PostgreSQL, dan SQLite

Di artikel sebelumnya kita sudah membangun web API dengan Axum dan menyimpan data di memory. Sekarang saatnya menggunakan database yang sesungguhnya.

Kita akan menggunakan SQLx — library database async untuk Rust yang paling populer. SQLx mendukung PostgreSQL, MySQL, SQLite, dan MSSQL.

Kenapa SQLx?

Library	Async	Compile-time Check	Populer
SQLx	Ya	Ya (query!)	Sangat
Diesel	Tidak	Ya (ORM)	Cukup
SeaORM	Ya	Ya (ORM)	Berkembang
rusqlite	Tidak	Tidak	SQLite only

Keunggulan SQLx:

• Fully async dibangun di atas Tokio
• Compile-time query checking — error SQL terdeteksi saat compile
• No ORM — tulis SQL langsung, lebih fleksibel
• Lightweight — tidak banyak magic

Setup dengan SQLite

SQLite cocok untuk development dan aplikasi kecil. Tidak perlu install database server.

Tambahkan di Cargo.toml:

toml

toml[dependencies]
axum = "0.8"
tokio = { version = "1", features = ["full"] }
serde = { version = "1", features = ["derive"] }
serde_json = "1"
sqlx = { version = "0.8", features = ["runtime-tokio", "sqlite"] }
uuid = { version = "1", features = ["v4"] }
chrono = { version = "0.4", features = ["serde"] }

Koneksi ke Database

rust

rustuse sqlx::sqlite::SqlitePoolOptions;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new()
        .max_connections(5)
        .connect("sqlite:app.db?mode=rwc")
        .await?;

    println!("Connected to SQLite!");

    // Buat tabel
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS todos (
            id TEXT PRIMARY KEY,
            title TEXT NOT NULL,
            completed BOOLEAN NOT NULL DEFAULT 0,
            created_at TEXT NOT NULL
        )"
    )
    .execute(&pool)
    .await?;

    println!("Table 'todos' ready!");

    Ok(())
}

Penjelasan:

• SqlitePoolOptions::new() membuat connection pool
• max_connections(5) batasi maksimal 5 koneksi
• connect("sqlite:app.db?mode=rwc") koneksi ke file SQLite, buat jika belum ada
• query() menjalankan SQL
• execute() mengeksekusi query yang tidak mengembalikan data

CRUD Operations

Insert

rust

rustuse sqlx::FromRow;
use serde::{Serialize};

#[derive(Debug, FromRow, Serialize)]
struct Todo {
    id: String,
    title: String,
    completed: bool,
    created_at: String,
}

async fn create_todo(
    pool: &sqlx::SqlitePool,
    title: &str,
) -> Result<Todo, sqlx::Error> {
    let id = uuid::Uuid::new_v4().to_string();
    let created_at = chrono::Utc::now().to_rfc3339();

    sqlx::query_as::<_, Todo>(
        "INSERT INTO todos (id, title, completed, created_at)
         VALUES (?, ?, 0, ?)
         RETURNING *"
    )
    .bind(&id)
    .bind(title)
    .bind(&created_at)
    .fetch_one(pool)
    .await
}

Penjelasan:

• FromRow derive otomatis mapping kolom ke struct field
• query_as::<_, Todo> mapping hasil query ke struct Todo
• bind() mengikat parameter ke placeholder ?
• RETURNING * mengembalikan row yang baru di-insert
• fetch_one() ambil satu row

Select All

rust

rustasync fn list_todos(
    pool: &sqlx::SqlitePool,
) -> Result<Vec<Todo>, sqlx::Error> {
    sqlx::query_as::<_, Todo>("SELECT * FROM todos ORDER BY created_at DESC")
        .fetch_all(pool)
        .await
}

Select by ID

rust

rustasync fn get_todo(
    pool: &sqlx::SqlitePool,
    id: &str,
) -> Result<Option<Todo>, sqlx::Error> {
    sqlx::query_as::<_, Todo>("SELECT * FROM todos WHERE id = ?")
        .bind(id)
        .fetch_optional(pool)
        .await
}

fetch_optional() mengembalikan Option<Todo>. Jika tidak ditemukan, hasilnya None.

Update

rust

rustasync fn update_todo(
    pool: &sqlx::SqlitePool,
    id: &str,
    title: Option<&str>,
    completed: Option<bool>,
) -> Result<Option<Todo>, sqlx::Error> {
    // Cek apakah todo ada
    let existing = get_todo(pool, id).await?;
    if existing.is_none() {
        return Ok(None);
    }

    let todo = existing.unwrap();
    let new_title = title.unwrap_or(&todo.title);
    let new_completed = completed.unwrap_or(todo.completed);

    let updated = sqlx::query_as::<_, Todo>(
        "UPDATE todos SET title = ?, completed = ? WHERE id = ? RETURNING *"
    )
    .bind(new_title)
    .bind(new_completed)
    .bind(id)
    .fetch_one(pool)
    .await?;

    Ok(Some(updated))
}

Delete

rust

rustasync fn delete_todo(
    pool: &sqlx::SqlitePool,
    id: &str,
) -> Result<bool, sqlx::Error> {
    let result = sqlx::query("DELETE FROM todos WHERE id = ?")
        .bind(id)
        .execute(pool)
        .await?;

    Ok(result.rows_affected() > 0)
}

rows_affected() mengembalikan jumlah row yang terpengaruh. Jika 0, berarti tidak ada yang dihapus.

Full API dengan Axum + SQLite

Berikut contoh lengkap yang menggabungkan Axum dengan SQLite:

rust

rustuse axum::{
    extract::{Path, State},
    http::StatusCode,
    response::IntoResponse,
    routing::{get, put, delete},
    Json, Router,
};
use serde::{Deserialize, Serialize};
use sqlx::{FromRow, sqlite::SqlitePoolOptions};

#[derive(Clone)]
struct AppState {
    db: sqlx::SqlitePool,
}

#[derive(Debug, FromRow, Serialize, Clone)]
struct Todo {
    id: String,
    title: String,
    completed: bool,
    created_at: String,
}

#[derive(Deserialize)]
struct CreateTodo {
    title: String,
}

#[derive(Deserialize)]
struct UpdateTodo {
    title: Option<String>,
    completed: Option<bool>,
}

#[derive(Serialize)]
struct ErrorResponse {
    error: String,
}

enum AppError {
    NotFound,
    DatabaseError(String),
}

impl IntoResponse for AppError {
    fn into_response(self) -> axum::response::Response {
        let (status, message) = match self {
            AppError::NotFound => (
                StatusCode::NOT_FOUND,
                "Todo not found".to_string(),
            ),
            AppError::DatabaseError(msg) => (
                StatusCode::INTERNAL_SERVER_ERROR,
                msg,
            ),
        };
        (status, Json(ErrorResponse { error: message })).into_response()
    }
}

impl From<sqlx::Error> for AppError {
    fn from(err: sqlx::Error) -> Self {
        AppError::DatabaseError(err.to_string())
    }
}

async fn list_todos(
    State(state): State<AppState>,
) -> Result<Json<Vec<Todo>>, AppError> {
    let todos = sqlx::query_as::<_, Todo>(
        "SELECT * FROM todos ORDER BY created_at DESC"
    )
    .fetch_all(&state.db)
    .await?;

    Ok(Json(todos))
}

async fn get_todo(
    State(state): State<AppState>,
    Path(id): Path<String>,
) -> Result<Json<Todo>, AppError> {
    let todo = sqlx::query_as::<_, Todo>(
        "SELECT * FROM todos WHERE id = ?"
    )
    .bind(&id)
    .fetch_optional(&state.db)
    .await?
    .ok_or(AppError::NotFound)?;

    Ok(Json(todo))
}

async fn create_todo(
    State(state): State<AppState>,
    Json(payload): Json<CreateTodo>,
) -> Result<(StatusCode, Json<Todo>), AppError> {
    let id = uuid::Uuid::new_v4().to_string();
    let created_at = chrono::Utc::now().to_rfc3339();

    let todo = sqlx::query_as::<_, Todo>(
        "INSERT INTO todos (id, title, completed, created_at)
         VALUES (?, ?, 0, ?) RETURNING *"
    )
    .bind(&id)
    .bind(&payload.title)
    .bind(&created_at)
    .fetch_one(&state.db)
    .await?;

    Ok((StatusCode::CREATED, Json(todo)))
}

async fn update_todo(
    State(state): State<AppState>,
    Path(id): Path<String>,
    Json(payload): Json<UpdateTodo>,
) -> Result<Json<Todo>, AppError> {
    let existing = sqlx::query_as::<_, Todo>(
        "SELECT * FROM todos WHERE id = ?"
    )
    .bind(&id)
    .fetch_optional(&state.db)
    .await?
    .ok_or(AppError::NotFound)?;

    let new_title = payload.title.unwrap_or(existing.title);
    let new_completed = payload.completed.unwrap_or(existing.completed);

    let todo = sqlx::query_as::<_, Todo>(
        "UPDATE todos SET title = ?, completed = ? WHERE id = ? RETURNING *"
    )
    .bind(&new_title)
    .bind(new_completed)
    .bind(&id)
    .fetch_one(&state.db)
    .await?;

    Ok(Json(todo))
}

async fn delete_todo(
    State(state): State<AppState>,
    Path(id): Path<String>,
) -> Result<StatusCode, AppError> {
    let result = sqlx::query("DELETE FROM todos WHERE id = ?")
        .bind(&id)
        .execute(&state.db)
        .await?;

    if result.rows_affected() == 0 {
        return Err(AppError::NotFound);
    }

    Ok(StatusCode::NO_CONTENT)
}

async fn stats(
    State(state): State<AppState>,
) -> Result<Json<serde_json::Value>, AppError> {
    let row: (i64, i64) = sqlx::query_as(
        "SELECT
            COUNT(*) as total,
            SUM(CASE WHEN completed THEN 1 ELSE 0 END) as completed
         FROM todos"
    )
    .fetch_one(&state.db)
    .await?;

    Ok(Json(serde_json::json!({
        "total": row.0,
        "completed": row.1,
        "pending": row.0 - row.1,
    })))
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new()
        .max_connections(5)
        .connect("sqlite:app.db?mode=rwc")
        .await?;

    // Buat tabel
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS todos (
            id TEXT PRIMARY KEY,
            title TEXT NOT NULL,
            completed BOOLEAN NOT NULL DEFAULT 0,
            created_at TEXT NOT NULL
        )"
    )
    .execute(&pool)
    .await?;

    let state = AppState { db: pool };

    let app = Router::new()
        .route("/todos", get(list_todos).post(create_todo))
        .route("/todos/{id}", get(get_todo).put(update_todo).delete(delete_todo))
        .route("/todos/stats", get(stats))
        .with_state(state);

    let listener = tokio::net::TcpListener::bind("0.0.0.0:3000")
        .await
        .unwrap();

    println!("Todo API with SQLite running on http://localhost:3000");

    axum::serve(listener, app).await.unwrap();

    Ok(())
}

Setup dengan PostgreSQL

Untuk production, PostgreSQL lebih cocok. Ganti dependencies:

toml

toml[dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres"] }

Ganti koneksi:

rust

rustuse sqlx::postgres::PgPoolOptions;

let pool = PgPoolOptions::new()
    .max_connections(5)
    .connect("postgres://user:password@localhost/mydb")
    .await?;

Ganti SQL syntax:

sql

sql-- SQLite
INSERT INTO todos (id, title, completed, created_at) VALUES (?, ?, 0, ?) RETURNING *

-- PostgreSQL
INSERT INTO todos (id, title, completed, created_at) VALUES ($1, $2, false, $3) RETURNING *

Perbedaan utama:

• SQLite pakai ? untuk placeholder
• PostgreSQL pakai $1, $2, ... untuk placeholder
• SQLite pakai BOOLEAN (0/1), PostgreSQL pakai BOOLEAN (true/false)

SQLx Migrations

SQLx punya built-in migration system:

bash

bash# Install SQLx CLI
cargo install sqlx-cli

# Buat migration
sqlx migrate add create_todos_table

Ini membuat file di folder migrations/:

text

textmigrations/
├── 20260614000000_create_todos_table.sql

Isi file SQL:

sql

sqlCREATE TABLE IF NOT EXISTS todos (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    completed BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_todos_completed ON todos(completed);
CREATE INDEX idx_todos_created_at ON todos(created_at DESC);

Jalankan migration:

bash

bash# Set database URL
export DATABASE_URL="sqlite:app.db?mode=rwc"

# Jalankan migration
sqlx migrate run

# Revert migration terakhir
sqlx migrate revert

Gunakan di kode:

rust

rustlet pool = SqlitePoolOptions::new()
    .connect("sqlite:app.db?mode=rwc")
    .await?;

// Jalankan migration otomatis saat startup
sqlx::migrate!().run(&pool).await?;

Compile-time Query Checking

Fitur unik SQLx: cek query saat compile time!

rust

rust// Query dicek saat compile!
let todo = sqlx::query_as!(
    Todo,
    "SELECT * FROM todos WHERE id = ?",
    id
)
.fetch_optional(&pool)
    .await?;

Dengan query_as! macro, SQLx akan:

1. 1.Konek ke database
2. 2.Cek apakah query valid
3. 3.Cek apakah tipe data cocok
4. 4.Error jika ada masalah

Untuk menggunakan fitur ini, set environment variable:

bash

bashexport DATABASE_URL="sqlite:app.db?mode=rwc"
cargo build

Connection Pool

Connection pool mengelola koneksi database agar efisien:

rust

rustlet pool = PgPoolOptions::new()
    .max_connections(10)      // Maksimal 10 koneksi
    .min_connections(2)       // Minimal 2 koneksi idle
    .acquire_timeout(std::time::Duration::from_secs(3))  // Timeout 3 detik
    .idle_timeout(std::time::Duration::from_secs(300))   // Idle timeout 5 menit
    .max_lifetime(std::time::Duration::from_secs(1800))  // Max lifetime 30 menit
    .connect("postgres://user:pass@localhost/db")
    .await?;

Tips connection pool:

• max_connections = 2-3x jumlah CPU core
• Jangan buka koneksi baru setiap request, gunakan pool
• Pool otomatis membuka dan menutup koneksi

Transactions

Transactions memastikan beberapa query berjalan sebagai satu unit:

rust

rustasync fn transfer(
    pool: &sqlx::SqlitePool,
    from_id: &str,
    to_id: &str,
    amount: f64,
) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;

    // Kurangi saldo pengirim
    sqlx::query("UPDATE accounts SET balance = balance - ? WHERE id = ?")
        .bind(amount)
        .bind(from_id)
        .execute(&mut *tx)
        .await?;

    // Tambah saldo penerima
    sqlx::query("UPDATE accounts SET balance = balance + ? WHERE id = ?")
        .bind(amount)
        .bind(to_id)
        .execute(&mut *tx)
        .await?;

    // Commit transaksi
    tx.commit().await?;

    Ok(())
}

Jika ada error di tengah, tx.rollback() otomatis dijalankan saat tx di-drop. Semua perubahan dibatalkan.

Error Handling

rust

rustenum AppError {
    NotFound,
    DuplicateEntry,
    DatabaseError(String),
}

impl From<sqlx::Error> for AppError {
    fn from(err: sqlx::Error) -> Self {
        match &err {
            sqlx::Error::RowNotFound => AppError::NotFound,
            sqlx::Error::Database(db_err) => {
                if db_err.constraint().is_some() {
                    AppError::DuplicateEntry
                } else {
                    AppError::DatabaseError(err.to_string())
                }
            }
            _ => AppError::DatabaseError(err.to_string()),
        }
    }
}

Raw Query vs Query Builder

SQLx mendukung raw SQL dan query builder:

rust

rust// Raw SQL (lebih fleksibel)
let todos = sqlx::query_as::<_, Todo>(
    "SELECT * FROM todos WHERE completed = ? ORDER BY created_at DESC LIMIT ?"
)
.bind(false)
.bind(10)
.fetch_all(&pool)
    .await?;

// Query builder (lebih aman untuk dynamic query)
let mut builder = sqlx::QueryBuilder::new(
    "SELECT * FROM todos WHERE 1=1"
);

if let Some(completed) = completed {
    builder.push(" AND completed = ");
    builder.push_bind(completed);
}

if let Some(limit) = limit {
    builder.push(" LIMIT ");
    builder.push_bind(limit);
}

let todos = builder
    .build_query_as::<Todo>()
    .fetch_all(&pool)
    .await?;

Query builder berguna untuk filter dinamis yang jumlahnya bervariasi.

Perbandingan SQLite vs PostgreSQL

Fitur	SQLite	PostgreSQL
Setup	Sangat mudah	Butuh server
Concurrent write	Terbatas	Sangat baik
Full text search	FTS5	Built-in
JSON support	Terbatas	Sangat baik
Cocok untuk	Development, CLI, embedded	Production, scale
File size	Single file	Multi file

Tips:

• Pakai SQLite untuk development, prototyping, dan aplikasi kecil
• Pakai PostgreSQL untuk production dan aplikasi yang butuh concurrent write tinggi
• SQLx mendukung keduanya, ganti hanya connection string

Best Practices

1. Selalu Gunakan Connection Pool

rust

rust// JANGAN: buka koneksi baru setiap request
let conn = SqlitePool::connect("sqlite:app.db").await?;

// BAIK: gunakan pool yang sudah dibuat
let result = sqlx::query("SELECT * FROM todos")
    .fetch_all(&state.db)
    .await?;

2. Gunakan Migrations

bash

bash# JANGAN: CREATE TABLE di kode
# BAIK: gunakan migrations
sqlx migrate add create_todos_table
sqlx migrate run

3. Handle Error dengan Benar

rust

rust// JANGAN: unwrap
let todo = sqlx::query_as::<_, Todo>("SELECT * FROM todos WHERE id = ?")
    .bind(id)
    .fetch_one(&pool)
    .await
    .unwrap(); // PANIC!

// BAIK: return error
let todo = sqlx::query_as::<_, Todo>("SELECT * FROM todos WHERE id = ?")
    .bind(id)
    .fetch_optional(&pool)
    .await?;

4. Gunakan Fetch yang Tepat

Method	Return	Kapan
fetch_one	T	Pasti ada 1 row
fetch_optional	Option<T>	Mungkin tidak ada
fetch_all	Vec<T>	Bisa banyak row
execute	QueryResult	INSERT/UPDATE/DELETE

5. Validasi Input Sebelum Query

rust

rustasync fn create_todo(
    State(state): State<AppState>,
    Json(payload): Json<CreateTodo>,
) -> Result<(StatusCode, Json<Todo>), AppError> {
    // Validasi dulu
    if payload.title.is_empty() {
        return Err(AppError::BadRequest("Title cannot be empty".to_string()));
    }
    if payload.title.len() > 200 {
        return Err(AppError::BadRequest("Title too long".to_string()));
    }

    // Baru query
    let todo = sqlx::query_as::<_, Todo>(...)
        .bind(&payload.title)
        .fetch_one(&state.db)
        .await?;

    Ok((StatusCode::CREATED, Json(todo)))
}

Kesimpulan

SQLx adalah library database terbaik untuk Rust:

• Fully async — cocok dengan Tokio dan Axum
• Compile-time checking — error SQL terdeteksi saat compile
• No ORM — tulis SQL langsung, lebih transparan
• Migrations — kelola schema database dengan rapi
• Connection pool — manajemen koneksi yang efisien
• Multi-database — support SQLite, PostgreSQL, MySQL

Dengan menguasai SQLx, kamu sudah bisa membangun full backend application dengan Rust: API + Database. Kombinasikan dengan Axum dari artikel sebelumnya, dan kamu punya stack yang sangat powerful untuk production.