Skip to content

SQLite

SQLite는 MySQL나 PostgreSQL와 같은 데이터베이스 관리 시스템이지만, 서버가 아니라 응용 프로그램에 넣어 사용하는 비교적 가벼운 데이터베이스이다. 영어권에서는 '에스큐엘라이트(ˌɛs ˌkjuː ˈɛlaɪt)'또는 '시큐엘라이트(siːˈkwɛlaɪt)'라고 읽는다.

일반적인 RDBMS에 비해 대규모 작업에는 적합하지 않지만, 중소 규모라면 속도에 손색이 없다. 또 API는 단순히 라이브러리를 호출하는 것만 있으며, 데이터를 저장하는 데 하나의 파일만을 사용하는 것이 특징이다. 버전 3.3.8에서는 풀텍스트 검색 기능을 가진 FTS1 모듈이 지원된다. 컬럼을 삭제하거나 변경하는 것 등이 제한된다.

구글 안드로이드 운영 체제에 기본 탑재된 데이터베이스이기도 하다.

Categories

Projects

ETC

  • sqlite3 fiddle - 웹에서 테스트 해보는 SQLite
  • XLite - 엑셀 파일을 가상 테이블로 쿼리하는 SQLite 확장

How to compile

MacOSX에서는 autotools를 별도로 설치해야 한다. 

$ brew install automake autoconf libtool m4

Command-line tool

sqlite3명령어 유틸리티를 사용할 경우 아래와 같은 명령을 입력할 수 있다.

  • .help: 도움말 출력.
  • .tables: 테이블 목록 출력.
  • .schema [테이블명]: 테이블의 스키마를 출력.
  • .quit: 프로그램 종료.

Data Type

아래와 같은 자료형을 사용할 수 있다.

  • NULL: 널값.
  • INTEGER: 1,2,3,4,6,8bytes의 정수값. (자동증가 시킬 경우 INTEGER PRIMARY KEY AUTOINCREMENT 로 표기)
  • REAL: 8bytes의 부동소수점값.
  • TEXT: UTF-8, UTF-16BE, UTF-16LE 인코딩 문자열.
  • BLOB: 입력된 그대로 저장, 바이너리 파일 등.

C API Reference

Example

