SQLite에서는 STRICT 테이블을 우선 사용하라

(evanhahn.com)

테이블 정의 끝에 STRICT 를 추가하면 정수 열에 임의의 텍스트가 들어가는 타입 오류를 조기에 차단해 데이터 무결성을 높일 수 있음
삽입·갱신 시 타입을 검사하면서도 '123'처럼 손실 없이 변환 가능한 값은 허용하며, 열 타입은 INT, INTEGER, REAL, TEXT, BLOB, ANY 로 제한됨
여러 타입을 담아야 하는 열에는 ANY 를 지정할 수 있어 엄격한 검증과 유연한 저장을 한 테이블 안에서 함께 적용 가능함
기존 테이블을 곧바로 STRICT로 바꿀 수는 없어 새 테이블 생성과 데이터 복사가 필요하며, 잘못된 기존 데이터는 정리하거나 형 변환해야 함
STRICT 테이블은 SQLite 3.37.0 이상에서만 지원되며 이론적인 검사 비용은 있지만, 비공식 실험에서는 뚜렷한 성능·파일 크기 차이가 확인되지 않았음

STRICT 테이블 생성

SQLite의 STRICT 테이블은 다른 SQL 엔진과 비슷하게 더 엄격한 타입 검사를 적용함
CREATE TABLE 정의 마지막에 STRICT 를 붙이면 됨

CREATE TABLE people (name TEXT) STRICT;

삽입과 갱신의 타입 검사

일반 SQLite 테이블은 INTEGER 열에도 'garbage' 같은 텍스트를 저장할 수 있지만, STRICT 테이블은 타입 불일치를 오류로 처리함

CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- 정상 처리됨

CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage');
-- 오류: cannot store TEXT value in INTEGER column

같은 검증이 UPDATE 에도 적용돼 저장 후 값을 변경할 때도 잘못된 타입을 막음
단, 값이 손실 없이 변환될 수 있다면 STRICT 테이블에서도 허용됨
- 문자열 '123'은 정수 123으로 완전히 변환할 수 있어 아래 두 삽입이 동일하게 처리됨

INSERT INTO people_strict (age) VALUES ('123');
INSERT INTO people_strict (age) VALUES (123);

테이블 정의 단계의 타입 제한

일반 테이블에서는 SQLite가 지원하지 않는 타입 이름도 열 선언에 사용할 수 있음
- GARBAGE, DATETIME, JSON, UUID, BLOBB도 모두 받아들여짐
- 이런 선언은 오타이거나 SQLite가 지원하는 데이터 타입에 대한 오해에서 비롯될 수 있음
STRICT 테이블은 지원하지 않는 타입 이름을 사용하면 생성 단계에서 오류가 발생함

CREATE TABLE tbl (name GARBAGE) STRICT;
CREATE TABLE tbl (name DATETIME) STRICT;
CREATE TABLE tbl (name JSON) STRICT;
CREATE TABLE tbl (name UUID) STRICT;
CREATE TABLE tbl (name BLOBB) STRICT;

허용되는 열 타입은 INT, INTEGER, REAL, TEXT, BLOB, ANY 뿐임
모든 열에 타입을 지정해야 하므로 CREATE TABLE tbl (name)처럼 타입을 생략할 수 없음

`ANY`로 유연성 유지

타입이 일정하지 않은 데이터를 저장해야 한다면 ANY 열을 사용할 수 있음
STRICT 테이블 안에서도 ANY 열에는 정수, 텍스트, 실수, BLOB 등 모든 타입을 저장 가능함

CREATE TABLE tbl (value ANY) STRICT;

INSERT INTO tbl (value) VALUES (123);
INSERT INTO tbl (value) VALUES ('text');
INSERT INTO tbl (value) VALUES (12.34);
INSERT INTO tbl (value) VALUES (X'8647');

기존 테이블 마이그레이션

