Slovník pojmů

SQLite

SQLite je plnohodnotný embedded relační databázový engine pro lokální a jednoduše provozované datové úlohy. Jeho souborový model snižuje složitost, zároveň má jiný model souběhu než samostatný databázový server.

Stručná definice

Relační databáze jako součást aplikace.

SQLite je knihovna, kterou aplikace používá přímo v procesu. Na rozdíl od MySQL nebo PostgreSQL se obvykle nespouští samostatná serverová služba, neotevírá se pro ni databázový port a klient neposílá vzdálené SQL požadavky. Stav databáze leží v souboru, s nímž engine bezpečně pracuje pomocí transakcí, žurnálu a souborových zámků.

To neznamená, že jde jen o testovací databázi nebo obyčejný datový soubor bez pravidel. SQLite podporuje SQL, atomické commit a rollback, indexy, UNIQUE, CHECK, NOT NULL i cizí klíče. Vhodnost ale závisí na tom, jak často aplikace zapisuje, kolik nezávislých procesů ke stejnému souboru přistupuje a kde soubor fyzicky leží.

Jaký problém řeší

Trvalá relační data bez správy samostatného serveru.

SQLite snižuje provozní počet částí systému, když databázi přirozeně vlastní jedna aplikace nebo jedno zařízení.

mobilní, desktopové a lokální aplikace s offline daty
interní nástroje, malé webové projekty a aplikačně serializované zápisy
lokální analytika nad importem CSV či exportem provozních dat
přenosné databázové soubory pro report nebo předání konkrétní datové sady
testy, v nichž se zároveň vědomě hlídají rozdíly oproti produkční databázi

Praktický příklad

Lokální analytický nástroj nad importovanými soubory.

Interní CLI nástroj stáhne denní export objednávek a uloží ho do lokální SQLite databáze. Unikátní název zdrojového souboru zabraňuje dvojímu importu, cizí klíč drží metriky u konkrétního běhu importu a index urychluje přehled podle času. Databázový soubor lze zálohovat nebo předat spolu s nástrojem, pokud je přístup správně řízený.

Při otevření každého připojení aplikace explicitně zapne foreign keys. V režimu WAL mohou čtenáři a jedna zapisující transakce postupovat souběžně, ale dlouhý zápis nebo dlouhé čtení stále ovlivní provoz. Nástroj proto dávkuje import, drží transakce krátké a nepředpokládá, že stejný soubor bez omezení obslouží mnoho paralelních zapisovačů.

SQL (SQLite)

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE import_run (
  id INTEGER PRIMARY KEY,
  source_file TEXT NOT NULL UNIQUE,
  imported_at TEXT NOT NULL
);

CREATE TABLE daily_metric (
  id INTEGER PRIMARY KEY,
  import_run_id INTEGER NOT NULL,
  metric_day TEXT NOT NULL,
  order_count INTEGER NOT NULL CHECK (order_count >= 0),
  FOREIGN KEY (import_run_id)
    REFERENCES import_run (id) ON DELETE CASCADE,
  UNIQUE (import_run_id, metric_day)
);

CREATE INDEX daily_metric_day_idx ON daily_metric (metric_day);

Jak funguje

Soubor, transakce a koordinace přístupu.

SQLite ukládá stav do databázového souboru, ale konzistenci nebere na lehkou váhu: změny řídí transakcemi a souborovými zámky.

