SQLite 데이터 내보내기: CSV, JSON, SQL 덤프 완벽 정리

내보내기는 SQL 문이 아니라 셸의 일이다

SQLite에는 Postgres나 MySQL처럼 COPY ... TO나 SELECT INTO OUTFILE 같은 구문이 없습니다. 대신 데이터를 내보내는 작업은 sqlite3 명령줄 셸에서 도트 명령어(dot-command)로 처리합니다. 핵심은 네 가지입니다. .mode, .headers, .output, .dump. 이 네 개만 익혀두면 어떤 DB에서든 CSV, JSON, 일반 텍스트는 물론 SQL 덤프 파일까지 뽑아낼 수 있습니다.

기본 개념은 이렇습니다. 셸한테 결과를 어떤 형식(.mode)으로 보여줄지, 컬럼명(헤더)을 포함할지(.headers), 그리고 어디로 보낼지(.output)를 알려준 다음 쿼리를 실행하면, 그 결과가 지정한 파일로 떨어집니다.

먼저 가지고 놀 수 있는 작은 데이터베이스를 하나 만들어 봅시다.

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

INSERT INTO users (name, email, signup_date) VALUES
    ('Ada Lovelace', 'ada@example.com', '2025-01-15'),
    ('Boris Johnson', 'boris@example.com', '2025-02-03'),
    ('Carmen Diaz', 'carmen@example.com', '2025-03-22');

SELECT * FROM users;

세 행, 네 열짜리 테이블입니다. 이걸 여러 가지 포맷으로 내보내 보겠습니다.

CSV로 내보내기: .mode csv와 헤더 포함

CSV는 가장 널리 쓰이는 sqlite 데이터 내보내기 포맷입니다. 스프레드시트, 데이터 파이프라인은 물론 거의 모든 도구가 CSV를 읽을 수 있죠. sqlite3 셸에서 바로 다음과 같이 실행하면 됩니다:

sqlite> .mode csv
sqlite> .headers on
sqlite> .output users.csv
sqlite> SELECT * FROM users;
sqlite> .output stdout

방금 일어난 일을 정리해 보겠습니다:

• .mode csv는 각 행을 쉼표로 구분된 값(CSV) 형식으로 출력합니다. 값 안에 쉼표, 따옴표, 줄바꿈이 들어 있으면 알아서 따옴표로 감싸줍니다.
• .headers on은 첫 줄에 컬럼명을 헤더로 추가합니다. 이 옵션을 켜지 않으면 헤더 없이 데이터만 저장되는데, 보통은 원하는 결과가 아닙니다.
• .output users.csv는 쿼리 결과를 파일로 리다이렉트합니다. 이 시점부터는 출력이 화면 대신 지정한 파일로 들어갑니다.
• 그 다음 실행한 SELECT는 화면에 아무것도 띄우지 않고 조용히 파일에 결과를 씁니다.
• .output stdout으로 다시 출력을 터미널로 되돌려야 이후 쿼리 결과를 화면에서 확인할 수 있습니다.

생성된 파일은 다음과 같습니다:

id,name,email,signup_date
1,"エイダ・ラブレス",ada@example.com,2025-01-15
2,"ボリス・ジョンソン",boris@example.com,2025-02-03
3,"カルメン・ディアス",carmen@example.com,2025-03-22

쿼리 결과는 테이블 전체뿐 아니라 어떤 형태든 자유롭게 내보낼 수 있습니다. 필터링하든, 조인하든, 집계하든 원하는 대로 처리한 뒤 그대로 파일로 리다이렉트하면 됩니다:

sqlite> .output recent_users.csv
sqlite> SELECT name, email FROM users WHERE signup_date >= '2025-02-01';
sqlite> .output stdout

셸에서 한 줄로 끝내기

굳이 대화형 셸에 들어갈 필요도 없습니다. 운영체제 셸에서 도트 명령과 SQL을 그대로 sqlite3로 파이프하면 됩니다:

sqlite3 mydb.sqlite <<EOF
.headers on
.mode csv
.output users.csv
SELECT * FROM users;
EOF

스크립트나 크론 작업에는 이 형태가 잘 맞습니다. 손으로 입력할 일 없이 반복 실행이 가능하고, .output 설정은 해당 세션 안에서만 유효하기 때문에 다른 곳으로 상태가 새어 나가지도 않습니다.

JSON으로 내보내기: .mode json

웹 애플리케이션이나 JSON을 받아 처리하는 도구로 데이터를 넘겨야 한다면, .mode json을 쓰면 됩니다. 행마다 객체 하나씩, 전체를 배열로 묶어서 출력해 줍니다:

sqlite> .mode json
sqlite> .output users.json
sqlite> SELECT * FROM users;
sqlite> .output stdout

파일 내용은 아래와 같습니다.

[{"id":1,"name":"Ada Lovelace","email":"ada@example.com","signup_date":"2025-01-15"},
{"id":2,"name":"Boris Johnson","email":"boris@example.com","signup_date":"2025-02-03"},
{"id":3,"name":"Carmen Diaz","email":"carmen@example.com","signup_date":"2025-03-22"}]

JSON에서는 키 자체가 헤더 역할을 하기 때문에 .headers 옵션은 의미가 없습니다. 중첩 객체를 만들거나 필드 이름을 바꾸는 등 원하는 형태로 출력하고 싶다면, 쿼리 안에서 json_object()로 직접 구성하면 됩니다:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
    ('Ada Lovelace',  'ada@example.com'),
    ('Boris Johnson', 'boris@example.com'),
    ('Carmen Diaz',   'carmen@example.com');

SELECT json_object(
    'id', id,
    'name', name,
    'contact', json_object('email', email)
) AS user_json
FROM users;

