Share
Embed
Transcript
22 slides
-
Hi, I'm Joel! Joel Jucá 35yo full-stack web engineer I
run Elug CE (elug-ce.github.io) x.com/holyshtjoe @[email protected] linkedin.com/in/joeljuca -
SQLite? You serious? Popular (the most used database in the
world) It's relational database (tables, rows, and SQL) Simple and powerful (reliable, full-featured, yet beginner friendly) Embedded (no servers or networking – it's just a library) Extensive (eg.: github.com/nalgeon/sqlean) Stable (no hype-driven development here) Public domain (absolute freedom) -
IRL SQLite: 101 It's just a file (eg.: database.sqlite )
It's just a library (aka.: embedded in your app) Good IDEs and GUIs (eg.: DBeaver) Good CLI ( $ sqlite3 , sqlite3_analyzer , sqldiff , etc.) It's FAST! (no networking; ~90% faster!) Learn more at sqlite.org I'd also highly recommend Mycelial's YouTube channel -
IRL SQLite Data types (Data types In SQLite) NULL INTEGER
& REAL TEXT BLOB -
SQLite & JSON JSON Functions and Operators Plain text (no
BSON, binary, etc.; uses TEXT columns) Brings arrays and objects to SQLite Support -> and ->> (Postgres syntax) Supports JSON5 // Comments! [ "trailing", "commas", ] { keys: 'without quotes' } -
Awesome for Read-heavy Sequential writes Medium-sized datasets (up to 1TB)
Segmented datasets (eg.: multi-tenancy) -
Not very awesome for Parallel writes Large datasets (multi-TBs of
data) Access control GIS (SpatiaLite is not super simple) -
Bets on SQLite Canonical is working on dqlite for quite
some time Fly is working on LiteFS Cloud Cloudflare is working on D1 Me -
SQLite WTFs Flexible Typing (Enforcement possible through STRICT tables) Keywords
as identifiers (eg.: CREATE TABLE t (INTEGER TEXT) ) No BOOLEAN type No DATE , TIME , and/or DATETIME , types -
Case: Expensify 4M queries/s (~345B/day) Lower costs, yet super scalable
Uses Bedrock (SQLite fork) Scaling SQLite to 4M QPS “ “ -
Case: Savi Solutions 1B writes/day (~11.5K/s on avg) Replaced a
whole cluster of Cassandra Lower costs, minimal overhead Replacing Cassandra with SQLite “ “ -
Elixir and SQLite exqlite (Elixir library for SQLite; raw access
to databases) ecto_sqlite3 (Ecto adapter, Phoenix built-in) Phoenix over SQLite: $ mix phx.new --database sqlite3 my_app Ecto DX pretty much the same -
Ecto's Dynamic Repos Allows primary & replica MyApp.Repo.start_link(name: :primary, database:
"primary.sqlite") MyApp.Repo.start_link(name: :replica, database: "replica.sqlite") Allows nameless, PID-based repos (processes) {:ok, pid} = Repo.start_link(name: nil, database: "123.sqlite") Ecto repos are just processes :) Read more at Replicas and dynamic repositories -
SQLite Multi-Tenancy Databases are cheap to create and maintain Multiple
databases are OK One project-wide database One database per tenant One process per database (Ecto's dynamic repos FTW!) -
SQLite Multi-Tenancy Eg.: database.sqlite (project-wide database) tenant-dbs/tenant-1.sqlite (per-tenant database) tenant-dbs/tenant-2.sqlite
(...) Managing a bunch of small SQLite databases is still easier than a huge Postgres server – and definitely cheaper! -
SQLite Multi-Tenancy Easy to use, cheap to maintain Availability benefits
Reduced downtimes Reduced unavailability -
Contributions ecto_sqlite3 v0.10.1 PR: fix bad init file loading in
dump_cmd/3 (#111) v0.10.2 PR: add :date column type (#112) PR: add :on keys to queries with joins (#113) -
Contributions SQLFluff (SQL linter written in Python) SQLite dialect does
not understand partial indexes (#4830) COLLATE NOCASE causes SQLFluff to fail SQL parsing (#4829) -
Projects worth watching libSQL (fork of SQLite, open to contributions)
Litestream & LiteFS Mycelial (CRDTs for SQLite) ElectricSQL (local-first apps w/ Postgres and SQLite) Datasette sql.js (SQLite in WebAssembly) rqlite Bedrock