SQLite

SQLite는 애플리케이션 안에서 실행되는 SQL 데이터베이스 엔진이다. 이 문서는 파일과 연결의 관계, 자료형과 제약, 트랜잭션·쓰기 경쟁·WAL·백업을 실제 예제로 살펴보고, 로컬 저장소를 설계하고 운영할 때의 판단 기준을 설명한다.

개요와 실행 구조

SQLite는 애플리케이션 내부에서 실행되는 관계형 데이터베이스 엔진이다. 별도의 데이터베이스 서버에 네트워크 요청을 보내는 대신, 프로세스에 연결된 라이브러리가 SQL을 처리하고 파일을 읽고 쓴다. 모바일 앱의 로컬 저장소, 데스크톱 도구, 임베디드 장치와 테스트 환경처럼 독립적인 저장 공간이 필요한 곳에서 유용하다.

서버리스라는 말은 클라우드 업체가 서버를 숨겨 운영한다는 의미와 다르다. SQLite 자체에는 클라이언트 요청을 받는 별도 서버 프로세스가 필요하지 않다는 뜻이다. 파일 권한과 애플리케이션의 접근 제어는 여전히 필요하며, 원격 접근 API가 필요하면 애플리케이션 계층에서 제공해야 한다. SQL을 쓴다는 공통점만으로 MySQL이나 PostgreSQL의 운영 구조를 그대로 적용하면 제약을 놓칠 수 있다.

이 문서는 단순 SQL 문법 목록보다 SQLite를 앱에 넣었을 때 발생하는 문제를 설명한다. 자료형과 외래 키, 쓰기 경쟁, 트랜잭션과 WAL, 백업과 복구를 작은 재현 예제로 확인한다. SQL 코드 블록은 별도 안내가 없으면 각각 새 실습 데이터베이스에서 실행한다. Python 블록은 표준 sqlite3 모듈을 사용하며 각각 독립적으로 실행할 수 있다.

파일, 연결과 프로세스의 관계

일반적인 파일 기반 SQLite 데이터베이스는 주 데이터 파일 하나로 테이블과 인덱스 등을 저장한다. 그렇다고 실행 중 항상 파일이 하나만 존재한다는 의미는 아니다. 저널 모드에 따라 rollback journal 또는 WAL과 공유 메모리 보조 파일이 생길 수 있다. 주 파일만 복사하고 보조 파일을 무시하면 현재 확정된 변경을 빠뜨리거나 일관성이 깨진 복사본을 만들 수 있다.

연결은 엔진과 주고받는 작업 상태를 가진다. 같은 파일에 연결 두 개를 열면 같은 데이터베이스를 대상으로 하지만, 각 연결의 트랜잭션과 설정은 별도로 관리된다. 연결별 설정을 초기화하지 않은 풀을 사용하면 어떤 요청은 외래 키 검사를 하고 다른 요청은 하지 않는 식의 불일치가 생길 수 있다. 파일 경로가 같은지와 연결 설정이 같은지는 별개의 확인 사항이다.

서로 다른 프로세스가 파일을 열 수도 있지만 잠금과 파일 시스템의 동작을 신뢰할 수 있어야 한다. 네트워크 공유 폴더에 파일을 놓고 여러 호스트가 직접 쓰는 구성을 서버형 DBMS의 대체재로 가정하지 않는다. 특히 WAL은 같은 호스트의 공유 메모리 사용을 전제로 한다. 원격 다중 사용자 환경이라면 애플리케이션 서버를 통해 접근을 모으거나 서버형 DBMS를 비교한다.

라이브러리 버전과 CLI 버전 확인

컴퓨터에 설치된 sqlite3 명령줄 도구와 애플리케이션에 포함된 SQLite 라이브러리는 서로 다른 버전일 수 있다. 명령줄에서 어떤 문법이 작동한다고 Python이나 모바일 앱에서도 지원된다고 단정하지 않는다. 실제 실행 환경의 SELECT sqlite_version() 또는 드라이버가 알려 주는 엔진 버전을 확인해야 한다.

Python의 sqlite3.sqlite_version은 포함된 SQLite 엔진 버전을 확인하는 데 쓰인다. Python 언어 버전과 같은 숫자가 아니며 운영체제 패키징에 따라 달라질 수 있다. STRICT 테이블이나 UPSERT, 창 함수처럼 버전 조건이 있는 기능을 사용할 때는 배포 대상의 최소 버전도 검토한다. 이 문서의 예제 검증 기록에는 실제 테스트 엔진 버전을 따로 남겼다.

CLI의 .tables, .schema 같은 점 명령은 SQL 문법이 아니라 셸 프로그램이 처리하는 명령이다. 이를 드라이버의 execute에 그대로 전달하면 SQL 오류가 날 수 있다. GUI 도구가 제공하는 편의 기능도 엔진 기능과 구별한다. 문제 보고에는 도구 이름과 버전, 실제 SQL, 데이터 파일의 위치, 저널 모드와 오류 코드를 함께 적으면 원인을 좁히기 쉽다.

인메모리 데이터베이스: 연결이 다르면 자료도 다르다

일반적인 :memory: 연결은 해당 연결에 속한 임시 데이터베이스를 만든다. 연결을 닫으면 그 데이터베이스는 사라진다. 테스트에 편리하지만 파일처럼 다른 연결에서 같은 이름으로 다시 열어 같은 자료를 볼 수 있다고 생각하면 안 된다. 공유 인메모리 구성에는 별도의 URI와 설정이 있으며 일반 :memory: 예제와 구별해야 한다.

