SQLite 연결하기: Python, Node, Go, Java 드라이버 정리

연결이란 그냥 파일을 여는 것이다

SQLite에는 서버가 없습니다. 포트를 열고 대기하는 데몬도 없고, 접속할 호스트도, 주고받을 인증 정보도 없죠. 여기서 "연결"이란 드라이버가 디스크의 파일 하나를 열고, 그 파일의 페이지를 읽고 쓰기 시작한다는 뜻입니다. SQLite 연결의 멘탈 모델은 딱 이게 전부입니다.

언어마다 SQLite C 라이브러리를 감싼 드라이버가 있습니다. 모양새는 조금씩 다르지만 구성 요소는 똑같습니다. 데이터베이스 파일 경로, 파일을 여는 호출, 그 위에서 쿼리를 실행할 핸들, 그리고 작업이 끝났을 때 호출하는 close. 이게 전부예요.

-- 概念的には、すべてのドライバーは以下のことを行います:
-- 1. 指定されたパスのファイルを開くか作成する。
-- 2. ハンドルを取得する。
-- 3. プリペアドステートメントを介してSQLを実行する。
-- 4. ハンドルを閉じる。

이 페이지의 나머지 부분에서는 실제 코드로 어떻게 생겼는지, 그리고 첫 쿼리를 날리기 전에 꼭 설정해 둘 만한 몇 가지 옵션을 살펴봅니다.

Python: 표준 라이브러리의 sqlite3 모듈

파이썬에는 sqlite3가 기본으로 들어 있어서 따로 설치할 필요가 없습니다. 가장 기본적인 형태는 다음과 같습니다.

-- Python
import sqlite3

conn = sqlite3.connect("app.db")
conn.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
conn.execute("INSERT INTO notes (body) VALUES (?)", ("첫 번째 메모",))
conn.commit()

for row in conn.execute("SELECT id, body FROM notes"):
    print(row)

conn.close()

알아두면 좋은 몇 가지 포인트:

• sqlite3.connect("app.db")는 파일이 없으면 새로 만들어 줍니다. 메모리에만 존재하는 DB가 필요하면 ":memory:"를 넘기면 됩니다.
• 읽기 전용으로 열고 싶다면 URI 형태로 sqlite3.connect("file:app.db?mode=ro", uri=True)처럼 작성하세요.
• SQL 안의 ?는 플레이스홀더입니다. 문자열을 직접 이어붙이지 말고 반드시 파라미터 바인딩을 쓰세요. 자세한 내용은 다음 장에서 다룹니다.
• conn.commit()은 꼭 호출해야 합니다. 다만 with conn: 형태의 컨텍스트 매니저를 쓰면 자동으로 커밋됩니다.

오래 떠 있는 애플리케이션이라면 busy timeout을 설정해 두세요. 동시에 쓰기가 일어날 때 바로 에러를 내지 않고 잠깐 기다리도록 만들어 줍니다:

-- Python
conn.execute("PRAGMA busy_timeout = 5000")   -- 최대 5초간 대기
conn.execute("PRAGMA journal_mode = WAL")    -- 더 나은 동시성

Node.js에서 better-sqlite3로 SQLite 연결하기

Node 생태계에는 SQLite를 다루는 라이브러리가 여러 개 있지만, 실무에서 대부분 better-sqlite3를 선택합니다. 동기(synchronous) 방식이라 "Node에서 동기? 이상한데?"라고 느낄 수 있지만, SQLite는 쿼리가 마이크로초 단위로 끝나기 때문에 오히려 동기 방식이 더 빠릅니다.

-- Node.js
const Database = require("better-sqlite3");
const db = new Database("app.db");

db.exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");

const insert = db.prepare("INSERT INTO notes (body) VALUES (?)");
insert.run("첫 번째 메모");

const rows = db.prepare("SELECT id, body FROM notes").all();
console.log(rows);

db.close();

db.prepare(...)는 재사용 가능한 statement 객체를 돌려줍니다. 쓰기 작업에는 .run(), 모든 행을 가져올 땐 .all(), 한 행만 필요할 땐 .get()을 쓰면 됩니다. 다른 SQL 드라이버들과 거의 같은 패턴이라 익숙하실 거예요.

시작 시점에 pragma를 설정해 두세요:

-- Node.js
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("foreign_keys = ON");   -- 기본값은 off이지만, 거의 항상 켜는 것이 좋음

foreign_keys = ON은 짚고 넘어갈 만한 옵션입니다. SQLite는 명시적으로 켜주지 않으면 외래 키를 강제하지 않으며, 게다가 연결마다 따로 설정해야 합니다. 깜빡하면 REFERENCES 절은 그냥 장식에 불과합니다.

