모던 SQLite: 당신이 몰랐던 기능들
Source: Hacker News
JSON 데이터 작업하기
SQLite는 JSON 확장을 제공하여 테이블에 JSON 문서를 직접 저장하고 조회할 수 있습니다. 스키마를 유연하게 유지하면서도 SQL을 사용해 구조화된 데이터를 자유롭게 다룰 수 있습니다.
예시: JSON 열에서 필드 추출
CREATE TABLE events (
id INTEGER PRIMARY KEY,
payload TEXT NOT NULL -- JSON
);
SELECT
json_extract(payload, '$.user.id') AS user_id,
json_extract(payload, '$.action') AS action,
json_extract(payload, '$.metadata') AS metadata
FROM events
WHERE json_extract(payload, '$.action') = 'login';JSON 표현식에 인덱스를 만들 수도 있어, 반구조화 데이터에 대한 쿼리가 놀라울 정도로 빠르게 수행됩니다.
FTS5를 이용한 전체 텍스트 검색
SQLite의 FTS5 확장은 이를 강력한 전체 텍스트 검색 엔진으로 변환합니다. 외부 검색 서비스를 별도로 도입할 필요 없이 모든 데이터를 하나의 데이터베이스 파일에 보관할 수 있습니다.
예시: 간단한 검색 인덱스 만들기
CREATE VIRTUAL TABLE docs USING fts5(
title,
body,
tokenize = "porter"
);
INSERT INTO docs (title, body) VALUES
('SQLite Guide', 'Learn how to use SQLite effectively.'),
('Local-first Apps', 'Why local storage and sync matter.');
SELECT rowid, title
FROM docs
WHERE docs MATCH 'local NEAR/5 storage';랭킹, 구문 쿼리, 접두사 검색 등 다양한 기능을 SQLite 안에서 바로 사용할 수 있어 별도의 서비스 관리가 필요 없습니다.
윈도우 함수와 CTE를 활용한 분석
SQLite는 공통 테이블 식(CTE)과 윈도우 함수를 지원합니다. 이를 통해 무거운 데이터베이스가 필요했던 복잡한 분석 쿼리를 손쉽게 작성할 수 있습니다.
예시: 윈도우 함수를 이용한 누적 합계 계산
SELECT
user_id,
created_at,
amount,
SUM(amount) OVER (
PARTITION BY user_id
ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM payments
ORDER BY user_id, created_at;CTE와 결합하면 단일 SQLite 파일만으로도 매우 풍부한 보고서와 대시보드를 만들 수 있습니다.
엄격한 테이블과 향상된 타입 지정
SQLite는 유연한 타입 모델로 유명(또는 악명)합니다. 최신 SQLite는 PostgreSQL 등 전통적인 DB와 비슷하게 타입 제약을 강하게 적용하는 STRICT 테이블을 도입했습니다.
예시: 엄격한 테이블 정의
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL,
is_active INTEGER NOT NULL DEFAULT 1
) STRICT;엄격한 테이블을 사용하면 삽입 시 잘못된 타입이 거부되어 스키마가 더 예측 가능해지고, 특히 대규모 코드베이스에서 미묘한 버그를 줄일 수 있습니다.
파생 데이터를 위한 생성 컬럼
생성 컬럼을 이용하면 표현식을 가상 또는 저장된 컬럼으로 정의해, 파생 데이터를 원본에 가깝게 유지하면서 애플리케이션 로직을 중복하지 않을 수 있습니다.
예시: 정규화된 검색 필드
CREATE TABLE contacts (
id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
full_name TEXT GENERATED ALWAYS AS (
trim(first_name || ' ' || last_name)
) STORED
);
CREATE INDEX idx_contacts_full_name ON contacts(full_name);이제 삽입이나 업데이트가 일어날 때마다 full_name이 자동으로 동기화되며, 효율적인 인덱싱과 조회가 가능합니다.
Write-ahead 로그와 동시성
Write-ahead 로그(WAL)는 많은 워크로드에서 동시성과 성능을 향상시키는 저널링 모드입니다. 일반적인 경우 읽기 작업이 쓰기를 차단하지 않으며, 쓰기 작업도 읽기를 방해하지 않습니다.
WAL 활성화
PRAGMA journal_mode = WAL;데스크톱 앱, 로컬‑퍼스트 도구, 소규모 서비스 등에서 WAL 모드를 사용하면 SQLite의 단순함과 신뢰성을 유지하면서 체감 성능을 크게 높일 수 있습니다.