Ch 05. 데이터베이스 연동 — SQLite — 실전 Dart

데이터베이스 연동 — SQLite

지금까지의 API는 하드코딩된 데이터를 반환했습니다. 이번 챕터에서는 SQLite 데이터베이스를 연동합니다. SQLite는 파일 하나로 동작하는 내장형 데이터베이스입니다. 별도 서버 없이 Dart 프로세스 안에서 바로 사용합니다.

sqlite3 패키지 추가

# 수정: pubspec.yamldependencies:  dart_frog: ^1.4.0  sqlite3: ^2.4.3

dart pub get

macOS에서는 SQLite가 기본 설치되어 있습니다. Linux에서는 추가 설치가 필요합니다.

# Ubuntu/Debiansudo apt-get install libsqlite3-dev

Database 클래스 — 연결 관리

// 새 파일: lib/src/database.dart
import 'package:sqlite3/sqlite3.dart';

/// SQLite 데이터베이스 연결을 관리합니다.
class Database {
  Database._(this._db);

  final sqlite3.Database _db;

  /// 데이터베이스를 열고 스키마를 초기화합니다.
  static Database open(String path) {
    final db = sqlite3.open(path);
    final instance = Database._(db);
    instance._initialize();
    return instance;
  }

  /// 인메모리 데이터베이스를 생성합니다 (테스트용).
  static Database openInMemory() {
    final db = sqlite3.openInMemory();
    final instance = Database._(db);
    instance._initialize();
    return instance;
  }

  void _initialize() {
    // WAL 모드 활성화 (읽기 성능 향상)
    _db.execute('PRAGMA journal_mode=WAL;');
    _db.execute('PRAGMA foreign_keys=ON;');

    // 테이블 생성
    _db.execute('''
      CREATE TABLE IF NOT EXISTS users (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        email         TEXT NOT NULL UNIQUE,
        password_hash TEXT NOT NULL,
        created_at    TEXT NOT NULL DEFAULT (datetime('now'))
      );
    ''');

    _db.execute('''
      CREATE TABLE IF NOT EXISTS todos (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        title       TEXT NOT NULL,
        completed   INTEGER NOT NULL DEFAULT 0,
        user_id     INTEGER NOT NULL,
        created_at  TEXT NOT NULL DEFAULT (datetime('now')),
        updated_at  TEXT,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      );
    ''');

    // 인덱스 생성
    _db.execute(
      'CREATE INDEX IF NOT EXISTS idx_todos_user_id ON todos(user_id);',
    );
  }

  /// 원시 SQL을 실행합니다 (INSERT, UPDATE, DELETE).
  void execute(String sql, [List<Object?> parameters = const []]) {
    _db.execute(sql, parameters);
  }

  /// SELECT 결과를 Map 목록으로 반환합니다.
  List<Map<String, dynamic>> query(
    String sql, [
    List<Object?> parameters = const [],
  ]) {
    final result = _db.select(sql, parameters);
    return result.map((row) => Map<String, dynamic>.from(row)).toList();
  }

  /// 마지막으로 삽입된 행의 ID를 반환합니다.
  int get lastInsertRowId => _db.lastInsertRowId;

  /// 변경된 행 수를 반환합니다.
  int get updatedRows => _db.getUpdatedRows();

  void close() => _db.dispose();
}

Repository 패턴

Repository 패턴은 데이터베이스 접근 로직을 캡슐화합니다. 서비스 레이어가 SQL을 직접 작성하지 않고 Repository 메서드를 호출합니다.

// 새 파일: lib/src/repositories/todo_repository.dart
import '../database.dart';
import '../exceptions.dart';
import '../models/todo.dart';

class TodoRepository {
  const TodoRepository(this._db);

  final Database _db;

  /// 사용자의 모든 할 일을 조회합니다.
  List<Todo> findByUserId(
    int userId, {
    bool? completed,
    int limit = 20,
    int offset = 0,
  }) {
    final buffer = StringBuffer(
      'SELECT * FROM todos WHERE user_id = ?',
    );
    final params = <Object?>[userId];

    if (completed != null) {
      buffer.write(' AND completed = ?');
      params.add(completed ? 1 : 0);
    }

    buffer.write(' ORDER BY created_at DESC LIMIT ? OFFSET ?');
    params.addAll([limit, offset]);

    final rows = _db.query(buffer.toString(), params);
    return rows.map(Todo.fromMap).toList();
  }

