Skip to content

SQLite 치트시트

자체 포함, 서버리스, 무설정 SQL 데이터베이스 엔진.

01

시작하기

CLI 기초

SQLite는 전체 데이터베이스를 하나의 파일에 저장합니다. sqlite3 CLI는 .sqlite/.db 파일을 필요할 때 열거나 만듭니다. MySQL/PostgreSQL과 달리 서버 프로세스가 없고 애플리케이션이 SQLite 라이브러리를 직접 링크합니다. .dump는 전체 데이터베이스를 텍스트 SQL로 내보내 백업이나 마이그레이션에 사용합니다.

sqlite
# open/create a database file
sqlite3 mydb.sqlite

# at the sqlite> prompt
.help              # show help
.tables            # list tables
.schema users      # show CREATE statement for users
.databases         # list attached databases
.dump              # dump entire DB as SQL text
.quit              # exit

# execute SQL from a file
sqlite3 mydb.sqlite < script.sql

# execute a query from command line
sqlite3 mydb.sqlite "SELECT COUNT(*) FROM users;"

도트 명령

도트 명령은 SQL 엔진이 아니라 sqlite3 CLI가 해석합니다. 세미콜론이 필요 없고 프로그래밍 API에서는 사용할 수 없습니다. .mode는 출력 형식을 제어합니다(가독성을 위해 .header on과 함께 'column'을 사용해 보세요). .schema는 CREATE 문을 보여주고 .fullschema는 인덱스, 뷰, 트리거를 추가합니다. .help로 더 알아보세요.

sqlite
-- dot commands are CLI-only, NOT SQL (no semicolon needed)
.help                 -- list all dot commands
.tables               -- list all tables
.schema               -- schema for all tables
.schema users         -- schema for the users table
.fullschema           -- schema incl. indexes, triggers, views
.indices users        -- list indexes on users
.header on            -- show column headers
.mode column          -- column-aligned output
.mode list            -- default, pipe-separated
.mode csv             -- CSV output
.mode json            -- JSON output
.show                 -- show current settings
.width 10 20 30       -- set column widths
.timer on             -- show query execution time

데이터베이스 파일 관리

ATTACH DATABASE를 쓰면 한 연결이 최대 10개 데이터베이스 파일을 스키마(main, temp, 추가 연결)로 동시에 질의할 수 있습니다. :memory: URI는 종료 시 사라지는 순수 인메모리 데이터베이스를 만들어 테스트나 임시 계산에 이상적입니다. URI 파일 이름(file:...)은 읽기 전용, 공유 캐시 등의 모드를 가능하게 합니다. 연결한 DB는 각각 디스크의 별도 파일입니다.

sqlite
-- attach another database file to the current connection
ATTACH DATABASE 'archive.sqlite' AS archive;
DETACH DATABASE archive;

-- list attached databases (main + attached)
.databases

-- the in-memory database (never written to disk)
sqlite3 :memory:

-- temporary database (deleted when connection closes)
sqlite3 ""          -- empty arg => temp DB

-- open read-only
sqlite3 "file:mydb.sqlite?mode=ro" --readonly

-- query the underlying page size / file format
PRAGMA page_size;
PRAGMA journal_mode;

헤더와 출력 형식

출력 형식은 CLI에만 영향을 주며 데이터 자체는 바뀌지 않습니다. .headers on과 'column' 모드가 사람이 읽기 가장 좋습니다. 'list'(기본)는 셸 파이프라인에 좋습니다. 'json'과 'csv'는 SQLite를 편리한 변환기로 만듭니다. .output은 결과를 파일로 보내므로 .output stdout으로 되돌리세요. .mode box/table은 ASCII 테두리를 그립니다.

sqlite
-- readable interactive mode
.headers on
.mode column
.width 15 20 10
SELECT id, name, email FROM users LIMIT 5;

-- pipe-separated (good for scripts)
.mode list
.separator "|"
SELECT id, name FROM users;

-- boxed table output (sqlite 3.36+)
.mode box
.mode table

-- JSON output for piping to other tools
.mode json
SELECT id, name FROM users;

-- write output to a file
.output users.txt
SELECT id, name FROM users;
.output stdout

스크립트와 SQL 파일 실행

.read는 열린 CLI 세션 안에서 SQL 파일을 실행하고, <로 리다이렉트하면 대화형 프롬프트 전에 실행합니다. --bail은 첫 오류에서 배치를 멈춥니다(그렇지 않으면 SQLite가 계속 진행해 실패를 가릴 수 있음). .parameter(3.44+)는 셸에서 명명 매개변수를 바인딩해 문자열 보간을 피합니다. 개발 중에는 .echo on으로 스크립트를 항상 점검하세요.

sqlite
-- run a .sql file from the shell
sqlite3 mydb.sqlite ".read setup.sql"
-- or piped in
sqlite3 mydb.sqlite < setup.sql

-- inside the CLI
.read setup.sql

-- stop on first error (shell flag)
sqlite3 mydb.sqlite --bail < script.sql

-- echo commands as they run
.echo on
.read migrations.sql

-- show EXPLAIN before each statement (debug)
.explain on

-- parameterize from the shell (sqlite 3.44+)
sqlite3 mydb.sqlite \
  -cmd ".parameter set @min 18" \
  "SELECT name FROM users WHERE age >= @min"
02

테이블과 데이터 타입

테이블 만들기

INTEGER PRIMARY KEY는 rowid의 별칭입니다. 자동 증가하며 가능한 가장 빠른 키입니다. AUTOINCREMENT는 삭제된 ID를 절대 재사용하지 않도록 알고리즘을 바꿉니다(약간 느리고 보통 불필요). DEFAULT (expr)은 datetime('now') 같은 임의 표현식을 허용합니다. CREATE TABLE AS SELECT는 쿼리 결과로 채워진 테이블을 만들지만 제약 조건과 인덱스는 복사하지 않습니다.

sqlite
CREATE TABLE users (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT    NOT NULL UNIQUE,
  email    TEXT    NOT NULL UNIQUE,
  age      INTEGER CHECK (age >= 0),
  role     TEXT    NOT NULL DEFAULT 'user',
  created  TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS orders (
  id        INTEGER PRIMARY KEY,
  user_id   INTEGER NOT NULL,
  total     REAL    NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

-- quick table from a query
CREATE TABLE archive AS
  SELECT * FROM orders WHERE created < '2024-01-01';

저장 클래스와 타입 선호

SQLite는 동적 타입을 씁니다. 어떤 컬럼이든 5개 저장 클래스 중 무엇이든 담을 수 있습니다. '타입 선호'는 가능하면 값을 변환하지만(예: INTEGER 컬럼의 '42'는 정수 42) 불일치 시 오류를 내지 않습니다. STRICT 테이블(3.37+)은 컬럼별 전통적인 엄격 타입 검사를 복원합니다. typeof()는 실제 저장 클래스를 보고합니다. 이 유연성은 강력하지만 규율이 필요합니다. 데이터 무결성이 중요한 새 스키마에는 STRICT를 사용하세요.

sqlite
-- SQLite has 5 storage classes (not strict types):
--   NULL, INTEGER, REAL, TEXT, BLOB
CREATE TYPE demo...  -- not supported

-- columns have 'type affinity', not enforced types
CREATE TABLE t (
  a INTEGER,   -- affinity INTEGER
  b TEXT,      -- affinity TEXT
  c REAL,      -- affinity REAL
  d BLOB,      -- affinity BLOB (no conversion)
  e            -- no affinity (accepts anything)
);

-- this WORKS despite 'INTEGER' column (dynamic typing)
INSERT INTO t (a) VALUES ('hello');
SELECT typeof(a) FROM t;   -- 'text'

-- STRICT tables (SQLite 3.37+) actually enforce types
CREATE TABLE strict_t (a INTEGER, b TEXT) STRICT;
INSERT INTO strict_t VALUES ('hi', 1);   -- error

흔한 데이터 타입

SQLite에는 네이티브 DATE/TIME이나 BOOLEAN 타입이 없습니다. 타임스탬프는 정렬 가능하도록 ISO-8601 TEXT('YYYY-MM-DD HH:MM:SS')로 저장하고 date/time 함수로 조작하세요. 불리언은 정수 0/1입니다. VARCHAR(n) 길이 제한은 무시됩니다(호환성용). NUMERIC 선호는 INTEGER를 먼저 시도하고 그다음 REAL을 시도하며 가능하면 정확한 소수를 보존합니다. BLOB은 원시 바이트를 그대로 저장합니다.

sqlite
-- integers
CREATE TABLE numerics (
  small  INTEGER,        -- 1/2/4/8 bytes depending on value
  big    INTEGER,        -- up to 64-bit
  -- no separate BIGINT/SMALLINT, all map to INTEGER affinity
  flag   BOOLEAN         -- stored as 0 (false) or 1 (true)
);

-- text & blobs
CREATE TABLE blobs (
  name   TEXT,           -- variable-length UTF-8, no length limit
  data   BLOB,           -- raw bytes, no conversion
  note   VARCHAR(255)    -- length ignored, affinity TEXT
);

-- real numbers
CREATE TABLE floats (
  price  REAL,           -- 8-byte IEEE float
  precise NUMERIC(10,2)  -- affinity NUMERIC, decimal preserved if exact
);

-- dates: SQLite has no native DATE type — store as TEXT (ISO-8601)
CREATE TABLE events (
  ts TEXT  -- 'YYYY-MM-DD HH:MM:SS' recommended
);

테이블 변경

SQLite의 ALTER TABLE은 서버리스 엔진답게 의도적으로 제한적입니다. ADD COLUMN은 O(1)이라 테이블 재작성이 없습니다. RENAME COLUMN(3.25+)과 DROP COLUMN(3.35+)은 현대적 편의 기능입니다. 타입 변경이나 제약 추가에는 12단계 테이블 재구축 패턴(새로 만들기, 복사, 삭제, 이름 변경, 인덱스 재구축)을 사용하세요. 재구축 중에는 외래 키를 일시적으로 비활성화해야 합니다(PRAGMA legacy_alter_table 참고).

sqlite
-- add a column (fast, appends to end)
ALTER TABLE users ADD COLUMN bio TEXT;

-- rename a column (SQLite 3.25+)
ALTER TABLE users RENAME COLUMN name TO username;

-- rename a table
ALTER TABLE users RENAME TO accounts;

-- drop a column (SQLite 3.35+)
ALTER TABLE users DROP COLUMN deprecated_field;

-- SQLite CANNOT: change a column type, add constraints to
-- an existing column, or reorder columns. Workaround:
-- 1) create new table with desired schema
-- 2) INSERT INTO new SELECT * FROM old
-- 3) DROP old; ALTER TABLE new RENAME TO old
-- 4) recreate indexes/triggers

제약 조건

제약 조건은 엔진 수준에서 데이터 무결성을 강제합니다. PRIMARY KEY는 NOT NULL과 UNIQUE를 함의합니다. CHECK는 행의 어떤 컬럼이든 참조할 수 있습니다. ON CONFLICT 절(OR IGNORE / OR REPLACE / OR ABORT / OR FAIL / OR ROLLBACK)은 문장별 충돌 해결을 제어합니다. OR REPLACE는 충돌 행을 삭제한 뒤 삽입하므로 rowid가 초기화됩니다. UPSERT 의미가 필요하면 ON CONFLICT ... DO UPDATE를 권장합니다(CRUD 섹션 참고).

sqlite
CREATE TABLE products (
  id         INTEGER PRIMARY KEY,
  sku        TEXT    NOT NULL UNIQUE,
  name       TEXT    NOT NULL,
  price      REAL    NOT NULL CHECK (price >= 0),
  stock      INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category   TEXT    CHECK (category IN ('a','b','c')),

  -- composite unique constraint
  UNIQUE (name, category),

  -- table-level check
  CHECK (price * stock < 1000000)
);

-- name a constraint for clearer error messages
CREATE TABLE t (
  x INTEGER CONSTRAINT x_positive CHECK (x > 0)
);

-- conflict handling
INSERT OR IGNORE INTO products (sku, name, price) VALUES (...);
INSERT OR REPLACE INTO products (...) VALUES (...);
UPDATE OR ABORT products SET stock = stock - 1 WHERE id = 1;
03

CRUD 연산

INSERT

다중 행 INSERT는 루프에서 행별로 삽입하는 것보다 훨씬 빠르므로 배치로 처리하세요. INSERT ... SELECT는 테이블 간에 데이터를 복사합니다. last_insert_rowid()는 현재 연결(트랜잭션 아님)에서 가장 최근 INSERT의 rowid를 반환합니다. 앱에서는 SQL 인젝션과 따옴표 문제를 피하려고 매개변수화된 삽입(통합 섹션 참고)을 권장합니다. 컬럼 기본값을 명시적으로 쓰려면 DEFAULT를 사용하세요.

sqlite
-- single row
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

-- multiple rows in one statement (fast)
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

-- insert from a query
INSERT INTO archive_users
SELECT * FROM users WHERE active = 0;

-- explicit NULL / default
INSERT INTO users (username, email, age)
VALUES ('eve', '[email protected]', DEFAULT);

-- return the new rowid (handy in apps)
INSERT INTO users (username) VALUES ('frank');
SELECT last_insert_rowid();   -- returns the new id

UPSERT(ON CONFLICT)

ON CONFLICT(UPSERT)는 '있으면 삽입 또는 갱신'을 위한 SQLite의 관용구입니다. 기존 행을 삭제하는(그래서 DELETE 트리거를 발생시키고 rowid를 초기화하는) INSERT OR REPLACE보다 훨씬 안전합니다. 'excluded'는 삽입하려는 행을 가리킵니다. DO NOTHING은 조용히 건너뜁니다. 충돌 대상은 모든 UNIQUE 또는 PRIMARY KEY 제약이 될 수 있습니다. 카운터, 마지막 확인 시각 갱신, 멱등 가져오기에 올바른 패턴입니다.

sqlite
-- insert, but update on conflict
INSERT INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]')
ON CONFLICT(id) DO UPDATE SET
  email  = excluded.email,
  username = excluded.username;

-- ignore on conflict
INSERT INTO users (id, username)
VALUES (1, 'alice')
ON CONFLICT(id) DO NOTHING;

-- conflict on a specific unique constraint
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]')
ON CONFLICT(email) DO UPDATE SET
  username = excluded.username;

UPDATE

정말로 모든 행을 갱신하려는 게 아니라면 항상 WHERE를 포함하세요. SQLite에는 드라이런 모드가 없습니다. UPDATE ... FROM(3.33+)과 UPDATE ... RETURNING(3.35+)은 SQLite를 PostgreSQL의 편의성에 가깝게 만듭니다. RETURNING은 별도 SELECT 없이 갱신 후 상태가 필요한 앱에 매우 유용합니다. CASE는 행별 로직이 있는 단일 문장 대량 갱신을 가능하게 하며 행별 갱신보다 훨씬 빠릅니다.

sqlite
-- basic update (ALWAYS use WHERE in production!)
UPDATE users SET age = 31 WHERE id = 1;

-- multiple columns
UPDATE users
SET age = age + 1, role = 'admin'
WHERE username = 'alice';

-- conditional update with CASE
UPDATE products
SET price = CASE
  WHEN category = 'a' THEN price * 0.9
  WHEN category = 'b' THEN price * 0.95
  ELSE price
END;

-- update from another table (subquery)
UPDATE orders
SET total = (SELECT SUM(qty * price)
             FROM items WHERE order_id = orders.id);

-- UPDATE ... RETURNING (SQLite 3.35+)
UPDATE users SET role = 'admin'
WHERE id IN (1,2,3)
RETURNING id, username, role;

DELETE