기존 비엄격 테이블을 ALTER로 곧바로 STRICT 테이블로 바꾸는 방법은 없으므로 처음부터 엄격하게 생성하는 편이 간단함
기존 테이블을 전환하려면 새 STRICT 테이블 생성 → 데이터 복사 → 기존 테이블 교체 절차를 거쳐야 함

CREATE TABLE new_people (name TEXT) STRICT;
INSERT INTO new_people SELECT * FROM people;
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;

기존 데이터의 정수 열에 텍스트가 들어 있는 등 타입이 잘못됐다면 복사 과정에서 오류가 날 수 있음
- 마이그레이션 전에 데이터를 정리하거나 CAST로 형 변환해야 할 수 있음
새 테이블에만 STRICT를 적용하는 정책도 가능하지만, 테이블마다 검증 강도가 달라져 모든 테이블이 느슨한 경우보다 동작을 예측하기 어려울 수 있음

유연한 타입이 적합한 경우

SQLite 개발진은 유연한 타입의 장점을 별도로 정리하며 기본 동작이 유용한 사례를 제시함
비엄격 테이블이 합리적일 수 있는 용도는 다음과 같음
- 여러 타입을 그대로 담는 순수 키-값 저장소
- 타입이 서로 다른 기타 속성을 보관하는 공간
- 지저분한 CSV를 직접 가져오면서 유효하지 않은 값까지 잃지 않고 보존해야 하는 경우
그러나 예상하지 못한 타입은 미묘한 버그를 일으킬 수 있으므로, 일반적인 테이블에서는 조용히 허용하기보다 즉시 오류를 내는 편이 유리함
SQLite 소스에는 비엄격 테이블을 legacy라고 부르는 주석도 있지만, 공식 문서보다 신뢰할 근거로 삼기는 어려움

버전 호환성

STRICT 테이블은 2021년 11월 출시된 SQLite 3.37.0 에서 도입됨
이전 SQLite 버전에서는 STRICT 테이블을 사용할 수 없음
구버전은 STRICT 테이블이 포함된 데이터베이스도 읽지 못함
- 최신 SQLite에서 STRICT 테이블을 생성한 뒤 SQLite 3.36.0으로 같은 데이터베이스를 열면 오류가 발생함

성능과 저장 공간

STRICT 테이블은 삽입·갱신 시 데이터 타입을 추가로 검사하므로 이론적으로는 더 느릴 수 있음
100개 열을 가진 테이블에 수백만 행을 삽입한 비공식 실험에서는 여러 컴퓨터 모두 뚜렷한 성능 차이가 나타나지 않았음
디스크의 데이터베이스 파일 크기도 같았지만, 철저한 벤치마크가 아니어서 발견하지 못한 차이가 있을 수 있음
열 선호도(column affinity)에 맞지 않는 값이 실수로 저장되는 일을 막아 오히려 성능이 나아질 가능성도 있으나, 별도로 검증되지는 않았음

적용 시 판단할 점

STRICT 테이블이 모든 데이터 문제를 해결하지는 않지만, 타입 관련 실수를 줄이고 데이터 무결성을 강화함
대부분은 테이블 정의에 STRICT만 추가하면 돼 적용이 간단함
일반적인 테이블에서는 엄격한 타입 검증으로 얻는 이점이 마이그레이션 부담, 구버전 비호환성, 제한된 유연성보다 큼

GN⁺ 2시간전 [-]

Hacker News 의견들

