Pliki SQLite wyjaśnione: struktura, zastosowania i kluczowe limity

Plik SQLite to pojedynczy plik na dysku, który mieści w sobie całą relacyjną bazę danych — tabele, indeksy oraz schemat. Własna dokumentacja SQLite nazywa go „głównym plikiem bazy danych” i zauważa, że „zazwyczaj zawiera on pełny stan bazy danych SQLite”. Słowo „zazwyczaj” odgrywa w tym zdaniu kluczową rolę.

Jeśli kiedykolwiek otrzymałeś plik .db, .sqlite lub .sqlite3 i zastanawiałeś się, czy dostałeś wszystkie dane, warto dobrze zrozumieć ten format.

Czym właściwie jest plik SQLite

SQLite opisuje się jako „biblioteka działająca w ramach procesu, która implementuje samodzielny, bezserwerowy, niewymagający konfiguracji, transakcyjny silnik bazodanowy SQL”. Ta sama strona stwierdza, że SQLite „nie posiada oddzielnego procesu serwera”. Aplikacja konsoliduje bibliotekę i odczytuje plik.

Wynikają z tego dwie konsekwencje. Po pierwsze, baza danych przemieszcza się jako jeden artefakt, dlatego tak wiele aplikacji dostarcza swoje dane w ten sposób. Po drugie, format musi być niezwykle stabilny, ponieważ pliki te żyją dłużej niż oprogramowanie, które je zapisało. SQLite wymienia „stabilny, trwały format plików” wśród swoich głównych zalet. Stwierdza również, że kod należy do domeny publicznej i jest „wolny do użytku w dowolnym celu, komercyjnym lub prywatnym”.

Łatwo niedocenić skali tego rozwiązania. Strona informacyjna SQLite podaje, że „jest to najczęściej wdrażana baza danych na świecie, z liczbą aplikacji przekraczającą możliwości policzenia”.

Co znajduje się wewnątrz pliku

100-bajtowy nagłówek

Pierwsze bajty identyfikują format. Pod offsetem 0 plik zawiera 16-bajtowy ciąg nagłówka: SQLite format 3\000. Ta sygnatura pozwala narzędziom rozpoznać plik niezależnie od jego rozszerzenia.

Kolejne pole ma większe znaczenie, niż mogłoby się wydawać. Pod offsetem 16 znajduje się 2-bajtowa liczba całkowita przechowująca „rozmiar strony bazy danych w bajtach”. Dokumentacja określa, że „musi to być potęga dwójki z przedziału od 512 do 32768 włącznie lub wartość 1 reprezentująca rozmiar strony wynoszący 65536”. Wszystkie wielobajtowe pola w nagłówku są zapisywane począwszy od najbardziej znaczącego bajtu.

Kolejne dwa bajty znajdują się pod offsetami 18 i 19: wersja zapisu i wersja odczytu formatu pliku. Dokumentacja zaznacza, że wartość ta wynosi „1 dla wersji starszych (legacy); 2 dla WAL”.

Strony, nie wiersze

Poniżej nagłówka plik stanowi stos stron o stałym rozmiarze. Specyfikacja mówi jasno: „Główny plik bazy danych składa się z jednej lub więcej stron. Rozmiar strony jest potęgą dwójki z przedziału od 512 do 65536 włącznie. Wszystkie strony w obrębie tej samej bazy danych mają ten sam rozmiar”.

Strony są numerowane od 1, a maksymalny numer strony to 4,294,967,294. Tabele i indeksy znajdują się wewnątrz tych stron jako struktury B-tree, dlatego edytor tekstu pokaże Ci bardzo niewiele.

Pliki towarzyszące, o których nikt nie wspomina

To jest część, która często zaskakuje użytkowników. Dokumentacja podaje, że pełny stan znajduje się „zazwyczaj” w jednym pliku. Następnie wskazuje wyjątek. Podczas transakcji SQLite „przechowuje dodatkowe informacje w drugim pliku zwanym 'rollback journal'”. W trybie WAL tym drugim plikiem jest dziennik zapisu z wyprzedzeniem (write-ahead log).

Z tego względu kopia wykonana w trakcie zapisu przez aplikację może nie zawierać zatwierdzonych danych, które wciąż znajdują się w pliku towarzyszącym. Jeśli współpracownik przesyła Ci tylko plik .db i nic więcej, a liczby wydają się nieco nieaktualne, jest to pierwsza rzecz, którą należy sprawdzić.