Otevření databáze Aplikace otevře soubor přímo pomocí SQLite knihovny. Databáze není síťový server; zabezpečení proto zahrnuje zejména oprávnění k souboru, adresáři, záloze a procesu, který soubor používá.
Schéma a pravidla Tabulky, indexy, PRIMARY KEY, UNIQUE, CHECK a NOT NULL definují povolený stav. FOREIGN KEY je podporován, ale aplikace ho má na každém připojení explicitně povolit přes PRAGMA foreign_keys = ON.
Transakce SQL příkazy se provádějí atomicky nebo v explicitní transakci. Commit zapisuje konzistentní změnu, rollback ji zahodí; žurnálovací mechanismus umožní zotavení i po přerušení zápisu.
Zámky a jeden zapisovač Ve stejné databázi může být v daném okamžiku jen jedna zapisující transakce. To může být velmi praktické pro krátké dávkové zápisy, ale je to důležitá hranice při vysokém souběhu.
WAL a checkpoint Režim write-ahead log zapisuje nejprve do souboru WAL. Umožní souběh čtenářů s jednou zapisující transakcí, ale potřebuje checkpointing, nemá fungovat přes síťový filesystem a dlouhé čtení může bránit postupu checkpointu.

Hlavní části a principy

Embedded provoz neznamená omezený datový model.

Nejdůležitější rozdíl oproti databázovému serveru není kvalita SQL, ale umístění enginu, model připojení a koordinace souběhu nad jedním souborem.

Embedded engine a databázový soubor

SQLite je knihovna linkovaná nebo načtená aplikací. Typická databáze je jeden hlavní soubor, nikoli vzdálená služba s účty a síťovými klienty. Souborový model usnadňuje distribuci a zálohu, ale vyžaduje správně nastavená práva a opatrnost při sdílení souboru mezi procesy či stroji.

ACID a transakce

SQLite poskytuje atomické commit a rollback a chrání integritu databáze i při výpadku. ACID neznamená, že lze bez návrhu bezpečně sdílet každý soubor na libovolném úložišti nebo že jedna transakce pokryje vzdálené API. Hranice transakce zůstává lokální databázová práce.

Constrainty a foreign keys

PRIMARY KEY, UNIQUE, CHECK a NOT NULL se vyjadřují přímo ve schématu. Foreign keys jsou podporované, ale pro zpětnou kompatibilitu nejsou automaticky vynucené: aplikace má po otevření každého připojení výslovně nastavit PRAGMA foreign_keys = ON a tento stav testovat.

Indexy a SQL dialekt

Index je stejně jako jinde pomocná struktura pro konkrétní dotazy a má cenu zápisu i místa. SQLite podporuje rozsáhlou část SQL, ne však identický dialekt ani stejné datové typy a chování jako MySQL nebo PostgreSQL. Test s SQLite proto není plnohodnotnou náhradou integračního testu nad produkční databází.

WAL, čtení a zápisy

WAL zlepšuje souběh tím, že čtenáři neblokují zapisovače a zapisovač neblokuje čtenáře. Neodstraňuje ale limit jednoho zapisovače. WAL vyžaduje procesy na stejném hostiteli, protože používá sdílenou paměť; dlouhý čtenář navíc může zdržet checkpoint a růst WAL souboru.

Vztah k podobným technologiím

SQLite není server ani zmenšená imitace jiné databáze.

Různé databáze mohou zpracovat podobné SQL úlohy, ale jejich provozní hranice a některé sémantické detaily se liší.

MySQL a PostgreSQL: Jsou samostatné klient–server databázové systémy, obvykle provozované jako sdílené síťové služby pro mnoho aplikačních procesů. SQLite je embedded engine v aplikaci; nejde jen o rozdíl v instalaci, ale i v modelu souběhu a správy přístupu.
Obyčejný datový soubor: SQLite databáze je soubor, ale ne neřízený export CSV nebo JSON. Engine nad ním poskytuje SQL, schéma, indexy, constrainty, transakční commit a mechanismy zotavení po přerušení zápisu.
Testovací databáze: SQLite je užitečná pro testy, ale její použití v testu může skrýt rozdíly v SQL dialektu, typech, izolaci, migracích nebo zamykání produkční databáze. Kritické dotazy a migrace je vhodné ověřit také proti produkčně používanému systému.
Lokální analytika: Databáze v souboru může být pohodlná pro opakované SQL dotazy nad exportem a přenos výsledné datové sady. Není nutné z ní dělat centrální server jen proto, že s ní pracuje více analytických skriptů; záleží na koordinaci přístupu.

