Baza danych w pamięci w SQLite: szybkie, jednorazowe bazy z :memory:

Baza danych, która żyje w RAM

SQLite ma specjalną nazwę pliku: :memory:. Otwórz bazę pod tą nazwą, a SQLite całkowicie pominie dysk: cała baza istnieje w pamięci RAM. Tabele, indeksy, transakcje, klucze obce, każda funkcja działa dokładnie tak samo. Jedyna różnica jest taka, że po zamknięciu połączenia bazy już nie ma.

Z wiersza poleceń:

sqlite3 :memory:

Jesteś teraz w wierszu poleceń SQLite ze świeżą, pustą bazą, która istnieje tylko w pamięci. Utwórz tabelę, wstaw kilka wierszy, wykonaj zapytanie, wszystko normalnie:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('pierwsza notatka'), ('druga notatka');
SELECT * FROM notes;

Zakończ sesję, a dane wyparują. Nie zostaje żaden plik, bo żaden nigdy nie powstał.

Po co ci taka baza

Baza, która nie przetrwa restartu, brzmi jak błąd, a nie funkcja. W rzeczywistości przydaje się w trzech sytuacjach.

Testy. Każdy test dostaje czystą bazę w kilka milisekund. Bez sprzątania plików tymczasowych, bez stanu pozostawionego przez poprzednie uruchomienie, bez blokowania wspólnego pliku z danymi testowymi. Większość zestawów testów w Pythonie, Node i Go, które korzystają z SQLite, otwiera :memory: właśnie z tego powodu.

Jednorazowa analiza. Wczytaj CSV, wykonaj kilka zapytań, wyrzuć. Szybciej niż stawianie prawdziwej bazy i prościej niż za każdym razem parsować plik w kodzie.

Cache i przestrzeń robocza. W długo działającym programie baza SQLite w pamięci to zaskakująco dobry silnik doraźnych zapytań dla danych, które już wczytano.

Wspólny mianownik: chcesz SQL, ale nie chcesz trwałości.

Wydajność: szybciej, ale bez cudów

Bazy w pamięci omijają dysk, więc zapisy, które normalnie trafiałyby do systemu plików, są tylko aktualizacjami pamięci. Obciążenia ograniczone przez operacje wejścia/wyjścia wyraźnie przyspieszają. Obciążenia ograniczone przez procesor (złożone planowanie zapytań, duże sortowania) prawie się nie zmieniają, bo SQLite i tak trzymał często używane strony w pamięci.

Krótka demonstracja, że składnia jest identyczna:

CREATE TABLE numbers (n INTEGER);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1000
)
INSERT INTO numbers SELECT n FROM seq;

SELECT COUNT(*), SUM(n), AVG(n) FROM numbers;

To zostało wykonane na bazie w pamięci, ale to ten sam SQL, który uruchomiłbyś na pliku. Silnikowi bazy jest to obojętne.

W pamięci czy w pliku: kiedy co wybrać

Kompromis jest prosty i warto go nazwać wprost:

• Baza w pliku (mydata.db): przetrwa restarty. Może ją otworzyć wiele procesów. Przeżywa awarie (w trybie WAL, w większości przypadków). Używaj jej do wszystkiego, co musi coś zapamiętać.
• Baza w pamięci (:memory:): znika po zamknięciu. Domyślnie prywatna dla połączenia, które ją otworzyło. Szybsza przy jednorazowej pracy z dużą liczbą zapisów. Używaj jej do testów, pracy roboczej i krótkotrwałych cache.

Jeśli nie masz pewności, potrzebujesz pliku. Pamięć to przypadek szczególny.

Każde połączenie dostaje własną bazę

Subtelna rzecz, na którą ludzie się łapią: dwukrotne otwarcie :memory: daje dwie osobne bazy. Nie współdzielą tabel ani danych, w ogóle się nie widzą.

-- Terminal 1
sqlite3 :memory:
sqlite> CREATE TABLE t (x); INSERT INTO t VALUES (1);

