Step 1: PostgreSQL 데이터베이스 연동

학습 목표

• SQLx로 PostgreSQL에 연결하기
• 데이터베이스 마이그레이션 작성하기
• 비동기 쿼리 실행하기

Cargo.toml에 추가

1[dependencies]
2sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "chrono", "uuid"] }
3chrono = { version = "0.4", features = ["serde"] }
4uuid = { version = "1.0", features = ["v4", "serde"] }
5dotenv = "0.15"

PostgreSQL 설치 및 실행

Docker로 실행:

1docker run --name postgres-library \
2  -e POSTGRES_PASSWORD=password \
3  -e POSTGRES_DB=library_db \
4  -p 5432:5432 \
5  -d postgres:15

환경 변수 설정

.env 파일 생성:

DATABASE_URL=postgresql://postgres:password@localhost:5432/library_db

SQLx CLI 설치

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

데이터베이스 마이그레이션

마이그레이션 생성

sqlx migrate add create_books_table

migrations/xxx_create_books_table.sql:

1CREATE TYPE book_category AS ENUM ('fiction', 'nonfiction', 'science', 'history');
2
3CREATE TYPE book_status AS ENUM ('available', 'borrowed', 'reserved', 'lost');
4
5CREATE TABLE books (
6    id SERIAL PRIMARY KEY,
7    title VARCHAR(255) NOT NULL,
8    author VARCHAR(255) NOT NULL,
9    category book_category NOT NULL,
10    status book_status NOT NULL DEFAULT 'available',
11    borrowed_by VARCHAR(255),
12    due_date DATE,
13    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
14    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
15);
16
17CREATE INDEX idx_books_status ON books(status);
18CREATE INDEX idx_books_category ON books(category);

마이그레이션 실행

sqlx migrate run

데이터베이스 모델

src/models.rs 생성:

1use chrono::{NaiveDate, NaiveDateTime};
2use serde::{Deserialize, Serialize};
3use sqlx::FromRow;
4
5#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
6pub struct BookRow {
7    pub id: i32,
8    pub title: String,
9    pub author: String,
10    pub category: String,
11    pub status: String,
12    pub borrowed_by: Option<String>,
13    pub due_date: Option<NaiveDate>,
14    pub created_at: NaiveDateTime,
15    pub updated_at: NaiveDateTime,
16}
17
18impl BookRow {
19    pub fn to_book(&self) -> Book {
20        let category = match self.category.as_str() {
21            "fiction" => Category::Fiction,
22            "nonfiction" => Category::NonFiction,
23            "science" => Category::Science,
24            "history" => Category::History,
25            _ => Category::NonFiction,
26        };
27        
28        let status = match self.status.as_str() {
29            "available" => BookStatus::Available,
30            "borrowed" => BookStatus::Borrowed {
31                by: self.borrowed_by.clone().unwrap_or_default(),
32                due_date: self.due_date
33                    .map(|d| d.to_string())
34                    .unwrap_or_default(),
35            },
36            "reserved" => BookStatus::Reserved {
37                by: self.borrowed_by.clone().unwrap_or_default(),
38            },
39            "lost" => BookStatus::Lost,
40            _ => BookStatus::Available,
41        };
42        
43        Book {
44            id: self.id as u32,
45            title: self.title.clone(),
46            author: self.author.clone(),
47            category,
48            status,
49        }
50    }
51}

데이터베이스 레이어

src/db.rs 생성:

