Sqlite Expert

sqlite-expert

SQLite 엔진 고유의 문제가 대상이다. 스키마 설계 이론·PostgreSQL 운영은 db-expert, Go 코드 관용구는 go-expert 가 맡는다.

SQLite 는 "작은 RDB"가 아니라 파일 하나가 데이터베이스인 라이브러리다. 서버가 없다는 사실에서 이 문서의 거의 모든 항목이 파생된다.

1. 남의 파일을 읽을 때 — 원본을 바꾸지 않는다

앱이 쓰고 있는 캐시·데이터 파일을 조회하는 작업이 흔하다. 원본을 건드리면 그 앱의 데이터가 깨진다. 기본은 읽기 전용이다.

dsn := "file:" + path + "?mode=ro&_pragma=busy_timeout(5000)"

• mode=ro — 쓰기를 엔진 수준에서 막는다. 애플리케이션 규율에 기대지 않는다.
• busy_timeout — 다른 프로세스가 쓰는 중이면 즉시 실패하지 않고 기다린다. 없으면 산발적인 database is locked 로 나타난다.
• URI 파일명에서 ?·# 은 구분자다. 경로에 들어 있으면 퍼센트 인코딩한다. 경로 문자열을 그냥 이어붙이면 파일을 못 찾는다.

곁 파일까지 확인한다

읽기만 해도 -wal·-shm·-journal 이 생기면 원본 폴더를 오염시킨 것이다. WAL 모드 DB 를 열면 실제로 발생할 수 있다. 정말 건드리면 안 되는 파일은 immutable=1 을 고려하되, 이건 "파일이 변하지 않는다"는 약속이므로 앱이 쓰는 중이면 쓰지 않는다.

가장 안전한 순서: 사본을 떠서 사본을 연다. 그럴 수 없으면 mode=ro + 곁 파일 검사.

이건 테스트로 고정한다

문서에만 적힌 "읽기 전용"은 다음 리팩터링에서 사라진다. 회귀 테스트로 못 박는다.

// 조회란 조회를 다 돌린 뒤 파일 해시가 같은지, 곁 파일이 안 생겼는지
before := sha256sum(path)
// ... Rooms / Messages / Count / Search ...
if after := sha256sum(path); after != before { t.Error("원본이 바뀌었다") }
for _, s := range []string{"-wal", "-shm", "-journal"} {
    if _, err := os.Stat(path + s); !os.IsNotExist(err) { t.Error("곁 파일이 생겼다") }
}

쓰기가 실제로 막히는지도 확인한다 — mode=ro 로 연 뒤 DELETE 가 실패해야 한다.

2. 동적 테이블·컬럼명 — 유일한 방어선

테이블명은 플레이스홀더로 넘길 수 없다. 스키마가 Chat_<방ID> 처럼 데이터에 따라 갈리는 구조면 문자열 조립이 불가피하다. 그러면 검증이 유일한 방어선이 된다.

var roomIDRe = regexp.MustCompile(`^[0-9a-f]{12}-[0-9]{3}$`)

func tableName(id string) (string, error) {
    if !roomIDRe.MatchString(id) {   // 통과 못 하면 절대 쿼리에 넣지 않는다
        return "", fmt.Errorf("%w: %q", ErrInvalidRoomID, id)
    }
    return "Chat_" + id, nil
}
// 조립 시 반드시 인용부호로 감싼다 — 이름의 '-' 가 연산자로 파싱되는 것도 막는다
q := fmt.Sprintf(`SELECT ... FROM %q WHERE Sequence > ?`, table)

규칙: 화이트리스트(정규식 또는 sqlite_master 조회 결과)를 통과한 값만 쓰고, %q 로 감싸고, 주입 시도 케이스를 테스트에 넣는다. 값은 언제나 플레이스홀더(?)로 넘긴다.

LIKE 검색