SQLite에는 TRUNCATE가 없습니다. DELETE FROM table은 모든 행을 제거하지만 행 수준 트리거를 발생시키고 AUTOINCREMENT 카운터(sqlite_sequence에 저장)를 초기화하지 않습니다. DELETE ... RETURNING(3.35+)은 제거된 내용을 기록합니다. 감사 데이터에는 하드 삭제보다 소프트 삭제(deleted_at 컬럼)를 권장합니다. 삭제로 생긴 공간은 이후 삽입에서 재사용되고 VACUUM이 OS로 반환합니다.

sqlite
-- basic delete
DELETE FROM users WHERE id = 1;

-- delete with a subquery condition
DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = 0);

-- DELETE ... RETURNING
DELETE FROM sessions
WHERE expires < datetime('now')
RETURNING id, user_id;

-- delete ALL rows (fast, but fires triggers)
DELETE FROM logs;

-- TRUNCATE equivalent: delete all + reset rowid
DELETE FROM logs;
DELETE FROM sqlite_sequence WHERE name = 'logs';

-- soft delete pattern
UPDATE users SET deleted_at = datetime('now') WHERE id = 1;

SELECT 기초

SELECT는 핵심입니다. LIMIT count OFFSET skip은 페이지네이션을 구현합니다(큰 오프셋에는 키셋 페이지네이션이 더 빠름, 쿼리 섹션 참고). 별칭(AS, 선택)은 가독성을 높이고 뷰의 계산 컬럼에는 필수입니다. DISTINCT는 동일한 행을 합칩니다. 더 세밀한 제어가 필요하면 GROUP BY를 고려하세요. CASE는 결과 집합에 if/else 로직을 추가하며 어느 절에서든 사용할 수 있습니다.

sqlite
-- basic select
SELECT id, username, email FROM users;

-- limit and offset
SELECT id, username FROM users LIMIT 10 OFFSET 20;
SELECT id, username FROM users LIMIT 20, 10;  -- offset, count

-- aliasing columns and tables
SELECT u.id AS user_id, u.username AS name
FROM users AS u;

-- expressions
SELECT
  username,
  age,
  age * 365 AS days_alive,
  UPPER(username) AS upper_name
FROM users;

-- distinct rows
SELECT DISTINCT category FROM products;

-- conditional output
SELECT name,
  CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS status
FROM users;
04

조회와 필터링

WHERE와 연산자

SQL에서 NULL은 특별합니다. 값이 아니라 '알 수 없음'입니다. = NULL은 항상 NULL(거짓으로 취급)을 반환하므로 IS NULL / IS NOT NULL을 사용하세요. NULL과 AND/OR를 결합하면 3값 논리를 따릅니다. BETWEEN은 양 끝을 포함합니다. IN은 OR 동등 비교의 축약형입니다. IN (NULL, ...)은 이상하게 동작하므로 피하세요. 복합 조건을 명확히 하려면 괄호를 적극적으로 사용하세요.

sqlite
-- comparison operators
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM users WHERE age IN (18, 21, 25);
SELECT * FROM users WHERE age NOT IN (18, 21);

-- NULL handling (NEVER use = NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- logical operators
SELECT * FROM users
WHERE age >= 18 AND age <= 65 AND role = 'admin';

SELECT * FROM users
WHERE role = 'admin' OR role = 'editor';

-- range with multiple conditions
SELECT * FROM orders
WHERE (status = 'shipped' AND total > 100)
   OR (status = 'pending' AND total > 500);

ORDER BY와 LIMIT

고유한 타이브레이커 없는 ORDER BY는 실행마다 비결정적 순서를 줍니다. 항상 고유 컬럼(id 등)을 마지막 정렬 키로 추가하세요. OFFSET 페이지네이션은 행을 스캔해 버리므로 O(n)입니다. 키셋 페이지네이션(WHERE id > last_seen_id)은 O(limit)이라 큰 테이블에 올바른 선택입니다. RANDOM()은 전체 정렬이 필요하므로 작은 결과 집합이나 샘플링된 부분 집합에만 사용하세요.

sqlite
-- ascending (default) / descending
SELECT * FROM users ORDER BY username;
SELECT * FROM users ORDER BY created DESC;
SELECT * FROM users ORDER BY age DESC, username ASC;

-- sort with NULLs first or last (SQLite 3.30+)
SELECT * FROM tasks ORDER BY due_date ASC NULLS FIRST;
SELECT * FROM tasks ORDER BY due_date DESC NULLS LAST;

-- pagination: keyset is faster than OFFSET
-- slow (offset scans & discards rows):
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000;
-- fast (keyset):
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;

-- random sample
SELECT * FROM users ORDER BY RANDOM() LIMIT 5;

패턴 매칭

LIKE는 기본적으로 ASCII에서 대소문자를 구분하지 않습니다(의외의 함정, 대소문자 구분에는 COLLATE BINARY 사용). GLOB은 항상 대소문자를 구분하고 [A-Z] 같은 문자 클래스를 지원합니다. REGEXP는 내장이 아니므로 함수를 등록하거나 확장을 로드해야 합니다. 대규모 전체 텍스트 검색에는 인덱스를 쓸 수 없는 LIKE '%term%' 대신 FTS5(성능 섹션 참고)를 사용하세요.

sqlite
-- LIKE: case-insensitive by default for ASCII,
--   wildcards % (any chars) and _ (single char)
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_lice';

-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'AL%' COLLATE BINARY;

-- GLOB: Unix shell-style, case-SENSITIVE
--   wildcards * and ?, plus [abc] character classes
SELECT * FROM users WHERE username GLOB 'Al*';
SELECT * FROM users WHERE username GLOB '[A-D]*';

-- REGEXP (requires extension or custom function)
SELECT * FROM users WHERE username REGEXP '^a.*e$';

-- substring match
SELECT * FROM users WHERE INSTR(username, 'lic') > 0;

CASE와 조건 로직

CASE는 SQL의 if/else이며 SELECT, WHERE, ORDER BY, GROUP BY, HAVING에서 동작합니다. '단순' 형식은 값을 일치시키고, '검색' 형식은 불리언 조건을 순서대로 평가합니다. SUM(CASE WHEN ... THEN 1 ELSE 0 END)는 고전적인 피벗/조건부 집계 관용구입니다. CASE는 첫 번째로 일치하는 WHEN에서 단락되므로 순서가 중요합니다. ELSE를 생략하면 기본값은 NULL입니다.

sqlite
-- simple CASE (value match)
SELECT name,
  CASE role
    WHEN 'admin' THEN 'Administrator'
    WHEN 'editor' THEN 'Editor'
    ELSE 'Regular User'
  END AS role_label
FROM users;

-- searched CASE (conditions)
SELECT name,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END AS age_group
FROM users;

-- CASE in aggregate (conditional count)
SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS active_count,
  SUM(CASE WHEN active = 0 THEN 1 ELSE 0 END) AS inactive_count
FROM users;

-- CASE in ORDER BY (custom sort order)
SELECT * FROM products
ORDER BY CASE category
  WHEN 'featured' THEN 0
  WHEN 'new' THEN 1
  ELSE 2
END;

DISTINCT와 집합 연산

UNION ALL은 UNION보다 빠릅니다. UNION은 중복 제거 정렬을 하기 때문입니다. 중복이 불가능하거나 허용되면 UNION ALL을 권장하세요. 모든 집합 연산은 호환되는 컬럼 수와 타입을 요구합니다. SQLite는 INTERSECT/EXCEPT를 UNION/UNION ALL보다 높은 우선순위로 적용하므로 괄호로 순서를 제어하세요. 복합 쿼리의 ORDER BY는 마지막 SELECT가 아니라 전체 결과 집합에 적용됩니다.

sqlite
-- distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT city, country FROM users;

-- UNION (dedup) and UNION ALL (faster, keeps dups)
SELECT 'user' AS type, id FROM users WHERE active = 1
UNION ALL
SELECT 'admin', id FROM admins;

-- INTERSECT: rows in both
SELECT id FROM users
INTERSECT
SELECT user_id FROM orders;

-- EXCEPT: rows in first but not second
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;

-- combine with ORDER BY (applies to whole result)
SELECT name FROM users WHERE active = 1
UNION
SELECT name FROM archived_users
ORDER BY name;
05

JOIN

INNER JOIN

INNER JOIN은 양쪽 테이블에 일치가 있는 행만 반환합니다. 어느 쪽이든 일치하지 않는 행은 버려집니다. USING (col)은 두 테이블에 같은 이름의 컬럼이 있을 때의 축약형이며 병합된 컬럼 하나를 만듭니다. NATURAL JOIN은 같은 이름의 모든 컬럼을 암묵적으로 사용하므로 편리하지만 취약합니다(스키마 변경이 조인 동작을 조용히 바꿀 수 있음). 명확성을 위해 명시적 ON을 권장하세요.

sqlite
-- only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id;

-- join with additional filter
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 100 AND u.active = 1;

-- USING shorthand (when join columns share a name)
SELECT u.username, o.total
FROM users u
JOIN orders o USING (user_id);

-- natural join (joins on all same-named columns — risky)
SELECT * FROM users NATURAL JOIN profiles;

LEFT JOIN

LEFT JOIN은 왼쪽 테이블의 모든 행을 보존하고 오른쪽 컬럼이 없으면 NULL이 됩니다. 'WHERE right.id IS NULL' 패턴(안티 조인)은 일치가 없는 행을 찾으며, 특히 NULL이 관련될 때 서브쿼리가 있는 NOT IN보다 명확하고 빠른 경우가 많습니다. LEFT JOIN을 가로질러 집계할 때 COUNT(right.id)(COUNT(*) 아님)는 일치한 행만 셉니다. 주문이 없는 사용자는 1이 아니라 0을 줍니다.

sqlite
-- all users, with their orders (NULLs if no orders)
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

-- find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- multiple left joins
SELECT u.username,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

CROSS JOIN과 RIGHT/FULL

CROSS JOIN은 카티션 곱을 만듭니다(size × color 조합 생성에 유용하지만 큰 테이블에서는 n×m 행이라 위험). SQLite는 RIGHT JOIN(3.39+)을 지원하지만 드물게 쓰이므로 명확성을 위해 LEFT JOIN으로 다시 쓰세요. 네이티브 FULL OUTER JOIN은 없습니다. LEFT JOIN ... UNION ... RIGHT JOIN으로 에뮬레이트하며 UNION이 겹치는 일치 행을 중복 제거합니다.

sqlite
-- CROSS JOIN: cartesian product (every combination)
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;

-- explicit CROSS JOIN (same as comma join)
SELECT a.name, b.name FROM teams a, teams b;

-- RIGHT JOIN (preserves right table) — supported but unusual
SELECT u.username, o.total
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;

-- SQLite has no FULL OUTER JOIN; emulate with LEFT + UNION:
SELECT u.username, o.id AS order_id
FROM users u LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id
FROM users u RIGHT JOIN orders o ON o.user_id = u.id;

셀프 조인

셀프 조인은 같은 테이블을 두 번 질의하며 별칭으로 각 복사본에 역할(직원 vs 관리자, user_a vs user_b)을 부여합니다. 'a.id < b.id' 기법은 행이 자기 자신과 일치해 거울 중복 쌍이 생기는 것을 피합니다. 깊은 계층(임의 깊이)에는 재귀 CTE(CTE 섹션 참고)가 올바른 도구입니다. 셀프 조인은 고정 깊이만 처리합니다.

sqlite
-- employees and their managers (same table)
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  manager_id INTEGER REFERENCES employees(id)
);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;

-- recursive hierarchy (tree traversal) — see CTE section
-- multi-level category paths
SELECT c.name, p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

다중 테이블 조인

조인은 왼쪽에서 오른쪽으로 연결되며 각 JOIN은 누적된 결과를 봅니다. LEFT와 INNER를 섞을 때 LEFT JOIN 뒤의 INNER JOIN이 NULL을 다시 걸러 LEFT의 목적을 무산시킬 수 있으므로 순서에 주의하세요. COUNT(DISTINCT col)은 일대다 조인에서 이중 집계를 피합니다. 조인 다중도에 유의하세요. 일대다 테이블 두 개를 조인하면 부모당 m × n 행의 폭발이 생길 수 있습니다.

sqlite
-- chain joins across 3+ tables
SELECT u.username, o.id AS order_id, i.name AS item, i.qty
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
WHERE u.active = 1
ORDER BY o.id, i.name;

-- mixing JOIN types
SELECT u.username, o.id AS order_id, p.name AS product
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN products p ON p.id = i.product_id;

-- join with aggregation
SELECT u.username,
  COUNT(DISTINCT o.id) AS orders,
  SUM(i.qty * i.price) AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
GROUP BY u.id, u.username;
06

집계와 GROUP BY

GROUP BY 기초

GROUP BY는 그룹 컬럼을 공유하는 행을 그룹당 한 행으로 합칩니다. SELECT의 모든 비집계 컬럼은 GROUP BY에 나타나야 합니다(SQLite는 관대해서 임의 값을 고르지만 의존하지 마세요). 표현식(날짜의 strftime 등)으로 그룹화하면 시간 버킷 리포트에 좋습니다. ORDER BY는 GROUP BY 후에 적용되므로 그룹 정렬에 사용하세요.

sqlite
-- count users per role
SELECT role, COUNT(*) AS user_count
FROM users
GROUP BY role;

-- multiple aggregates per group
SELECT role,
  COUNT(*)            AS user_count,
  AVG(age)            AS avg_age,
  MIN(age)            AS min_age,
  MAX(age)            AS max_age,
  SUM(age)            AS total_age
FROM users
GROUP BY role;

-- group by multiple columns
SELECT role, active, COUNT(*) AS cnt
FROM users
GROUP BY role, active
ORDER BY role, active;

-- group by expression
SELECT strftime('%Y-%m', created) AS month,
  COUNT(*) AS signups
FROM users
GROUP BY month
ORDER BY month;

집계 함수

COUNT(*)는 행 수, COUNT(col)은 NULL이 아닌 값 수, COUNT(DISTINCT col)은 고유한 비NULL 수를 셉니다. AVG/SUM은 NULL을 무시합니다. GROUP_CONCAT은 값을 문자열로 연결합니다(기본 쉼표 구분자). TOTAL()은 빈 입력에 0.0을 반환하는 REAL이고, SUM()은 빈 입력에 NULL을 반환합니다. FILTER 절(3.30+)은 조건부 집계에서 CASE보다 깔끔하고 빠를 수 있습니다.

sqlite
SELECT
  COUNT(*)             AS row_count,
  COUNT(email)         AS emails_non_null,
  COUNT(DISTINCT role) AS distinct_roles,
  AVG(age)             AS avg_age,
  SUM(total)           AS revenue,
  MIN(created)         AS first_signup,
  MAX(created)         AS last_signup,
  GROUP_CONCAT(name)   AS all_names,
  GROUP_CONCAT(name, ' | ') AS names_piped,
  TOTAL(price)         AS total_real  -- always returns REAL, 0.0 if empty
FROM orders;

-- aggregate with FILTER (SQLite 3.30+)
SELECT
  COUNT(*)                              AS all_orders,
  COUNT(*) FILTER (WHERE status='paid') AS paid,
  SUM(total) FILTER (WHERE status='paid') AS paid_revenue
FROM orders;

HAVING(그룹 필터)

WHERE는 그룹화 전에 입력 행을 필터링하고, HAVING은 집계 후 그룹을 필터링합니다. 이 순서가 중요합니다. 집계에 WHERE를 쓰는 것은 불법입니다. SQLite는 가독성을 위해 HAVING(과 GROUP BY)에서 별칭 참조를 허용하지만 표준은 아닙니다. 'group by X having count(*) > 1' 패턴은 고전적인 중복 찾기 쿼리입니다.