  /// 특정 할 일을 ID로 조회합니다.
  Todo? findById(int id) {
    final rows = _db.query('SELECT * FROM todos WHERE id = ?', [id]);
    if (rows.isEmpty) return null;
    return Todo.fromMap(rows.first);
  }

  /// 새 할 일을 생성합니다.
  Todo create({required String title, required int userId}) {
    _db.execute(
      'INSERT INTO todos (title, user_id) VALUES (?, ?)',
      [title, userId],
    );

    final id = _db.lastInsertRowId;
    return findById(id)!;
  }

  /// 할 일을 수정합니다.
  Todo update({
    required int id,
    String? title,
    bool? completed,
  }) {
    final existing = findById(id);
    if (existing == null) throw const NotFoundException('할 일을 찾을 수 없습니다.');

    final newTitle = title ?? existing.title;
    final newCompleted = completed ?? existing.completed;

    _db.execute(
      '''
      UPDATE todos
      SET title = ?, completed = ?, updated_at = datetime('now')
      WHERE id = ?
      ''',
      [newTitle, newCompleted ? 1 : 0, id],
    );

    return findById(id)!;
  }

  /// 할 일을 삭제합니다.
  void delete(int id) {
    _db.execute('DELETE FROM todos WHERE id = ?', [id]);
    if (_db.updatedRows == 0) {
      throw const NotFoundException('할 일을 찾을 수 없습니다.');
    }
  }

  /// 사용자의 할 일 수를 반환합니다.
  int countByUserId(int userId, {bool? completed}) {
    var sql = 'SELECT COUNT(*) as cnt FROM todos WHERE user_id = ?';
    final params = <Object?>[userId];

    if (completed != null) {
      sql += ' AND completed = ?';
      params.add(completed ? 1 : 0);
    }

    final rows = _db.query(sql, params);
    return rows.first['cnt'] as int;
  }
}

// 새 파일: lib/src/repositories/user_repository.dart
import '../database.dart';
import '../models/user.dart';

class UserRepository {
  const UserRepository(this._db);

  final Database _db;

  User? findByEmail(String email) {
    final rows = _db.query(
      'SELECT * FROM users WHERE email = ?',
      [email],
    );
    if (rows.isEmpty) return null;
    return User.fromMap(rows.first);
  }

  User? findById(int id) {
    final rows = _db.query('SELECT * FROM users WHERE id = ?', [id]);
    if (rows.isEmpty) return null;
    return User.fromMap(rows.first);
  }

  User create({required String email, required String passwordHash}) {
    _db.execute(
      'INSERT INTO users (email, password_hash) VALUES (?, ?)',
      [email, passwordHash],
    );
    return findById(_db.lastInsertRowId)!;
  }
}

의존성 주입 미들웨어

데이터베이스와 Repository를 미들웨어로 주입합니다.

// 수정: routes/_middleware.dart
import 'package:dart_frog/dart_frog.dart';

import '../lib/src/database.dart';
import '../lib/src/middleware/cors_middleware.dart';
import '../lib/src/middleware/error_middleware.dart';
import '../lib/src/middleware/logging_middleware.dart';
import '../lib/src/repositories/todo_repository.dart';
import '../lib/src/repositories/user_repository.dart';

// 애플리케이션 수명 동안 단일 DB 인스턴스 유지
final _db = Database.open('todo.db');

Handler middleware(Handler handler) {
  return handler
      .use(errorHandler())
      .use(requestLogging())
      .use(cors())
      .use(provider<Database>((_) => _db))
      .use(provider<TodoRepository>(
        (context) => TodoRepository(context.read<Database>()),
      ))
      .use(provider<UserRepository>(
        (context) => UserRepository(context.read<Database>()),
      ));
}

provider<T>()는 dart_frog가 제공하는 의존성 주입 미들웨어입니다. context.read<T>()로 주입된 값을 꺼낼 수 있습니다.

라우트 핸들러에서 Repository 사용

// 수정: routes/todos/index.dart
import 'package:dart_frog/dart_frog.dart';

import '../../lib/src/dto/todo_dto.dart';
import '../../lib/src/repositories/todo_repository.dart';
import '../../lib/src/utils/query_params.dart';
import '../../lib/src/utils/response_utils.dart';