아래는 a와 b라는 두 연결을 각각 :memory:로 연다. a에 만든 notes 테이블은 b에 없으므로 a의 건수는 1, b의 조회는 no shared table로 출력된다. 이 동작을 모르고 연결 풀의 각 연결에서 같은 테스트 데이터를 기대하면 간헐적으로 테이블이 없다는 오류가 발생할 수 있다.

인메모리 테스트는 파일 잠금, 디스크 오류, WAL과 복구 경로를 재현하지 않는다. 데이터 처리 로직은 빠르게 확인할 수 있지만 운영 신뢰성을 검증하려면 임시 파일 기반 테스트와 다중 연결 테스트도 필요하다. 테스트 속도와 현실성을 모두 한 종류의 데이터베이스로 해결하려 하지 않는다.

예제 코드

import sqlite3
a = sqlite3.connect(":memory:")
b = sqlite3.connect(":memory:")
try:
    a.execute("CREATE TABLE notes (title TEXT)")
    a.execute("INSERT INTO notes VALUES ('draft')")
    print(a.execute("SELECT COUNT(*) FROM notes").fetchone()[0])
    try:
        b.execute("SELECT COUNT(*) FROM notes")
    except sqlite3.OperationalError:
        print("no shared table")
finally:
    a.close()
    b.close()

실행 결과

1
no shared table

기본 SQL 실습: 재고표와 제약

아래 SQL은 새 데이터베이스에 stock을 만들고 공책과 펜의 수량을 저장한다. id는 INTEGER PRIMARY KEY로 행을 구별하고 name과 qty는 NULL을 허용하지 않는다. qty의 CHECK는 음수 재고를 거부한다. 조회 결과는 pen과 7 한 행이다.

WHERE는 수량 조건을 고르고 ORDER BY는 결과 순서를 정한다. 두 행을 입력한 순서가 모든 후속 조회 순서를 보장하지는 않는다. 여러 행이 같은 수량일 때도 안정적인 결과 순서가 필요하면 고유 식별자를 정렬 기준에 포함한다.

이 스키마는 설명용이며 제품 이름의 유일성이나 단위, 입출고 이력은 다루지 않는다. 같은 이름의 상품을 허용할지, 재고를 직접 덮어쓸지 거래 이력으로 계산할지에 따라 설계가 달라진다. 데이터베이스가 제공하는 제약과 업무 규칙을 함께 정의해야 SQL 실행 성공을 올바른 재고 상태로 해석할 수 있다.

예제 코드

CREATE TABLE stock (id INTEGER PRIMARY KEY, name TEXT NOT NULL, qty INTEGER NOT NULL CHECK (qty >= 0));
INSERT INTO stock VALUES (1, 'notebook', 3), (2, 'pen', 7);
SELECT name, qty FROM stock WHERE qty >= 5 ORDER BY id;

실행 결과

pen | 7

동적 자료형과 타입 선호도

일반 SQLite 테이블에서 열의 선언 타입은 값의 저장 형식을 유도하는 선호도와 연결된다. INTEGER라고 선언한 열도 변환할 수 없는 문자열을 무조건 거부하는 것은 아니다. 다음 예제는 정수 열에 문자열 12와 unknown을 넣는다. 12는 정수로 저장되고 unknown은 텍스트로 남아 typeof 결과가 각각 integer와 text다.

이 동작은 다른 DBMS의 엄격한 열 타입 처리에 익숙한 개발자가 자주 놓치는 차이다. 같은 열에 숫자와 텍스트가 섞이면 비교와 정렬의 의미가 기대와 달라질 수 있다. 자료형 검증을 ORM이 대신 해 준다고 가정하지 말고 실제 저장된 값의 typeof와 조건 결과를 확인한다.

유연한 저장이 필요한 데이터도 있지만 수량이나 상태처럼 엄격한 형식이 필요한 열은 별도 제약이나 STRICT 테이블을 검토한다. SQLite가 자료형이 없다는 뜻은 아니다. 값에는 저장 클래스가 있고 표현식의 변환과 비교에도 규칙이 있다. “선언 타입이 입력을 얼마나 제한하는가”를 정확하게 이해해야 한다.

예제 코드

CREATE TABLE samples (value INTEGER);
INSERT INTO samples VALUES ('12'), ('unknown');
SELECT value, typeof(value) FROM samples ORDER BY rowid;

실행 결과

12 | integer
unknown | text

STRICT 테이블: 더 엄격한 입력을 원할 때

STRICT 테이블은 지원되는 버전에서 더 엄격한 자료형 처리를 선택하는 기능이다. SQLite 3.37.0부터 제공되므로 실행 환경의 버전을 확인한다. 모든 타입 이름을 마음대로 선언할 수 있는 방식이 아니며 허용된 타입과 변환 규칙을 따른다. 일반 테이블의 동작을 바꾸는 전역 스위치가 아니라 테이블 정의의 선택이다.

아래는 엄격한 INTEGER 열에 unknown을 넣으려는 실패 예제다. 유효한 정수로 저장할 수 없으므로 오류가 나야 한다. 반면 문자열로 전달된 숫자처럼 손실 없이 변환 가능한 값은 타입에 맞게 변환될 수 있다. STRICT를 “호출자가 정확히 같은 언어 타입의 객체를 넘겨야 한다”는 규칙으로 이해하지 않는다.