sqlite
-- HAVING filters groups (after GROUP BY);
-- WHERE filters rows (before GROUP BY)
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS spent
FROM orders
WHERE status = 'paid'         -- filter rows first
GROUP BY user_id
HAVING COUNT(*) >= 3 AND SUM(total) > 100   -- filter groups
ORDER BY spent DESC;

-- HAVING with aliases (SQLite allows this)
SELECT role, COUNT(*) AS n
FROM users
GROUP BY role
HAVING n > 1;

-- find duplicate usernames
SELECT username, COUNT(*) AS dupes
FROM users
GROUP BY username
HAVING COUNT(*) > 1;

GROUP_CONCAT

GROUP_CONCAT은 SQLite의 문자열 집계 함수입니다(다른 DB에서는 STRING_AGG나 LISTAGG). 기본 구분자는 쉼표이며 두 번째 인자로 사용자 정의 구분자를 지정할 수 있습니다. 집계 내부의 ORDER BY(3.44+)는 연결 순서를 제어합니다. DISTINCT는 연결 전에 중복을 제거합니다. 결과는 1GB 제한이 있으므로 거대한 그룹은 행을 가져와 애플리케이션에서 집계하세요.

sqlite
-- concatenate values within a group
SELECT role, GROUP_CONCAT(username) AS members
FROM users
GROUP BY role;
-- members: 'alice,bob,carol'

-- custom separator
SELECT role, GROUP_CONCAT(username, ' | ') AS members
FROM users
GROUP BY role;

-- ordered concatenation (SQLite 3.44+)
SELECT role,
  GROUP_CONCAT(username ORDER BY username) AS sorted_members
FROM users
GROUP BY role;

-- distinct values only
SELECT role,
  GROUP_CONCAT(DISTINCT city) AS cities
FROM users
GROUP BY role;

데이터 피벗

SQLite에는 네이티브 PIVOT이 없으므로 SUM(CASE WHEN ...)이나 더 깔끔한 FILTER 절로 에뮬레이트하세요. 각 피벗 컬럼은 그룹 속성의 값 하나를 검사합니다. 이 패턴은 긴(정규화된) 데이터를 넓은(교차표) 리포트로 변환합니다. 완전 동적 피벗(알 수 없는 카테고리)은 애플리케이션 코드에서 SQL 문자열을 만들어야 합니다. SQL은 런타임에 컬럼을 생성할 수 없습니다.

sqlite
-- manual pivot with SUM(CASE ...)
SELECT user_id,
  SUM(CASE WHEN strftime('%w', created) = '0' THEN 1 ELSE 0 END) AS sun,
  SUM(CASE WHEN strftime('%w', created) = '1' THEN 1 ELSE 0 END) AS mon,
  SUM(CASE WHEN strftime('%w', created) = '2' THEN 1 ELSE 0 END) AS tue,
  SUM(CASE WHEN strftime('%w', created) = '3' THEN 1 ELSE 0 END) AS wed,
  SUM(CASE WHEN strftime('%w', created) = '4' THEN 1 ELSE 0 END) AS thu,
  SUM(CASE WHEN strftime('%w', created) = '5' THEN 1 ELSE 0 END) AS fri,
  SUM(CASE WHEN strftime('%w', created) = '6' THEN 1 ELSE 0 END) AS sat
FROM orders
GROUP BY user_id;

-- pivot with FILTER (cleaner)
SELECT user_id,
  COUNT(*) FILTER (WHERE status='paid')    AS paid,
  COUNT(*) FILTER (WHERE status='pending') AS pending,
  COUNT(*) FILTER (WHERE status='refunded') AS refunded
FROM orders
GROUP BY user_id;
07

서브쿼리

스칼라 서브쿼리

스칼라 서브쿼리는 정확히 한 행과 한 컬럼을 반환하며 값 표현식이 유효한 어디든 나타날 수 있습니다. 상관 서브쿼리는 외부 쿼리를 참조하고 행마다 다시 실행되므로 강력하지만 큰 테이블에서는 느릴 수 있습니다(JOIN이나 윈도우 함수 고려). SQLite는 단순 상관 서브쿼리를 조인으로 최적화하는 데 능숙하지만 핫 경로에서는 항상 EXPLAIN QUERY PLAN을 확인하세요.

sqlite
-- a subquery returning a single value
SELECT username, age
FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- in SELECT list
SELECT username,
  age,
  age - (SELECT AVG(age) FROM users) AS age_diff
FROM users;

-- in HAVING
SELECT role, AVG(age) AS avg_age
FROM users
GROUP BY role
HAVING AVG(age) > (SELECT AVG(age) FROM users);

-- correlated scalar subquery (re-evaluated per row)
SELECT username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

IN과 NOT IN 서브쿼리

IN 서브쿼리는 직관적이지만 NULL에 주의하세요. 서브쿼리 결과에 NULL이 있으면 NOT IN은 모든 행에 대해 NULL(거짓 취급)을 평가해 빈 결과를 반환합니다. NULL 허용 컬럼이 있는 안티 조인에는 NOT EXISTS를 권장합니다. NULL 안전하고 종종 더 빠릅니다. 다중 컬럼 IN은 복합 키 일치에 간결합니다.

sqlite
-- users who have placed an order
SELECT username FROM users
WHERE id IN (SELECT user_id FROM orders);

-- users who have NOT placed an order
SELECT username FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- DANGER: NOT IN with NULLs returns no rows!
-- if the subquery returns any NULL, NOT IN matches nothing.
-- Safe version using NOT EXISTS:
SELECT username FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- multi-column IN
SELECT * FROM products
WHERE (category, price) IN (
  SELECT category, MIN(price) FROM products GROUP BY category
);

EXISTS와 NOT EXISTS

EXISTS는 데이터를 가져오지 않고 행 존재를 검사하며 첫 일치에서 멈추므로 효율적입니다. NOT EXISTS는 NOT IN과 달리 일치하지 않는 행을 찾는 NULL 안전 방법입니다. 최상의 성능을 위해 상관 서브쿼리가 조인하는 컬럼(예제의 orders.user_id)에 인덱스가 있는지 확인하세요. SELECT 1이 관례이며 컬럼 목록은 EXISTS에 무관합니다.

sqlite
-- EXISTS: true if subquery returns any row
SELECT username FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 100
);

-- NOT EXISTS: classic anti-join (NULL-safe)
SELECT username FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- correlated EXISTS — usually efficient with an index on
-- the subquery's join column (orders.user_id here)
SELECT p.name FROM products p
WHERE EXISTS (
  SELECT 1 FROM order_items i
  WHERE i.product_id = p.id AND i.qty > 10
);

파생 테이블(FROM의 서브쿼리)

FROM의 서브쿼리(파생 테이블)는 중간 결과를 준비하거나 조인 전에 사전 집계/필터링하는 강력한 방법입니다. 파생 테이블에는 별칭이 필요합니다. SQLite는 대부분의 경우 서브쿼리를 구체화(임시 테이블에 기록)합니다. 반복 사용에는 CTE(WITH 절)가 종종 더 명확하고 여러 번 참조할 수 있습니다. SQLite는 FROM 서브쿼리로의 상관 참조를 허용하지 않습니다.

sqlite
-- treat a query result as a table
SELECT t.role, t.cnt
FROM (
  SELECT role, COUNT(*) AS cnt
  FROM users
  GROUP BY role
) t
WHERE t.cnt > 1;

-- join a derived table
SELECT u.username, t.total_spent
FROM users u
JOIN (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY user_id
) t ON t.user_id = u.id;

-- derived tables must be aliased
SELECT * FROM (SELECT 1 AS x) AS sub;

상관 vs 비상관

비상관 서브쿼리는 한 번 실행되어 캐시될 수 있고, 상관 서브쿼리는 외부 행마다 다시 실행됩니다(인덱스로 빠르게 유지하세요). SQLite에는 LATERAL/APPLY가 없으므로 행별 집계를 '조인'하려면 파생 테이블에서 사전 집계한 뒤 LEFT JOIN하세요. 상관 서브쿼리가 느리면 JOIN + GROUP BY나 윈도우 함수로 다시 쓰는 것이 일반적 해결책입니다.

sqlite
-- UNCORRELATED: runs once, result cached
SELECT username FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- CORRELATED: references outer row, re-runs per outer row
SELECT u.username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS orders
FROM users u;

-- lateral-style correlation in a JOIN subquery is NOT
-- supported directly; use a scalar subquery in SELECT or
-- rewrite as a join with GROUP BY:
SELECT u.username, COALESCE(t.n, 0) AS orders
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS n FROM orders GROUP BY user_id
) t ON t.user_id = u.id;
08

인덱스

인덱스 만들기

인덱스는 읽기를 빠르게 하지만 쓰기를 느리게 합니다(각 INSERT/UPDATE/DELETE가 모든 인덱스를 갱신). 인덱스 컬럼 순서가 중요합니다. (a, b) 복합 인덱스는 WHERE a=?와 WHERE a=? AND b=?에 도움이 되지만 WHERE b=? 단독에는 도움이 되지 않습니다(최좌측 접두사 규칙). 고유 인덱스는 제약을 강제합니다. SQLite는 인덱스를 B-트리로 저장합니다. 사용하지 않는 인덱스는 삭제하세요. 공간과 쓰기 성능을 소모합니다.

sqlite
-- single-column index
CREATE INDEX idx_users_email ON users(email);

-- unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- composite index (column order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- IF NOT EXISTS for idempotent scripts
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- drop an index
DROP INDEX IF EXISTS idx_users_email;

-- list indexes
.indices users
-- or
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

부분 인덱스

부분 인덱스는 일치하는 행만 인덱싱하므로 더 작고 유지가 빠릅니다. 쿼리가 항상 안정적인 조건('active = 1', 'deleted_at IS NULL')으로 필터링할 때 이상적입니다. 옵티마이저가 사용하려면 쿼리의 WHERE가 인덱스의 WHERE와 일치하거나 더 엄격해야 합니다. 고유 부분 인덱스는 부분 집합에만 고유성을 강제하므로 '부모당 활성 행 하나' 제약에 완벽합니다.

sqlite
-- index only active users (smaller, faster)
CREATE INDEX idx_active_users ON users(username)
WHERE active = 1;

-- index only unpaid orders
CREATE INDEX idx_unpaid_orders ON orders(user_id)
WHERE status = 'unpaid';

-- unique partial index: one active session per user
CREATE UNIQUE INDEX idx_one_active_session
ON sessions(user_id) WHERE active = 1;

-- query must match the WHERE for the index to be used
SELECT * FROM users WHERE active = 1 AND username = 'alice';

표현식 인덱스

표현식 인덱스는 함수나 표현식의 결과를 인덱싱해 변환된 값의 빠른 조회를 가능하게 합니다. 단, 쿼리가 정확히 같은 표현식을 사용해야 합니다. 흔한 용도: 대소문자 무시 검색(LOWER), 날짜 버킷팅(strftime), JSON 필드 추출(json_extract). 결정적 함수만 가능합니다(RANDOM()이나 NOW() 불가). 표현식 인덱스는 강력하지만 스키마 변경을 까다롭게 만듭니다.

sqlite
-- index a function result (must match the query expression exactly)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- query that can use it (must use the same expression)
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- index a date extraction for fast monthly queries
CREATE INDEX idx_orders_month ON orders(strftime('%Y-%m', created));
SELECT * FROM orders WHERE strftime('%Y-%m', created) = '2024-06';

-- index a JSON path
CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'));
SELECT * FROM events WHERE json_extract(data, '$.type') = 'login';

-- indexed computed value (collation)
CREATE INDEX idx_users_name_ci ON users(username COLLATE NOCASE);

커버링과 WITHOUT ROWID

커버링 인덱스는 쿼리가 읽는 모든 컬럼을 포함해 SQLite가 인덱스만으로 쿼리를 충족하게 합니다(테이블 조회 없음). WITHOUT ROWID 테이블은 PRIMARY KEY를 클러스터형 인덱스로 만들어 행이 키순으로 저장되므로 키/값 워크로드와 PK 범위 스캔에 이상적입니다. 단점: AUTOINCREMENT 없음, rowid 별칭 없음, 갱신이 약간 복잡. 널리 도입하기 전에 벤치마크하세요.

sqlite
-- covering index: includes all columns the query needs,
-- so SQLite never reads the table
CREATE INDEX idx_orders_cover ON orders(user_id, status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 1;
-- ^ fully served by the index

-- WITHOUT ROWID tables: store rows IN the index (clustered)
CREATE TABLE kv (
  key   TEXT PRIMARY KEY,
  value TEXT
) WITHOUT ROWID;

-- the PK becomes the table itself; lookups by key are fast
SELECT value FROM kv WHERE key = 'config:theme';

-- good for key/value lookups and read-heavy tables
-- trade-off: no rowid, some features unsupported

인덱스 검사

EXPLAIN QUERY PLAN은 필수 도구입니다. 'USING INDEX'는 인덱스 사용, 'SCAN'은 전체 테이블 스캔(대개 나쁨)을 뜻합니다. sqlite_stat1(ANALYZE로 채워짐)은 플래너가 인덱스 선택에 쓰는 카디널리티 통계를 담습니다. REINDEX는 테이블을 재구축하지 않고 비대해지거나 손상된 인덱스를 재구축합니다. 큰 데이터 적재 후 ANALYZE를 실행해 플래너가 좋은 계획을 세우도록 최신 통계를 제공하세요.

sqlite
-- see if an index is used
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';
-- look for 'SEARCH users USING INDEX idx_users_email'

-- list all indexes on a table with their definitions
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

-- index stats (how often each index is used)
SELECT name, idx_scan, idx_read
FROM sqlite_stat1, sqlite_master
WHERE sqlite_stat1.tbl = 'users';

-- rebuild an index (after corruption or bloat)
REINDEX idx_users_email;
REINDEX;  -- rebuild all indexes

-- analyze tables to update query planner stats
ANALYZE;
09

뷰

뷰 만들기

뷰는 저장된 쿼리로 정의된 가상 테이블입니다. 데이터를 저장하지 않고(임시 뷰 제외) 매번 기본 SELECT를 다시 실행합니다. 복잡한 쿼리를 단순화하고 일관된 접근 패턴을 강제하며 스키마 변경을 애플리케이션에서 추상화하는 데 사용하세요. SQLite의 뷰는 기본적으로 읽기 전용입니다(갱신 가능 뷰에는 제약 있음). DROP VIEW는 기본 테이블에 영향을 주지 않습니다.

sqlite
-- a view is a saved SELECT, queried like a table
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE active = 1;

SELECT * FROM active_users WHERE age > 18;

-- view with calculated columns
CREATE VIEW user_summary AS
SELECT u.id, u.username,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- IF NOT EXISTS for idempotent scripts
CREATE VIEW IF NOT EXISTS active_users AS
SELECT id, username FROM users WHERE active = 1;

-- drop a view
DROP VIEW IF EXISTS active_users;

갱신 가능 뷰

SQLite는 단순 뷰(단일 기본 테이블, JOIN/집계/DISTINCT/GROUP BY 없음)에서 INSERT/UPDATE/DELETE를 허용합니다. 작업은 기본 테이블로 전달됩니다. SQLite는 WITH CHECK OPTION을 지원하지 않으므로 뷰를 통해 삽입한 행이 뷰의 WHERE를 만족하지 않아 이후 뷰에서 보이지 않을 수 있습니다. 쓰기가 필요한 복잡한 뷰에는 INSTEAD OF 트리거를 사용하세요.

sqlite
-- simple views (one base table, no aggregates/distinct/etc.)
-- are auto-updatable in SQLite
CREATE VIEW active_users AS
SELECT id, username, email, age FROM users WHERE active = 1;

-- INSERT through the view (active defaults/applies)
INSERT INTO active_users (id, username, email) VALUES (1, 'x', '[email protected]');

-- UPDATE through the view
UPDATE active_users SET age = 30 WHERE id = 1;

-- DELETE through the view
DELETE FROM active_users WHERE id = 1;

-- the WITH CHECK OPTION is NOT supported in SQLite, so
-- inserts/updates that don't match the view's WHERE still
-- land in the base table but vanish from the view.

뷰의 INSTEAD OF 트리거

INSTEAD OF 트리거는 뷰에 대한 쓰기를 가로채 사용자 로직을 실행하므로 어떤 뷰든 갱신 가능하게 만듭니다. 복잡한(다중 테이블, 집계) 뷰를 쓰기 가능하게 하는 표준 방법입니다. NEW와 OLD는 들어오는 행과 기존 행을 가리킵니다. INSTEAD OF 트리거는 기본 테이블이 아니라 뷰에서만 발생합니다. 캡슐화된 비즈니스 로직을 구현하거나 정규화된 스키마를 비정규화 뷰 뒤에 숨기는 데 사용하세요.

sqlite
-- a view joining multiple tables is not auto-updatable,
-- but INSTEAD OF triggers let you define the write behavior
CREATE VIEW user_orders AS
SELECT u.id AS user_id, u.username, o.id AS order_id, o.total
FROM users u LEFT JOIN orders o ON o.user_id = u.id;

-- route INSERTs on the view to the orders table
CREATE TRIGGER trg_user_orders_insert
INSTEAD OF INSERT ON user_orders
FOR EACH ROW
BEGIN
  INSERT INTO orders (id, user_id, total)
  VALUES (NEW.order_id, NEW.user_id, NEW.total);
END;

-- now this works:
INSERT INTO user_orders (user_id, order_id, total)
VALUES (1, 100, 49.99);

임시 뷰

임시 뷰는 현재 데이터베이스 연결 동안만 존재하고 연결이 끊기면 자동 삭제됩니다. 공유 스키마를 어지럽히지 않고 복잡한 임시 쿼리를 준비하는 데 완벽합니다. 별도 temp 스키마에 저장되므로 다른 연결(같은 파일이라도)에서는 보이지 않습니다. TEMP 뷰는 TEMP 테이블을 참조할 수 있어 세션 범위 ETL 파이프라인에 유용합니다.

sqlite
-- TEMP view exists only for the current connection
CREATE TEMP VIEW recent_orders AS
SELECT * FROM orders WHERE created > datetime('now', '-7 days');

-- the view vanishes when the connection closes
-- (no other connection can see it)

-- also valid: CREATE TEMPORARY VIEW
CREATE TEMPORARY VIEW my_scratch AS
SELECT id, username FROM users WHERE role = 'admin';

-- use case: ad-hoc analysis without polluting the schema
SELECT * FROM recent_orders WHERE total > 100;

-- list views
SELECT name, sql FROM sqlite_master
WHERE type = 'view';
SELECT name, sql FROM temp.sqlite_master
WHERE type = 'view';  -- temp views

구체화 뷰(에뮬레이션)

SQLite에는 네이티브 구체화 뷰가 없지만 정기적으로 갱신되는 일반 테이블로 같은 효과를 냅니다. 원자적 갱신을 위해 트랜잭션에서 새 테이블을 만들고 이름을 바꾸세요. 커밋 전까지 읽는 쪽은 이전 버전을 보고 커밋 후 새 버전을 봅니다. 재구축 후 인덱스를 추가하세요. 구체화 뷰는 쓰기/저장 비용을 읽기 속도와 맞바꾸므로 자주 조회하는 비싼 집계에 이상적입니다.

sqlite
-- SQLite has no native materialized views; emulate with a
-- real table that you refresh periodically

-- 1) create the 'materialized' table
CREATE TABLE mv_user_summary AS
SELECT u.id, u.username,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- 2) add an index for fast lookups
CREATE INDEX idx_mv_user_summary_id ON mv_user_summary(id);