Výhody a omezení

Jednoduchý provoz výměnou za vědomou práci se souběhem.

Přínosy

žádná samostatná databázová služba pro instalaci, aktualizaci a dohled
relační model, SQL, transakce, indexy a constrainty v jednom přenositelném souboru
vhodné pro lokální data, zařízení, analytiku a řadu nízko až středně zatížených aplikací
WAL dovoluje souběh čtenářů s jedním zapisovačem

Omezení a časté chyby

označit SQLite jen za hračku nebo databázi pro testy
zacházet s ní jako s klasickým síťovým databázovým serverem
předpokládat více souběžných zapisovačů bez návrhu a měření
zapomenout povolit foreign keys na každém připojení
použít SQLite testy jako jediný důkaz kompatibility s MySQL či PostgreSQL
provozovat WAL nad síťovým filesystemem

Praktické použití

Silná volba, když databáze patří k jedné aplikaci nebo zařízení.

SQLite dobře sedí do mobilní aplikace, desktopového nástroje, lokální cache, interní analytiky nebo menšího webu, kde jsou zápisy krátké a jejich koordinaci lze řídit. Oficiální dokumentace uvádí použití i pro nízko až středně navštěvované weby; rozhodující však není univerzální počet požadavků, ale konkrétní poměr čtení a zápisů, délka transakcí, architektura procesu a umístění souboru.

Pro centrální aplikaci s mnoha nezávislými zapisujícími workery, složitou správou rolí na databázovém serveru nebo distribuovaným provozem může být praktičtější klient–server systém. To není hodnotový soud o SQLite: jde o volbu modelu, který odpovídá skutečným hranicím nasazení a souběhu.

Na co myslet

Souběh a integritu ověřit v prostředí, kde aplikace skutečně běží.

SQLite je spolehlivá, když aplikace explicitně nastaví pravidla a nepřenáší na ni předpoklady z jiného databázového systému.

po otevření každého připojení explicitně zapnout a ověřit PRAGMA foreign_keys = ON
držet zapisovací transakce krátké a rozumně reagovat na SQLITE_BUSY
před zapnutím WAL ověřit umístění souboru a plán checkpointingu
navrhovat indexy podle skutečných dotazů, ne jen podle názvu sloupce
kritické migrace, SQL a chování testovat také nad produkčně používanou databází
chránit databázový soubor, související WAL soubory i zálohy správnými oprávněními

Časté otázky

SQLite v provozu a testech

Je SQLite pouze databáze pro testy?

Ne. Používá se v mobilních a desktopových aplikacích, lokálních nástrojích, analytice i řadě serverových aplikací. V testech je užitečná, ale nesmí zakrýt rozdíly oproti produkční databázi.

Je SQLite databázový server?

Ne. SQLite je embedded databázový engine používaný přímo aplikací a databáze obvykle leží v souboru. Aplikace s ní nekomunikuje přes samostatný síťový server jako s MySQL nebo PostgreSQL.

Umožní WAL mnoho současných zápisů?

Ne. WAL umožňuje souběh čtenářů s jedním zapisovačem, ale pro jeden databázový soubor existuje vždy jen jeden zapisovač. Krátké zápisy a kontrola čekání jsou proto důležité.

Proč explicitně zapínat foreign keys?

Jejich vynucení není bezpečné předpokládat jako výchozí stav. Aplikace je má nastavit přes PRAGMA foreign_keys = ON pro každé připojení, aby deklarované vztahy skutečně chránily data.

Jak pracuji s databázemi v praxi

Datové úložiště volím podle souběhu, provozu a skutečných pravidel.

V e-commerce a integračních aplikacích řeším model dat, transakce, constrainty, importy i vhodnou hranici mezi lokálním a centrálním úložištěm.

Jak pracuji s databázemi Nezávazně probrat projekt