Go에서 SQLite 연결: database/sql과 드라이버

Go의 표준 패키지 database/sql은 드라이버에 종속되지 않는 구조라, 어떤 드라이버를 쓸지 따로 골라야 합니다. SQLite의 경우 보통 modernc.org/sqlite(순수 Go, CGO 불필요)나 github.com/mattn/go-sqlite3(CGO 사용) 중에서 선택합니다.

-- Go
import (
    "database/sql"
    _ "modernc.org/sqlite"
)

db, err := sql.Open("sqlite", "app.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")
if err != nil { panic(err) }
defer db.Close()

_, err = db.Exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
_, err = db.Exec("INSERT INTO notes (body) VALUES (?)", "첫 번째 메모")

rows, _ := db.Query("SELECT id, body FROM notes")
defer rows.Close()
for rows.Next() {
    var id int; var body string
    rows.Scan(&id, &body)
    fmt.Println(id, body)
}

파일명 뒤에 붙는 쿼리 문자열은 이 드라이버가 연결 시점에 pragma를 넘기는 방식이다. 형식은 드라이버마다 다르니 사용하는 드라이버의 문서를 꼭 확인하자.

sql.Open은 실제로 연결을 여는 게 아니라, 첫 쿼리가 실행될 때 비로소 연결이 열린다. db는 커넥션 풀이며, SQLite의 경우 풀 크기를 작게 잡거나 쓰기 위주 워크로드라면 아예 db.SetMaxOpenConns(1)로 두는 편이 보통 가장 무난하다.

Java에서 JDBC로 SQLite 연결하기

org.xerial:sqlite-jdbc 드라이버가 사실상 표준이다. JDBC URL은 jdbc:sqlite:<path> 형식을 따른다:

-- Java
import java.sql.*;

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db")) {
    try (Statement st = conn.createStatement()) {
        st.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
        st.execute("PRAGMA journal_mode = WAL");
        st.execute("PRAGMA busy_timeout = 5000");
    }

    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO notes (body) VALUES (?)")) {
        ps.setString(1, "첫 번째 노트");
        ps.executeUpdate();
    }

    try (PreparedStatement ps = conn.prepareStatement("SELECT id, body FROM notes");
         ResultSet rs = ps.executeQuery()) {
        while (rs.next()) System.out.println(rs.getInt(1) + " " + rs.getString(2));
    }
}

인메모리 DB는 jdbc:sqlite::memory:로 연결합니다. 읽기 전용으로 열려면 뒤에 ?open_mode=1을 붙이거나 SQLiteConfig 객체를 사용하면 됩니다.

PHP: PDO로 SQLite 연결하기

PDO에서 SQLite DSN은 sqlite:<경로> 형식입니다:

-- PHP
$db = new PDO("sqlite:app.db");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
$db->exec("PRAGMA journal_mode = WAL");
$db->exec("PRAGMA busy_timeout = 5000");

$stmt = $db->prepare("INSERT INTO notes (body) VALUES (?)");
$stmt->execute(["첫 번째 메모"]);

foreach ($db->query("SELECT id, body FROM notes") as $row) {
    echo $row["id"] . " " . $row["body"] . "\n";
}

sqlite::memory:는 인메모리 데이터베이스를 의미합니다. 그리고 ATTR_ERRMODE는 반드시 예외(exception) 모드로 설정하세요. 조용히 실패하면 디버깅이 정말 괴롭습니다.

SQLite 연결 문자열과 파일 경로

드라이버마다 차이는 있지만, sqlite connection string은 크게 두 가지 형태를 띱니다.

• 일반 경로: app.db, ./data/app.db, /var/lib/myapp/app.db 같은 형식입니다. 상대 경로는 프로세스의 작업 디렉터리를 기준으로 해석되는데, 운영 환경에서는 거의 원하는 대로 동작하지 않습니다. 절대 경로를 쓰는 편이 안전합니다.
• URI 형식: file:app.db?mode=rwc&cache=shared 처럼 작성합니다. mode=ro(읽기 전용), mode=rwc(읽기·쓰기·생성, 기본값), cache=shared, nolock=1 같은 플래그를 지정할 수 있습니다.

자주 마주치는 특수 값도 알아두면 좋습니다.

• :memory: — 비공개 인메모리 데이터베이스입니다. 연결마다 독립적으로 생성됩니다.
• file::memory:?cache=shared — 같은 프로세스 안의 여러 연결이 공유할 수 있는 인메모리 데이터베이스입니다.
• "" (빈 문자열) — 닫는 순간 사라지는, 비공개 임시 디스크 데이터베이스입니다.