-- 3) refresh (full rebuild)
DELETE FROM mv_user_summary;
INSERT INTO mv_user_summary
SELECT u.id, u.username, COUNT(o.id), COALESCE(SUM(o.total), 0)
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- 4) refresh atomically with a name swap
BEGIN;
CREATE TABLE mv_user_summary_new AS SELECT ...;
DROP TABLE mv_user_summary;
ALTER TABLE mv_user_summary_new RENAME TO mv_user_summary;
COMMIT;
10

트리거

BEFORE / AFTER 트리거

BEFORE 트리거는 행이 기록되기 전에 실행되므로 NEW 값을 검증(RAISE ABORT)하거나 변환하는 데 사용하세요. AFTER 트리거는 행이 커밋된 후 실행되므로 감사, 캐스케이드 효과, 비정규화에 사용하세요. FOR EACH ROW만 가능하며 문장 수준 트리거는 없습니다. NEW는 들어오는 행(INSERT/UPDATE), OLD는 이전 행(UPDATE/DELETE)을 담습니다. WHEN 절은 조건부로 트리거를 발생시킵니다.

sqlite
-- AFTER INSERT: audit log
CREATE TRIGGER trg_users_audit
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO users_audit (user_id, action, at)
  VALUES (NEW.id, 'insert', datetime('now'));
END;

-- BEFORE INSERT: validate / normalize data
CREATE TRIGGER trg_users_lower_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'email cannot be empty')
  WHERE NEW.email = '';
END;

-- AFTER UPDATE: log changes
CREATE TRIGGER trg_users_update_log
AFTER UPDATE ON users
FOR EACH ROW
WHEN OLD.email IS NOT NEW.email
BEGIN
  INSERT INTO email_changes (user_id, old_email, new_email, at)
  VALUES (NEW.id, OLD.email, NEW.email, datetime('now'));
END;

캐스케이드 효과 트리거

트리거는 비정규화 카운터, 요약 테이블, updated_at 타임스탬프를 자동으로 유지할 수 있습니다. 주의: 재귀 트리거 체인이 가능하므로(트리거가 자신의 테이블을 갱신) PRAGMA recursive_triggers로 제어하세요(하위 호환성을 위해 기본 OFF). 같은 이벤트의 여러 트리거 순서는 생성 순서를 따르며 같은 타이밍의 트리거 간 BEFORE/AFTER 우선순위는 없습니다.

sqlite
-- maintain a denormalized counter
CREATE TRIGGER trg_orders_count_inc
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE users
  SET order_count = order_count + 1
  WHERE id = NEW.user_id;
END;

CREATE TRIGGER trg_orders_count_dec
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  UPDATE users
  SET order_count = order_count - 1
  WHERE id = OLD.user_id;
END;

-- keep an 'updated_at' column fresh
CREATE TRIGGER trg_users_touch
AFTER UPDATE ON users
FOR EACH ROW
WHEN NEW.updated_at IS OLD.updated_at
BEGIN
  UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
END;

INSTEAD OF 트리거(뷰)

INSTEAD OF 트리거는 뷰의 원래 INSERT/UPDATE/DELETE 대신 실행되어 쓰기를 하나 이상의 기본 테이블로 라우팅합니다. 복잡한 뷰를 쓰기 가능하게 하는 표준 방법입니다. 뷰의 각 행마다 발생합니다. 뷰에는 INSTEAD OF 트리거만 정의할 수 있고 BEFORE/AFTER 트리거는 기본 테이블이 필요합니다. 정규화된 스키마 위에 API 스타일 추상화 계층을 만들 때 유용합니다.

sqlite
-- make a multi-table view writable
CREATE VIEW order_detail AS
SELECT o.id AS order_id, o.total, u.username, i.product_name
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items i ON i.order_id = o.id;

CREATE TRIGGER trg_order_detail_update
INSTEAD OF UPDATE ON order_detail
FOR EACH ROW
BEGIN
  UPDATE orders SET total = NEW.total WHERE id = NEW.order_id;
  UPDATE order_items SET product_name = NEW.product_name
  WHERE order_id = NEW.order_id;
END;

-- now UPDATE on the view routes to both tables
UPDATE order_detail SET total = 99.99, product_name = 'Widget'
WHERE order_id = 5;

RAISE와 오류 처리

RAISE는 트리거 실패 전파 방식을 제어합니다. ABORT(가장 흔함)는 문장을 취소하고 그 변경을 롤백하며, ROLLBACK은 전체 트랜잭션을 롤백하고, FAIL은 문장을 중단하되 트랜잭션의 이전 성공 문장은 유지하며, IGNORE는 문제 행만 건너뜁니다. RAISE(IGNORE)는 대량 적재 중 잘못된 행을 조용히 버리는 깔끔한 방법입니다.

sqlite
-- RAISE aborts the statement with a custom error
CREATE TRIGGER trg_check_age
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.age < 0
BEGIN
  SELECT RAISE(ABORT, 'age must be non-negative');
END;

-- ROLLBACK: undo the whole transaction, statement fails
CREATE TRIGGER trg_rollback_example
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN NEW.balance < 0
BEGIN
  SELECT RAISE(ROLLBACK, 'balance cannot go negative');
END;

-- FAIL: abort the current statement only (like ABORT)
-- IGNORE: skip this row, continue with the rest
CREATE TRIGGER trg_skip_invalid
BEFORE INSERT ON logs
FOR EACH ROW
WHEN NEW.level NOT IN ('info','warn','error')
BEGIN
  SELECT RAISE(IGNORE);
END;

트리거 관리

트리거는 sqlite_master(TEMP 트리거는 temp.sqlite_master)에 저장됩니다. DROP TRIGGER로 제거하며 ALTER TRIGGER는 없으므로 삭제 후 재생성하세요. TEMP 트리거는 연결 로컬이라 연결이 끊기면 사라지며 디버깅에 유용합니다. 트리거를 일시적으로 비활성화하는 내장 방법은 삭제뿐이므로 조건부 실행에는 WHEN 절을 사용하세요. 같은 이벤트의 여러 트리거는 타이밍 안에서 생성 순서로 발생합니다.

sqlite
-- list all triggers
SELECT name, tbl_name, sql FROM sqlite_master
WHERE type = 'trigger';

-- list triggers on a specific table
SELECT name, sql FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'users';

-- drop a trigger
DROP TRIGGER IF EXISTS trg_users_audit;

-- triggers in a TEMP schema (connection-local)
CREATE TEMP TRIGGER trg_temp_log
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  SELECT 'inserted ' || NEW.id;
END;

-- trigger timing order: BEFORE triggers fire in
-- creation order, then AFTER triggers in creation order.
11

PRAGMA 설정

PRAGMA 기초

PRAGMA는 SQLite의 독점 설정 인터페이스입니다. 설정은 저널링, 캐싱, 외래 키, 무결성, 쿼리 플래너를 제어합니다. 문서에 달리 명시되지 않으면 설정은 세션(연결별) 범위이며 트랜잭션 안에서 설정하면 무시될 수 있습니다. 일부 PRAGMA(journal_mode, page_size)는 트랜잭션 밖이나 테이블 생성 전에만 효과가 있습니다. PRAGMA compile_options로 빌드가 지원하는 기능을 확인하세요.

sqlite
-- PRAGMA is SQLite's configuration knob (CLI and APIs)
-- read a setting
PRAGMA journal_mode;
PRAGMA cache_size;
PRAGMA foreign_keys;

-- set a setting (session scope unless noted)
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -20000;   -- negative = KB, positive = pages
PRAGMA temp_store = MEMORY;

-- many PRAGMAs are no-ops below a certain SQLite version
-- check the compile-time options
PRAGMA compile_options;
-- e.g. ENABLE_FTS5, ENABLE_JSON1, MAX_ATTACHED, etc.

-- PRAGMAs are NOT part of SQL standard and are SQLite-only.

외래 키

SQLite는 외래 키 강제가 기본 OFF입니다. 다른 데이터베이스에서 온 사용자에게 악명 높은 함정입니다. 모든 연결에서 PRAGMA foreign_keys = ON으로 활성화해야 하며 트랜잭션 안에서는 설정할 수 없습니다. 이 설정이 있는 이유는 초기 스키마(FK 지원 이전)가 그렇지 않으면 깨지기 때문입니다. 운영 환경에서는 항상 활성화하세요. defer_foreign_keys는 의존성을 위반하는 순서로 데이터를 가져올 때 도움이 됩니다.

sqlite
-- foreign keys are OFF by default in SQLite!
PRAGMA foreign_keys = ON;   -- enable per connection

-- check current state
PRAGMA foreign_keys;

-- inspect FK definitions on a table
PRAGMA foreign_key_list(orders);
-- returns: id, seq, table, from, to, on_update, on_delete, match

-- enable FKs in your app's connection setup, every time
-- (this is a frequent source of bugs when moving from MySQL/PG)

-- deferred FK checking (checked at COMMIT, not per statement)
PRAGMA defer_foreign_keys = ON;
-- or declare DEFERRABLE INITIALLY DEFERRED on the FK

저널 모드와 WAL

WAL(Write-Ahead Logging)은 대부분의 앱에 권장되는 저널 모드입니다. 읽기와 쓰기가 서로를 막지 않고 내구성도 좋습니다. 메인 DB 옆에 -wal과 -shm 사이드카 파일을 만듭니다. WAL은 데이터베이스 파일별로 지속되므로 한 번만 설정하면 됩니다. wal_checkpoint(TRUNCATE)로 WAL 내용을 메인 파일에 합치고 공간을 회수하세요. WAL은 공유 메모리가 필요하므로 네트워크 파일 시스템에서는 피하세요.

sqlite
-- WAL: write-ahead logging (best for most apps)
PRAGMA journal_mode = WAL;
-- benefits: readers never block writers, writers never block
--           readers, crash recovery is fast
-- trade-off: creates -wal and -shm sidecar files

-- other modes: DELETE (default), TRUNCATE, PERSIST, MEMORY, OFF
PRAGMA journal_mode = DELETE;

-- WAL checkpoint: merge -wal back into the main file
PRAGMA wal_checkpoint;
PRAGMA wal_checkpoint(TRUNCATE);  -- also shrinks the -wal file

-- auto-checkpoint threshold (pages of WAL before auto-checkpoint)
PRAGMA wal_autocheckpoint = 1000;

-- WAL is persistent: setting it once keeps it for that DB file.

동기화와 내구성

synchronous는 SQLite가 fsync를 얼마나 적극적으로 호출할지 제어합니다. FULL이 가장 안전하고(커밋마다 fsync), NORMAL은 WAL 사용 시 권장 값이며(체크포인트에서 fsync, 커밋마다 아님, 실제 손상 위험 무시 가능), OFF는 전원 손실 시 데이터베이스 손상 위험이 있습니다. 일회성 데이터나 재구축 가능한 캐시에만 사용하세요. page_size는 스키마 생성 전에 설정해야 하며 나중에 바꾸려면 VACUUM이 필요합니다.

sqlite
-- how thoroughly SQLite flushes to disk on commit
PRAGMA synchronous;        -- read current value
PRAGMA synchronous = FULL;       -- safest, slowest (default for rollback journal)
PRAGMA synchronous = NORMAL;     -- safe with WAL, fast (recommended for WAL)
PRAGMA synchronous = OFF;        -- fastest; corruption risk on power loss
PRAGMA synchronous = EXTRA;      -- even more careful than FULL

-- trade-off: durability vs speed
-- FULL: fsync at every commit (survives OS crash)
-- NORMAL (WAL): fsync at checkpoint, not every commit
-- OFF: no fsync — fast but may corrupt on power loss

-- page size (set BEFORE creating tables, ideally)
PRAGMA page_size = 4096;
PRAGMA page_size;