SQLite에는 테이블을 ALTER해 엄격 모드로 바꾸는 기능이 없어 비엄격 테이블의 데이터를 엄격 테이블로 복사해야 한다는 점에서 착안해, sqlite-utils 4.1에 양방향 변환 기능을 추가함
CLI에서는 uvx sqlite-utils transform data.db mytable --strict, Python에서는 db.table("mytable").transform(strict=True)로 사용할 수 있음
릴리스 노트는 https://sqlite-utils.datasette.io/en/stable/changelog.html#v..., Python API 문서는 https://sqlite-utils.datasette.io/en/stable/python-api.html#..., CLI 문서는 https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...에서 볼 수 있음
https://sqlite.org/flextypegood.html은 유연한 타입 지정이 기본값인 이유와 앞으로도 바뀌지 않을 가능성을 설명하지만, 실제 경험과 맞지 않음
고객 이름을 정수형 Customer.creditScore에 넣어도 쉽게 발견하고 수정할 수 있다는 설명과 달리, 손상된 행은 복구하기 어렵고 데이터가 완전히 사라졌을 수도 있음
엄격한 타입 검사가 찾기 쉬운 오류만 막아 오히려 버그 탐지와 수정을 어렵게 한다는 논리에도 전혀 동의하기 어려움
- 신뢰도 높은 데이터베이스 구성이 주는 안전성과 신뢰가 오히려 데이터 버그 수정을 어렵게 한다는 관점은 이해하기 어려움
  Postgres에는 가능한 한 많은 검사와 안전장치를 추가하는 편이며, 그러면 애초에 발생하지 말아야 할 오류를 나중에 찾아다닐 필요가 없어짐
- MongoDB에도 무엇이든 저장할 수 있다는 비슷한 논리가 있었지만, 대부분은 실제 사용 후 그것이 대개 나쁜 특성임을 깨달았음
  이는 엄밀한 공학 원칙이라기보다 SQLite가 만들어진 시대와 저자의 강한 소신에서 비롯된 결과처럼 보이며, 비판을 많이 받은 뒤 어떤 근거를 동원해서라도 입장을 고수하는 인상임
  Postgres의 JSON이나 HSTORE 같은 타입으로 원하는 유연성을 제공할 수 있지만, 무제한 타입 지정을 기본으로 강제하기보다는 필요할 때 선택하게 하는 편이 거의 언제나 낫다
STRICT가 기본값이면 좋겠음
이 점을 제외하면 SQLite 개발자는 훌륭한 도구를 만든 대단한 사람임
- 외래 키조차 기본으로 활성화되지 않아 PRAGMA foreign_keys = ON;을 사용해야 함
  더 큰 문제는 엄격 테이블에는 동등한 pragma가 없어 모든 CREATE TABLE에 비표준 STRICT를 붙여야 한다는 것임
  전역 STRICT pragma도 검토됐지만 구현되지는 않았으며, 관련 자료는 https://sqlite.org/foreignkeys.html과 https://sqlite.org/forum/forumpost/1b9d073a37ca5998에서 볼 수 있음
- SQLite는 하위 호환성을 중시해 기본값을 거의 바꾸지 않음
  SQLite 3.53용 소프트웨어가 3.54로 업그레이드된 뒤 CREATE TABLE이 갑자기 엄격 테이블을 만들면서 오류를 내고 전체가 망가지는 일을 피하려는 것임
- 외래 키 제약 조건도 호환성을 이유로 기본 비활성화되는 등 비슷한 문제가 있음
  한때는 SQLite가 외래 키 문법만 지원하고 실제 기능은 구현하지 않았던 것인지 궁금해짐
- 단순 문자열이 아닌 제대로 된 날짜·타임스탬프 타입도 필요함
- 데이터베이스가 실수로 잘못된 타입을 넣도록 허용할 이유가 없으며, SQLite의 타입 안전성 철학은 아쉬움
  수천 대의 장치에 배포된 코드가 불리언 열에 문자열 '1'과 '0'을 저장해 이를 정리해야 했는데 전혀 즐겁지 않았음
  타임스탬프 타입도 없어 텍스트 열에 저장해야 하고, 표준 날짜·시간 함수조차 ISO 형식인 yyyy-mm-ddTHH:MM:SSZ 대신 UTC라고 암묵적으로 가정해야 하는 yyyy-mm-dd HH:MM:SS를 생성함
  SQLite는 정말 훌륭한 프로젝트지만 일부 설계 결정은 당혹스럽다
