Kết nối SQLite từ ứng dụng: Python, Node, Go, Java

Kết nối chỉ là một tệp đang mở

SQLite không có máy chủ. Không có tiến trình nền nào lắng nghe trên cổng, không có host để gọi tới, không có thông tin đăng nhập để thương lượng. "Kết nối" nghĩa là driver mở một tệp trên ổ đĩa và bắt đầu đọc, ghi các trang của tệp đó. Toàn bộ mô hình chỉ có vậy.

Ngôn ngữ nào cũng có một driver bọc thư viện C của SQLite. Hình dạng mỗi nơi một khác, nhưng các thành phần thì giống nhau: đường dẫn tới tệp cơ sở dữ liệu, một lời gọi mở, một handle để chạy câu lệnh, và một lời gọi đóng khi xong việc.

-- Conceptually, every driver does this:
-- 1. Open or create the file at the given path.
-- 2. Acquire a handle.
-- 3. Run SQL via prepared statements.
-- 4. Close the handle.

Phần còn lại của trang này cho thấy điều đó trông ra sao trong code thật, cùng vài cài đặt nên đặt trước truy vấn đầu tiên.

Python: sqlite3 trong thư viện chuẩn

Python có sẵn sqlite3, không cần cài đặt. Cấu trúc cơ bản:

-- Python
import sqlite3

conn = sqlite3.connect("app.db")
conn.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
conn.execute("INSERT INTO notes (body) VALUES (?)", ("first note",))
conn.commit()

for row in conn.execute("SELECT id, body FROM notes"):
    print(row)

conn.close()

Vài điều nên biết:

• sqlite3.connect("app.db") tạo tệp nếu nó chưa tồn tại. Truyền ":memory:" để có cơ sở dữ liệu chỉ nằm trong RAM.
• sqlite3.connect("file:app.db?mode=ro", uri=True) mở ở chế độ chỉ đọc bằng dạng URI.
• Dấu ? trong câu SQL là placeholder: hãy dùng ràng buộc tham số, đừng bao giờ ghép chuỗi. Chương tiếp theo sẽ đi sâu hơn vào chuyện này.
• Bắt buộc gọi conn.commit(), trừ khi bạn dùng context manager (with conn:), vốn tự động commit.

Với ứng dụng chạy lâu dài, hãy đặt busy timeout để các kết nối ghi đồng thời chờ thay vì báo lỗi:

-- Python
conn.execute("PRAGMA busy_timeout = 5000")   -- wait up to 5s
conn.execute("PRAGMA journal_mode = WAL")    -- better concurrency

Node.js: better-sqlite3

Hệ sinh thái Node có vài lựa chọn, nhưng better-sqlite3 là thư viện được nhiều đội dùng nhất. Nó chạy đồng bộ (nghe có vẻ sai với Node, nhưng thực ra lại nhanh hơn với SQLite, vì truy vấn trả về chỉ trong vài micro giây).

-- Node.js
const Database = require("better-sqlite3");
const db = new Database("app.db");

db.exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");

const insert = db.prepare("INSERT INTO notes (body) VALUES (?)");
insert.run("first note");

const rows = db.prepare("SELECT id, body FROM notes").all();
console.log(rows);

db.close();

db.prepare(...) trả về một đối tượng câu lệnh dùng lại được. .run() dành cho thao tác ghi, .all() trả về mọi hàng, .get() trả về một hàng. Cùng mẫu với hầu hết driver SQL.

Đặt các pragma khi khởi động:

-- Node.js
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("foreign_keys = ON");   -- off by default, almost always wanted

foreign_keys = ON cần được nhấn mạnh: SQLite không kiểm tra khóa ngoại trừ khi bạn yêu cầu, và yêu cầu đó tính theo từng kết nối. Nếu quên, các mệnh đề REFERENCES chỉ để trang trí.

Go: database/sql với một driver