r := strings.NewReplacer(`\`, `\\`, `%`, `\%`, `_`, `\_`)
pattern := "%" + r.Replace(query) + "%"
// ... WHERE Content LIKE ? ESCAPE ''

이스케이프를 빠뜨리면 사용자가 % 만 넣어도 전부 걸린다. ESCAPE 절을 함께 줘야 한다.

LIKE 는 ASCII 만 대소문자를 무시한다. 한글은 대소문자가 없어 문제되지 않지만, 라틴 문자 검색에서 유니코드 대소문자를 맞추려면 애플리케이션에서 정규화한다.

3. 타입과 NULL

• 동적 타입이다. 컬럼 선언이 INTEGER 여도 문자열이 들어가 있을 수 있다. 남의 파일을 읽을 때는 전 컬럼을 sql.NullXxx 로 받는다. 스키마 선언을 믿지 않는다.
• 날짜 전용 타입이 없다. TEXT(2026-07-29 10:55:20)·정수 epoch 가 섞여 있다. 타임존 정보가 없으면 로컬로 해석하고 그 가정을 주석에 남긴다.
• WITHOUT ROWID 는 TEXT 기본키에서 흔하다. 읽기에는 영향 없다.
• 불리언은 정수 0/1 이다.

4. 쓰기가 있는 경우

• 기본이 자동 커밋이라 대량 INSERT 가 극단적으로 느리다. 트랜잭션으로 감싸면 수십~수백 배 차이가 난다. 이건 최적화가 아니라 기본이다.
• 동시 쓰기는 한 번에 하나다. 여러 프로세스가 쓰면 SQLITE_BUSY 를 각오하고 busy_timeout + 재시도를 둔다.
• WAL(journal_mode=WAL)은 읽기와 쓰기를 겹치게 해준다. 대신 곁 파일이 생기고, 네트워크 파일시스템에서는 쓰지 않는다(잠금이 깨진다).
• Go 에서 쓰기 커넥션은 SetMaxOpenConns(1) 이 안전한 기본이다. 읽기 전용이면 불필요.
• PRAGMA foreign_keys = ON 은 커넥션마다 켜야 한다. 기본이 꺼져 있다.

5. 인덱스

• 기본키가 아닌 조회 조건에는 인덱스를 만든다. 다만 테이블이 작으면 의미 없다 — 수백 행짜리에 인덱스를 붙이며 시간 쓰지 않는다.
• 복합 인덱스는 앞 컬럼부터 쓰인다. (RoomId, Sequence) 는 RoomId 단독 조회에는 쓰이지만 Sequence 단독에는 안 쓰인다.
• 앞 컬럼이 상수 하나뿐인 인덱스는 사실상 뒤 컬럼 인덱스다 — 그런 구조를 발견하면 지적한다.
• EXPLAIN QUERY PLAN <쿼리> 로 확인한다. SCAN 이 보이면 인덱스를 안 탄 것이다.

6. 드라이버 선택 (Go)

조회 위주·크로스컴파일 배포면 modernc.org/sqlite 를 기본으로 한다. 대량 쓰기 성능이 병목으로 측정된 경우에만 CGO 판을 고려한다. 드라이버 이름은 "sqlite"(modernc) / "sqlite3"(mattn) 으로 다르다.

7. 마이그레이션

• ALTER TABLE 지원이 제한적이다. 컬럼 삭제·타입 변경은 새 테이블 생성 → 복사 → 교체가 정석이다. 이 절차 전체를 하나의 트랜잭션에 넣는다.
• PRAGMA user_version 으로 스키마 버전을 관리하면 의존성 없이 충분하다.
• 마이그레이션 전 파일을 복사해 둔다. 파일 하나라 백업이 쉽다 — 안 할 이유가 없다.

8. 완료 조건

• 남의 파일을 읽는 코드면: mode=ro + 원본 불변 회귀 테스트(해시·곁 파일)가 있음
• 동적 테이블·컬럼명이 있으면: 화이트리스트 검증 + 인용 + 주입 시도 테스트가 있음
• LIKE 를 쓰면 와일드카드 이스케이프 + ESCAPE 절이 있음
• 남의 파일을 읽는 경우 전 컬럼 NULL 방어가 되어 있음
• 대량 쓰기가 트랜잭션으로 묶여 있음
• 느린 쿼리는 EXPLAIN QUERY PLAN 으로 확인함