Łączenie z SQLite z aplikacji: Python, Node, Go, Java

Połączenie to po prostu otwarty plik

SQLite nie ma serwera. Nie ma demona nasłuchującego na porcie, hosta, z którym trzeba się połączyć, ani danych logowania do wynegocjowania. "Łączenie się" oznacza, że twój sterownik otwiera plik na dysku i zaczyna czytać i zapisywać jego strony. To cały model myślowy.

Każdy język ma sterownik, który opakowuje bibliotekę C SQLite. Kształty się różnią, ale elementy są te same: ścieżka do pliku bazy, wywołanie otwierające, uchwyt, na którym wykonujesz instrukcje, i wywołanie zamykające, gdy skończysz.

-- W uproszczeniu każdy sterownik robi to:
-- 1. Otwiera lub tworzy plik pod podaną ścieżką.
-- 2. Uzyskuje uchwyt.
-- 3. Wykonuje SQL przez prepared statements.
-- 4. Zamyka uchwyt.

Reszta tej strony pokazuje, jak to wygląda w prawdziwym kodzie, oraz kilka ustawień, które warto skonfigurować przed pierwszym zapytaniem.

Python: sqlite3 w bibliotece standardowej

Python ma wbudowany moduł sqlite3, więc nic nie trzeba instalować. Podstawowy kształt:

-- 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()

Kilka rzeczy, które warto wiedzieć:

• sqlite3.connect("app.db") tworzy plik, jeśli nie istnieje. Przekaż ":memory:", aby dostać bazę istniejącą tylko w RAM.
• sqlite3.connect("file:app.db?mode=ro", uri=True) otwiera bazę tylko do odczytu przez formę URI.
• ? w SQL to symbol zastępczy: używaj wiązania parametrów, nigdy sklejania ciągów. Następny rozdział omawia to dokładniej.
• conn.commit() jest wymagane, chyba że używasz menedżera kontekstu (with conn:), który zatwierdza automatycznie.

W długo działającej aplikacji ustaw busy timeout, aby równolegli zapisujący czekali, zamiast zgłaszać błąd:

-- Python
conn.execute("PRAGMA busy_timeout = 5000")   -- czekaj do 5 s
conn.execute("PRAGMA journal_mode = WAL")    -- lepsza współbieżność

Node.js: better-sqlite3

Ekosystem Node ma kilka opcji, ale większość zespołów wybiera better-sqlite3. Jest synchroniczny (co w Node brzmi źle, ale w przypadku SQLite jest szybsze, bo zapytania wracają w mikrosekundach).

-- 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(...) zwraca obiekt instrukcji wielokrotnego użytku. .run() służy do zapisów, .all() zwraca wszystkie wiersze, a .get() jeden. Ten sam wzorzec co w większości sterowników SQL.

Ustaw pragmy przy starcie:

-- Node.js
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("foreign_keys = ON");   -- domyślnie wyłączone, prawie zawsze potrzebne

foreign_keys = ON warto podkreślić: SQLite nie egzekwuje kluczy obcych, dopóki o to nie poprosisz, i to dla każdego połączenia osobno. Jeśli zapomnisz, twoje klauzule REFERENCES są tylko ozdobą.

Go: database/sql ze sterownikiem

Standardowy pakiet Go database/sql nie zależy od konkretnego sterownika. Dla SQLite popularne są modernc.org/sqlite (czyste Go, bez CGO) i github.com/mattn/go-sqlite3 (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)
}

Ciąg zapytania po nazwie pliku to sposób, w jaki ten sterownik przekazuje pragmy przy nawiązywaniu połączenia. Format zależy od sterownika, więc sprawdź dokumentację tego, który wybierzesz.

sql.Open tak naprawdę nie otwiera połączenia, robi to dopiero pierwsze zapytanie. db to pula połączeń. Dla SQLite zwykle właściwa jest mała pula (a nawet db.SetMaxOpenConns(1) przy obciążeniach z dużą liczbą zapisów).

Java: JDBC

Standardem jest sterownik org.xerial:sqlite-jdbc. Adresy JDBC wyglądają tak: 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));
    }
}

W pamięci: jdbc:sqlite::memory:. Tylko do odczytu: dopisz ?open_mode=1 albo użyj obiektu SQLiteConfig.

PHP: PDO

DSN SQLite w PDO to 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";
}

sqlite::memory: daje bazę w pamięci. Zawsze ustawiaj ATTR_ERRMODE na wyjątki: ciche błędy trudno debugować.