CREATE TABLE IF NOT EXISTS synset(idx INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
synset이름의 테이블이 존재하지 않다면 테이블을 생성하고, idx이름의 Primary key(Integer타입의 자동증가)와 name 문자열 컬럼을 생성한다.
DROP TABLE IF EXISTS synset;
synset이름의 테이블이 존재한다면 테이블을 제거한다.

문자열 연결하기

"\r\n" 문자를 "/" 으로 변환하기는 \r = char(13), \n = char(10) 이므로: 

select replace(replace(name, char(13), ''), char(10), '/') as name
from scrap_grade;

"\r\n" 를 하나의 문자로 만들려면 char(13) || char(10) 으로 문자를 연결할 수 있다. 

select replace(name, char(13) || char(10), '/') as name
from scrap_grade;

SQLite에 1분 내로 10억 Row 추가하기

SQLite는 싱글 커넥션 DB가 아님

SQLite에 대해 사람들이 자주 오해하는 점 중 하나는 “SQLite는 단일 연결만 허용하는 데이터베이스” 라고 생각해서 사람들이 사용하지 않게 되는 것

이는 혼란스럽기도 하고(여기서 "연결"의 의미?), 여러 측면에서 틀린 주장임

  • 읽기 작업 (Read Operations)
    • SQLite는 여러 개의 동시 읽기 작업을 완벽하게 지원함
    • 여러 “연결”에서 동시에 데이터를 읽을 수 있으며, 이 과정에서 충돌이나 문제가 발생하지 않음
  • 쓰기 작업 (Write Operations)
    • SQLite는 쓰기 작업 시 DB 레벨의 쓰기 잠금 (Write Lock) 을 사용함
    • 동시에 여러 개의 쓰기 작업을 허용하지 않음, 따라서 한 번에 한 개의 “연결”만 쓰기 작업을 수행할 수 있음
    • 일반적으로 이것이 문제로 작용하지 않는 이유는 IMMEDIATE TRANSACTION을 시작할 수 있기 때문임
    • 이 경우 SQLite는 쓰기 잠금을 획득하기 위해 큐에서 재시도를 할 수 있음
      • (이 방식은 쓰기 잠금이 해제될 때 자동으로 쓰기 작업이 수행될 수 있게 함)

5.0 데이터베이스 파일에 쓰기

데이터베이스에 쓰려면 먼저 위에서 설명한 대로 프로세스가 SHARED 락을 획득해야 합니다. SHARED 락을 획득한 후에는 RESERVED 락을 획득해야 합니다. RESERVED 락은 프로세스가 미래의 어느 시점에 데이터베이스에 쓰겠다는 신호를 보냅니다. 한 번에 한 프로세스만 RESERVED 락을 보유할 수 있습니다. 그러나 다른 프로세스는 RESERVED 락이 유지되는 동안 데이터베이스를 계속 읽을 수 있습니다.

2025년을 위한 7개의 데이터베이스 중 하나

  • Database#2025년을 위한 7개의 데이터베이스 (GeekNews) 참조
  • SQLite는 "로컬 우선" 데이터베이스로 독립적으로 실행 가능
    • 클라이언트-서버 모델을 벗어나 애플리케이션과 같은 환경에서 실행됨
    • 예시: WhatsApp과 Signal은 기기 내부에 SQLite를 사용하여 채팅 데이터를 저장함
  • SQLite의 발전된 활용 사례
    • 기본 ACID 준수 데이터베이스 이상의 창의적인 사용 가능
    • 새로운 도구와 확장 기능:
      • Litestream: SQLite의 스트리밍 백업 제공
      • LiteFS: 분산 액세스를 지원하여 더욱 유연한 토폴로지 구현
      • CR-SQLite: CRDT(Conflict-free Replicated Data Types)를 사용해 변경 세트를 병합할 때 충돌 해결 필요성을 제거
  • SQLite의 인기 재조명
    • Ruby on Rails 8.0 덕분에 다시 주목받고 있음
    • 37signals: SQLite를 기반으로 Rails 모듈(Solid Queue 등)을 개발
      • Rails의 다중 SQLite 데이터베이스 관리 지원 (database.yml)
    • Bluesky: 개인 데이터 서버(Personal Data Servers)로 사용자마다 개별 SQLite 데이터베이스 사용
  • SQLite 활용 학습 권장
    • SQLite를 이용한 로컬 중심 아키텍처 실험
    • 기존의 PostgreSQL 기반 클라이언트-서버 모델을 SQLite로 대체할 수 있는지 시도

Bloom filter로 10배 빨라진 SQLite

  • Bloom 필터로 10배 빨라진 SQLite | GeekNews
  • How bloom filters made SQLite 10x faster - blag
  • SQLite는 디스크에 B-트리 구조로 저장되는 행 기반 저장소로, VDBE라는 가상 머신을 사용하여 쿼리를 실행함. 플랫폼에 구애받지 않고 단일 스레드로 거의 모든 환경에서 실행 가능함
  • 일반적인 용도의 데이터베이스이지만 OLTP 작업에 뛰어남. 2015년 버팔로 대학 연구진은 대부분의 쿼리가 간단한 키-값 조회와 복잡한 OLAP 쿼리임을 발견함
  • 위스콘신-매디슨 대학 연구진은 분석 쿼리를 더 빠르게 만들기 위해 노력함. DuckDB와 Star Schema Benchmark(SSB)를 사용하여 성능을 비교함

원인

  • SQLite가 느린 이유를 파악하기 위해 VDBE_PROFILE 옵션을 사용하여 VDBE의 각 명령어가 소모하는 CPU 사이클을 측정함.
  • SeekRowID와 Column이라는 두 개의 opcode가 주요 원인으로 밝혀짐.

데이터베이스 조인

  • 데이터베이스가 조인을 구현하는 방법에는 중첩 루프 조인, 해시 조인, 정렬-병합 조인이 있음.
  • SQLite는 가장 간단한 "중첩 루프 조인"을 사용함. 이는 B-트리 탐색과 유사하여 비용이 많이 듦.

조인 최적화의 중요성

  • 조인 연산에서 테이블의 순서가 중요함. 순서를 바꾸면 연산 횟수를 크게 줄일 수 있음. 이는 NP-난해 문제임.
  • 중첩 루프 조인보다 더 나은 두 가지 조인 알고리듬이 있지만, 해시 조인은 메모리를 많이 소모하며 SQLite는 메모리 제약 환경에서 주로 실행됨.
  • 연구진은 Bloom 필터를 사용하여 공간 효율성을 높이고 CPU 캐시 라인에 맞도록 함. Filter와 FilterAdd라는 두 개의 opcode를 추가함.

결과

  • 최적화 후 CPU 사이클 분석에서 큰 블루 바가 거의 사라짐.
  • SQLite는 7배에서 10배 더 빨라짐. 이 연구 결과는 SQLite v3.38.0에 적용됨.
  • Bloom 필터는 최소한의 메모리 오버헤드로 SQLite의 간단한 구현과 잘 맞고 기존 쿼리 엔진 내에서 작동함

Troubleshooting

Integration bash script

sqlite3를 BASH 스크립트에 통합할 경우 대화형 프로그램이 실행되어 스크립트가 중간에 멈추는 현상이 발생된다. 이 현상을 방지하기 위해, 아래와 같이 적용하면 된다. 

$ cat > test.sql << EOF
CREATE TABLE log_entry ();
.separator "\t"
.import logfile.log log_entry
.quit
EOF
$ sqlite3 db.sqlite < test.sql

Libtool library used but LIBTOOL is undefined

Libtool#Libtool library used but LIBTOOL is undefined 항목 참조.

Local Download

C source code as an amalgamation, version 3.11.1.
Sqlite-amalgamation-3110100.zip (sha1: 868a00d2adcc8b3803c46d461d7d8d23b2bfccbc)
C source code as an amalgamation. Also includes a "configure" script and TEA makefiles for the TCL Interface.
Sqlite-autoconf-3110100.tar.gz (sha1: c4b4dcd735a4daf5a2e2bb90f374484c8d4dad29)

See also

Favorite site