Package chuẩn database/sql của Go không phụ thuộc driver. Với SQLite, các lựa chọn phổ biến là modernc.org/sqlite (thuần Go, không cần CGO) và github.com/mattn/go-sqlite3 (dùng CGO).

-- Go
import (
    "database/sql"
    _ "modernc.org/sqlite"
)

db, err := sql.Open("sqlite", "app.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")
if err != nil { panic(err) }
defer db.Close()

_, err = db.Exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
_, err = db.Exec("INSERT INTO notes (body) VALUES (?)", "first note")

rows, _ := db.Query("SELECT id, body FROM notes")
defer rows.Close()
for rows.Next() {
    var id int; var body string
    rows.Scan(&id, &body)
    fmt.Println(id, body)
}

Chuỗi truy vấn sau tên tệp là cách driver này truyền pragma lúc kết nối. Định dạng khác nhau tùy driver, nên hãy xem tài liệu của driver bạn chọn.

sql.Open không thực sự mở kết nối; truy vấn đầu tiên mới làm việc đó. db là một connection pool. Với SQLite, một pool nhỏ (hoặc thậm chí db.SetMaxOpenConns(1) cho khối lượng ghi lớn) thường là hợp lý.

Java: JDBC

Driver org.xerial:sqlite-jdbc là lựa chọn tiêu chuẩn. URL của JDBC có dạng jdbc:sqlite:<path>:

-- Java
import java.sql.*;

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db")) {
    try (Statement st = conn.createStatement()) {
        st.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
        st.execute("PRAGMA journal_mode = WAL");
        st.execute("PRAGMA busy_timeout = 5000");
    }

    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO notes (body) VALUES (?)")) {
        ps.setString(1, "first note");
        ps.executeUpdate();
    }

    try (PreparedStatement ps = conn.prepareStatement("SELECT id, body FROM notes");
         ResultSet rs = ps.executeQuery()) {
        while (rs.next()) System.out.println(rs.getInt(1) + " " + rs.getString(2));
    }
}

Trong bộ nhớ: jdbc:sqlite::memory:. Chỉ đọc: thêm ?open_mode=1 hoặc dùng một đối tượng SQLiteConfig.

PHP: PDO

DSN cho SQLite của PDO là sqlite:<path>:

-- PHP
$db = new PDO("sqlite:app.db");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
$db->exec("PRAGMA journal_mode = WAL");
$db->exec("PRAGMA busy_timeout = 5000");

$stmt = $db->prepare("INSERT INTO notes (body) VALUES (?)");
$stmt->execute(["first note"]);

foreach ($db->query("SELECT id, body FROM notes") as $row) {
    echo $row["id"] . " " . $row["body"] . "\n";
}

Dùng sqlite::memory: cho cơ sở dữ liệu trong bộ nhớ. Luôn đặt ATTR_ERRMODE sang chế độ ngoại lệ: lỗi im lặng rất khó gỡ.

Connection string và đường dẫn tệp

Qua các driver, bạn sẽ gặp hai kiểu "connection string":

• Đường dẫn thường: app.db, ./data/app.db, /var/lib/myapp/app.db. Đường dẫn tương đối được tính theo thư mục làm việc của tiến trình, mà điều đó hiếm khi là thứ bạn muốn trên production. Nên dùng đường dẫn tuyệt đối.
• Dạng URI: file:app.db?mode=rwc&cache=shared. Cho phép đặt các cờ như mode=ro (chỉ đọc), mode=rwc (đọc, ghi, tạo mới; mặc định), cache=shared và nolock=1.

Các giá trị đặc biệt bạn sẽ gặp:

• :memory:: một cơ sở dữ liệu riêng trong bộ nhớ. Mỗi kết nối có cơ sở dữ liệu riêng.
• file::memory:?cache=shared: cơ sở dữ liệu trong bộ nhớ mà nhiều kết nối trong cùng một tiến trình có thể dùng chung.
• "" (chuỗi rỗng): một cơ sở dữ liệu tạm, riêng tư trên ổ đĩa, bị xóa khi đóng.