Jak otworzyć plik SQLite

Istnieją trzy drogi, a wybór właściwej zależy od tego, co zamierzasz zrobić dalej.

Odczytaj go za pomocą przeglądarki. Desktopowe i przeglądarkowe przeglądarki SQLite otwierają plik, wyświetlają listę tabel i pozwalają na przeglądanie wierszy. To najszybszy sposób, aby odpowiedzieć na pytanie „co w ogóle tu jest” i zazwyczaj wystarcza na pierwszy rzut oka.

Prześlij zapytanie za pomocą wiersza poleceń lub biblioteki. Powłoka sqlite3 oraz powiązania biblioteki standardowej w językach Python, Node i większości innych odczytują ten format bezpośrednio. To właściwa droga, gdy znasz już schemat i potrzebujesz konkretnej liczby.

Wyeksportuj tabelę i przeanalizuj ją gdzie indziej. Zrzuć tabelę do formatu CSV i przenieś ją do dowolnego narzędzia, z którego korzysta już Twój zespół. Tracisz wtedy relacje między tabelami, czyli dokładnie to, co ten format chronił. Jeśli to możliwe, eksportuj połączone wyniki zamiast surowych tabel.

Dlaczego narzędzie może twierdzić, że plik nie jest bazą danych

Specyfikacja wyjaśnia tę kwestię. Każdy prawidłowy plik zaczyna się od 16-bajtowego ciągu nagłówka: SQLite format 3\000. Program odczytujący, który otwiera plik i nie znajduje tej sygnatury pod offsetem 0, nie otrzymał bazy danych SQLite.

Większość przypadków sprowadza się do trzech zwykłych przyczyn. Plik został przesłany niekompletnie, więc nagłówek istnieje, ale reszta jest obcięta. Plik jest zaszyfrowany lub opakowany przez aplikację, więc pierwsze bajty są czymś innym. Albo rozszerzenie wprowadza w błąd, a to, co faktycznie otrzymałeś, to zwykły eksport o zmienionej nazwie przez kogoś, kto chciał pomóc.

Jak duży może być plik SQLite

Większy, niż zazwyczaj sugeruje to pytanie. Strona z ograniczeniami SQLite podaje, że maksymalny rozmiar pliku bazy danych to 4,294,967,294 stron. Przy maksymalnym rozmiarze strony wynoszącym 65,536 bajtów daje to maksymalny rozmiar bazy danych wynoszący około 281 terabajtów.

Strona jest odświeżająco szczera w kwestii tej liczby. Zauważa, że górna granica „nie została przetestowana, ponieważ programiści nie mają dostępu do sprzętu zdolnego do osiągnięcia tego limitu”.

Liczba wierszy napotyka tę samą barierę. Teoretyczne maksimum to 2^64 wierszy w tabeli. Dokumentacja wskazuje, że limit ten „jest nieosiągalny, ponieważ wcześniej zostanie osiągnięty maksymalny rozmiar bazy danych wynoszący 281 terabajtów”.

W praktycznej pracy przydatny wniosek jest odwrotnością limitu. Jeśli ktoś przekazuje Ci plik .db i ostrzega, że jest duży, sam format prawie na pewno nie będzie przeszkodą. Rozmiar strony wybrany podczas tworzenia pliku oraz to, czy zawiera on indeksy, wpłyną na komfort pracy znacznie bardziej niż jakikolwiek udokumentowany limit.

Gdzie można spotkać pliki SQLite

• Eksporty z aplikacji. Aplikacje desktopowe i mobilne często przechowują historię, ustawienia i logi wiadomości w pliku SQLite, który można skopiować.
• Przekazywanie danych analitycznych. Inżynierowie przesyłają migawkę jako jeden plik, zamiast przyznawać dostęp do bazy danych.
• Urządzenia i telemetria. Systemy wbudowane zapisują dane lokalnie, ponieważ nie ma serwera, z którym mogłyby się komunikować.
• Archiwa. Długoterminowa stabilność formatu sprawia, że jest on częstym wyborem dla zbiorów danych, które muszą pozostać czytelne przez lata.
• Wnętrze przeglądarek i narzędzi. Wiele lokalnych narzędzi przechowuje stan w ten sposób, dlatego to rozszerzenie pojawia się w zgłoszeniach pomocy technicznej.

Do czego służą pliki WAL i dziennika (journal)