STRICT도 업무 범위를 모두 검증하지는 않는다. 음수를 금지하려면 CHECK가 필요하고 필수 값이면 NOT NULL을 명시한다. 금액의 통화 단위, 날짜의 실제 유효성, 문자열의 허용 목록 같은 조건은 별도 설계 대상이다. 다른 DBMS와 동일한 타입 체계가 되는 것은 아니므로 이식성 테스트도 여전히 필요하다.

실패해야 하는 예제

CREATE TABLE strict_samples (value INTEGER) STRICT;
INSERT INTO strict_samples VALUES ('unknown');

기본 키와 rowid: 자동 번호의 의미

일반 rowid 테이블에는 각 행을 찾는 정수 rowid가 있다. 정확한 INTEGER PRIMARY KEY 선언은 일반적인 경우 이 rowid의 별칭이 된다. 단순히 이름에 INT가 들어간 모든 타입 선언이나 모든 PRIMARY KEY가 같은 방식이라고 생각하면 안 된다. 테이블 정의의 형태와 WITHOUT ROWID 여부를 구분한다.

자동 번호를 사용하더라도 ID가 업무상 연속된 순번이나 빠짐없는 건수를 의미하지는 않는다. 삭제와 실패, 명시적 ID 삽입 등으로 빈 구간이 생길 수 있다. 마지막 ID만 보고 실제 행 수를 계산하지 않고 COUNT를 사용해야 한다. AUTOINCREMENT는 기본 자동 rowid 선택에 추가 규칙과 비용을 부여하는 기능이며, 모든 기본 키에 반드시 붙여야 하는 장식이 아니다.

WITHOUT ROWID는 기본 키 중심으로 저장 구조를 다르게 구성하는 선택지다. 복합 키나 특정 데이터 형태에서 효용을 검토할 수 있지만 rowid 관련 API와 일부 동작이 달라진다. 성능을 높이는 보편적인 스위치로 사용하지 않고 실제 스키마와 질의에서 공간·읽기·쓰기 비용을 비교한다. 키가 바뀔 수 있는 표시 이름보다 안정적인 식별자를 사용하는 이유도 함께 생각해야 한다.

외래 키: 선언과 활성화를 함께 확인

외래 키는 자식 행이 가리키는 부모 행의 존재를 검사한다. SQLite에서는 연결에서 외래 키 검사가 켜져 있는지 확인해야 한다. 트랜잭션 도중 PRAGMA foreign_keys를 바꾸려 해도 기대한 적용이 되지 않을 수 있으므로 연결 초기화 시점에 설정하고 조회 결과를 확인한다. 연결이 여러 개면 각 연결을 초기화한다.

아래는 외래 키를 켠 새 연결에 부모 1을 만들고 존재하지 않는 부모 9를 참조하는 자식 행을 넣는다. enabled 값은 1이고 삽입은 IntegrityError가 되어 rejected로 출력된다. 부모 테이블과 외래 키 선언이 존재한다는 사실만 보지 않고 실제 거부 동작까지 확인하는 테스트다.

삭제나 갱신 시 연결된 자식을 어떻게 처리할지도 결정해야 한다. CASCADE는 편리할 수 있지만 부모 삭제가 많은 자식 삭제로 이어질 수 있고, RESTRICT나 NO ACTION은 오류 처리 경로가 필요하다. 외래 키 열의 인덱스는 검사와 관련 질의의 비용에 영향을 주므로 실제 작업 패턴에 맞게 검토한다.

예제 코드

import sqlite3
connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    connection.execute("PRAGMA foreign_keys = ON")
    connection.executescript("CREATE TABLE parent (id INTEGER PRIMARY KEY); CREATE TABLE child (parent_id INTEGER REFERENCES parent(id)); INSERT INTO parent VALUES (1);")
    print(connection.execute("PRAGMA foreign_keys").fetchone()[0])
    try:
        connection.execute("INSERT INTO child VALUES (9)")
    except sqlite3.IntegrityError:
        print("rejected")
finally:
    connection.close()

실행 결과

1
rejected

트랜잭션과 자동 커밋: 드라이버의 경계

SQLite에서 데이터 접근은 트랜잭션 안에서 처리된다. 명시적으로 BEGIN을 쓰지 않아도 필요한 시점에 자동 트랜잭션이 생길 수 있다. 여기에 드라이버가 자체적으로 BEGIN이나 COMMIT을 관리하면 애플리케이션 코드에서 보이는 경계가 달라진다. 엔진의 규칙과 드라이버의 기본 설정을 함께 확인해야 한다.

아래 SQL은 명시적 트랜잭션 안에서 값 7을 5로 바꾸고 취소한다. 첫 조회는 같은 연결의 변경값 5, 취소 뒤 조회는 7이다. 이 예제에서 ROLLBACK은 현재 트랜잭션의 변경을 취소하며 이미 예전에 커밋된 모든 작업을 되돌리는 기능은 아니다. 데이터베이스 파일 삭제나 외부 API 호출 같은 부수 효과도 자동으로 취소하지 않는다.

Python 예제에서 isolation_level=None을 명시한 경우에는 드라이버가 전통적인 방식의 암묵적 BEGIN을 자동으로 추가하지 않게 하여 SQL로 경계를 설명했다. Python의 autocommit API와 기본값은 버전에 따라 확인해야 한다. 서로 다른 트랜잭션 관리 방식을 섞으면 “이미 트랜잭션 안이다” 오류나 의도보다 이른 확정이 생길 수 있다.

