Migracje w SQLite: wersjonowanie schematu przez user_version

Schematy się zmieniają. Zaplanuj to.

Pierwsza wersja schematu nigdy nie jest ostatnia. Dochodzą kolumny, tabele się dzielą, indeksy są przemyślane na nowo. Pytanie nie brzmi, czy schemat się zmieni, tylko czy zmiana gładko trafi na każdy laptop, serwer i urządzenie użytkownika, które mają już starszą kopię bazy.

Do tego służą migracje: ciąg małych, uporządkowanych skryptów, które przenoszą bazę z wersji N do wersji N+1. Uruchamiaj je po kolei, a każda baza dogoni aktualny stan. Zrezygnuj z tej dyscypliny, a skończysz z błędami typu „u mnie działa”, których szukanie zajmuje całe popołudnie.

SQLite daje do tego dokładnie jedno wbudowane narzędzie: PRAGMA user_version. To 32-bitowa liczba całkowita, którą baza przechowuje za ciebie i której sam SQLite nie rusza. To ty decydujesz, co oznacza.

PRAGMA user_version;
PRAGMA user_version = 1;
PRAGMA user_version;

Świeża baza zaczyna od 0. Ustaw wartość na numer migracji, którą właśnie zastosowano. Odczytuj ją przy starcie, żeby wiedzieć, w jakim miejscu jesteś.

Minimalna pętla migracji

Model myślowy: każda migracja to numerowany skrypt SQL. Aplikacja odczytuje bieżące user_version, uruchamia po kolei każdy skrypt z wyższym numerem i po każdym aktualizuje user_version.

Oto migracja 1, która tworzy początkowy schemat:

BEGIN;

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

PRAGMA user_version = 1;

COMMIT;

Warto zauważyć dwie rzeczy. Całość jest opakowana w BEGIN; ... COMMIT;, więc jest atomowa: jeśli CREATE TABLE się nie powiedzie, user_version nie zostanie podbite i możesz poprawić błąd i spróbować ponownie. A PRAGMA user_version = 1 to ostatnia instrukcja przed zatwierdzeniem, więc wersja zmienia się tylko wtedy, gdy wszystko inne się udało.

Załóżmy teraz, że trzeba dodać kolumnę created_at. To migracja 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';

Baza w wersji 0 uruchamia obie. Baza w wersji 1 uruchamia tylko drugą. Baza w wersji 2 nie uruchamia niczego. Kolejność to umowa.

Co ALTER TABLE potrafi, a czego nie

ALTER TABLE w SQLite jest celowo wąskie. Obsługuje:

• ADD COLUMN: dodanie nowej kolumny na końcu, z opcjonalną wartością domyślną.
• DROP COLUMN: usunięcie kolumny (od wersji 3.35).
• RENAME COLUMN: zmianę nazwy kolumny (od wersji 3.25).
• RENAME TO: zmianę nazwy samej tabeli.

To wszystko. Nie zmienisz typu kolumny, NOT NULL, ograniczenia CHECK ani nie dodasz FOREIGN KEY do istniejącej kolumny.

-- Nieobsługiwane:
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');

Gdy potrzebujesz zmiany, której SQLite nie wykona bezpośrednio, oficjalny przepis to „przebuduj tabelę”. Jest bardziej rozwlekły, ale całkowicie niezawodny.

Przebudowa tabeli przy większych zmianach

Schemat jest taki: utwórz nową tabelę o pożądanym kształcie, skopiuj dane, usuń starą i zmień nazwę nowej, żeby zajęła jej miejsce. Wszystko w jednej transakcji.

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');

-- Teraz chcemy ograniczenia CHECK na email. Przebudowa:
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;

Pełna dokumentacja SQLite nazywa to przepisem w 12 krokach i dodaje kilka ostrzeżeń dotyczących wyzwalaczy, widoków i odwołań kluczy obcych. Warto ją raz przeczytać, zanim zrobisz to na schemacie produkcyjnym. W większości przypadków wystarczy powyższa wersja w czterech krokach.

Uwaga: jeśli na przebudowywaną tabelę wskazują klucze obce, uruchom PRAGMA foreign_keys = OFF przed migracją i PRAGMA foreign_keys = ON po niej. W przeciwnym razie DROP TABLE może w trakcie złamać integralność referencyjną.

Sterowanie migracjami z aplikacji

Ta księgowość jest na tyle prosta, że możesz ją napisać sam. W Pythonie z biblioteką standardową:

-- W uproszczeniu aplikacja robi przy starcie to:
--
--   current = SELECT user_version FROM PRAGMA;
--   for i in range(current + 1, len(MIGRATIONS) + 1):
--       uruchom MIGRATIONS[i] w transakcji
--       PRAGMA user_version = i;
--
-- SQL każdego kroku wygląda jak migracje powyżej.

PRAGMA user_version;

Kluczowe niezmienniki:

• Migracje są numerowane kolejno od 1. Bez luk i bez zmiany kolejności.
• Każda migracja jest opakowana w transakcję razem z podbiciem PRAGMA user_version = N.
• Gdy migracja zostanie zatwierdzona i wydana, nigdy jej nie edytujesz. Nowe zmiany trafiają do nowej migracji.

Tę ostatnią zasadę zespoły łamią najczęściej. Jeśli zmienisz migrację 3 po tym, jak baza współpracownika już ją zastosowała, jego baza na zawsze i po cichu rozjedzie się z twoją.

Zapisywanie historii zmian

user_version mówi, gdzie jest baza. Nie mówi, kiedy uruchomiono każdy krok ani co zrobił. Naprawia to mała tabela ewidencyjna:

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;

Masz teraz wiersz na każdą migrację, z nazwą i znacznikiem czasu. Przydaje się przy debugowaniu w stylu „dlaczego ta baza ma kolumnę, której kod się nie spodziewa?”.

PRAGMA user_version pozostaje źródłem prawdy dla pętli, a tabela jest dla ludzi.

Wycofywanie: co dają transakcje, a czego nie

DDL w SQLite jest transakcyjny. Jeśli migracja 5 zaczyna tworzyć tabelę, kopiować dane i podbijać user_version, a kopiowanie w połowie się nie uda, ROLLBACK cofa wszystko, łącznie z CREATE TABLE. Baza jest dokładnie taka jak przed BEGIN.

BEGIN;

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

-- Załóżmy, że tu coś poszło nie tak:
ROLLBACK;

-- Tabela nie istnieje:
SELECT name FROM sqlite_master WHERE type='table' AND name='accounts';

To obejmuje migracje nieudane. Nie obejmuje migracji, które zatwierdzono z powodzeniem, a których teraz żałujesz. Do nich piszesz osobną migrację w dół, czyli skrypt, który cofa zmianę. SQLite nie ma automatycznego odwrócenia. Jeśli migracja 7 dodała kolumnę, wersja w dół ją usuwa. Jeśli migracja 7 usunęła kolumnę, wersja w dół nie odzyska danych: najwyżej odtworzy pustą kolumnę.

W praktyce wiele małych projektów całkiem pomija migracje w dół i do „cofania” polega na kopiach zapasowych. To rozsądny wybór, o ile kopie faktycznie się robi.

Kilka nawyków, które oszczędzą kłopotów

• Jedna migracja na jedną logiczną zmianę. Migrację, która dodaje trzy niezwiązane kolumny, trudniej przejrzeć i trudniej cofnąć niż trzy osobne migracje.
• Testuj migracje na kopii produkcji. Zmiany schematu bywają wolne na dużych tabelach, a przekonywanie się o tym na produkcji nie jest przyjemne.
• Nigdy nie edytuj wydanej migracji. Dodaj nową.
• Najpierw kopia zapasowa. Szybkie .backup w CLI albo skopiowanie pliku przy zamkniętej bazie to tanie ubezpieczenie przed każdą nietrywialną migracją.
• Uważaj na PRAGMA foreign_keys. Wyłącz je na czas przebudowy tabel i włącz z powrotem po niej.

W większych projektach sięgnij po dedykowane narzędzie: Alembic z SQLAlchemy, golang-migrate, Knex, Flyway. Obsługują kolejność, równoległe uruchomienia i konwencje zespołowe, które inaczej trzeba by wymyślać od nowa. Zasady są te same co w pętli powyżej, narzędzie po prostu usuwa powtarzalny kod.

Dalej: tryb WAL i współbieżność

Migracje zwykle działają, gdy aplikacja jest wyłączona albo trzyma blokadę na wyłączność. Przez resztę czasu baza obsługuje odczyty i zapisy z wielu połączeń naraz, a domyślny tryb dziennika w SQLite nie zawsze pasuje do tego najlepiej. Następna strona omawia tryb WAL, co zmienia i kiedy się na niego przełączyć.