Mogłeś skopiować plik .db i znaleźć obok niego plik z rozszerzeniem -wal lub -journal. Są to pliki towarzyszące opisywane w specyfikacji, a ich usunięcie to najczęstszy sposób, w jaki ludzie tracą dane.

Dziennik wycofywania zmian (rollback journal) to starszy mechanizm. Przed modyfikacją strony SQLite zapisuje jej oryginalną wersję w dzienniku. Jeśli zapis zostanie przerwany, oryginał można przywrócić, co pozwala transakcji przetrwać awarię.

Dziennik zapisu z wyprzedzeniem (write-ahead log) odwraca ten układ. Zmiany trafiają najpierw do dziennika, a plik główny jest aktualizowany później. Nagłówek wskazuje, w jakim trybie znajduje się baza danych. Wersja zapisu formatu pliku pod offsetem 18 to „1 dla wersji starszych (legacy); 2 dla WAL”.

Praktyczna zasada wynika bezpośrednio ze zdania o pełnym stanie. Załóżmy, że baza danych działa w trybie WAL, a ktoś przekazuje Ci tylko główny plik. Najnowsze zatwierdzone zmiany mogą wciąż znajdować się w dzienniku, którego nie otrzymałeś.

Kiedy więc otrzymasz plik bazy danych, zadaj dwa pytania. Czy aplikacja została prawidłowo zamknięta w momencie wykonywania kopii i czy dołączono do niej coś jeszcze? Obie odpowiedzi zazwyczaj brzmią „tak”, a ten jeden raz, kiedy tak nie jest, to moment, w którym liczby po cichu przestają zgadzać się ze środowiskiem produkcyjnym.

Plik SQLite vs CSV vs Parquet

	Plik SQLite	CSV	Parquet
Struktura	Wiele tabel, jeden plik	Jedna tabela, jeden plik	Jedna tabela, jeden plik lub folder
Typy danych	Przechowywane wraz z danymi	Wnioskowane przez program odczytujący	Przechowywane wraz z danymi
Relacje	Zachowane, za pomocą kluczy i indeksów	Utracone	Utracone
Czytelny dla człowieka	Nie	Tak	Nie
Stworzony do zapytań	Tak, za pomocą SQL	Nie	Tak, przez silniki analityczne
Typowy problem	Brakujący plik towarzyszący journal lub WAL	Zgadywanie typów i ograniczników	Wsparcie narzędziowe

If you work with these formats regularly, our explainers on plikach Parquet and plikach TSV cover the same ground for those two.

Dlaczego zespoły wybierają ten format

Brak konieczności uruchamiania czegokolwiek. Ponieważ SQLite „nie posiada oddzielnego procesu serwera”, przekazanie danych to po prostu skopiowanie pliku, a nie zgłoszenie o przydzielenie zasobów.

Typy danych przetrwają podróż. Kolumna z datą dociera jako data. Każdy, kto widział, jak czytnik CSV zamienia identyfikator w notację naukową, rozumie wartość tego rozwiązania.

Relacje również zostają zachowane. Kilka powiązanych tabel pozostaje razem w jednym artefakcie, dzięki czemu złączenia, które nadawały danym sens, są nadal dostępne.

Trwałość jest wpisana w projekt. SQLite wymienia transakcje „nawet po utracie zasilania” wśród swoich kluczowych funkcji, dlatego tak wiele oprogramowania wbudowanego na nim polega.

Limity, które warto znać

Jeden plik, tylko jeden zapisujący jednocześnie. Silnik jest wbudowany, a nie serwowany, więc model współbieżności różni się od bazy danych klient-serwer. To decyzja projektowa, a nie wada, ale kształtuje ona to, do czego ten plik się nadaje.

Rozmiar strony jest ustalany przy tworzeniu. Każda strona w bazie danych ma ten sam rozmiar, a rozmiar ten jest zapisany w nagłówku. Wybierasz go tylko raz.

Ponownie zasada pliku towarzyszącego. Każda procedura kopiowania, tworzenia kopii zapasowej lub przesyłania, która pobiera tylko plik główny, może pominąć to, co znajdowało się w dzienniku (journal) lub dzienniku zapisu z wyprzedzeniem (write-ahead log).

Brak przejrzystości. Pliku SQLite nie da się szybko przejrzeć tak jak pliku CSV. Odczytanie go wymaga narzędzia, co stanowi dokładnie tę barierę, która często spowalnia analizę.

Jak uzyskać odpowiedzi z pliku SQLite