캐시, 메모리, 임시 저장소

cache_size는 인메모리 페이지 캐시입니다. 읽기 위주 워크로드에서는 클수록 대체로 좋습니다. 음수는 KB, 양수는 페이지 단위입니다. temp_store = MEMORY는 중간 결과(정렬, 임시 테이블)를 RAM으로 옮겨 큰 집계를 빠르게 합니다. mmap_size는 읽기용 메모리 매핑 I/O를 켜서 큰 읽기 전용 데이터베이스를 빠르게 할 수 있습니다. 앱의 메모리 예산과 접근 패턴에 맞춰 조정하세요.

sqlite
-- page cache (negative = KB, positive = pages)
PRAGMA cache_size = -65536;   -- 64 MB cache
PRAGMA cache_size = 20000;    -- 20000 pages

-- where temp tables and intermediate results live
PRAGMA temp_store = DEFAULT;  -- compile-time default
PRAGMA temp_store = FILE;     -- temp files
PRAGMA temp_store = MEMORY;   -- all temp data in RAM

-- mmap memory-mapped I/O for reads
PRAGMA mmap_size = 268435456;  -- 256 MB

-- soft heap limit for the SQLite library
PRAGMA soft_heap_limit = 100000000;  -- 100 MB

-- store prepared statements in memory between calls
PRAGMA cache_spill;

무결성과 스키마 정보

integrity_check는 전체 데이터베이스 구조와 내용을 검증합니다(느리지만 철저). quick_check는 내용 검증을 생략해 훨씬 빠릅니다. 백업 중이나 충돌 후에 integrity_check를 실행하세요. table_info는 컬럼 메타데이터를 보고합니다(FTS5 rowid 같은 숨은 컬럼에는 table_xinfo 사용). index_list와 index_info는 존재하는 인덱스와 그 컬럼을 보여줍니다. database_list는 연결된 데이터베이스를 보여줍니다.

sqlite
-- full integrity check (slow on large DBs)
PRAGMA integrity_check;
-- returns 'ok' or a list of problems

-- quick check (faster, less thorough)
PRAGMA quick_check;

-- list tables / schema
SELECT name, sql FROM sqlite_master WHERE type = 'table';

-- table info: columns, types, notnull, default, pk
PRAGMA table_info(users);
-- returns: cid, name, type, notnull, dflt_value, pk

-- extended table info (includes hidden columns from FTS/etc.)
PRAGMA table_xinfo(users);

-- index list with origin and unique flag
PRAGMA index_list(users);
PRAGMA index_info(idx_users_email);

-- database file header info
PRAGMA database_list;
12

날짜와 시간 함수

date / time / datetime

SQLite에는 네이티브 DATE 타입이 없고 날짜는 ISO-8601 TEXT('YYYY-MM-DD HH:MM:SS')입니다. date/time 함수는 다양한 입력 형식(ISO, 슬래시, 월 이름, 'unixepoch' 수정자를 통한 Unix 타임스탬프)을 받습니다. 'now'는 행마다가 아니라 문장당 한 번 평가되므로 다중 행 UPDATE의 모든 행이 같은 타임스탬프를 받습니다(보통 원하는 동작). UTC로 저장하고 표시할 때 localtime으로 서식화하세요.

sqlite
-- current date/time (UTC by default)
SELECT date('now');              -- '2024-06-15'
SELECT time('now');              -- '14:30:00'
SELECT datetime('now');          -- '2024-06-15 14:30:00'
SELECT julianday('now');         -- 2460476.1042 (days since 4713 BC)

-- parse various formats
SELECT date('2024-06-15');
SELECT date('2024/06/15');
SELECT date('June 15, 2024');
SELECT datetime('2024-06-15 14:30:00.123');
SELECT date('2024-06-15','unixepoch');  -- from Unix timestamp (seconds)

-- Unix timestamp to datetime
SELECT datetime(1718450400, 'unixepoch');
SELECT datetime(1718450400, 'unixepoch', 'localtime');

strftime 서식

strftime은 범용 날짜 서식기이며 형식 코드는 C의 strftime과 같습니다. 그룹화를 위해 날짜의 일부(연, 월, 요일)를 추출하거나 사용자 정의 표시 형식을 렌더링할 때 사용하세요. 월별 그룹화에는 strftime('%Y-%m', created)가 'YYYY-MM'을 만들어 자연스럽게 정렬/그룹화됩니다. SQLite에는 DAYNAME()/MONTHNAME()이 없으므로 요일 이름이 필요하면 strftime('%w', ...)와 CASE를 결합하세요.

sqlite
-- strftime is the most flexible formatter
--   strftime(format, timestring, modifier, modifier, ...)
SELECT strftime('%Y-%m-%d', 'now');          -- '2024-06-15'
SELECT strftime('%Y-%m-%d %H:%M', 'now');    -- '2024-06-15 14:30'
SELECT strftime('%H:%M:%S', 'now');          -- '14:30:00'

-- common format codes
--   %Y year (4-digit)    %m month (01-12)   %d day (01-31)
--   %H hour (00-23)      %M minute (00-59)  %S second (00-59)
--   %j day of year       %w day of week (0=Sun)
--   %W week of year      %p AM/PM           %I hour (01-12)

-- get the day name
SELECT strftime('%w', '2024-06-15');   -- '6' (Saturday)
SELECT strftime('%Y-%m', 'now');       -- month bucket for grouping

수정자(산술)

수정자는 왼쪽에서 오른쪽으로 연결되어 날짜를 이동하거나 정렬합니다. 간격은 '+N units' / '-N units'입니다(단위: days, hours, minutes, seconds, months, years). 'start of month/year/day'는 기간 경계로 맞추므로 월별 리포트에 완벽합니다. 'weekday N'은 다음 N요일의 날짜를 반환합니다(오늘이 N요일이면 오늘). 'start of month', '+1 month', '-1 day'를 결합하면 현재 달의 마지막 날이 되는 고전 관용구입니다.

sqlite
-- shift a date by an interval
SELECT date('now', '+1 day');           -- tomorrow
SELECT date('now', '-1 month');         -- one month ago
SELECT date('now', '+1 year', '+1 day');-- next year + 1 day
SELECT datetime('now', '+2 hours', '+30 minutes');

-- start of period
SELECT date('now', 'start of month');       -- first day of month
SELECT date('now', 'start of year');        -- first day of year
SELECT date('now', 'start of day');         -- midnight today

-- weekday: next occurrence of a given weekday (0=Sun, 1=Mon, ... 6=Sat)
SELECT date('now', 'weekday 1');            -- next Monday
SELECT date('now', 'weekday 0', '+7 days'); -- Sunday after this one

-- combine modifiers
SELECT date('now', 'start of month', '+1 month', '-1 day'); -- last day of month

Unix 시간과 율리우스일

Unix 타임스탬프는 에포크 이후 정수 초입니다. unixepoch()(3.38+)는 정수를 직접 반환합니다. 율리우스일은 연속 소수 일수이므로 두 julianday 값을 빼면 경과 시간이 일 단위로 나옵니다(초는 86400을 곱함). 나이 계산에서 365.25로 나누면 윤년을 근사적으로 처리합니다. 법적 정밀도가 필요하면 연/월/일 구성 요소를 명시적으로 비교하세요.

sqlite
-- Unix timestamp (seconds since 1970-01-01 UTC)
SELECT strftime('%s', 'now');                  -- current Unix time (text)
SELECT unixepoch('now');                       -- integer Unix time (SQLite 3.38+)
SELECT datetime(1718450400, 'unixepoch');      -- back to datetime

-- Julian day (fractional days since 4713-01-01 BC)
SELECT julianday('now');                       -- 2460476.6042
SELECT julianday('2024-06-15') - julianday('2024-06-10');  -- 5.0 (days between)

-- compute elapsed time in seconds
SELECT (julianday('now') - julianday('2024-01-01')) * 86400.0 AS seconds_elapsed;

-- human-readable age from a birthdate
SELECT CAST((julianday('now') - julianday('1990-05-20')) / 365.25 AS INT) AS age;

시간대와 localtime

SQLite에는 시간대 데이터베이스가 없습니다. 'now'는 UTC이고 'localtime'/'utc' 수정자는 호스트 OS의 시간대 설정에 의존합니다. 권장 패턴: 항상 타임스탬프를 UTC로 저장하고(datetime('now')는 UTC) 표시할 때만 'localtime' 수정자로 현지 시간으로 변환하세요. 이렇게 하면 다른 시간대의 서버 간 모호성과 일광 절약 시간 전환 문제를 피할 수 있습니다. 현지 시간을 절대 저장하지 마세요.

sqlite
-- 'now' is always UTC; convert to local time for display
SELECT datetime('now', 'localtime');          -- local wall-clock time
SELECT datetime('now');                       -- UTC

-- convert a stored UTC timestamp to local time
SELECT datetime(created, 'localtime') FROM users;

-- convert local time back to UTC for storage
SELECT datetime('2024-06-15 14:30:00', 'utc');

-- best practice: store UTC, format for display
CREATE TABLE events (ts TEXT DEFAULT (datetime('now')));  -- UTC
-- display:
SELECT datetime(ts, 'localtime') AS local_ts FROM events;

-- timezone is determined by the OS; SQLite has no TZ database.
13

문자열 함수

길이와 부분 문자열

length()는 TEXT에서 문자 수, BLOB에서 바이트 수를 셉니다. UTF-8 바이트 길이는 BLOB으로 캐스트한 뒤 재세요. substr()은 1부터 시작하고 음수 시작은 끝에서부터 셉니다. SQLite에는 네이티브 LPAD/RPAD가 없으므로 문자열 연결과 substr로 에뮬레이트하세요. 다중 바이트 텍스트를 자를 때 바이트/문자 혼동에 주의하세요. substr은 문자 단위라 안전하지만 바이트 단위 슬라이싱은 그렇지 않습니다.

sqlite
SELECT length('hello');              -- 5
SELECT length('日本語');             -- 3 (characters, not bytes)
SELECT length(x'00ff');             -- 2 (blob length in bytes)

SELECT substr('hello world', 7);    -- 'world' (1-indexed)
SELECT substr('hello world', 1, 5); -- 'hello' (length 5)
SELECT substr('hello', -3);         -- 'llo' (negative = from end)
SELECT substr('hello', -3, 2);      -- 'll'

-- byte length (vs character length)
SELECT length('日本語');            -- 3 (characters)
SELECT length(CAST('日本語' AS BLOB)); -- 9 (UTF-8 bytes)

-- left/right pad (workaround; no native LPAD/RPAD)
SELECT substr('0000' || '42', -4, 4);  -- '0042'

대소문자와 다듬기

upper()/lower()는 기본적으로 ASCII 문자에만 영향을 주며 ICU를 로드하지 않으면 비ASCII 문자는 그대로입니다. trim()은 기본적으로 앞뒤 공백을 제거하고 두 번째 인자로 특정 문자를 지정할 수 있습니다. replace()는 리터럴 부분 문자열 교체입니다(정규식/패턴 없음). 문자 집합 치환을 위한 네이티브 TRANSLATE()는 없으므로 replace()를 연결하거나 사용자 정의 함수를 사용하세요.

sqlite
SELECT upper('hello');       -- 'HELLO'
SELECT lower('HELLO');       -- 'hello'
SELECT upper('日本語');      -- '日本語' (no case to change)

SELECT trim('  hi  ');            -- 'hi'  (both sides)
SELECT ltrim('  hi  ');           -- 'hi   ' (left only)
SELECT rtrim('  hi  ');           -- '  hi' (right only)

-- trim specific characters
SELECT trim('xxhelloxx', 'x');    -- 'hello'
SELECT ltrim('aaabbb', 'a');      -- 'bbb'

-- replace characters
SELECT replace('a-b-c', '-', '/'); -- 'a/b/c'
SELECT replace('Hello World', 'o', '0'); -- 'Hell0 W0rld'

연결과 printf

|| 연산자는 표준 SQL 연결자이지만 NULL을 전파합니다(NULL || x = NULL). NULL이 가능하면 COALESCE로 감싸세요. printf()/format()은 C 스타일 서식을 제공하며 패딩, 숫자 서식, 쉼표 구분자가 있는 문자열을 만드는 가장 깔끔한 방법입니다. quote()는 SQL에 삽입할 수 있도록 안전하게 따옴표와 이스케이프된 문자열을 반환하므로 동적 SQL을 만들 때 유용합니다.

sqlite
-- || operator concatenates
SELECT 'Hello' || ' ' || 'World';      -- 'Hello World'
SELECT username || ' <' || email || '>' FROM users;

-- NULL propagates through || (NULL || 'x' IS NULL)
SELECT 'a' || NULL || 'b';             -- NULL
-- use COALESCE to handle NULLs:
SELECT COALESCE(username, '') || ' <' || COALESCE(email, '') || '>';

-- printf-style formatting (also called format())
SELECT printf('%s has %d orders', 'alice', 5);  -- 'alice has 5 orders'
SELECT format('%,d', 1234567);                   -- '1,234,567' (3.38+)
SELECT printf('%5.2f', 3.14159);                 -- ' 3.14'
SELECT printf('%04d', 42);                       -- '0042'

-- quote() escapes a string for SQL
SELECT quote("It's alive");   -- '''It''s alive'''

분할과 검색

instr()은 부분 문자열의 첫 위치를 찾습니다(1부터 시작, 없으면 0). SQLite에는 네이티브 SPLIT_PART가 없으므로 substr + instr이나 재귀 CTE로 에뮬레이트하세요. '발생 횟수 세기' 관용구는 구분자를 제거하기 전후의 길이를 뺍니다. char()는 코드 포인트로 문자열을 만들고 unicode()는 첫 문자의 코드 포인트를 반환합니다. 전체 텍스트 분할에는 json_each로 JSON 배열을 로드하세요.

sqlite
-- find position of substring (1-indexed; 0 if not found)
SELECT instr('hello world', 'world');   -- 7
SELECT instr('hello', 'xyz');           -- 0

-- emulate split by getting the Nth field
-- (no native SPLIT_PART; use a CTE or substring + instr)
SELECT
  substr('a,b,c,d', 1, instr('a,b,c,d', ',') - 1) AS first;  -- 'a'

-- count occurrences of a substring
SELECT (length('a,b,c,d') - length(replace('a,b,c,d', ',', ''))) / length(',') AS commas;
-- 3

-- reverse a string
SELECT reverse('hello');   -- 'olleh'

-- character from integer code
SELECT char(65, 66, 67);   -- 'ABC'
SELECT unicode('A');       -- 65

LIKE / GLOB / 콜레이션

LIKE는 기본적으로 ASCII에서 대소문자를 구분하지 않습니다(자주 놀라는 부분). 대소문자 구분에는 COLLATE BINARY를 사용하세요. GLOB은 항상 대소문자를 구분하고 셸 스타일 문자 클래스를 지원합니다. NOCASE는 대소문자 무시 비교를 위한 내장 콜레이션입니다. 컬럼 인덱스에 적용하면 대소문자 무시 쿼리가 인덱스를 사용합니다. 비ASCII 대소문자 폴딩에는 ICU 확장을 로드하거나 소문자 사본 컬럼을 인덱스와 함께 저장하세요.

sqlite
-- LIKE: case-insensitive (ASCII), wildcards % and _
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE username LIKE '_lice';

-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'Al%' COLLATE BINARY;

-- make LIKE case-insensitive for non-ASCII too (needs ICU)
-- or store a lowercased copy and query that
SELECT * FROM users WHERE LOWER(username) = LOWER('Alice');