예제 코드

CREATE TABLE counter (value INTEGER NOT NULL);
INSERT INTO counter VALUES (7);
BEGIN;
UPDATE counter SET value = 5;
SELECT value FROM counter;
ROLLBACK;
SELECT value FROM counter;

실행 결과

5

7

SAVEPOINT: 큰 작업의 일부를 되돌리기

BEGIN으로 시작한 트랜잭션 안에서 다시 BEGIN을 중첩하여 독립적인 트랜잭션을 만들 수는 없다. 부분 작업을 되돌릴 기준점이 필요하면 SAVEPOINT를 사용한다. ROLLBACK TO는 해당 지점 이후 변경을 되돌리고, RELEASE는 그 저장점을 해제한다. 안쪽 RELEASE가 바깥 트랜잭션까지 항상 디스크에 확정한다는 의미는 아니다.

아래는 값 10을 20으로 바꾼 뒤 저장점을 만들고 30으로 변경한다. 저장점으로 되돌린 후 조회하면 20이다. 바깥 트랜잭션까지 ROLLBACK하면 원래 10이 된다. 출력 두 값을 비교하면 부분 취소와 전체 취소의 차이가 드러난다.

배치 처리에서 일부 항목 실패만 취소하고 나머지를 계속할지, 하나라도 실패하면 전체를 취소할지는 업무 정책이다. SAVEPOINT를 사용한다고 실패 정책이 저절로 정해지지 않는다. 이름을 재사용하는 중첩 구조는 읽기 어려울 수 있으므로 각 작업의 경계와 오류 처리 후 상태를 문서화한다.

예제 코드

CREATE TABLE counter (value INTEGER);
INSERT INTO counter VALUES (10);
BEGIN;
UPDATE counter SET value = 20;
SAVEPOINT item;
UPDATE counter SET value = 30;
ROLLBACK TO item;
RELEASE item;
SELECT value FROM counter;
ROLLBACK;
SELECT value FROM counter;

실행 결과

20

10

쓰기 경쟁과 BEGIN IMMEDIATE

SQLite는 여러 연결의 읽기를 지원하지만 같은 데이터베이스에 동시에 진행되는 쓰기 트랜잭션은 하나다. 쓰기 작업이 짧고 빈도가 적절하면 이것이 곧바로 문제가 되는 것은 아니다. 그러나 트랜잭션을 길게 유지하거나 많은 요청이 동시에 쓰려고 하면 대기와 SQLITE_BUSY가 중요한 운영 지표가 된다.

BEGIN DEFERRED는 기본적으로 실제 접근 시점까지 일부 동작을 미루고, 읽기로 시작한 뒤 쓰기로 전환할 때 경쟁을 만날 수 있다. BEGIN IMMEDIATE는 처음부터 쓰기 트랜잭션을 확보하려 시도한다. 이 방식은 뒤늦은 전환 실패를 피하는 데 도움이 될 수 있지만 다른 작성자가 있으면 시작 단계에서 실패하거나 기다릴 수 있다. 경쟁을 없애는 명령이 아니라 실패 시점을 다루는 선택이다.

다음 Python 예제는 임시 파일에 연결 두 개를 열어 첫 연결이 BEGIN IMMEDIATE를 유지하게 한다. 두 번째 연결은 대기 시간을 0으로 설정했으므로 바로 쓰기 잠금 경쟁을 보고한다. 첫 연결이 ROLLBACK한 뒤 두 번째 쓰기는 성공한다. 출력은 busy와 1이다. 이 예제의 OperationalError 처리는 의도적으로 잠금 상황만 만든 실험이며, 실제 서비스에서는 오류 코드별로 원인을 구분해야 한다.

예제 코드

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory
with TemporaryDirectory() as directory:
    path = str(Path(directory) / "test.db")
    first = sqlite3.connect(path, isolation_level=None, timeout=0)
    second = sqlite3.connect(path, isolation_level=None, timeout=0)
    try:
        first.execute("CREATE TABLE events (id INTEGER)")
        first.execute("BEGIN IMMEDIATE")
        try:
            second.execute("INSERT INTO events VALUES (1)")
        except sqlite3.OperationalError:
            print("busy")
        first.execute("ROLLBACK")
        second.execute("INSERT INTO events VALUES (1)")
        print(second.execute("SELECT COUNT(*) FROM events").fetchone()[0])
    finally:
        first.close()
        second.close()

실행 결과

busy
1

busy timeout과 재시도: 기다리기만 하면 해결되는가

busy timeout은 잠금이 풀릴 때까지 기다릴 기회를 주는 설정이다. 짧은 경쟁을 흡수할 수 있지만 긴 트랜잭션이나 구조적인 쓰기 병목을 없애지는 않는다. 무한정 재시도하거나 대기 시간을 크게 늘리면 사용자는 긴 지연을 겪고 작업이 쌓일 수 있다. 응답 시간 목표와 최대 시도 횟수, 취소 조건을 함께 정한다.

이미 읽은 스냅샷을 쓰기로 전환할 수 없는 경우처럼 같은 트랜잭션에서 명령 한 줄을 반복하는 것만으로 해결되지 않는 상황도 있다. 오류의 종류에 따라 트랜잭션 전체를 끝내고 새 상태에서 다시 읽고 계산해야 할 수 있다. 재시도 함수가 무엇을 다시 실행하는지 명확히 해야 한다.