1use sqlx::{PgPool, postgres::PgPoolOptions};
2use crate::{Book, Category, LibraryError, Result, models::BookRow};
3
4pub struct Database {
5    pool: PgPool,
6}
7
8impl Database {
9    pub async fn new(database_url: &str) -> Result<Self> {
10        let pool = PgPoolOptions::new()
11            .max_connections(5)
12            .connect(database_url)
13            .await
14            .map_err(|e| LibraryError::DatabaseError(e.to_string()))?;
15        
16        Ok(Database { pool })
17    }
18    
19    pub async fn add_book(
20        &self,
21        title: &str,
22        author: &str,
23        category: &Category,
24    ) -> Result<i32> {
25        let category_str = match category {
26            Category::Fiction => "fiction",
27            Category::NonFiction => "nonfiction",
28            Category::Science => "science",
29            Category::History => "history",
30        };
31        
32        let row = sqlx::query!(
33            r#"
34            INSERT INTO books (title, author, category)
35            VALUES ($1, $2, $3::book_category)
36            RETURNING id
37            "#,
38            title,
39            author,
40            category_str
41        )
42        .fetch_one(&self.pool)
43        .await
44        .map_err(|e| LibraryError::DatabaseError(e.to_string()))?;
45        
46        Ok(row.id)
47    }
48    
49    pub async fn find_book(&self, id: i32) -> Result<Book> {
50        let row = sqlx::query_as::<_, BookRow>(
51            "SELECT * FROM books WHERE id = $1"
52        )
53        .bind(id)
54        .fetch_optional(&self.pool)
55        .await
56        .map_err(|e| LibraryError::DatabaseError(e.to_string()))?;
57        
58        row.map(|r| r.to_book())
59            .ok_or(LibraryError::BookNotFound(id as u32))
60    }
61    
62    pub async fn list_all_books(&self) -> Result<Vec<Book>> {
63        let rows = sqlx::query_as::<_, BookRow>(
64            "SELECT * FROM books ORDER BY id"
65        )
66        .fetch_all(&self.pool)
67        .await
68        .map_err(|e| LibraryError::DatabaseError(e.to_string()))?;
69        
70        Ok(rows.into_iter().map(|r| r.to_book()).collect())
71    }
72    
73    pub async fn borrow_book(
74        &self,
75        id: i32,
76        borrower: &str,
77        due_date: &str,
78    ) -> Result<()> {
79        let due = chrono::NaiveDate::parse_from_str(due_date, "%Y-%m-%d")
80            .map_err(|_| LibraryError::InvalidDate(due_date.to_string()))?;
81        
82        let result = sqlx::query!(
83            r#"
84            UPDATE books
85            SET status = 'borrowed'::book_status,
86                borrowed_by = $1,
87                due_date = $2,
88                updated_at = NOW()
89            WHERE id = $3 AND status = 'available'::book_status
90            "#,
91            borrower,
92            due,
93            id
94        )
95        .execute(&self.pool)
96        .await
97        .map_err(|e| LibraryError::DatabaseError(e.to_string()))?;
98        
99        if result.rows_affected() == 0 {
100            return Err(LibraryError::AlreadyBorrowed {
101                title: format!("ID {}", id),
102                borrower: "알 수 없음".to_string(),
103            });
104        }
105        
106        Ok(())
107    }
108    
109    pub async fn return_book(&self, id: i32) -> Result<()> {
110        let result = sqlx::query!(
111            r#"
112            UPDATE books
113            SET status = 'available'::book_status,
114                borrowed_by = NULL,
115                due_date = NULL,
116                updated_at = NOW()
117            WHERE id = $1 AND status = 'borrowed'::book_status
118            "#,
119            id
120        )
121        .execute(&self.pool)
122        .await
123        .map_err(|e| LibraryError::DatabaseError(e.to_string()))?;
124        
125        if result.rows_affected() == 0 {
126            return Err(LibraryError::NotBorrowed(format!("ID {}", id)));
127        }
128        
129        Ok(())
130    }
131}

웹 서버 수정

src/bin/web.rs:

1use book_manager::db::Database;
2use dotenv::dotenv;
3use std::env;
4
5struct AppState {
6    db: Database,
7}
8
9#[actix_web::main]
10async fn main() -> std::io::Result<()> {
11    dotenv().ok();
12    
13    let database_url = env::var("DATABASE_URL")
14        .expect("DATABASE_URL must be set");
15    
16    let db = Database::new(&database_url)
17        .await
18        .expect("Failed to connect to database");
19    
20    println!("✅ Database connected");
21    println!("🚀 Server starting at http://localhost:8080");
22    
23    let app_state = web::Data::new(AppState { db });
24    
25    HttpServer::new(move || {
26        App::new()
27            .app_data(app_state.clone())
28            .route("/health", web::get().to(health_check))
29            .route("/books", web::get().to(list_books))
30            .route("/books/{id}", web::get().to(get_book))
31            .route("/books", web::post().to(create_book))
32            .route("/books/{id}/borrow", web::post().to(borrow_book))
33            .route("/books/{id}/return", web::post().to(return_book))
34    })
35    .bind(("127.0.0.1", 8080))?
36    .run()
37    .await
38}
39
40async fn list_books(data: web::Data<AppState>) -> Result<HttpResponse, LibraryError> {
41    let books = data.db.list_all_books().await?;
42    Ok(HttpResponse::Ok().json(ApiResponse::success(books)))
43}
44
45async fn get_book(
46    data: web::Data<AppState>,
47    path: web::Path<i32>,
48) -> Result<HttpResponse, LibraryError> {
49    let id = path.into_inner();
50    let book = data.db.find_book(id).await?;
51    Ok(HttpResponse::Ok().json(ApiResponse::success(book)))
52}
53
54async fn create_book(
55    data: web::Data<AppState>,
56    req: web::Json<CreateBookRequest>,
57) -> Result<HttpResponse, LibraryError> {
58    let category: Category = req.category.as_str().into();
59    let id = data.db.add_book(&req.title, &req.author, &category).await?;
60    Ok(HttpResponse::Created().json(ApiResponse::success(id)))
61}

체크리스트

• [ ] PostgreSQL을 설치하고 실행했습니다
• [ ] SQLx로 데이터베이스에 연결했습니다
• [ ] 마이그레이션을 작성하고 실행했습니다
• [ ] CRUD 쿼리를 구현했습니다
• [ ] 웹 서버를 데이터베이스와 연동했습니다

다음 단계

Step 2에서는 JWT 인증을 추가하여 사용자 인증 시스템을 구현합니다.