-- GLOB: case-sensitive, Unix-style (* ? [abc] [a-z])
SELECT * FROM users WHERE username GLOB 'A*';
SELECT * FROM users WHERE username GLOB '[A-D]*';

-- built-in collations: BINARY (default), NOCASE, RTRIM
CREATE INDEX idx_users_name_ci ON users(username COLLATE NOCASE);
SELECT * FROM users WHERE username = 'ALICE' COLLATE NOCASE;

-- COLLATE in ORDER BY
SELECT * FROM users ORDER BY username COLLATE NOCASE;
14

JSON 함수

값 추출

json_extract(그리고 -> / ->> 연산자, 3.38+)는 JSON 읽기의 핵심입니다. $는 루트이고 $.key는 객체 속성, $.arr[i]는 배열 요소(0부터 시작)에 접근합니다. ->>는 스칼라(text/integer/real)를, ->는 JSON을 반환합니다(배열에서 '$.b'는 배열을 JSON으로 반환). 자주 조회하는 JSON 필드는 일반 컬럼으로 저장하거나 성능을 위해 json_extract에 표현식 인덱스를 추가하세요.

sqlite
-- json_extract: get a value by JSON path
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.a');       -- 1
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.b[1]');    -- 20
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.b');       -- '[10,20,30]'

-- -> returns JSON; ->> returns scalar (text/integer/etc.)
SELECT '{"a": 1, "b": "x"}' -> '$.a';   -- 1 (as JSON)
SELECT '{"a": 1, "b": "x"}' ->> '$.a';  -- 1 (as scalar)
SELECT '{"a": 1, "b": "x"}' ->> '$.b';  -- 'x' (as text)

-- nested paths
SELECT json_extract('{"u": {"name": "alice"}}', '$.u.name');   -- 'alice'

-- array length
SELECT json_array_length('[1, 2, 3]');  -- 3
SELECT json_array_length('{"a": [1,2]}', '$.a');  -- 2

-- get object keys
SELECT json_each.name FROM json_each('{"a":1,"b":2}');  -- 'a', 'b'

JSON 만들기

json_object와 json_array는 SQL 값으로 JSON을 구성합니다(NULL은 생략되지 않고 JSON null이 됨). json_group_array와 json_group_object(3.38+)는 쿼리 행으로 JSON을 만드는 집계 함수로, SQL에서 직접 JSON API를 생성할 때 매우 유용합니다. 생성자를 중첩해 복잡한 문서를 만드세요. 문자열을 JSON 문자열 리터럴로 안전하게 삽입하려면 json_quote를 사용하세요.

sqlite
-- build a JSON object
SELECT json_object('id', 1, 'name', 'alice', 'active', 1);
-- {"id":1,"name":"alice","active":1}

-- build a JSON array
SELECT json_array(1, 2, 3, 'four', NULL);
-- [1,2,3,"four",null]

-- nest them
SELECT json_object(
  'user', json_object('id', 1, 'name', 'alice'),
  'roles', json_array('admin', 'editor')
);

-- aggregate rows into a JSON array
SELECT json_group_array(username) FROM users WHERE active = 1;
-- ["alice","bob","carol"]

-- aggregate into a JSON object (key:value)
SELECT json_group_object(username, email) FROM users WHERE active = 1;
-- {"alice":"[email protected]","bob":"[email protected]"}

-- quote a string as JSON
SELECT json_quote('hello');   -- '"hello"'

JSON 수정

json_set/insert/replace는 삽입과 갱신 동작을 구분하고 json_remove는 경로를 삭제합니다. 이들은 새 JSON 값을 반환합니다. SQLite JSON은 불변이므로 변경을 영구화하려면 결과로 컬럼을 UPDATE하세요: UPDATE t SET data = json_set(data, '$.x', 1) WHERE id = 5. json_patch(RFC 7396)는 객체를 병합하고 패치의 null 값은 키를 삭제합니다. 큰 문서는 자주 쓰는 필드를 별도 컬럼으로 저장하는 것을 고려하세요.

sqlite
-- json_set: insert or update a path
SELECT json_set('{"a": 1}', '$.b', 2);
-- {"a":1,"b":2}
SELECT json_set('{"a": 1}', '$.a', 99);
-- {"a":99}

-- json_insert: insert only if path does NOT exist
SELECT json_insert('{"a": 1}', '$.a', 99, '$.b', 2);
-- {"a":1,"b":2}  (a unchanged because it exists)

-- json_replace: update only if path EXISTS
SELECT json_replace('{"a": 1}', '$.a', 99, '$.b', 2);
-- {"a":99}  (b not added because it doesn't exist)

-- json_remove: delete a path
SELECT json_remove('{"a": 1, "b": 2}', '$.b');
-- {"a":1}

-- json_patch: merge per RFC 7396
SELECT json_patch('{"a": 1, "b": 2}', '{"b": 3, "c": 4}');
-- {"a":1,"b":3,"c":4}

JSON 배열 조회

json_each와 json_tree는 JSON을 행으로 펼치는 가상 테이블 값 함수입니다. json_each는 단일 배열/객체를 처리하고 json_tree는 전체 구조를 재귀적으로 순회해 fullkey 경로를 노출합니다. 덕분에 JSON 배열에 집합 기반 연산(필터링, 조인, 집계)을 할 수 있습니다. 컬럼의 객체 배열을 조회하려면 LATERAL 스타일 조인을 사용하세요: FROM users u, json_each(u.tags) e.

sqlite
-- json_each: virtual table, one row per array element
SELECT value FROM json_each('[10, 20, 30]');
-- 10, 20, 30

-- with index
SELECT key, value FROM json_each('["a","b","c"]');
-- 0,'a'  1,'b'  2,'c'

-- query an array stored in a column
SELECT u.username, e.value AS tag
FROM users u, json_each(u.tags) e
WHERE e.value LIKE 'admin%';

-- aggregate back to JSON
SELECT json_group_array(value)
FROM json_each('[10, 20, 30]')
WHERE value > 15;
-- [20,30]

-- json_tree: recursively walk nested structures
SELECT fullkey, value
FROM json_tree('{"a": {"b": [1, 2]}}');
-- '$','{"a":{"b":[1,2]}}'
-- '$.a','{"b":[1,2]}'
-- '$.a.b[0]',1
-- '$.a.b[1]',2

JSON 인덱싱과 검증

빠른 JSON 필드 조회를 위해 json_extract 표현식에 인덱스를 만드세요. 혜택을 받으려면 쿼리가 동일한 표현식을 사용해야 합니다. json_valid()는 1/0을 반환하므로 CHECK 제약에서 JSON 전용 컬럼을 강제할 수 있습니다. json_type()은 경로의 JSON 타입('object', 'array', 'integer', 'real', 'string', 'boolean', 'null')을 보고합니다. JSON 워크로드가 많으면 자주 쓰는 필드를 일반 인덱스 컬럼으로 비정규화하고 JSON은 유연한 나머지에 사용하세요.

sqlite
-- expression index on a JSON field for fast lookups
CREATE INDEX idx_events_type
ON events(json_extract(data, '$.type'));
SELECT * FROM events WHERE json_extract(data, '$.type') = 'login';

-- unique index on a JSON field
CREATE UNIQUE INDEX idx_users_email_json
ON users(json_extract(profile, '$.email'));

-- validate JSON
SELECT json_valid('{"a": 1}');     -- 1
SELECT json_valid('{a: 1}');       -- 0 (unquoted keys)

-- JSON type of a path
SELECT json_type('{"a": 1, "b": [1,2]}', '$.a');  -- 'integer'
SELECT json_type('{"a": 1, "b": [1,2]}', '$.b');  -- 'array'

-- check constraint for valid JSON columns
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  data TEXT CHECK (json_valid(data))
);
15

윈도우 함수

OVER 절 기초

윈도우 함수(SQLite 3.25+)는 현재 행과 관련된 행 집합에 걸쳐 계산하되 GROUP BY처럼 행을 합치지 않습니다. OVER ()는 '모든 행', OVER (ORDER BY ...)는 순위용 정렬을 정의합니다. ROW_NUMBER는 고유하고, RANK는 동점 뒤에 간격을 두며, DENSE_RANK는 두지 않습니다. 윈도우 함수는 SELECT와 ORDER BY에 나타나고 WHERE에는 쓸 수 없으므로 외부 쿼리나 CTE로 필터링하세요.

sqlite
-- window functions: aggregate OVER a 'window' of rows
-- without collapsing rows (unlike GROUP BY)
SELECT username, age,
  AVG(age) OVER () AS overall_avg
FROM users;

-- ROW_NUMBER: unique sequential number per row
SELECT username,
  ROW_NUMBER() OVER (ORDER BY created) AS row_num
FROM users;

