실행 가능한 SQLite 문서: 마이그레이션

Q: SQLite 스키마 버전은 어떻게 관리하나요?

SQLite에는 데이터베이스마다 32비트 정수 슬롯이 하나 내장돼 있는데, PRAGMA user_version으로 읽고 쓸 수 있습니다. 앱이 시작될 때 이 값을 읽어서 코드가 알고 있는 최신 마이그레이션 번호와 비교한 뒤, 누락된 마이그레이션을 순서대로 실행하면 끝입니다. 별도의 테이블을 만들 필요는 없지만, 감사 이력을 남기기 위해 마이그레이션 기록용 테이블을 추가로 두는 경우도 많습니다.

Q: SQLite 마이그레이션을 롤백할 수 있나요?

각 마이그레이션을 BEGIN; ... COMMIT;으로 감싸세요. SQLite는 DDL도 트랜잭션으로 처리하기 때문에 중간에 하나라도 실패하면 ROLLBACK으로 스키마 변경과 데이터 변경이 모두 한 번에 되돌려집니다. 다만 이미 커밋된 마이그레이션을 되돌리려면 직접 작성한 down 스크립트가 필요합니다 — SQLite가 자동으로 만들어주지는 않습니다.

Q: SQLite의 ALTER TABLE은 왜 제약이 많나요?

SQLite가 지원하는 건 ALTER TABLE ADD COLUMN, RENAME TABLE, RENAME COLUMN, DROP COLUMN 정도이고, 컬럼 타입이나 제약조건을 바꾸는 식의 자유로운 변경은 안 됩니다. 우회 방법은 공식 문서에서 말하는 12단계 레시피인데, 원하는 구조로 새 테이블을 만들고 INSERT INTO newtable SELECT ... FROM oldtable로 데이터를 옮긴 다음, 기존 테이블을 drop하고 새 테이블의 이름을 바꾸는 흐름입니다.

Q: 마이그레이션 도구를 써야 할까요, 직접 만들어도 될까요?

규모가 작은 앱이라면 번호가 매겨진 .sql 파일들을 PRAGMA user_version 기준으로 돌리는 루프 한 30줄짜리 코드로 충분합니다. 프로젝트가 커지고 팀 단위로 협업해야 한다면 Alembic(Python), golang-migrate(Go), Knex(Node) 같은 도구를 쓰는 편이 낫습니다. 직접 만들면 결국 다시 만들게 될 순서 보장, 락 처리, 팀 워크플로우 같은 부분을 알아서 챙겨주거든요.

스키마는 변한다. 처음부터 변경을 염두에 두자

처음 설계한 스키마가 마지막 버전인 경우는 없습니다. 컬럼이 추가되고, 테이블이 쪼개지고, 인덱스도 다시 짜게 되죠. 정작 중요한 질문은 "스키마가 바뀔 것인가?"가 아니라, 이미 예전 버전 DB를 들고 있는 노트북, 서버, 사용자 기기 모두에 변경 사항이 깔끔하게 반영될 것인가입니다.

바로 이 지점에서 SQLite 마이그레이션이 필요합니다. 마이그레이션은 데이터베이스를 버전 N에서 N+1로 끌어올리는, 작고 순서가 정해진 스크립트들의 모음입니다. 순서대로 실행하기만 하면 어떤 DB든 최신 상태로 따라잡을 수 있습니다. 이 규율을 무시하면 결국 "내 컴퓨터에선 되는데" 식의 버그를 잡느라 오후 시간을 통째로 날리게 됩니다.

SQLite에서 이를 위해 기본으로 제공하는 도구는 딱 하나, PRAGMA user_version입니다. 데이터베이스가 알아서 보관해 주는 32비트 정수인데, SQLite 자체는 이 값에 손대지 않습니다. 이 숫자가 무엇을 의미할지는 전적으로 여러분이 정합니다.

PRAGMA user_version;
PRAGMA user_version = 1;
PRAGMA user_version;

새 데이터베이스는 0에서 시작합니다. 마이그레이션을 적용할 때마다 해당 번호로 값을 갱신해 두고, 앱이 시작할 때 이 값을 읽어 현재 어느 버전에 있는지 파악하면 됩니다.

최소한의 SQLite 마이그레이션 루프

