Sinds versie 3.31.0 ondersteunt SQLite generated columns, een functie waarmee de database kan worden ingezet als een documentdatabase. Door json_extract te gebruiken in combinatie met GENERATED ALWAYS, kunnen specifieke gegevens uit JSON-strings worden geëxtraheerd en geïndexeerd.
Belangrijke technische aspecten zijn:
- Validatie: Het gebruik van gegenereerde kolommen dwingt automatisch de validatie van JSON af; ongeldige JSON leidt tot een fout bij invoeging. Door
NOT NULL toe te voegen, kunnen specifieke velden in de JSON-structuur verplicht worden gesteld.
- Virtueel vs. Opgeslagen: De auteur maakt onderscheid tussen
VIRTUAL kolommen (die on-the-fly worden berekend) en STORED kolommen (die worden gecached). Virtuele kolommen kunnen nog steeds worden voorzien van een index om de zoekprestaties te optimaliseren.
- Flexibiliteit: Dankzij
ALTER TABLE kan een gebruiker starten met een simpele JSON-kolom en later, naarmate de datastructuur duidelijker wordt, specifieke velden toevoegen en indexeren. Dit is bijzonder nuttig voor toepassingen zoals webhooks.
SQLite als documentdatabase
Hiermee is het mogelijk om JSON direct in SQLite in te voegen, waarna de database gegevens kan extraheren en indexeren. In feite kun je SQLite hiermee behandelen als een documentdatabase. Dit is al mogelijk met PostgreSQL en is uiteraard wat systemen zoals Elastic bieden, maar het is erg handig dat dit nu ook beschikbaar is in een embedded database voor lichtgewicht toepassingen.
Aan de slag
Laten we beginnen:
$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Connected to a transient in-memory database.
sqlite> CREATE TABLE t (
body TEXT,
d INT GENERATED ALWAYS AS (json_extract(body, '$.d')) VIRTUAL);
sqlite> insert into t values(json('{"d":"42"}'));
sqlite> select * from t WHERE d = 42;
{"d":"42"}|42
Het is zo simpel: de kolom d wordt geëxtraheerd uit de meegeleverde JSON.
(Terzijde: Het lastigste deel kan het verkrijgen van een recente versie van SQLite zijn. Op het moment van schrijven is deze beschikbaar via Homebrew op macOS; anders moet je waarschijnlijk een onstabiele bron zoals nixpkgs-unstable gebruiken.)
Validatie en eigenschappen
Deze methode heeft enkele interessante eigenschappen. Normaal gesproken wordt aangeraden om JSON te minimaliseren en te valideren bij het invoegen (via de json() functie), omdat SQLite geen specifiek JSON-type heeft en daarom in principe alles toestaat. Er is echter niets dat dit afdwingt; je zou een constraint kunnen toevoegen, maar dat vergeet je waarschijnlijk.
Door GENERATED ALWAYS te gebruiken in combinatie met json_extract, zal ongeldige JSON bij een INSERT-actie direct een Error: malformed JSON opleveren.
Dit kan nog verder worden doorgevoerd:
sqlite> CREATE TABLE x (
body TEXT,
id TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL);
sqlite> insert into x values('');
Error: malformed JSON
sqlite> insert into x values('{}');
Error: NOT NULL constraint failed: x.id
We kunnen dus afdwingen dat bepaalde items aanwezig moeten zijn in de ingevoegde JSON door NOT NULL toe te voegen. Daarnaast kunnen we ook andere constraints en SQLite-functies gebruiken.
Virtuele versus opgeslagen kolommen
In deze voorbeelden is gebruikgemaakt van VIRTUAL voor de gegenereerde kolom. Er is ook de optie om STORED te gebruiken om de waarden in feite te cachen. Een nadeel hiervan is dat je deze kolommen niet via ALTER TABLE kunt toevoegen.
Je kunt echter altijd een index aanmaken op een kolom, zelfs als deze als virtueel is gedefinieerd:
CREATE INDEX xid on x(id);
Controleer vervolgens of dit werkt zoals verwacht:
EXPLAIN QUERY PLAN SELECT * FROM x WHERE id='foo';
QUERY PLAN
`--SEARCH TABLE x USING INDEX xid (id=?)
Flexibiliteit en uitbreiding
In combinatie met ALTER TABLE kunnen we een nieuwe kolom toevoegen en deze vervolgens indexeren:
ALTER TABLE x ADD COLUMN text TEXT
GENERATED ALWAYS AS (json_extract(body, '$.text')) VIRTUAL;
INSERT INTO x VALUES(json('{"id":43, "text":"test"}'));
CREATE INDEX xtext ON x(text);
Het voordeel hiervan is dat je kunt beginnen met een tabel die zo simpel is als een enkele JSON-kolom, en pas later kolommen en indexen toevoegt zodra je nuttige gegevens in die JSON ontdekt. Dit werkt bijvoorbeeld erg goed voor webhooks: voeg alle gegevens die je ontvangt direct toe aan een tabel, en haal later de relevante informatie eruit.
Veel plezier.
***
Datum: 17 juni 2020 Auteur: David Leadbeater
SQLite als documentdatabase
Hiermee is het mogelijk om JSON direct in SQLite in te voegen, waarna de database gegevens kan extraheren en indexeren. In feite kun je SQLite hiermee behandelen als een documentdatabase. Dit is al mogelijk met PostgreSQL en is uiteraard wat systemen zoals Elastic bieden, maar het is erg handig dat dit nu ook beschikbaar is in een embedded database voor lichtgewicht toepassingen.
Aan de slag
Laten we beginnen:
$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Connected to a transient in-memory database.
sqlite> CREATE TABLE t (
body TEXT,
d INT GENERATED ALWAYS AS (json_extract(body, '$.d')) VIRTUAL);
sqlite> insert into t values(json('{"d":"42"}'));
sqlite> select * from t WHERE d = 42;
{"d":"42"}|42
Het is zo simpel: de kolom d wordt geëxtraheerd uit de meegeleverde JSON.
(Terzijde: Het lastigste deel kan het verkrijgen van een recente versie van SQLite zijn. Op het moment van schrijven is deze beschikbaar via Homebrew op macOS; anders moet je waarschijnlijk een onstabiele bron zoals nixpkgs-unstable gebruiken.)
Validatie en eigenschappen
Deze methode heeft enkele interessante eigenschappen. Normaal gesproken wordt aangeraden om JSON te minimaliseren en te valideren bij het invoegen (via de json() functie), omdat SQLite geen specifiek JSON-type heeft en daarom in principe alles toestaat. Er is echter niets dat dit afdwingt; je zou een constraint kunnen toevoegen, maar dat vergeet je waarschijnlijk.
Door GENERATED ALWAYS te gebruiken in combinatie met json_extract, zal ongeldige JSON bij een INSERT-actie direct een Error: malformed JSON opleveren.
Dit kan nog verder worden doorgevoerd:
sqlite> CREATE TABLE x (
body TEXT,
id TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL);
sqlite> insert into x values('');
Error: malformed JSON
sqlite> insert into x values('{}');
Error: NOT NULL constraint failed: x.id
We kunnen dus afdwingen dat bepaalde items aanwezig moeten zijn in de ingevoegde JSON door NOT NULL toe te voegen. Daarnaast kunnen we ook andere constraints en SQLite-functies gebruiken.
Virtuele versus opgeslagen kolommen
In deze voorbeelden is gebruikgemaakt van VIRTUAL voor de gegenereerde kolom. Er is ook de optie om STORED te gebruiken om de waarden in feite te cachen. Een nadeel hiervan is dat je deze kolommen niet via ALTER TABLE kunt toevoegen.
Je kunt echter altijd een index aanmaken op een kolom, zelfs als deze als virtueel is gedefinieerd:
CREATE INDEX xid on x(id);
Controleer vervolgens of dit werkt zoals verwacht:
EXPLAIN QUERY PLAN SELECT * FROM x WHERE id='foo';
QUERY PLAN
`--SEARCH TABLE x USING INDEX xid (id=?)
Flexibiliteit en uitbreiding
In combinatie met ALTER TABLE kunnen we een nieuwe kolom toevoegen en deze vervolgens indexeren:
ALTER TABLE x ADD COLUMN text TEXT
GENERATED ALWAYS AS (json_extract(body, '$.text')) VIRTUAL;
INSERT INTO x VALUES(json('{"id":43, "text":"test"}'));
CREATE INDEX xtext ON x(text);
Het voordeel hiervan is dat je kunt beginnen met een tabel die zo simpel is als een enkele JSON-kolom, en pas later kolommen en indexen toevoegt zodra je nuttige gegevens in die JSON ontdekt. Dit werkt bijvoorbeeld erg goed voor webhooks: voeg alle gegevens die je ontvangt direct toe aan een tabel, en haal later de relevante informatie eruit.
Veel plezier.
***
Datum: 17 juni 2020 Auteur: David Leadbeater
Groetjes,