재시도 가능한 작업은 중복 실행의 의미도 정해야 한다. 예를 들어 “조회 수를 1 증가”와 “요청 ID가 같은 처리 결과를 한 번만 등록”은 다른 계약이다. 네트워크 응답을 받기 전에 연결이 끊기면 실제 커밋 여부를 모를 수 있으므로 고유 요청 키와 중복 처리 방지 설계가 도움이 된다. 대기 시간을 조정하기 전에 트랜잭션 안의 파일 읽기나 외부 요청을 줄일 수 있는지도 살핀다.

rollback journal과 원자적 커밋

기본적인 rollback journal 방식은 변경 전에 필요한 이전 내용을 저널에 보관하여 실패 시 되돌릴 수 있게 한다. 원자적 커밋은 전원이 꺼지는 등의 실패에도 중간 변경만 보이는 상태를 피하려는 장치다. 그 보장은 파일 시스템과 저장 장치의 동기화·잠금 동작 같은 전제와 설정에 의존한다.

성능을 이유로 동기화나 저널을 끄는 설정을 복사해 넣으면 장애 시 데이터 손상이나 확정된 변경 유실 위험이 달라질 수 있다. 빠른 벤치마크 숫자만 보고 운영 설정으로 채택하지 않고 어떤 실패를 감수하는 설정인지 공식 문서를 확인한다. SQLite를 쓰는 앱의 전원 중단 패턴과 저장 장치 특성도 고려해야 한다.

트랜잭션의 원자성과 백업은 다른 문제다. 원자적으로 저장된 파일이라도 사용자가 실수로 행을 삭제하거나 장치가 분실되면 복구할 수 없을 수 있다. 저널이 있다는 이유로 별도 백업과 복구 검증을 생략하지 않는다. 오래된 백업을 실제로 열어 필요한 자료가 있는지 확인하는 절차가 필요하다.

WAL: 읽기와 쓰기의 공존, 그리고 한계

WAL 모드에서는 변경을 주 데이터 파일에 곧바로 덮어쓰는 대신 로그에 기록하고, 읽는 쪽은 자신에게 해당하는 일관된 상태를 본다. 읽기와 쓰기가 함께 진행되는 상황을 개선할 수 있지만 동시에 여러 쓰기 트랜잭션을 허용하는 기능은 아니다. 주 파일 외에 WAL과 공유 메모리 파일을 함께 관리하는 운영 구조가 된다.

오래 지속되는 읽기 트랜잭션은 과거 상태를 유지해야 하므로 체크포인트가 모든 내용을 회수하는 데 영향을 줄 수 있다. 읽기 전용이라고 아무 비용이 없다고 생각해 연결과 커서를 장시간 방치하면 로그 파일 증가와 지연 원인이 될 수 있다. 데이터 크기뿐 아니라 트랜잭션 지속 시간을 관찰한다.

WAL을 켜는 것과 백업·복구 정책을 준비하는 것은 별도다. PRAGMA journal_mode=WAL의 실제 반환값을 확인하고 실행 환경에서 모드가 적용되었는지 검증한다. 네트워크 파일 시스템이나 읽기 전용 배포 환경, 여러 데이터베이스를 ATTACH하여 함께 바꾸는 작업의 원자성 범위도 확인한다. 모든 프로젝트에 동일한 PRAGMA 묶음을 적용하는 대신 필요한 동시성과 장애 조건에서 선택한다.

UPSERT와 REPLACE: 충돌 처리의 의미가 다르다

중복 키가 있을 때 기존 값을 갱신하고 싶다면 UPSERT의 ON CONFLICT ... DO UPDATE를 검토할 수 있다. 아래는 visits의 name이 이미 있으면 현재 횟수에 새로 들어온 횟수를 더한다. 첫 저장값 2에 추가값 3을 더해 pen·5가 출력된다. excluded는 삽입하려던 행의 값을 참조한다.

INSERT OR REPLACE는 단순히 UPDATE의 다른 철자가 아니다. 유일성 충돌을 해결하는 과정에서 기존 행을 삭제하고 새 행을 넣는 의미가 있어 외래 키, 트리거와 행 식별에 미치는 영향이 다를 수 있다. 객체의 “같은 행을 수정한다”는 의도를 표현하려고 무심코 사용하면 예상치 못한 결과를 낳을 수 있다.

UPSERT도 어떤 제약 충돌을 대상으로 하는지, 변경 후 다른 제약을 위반하면 어떻게 되는지 확인해야 한다. 중복된 요청을 무시할지, 합산할지, 기존 값을 덮어쓸지는 업무적으로 다른 정책이다. 방문 수처럼 합산하는 예제를 주문 생성의 중복 처리에 그대로 적용하면 같은 요청을 여러 번 처리할 수 있다.

예제 코드

CREATE TABLE visits (name TEXT PRIMARY KEY, count INTEGER NOT NULL);
INSERT INTO visits VALUES ('pen', 2);
INSERT INTO visits VALUES ('pen', 3)
ON CONFLICT(name) DO UPDATE SET count = visits.count + excluded.count;
SELECT name, count FROM visits;

실행 결과

pen | 5

매개변수와 입력 검증

