Xuất dữ liệu SQLite: CSV, JSON và bản dump SQL từ CLI

Xuất dữ liệu là việc của shell, không phải câu lệnh SQL

SQLite không có COPY ... TO hay SELECT INTO OUTFILE như Postgres hoặc MySQL. Việc xuất dữ liệu nằm trong shell dòng lệnh sqlite3, điều khiển bằng các lệnh chấm (dot-command): .mode, .headers, .output, .dump. Nắm được bốn lệnh này, bạn có thể tạo CSV, JSON, văn bản thuần hoặc bản dump SQL đầy đủ từ bất kỳ cơ sở dữ liệu nào.

Cách hình dung: bạn bảo shell định dạng kết quả thế nào (.mode), có kèm tên cột hay không (.headers), và gửi đầu ra đến đâu (.output). Sau đó bạn chạy truy vấn, và kết quả của truy vấn đó được ghi vào tệp.

Hãy tạo một cơ sở dữ liệu nhỏ để thử:

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;

Ba hàng, bốn cột. Ta sẽ xuất dữ liệu này ra nhiều định dạng.

CSV: .mode csv kèm tiêu đề cột

CSV là định dạng xuất phổ biến nhất: bảng tính, pipeline dữ liệu và hầu hết các công cụ khác đều đọc được. Bên trong shell sqlite3:

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

Chuyện gì vừa xảy ra:

• .mode csv định dạng mỗi hàng thành các giá trị phân tách bằng dấu phẩy, đặt trong dấu ngoặc kép những trường chứa dấu phẩy, dấu ngoặc kép hoặc ký tự xuống dòng.
• .headers on thêm hàng đầu tiên chứa tên cột. Không có lệnh này, tệp CSV sẽ không có tiêu đề, thường không phải điều bạn muốn.
• .output users.csv chuyển hướng kết quả vào một tệp. Từ lúc này, đầu ra của truy vấn được ghi vào đó thay vì hiện trên màn hình.
• Câu SELECT chạy và lặng lẽ ghi vào tệp.
• .output stdout chuyển đầu ra trở lại terminal để bạn thấy kết quả của truy vấn tiếp theo.

Tệp kết quả:

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

Bạn có thể xuất kết quả của bất kỳ truy vấn nào, không chỉ cả bảng: lọc, join, gộp nhóm, rồi chuyển hướng:

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

Một lệnh duy nhất từ shell

Bạn không nhất thiết phải vào shell tương tác. Hãy truyền các lệnh chấm và SQL vào sqlite3 từ shell của hệ điều hành:

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

Đây là dạng bạn cần cho script và cron job: lặp lại được, không phải gõ tay. .output chỉ có tác dụng trong phiên đó, nên không để lại trạng thái ở đâu cả.

JSON: .mode json

Với dữ liệu xuất cho ứng dụng web hoặc công cụ đọc JSON, .mode json tạo ra một mảng các đối tượng, mỗi đối tượng là một hàng:

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

Tệp kết quả:

[{"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"}]

Trong JSON, tiêu đề cột đã nằm sẵn trong các khóa, nên .headers không có tác dụng ở đây. Nếu bạn muốn cấu trúc riêng (đối tượng lồng nhau, đổi tên trường), hãy dựng nó ngay trong truy vấn với 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;

Cách này cho bạn chuỗi JSON theo từng hàng với toàn quyền kiểm soát cấu trúc. Kết hợp với json_group_array() để gom toàn bộ kết quả thành một tài liệu JSON duy nhất.

Bản dump SQL đầy đủ: .dump

.dump khác hẳn CSV hay JSON. Nó tạo ra một tệp .sql chứa cấu trúc bảng và toàn bộ dữ liệu dưới dạng các câu lệnh CREATE TABLE và INSERT, đủ để dựng lại cơ sở dữ liệu từ con số 0:

sqlite3 mydb.sqlite .dump > backup.sql

