התחברות ל-SQLite מאפליקציות: Python, Node, Go, Java

חיבור הוא פשוט קובץ פתוח

ל-SQLite אין שרת. אין daemon שמאזין על פורט, אין מארח שמתקשרים אליו, אין פרטי התחברות לנהל עליהם משא ומתן. "התחברות" פירושה שהדרייבר שלכם פותח קובץ בדיסק ומתחיל לקרוא ולכתוב דפים שלו. זה כל המודל המחשבתי.

לכל שפה יש דרייבר שעוטף את ספריית ה-C של SQLite. הצורות שונות, אבל החלקים הנעים זהים: נתיב לקובץ מסד הנתונים, קריאת פתיחה, ידית שמריצים עליה פקודות, וקריאת סגירה כשמסיימים.

-- מבחינה מושגית, כל דרייבר עושה את זה:
-- 1. פותח או יוצר את הקובץ בנתיב שניתן.
-- 2. מקבל ידית.
-- 3. מריץ SQL דרך prepared statements.
-- 4. סוגר את הידית.

שאר העמוד מראה איך זה נראה בקוד אמיתי, ואת מעט ההגדרות ששווה לקבוע לפני השאילתה הראשונה.

Python: sqlite3 בספרייה הסטנדרטית

Python מגיעה עם sqlite3, בלי צורך בהתקנה. המבנה הבסיסי:

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

כמה דברים שכדאי לדעת:

• sqlite3.connect("app.db") יוצר את הקובץ אם הוא לא קיים. העבירו ":memory:" למסד נתונים שחי רק ב-RAM.
• sqlite3.connect("file:app.db?mode=ro", uri=True) פותח לקריאה בלבד דרך צורת ה-URI.
• ה-? ב-SQL הוא מציין מקום: השתמשו בקשירת פרמטרים, אף פעם לא בשרשור מחרוזות. הפרק הבא מעמיק בזה.
• conn.commit() נדרש, אלא אם משתמשים ב-context manager (with conn:) שמבצע commit אוטומטית.

באפליקציה שרצה לאורך זמן, הגדירו זמן המתנה כדי שכותבים מקבילים יחכו במקום לזרוק שגיאה:

-- Python
conn.execute("PRAGMA busy_timeout = 5000")   -- להמתין עד 5 שניות
conn.execute("PRAGMA journal_mode = WAL")    -- מקביליות טובה יותר

Node.js: better-sqlite3

לאקוסיסטם של Node יש כמה אפשרויות, אבל better-sqlite3 היא זו שרוב הצוותים בוחרים. היא סינכרונית (מה שנשמע לא נכון ל-Node, אבל בפועל מהיר יותר ל-SQLite, כי השאילתות חוזרות תוך מיקרו שניות).

-- 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(...) מחזירה אובייקט פקודה שאפשר להשתמש בו שוב. .run() מיועדת לכתיבות, .all() מחזירה את כל השורות, .get() מחזירה שורה אחת. אותו דפוס כמו ברוב הדרייברים של SQL.

הגדירו pragmas בזמן העלייה:

-- Node.js
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("foreign_keys = ON");   -- כבוי כברירת מחדל, כמעט תמיד רוצים אותו

foreign_keys = ON ראוי לציון מיוחד: SQLite לא אוכפת מפתחות זרים אלא אם מבקשים ממנה, בכל חיבור בנפרד. אם שכחתם, פסוקיות ה-REFERENCES שלכם הן קישוט בלבד.

Go: database/sql עם דרייבר

החבילה הסטנדרטית database/sql של Go לא תלויה בדרייבר מסוים. ל-SQLite, modernc.org/sqlite (Go טהור, בלי CGO) ו-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)
}

מחרוזת השאילתה שאחרי שם הקובץ היא הדרך שבה הדרייבר הזה מעביר pragmas בזמן החיבור: הפורמט משתנה מדרייבר לדרייבר, אז בדקו את התיעוד של זה שבחרתם.

sql.Open לא באמת פותחת חיבור; השאילתה הראשונה עושה את זה. db הוא מאגר חיבורים. ל-SQLite, מאגר קטן (או אפילו db.SetMaxOpenConns(1) לעומסים עם הרבה כתיבות) הוא בדרך כלל הבחירה הנכונה.

Java: JDBC

הדרייבר org.xerial:sqlite-jdbc הוא הסטנדרט. כתובות JDBC נראות כך: 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));
    }
}

בזיכרון: jdbc:sqlite::memory:. לקריאה בלבד: הוסיפו ?open_mode=1 או השתמשו באובייקט SQLiteConfig.

PHP: PDO

ה-DSN של PDO ל-SQLite הוא 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: למסד נתונים בזיכרון. הגדירו תמיד את ATTR_ERRMODE לחריגות: קשה מאוד לדבג כישלונות שקטים.

מחרוזות חיבור ונתיבי קבצים