Future<Response> onRequest(RequestContext context) async {
  return switch (context.request.method) {
    HttpMethod.get => _getTodos(context),
    HttpMethod.post => _createTodo(context),
    _ => Response(statusCode: 405),
  };
}

Future<Response> _getTodos(RequestContext context) async {
  final repo = context.read<TodoRepository>();
  final params = context.request.uri.queryParameters;

  final limit = parseIntParam(params, 'limit', defaultValue: 20, max: 100);
  final offset = parseIntParam(params, 'offset', defaultValue: 0, min: 0);
  final completed = parseBoolParam(params, 'completed');

  // 인증 미들웨어에서 주입된 userId (Ch 06에서 구현)
  const userId = 1; // 임시

  final todos = repo.findByUserId(
    userId,
    completed: completed,
    limit: limit,
    offset: offset,
  );
  final total = repo.countByUserId(userId, completed: completed);

  return listResponse(
    todos.map((t) => t.toJson()).toList(),
    total: total,
    page: offset ~/ limit + 1,
    limit: limit,
  );
}

Future<Response> _createTodo(RequestContext context) async {
  final repo = context.read<TodoRepository>();
  final json = await context.request.json() as Map<String, dynamic>;
  final request = CreateTodoRequest.fromJson(json);

  const userId = 1; // Ch 06에서 JWT에서 추출

  final todo = repo.create(title: request.title, userId: userId);
  return createdResponse(todo.toJson());
}

// 수정: routes/todos/[id].dart
import 'package:dart_frog/dart_frog.dart';

import '../../lib/src/dto/todo_dto.dart';
import '../../lib/src/exceptions.dart';
import '../../lib/src/repositories/todo_repository.dart';
import '../../lib/src/utils/response_utils.dart';

Future<Response> onRequest(RequestContext context, String id) async {
  final todoId = int.tryParse(id);
  if (todoId == null) throw const ValidationException('유효하지 않은 ID입니다.');

  return switch (context.request.method) {
    HttpMethod.get => _getTodo(context, todoId),
    HttpMethod.put => _updateTodo(context, todoId),
    HttpMethod.delete => _deleteTodo(context, todoId),
    _ => Response(statusCode: 405),
  };
}

Future<Response> _getTodo(RequestContext context, int id) async {
  final repo = context.read<TodoRepository>();
  final todo = repo.findById(id);
  if (todo == null) throw const NotFoundException('할 일을 찾을 수 없습니다.');
  return okResponse(todo.toJson());
}

Future<Response> _updateTodo(RequestContext context, int id) async {
  final repo = context.read<TodoRepository>();
  final json = await context.request.json() as Map<String, dynamic>;
  final request = UpdateTodoRequest.fromJson(json);

  final updated = repo.update(
    id: id,
    title: request.title,
    completed: request.completed,
  );

  return okResponse(updated.toJson());
}

Future<Response> _deleteTodo(RequestContext context, int id) async {
  final repo = context.read<TodoRepository>();
  repo.delete(id); // 없으면 NotFoundException 던짐
  return noContentResponse();
}

실행 및 테스트

dart_frog dev

# 테스트 사용자 직접 삽입 (DB 초기 데이터)sqlite3 todo.db "INSERT INTO users (email, password_hash) VALUES ('[email protected]', 'hash');"# 할 일 생성curl -s -X POST http://localhost:8080/todos \  -H "Content-Type: application/json" \  -H "Authorization: Bearer dummy" \  -d '{"title": "SQLite에 저장되는 할 일"}' | python3 -m json.tool# 전체 조회curl -s http://localhost:8080/todos \  -H "Authorization: Bearer dummy" | python3 -m json.tool# 완료 필터curl -s "http://localhost:8080/todos?completed=false" \  -H "Authorization: Bearer dummy" | python3 -m json.tool

정리

이번 챕터에서는 SQLite를 연동하고 Repository 패턴으로 데이터 접근 로직을 캡슐화했습니다.

sqlite3 패키지로 파일 기반 데이터베이스를 사용합니다.
Database 클래스가 연결과 스키마 초기화를 담당합니다.
TodoRepository와 UserRepository가 SQL을 캡슐화합니다.
provider<T>() 미들웨어로 의존성을 주입합니다.

다음 챕터에서는 JWT를 도입해 실제 인증을 구현합니다.