기업용 SQL 환경에서 온 입장에서는 필드 타입이 기본적으로 강제되지 않는다는 이유로 SQLite를 진지하게 보지 않았고, 스마트폰 앱 메타데이터의 기반이 됐을 때도 놀랐음
이는 낮은 지연 시간과 단순함 때문에 UDP를 택한 뒤 TCP의 신뢰성 기능 대부분을 애플리케이션에서 직접 다시 만드는 오래된 네트워크 이야기를 떠올리게 함
- 직접 구현하면 TCP와 다른 방식으로 만들 수 있고 때로는 큰 효과를 얻을 수 있으며, QUIC과 HTTP/3가 그 예임
  하지만 비엄격 SQLite 위에 타입 검사를 직접 구현한다고 비슷한 강점이 생기지는 않아 보임
- 외래 키는 2009년 이전에는 아예 지원되지 않았고 지금도 기본으로 활성화되지 않음: https://sqlite.org/foreignkeys.html
- 이런 방식을 실제 사업으로 성공시킬 때도 있음: https://aeron.io/
- 타입 검사가 완전히 지원되지 않는다면 SQLite를 진지하게 보지 않는 태도를 이해할 수 있지만, 대부분의 데이터베이스도 완벽한 기본 설정을 제공하지는 않음
  PostgreSQL도 기본값이 저사양 시스템을 대상으로 하므로 최적 성능을 내려면 설정이 필요함
  또한 TCP는 스트림 기반이라 많은 애플리케이션에서 HOL 블로킹 문제가 있으며, UDP 위에 신뢰성 계층을 직접 만들었다면 많은 작업을 거친 뒤에도 TCP보다 나은 결과를 얻을 수 있음
- Jeep이나 Bronco에 관심이 있다면 일반 자동차 평론가를 찾지 않을 것임
  그들은 시끄럽고 조종성이 나쁘다고 평가하지만, 애초에 다른 용도를 겨냥한 제품에 자신의 사용 사례만 중요하다는 듯 접근하기 때문임
엄격 테이블은 Date 같은 일부 타입을 쓸 수 없다는 단점이 있지만 그래도 기본값이어야 함
여러 애플리케이션이 데이터베이스를 공유한다면 선언된 타입을 신뢰할 수 있어야 하며, 하나가 숫자 열에 문자열을 저장하면 나머지가 모두 망가짐
반면 SQLite의 주된 용도인 임베디드 데이터베이스는 보통 하나의 애플리케이션만 사용하므로, 새 데이터베이스를 만들고 복사하는 대신 스키마를 발전시킬 수 있다는 장점도 있음
이때는 애플리케이션 코드가 혼합 타입을 포함해 각 열에서 무엇을 기대해야 하는지 알고 있음
- SQLite에는 INTEGER, TEXT, BLOB, REAL, NUMERIC의 5가지 데이터 타입만 있음: https://sqlite.org/datatype3.html
- SQLite에는 날짜 데이터 타입이 없고, 임의의 SELECT 쿼리에 EXPLAIN을 호출해 형식상 지정한 타입 이름을 얻는 방법도 없음
  따라서 DATE나 DATETIME 같은 이름을 써도 이를 추론할 수조차 없음