핵심 아이디어는 간단합니다. 각 마이그레이션은 번호가 매겨진 SQL 스크립트이고, 앱은 현재 user_version을 읽은 뒤 그보다 큰 번호의 스크립트를 순서대로 실행합니다. 그리고 스크립트가 하나 끝날 때마다 user_version을 업데이트하죠.

다음은 첫 번째 마이그레이션 — 초기 스키마를 만드는 단계입니다:

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);

PRAGMA user_version = 1;

COMMIT;

두 가지를 짚고 넘어가자. 전체가 BEGIN; ... COMMIT;으로 감싸져 있어서 원자적으로 동작한다. 즉 CREATE TABLE이 실패하면 user_version도 올라가지 않으므로, 문제를 고친 뒤 다시 실행하면 된다. 그리고 PRAGMA user_version = 1을 커밋 직전 마지막에 두었기 때문에, 앞 단계가 모두 성공해야만 버전이 올라간다.

이제 created_at 컬럼을 추가해야 한다고 가정해 보자. 이게 바로 마이그레이션 2번이다:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

BEGIN;

ALTER TABLE users ADD COLUMN created_at TEXT
    NOT NULL DEFAULT (datetime('now'));

PRAGMA user_version = 2;

COMMIT;

SELECT sql FROM sqlite_master WHERE name = 'users';

버전이 0인 데이터베이스는 두 마이그레이션을 모두 실행하고, 버전 1인 데이터베이스는 두 번째만 실행합니다. 버전 2인 데이터베이스는 아무것도 실행하지 않죠. 순서가 곧 계약(contract)입니다.

SQLite ALTER TABLE로 할 수 있는 것과 없는 것

SQLite의 ALTER TABLE은 의도적으로 기능이 제한되어 있습니다. 지원하는 동작은 다음과 같습니다.

ADD COLUMN — 새 컬럼을 추가하며, 기본값도 지정할 수 있습니다.
DROP COLUMN — 컬럼을 제거합니다 (3.35 버전부터).
RENAME COLUMN — 컬럼 이름을 바꿉니다 (3.25 버전부터).
RENAME TO — 테이블 자체의 이름을 바꿉니다.

여기까지가 전부입니다. 컬럼의 타입을 바꾸거나, NOT NULL 제약을 변경하거나, CHECK 제약을 수정하거나, 기존 컬럼에 FOREIGN KEY를 추가하는 일은 할 수 없습니다.

-- 지원되지 않음:
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');

SQLite가 직접 처리하지 못하는 변경이 필요할 때 공식 권장 방식은 "테이블 재생성"입니다. 코드는 좀 길어지지만 확실하게 동작하는 방법이죠.

큰 변경이 필요할 때 테이블 재생성하기

흐름은 이렇습니다. 원하는 모양으로 새 테이블을 만들고, 기존 데이터를 옮겨 담은 뒤, 옛 테이블을 삭제하고, 새 테이블의 이름을 원래 이름으로 바꿉니다. 이 모든 과정은 하나의 트랜잭션 안에서 처리합니다.

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada'), ('b@x.com', 'Boris');

-- 이제 email에 CHECK 제약 조건을 추가하고자 합니다. 테이블을 재구성합니다:
CREATE TABLE users_new (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE CHECK (email LIKE '%@%'),
    name  TEXT NOT NULL
);

INSERT INTO users_new (id, email, name)
    SELECT id, email, name FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

SQLite 공식 문서에서는 이 절차를 흔히 12단계 레시피라고 부르는데, 트리거나 뷰, 외래 키 참조에 대한 추가 주의사항까지 함께 다루고 있습니다. 운영 환경 스키마에 적용하기 전에 한 번쯤 정독해 두면 좋습니다. 일반적인 경우라면 위에서 정리한 4단계 버전만으로도 충분합니다.

한 가지 주의할 점: 다시 만들려는 테이블을 참조하는 외래 키가 있다면, 마이그레이션 전에 PRAGMA foreign_keys = OFF로 꺼 두고 작업이 끝난 뒤 PRAGMA foreign_keys = ON으로 다시 켜 줘야 합니다. 그렇지 않으면 중간에 DROP TABLE이 참조 무결성을 깨뜨릴 수 있습니다.

애플리케이션 코드에서 SQLite 마이그레이션 실행하기

관리해야 할 정보가 많지 않기 때문에 마이그레이션 로직은 직접 짜도 충분합니다. 파이썬 표준 라이브러리만 사용한 예시는 다음과 같습니다:

-- 概念的には、アプリは起動時に次のような処理を行います:
--
--   current = SELECT user_version FROM PRAGMA;
--   for i in range(current + 1, len(MIGRATIONS) + 1):
--       run MIGRATIONS[i] in a transaction
--       PRAGMA user_version = i;
--
-- 各ステップで実行されるSQLは、上記のマイグレーションのようになります。

PRAGMA user_version;

핵심 불변 조건은 다음과 같습니다:

마이그레이션은 1부터 시작해서 빈 번호 없이 연속적으로 매깁니다. 중간 누락도, 순서 변경도 안 됩니다.
각 마이그레이션은 PRAGMA user_version = N 갱신과 함께 하나의 트랜잭션으로 묶습니다.
커밋해서 배포한 마이그레이션은 절대 수정하지 않습니다. 새 변경 사항은 새 마이그레이션에 담습니다.

팀에서 가장 자주 깨뜨리는 게 바로 마지막 규칙입니다. 동료의 DB가 이미 마이그레이션 3번을 적용한 뒤에 그 파일을 수정해 버리면, 그 순간부터 동료의 데이터베이스는 여러분 것과 조용히 어긋난 채로 영영 동기화되지 않습니다.

감사 로그(Audit Trail) 남기기

user_version은 데이터베이스가 _어느 버전에 있는지_는 알려 주지만, 언제 어떤 단계가 무엇을 했는지는 알려 주지 않습니다. 작은 기록용 테이블 하나면 이 부분을 보완할 수 있습니다:

CREATE TABLE schema_migrations (
    version    INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

BEGIN;

CREATE TABLE posts (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    body   TEXT NOT NULL
);

INSERT INTO schema_migrations (version, name)
    VALUES (1, 'create_posts');
PRAGMA user_version = 1;

COMMIT;

SELECT * FROM schema_migrations;

이제 마이그레이션마다 이름과 타임스탬프가 한 줄씩 기록됩니다. "왜 이 DB에는 코드가 모르는 컬럼이 있지?" 같은 상황을 디버깅할 때 정말 유용하죠.

루프가 참고하는 진짜 기준은 여전히 PRAGMA user_version입니다. 위 테이블은 어디까지나 사람이 보기 위한 용도예요.

롤백: 트랜잭션이 해주는 것과 못 해주는 것

SQLite는 DDL도 트랜잭션으로 묶입니다. 마이그레이션 5번이 테이블을 만들고 데이터를 복사하고 user_version을 올리는 도중에 복사 단계에서 실패했다고 해봅시다. ROLLBACK을 걸면 CREATE TABLE까지 포함해 전부 되돌아갑니다. 데이터베이스는 BEGIN 직전 상태 그대로 남죠.

BEGIN;

CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts (id, balance) VALUES (1, 100);

-- 여기서 무언가 잘못되었다고 가정합니다:
ROLLBACK;

-- 테이블이 존재하지 않습니다:
SELECT name FROM sqlite_master WHERE type='table' AND name='accounts';

여기까지가 실패한 마이그레이션을 처리하는 방법입니다. 그런데 이미 커밋이 성공한 마이그레이션을 나중에 되돌리고 싶을 때는 이야기가 다릅니다. 이런 경우엔 변경을 되돌리는 별도의 스크립트, 즉 다운 마이그레이션(down-migration) 을 따로 작성해야 합니다. SQLite는 자동 역실행 같은 건 지원하지 않거든요. 마이그레이션 7번이 컬럼을 추가했다면 다운 버전에서는 그 컬럼을 DROP 합니다. 반대로 7번이 컬럼을 삭제한 거라면, 다운 버전으로는 데이터를 복구할 수 없습니다. 기껏해야 빈 컬럼을 다시 만들어 놓는 정도가 한계죠.

실제로 작은 프로젝트들은 다운 마이그레이션을 아예 만들지 않고, "되돌리기"는 백업으로 해결하는 경우가 많습니다. 백업만 꼬박꼬박 챙긴다면 이것도 충분히 합리적인 선택입니다.

나중에 고생을 덜어주는 습관 몇 가지

하나의 마이그레이션은 하나의 논리적 변경만. 서로 관련 없는 컬럼 세 개를 한꺼번에 추가하는 마이그레이션은 리뷰하기도 어렵고 되돌리기도 까다롭습니다. 차라리 세 개로 쪼개세요.
운영 DB 사본으로 마이그레이션을 테스트하세요. 큰 테이블에서는 스키마 변경이 꽤 느릴 수 있는데, 그걸 운영에서 처음 알게 되면 정말 괴롭습니다.
이미 배포된 마이그레이션은 절대 수정하지 마세요. 새로운 마이그레이션을 추가하면 됩니다.
먼저 백업. CLI에서 .backup 한 줄, 혹은 DB가 닫힌 상태에서 파일을 복사해 두는 것만으로도 어지간한 마이그레이션 전에는 든든한 보험이 됩니다.
PRAGMA foreign_keys를 조심하세요. 테이블을 재생성할 때는 잠시 꺼두고, 끝난 뒤에 다시 켭니다.

규모가 큰 프로젝트라면 전용 도구를 쓰는 편이 낫습니다. SQLAlchemy의 Alembic, golang-migrate, Knex, Flyway 같은 것들요. 순서 관리, 동시 실행 처리, 팀 컨벤션처럼 직접 만들면 끝없이 손이 가는 부분들을 알아서 처리해 줍니다. 동작 원리는 위에서 본 루프와 똑같고, 도구는 그저 보일러플레이트를 덜어줄 뿐입니다.

다음 글: WAL 모드와 동시성

마이그레이션은 보통 애플리케이션이 내려가 있거나 배타적 잠금을 잡고 있는 동안 실행됩니다. 그 외의 시간에는 데이터베이스가 여러 커넥션에서 읽기와 쓰기를 동시에 처리하고 있죠. 그런데 SQLite의 기본 저널 모드가 이런 상황에 항상 최적인 건 아닙니다. 다음 글에서는 WAL 모드가 무엇을 바꾸는지, 그리고 언제 전환하는 게 좋은지 다뤄보겠습니다.

자주 묻는 질문

SQLite 스키마 버전은 어떻게 관리하나요?

SQLite에는 데이터베이스마다 32비트 정수 슬롯이 하나 내장돼 있는데, PRAGMA user_version으로 읽고 쓸 수 있습니다. 앱이 시작될 때 이 값을 읽어서 코드가 알고 있는 최신 마이그레이션 번호와 비교한 뒤, 누락된 마이그레이션을 순서대로 실행하면 끝입니다. 별도의 테이블을 만들 필요는 없지만, 감사 이력을 남기기 위해 마이그레이션 기록용 테이블을 추가로 두는 경우도 많습니다.

SQLite 마이그레이션을 롤백할 수 있나요?

각 마이그레이션을 BEGIN; ... COMMIT;으로 감싸세요. SQLite는 DDL도 트랜잭션으로 처리하기 때문에 중간에 하나라도 실패하면 ROLLBACK으로 스키마 변경과 데이터 변경이 모두 한 번에 되돌려집니다. 다만 이미 커밋된 마이그레이션을 되돌리려면 직접 작성한 down 스크립트가 필요합니다 — SQLite가 자동으로 만들어주지는 않습니다.

SQLite의 ALTER TABLE은 왜 제약이 많나요?

SQLite가 지원하는 건 ALTER TABLE ADD COLUMN, RENAME TABLE, RENAME COLUMN, DROP COLUMN 정도이고, 컬럼 타입이나 제약조건을 바꾸는 식의 자유로운 변경은 안 됩니다. 우회 방법은 공식 문서에서 말하는 12단계 레시피인데, 원하는 구조로 새 테이블을 만들고 INSERT INTO new_table SELECT ... FROM old_table로 데이터를 옮긴 다음, 기존 테이블을 drop하고 새 테이블의 이름을 바꾸는 흐름입니다.

마이그레이션 도구를 써야 할까요, 직접 만들어도 될까요?

규모가 작은 앱이라면 번호가 매겨진 .sql 파일들을 PRAGMA user_version 기준으로 돌리는 루프 한 30줄짜리 코드로 충분합니다. 프로젝트가 커지고 팀 단위로 협업해야 한다면 Alembic(Python), golang-migrate(Go), Knex(Node) 같은 도구를 쓰는 편이 낫습니다. 직접 만들면 결국 다시 만들게 될 순서 보장, 락 처리, 팀 워크플로우 같은 부분을 알아서 챙겨주거든요.

SQLite 마이그레이션: user_version으로 스키마 버전 관리