Một đoạn trong kết quả:

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;

Khôi phục là thao tác ngược lại: truyền tệp vào một cơ sở dữ liệu mới:

sqlite3 restored.sqlite < backup.sql

.dump là công cụ phù hợp để sao lưu, lưu ảnh chụp dữ liệu kiểm thử vào hệ thống quản lý phiên bản, và chuyển dữ liệu giữa các máy. Nó giữ nguyên index, trigger, view, tức mọi thứ trong cấu trúc cơ sở dữ liệu.

Dump một bảng duy nhất

.dump nhận tên bảng (hoặc mẫu tên) để giới hạn đầu ra:

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

Lệnh này chỉ dump cấu trúc và các hàng của bảng users. Hữu ích khi bạn muốn chép một bảng sang cơ sở dữ liệu khác mà không mang theo phần còn lại. Bạn cũng có thể dùng mẫu: .dump 'log_%' dump mọi bảng có tên bắt đầu bằng log_.

Cấu trúc không kèm dữ liệu

Đôi khi bạn chỉ cần cấu trúc mà không cần các hàng: để làm tài liệu, dựng môi trường phát triển sạch, hoặc so sánh cấu trúc giữa các cơ sở dữ liệu. .schema chỉ in ra các câu lệnh CREATE:

sqlite3 mydb.sqlite .schema > schema.sql

Thêm tên bảng để chỉ lấy một bảng:

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

Đầu ra là SQL thuần (CREATE TABLE, CREATE INDEX, CREATE TRIGGER), sẵn sàng chạy trên một cơ sở dữ liệu trống.

Các chế độ hữu ích khác

.mode còn nhiều lựa chọn ngoài CSV và JSON. Một vài chế độ nên biết:

.mode column        -- aligned columns, nice for reading at the terminal
.mode markdown      -- pipe-separated, GitHub-friendly tables
.mode html          -- HTML <table> output
.mode tabs          -- tab-separated values (TSV)
.mode insert users  -- emits INSERT statements for the named table
.mode quote         -- SQL-quoted values, useful for inspection

.mode markdown rất tiện để dán kết quả truy vấn vào README hoặc pull request. .mode insert <table> là cách nhanh để tạo dữ liệu mẫu: chạy một câu SELECT, lấy các câu lệnh INSERT, rồi dán vào tệp fixture.

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

Vài lưu ý thực tế

• .output stdout (hoặc .output không kèm đối số) đưa đầu ra trở lại terminal. Quên lệnh này, kết quả của truy vấn tiếp theo sẽ lặng lẽ biến mất vào tệp.
• Xuất CSV không giữ kiểu dữ liệu. Mọi thứ trong tệp đều thành văn bản, và khi nhập lại cần một cấu trúc bảng đích để diễn giải. Dùng .dump nếu bạn cần chuyển dữ liệu qua lại với một cơ sở dữ liệu SQLite khác mà không mất gì.
• Dữ liệu lớn được xuất theo luồng. .output ghi từng hàng ngay khi được tạo ra, nên bạn có thể dump những bảng lớn hơn RAM mà không gặp vấn đề.
• Để sao lưu nóng một cơ sở dữ liệu đang hoạt động, .dump vẫn dùng được, nhưng lệnh chuyên dụng .backup (sẽ học ở phần sau) nhanh và an toàn hơn vì nó dùng API sao lưu trực tuyến của SQLite.

Tiếp theo: truy vấn dữ liệu

Giờ bạn đã có bức tranh đầy đủ về việc ghi dữ liệu: INSERT, UPDATE, DELETE, UPSERT, RETURNING, nhập từ CSV và xuất dữ liệu ra ngoài. Tiếp theo là nửa còn lại của việc làm với cơ sở dữ liệu: đọc dữ liệu hiệu quả. Câu lệnh SELECT là nơi bạn sẽ dành nhiều thời gian nhất, và đó là nội dung của trang tiếp theo.