이렇게 하면 행마다 JSON 문자열을 원하는 구조로 자유롭게 만들 수 있습니다. 여기에 json_group_array()를 함께 쓰면 전체 결과를 하나의 JSON 문서로 묶을 수 있죠.

SQL 전체 덤프: .dump로 sqlite 백업 sql 파일 만들기

.dump는 CSV나 JSON 내보내기와는 결이 다릅니다. 스키마 와 모든 데이터를 CREATE TABLE과 INSERT 구문 형태로 담은 .sql 파일을 생성해 주거든요. 빈 상태에서도 데이터베이스를 그대로 복원할 수 있을 만큼의 정보가 들어 있습니다:

sqlite3 mydb.sqlite .dump > backup.sql

결과로 나오는 스니펫은 다음과 같습니다:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    signup_date TEXT NOT NULL
);
INSERT INTO users VALUES(1,'Ada Lovelace','ada@example.com','2025-01-15');
INSERT INTO users VALUES(2,'Boris Johnson','boris@example.com','2025-02-03');
INSERT INTO users VALUES(3,'Carmen Diaz','carmen@example.com','2025-03-22');
COMMIT;

복원은 정반대 과정입니다. 저장해 둔 파일을 새 데이터베이스로 그대로 흘려 넣으면 됩니다.

sqlite3 restored.sqlite < backup.sql

.dump은 백업, 테스트 데이터의 버전 관리 스냅샷, 그리고 머신 간 마이그레이션에 딱 맞는 도구입니다. 인덱스, 트리거, 뷰까지 — 스키마에 들어 있는 모든 것을 그대로 보존해 주거든요.

특정 테이블만 덤프하기

.dump 뒤에 테이블 이름(또는 패턴)을 붙이면 출력 범위를 제한할 수 있습니다:

sqlite3 mydb.sqlite ".dump users" > users_only.sql

이렇게 하면 users 테이블의 스키마와 행만 덤프됩니다. 다른 테이블까지 끌어오지 않고 특정 테이블 하나만 다른 데이터베이스로 옮기고 싶을 때 유용하죠. 패턴 매칭도 가능합니다. .dump 'log_%'처럼 쓰면 log_로 시작하는 모든 테이블이 덤프됩니다.

데이터 없이 스키마만 내보내기

행 데이터는 빼고 구조만 필요할 때도 있습니다. 문서화 용도이거나, 깔끔한 개발 환경을 새로 꾸리거나, 여러 데이터베이스의 스키마를 비교할 때가 그렇죠. .schema는 CREATE 문만 출력해 줍니다.

sqlite3 mydb.sqlite .schema > schema.sql

테이블 이름까지 같이 적어주면 해당 테이블 하나만 가져올 수 있습니다:

sqlite3 mydb.sqlite ".schema users" > users_schema.sql

출력은 순수 SQL입니다. CREATE TABLE, CREATE INDEX, CREATE TRIGGER 문이 그대로 떨어져서, 빈 데이터베이스에 바로 실행할 수 있는 형태죠.

알아두면 유용한 다른 출력 모드

.mode에는 CSV와 JSON 말고도 쓸 만한 옵션이 꽤 있습니다. 그중에서 알아두면 좋은 것들을 정리해 봤습니다.

.mode column        -- 정렬된 열, 터미널에서 읽기 좋음
.mode markdown      -- 파이프로 구분된, GitHub 친화적인 테이블
.mode html          -- HTML <table> 출력
.mode tabs          -- 탭으로 구분된 값 (TSV)
.mode insert users  -- 지정된 테이블에 대한 INSERT 문을 생성
.mode quote         -- SQL로 인용된 값, 검사에 유용

.mode markdown은 쿼리 결과를 README나 PR 본문에 그대로 붙여넣기 딱 좋습니다. .mode insert <table>은 시드 데이터를 만들 때 유용한데요, SELECT을 한 번 실행해서 나온 INSERT 구문을 그대로 픽스처 파일에 복사해 넣으면 끝입니다.

sqlite> .mode insert users
sqlite> .output seed.sql
sqlite> SELECT * FROM users WHERE signup_date >= '2025-02-01';
sqlite> .output stdout

알아두면 좋은 실전 팁

• .output stdout(또는 인자 없이 .output)으로 출력 대상을 터미널로 되돌릴 수 있습니다. 이걸 깜빡하면 다음 쿼리 결과가 조용히 파일로 새어나가 버리니 주의하세요.
• CSV로 내보내면 타입 정보는 사라집니다. 파일 안에서는 모두 텍스트가 되고, 다시 가져올 때는 해석할 스키마가 필요합니다. SQLite 데이터베이스 사이를 그대로 왕복시키고 싶다면 .dump를 쓰세요.
• 대용량 내보내기는 스트리밍 방식으로 처리됩니다. .output은 행이 생성되는 즉시 파일에 기록하므로, RAM보다 큰 테이블도 무리 없이 덤프할 수 있습니다.
• 운영 중인 데이터베이스를 핫 백업하려면 .dump로도 가능하지만, 전용 .backup 명령(뒤쪽 커리큘럼에서 다룹니다)이 더 빠르고 안전합니다. SQLite의 온라인 백업 API를 사용하기 때문입니다.

다음 주제: 데이터 조회하기

여기까지 데이터를 쓰는 방법은 거의 다 다뤘습니다 — INSERT, UPDATE, DELETE, UPSERT, RETURNING, CSV에서 가져오기, 그리고 다시 내보내기까지요. 이제 데이터베이스를 다루는 또 다른 절반, 즉 데이터를 효율적으로 읽어오는 방법으로 넘어갈 차례입니다. 실제로 시간을 가장 많이 쏟게 될 SELECT 문을 다음 페이지에서 만나보겠습니다.