-- Terminal 2
sqlite3 :memory:
sqlite> SELECT * FROM t;
Error: no such table: t

To nie błąd, tylko zamierzony projekt. :memory: oznacza „prywatną bazę dla tego połączenia”. To samo dotyczy pojedynczego programu: jeśli kod otwiera dwa połączenia do :memory:, każde dostaje własną, odizolowaną bazę.

Współdzielenie bazy w pamięci między połączeniami

Jeśli naprawdę potrzebujesz, żeby kilka połączeń widziało tę samą bazę w pamięci, SQLite to obsługuje przez nazwy plików w formacie URI i współdzieloną pamięć podręczną. Magiczny ciąg to file::memory:?cache=shared:

sqlite3 'file::memory:?cache=shared'

Każde połączenie w tym samym procesie, które otworzy dokładnie ten URI, dołącza do tej samej bazy. Zamknij je wszystkie, a baza zniknie.

Możesz też nadać bazie w pamięci nazwę, co pomaga, gdy chcesz mieć kilka różnych współdzielonych baz:

sqlite3 'file:mydb?mode=memory&cache=shared'

Nazwa mydb to tylko etykieta: pliku nadal nie ma. Dwa połączenia, które otwierają file:mydb?mode=memory&cache=shared, współdzielą jedną bazę, a połączenie, które otwiera file:other?mode=memory&cache=shared, dostaje inną.

Zapis bazy z pamięci na dysk

Czasem wykonujesz cały proces w pamięci, a potem decydujesz, że chcesz zachować wynik. CLI ma do tego polecenie z kropką .backup:

sqlite3 :memory:
sqlite> CREATE TABLE results (id INTEGER, score REAL);
sqlite> INSERT INTO results VALUES (1, 0.91), (2, 0.87);
sqlite> .backup snapshot.db
sqlite> .quit

snapshot.db to teraz zwykła baza w pliku o tej samej zawartości. Możesz ją później otworzyć przez sqlite3 snapshot.db i kontynuować pracę.

Działa to też w drugą stronę: .restore wczytuje bazę z pliku do pamięci bieżącego połączenia:

sqlite3 :memory:
sqlite> .restore snapshot.db
sqlite> SELECT * FROM results;

W kodzie aplikacji C API SQLite udostępnia ten sam mechanizm jako sqlite3_backup_init, a większość bibliotek dla różnych języków go opakowuje. Na przykład moduł sqlite3 w Pythonie ma Connection.backup().

Częsta pułapka

Ludzie czasem próbują „zapisać” bazę z pamięci, dołączając plik i kopiując dane:

ATTACH DATABASE 'output.db' AS disk;
CREATE TABLE disk.notes AS SELECT * FROM main.notes;
DETACH DATABASE disk;

Przy prostym kopiowaniu tabel to działa, ale nie zachowuje dokładnie indeksów, wyzwalaczy, widoków ani kluczy obcych. Do wiernej kopii całej bazy użyj .backup (albo API kopii zapasowych): wykonuje dokładną binarnie kopię na poziomie stron.

Co warto zapamiętać

• :memory: to specjalna nazwa pliku w SQLite, która tworzy bazę w RAM bez żadnego pliku.
• SQL jest identyczny jak w bazie plikowej: te same tabele, te same zapytania, te same ograniczenia.
• Każde połączenie do :memory: jest prywatne. Gdy kilka połączeń ma współdzielić jedną bazę, użyj URI ze współdzieloną pamięcią podręczną (file::memory:?cache=shared).
• To właściwe narzędzie do testów, jednorazowej analizy i krótkotrwałych cache, ale nie do niczego, co musi przetrwać restart.
• Gdy zdecydujesz, że chcesz zachować bazę z pamięci, przenieś ją na dysk przez .backup.

Dalej: tworzenie tabel

CREATE TABLE pojawiło się już w kilku przykładach. Następna strona zwalnia i omawia je porządnie: definicje kolumn, typy, ograniczenia, które możesz dodać, i drobne decyzje, dzięki którym ze schematem przyjemnie się pracuje.