Tradycyjna droga to instalacja klienta, otwarcie pliku, poznanie schematu i rozpoczęcie pisania zapytań SQL. To dobre rozwiązanie, gdy znasz już tabele. Jest jednak powolne, gdy plik otrzymałeś dziś rano, a spotkanie jest po południu.

Krótsza droga to bezpośrednie zadanie pytania. Powerdrill Bloom pozwala pracować z danymi przy użyciu języka naturalnego i zwraca odpowiedź wraz z dołączonym źródłem. Strona główna obiecuje, że „każda liczba wraca wraz ze stroną, wierszem i wartością, która za nią stoi”. Z poziomu tego samego obszaru roboczego można następnie wygenerować wykresy, arkusze lub krótką prezentację.

Warto znać dwie powiązane strony, jeśli jest to Twój stały proces pracy. Chat with Database opisuje konwersacyjną drogę do ustrukturyzowanych danych, a Text to SQL dotyczy przypadku, gdy potrzebujesz samego zapytania. Jeśli natomiast przekazane dane dotarły jako płaski eksport, ścieżkę tę opisuje strona CSV AI assistant.

Jeszcze jedna rzecz, którą mówi nagłówek

Ponieważ rozmiar strony znajduje się pod stałym offsetem, możesz dowiedzieć się czegoś przydatnego o pliku jeszcze przed jego właściwym otwarciem. Baza danych utworzona ze stroną o rozmiarze 4,096 bajtów zachowuje się inaczej niż ta utworzona ze stronami o rozmiarze 65,536 bajtów. Wybór ten został dokonany raz, podczas tworzenia pliku.

Nie jest to liczba, którą można później swobodnie zmienić. Należy ją traktować raczej jako decyzję projektową dotyczącą schematu, a nie zwykłe ustawienie.

Podsumowanie

Plik SQLite to cała baza danych w jednym artefakcie. Zawiera 16-bajtową sygnaturę, rozmiar strony zapisany pod offsetem 16 oraz stos stron o stałym rozmiarze, na których znajdują się Twoje tabele i indeksy. Łatwo go przenosić, zachowuje swoje typy danych i pozostaje czytelny przez lata.

Pamiętaj o jednym zastrzeżeniu, na które zwraca uwagę specyfikacja. Pełny stan znajduje się zazwyczaj w tym pliku. Podczas transakcji część z niego żyje w dzienniku wycofywania zmian (rollback journal) lub dzienniku zapisu z wyprzedzeniem (write-ahead log) obok niego. Przed zaufaniem kopii sprawdź, czy istnieje plik towarzyszący.

Gdy masz plik i potrzebujesz odpowiedzi, a nie schematu, wypróbuj Powerdrill Bloom i zadaj pytanie bezpośrednio do swoich danych.

Najczęściej zadawane pytania

Jaka jest różnica między .db, .sqlite i .sqlite3?

Brak różnic strukturalnych. Wszystkie trzy to zwyczajowe rozszerzenia dla tego samego formatu, a rzeczywistym identyfikatorem jest 16-bajtowy ciąg nagłówka SQLite format 3\000 na początku pliku.

Skąd mam wiedzieć, jakiego rozmiaru strony używa plik SQLite?

Jest on zapisany w nagłówku. 2-bajtowa liczba całkowita pod offsetem 16 przechowuje rozmiar strony w bajtach. Musi to być potęga dwójki z przedziału od 512 do 32768 lub wartość 1 reprezentująca 65536.

Czy plik SQLite to kompletna baza danych?

Zazwyczaj tak, ale nie zawsze. Dokumentacja stwierdza, że podczas transakcji SQLite przechowuje dodatkowe informacje w dzienniku wycofywania zmian (rollback journal). W trybie WAL informacje te trafiają zamiast tego do dziennika zapisu z wyprzedzeniem (write-ahead log).

Czy mogę otworzyć plik SQLite w programie Excel?

Nie bezpośrednio, ponieważ plik przechowuje strony B-tree, a nie wiersze tekstu. Typowym rozwiązaniem jest najpierw wyeksportowanie tabeli do formatu CSV lub użycie narzędzia, które odczytuje format bazy danych i zwraca wyniki.

Czy SQLite jest darmowy do użytku komercyjnego?

Tak. SQLite stwierdza, że jego kod należy do domeny publicznej i jest „wolny do użytku w dowolnym celu, komercyjnym lub prywatnym”.