Connection stringi i ścieżki plików

We wszystkich sterownikach zobaczysz dwa rodzaje "connection stringa":

• Zwykła ścieżka: app.db, ./data/app.db, /var/lib/myapp/app.db. Ścieżki względne są liczone od katalogu roboczego procesu, a na produkcji rzadko o to chodzi. Wybieraj ścieżki bezwzględne.
• Forma URI: file:app.db?mode=rwc&cache=shared. Pozwala ustawić flagi, takie jak mode=ro (tylko do odczytu), mode=rwc (odczyt, zapis i tworzenie, domyślnie), cache=shared i nolock=1.

Specjalne wartości, które spotkasz:

• :memory:: prywatna baza w pamięci. Każde połączenie dostaje własną.
• file::memory:?cache=shared: baza w pamięci, którą może współdzielić wiele połączeń w tym samym procesie.
• "" (pusty ciąg): prywatna, tymczasowa baza na dysku, usuwana przy zamknięciu.

JDBC poprzedza URI prefiksem jdbc:sqlite:. PDO używa sqlite:. Sterowniki Go i moduł sqlite3 w Pythonie przyjmują ścieżkę lub URI bezpośrednio.

A co z pulami połączeń?

SQLite to baza z jednym zapisującym. W każdej chwili blokadę zapisu trzyma dokładnie jedno połączenie, a wszyscy inni czekają. Pula wielu zapisujących nie przyspiesza zapisów, tylko zwiększa liczbę chętnych do tej samej blokady.

Mimo to mała pula przydaje się do:

• Równoległych odczytów w trybie WAL, gdzie czytający nie blokują siebie nawzajem ani zapisującego.
• Unikania blokowania kolejki, gdy jedno wolne zapytanie wstrzymuje całą aplikację.

Rozsądne ustawienia domyślne dla aplikacji webowej:

• Włączony tryb WAL.
• busy_timeout rzędu kilku sekund, aby przy rywalizacji grzecznie czekać, zamiast zgłaszać błąd.
• Pula z 1 zapisującym i N czytającymi albo jedno współdzielone połączenie przy małym ruchu.
• Włączone klucze obce, w każdym połączeniu.

-- Ustaw to w każdym nowym połączeniu:
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;   -- bezpieczne z WAL, szybsze niż FULL

synchronous = NORMAL to typowa para dla WAL: trwałe przy awariach aplikacji, nieco luźniejsze przy awariach systemu operacyjnego i wyraźnie szybsze niż domyślne FULL.

Zamykanie połączeń (i dlaczego to ważne)

Każdy sterownik ma wywołanie zamykające: conn.close(), db.Close(), db.close(). Brak zamknięcia powoduje wyciek deskryptorów plików i może sprawić, że plik WAL będzie rósł.

W długo działających usługach częstszy wzorzec to jedno połączenie (lub pula) na cały czas życia procesu, a nie otwieranie i zamykanie przy każdym żądaniu. Otwarcie połączenia SQLite jest tanie, ale ponowne ustawianie pragm za każdym razem to marnotrawstwo i łatwo o nim zapomnieć.

-- Python: jedno połączenie na proces, wspólne dla wszystkich żądań
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")

W Pythonie check_same_thread=False jest potrzebne, jeśli połączenie będzie używane z wielu wątków, a wtedy przyda się blokada albo pula, aby wywołania szły po kolei.

Lista kontrolna przed wdrożeniem

Zanim skierujesz prawdziwy ruch na bazę SQLite:

• Użyj ścieżki bezwzględnej do pliku bazy.
• Włącz tryb WAL (PRAGMA journal_mode = WAL).
• Ustaw busy_timeout na 2 do 10 sekund.
• Włącz klucze obce w każdym połączeniu.
• Używaj prepared statements z wiązaniem parametrów, nigdy wstawiania wartości do ciągów.
• Upewnij się, że proces może zapisywać w katalogu z bazą (w trybie WAL SQLite zapisuje obok głównego pliku pliki -wal i -shm).
• Pomyśl o kopiach zapasowych, zanim będą potrzebne: VACUUM INTO i polecenie .backup są omówione dalej.

Dalej: migracje

Łączenie się to łatwa część. Trudniejsza to rozwijanie schematu w czasie bez ręcznej edycji baz produkcyjnych. Migracje zamieniają ALTER TABLE w powtarzalny proces pod kontrolą wersji. O tym na następnej stronie.