사용자 입력은 SQL 문자열에 직접 이어 붙이지 않고 드라이버의 매개변수 바인딩을 사용한다. Python sqlite3에서는 ?나 이름 있는 플레이스홀더를 사용할 수 있다. 바인딩은 값과 SQL 문법을 분리하여 따옴표가 포함된 이름도 데이터로 전달하게 한다. 수동으로 따옴표를 교체하는 방식과 섞어 쓰지 않는다.

바인딩은 테이블 이름과 열 이름, 정렬 방향 같은 SQL 구조를 일반적인 데이터 값처럼 선택하는 기능은 아니다. 사용자가 정렬 열을 고르는 API는 허용한 열 이름에서 선택하는 식으로 구조를 제한해야 한다. 또 음수 수량이나 과도하게 긴 문자열처럼 업무적으로 잘못된 값은 바인딩을 해도 데이터로 전달되므로 별도 검증이 필요하다.

prepared statement나 드라이버의 문장 캐시가 사용될 수 있지만 같은 문장을 바인딩했다고 모든 입력에서 같은 계획과 성능이 보장되는 것은 아니다. 입력 분포와 결과 크기를 고려한다. 파일 기반 DB라는 이유로 앱 내부에서만 믿을 수 있는 자료가 들어온다고 가정하지 않고, 가져오기 파일과 동기화 데이터의 경계도 검증한다.

인덱스와 EXPLAIN QUERY PLAN

SQLite의 계획기는 조건과 정렬을 만족시키기 위한 접근 방법을 선택한다. name으로 자주 조회하는 큰 테이블에서는 해당 열의 인덱스를 검토하고, 여러 조건을 함께 쓰면 복합 인덱스의 열 순서와 선택도를 살핀다. 인덱스는 추가 저장 공간을 차지하고 쓰기 때 갱신되므로 모든 열에 만드는 방식은 적절하지 않다.

EXPLAIN QUERY PLAN은 전체 스캔인지 인덱스를 통한 검색인지, 정렬용 임시 구조가 필요한지 등을 읽는 데 도움이 된다. 출력 형식은 버전에 따라 바뀔 수 있으므로 그 문자열을 애플리케이션의 영구 API처럼 파싱하여 의존하지 않는다. 계획의 문구보다 실제 접근 범위와 결과 행 수, 실행 시간을 함께 본다.

작은 실습 테이블은 인덱스를 사용하지 않아도 빠르다. 그 결과를 대규모 자료에 그대로 적용할 수 없다. 대표적인 값 분포를 넣고 읽기·쓰기 비율과 캐시 상태를 통제하여 비교한다. 질의가 잘못된 조인으로 행을 증폭시키는 경우에는 인덱스를 추가하기 전에 의미를 바로잡아야 한다. 단일 파일이라는 물리적 형태가 알고리즘과 자료 접근 비용의 중요성을 줄이지는 않는다.

스키마 변경과 마이그레이션

앱이 업데이트되면 테이블과 인덱스 구조도 바뀔 수 있다. SQLite의 ALTER TABLE은 지원하는 변경 종류와 제약이 있으므로 서버형 DBMS의 모든 마이그레이션 문법을 그대로 사용할 수 있다고 가정하지 않는다. 복잡한 변경은 새 테이블을 만들고 자료를 옮기는 절차가 필요할 수 있다.

마이그레이션에는 기존 자료가 새 제약을 만족하는지, 연결된 외래 키와 트리거·뷰·인덱스를 어떻게 유지할지, 중간 실패 시 어떤 상태로 돌아갈지가 포함된다. “열 추가 SQL이 실행됐다”만 확인하지 않고 이전 버전의 실제 자료를 넣어 업그레이드하고 핵심 조회와 제약을 검증해야 한다. 사용자 파일을 장기간 보관하는 앱일수록 오래된 스키마에서 한 번에 이동하는 경로가 중요하다.

스키마 버전을 앱에서 관리한다면 파일 안의 버전과 코드가 기대하는 버전을 비교하고 중복 실행을 방지한다. PRAGMA user_version 같은 기능을 사용할 수 있지만 엔진이 애플리케이션 마이그레이션을 자동으로 구현해 주는 것은 아니다. 운영 파일을 변경하기 전에 복구 가능한 백업을 준비하고, 마이그레이션 후 새 파일을 구버전 앱이 열 수 있는지도 배포 정책에 맞게 판단한다.

백업 API: 복사본이 열리는지까지 확인하기

SQLite는 실행 중인 데이터베이스를 일관된 대상으로 복사하기 위한 백업 API를 제공한다. 주 파일을 파일 시스템에서 임의로 복사하는 작업과 목적이 다르다. WAL에 아직 반영 중인 상태나 다른 연결의 변경이 있을 수 있는 환경에서는 지원되는 백업 경로를 사용하고 완료 상태를 확인한다.

아래 Python 예제는 인메모리 원본에 memo 한 행을 커밋하고 backup을 이용해 별도의 인메모리 대상에 복사한다. 대상에서 조회해 draft를 얻는 것으로 복사 결과를 확인한다. 인메모리 대상을 사용했으므로 이 예제 자체는 연결 종료 후에도 남는 영구 백업이 아니다. 실제 백업에서는 목적지 파일과 보관 위치, 접근 권한, 보존 기간을 정해야 한다.