JDBC thêm tiền tố jdbc:sqlite: vào URI. PDO dùng sqlite:. Các driver Go và sqlite3 của Python nhận trực tiếp đường dẫn hoặc URI.

Còn connection pool thì sao?

SQLite là cơ sở dữ liệu một kết nối ghi. Tại mỗi thời điểm, đúng một kết nối giữ khóa ghi; mọi kết nối khác phải chờ. Gom nhiều kết nối ghi vào pool không làm ghi nhanh hơn, mà chỉ tạo thêm đối thủ tranh cùng một khóa.

Dù vậy, một pool nhỏ vẫn có ích để:

• Đọc đồng thời ở chế độ WAL, khi các thao tác đọc không chặn nhau và không chặn thao tác ghi.
• Tránh nghẽn đầu hàng, khi một truy vấn chậm làm đứng cả ứng dụng.

Giá trị mặc định hợp lý cho một ứng dụng web:

• Bật chế độ WAL.
• busy_timeout vài giây để tranh chấp chờ một cách lịch sự thay vì báo lỗi.
• Kích thước pool là 1 kết nối ghi cộng N kết nối đọc, hoặc chỉ một kết nối dùng chung nếu lưu lượng thấp.
• Bật khóa ngoại trên mọi kết nối.

-- Apply these on every new connection:
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;   -- safe with WAL; faster than FULL

synchronous = NORMAL là cặp đi kèm điển hình với WAL: bền vững khi ứng dụng bị crash, kém chặt hơn một chút khi hệ điều hành bị crash, và nhanh hơn rõ rệt so với mặc định FULL.

Đóng kết nối (và vì sao điều đó quan trọng)

Driver nào cũng có lời gọi đóng: conn.close(), db.Close(), db.close(). Không đóng sẽ rò rỉ file descriptor và có thể khiến tệp WAL cứ phình to.

Trong các dịch vụ chạy lâu dài, mẫu phổ biến hơn là một kết nối (hoặc một pool) cho suốt vòng đời của tiến trình, chứ không mở rồi đóng quanh mỗi request. Mở một kết nối SQLite thì rẻ, nhưng áp lại pragma mỗi lần vừa lãng phí vừa dễ quên.

-- Python - connection per process, reused across requests
DB = sqlite3.connect("app.db", check_same_thread=False)
DB.execute("PRAGMA journal_mode = WAL")
DB.execute("PRAGMA busy_timeout = 5000")
DB.execute("PRAGMA foreign_keys = ON")

Riêng với Python, cần check_same_thread=False nếu bạn dùng kết nối từ nhiều luồng, và bạn sẽ cần một lock hoặc một pool để tuần tự hóa các lời gọi.

Danh sách kiểm tra trước khi đưa lên production

Trước khi cho lưu lượng thật đổ vào một cơ sở dữ liệu SQLite:

• Dùng đường dẫn tuyệt đối cho tệp cơ sở dữ liệu.
• Bật chế độ WAL (PRAGMA journal_mode = WAL).
• Đặt busy_timeout từ 2 đến 10 giây.
• Bật khóa ngoại trên mọi kết nối.
• Dùng prepared statement với ràng buộc tham số, đừng bao giờ chèn chuỗi trực tiếp.
• Chắc chắn tiến trình có quyền ghi vào thư mục chứa cơ sở dữ liệu (ở chế độ WAL, SQLite ghi tệp -wal và -shm cạnh tệp chính).
• Nghĩ tới việc sao lưu trước khi cần đến nó: VACUUM INTO và lệnh .backup sẽ được trình bày ở phần sau.

Tiếp theo: migration

Kết nối là phần dễ. Phần khó hơn là thay đổi schema theo thời gian mà không phải sửa tay cơ sở dữ liệu production. Migration là cách biến ALTER TABLE thành một quy trình lặp lại được và có quản lý phiên bản. Đó là nội dung của trang tiếp theo.