JDBC sqlite 연결에서는 URI 앞에 jdbc:sqlite:를 붙이고, PHP PDO sqlite는 sqlite: 접두사를 사용합니다. Go sqlite 드라이버와 Python의 sqlite3는 경로나 URI를 그대로 받습니다.

커넥션 풀은 어떻게 다뤄야 할까?

SQLite는 본질적으로 단일 쓰기(single-writer) 데이터베이스입니다. 어느 순간이든 쓰기 락(write lock)을 잡는 연결은 정확히 하나뿐이고, 나머지는 대기합니다. 그래서 쓰기 연결을 잔뜩 풀링한다고 쓰기 속도가 빨라지진 않습니다. 같은 락을 노리는 경쟁자만 늘어날 뿐이죠.

그렇긴 해도, 소규모 풀은 다음과 같은 상황에서 쓸모가 있습니다.

• WAL 모드에서의 동시 읽기 — 리더끼리는 물론이고, 리더와 라이터도 서로를 막지 않습니다.
• 선두 차단(head-of-line blocking) 회피 — 느린 쿼리 하나 때문에 앱 전체가 멈추는 사태를 막을 수 있습니다.

웹 애플리케이션에서 쓸 만한 기본값은 다음과 같습니다.

• PRAGMA journal_mode=WAL로 WAL 모드 활성화.
• PRAGMA busy_timeout을 몇 초 정도로 설정해, 경합이 발생해도 곧바로 에러를 내지 않고 잠시 기다리도록 함.
• 풀 크기는 라이터 1 + 리더 N개, 트래픽이 적다면 공유 연결 하나만 두는 것도 충분합니다.
• 외래 키(PRAGMA foreign_keys=ON)는 모든 연결에서 켜두기.

-- 모든 새 연결에 다음을 적용하세요:
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;   -- WAL과 함께 사용하면 안전하며, FULL보다 빠릅니다

synchronous = NORMAL은 WAL 모드와 함께 쓰는 가장 일반적인 조합입니다. 앱이 크래시되어도 데이터는 안전하게 보존되고, OS 크래시 상황에서는 약간 느슨해지는 대신 기본값인 FULL보다 눈에 띄게 빠릅니다.

연결 닫기 — 왜 중요한가

드라이버마다 연결을 닫는 메서드가 있습니다. conn.close(), db.Close(), db.close() 같은 것들이죠. 이걸 호출하지 않으면 파일 디스크립터가 새고, WAL 파일이 계속 커지는 문제가 생길 수 있습니다.

오래 떠 있는 서비스에서는 요청마다 열고 닫는 방식보다 프로세스 수명 동안 연결(또는 커넥션 풀) 하나를 유지하는 패턴 이 훨씬 일반적입니다. SQLite 연결을 여는 비용은 저렴하지만, 매번 PRAGMA를 다시 적용하는 건 낭비인 데다 빼먹기도 쉽습니다.

-- Python — 프로세스당 연결, 요청 간 재사용
DB = sqlite3.connect("app.db", check_same_thread=False)
DB.execute("PRAGMA journal_mode = WAL")
DB.execute("PRAGMA busy_timeout = 5000")
DB.execute("PRAGMA foreign_keys = ON")

Python의 경우 한 가지 더 짚어두자면, 여러 스레드에서 같은 커넥션을 쓸 거라면 check_same_thread=False 옵션을 줘야 합니다. 다만 이때는 호출을 직렬화할 수 있도록 락이나 커넥션 풀을 함께 두는 게 좋습니다.

배포 전 체크리스트

실제 트래픽을 SQLite 데이터베이스에 연결하기 전에 아래 항목을 한 번씩 확인하세요.

• 데이터베이스 파일은 절대 경로로 지정하기.
• WAL 모드 켜기 (PRAGMA journal_mode = WAL).
• busy_timeout을 2~10초 정도로 설정하기.
• 모든 커넥션마다 외래 키(foreign key) 활성화하기.
• 문자열 보간 대신 파라미터 바인딩이 적용된 prepared statement 사용하기.
• 데이터베이스가 들어 있는 디렉터리가 프로세스에 쓰기 가능한지 확인하기 (WAL 모드에서는 메인 파일 옆에 -wal, -shm 파일이 함께 생성됩니다).
• 필요해지기 전에 미리 백업 전략 세우기 — VACUUM INTO나 .backup 명령은 뒤에서 따로 다룹니다.

다음 단계: 마이그레이션

연결은 쉬운 쪽에 속합니다. 정작 까다로운 건, 운영 중인 데이터베이스를 직접 손대지 않고도 시간이 지남에 따라 스키마를 안전하게 진화시키는 일이죠. 마이그레이션은 ALTER TABLE 작업을 반복 가능하고 버전 관리되는 절차로 바꿔주는 방법인데, 바로 다음 페이지에서 이어집니다.