백업 성공 로그만 남기고 복구를 시험하지 않으면 필요한 순간에 파일이 비어 있거나 앱 버전이 맞지 않는 문제를 발견할 수 있다. 복구 테스트는 파일 열기, 무결성 검사, 핵심 테이블의 건수와 대표 자료, 앱의 실제 조회 동작을 포함한다. 백업에 비밀 정보가 포함되면 원본과 별도로 보호해야 한다. 기본 SQLite 파일이 자동으로 암호화되어 있다고 가정하지 않는다.

예제 코드

import sqlite3
source = sqlite3.connect(":memory:")
target = sqlite3.connect(":memory:")
try:
    source.execute("CREATE TABLE memo (title TEXT NOT NULL)")
    source.execute("INSERT INTO memo VALUES (?)", ("draft",))
    source.commit()
    source.backup(target)
    print(target.execute("SELECT title FROM memo").fetchone()[0])
finally:
    source.close()
    target.close()

실행 결과

draft

무결성 검사와 장애 대응

PRAGMA integrity_check는 데이터베이스의 구조적 무결성을 검사하는 도구다. 정상 상태에서 ok를 얻을 수 있지만 이것이 모든 업무 자료가 올바르다는 증명은 아니다. 잘못된 수량이나 중복 처리처럼 SQL 규칙에 맞게 저장된 논리 오류는 별도 검증이 필요하다. 외래 키 검사는 foreign_key_check 같은 별도의 검사를 고려한다.

앱에서 database is locked나 malformed 같은 오류를 같은 일반 오류로 덮지 않는다. 전자는 잠금 경쟁과 트랜잭션 길이를 조사할 단서가 될 수 있고, 후자는 파일 상태와 저장 장치·복사 방식 등을 조사해야 할 수 있다. 정확한 오류 코드와 실패한 작업, 파일 위치, 동시 연결 수를 기록하되 민감한 SQL 매개변수를 로그에 무분별하게 남기지 않는다.

파일 문제가 의심될 때는 원본을 보존하고 복구 작업을 복사본에서 수행하는 것이 도움이 된다. 즉시 파일을 삭제하고 재생성하는 방식은 재생 가능한 캐시에는 맞을 수 있지만 사용자 원본 자료에는 부적절하다. 데이터가 캐시인지 유일한 원본인지에 따라 장애 대응 정책을 처음부터 구분한다. 무결성 검사와 백업, 운영 로그는 서로 대체하는 기능이 아니다.

예제 코드

CREATE TABLE sample (id INTEGER PRIMARY KEY, value TEXT);
INSERT INTO sample VALUES (1, 'ok');
PRAGMA integrity_check;

실행 결과

ok

실전 예제: 재고가 있을 때만 차감하기

“재고를 SELECT로 읽고 1 이상이면 나중에 UPDATE한다”는 분리된 절차는 다른 요청이 사이에 재고를 바꾸는 상황을 고려해야 한다. 아래는 조건을 UPDATE 자체에 포함하여 qty가 충분한 경우에만 차감한다. 영향받은 행 수를 확인해 성공 여부를 구별한다. 여기서는 고정된 요청량을 사용하며 외부 입력에서는 양의 정수라는 검증을 먼저 해야 한다.

처음 재고는 3이다. 2개 차감은 성공하여 True와 남은 재고 1이 출력된다. 다시 2개 차감하려 하면 WHERE qty >= 2를 만족하지 않아 False와 1이 출력된다. 음수 재고를 만들었다가 나중에 검사하는 구조가 아니라 변경 문장의 조건으로 허용 범위를 표현한 것이다.

실제 주문 처리에는 주문 레코드와 결제 상태, 요청의 중복 여부도 있다. 재고 차감과 주문 등록이 함께 원자적이어야 하면 같은 트랜잭션에 묶고, 외부 결제와의 경계는 별도 정책을 설계한다. 이 예제의 rowcount 검사는 단일 키 UPDATE의 기대를 확인하는 용도다. 일반적인 모든 질의에서 rowcount가 전체 결과 건수를 뜻한다고 가정하지 않는다.

예제 코드

import sqlite3
connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    connection.execute("CREATE TABLE stock (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL CHECK (qty >= 0))")
    connection.execute("INSERT INTO stock VALUES (1, 3)")
    for requested in [2, 2]:
        cursor = connection.execute("UPDATE stock SET qty = qty - ? WHERE id = ? AND qty >= ?", (requested, 1, requested))
        remaining = connection.execute("SELECT qty FROM stock WHERE id = 1").fetchone()[0]
        print(cursor.rowcount == 1, remaining)
finally:
    connection.close()

실행 결과

True 1
False 1

애플리케이션 통합과 연결 수명

데이터베이스 연결을 요청마다 만들지, 제한된 수로 재사용할지는 앱의 동작과 드라이버에 따라 판단한다. 연결을 재사용하면 설정과 열린 트랜잭션이 다음 작업으로 넘어가지 않도록 정리해야 한다. 요청이 실패했을 때 롤백하지 않고 연결을 돌려주면 이후 작업이 예상하지 못한 상태에서 시작할 수 있다.

스레드 간 연결 공유는 SQLite의 빌드 옵션과 드라이버 제한, 애플리케이션의 동기화 정책을 함께 확인한다. 드라이버의 스레드 검사 옵션을 끄는 것이 모든 경쟁을 안전하게 해결하는 것은 아니다. 같은 연결에서 작업을 섞을 때 트랜잭션 경계가 누구에게 속하는지 모호해질 수 있다. 필요하면 작업을 한 소유 실행 흐름으로 모으거나 연결별 책임을 분리한다.