בכל הדרייברים תראו שני סוגים של "מחרוזת חיבור":

• נתיב רגיל: app.db, ./data/app.db, /var/lib/myapp/app.db. נתיבים יחסיים הם יחסיים לתיקיית העבודה של התהליך, וזה כמעט אף פעם לא מה שרוצים בפרודקשן. העדיפו נתיבים מוחלטים.
• צורת URI: file:app.db?mode=rwc&cache=shared. מאפשרת להגדיר דגלים כמו mode=ro (קריאה בלבד), mode=rwc (קריאה, כתיבה ויצירה, ברירת המחדל), cache=shared ו-nolock=1.

ערכים מיוחדים שתפגשו:

• :memory:: מסד נתונים פרטי בזיכרון. כל חיבור מקבל אחד משלו.
• file::memory:?cache=shared: מסד נתונים בזיכרון שכמה חיבורים באותו תהליך יכולים לחלוק.
• "" (מחרוזת ריקה): מסד נתונים פרטי וזמני בדיסק שנמחק כשסוגרים אותו.

JDBC מוסיף לפני ה-URI את הקידומת jdbc:sqlite:. PDO משתמש ב-sqlite:. דרייברים של Go וה-sqlite3 של Python מקבלים את הנתיב או את ה-URI ישירות.

ומה עם מאגרי חיבורים?

SQLite היא מסד נתונים עם כותב יחיד. בכל רגע, בדיוק חיבור אחד מחזיק את נעילת הכתיבה; כל השאר מחכים. מאגר של הרבה כותבים לא הופך כתיבות למהירות יותר: הוא רק נותן לכם יותר מתחרים על אותה נעילה.

עם זאת, מאגר קטן שימושי ל:

• קריאות מקבילות במצב WAL, שבו קוראים לא חוסמים זה את זה ולא את הכותב.
• הימנעות מחסימת ראש התור, שבה שאילתה איטית אחת תוקעת את כל האפליקציה.

ברירות מחדל סבירות לאפליקציית ווב:

• מצב WAL מופעל.
• busy_timeout של כמה שניות, כדי שתחרות תחכה בנימוס במקום לזרוק שגיאה.
• גודל מאגר של כותב אחד ועוד N קוראים, או פשוט חיבור משותף אחד אם התנועה קלה.
• מפתחות זרים מופעלים, בכל חיבור.

-- החילו את אלה על כל חיבור חדש:
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;   -- בטוח עם WAL; מהיר יותר מ-FULL

synchronous = NORMAL הוא הצירוף הטיפוסי עם WAL: עמיד בפני קריסות של האפליקציה, קצת פחות מחמיר בפני קריסות של מערכת ההפעלה, ומהיר באופן מורגש מברירת המחדל FULL.

סגירת חיבורים (ולמה זה חשוב)

לכל דרייבר יש קריאת סגירה: conn.close(), db.Close(), db.close(). אי סגירה גורמת לדליפה של file descriptors ועלולה להשאיר את קובץ ה-WAL גדל.

בשירותים שרצים לאורך זמן, הדפוס הנפוץ יותר הוא חיבור אחד (או מאגר) לכל משך החיים של התהליך, ולא פתיחה וסגירה סביב כל בקשה. פתיחת חיבור ל-SQLite זולה, אבל החלה מחדש של pragmas בכל פעם היא בזבוז, וקל לשכוח אותה.

-- Python: חיבור אחד לתהליך, בשימוש חוזר בין בקשות
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")

ב-Python ספציפית, צריך check_same_thread=False אם תשתמשו בחיבור מכמה threads, ותרצו נעילה או מאגר כדי לסדר את הקריאות בתור.

רשימת בדיקה לפני שעולים לאוויר

לפני שמפנים תנועה אמיתית למסד נתונים של SQLite:

• השתמשו בנתיב מוחלט לקובץ מסד הנתונים.
• הפעילו מצב WAL (PRAGMA journal_mode = WAL).
• הגדירו busy_timeout של 2 עד 10 שניות.
• השתמשו ב-prepared statements עם קשירת פרמטרים, אף פעם לא בשתילת מחרוזות.
• הפעילו מפתחות זרים, בכל חיבור.
• ודאו שלתהליך יש הרשאת כתיבה לתיקייה שמכילה את מסד הנתונים (במצב WAL, SQLite כותבת קבצי -wal ו--shm לצד הקובץ הראשי).
• חשבו על גיבויים לפני שתצטרכו אותם: VACUUM INTO והפקודה .backup מכוסים בהמשך.

הבא בתור: מיגרציות

ההתחברות היא החלק הקל. החלק הקשה יותר הוא לפתח את הסכמה לאורך זמן בלי לערוך ידנית מסדי נתונים בפרודקשן. מיגרציות הן הדרך להפוך את ALTER TABLE לתהליך שאפשר לחזור עליו ושמנוהל בבקרת גרסאות: זה העמוד הבא.