- Date는 실제 타입이 아니며 단지 숫자 친화성을 가진 열이 만들어질 뿐임
엄격 모드는 열 타입 표기를 제한해 더 의미 있는 이름을 쓰지 못하게 하고, 데이터베이스 타입과 애플리케이션 타입을 매핑할 때 코드가 그 이름을 활용하지 못하게 하므로 오히려 애플리케이션 계층의 더 엄격한 타입 체계를 방해함
자세한 내용은 https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type...에 있으며, Rust의 sqlx 같은 크레이트로 데이터베이스를 다룬다면 엄격 모드를 피하는 편이 낫다고 봄
데이터베이스와 RPC에는 엄격한 타입을 원하지만 SQLite는 용도가 다소 달라 직접 써 보면 https://sqlite.org/flextypegood.html을 더 이해하게 될지도 모르겠음
예를 들어 SQLite용이 아닌 임의의 스크립트도 우연히 작동하게 한다는 목표는 다른 DBMS에서는 일반적으로 고려하지 않는 특성임
- 해당 문서의 장단점 항목에는 핵심 논거가 빠져 있음
  유연한 타입 지정의 핵심 장점은 스키마를 쉽게 발전시킬 수 있다는 것임
  임베디드 데이터베이스의 요구 사항이 바뀔 때마다 새 데이터베이스를 만들고 데이터를 이전하는 대신 제자리에서 스키마를 바꿀 수 있고, 한 애플리케이션만 읽고 쓴다면 정수 열에 문자열이 추가돼도 놀랄 일이 없음
  반대로 여러 애플리케이션이 각자 일정에 맞춰 업데이트되며 같은 데이터베이스를 공유한다면 스키마는 계약이므로 잘못된 타입 하나가 다른 애플리케이션을 망가뜨릴 수 있음
  한 애플리케이션이 만든 테이블을 다른 애플리케이션이 사용한다면 합의한 데이터 타입을 엄격히 지켜야 함
- 대부분은 코드가 타입 구조를 준수하는지 컴파일 시점에 확인하며, 함께 개발하는 SQLite 스키마가 정적 분석에 필요한 타입 정보를 제공함
  런타임에 다시 검사해도 실질적인 이득은 없고, 정적 분석에서 문자열을 넣는다고 확인된 코드가 갑자기 변이해 정수를 넣지는 않음
  SQLite는 여러 애플리케이션이 데이터를 공유하고 제3자를 신뢰해야 하는 Postgres와 다름
  Postgres 같은 환경에서는 런타임 검증이 필수지만, SQLite는 기본적으로 애플리케이션 하나와 데이터베이스 하나를 위해 설계됐으므로 컴파일 시점에 평가할 수 있는 자기 코드만 신뢰하면 됨
  여러 애플리케이션이 파일 하나를 공유하는 비전형적 상황에서는 엄격 테이블을 활성화할 수 있음
동적 데이터 타입이 단순 키·값 저장소 같은 용도에는 맞을 수 있지만, INTEGER가 오류 없이 'hello world'를 받는 것과 NONSTRICT 같은 키워드나 ANY 타입을 명시해야만 그런 값을 넣을 수 있는 것 중 어느 쪽이 최소 놀람 원칙에 맞는지 물어야 함
대다수 SQLite 사용자는 전자가 가능할 것이라고 예상하지 못할 듯함
- SQLite는 타입 없는 데이터베이스를 지향하면서도 구문은 표준 SQL처럼 보이게 하려 했기 때문일 수 있음
  다른 DBMS용 코드가 SQLite에서도 우연히 작동하기를 바란다는 설명과도 일치함
  그래도 사용자가 타입을 생략한 것도 아닌데 INTEGER를 명시하고 텍스트를 넣을 수 있다는 것은 매우 놀랍다
08123… 같은 값으로 시작하는 UUID가 8진수로 파싱되면서 일부가 숫자로 잘못 변환된 적이 있음
혼란스럽고 성가셨지만 STRICT를 적용하고 테이블 전체를 다시 만들어 해결함
- 그것은 SQLite가 아니라 언어 드라이버가 과하게 도와준 결과임
  SQLite에는 8진수 타입이 없음
CREATE TABLE ... STRICT WITHOUT ROWID를 기본으로 사용하며, 굳이 다르게 만들 이유를 모르겠음

답변달기

SQLite에서는 STRICT 테이블을 우선 사용하라

STRICT 테이블 생성

삽입과 갱신의 타입 검사

테이블 정의 단계의 타입 제한

ANY로 유연성 유지

기존 테이블 마이그레이션

유연한 타입이 적합한 경우

버전 호환성

성능과 저장 공간

적용 시 판단할 점

함께 보면 좋은 글 β

댓글과 토론

Hacker News 의견들

`ANY`로 유연성 유지