커서가 결과를 얼마나 가져오는지도 성능에 영향을 준다. fetchall로 큰 결과를 한 번에 만들면 DB 파일이 작아도 애플리케이션 메모리가 커질 수 있다. 필요한 열과 행을 SQL에서 제한하고, 대용량 처리에는 점진적인 읽기와 일괄 쓰기를 검토한다. 일괄 처리의 크기를 늘리는 것은 커밋 비용을 줄일 수 있지만 트랜잭션 지속 시간과 장애 시 재처리량도 늘릴 수 있다.

날짜·금액·문자열 비교의 설계

SQLite에는 날짜와 시간을 표현하는 여러 방식이 있으며, 문자열·숫자 표현과 함수를 선택할 수 있다. 프로젝트에서 형식과 시간대, 정밀도를 통일하지 않으면 정렬이나 기간 조회가 잘못될 수 있다. 예를 들어 서로 다른 시간대 오프셋이 붙은 문자열을 단순 사전순으로 정렬하는 것이 실제 시각 순서와 항상 같다고 가정하지 않는다.

금액도 REAL에 넣었다는 이유만으로 십진 계산이 정확해지는 것은 아니다. 최소 단위 정수로 저장할지, 문자열이나 별도 처리로 십진 의미를 유지할지, 계산을 어디에서 수행할지 결정한다. 입력 금액과 수량의 범위, 곱셈과 합계의 오버플로 가능성도 점검한다. Python의 큰 정수가 엔진의 INTEGER 저장 범위와 같다고 생각하면 바인딩 단계에서 오류가 생길 수 있다.

문자열 정렬과 비교는 collation과 함수의 범위에 영향을 받는다. 모든 언어의 대소문자나 유니코드 정규화가 기대대로 처리된다고 가정하지 않는다. 사용자 이름의 유일성, 검색어 일치, 파일 경로 비교처럼 업무적으로 민감한 부분은 대표적인 문자와 조합을 넣어 검증한다. 자료형을 정하는 일은 저장할 수 있는지를 넘어 비교와 계산의 의미를 정하는 작업이다.

언제 적합하고 언제 다른 DBMS를 비교할까

SQLite는 앱과 데이터가 같은 기기에서 관리되고 운영 부담을 줄이고 싶을 때 자연스러운 선택이다. 개인 기록, 오프라인 기능, 배포용 데이터 파일, 재생 가능한 캐시와 테스트 환경에서 유용할 수 있다. 작은 웹 서비스에서도 실제 읽기·쓰기 패턴과 운영 요구에 맞으면 사용할 수 있으므로 단순한 사용자 수만으로 적합성을 결정하지 않는다.

여러 호스트가 공통 데이터에 빈번하게 쓰고, DB 서버의 중앙 계정·권한 관리, 복제와 고가용성 운영 기능이 필요하면 서버형 DBMS를 비교한다. “파일 한 개라 간단하다”는 장점이 “모든 운영 요구를 앱에서 해결해야 한다”는 비용으로 바뀌는 지점을 살펴야 한다. SQLite를 감싼 별도 서비스나 확장 제품의 기능을 SQLite 엔진 자체의 기본 기능과 혼동하지 않는다.

테스트는 SQLite로 하고 운영은 다른 DBMS로 하는 구성에서는 자료형, 제약, SQL 방언과 동시성 차이를 검증해야 한다. 단위 테스트가 모두 통과해도 실제 운영 DB의 잠금이나 격리 동작을 대변하지 않을 수 있다. 제품 이름의 우열보다 데이터의 소유 위치, 동시 쓰기 패턴, 장애 복구 목표, 배포와 유지보수 비용을 기준으로 선택한다.

운영 전에 확인할 흐름

먼저 실제 드라이버가 사용하는 엔진 버전과 파일 경로를 확인한다. 연결마다 필요한 외래 키와 대기 정책을 초기화하고, 스키마의 필수 값·범위·유일성을 명시한다. 읽기와 쓰기의 경계를 짧게 유지하면서 오류 시 트랜잭션을 정리하는지 확인한다. 이 단계는 특정 PRAGMA 목록을 복사하는 것보다 앱의 자료 수명과 업무 규칙을 정하는 작업이다.

다음으로 정상 경로뿐 아니라 잠금 경쟁, 잘못된 자료형, 외래 키 위반, 디스크 공간 부족, 앱 중단, 백업 복원과 구버전 파일 업그레이드를 검토한다. 이 문서의 작은 예제는 일부 경계를 직접 관찰하기 위한 것이며 모든 운영 장애를 시험한 것은 아니다. WAL과 동기화 설정을 바꾼 경우에는 기대하는 내구성과 장애 조건이 유지되는지 별도 검증한다.

마지막으로 느린 작업을 관찰할 수 있게 질의 시간과 트랜잭션 지속 시간, 실패 유형을 기록한다. 작은 파일의 대기 문제가 반드시 느린 SQL 때문인 것은 아니며 오래 잡힌 트랜잭션이 원인일 수도 있다. 반대로 잠금 대기 시간을 늘리는 것으로 잘못된 조인이나 전체 스캔이 해결되지는 않는다. 자료 모델·질의 계획·연결 관리·파일 운영을 함께 보아야 원인에 맞는 개선을 할 수 있다.

참고 자료