-- RANK / DENSE_RANK
SELECT username, age,
  RANK()       OVER (ORDER BY age DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank
FROM users;

PARTITION BY

PARTITION BY는 윈도우 함수용으로 행을 그룹으로 나눕니다. GROUP BY와 비슷하지만 행을 합치지 않아 각 행이 정체성을 유지하면서 그룹별 집계를 얻습니다. '그룹당 상위 N개' 패턴(ROW_NUMBER + 외부 WHERE rn <= N)은 '고객당 상위 3개 주문', '작성자당 최신 5개 글' 등의 표준 해법입니다. 보통 상관 서브쿼리보다 훨씬 빠릅니다.

sqlite
-- per-group ranking / aggregation
SELECT username, role, age,
  ROW_NUMBER() OVER (PARTITION BY role ORDER BY age DESC) AS rank_in_role,
  AVG(age)     OVER (PARTITION BY role)                   AS avg_role_age
FROM users;

-- top N per group (common pattern)
SELECT * FROM (
  SELECT user_id, total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
  FROM orders
) WHERE rn <= 3;

-- percent of group total
SELECT username, role,
  age * 1.0 / SUM(age) OVER (PARTITION BY role) AS pct_of_role_total
FROM users;

프레임 지정

프레임은 윈도우 함수가 현재 행을 기준으로 어떤 행을 보는지 정의합니다. UNBOUNDED PRECEDING..CURRENT ROW는 누적 합계를 줍니다. N PRECEDING..N FOLLOWING은 이동 평균 같은 슬라이딩 윈도우를 줍니다. ORDER BY가 있는 기본 프레임은 RANGE를 사용해 동점 행(같은 ORDER BY 값)을 포함합니다. ROWS는 엄격한 행 수 기준입니다. 프레임과 ORDER BY를 혼동하면 잘못된 분석 결과가 나오는 흔한 원인입니다.

sqlite
-- RUNNING TOTAL: cumulative sum over rows so far
SELECT username, total,
  SUM(total) OVER (ORDER BY created
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders;

-- MOVING AVERAGE: 3-row window centered on current row
SELECT username, total,
  AVG(total) OVER (ORDER BY created
                   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM orders;

-- default frame for ORDER BY windows:
--   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- default frame (no ORDER BY):
--   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- ROWS vs RANGE: ROWS counts rows; RANGE includes peers.

LAG와 LEAD

LAG와 LEAD는 현재 행 기준 다른 행에 접근하므로 시계열 분석(일별 변화, 연속 기록 탐지)에 필수입니다. 선택적 세 번째 인자는 범위를 벗어난 행의 기본값입니다. FIRST_VALUE/LAST_VALUE는 프레임 경계의 값을 반환합니다. LAST_VALUE는 명시적 UNBOUNDED FOLLOWING 프레임이 없으면 현재 행 값을 반환하는 고전적 함정이 있습니다.

sqlite
-- compare current row to previous / next row
SELECT username, created,
  LAG(created, 1)  OVER (ORDER BY created) AS prev_created,
  LEAD(created, 1) OVER (ORDER BY created) AS next_created
FROM users;

-- difference from previous value (time-series analysis)
SELECT date, sales,
  sales - LAG(sales, 1) OVER (ORDER BY date) AS day_over_day
FROM daily_sales;

-- LAG with default value and offset
SELECT username, created,
  LAG(username, 3, 'N/A') OVER (ORDER BY created) AS three_back
FROM users;

-- FIRST_VALUE / LAST_VALUE / NTH_VALUE
SELECT username, age,
  FIRST_VALUE(age) OVER (ORDER BY age) AS youngest,
  LAST_VALUE(age)  OVER (ORDER BY age
                         ROWS BETWEEN UNBOUNDED PRECEDING
                         AND UNBOUNDED FOLLOWING) AS oldest
FROM users;

NTILE과 백분위

NTILE(n)은 정렬된 행을 n개의 대략 같은 버킷으로 나눕니다. 사분위는 4, 백분위는 100, 십분위는 10을 사용하세요. CUME_DIST는 현재 행 값 이하인 행의 비율이고 PERCENT_RANK는 0..1 비율의 상대 순위입니다. 통계/분석 쿼리의 구성 요소입니다. 모든 윈도우 함수처럼 행을 합치지 않으므로 필터링하려면 외부 쿼리로 감싸세요.

sqlite
-- divide rows into N equal buckets (quartiles, percentiles)
SELECT username, age,
  NTILE(4) OVER (ORDER BY age) AS quartile
FROM users;

-- CUME_DIST: cumulative distribution (0..1)
SELECT username, age,
  CUME_DIST() OVER (ORDER BY age) AS cume_dist
FROM users;

-- PERCENT_RANK: rank as a fraction (0..1)
SELECT username, age,
  PERCENT_RANK() OVER (ORDER BY age) AS pct_rank
FROM users;

-- NTH_VALUE: value at a specific row in the frame
SELECT username, age,
  NTH_VALUE(age, 2) OVER (ORDER BY age) AS second_age
FROM users;
16

CTE(WITH 절)

기본 CTE

WITH로 정의하는 CTE(공통 테이블 표현식)는 이름이 있는 범위 지정 서브쿼리로 가독성을 높이고 같은 문장에서 여러 번 참조할 수 있습니다. SQLite는 보통 CTE를 구체화(임시 테이블에 기록)하므로 성능은 파생 테이블과 비슷합니다. 복잡한 쿼리를 단계로 나누거나 사전 집계하거나 서브쿼리 반복을 피할 때 CTE를 사용하세요. 표준 SQL이라 이식성이 좋습니다.

sqlite
-- a CTE is a named temporary result set, scoped to one statement
WITH active_user_count AS (
  SELECT COUNT(*) AS n FROM users WHERE active = 1
)
SELECT username,
  (SELECT n FROM active_user_count) AS total_active
FROM users WHERE active = 1;

-- CTE in a join
WITH user_totals AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
)
SELECT u.username, COALESCE(t.spent, 0) AS spent
FROM users u
LEFT JOIN user_totals t ON t.user_id = u.id
ORDER BY spent DESC;

-- CTEs make complex queries readable; they are syntactic sugar
-- (SQLite often materializes them, like a temp table).

여러 CTE

쉼표로 구분된 여러 CTE는 같은 WITH에서 앞선 CTE를 참조할 수 있으므로 한 문장 안의 단계별 데이터 파이프라인에 이상적입니다. 각 CTE는 논리적 단계이고 위에서 아래로 읽으면 데이터 흐름을 반영합니다. 중첩 서브쿼리보다 훨씬 유지보수하기 쉽습니다. SQLite는 각 CTE를 구체화하므로 중간 결과가 매우 크면 하나의 거대한 CTE 체인보다 문장 간 임시 테이블을 고려하세요.

sqlite
-- chain multiple CTEs, each able to reference earlier ones
WITH
active_users AS (
  SELECT id, username FROM users WHERE active = 1
),
user_orders AS (
  SELECT user_id, COUNT(*) AS n_orders, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
),
labeled AS (
  SELECT au.username,
    COALESCE(uo.n_orders, 0) AS orders,
    COALESCE(uo.spent, 0)    AS spent
  FROM active_users au
  LEFT JOIN user_orders uo ON uo.user_id = au.id
)
SELECT * FROM labeled
WHERE orders > 0
ORDER BY spent DESC;

재귀 CTE

재귀 CTE는 앵커(기저 사례)와 재귀 멤버가 UNION(중복 제거) 또는 UNION ALL로 연결됩니다. 트리(조직도, 카테고리 트리, 스레드 댓글)와 그래프를 순회하는 표준 SQL 방법입니다. SQLite는 재귀 깊이를 제한합니다(기본 1000, PRAGMA로 조정). SQLite에는 GENERATE_SERIES가 없으므로(확장은 존재) '시리즈 생성' 기법이 매우 유용합니다.

sqlite
-- a recursive CTE references itself; perfect for hierarchies
WITH RECURSIVE subordinates AS (
  -- anchor: top-level manager
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- recursive: their direct reports, one level deeper
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT depth, id, name FROM subordinates ORDER BY depth, name;

-- generate a series of numbers (no native GENERATE_SERIES)
WITH RECURSIVE cnt(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cnt WHERE n < 10
)
SELECT n FROM cnt;

계층 쿼리

재귀 CTE는 자기 참조 테이블에서 빛납니다. 첫 예제는 루트에서 각 노드까지 슬래시 구분 경로를 만들어 브레드크럼에 유용합니다. 두 번째는 노드의 모든 하위(서브트리)를 찾습니다. 순환이 가능하면 UNION(UNION ALL 아님)을 사용해 무한 루프를 막고, 그렇지 않으면 UNION ALL이 더 빠릅니다. 매우 깊은 트리는 조인 컬럼에 적절한 인덱스가 없으면 재귀 CTE가 느릴 수 있으니 성능을 모니터링하세요.

sqlite
-- categories with parent/child self-reference
CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
  parent_id INTEGER REFERENCES categories(id)
);

-- full path from root to each node
WITH RECURSIVE cat_path(id, name, path) AS (
  SELECT id, name, name
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, cp.path || ' > ' || c.name
  FROM categories c
  JOIN cat_path cp ON c.parent_id = cp.id
)
SELECT id, path FROM cat_path ORDER BY path;

-- find all descendants of a node
WITH RECURSIVE descendants(id) AS (
  SELECT id FROM categories WHERE id = 5   -- root of subtree
  UNION ALL
  SELECT c.id FROM categories c
  JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM categories WHERE id IN (SELECT id FROM descendants);

CTE 최적화

SQLite 3.35+는 MATERIALIZED / NOT MATERIALIZED 힌트로 CTE 평가를 제어합니다. MATERIALIZED는 임시 테이블 쓰기를 강제하므로(여러 번 참조하는 비싼 CTE에 좋음) NOT MATERIALIZED는 CTE를 인라인해 옵티마이저가 조건을 밀어 넣게 합니다(저렴한 CTE에 좋음). 플래너는 보통 스스로 잘 선택하므로 EXPLAIN QUERY PLAN이 나쁜 계획을 보일 때만 힌트를 사용하세요. 재귀 CTE는 항상 구체화됩니다.

sqlite
-- MATERIALIZED: force SQLite to materialize (temp table)
WITH cte AS MATERIALIZED (
  SELECT user_id, SUM(total) AS spent FROM orders GROUP BY user_id
)
SELECT u.username, cte.spent
FROM users u JOIN cte ON cte.user_id = u.id;

-- AS NOT MATERIALIZED: inline the CTE (may run multiple times)
WITH cte AS NOT MATERIALIZED (
  SELECT COUNT(*) FROM users WHERE active = 1
)
SELECT * FROM products WHERE id < (SELECT * FROM cte);

-- choose MATERIALIZED when the CTE is expensive and used
-- multiple times; NOT MATERIALIZED when it's cheap or used
-- once and could benefit from predicate pushdown.

-- recursive CTEs are always materialized.
17

백업과 복원

.dump(텍스트 백업)

.dump는 데이터베이스를 일반 SQL INSERT 문으로 내보냅니다. 사람이 읽을 수 있고 diff가 가능하며 SQLite 버전과 아키텍처 간에 이식됩니다. 가장 호환성 좋은 백업 형식입니다. 복원은 SQL을 새 데이터베이스에 재생합니다. --nosys는 sqlite_sequence와 기타 내부 테이블을 건너뜁니다. 큰 데이터베이스에서는 .dump가 느리고 파일이 거대해지므로 .backup(바이너리 복사)이나 Online Backup API를 사용하세요.

sqlite
-- dump the entire database as SQL text (portable)
sqlite3 mydb.sqlite .dump > backup.sql

-- dump a single table
sqlite3 mydb.sqlite ".dump users" > users.sql

-- dump only data (no schema)
sqlite3 mydb.sqlite ".dump" | grep '^INSERT' > data.sql

-- restore from a dump
sqlite3 newdb.sqlite < backup.sql
-- or inside the CLI:
.read backup.sql

-- dump in a specific order to satisfy FK constraints
sqlite3 mydb.sqlite ".dump --nosys" > backup.sql

-- .dump works on attached databases too
ATTACH 'archive.sqlite' AS archive;
.dump archive

.backup(바이너리 복사)

.backup은 SQLite Online Backup API로 안전한 온라인 바이너리 복사를 수행합니다. 쓰기가 활성인 동안에도 동작하며 트랜잭션적으로 일관된 스냅샷을 만듭니다. 실행 중인 데이터베이스를 백업하는 권장 방법입니다. 출력은 유효한 SQLite 파일이라 복원은 파일 복사일 뿐입니다. .dump와 달리 바이너리 형식은 버전/아키텍처 호환이지만 사람이 읽을 수 없습니다.

sqlite
-- binary copy to another file (online, safe with active writers)
sqlite3 mydb.sqlite ".backup 'backup.sqlite'"

-- inside the CLI
.backup backup.sqlite
.backup main backup.sqlite     -- source 'main', target file

-- backup to an attached database
ATTACH 'backup.sqlite' AS bk;
.backup main bk;
DETACH bk;

-- .backup uses the Online Backup API internally: it copies
-- page-by-page and handles concurrent writers safely, so it's
-- the right choice for live production backups.

-- restore = just copy the file back (or use it directly)
cp backup.sqlite mydb.sqlite

Online Backup API

Online Backup API(sqlite3_backup_*)는 최고 수준의 백업 방법입니다. 라이브 데이터베이스를 페이지 단위로 복사하고 동시 쓰기를 올바르게 처리하며 일관된 스냅샷을 만듭니다. Python의 sqlite3.Connection.backup()과 Node의 better-sqlite3 .backup()이 이를 감쌉니다. WAL 모드에서도 동작합니다. 원시 파일 복사는 쓰기가 진행 중이면 손상된 백업을 만들 수 있으므로 중지된 데이터베이스나 파일 수준 스냅샷에서만 사용하세요.

sqlite
-- the sqlite3 CLI's .backup uses this C API; in Python:
import sqlite3
src = sqlite3.connect("mydb.sqlite")
dst = sqlite3.connect("backup.sqlite")
src.backup(dst)
dst.close()
src.close()

-- with progress callback
def progress(remaining, total):
    print(f"{total - remaining}/{total} pages copied")
src.backup(dst, pages=progress)

-- this is the safest backup method: it's online (doesn't block
-- writers), transactionally consistent, and works on WAL-mode
-- databases. Prefer it over raw file copy for production.

-- Node.js (better-sqlite3):
const backup = db.backup('backup.sqlite');
while (!backup.completed) backup.step(-1);
backup.delete();

데이터베이스 연결

ATTACH DATABASE는 최대 10개 데이터베이스 파일을 하나의 연결에 명명된 스키마(main, temp, 추가 연결)로 가져옵니다. 스키마 한정 이름(main.users, archive.orders)으로 질의, 조인, 데이터 복사가 가능합니다. 아카이빙(오래된 행을 아카이브 파일로 이동), 샤딩, 병합 워크플로의 기반입니다. DETACH는 스키마를 닫지만 파일은 그대로 둡니다. WAL 모드에서는 데이터베이스 간 트랜잭션이 원자적입니다.

sqlite
-- work with multiple DB files in one connection
ATTACH DATABASE 'archive.sqlite' AS archive;
ATTACH DATABASE 'lookup.sqlite' AS lookup;

-- query across databases
SELECT u.username, a.note
FROM main.users u
JOIN archive.old_users a ON u.id = a.id;

-- copy a table between databases
CREATE TABLE archive.users_copy AS
SELECT * FROM main.users;

-- move data: insert into one DB, delete from another
INSERT INTO archive.orders SELECT * FROM main.orders WHERE created < '2023-01-01';
DELETE FROM main.orders WHERE created < '2023-01-01';

-- list attached databases
.databases

-- detach (does NOT delete the file)
DETACH DATABASE archive;

-- limit: 10 attached databases (compile-time SQLITE_MAX_ATTACHED).

복구와 손상

integrity_check는 구조적 손상을 감지합니다. .recover(3.29+)는 손상된 파일에서 데이터를 구출하는 최고의 도구입니다. 읽을 수 있는 행을 추출하고 나쁜 페이지를 건너뛰어 새 데이터베이스에 파이프할 SQL을 만듭니다. .recover가 실패하면 .dump로 폴백하고 부분 손실을 감수하세요. 대부분의 손상은 안전하지 않은 파일 작업(라이브 DB 복사, 네트워크 파일 시스템, synchronous=OFF 상태의 전원 손실)에서 비롯됩니다. 복구 시도 전에 항상 백업하세요.

sqlite
-- check integrity
PRAGMA integrity_check;
PRAGMA quick_check;

-- recover from a corrupt database (best effort)
sqlite3 corrupt.sqlite ".recover" > recovered.sql
sqlite3 clean.sqlite < recovered.sql

-- .recover extracts everything readable, skipping corrupt pages

-- if .recover fails, try dumping what you can
sqlite3 corrupt.sqlite ".dump" > partial.sql 2>errors.txt
sqlite3 clean.sqlite < partial.sql

-- point-in-time from a backup file
cp nightly_backup.sqlite mydb.sqlite

-- after a crash, WAL is replayed automatically on next open;
-- if stuck, checkpoint manually:
sqlite3 mydb.sqlite "PRAGMA wal_checkpoint(TRUNCATE);"

-- prevent corruption: never overwrite a live DB file,
-- never share a DB on a network filesystem without WAL off.
18

가져오기와 내보내기

CSV 가져오기

.import는 CSV를 테이블로 읽습니다. 테이블이 없으면 SQLite가 모든 컬럼을 TEXT로 만들어 생성합니다(보통 원하는 것이 아니므로 적절한 타입으로 먼저 테이블을 만드세요). --skip N은 헤더 행을 건너뜁니다. .mode csv는 따옴표 필드의 쉼표/줄바꿈을 처리하도록 파서를 설정합니다. 큰 가져오기는 BEGIN/COMMIT으로 감싸고 PRAGMA synchronous=OFF를 일시적으로 사용하면 10배 이상 빨라집니다. 대량 적재 중 인덱스를 비활성화하고 후에 재생성하세요.

sqlite
-- import a CSV file into a table
.mode csv
.import users.csv users
-- ^ creates 'users' table with text columns named from the header row

-- import into an EXISTING table (column order must match)
.mode csv
.import --skip 1 users.csv users   -- --skip 1 to skip header

-- recommended: create the table first with proper types
CREATE TABLE users (id INTEGER, name TEXT, email TEXT);
.mode csv
.import --skip 1 users.csv users

-- from the shell, one-shot
sqlite3 mydb.sqlite ".mode csv" ".import users.csv users"

-- handle quotes/escapes
.mode csv
.separator ,
.import data.csv mytable

CSV 내보내기

.mode csv와 .headers on은 헤더 행이 있는 표준 CSV를 만듭니다. .output으로 파일에 파이프하세요. 셸 플래그 -header -csv와 출력 리다이렉트를 쓰면 CLI에 들어가지 않고 한 줄로 됩니다. TSV에는 .mode list와 .separator "\t"를 사용하세요. 큰 내보내기는 스트림 기반이라 메모리 효율이 좋습니다. 여러 테이블을 내보내려면 쿼리 사이에 .output을 바꾸세요.

sqlite
-- export a query to CSV
.mode csv
.headers on
.output users.csv
SELECT id, username, email FROM users;
.output stdout

-- from the shell, one-shot
sqlite3 mydb.sqlite -header -csv \
  "SELECT id, username, email FROM users" > users.csv

-- export all rows of a table
.mode csv
.headers on
.output products.csv
SELECT * FROM products;
.output stdout

-- custom separator
.mode list
.separator "|"
.output users.txt
SELECT id, username FROM users;
.output stdout

JSON 내보내기

.mode json은 행마다 JSON 객체 하나를 출력합니다(감싸는 배열 없음). 제대로 된 JSON 배열에는 json_group_array(json_object(...))를 사용하세요. 필드 이름과 중첩을 완전히 제어할 수 있고 상관 서브쿼리로 중첩 배열도 만들 수 있습니다. ORM 없이 SQL에서 직접 JSON API를 만드는 강력한 방법입니다. 큰 결과 집합은 메모리에 거대한 배열을 만드는 대신 .mode json을 줄 단위로 스트리밍하세요.

sqlite
-- export a query as JSON (one JSON object per row)
.mode json
.output users.json
SELECT id, username, email FROM users;
.output stdout

-- build a single JSON array with json_group_array
SELECT json_group_array(json_object(
  'id', id, 'username', username, 'email', email
)) AS users_json
FROM users;
-- [{"id":1,"username":"alice","email":"[email protected]"},...]

-- nested JSON (orders with their items)
SELECT json_object(
  'order_id', o.id,
  'total', o.total,
  'items', (SELECT json_group_array(json_object('name', i.name, 'qty', i.qty))
            FROM order_items i WHERE i.order_id = o.id)
) AS order_json
FROM orders o;

JSON 가져오기

SQLite에는 JSON용 .import가 없습니다. 파일을 TEXT로 로드한 뒤(앱 코드나 INSERT가 있는 .read 사용) json_each/json_extract로 파싱하세요. json_each(array)는 배열을 행으로 펼쳐 집합 기반 삽입을 가능하게 합니다. 큰 JSON 파일은 애플리케이션에서 스트림 파싱하세요(파일 전체를 메모리에 올리지 마세요). 로드 중 json_valid()로 검증하고 지속적 무결성을 위해 JSON 컬럼에 CHECK 제약을 사용하세요.

sqlite
-- load a JSON file into a text column, then parse
CREATE TABLE raw (data TEXT);
-- (no built-in .import for JSON; load via CLI or app)

-- once loaded, parse with json_each (array) or json_extract (object)
SELECT json_extract(data, '$.name') AS name,
       json_extract(data, '$.age')  AS age
FROM raw;

-- expand a JSON array into rows
INSERT INTO users (username, email)
SELECT json_extract(value, '$.username'),
       json_extract(value, '$.email')
FROM raw, json_each(raw.data);

-- import via Python
import sqlite3, json
db = sqlite3.connect("mydb.sqlite")
with open("users.json") as f:
    for row in json.load(f):
        db.execute("INSERT INTO users(username,email) VALUES(?,?)",
                   (row["username"], row["email"]))
db.commit()

Excel과 기타 형식

Excel에는 CSV로 내보내세요(Excel이 직접 엽니다). .mode excel(3.36+)은 UTF-8이 올바르게 보이도록 BOM을 추가합니다. .mode markdown은 문서에 붙여넣을 수 있는 표 마크업을 만듭니다. .mode insert는 INSERT 문을 생성하므로 테스트 DB 시딩이나 스키마 간 데이터 마이그레이션에 좋습니다. .mode quote는 모든 값을 인용해 데이터에 다른 도구를 혼란시키는 쉼표/줄바꿈/따옴표가 있을 때 유용합니다. .mode table/box는 터미널용 ASCII 표를 그립니다.

sqlite
-- Excel: export CSV (Excel opens it) or use .mode excel
.mode csv
.headers on
.output report.csv
SELECT * FROM monthly_report;
.output stdout

-- .mode excel (3.36+): CSV with BOM, friendly to Excel
.mode excel
.output report.csv
SELECT * FROM monthly_report;
.output stdout

-- .mode markdown: GitHub-flavored markdown tables
.mode markdown
SELECT id, username, email FROM users LIMIT 5;

-- .mode insert: generate INSERT statements
.mode insert users
.output seed.sql
SELECT id, username, email FROM users;
.output stdout
-- produces: INSERT INTO "users" VALUES(1,'alice','[email protected]');

-- .mode quote: CSV with all values quoted
.mode quote
.output safe.csv
SELECT * FROM users;
.output stdout
19

애플리케이션 통합

Python(sqlite3)

Python의 sqlite3은 표준 라이브러리에 내장되어 있습니다. SQL 인젝션을 막고 인용을 올바르게 처리하려면 항상 ? 자리 표시자(또는 :name)를 사용하고 f-string이나 %는 절대 쓰지 마세요. 대량 삽입에는 루프 execute보다 executemany가 훨씬 빠릅니다. 컬럼 이름 접근에는 row_factory = sqlite3.Row를 설정하세요. 자동 커밋/롤백에는 컨텍스트 관리자(with conn:)를 사용하세요. 동시성에는 스레드마다 연결 하나를 사용하세요(sqlite3는 기본적으로 스레드 간 공유를 금지).

sqlite
import sqlite3

# connect (creates the file if missing)
conn = sqlite3.connect("mydb.sqlite")
conn.row_factory = sqlite3.Row  # dict-like rows
cur = conn.cursor()

# parameterized query (NEVER use string formatting!)
cur.execute(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    ("alice", "[email protected]"),
)
conn.commit()

# query with parameters
cur.execute("SELECT id, username FROM users WHERE active = ?", (1,))
for row in cur:
    print(row["id"], row["username"])

# named parameters
cur.execute(
    "SELECT * FROM users WHERE age >= :min_age",
    {"min_age": 18},
)

# bulk insert (fast)
cur.executemany(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    [("bob", "[email protected]"), ("carol", "[email protected]")],
)
conn.commit()
conn.close()

Node.js(better-sqlite3)

better-sqlite3은 동기식(콜백/Promise 상용구 없음)이며 가장 빠른 Node.js SQLite 드라이버입니다. 준비된 문을 재사용하므로 한 번 prepare하고 여러 번 실행하세요. .transaction(fn)은 함수를 BEGIN/COMMIT(예외 시 ROLLBACK)으로 감싸며 문장별 자동 커밋보다 훨씬 빠릅니다. SQL 인젝션을 피하려면 ? 자리 표시자를 사용하세요. 동시성을 위해 WAL을 활성화하세요. Promise 기반 API가 필요하면 호출을 감싸거나 sqlite/sqlite3를 사용하세요.

sqlite
const Database = require("better-sqlite3");
const db = new Database("mydb.sqlite");
db.pragma("journal_mode = WAL");

// prepared statement (synchronous, fast)
const insert = db.prepare(
  "INSERT INTO users (username, email) VALUES (?, ?)"
);
const info = insert.run("alice", "[email protected]");
console.log(info.lastInsertRowid);

// query multiple rows
const select = db.prepare("SELECT id, username FROM users WHERE active = ?");
const rows = select.all(1);  // array of objects

// query one row
const one = select.get(1);

// transaction (atomic, fast)
const insertMany = db.transaction((users) => {
  for (const u of users) insert.run(u.username, u.email);
});
insertMany([{username: "bob"}, {username: "carol"}]);

db.close();

연결 URI와 모드

URI 파일 이름(file:...)은 읽기 전용, 공유 캐시, 인메모리 모드를 가능하게 합니다. Python에서는 uri=True를, Node 드라이버에는 file: 문자열을 전달하세요. :memory:는 기본적으로 연결별입니다. file:name?mode=memory&cache=shared는 같은 프로세스의 연결 간에 공유되는 이름 있는 인메모리 DB를 만듭니다(테스트에 유용). busy_timeout을 설정해 락을 잠시 기다리는 쓰기가 즉시 오류를 내지 않게 하세요. 연결마다 항상 PRAGMA foreign_keys = ON을 설정하세요.

sqlite
# open read-only
sqlite3.connect("file:mydb.sqlite?mode=ro", uri=True)

# open in-memory (vanishes on close)
sqlite3.connect(":memory:")
# or via URI:
sqlite3.connect("file::memory:", uri=True)

# shared in-memory database (visible across connections in same process)
sqlite3.connect("file:memdb1?mode=memory&cache=shared", uri=True)

# with a busy timeout (wait instead of erroring on locks)
conn = sqlite3.connect("mydb.sqlite", timeout=30)

# Node.js: same URI form
const db = new Database("file:mydb.sqlite?mode=ro", { readonly: true });

# Node.js in-memory
const mem = new Database(":memory:");

# Python: per-connection pragmas
conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA foreign_keys = ON")

트랜잭션과 동시성

트랜잭션은 문장을 원자적으로 묶습니다(전부 아니면 전무). SQLite는 한 번에 쓰기 하나만 허용하지만(데이터베이스 수준 쓰기 락) WAL 모드는 동시 읽기를 허용합니다. Python의 기본 isolation_level은 DML을 암시적 트랜잭션으로 감싸므로 commit이 필요합니다. 명시적 제어를 원하면 isolation_level = None으로 설정하고 BEGIN/COMMIT을 직접 실행하세요. busy_timeout(PRAGMA busy_timeout 또는 연결 timeout)을 설정해 락을 잠시 기다리는 쓰기가 즉시 SQLITE_BUSY를 던지지 않게 하세요.

sqlite
# Python: explicit transaction
conn = sqlite3.connect("mydb.sqlite")
conn.isolation_level = None  # autocommit off -> manual BEGIN
cur = conn.cursor()
cur.execute("BEGIN")
try:
    cur.execute("UPDATE accounts SET bal = bal - 100 WHERE id = 1")
    cur.execute("UPDATE accounts SET bal = bal + 100 WHERE id = 2")
    cur.execute("COMMIT")
except Exception:
    cur.execute("ROLLBACK")
    raise

// Node.js (better-sqlite3) transaction
const transfer = db.transaction((from, to, amt) => {
  db.prepare("UPDATE accounts SET bal = bal - ? WHERE id = ?").run(amt, from);
  db.prepare("UPDATE accounts SET bal = bal + ? WHERE id = ?").run(amt, to);
});
transfer(1, 2, 100);

-- SQLite allows ONE writer at a time; many readers in WAL mode.
-- Use a busy_timeout to wait for locks instead of failing fast.

준비된 문과 안전성

매개변수화된 쿼리(? 자리 표시자)는 가장 중요한 단일 보안 관행입니다. SQL 인젝션을 막고 타입 변환/이스케이프를 올바르게 처리합니다. 사용자 입력을 SQL 문자열에 절대 보간하지 마세요. 자리 표시자는 값에만 동작합니다. 식별자(테이블/컬럼 이름)는 매개변수화할 수 없으므로 허용 목록으로 검증하세요. LIKE에서는 사용자 입력의 %와 _를 이스케이프해 와일드카드를 주입하지 못하게 하세요. 모든 언어와 드라이버에 적용됩니다.

sqlite
-- SQL injection vulnerability (NEVER do this):
-- app: "SELECT * FROM users WHERE name = '" + user_input + "'"
-- user_input = "'; DROP TABLE users; --"
-- result:    SELECT * FROM users WHERE name = ''; DROP TABLE users; --'

-- SAFE: parameterized query (placeholders are NOT string substitution)
-- Python:
cur.execute("SELECT * FROM users WHERE name = ?", (user_input,))
-- Node.js:
stmt.get(user_input);

-- identifiers (table/column names) CANNOT be parameterized;
-- validate against a whitelist:
allowed_tables = {"users", "orders", "products"}
if table not in allowed_tables:
    raise ValueError("bad table")
cur.execute(f"SELECT * FROM {table} WHERE id = ?", (id_,))

-- LIKE with user input: escape wildcards
cur.execute(
    "SELECT * FROM users WHERE name LIKE ? ESCAPE '\'",
    (user_input.replace("\", "\\").replace("%", "\%").replace("_", "\_") + "%",)
)
20

성능 최적화

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN은 최고의 튜닝 도구입니다. 'USING INDEX'는 좋음, 'SCAN'(전체 테이블 스캔)은 큰 테이블에서 대개 나쁨입니다. 조인에서는 조인 컬럼이 인덱싱되었는지, 플래너가 더 큰 테이블을 스캔하지 않는지 확인하세요. EXPLAIN(QUERY PLAN 없이)은 VDBE 바이트코드를 보여주며 드물게 필요하지만 깊은 디버깅에 유용합니다. 실험적 EXPERT 명령(3.36+)이 인덱스를 제안하거나 .expert CLI 도트 명령을 사용할 수 있습니다.

sqlite
-- see how SQLite executes a query (essential for tuning)
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';

-- good: uses an index
--   SEARCH users USING INDEX idx_users_email (email=?)

-- bad: full table scan
--   SCAN users

-- join plan
EXPLAIN QUERY PLAN
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = 1;

-- automated index recommendation (3.36+, experimental)
SELECT * FROM users WHERE email = 'x';
-- then:
EXPLAIN QUERY PLAN SELECT * FROM sqlite_stat1;

-- lower-level: EXPLAIN (bytecode)
EXPLAIN SELECT * FROM users WHERE id = 1;

VACUUM과 ANALYZE

VACUUM은 파일을 재구축해 삭제로 생긴 공간을 회수하고 페이지를 조각 모음합니다. 큰 삭제 후나 쓰기가 많은 데이터베이스에 주기적으로 실행하세요. VACUUM INTO(3.27+)는 소스를 오래 잠그지 않고 깨끗한 사본을 씁니다. ANALYZE는 쿼리 플래너가 인덱스 선택에 쓰는 sqlite_stat1 통계를 갱신합니다. 대량 적재나 스키마/데이터 변경 후 실행하세요. auto_vacuum=INCREMENTAL은 전체 VACUUM 없이 점진적으로 공간을 회수합니다.

sqlite
-- VACUUM: rebuild the database file, reclaiming free space
VACUUM;
-- after large deletes, this shrinks the file and defrags pages

-- VACUUM INTO: write a defragmented copy to a new file (3.27+)
VACUUM INTO 'clean.sqlite';

-- incremental VACUUM (only with auto_vacuum enabled)
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(100);  -- free up to 100 pages

-- ANALYZE: update planner statistics for better query plans
ANALYZE;                          -- all tables
ANALYZE users;                    -- one table
ANALYZE users(idx_users_email);   -- one index

-- run ANALYZE after bulk loads or major data changes

대량 적재

대량 적재: 단일 트랜잭션으로 감싸고(가장 큰 속도 향상, 행별 자동 커밋은 치명적으로 느림), synchronous와 저널링을 일시적으로 비활성화하며, 인덱스를 삭제 후 재생성하고, PRAGMA cache_size 증가를 고려하세요. .import 도트 명령이 INSERT 문보다 빠릅니다. 정말 거대한 적재에는 미리 만든 데이터베이스의 .backup을 고려하세요. 운영 데이터 적재 후에는 항상 안전 PRAGMA를 복원하세요.

sqlite
-- fastest bulk load into a fresh table
PRAGMA synchronous = OFF;     -- risk: power-loss corruption (use only for one-off loads)
PRAGMA journal_mode = MEMORY;
PRAGMA temp_store = MEMORY;

BEGIN;
CREATE TABLE big (id INTEGER, data TEXT);
.import data.csv big
COMMIT;

-- then turn safety back on
PRAGMA synchronous = FULL;
PRAGMA journal_mode = WAL;

-- faster still: drop indexes, load, recreate
DROP INDEX idx_big_data;
-- ... load ...
CREATE INDEX idx_big_data ON big(data);

-- use a transaction (10-100x faster than autocommit per row)
BEGIN;
INSERT INTO big VALUES (1, 'a');
-- ... thousands of inserts ...
COMMIT;

FTS5(전체 텍스트 검색)

FTS5는 SQLite의 전체 텍스트 검색 엔진입니다. 텍스트를 인덱싱해 빠른 MATCH 쿼리를 가능하게 하며 모든 행을 스캔하는 LIKE '%term%'보다 훨씬 빠릅니다. 불리언(AND/OR/NOT), 접두사(term*), 구문("..."), 관련도 순위를 지원합니다. 외부 콘텐츠 패턴(content='posts')은 데이터 중복을 피합니다. 트리거(기본 테이블의 INSERT/UPDATE/DELETE)로 FTS 인덱스를 동기화하세요. FTS5는 검색-as-you-type, 자동 완성, 문서 검색에 이상적입니다.

sqlite
-- create a full-text search virtual table
CREATE VIRTUAL TABLE posts_fts USING fts5(
  title, body,
  content='posts', content_rowid='id'
);

-- populate it (keep in sync with triggers or rebuild)
INSERT INTO posts_fts(rowid, title, body)
  SELECT id, title, body FROM posts;

-- search with MATCH (much faster than LIKE '%term%')
SELECT p.* FROM posts p
JOIN posts_fts f ON f.rowid = p.id
WHERE posts_fts MATCH 'sqlite python';

-- ranking by relevance
SELECT p.title, rank
FROM posts p
JOIN posts_fts f ON f.rowid = p.id
WHERE posts_fts MATCH 'sqlite OR python'
ORDER BY rank;

-- prefix and phrase queries
SELECT * FROM posts_fts WHERE posts_fts MATCH 'sql*';        -- prefix
SELECT * FROM posts_fts WHERE posts_fts MATCH '"full text"'; -- exact phrase

흔한 함정과 튜닝

대부분의 SQLite 성능 문제는 몇 가지 원인에서 비롯됩니다. WHERE/JOIN/ORDER BY 컬럼의 인덱스 누락, 트랜잭션 누락(행별 자동 커밋은 느린 대량 삽입의 1위 원인), 조용히 꺼진 외래 키, 넓은 테이블의 SELECT *. 모든 연결 설정에서 WAL과 외래 키를 활성화하세요. 큰 DELETE는 배치로 나눠 긴 쓰기 락과 거대한 임시 공간을 피하세요. 큰 데이터 변경 후 ANALYZE를 실행해 플래너가 좋은 계획을 선택하게 하세요.

sqlite
-- 1. Enable foreign keys (OFF by default!)
PRAGMA foreign_keys = ON;

-- 2. Use WAL for concurrency
PRAGMA journal_mode = WAL;

-- 3. Index foreign keys AND join columns
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 4. Avoid SELECT * (more I/O, breaks on schema change)
SELECT id, username FROM users;

-- 5. Use transactions for multi-statement writes
BEGIN;
-- ... several inserts/updates ...
COMMIT;

-- 6. Don't index every column (slows writes)
-- index selectively on WHERE/JOIN/ORDER BY columns.

-- 7. Use LIMIT on exploratory queries
SELECT * FROM big_table LIMIT 10;

-- 8. Batch UPDATEs/DELETEs to avoid long locks
DELETE FROM logs WHERE id IN (
  SELECT id FROM logs WHERE ts < '2023-01-01' LIMIT 10000
);

-- 9. Prefer EXISTS over IN for large subquery results
-- 10. Run ANALYZE after big data changes.

Was this helpful?