SQLite Migrations: ניהול גרסאות של סכמה עם user_version

סכמות משתנות. תכננו לזה.

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

בשביל זה יש מיגרציות: רצף של סקריפטים קטנים וממוספרים שמעבירים מסד נתונים מגרסה N לגרסה N+1. הריצו אותם לפי הסדר, וכל מסד נתונים מדביק את הגרסה הנוכחית. אם תוותרו על המשמעת, תקבלו באגים של "אצלי זה עובד" שלוקח אחר צהריים שלם לאתר.

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

PRAGMA user_version;
PRAGMA user_version = 1;
PRAGMA user_version;

מסד נתונים חדש מתחיל ב-0. הגדירו אותו למספר המיגרציה שהרגע החלתם. קראו אותו בעליית האפליקציה כדי לדעת איפה אתם.

לולאת מיגרציות מינימלית

המודל המנטלי: כל מיגרציה היא סקריפט SQL ממוספר. האפליקציה קוראת את ה-user_version הנוכחי, מריצה לפי הסדר כל סקריפט עם מספר גבוה יותר, ומעדכנת את user_version אחרי כל אחד.

הנה מיגרציה 1, שיוצרת את הסכמה ההתחלתית:

BEGIN;

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

PRAGMA user_version = 1;

COMMIT;

שני דברים לשים לב אליהם. הכול עטוף ב-BEGIN; ... COMMIT; כך שהפעולה אטומית: אם ה-CREATE TABLE נכשל, user_version לא מתעדכן ואפשר לתקן ולנסות שוב. ו-PRAGMA user_version = 1 הוא הפקודה האחרונה לפני ה-commit, כך שהגרסה מתחלפת רק אם כל השאר הצליח.

עכשיו נניח שצריך להוסיף עמודה created_at. זו מיגרציה 2:

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

BEGIN;

ALTER TABLE users ADD COLUMN created_at TEXT
    NOT NULL DEFAULT (datetime('now'));

PRAGMA user_version = 2;

COMMIT;

SELECT sql FROM sqlite_master WHERE name = 'users';

מסד נתונים בגרסה 0 מריץ את שתיהן. מסד נתונים בגרסה 1 מריץ רק את השנייה. מסד נתונים בגרסה 2 לא מריץ כלום. הסדר הוא החוזה.

מה ALTER TABLE יכול ולא יכול לעשות

ה-ALTER TABLE של SQLite צר בכוונה. הוא תומך ב:

• ADD COLUMN: הוספת עמודה חדשה בסוף, עם ברירת מחדל אופציונלית.
• DROP COLUMN: הסרת עמודה (מאז 3.35).
• RENAME COLUMN: שינוי שם של עמודה (מאז 3.25).
• RENAME TO: שינוי שם הטבלה עצמה.

וזהו. אי אפשר לשנות טיפוס של עמודה, לשנות NOT NULL, לשנות אילוץ CHECK או להוסיף FOREIGN KEY לעמודה קיימת.

-- לא נתמך:
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');

כשצריך שינוי ש-SQLite לא יכולה לעשות ישירות, המתכון הרשמי הוא "לבנות את הטבלה מחדש". זה ארוך יותר אבל אמין לחלוטין.

בנייה מחדש של טבלה לשינויים גדולים יותר

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

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada'), ('b@x.com', 'Boris');

-- עכשיו אנחנו רוצים אילוץ CHECK על email. בונים מחדש:
CREATE TABLE users_new (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE CHECK (email LIKE '%@%'),
    name  TEXT NOT NULL
);

INSERT INTO users_new (id, email, name)
    SELECT id, email, name FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

התיעוד המלא של SQLite קורא לזה המתכון של 12 השלבים ומוסיף כמה אזהרות נוספות לגבי טריגרים, views והפניות של מפתחות זרים, ושווה לקרוא אותו פעם אחת לפני שעושים את זה על סכמת ייצור. ברוב המקרים גרסת ארבעת השלבים שלמעלה מספיקה.

אזהרה: אם יש לכם מפתחות זרים שמצביעים על הטבלה שאתם בונים מחדש, הריצו PRAGMA foreign_keys = OFF לפני המיגרציה ו-PRAGMA foreign_keys = ON אחריה. אחרת ה-DROP TABLE יכול לשבור את השלמות ההפניתית באמצע הדרך.

הרצת מיגרציות מתוך האפליקציה

