SQLite
Abstract
Titel
SQLite
Abstract
Vrijwel iedereen gebruikt het (onbewust) als eindgebruiker. Maar wanneer en hoe gebruik je het als ontwikkelaar? En wanneer niet?
ˈsiːkwəˌlaɪt ?

ˌɛsˌkjuːˌɛlˈaɪt ?
Joris Zwart logo
Over Joris

Wie?

Mijn naam is Joris Zwart (1974) woonachtig te Lent (boven Nijmegen) met mijn lieftallige gezin. Online ben ik te vinden op joriszwart.nl

Wat?

lastcrud.com; upcoming nocode platform, main USP: visual business rules, scratch for grown-ups.

Hoe?

C#, SQLite, Vue/Vuetify.

Waar?

Ex-Planonner 2005 – 2009. PlanonNet Self-Service I (nu PSS2?), PlanonTalk, reserveringen (express, outlook), SAP support calls.

Lies, damned lies, benchmarks.
Benchmark 
CREATE TABLE IF NOT EXISTS data (
    id INTEGER PRIMARY KEY,
    content TEXT
) STRICT
          
.timer on
> 
INSERT INTO numbers (content)
    SELECT value FROM generate_series(1, 1000000);
          
  explain select count(*) from numbers;
  explain query plan select count(*) from numbers;

  explain query plan select sum(*) from numbers;
          
INSERTs   : 1.000.000
Duration  :     1.357s
---------------------
INSERTs   :  ~737.000/s
Mac mini M1 2022, 16 GB, SSD (the latter is important). 
$ go run bench.go
$ sqlite3 bench.sqlite
> .timers on
> select count(*) from data;    
> explain query plan select count(*) from data;
> select * from data limit 0, 10;
> select * from data limit 999990, 10;
Small. Fast. Reliable.
Klopt dat?

Ja, mits... WAL-mode, indexen en transactions

Tenzij..., heavy writing.

ACID, auteurs garanderen dat het minstens tot 2050 meegaat (hoe dan? C :-))

Meest getest? Miljoenen regels testcode. Op allerlei execution points (WAT als ik NU de stroom er af haal? Gelden de ACID-principes nog steeds?) Diep onderzoek naar filesystemen en hun issues. Disks and disk controllers lie.

Oorsprong Dr. Richard Hipp. 2000. Ik kwam er zelf mee in aanraking rond 2003. Officieel archive format US Library of Congress. Auteurs: alternatief voor fopen() (C file open), maar daar doen ze zichzelf tekort IMHO.

Public domain
fopen()
Demo
CLI - open database (in memory of met file) - create table - insert - ls files
Installation
apt install sqlite3
brew install sqlite3
pkg install sqlite3
choco install sqlite
sqlite.org/download.html
./configure && make
Five data types
Eenvoud Vergelijk met Big Monsters:
  • text
  • blob
  • varchar(n)
  • nvarchar(n), wat n?
  • int(1)
  • int(11)
  • ...
  • What me worry?

( ͡ಠ ʖ̯ ͡ಠ) column affinity, strict tables

INTEGER
REAL
TEXT
Encoding Standaard UTF-8, maar pragma mogelijk voor andere encodings.
BLOB
Details Binary Large Objects (afbeeldingen, documenten, apps) Streaming Blob API
NULL
Yep, het is een type. Don't ask, maar dit heeft te maken met column affinity.
Caveat: heavy writing
Caveat: scaling
Features
Relational database
Document database
JSON/JSONB + calculated indexen
Columnar database
Ugly hack Tabel per kolom *of* verticaal gepivotteerd.
Time series
Zero configuration, but…
True Maar WAL-mode én foreign keys! Verder tunen (cache-size etc) kan altijd en loont ook.
Embedded library
Details Library, geen standalone (server)applicatie. Self-contained. In-process. Dus geen socket of netwerkoverhead.
Small footprint
Size 500 kB – 1 MB

MySQL installer: 2 MB (enkel de installer)
ACID compliant
Betekenis Atomicity
Consistency
Isolation
Durability
Foreign keys, ha!
Mental note Wel even aanzetten (bij iedere connectie).
Modern SQL
N+1 problems
Problem?! Geen probleem!
Window functions
BLOB API
Details Streamend schrijven en lezen van blobs. Afbeeldingen of andere binaries *in* de database is hiermee geen anti-pattern meer en zelfs aan te bevelen; referentiële integriteit op je assets.
JSON and JSONB
Language bindings
Details Vrijwel elk taal/platform heeft bindings. C, C++, C#, Golang, Java, JavaScript (Node), Python, Haskell etc.
:memory:
Toepassingen Cache (smart querying), snelle integrations tests.
Extensions
Authorization
Compression
Encryption
Full-text search
FTS5 Zie ook mijn artikel Full-text document indexing - part Ⅰ over het indexeren van docx, gebruik makende van het feit dat het eigenlijk gezipte XML-bestanden zijn.
Geospatial
Transactional DDL
Belangrijk?

Data Definition Language

Waarom handig?

Omdat je niet een half gelukte migratie met de hand wilt terugdraaien.

Write-Ahead Log
Details
  • Readers blokkeren elkaar niet en worden niet geblokkeerd door writers.
  • Writers blokkeren elkaar.

Ergo: single-writer.

Je kan split reading/writing (queue) doen. Zelfs aan te raden.

Busy timeout (bij 5000 ms) is van belang, want default erg kort.

PRAGMA journal_mode = WAL
PRAGMA foreign_keys = true
PRAGMA busy_timeout = 5000
PRAGMA synchronous = NORMAL
SQLite
vs
Big Monsters
<spoiler> Absoluut! in 99.214% van de gevallen. We zijn geen MANGA (Meta Amazon Netflix Google Apple)
Synopsis
Inspired?
Aanbod

Ik zelf ben er zeer enthousiast over en het is mijn go-to database platform.

Wie gaat er mee experimenteren? Vingers?

Als je het serieus overweegt: ik ben er graag om je te helpen (om niets).

joriszwart.nl/sqlite