הניהול פשוט מספיק כדי שתוכלו לכתוב אותו בעצמכם. ב-Python עם הספרייה הסטנדרטית:

-- מבחינה רעיונית, האפליקציה עושה את זה בעלייה:
--
--   current = SELECT user_version FROM PRAGMA;
--   for i in range(current + 1, len(MIGRATIONS) + 1):
--       להריץ את MIGRATIONS[i] בתוך טרנזקציה
--       PRAGMA user_version = i;
--
-- ה-SQL שכל שלב מריץ נראה כמו המיגרציות שלמעלה.

PRAGMA user_version;

העקרונות הקבועים:

• המיגרציות ממוספרות ברצף החל מ-1. בלי פערים, בלי שינויי סדר.
• כל מיגרציה עטופה בטרנזקציה יחד עם העדכון PRAGMA user_version = N.
• ברגע שמיגרציה בוצעה ושוחררה, אף פעם לא עורכים אותה. שינויים חדשים נכנסים למיגרציה חדשה.

הכלל האחרון הוא זה שצוותים שוברים הכי הרבה. אם תערכו את מיגרציה 3 אחרי שמסד הנתונים של עמית כבר החיל אותה, מסד הנתונים שלו יהיה לא מסונכרן עם שלכם, בשקט, לתמיד.

תיעוד היסטוריה

user_version אומר לכם איפה מסד נתונים נמצא. הוא לא אומר לכם מתי כל שלב רץ או מה הוא עשה. טבלת רישום קטנה פותרת את זה:

CREATE TABLE schema_migrations (
    version    INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

BEGIN;

CREATE TABLE posts (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    body   TEXT NOT NULL
);

INSERT INTO schema_migrations (version, name)
    VALUES (1, 'create_posts');
PRAGMA user_version = 1;

COMMIT;

SELECT * FROM schema_migrations;

עכשיו יש לכם שורה לכל מיגרציה עם שם וחותמת זמן, וזה שימושי כשמדבגים "למה יש במסד הנתונים הזה עמודה שהקוד לא מצפה לה?"

PRAGMA user_version הוא עדיין מקור האמת של הלולאה; הטבלה היא בשביל בני אדם.

ביטול: מה טרנזקציות נותנות לכם, ומה לא

ה-DDL של SQLite הוא טרנזקציוני. אם מיגרציה 5 מתחילה ליצור טבלה, להעתיק נתונים ולעדכן את user_version, וההעתקה נכשלת באמצע, ROLLBACK מבטל הכול, כולל ה-CREATE TABLE. מסד הנתונים חוזר בדיוק למצב שהיה בו לפני BEGIN.

BEGIN;

CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts (id, balance) VALUES (1, 100);

-- נניח שמשהו משתבש כאן:
ROLLBACK;

-- הטבלה לא קיימת:
SELECT name FROM sqlite_master WHERE type='table' AND name='accounts';

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

בפועל, הרבה פרויקטים קטנים מוותרים לגמרי על down-migrations וסומכים על גיבויים בשביל "ביטול". זו בחירה לגיטימית, כל עוד באמת לוקחים גיבויים.

כמה הרגלים שיחסכו כאב בהמשך

• מיגרציה אחת לכל שינוי לוגי. מיגרציה שמוסיפה שלוש עמודות לא קשורות קשה יותר לבדיקה ולביטול משלוש מיגרציות.
• בדקו מיגרציות מול עותק של סביבת הייצור. שינויי סכמה יכולים להיות איטיים בטבלאות גדולות; לגלות את זה בייצור זה לא כיף.
• אף פעם לא לערוך מיגרציה ששוחררה. הוסיפו חדשה.
• גבו קודם. .backup מהיר ב-CLI או העתקת הקובץ כשמסד הנתונים סגור הם ביטוח זול לפני כל מיגרציה לא טריוויאלית.
• היזהרו עם PRAGMA foreign_keys. כבו אותו בזמן בנייה מחדש של טבלאות, והדליקו אותו שוב אחר כך.

בפרויקטים גדולים יותר, השתמשו בכלי ייעודי: Alembic עם SQLAlchemy, golang-migrate, Knex, Flyway. הם מטפלים בסדר, בהרצות מקבילות ובמוסכמות צוות שאחרת הייתם ממציאים מחדש. העקרונות זהים ללולאה שלמעלה; הכלי רק חוסך את קוד השלד.

הצעד הבא: מצב WAL ומקביליות

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