Phoenix over SQLite

Transcript

1. Phoenix over SQLite Por Joel Jucá
2. Hi, I'm Joel! Joel Jucá 35yo full-stack web engineer I run Elug CE (elug-ce.github.io) x.com/holyshtjoe @joel@functional.cafe linkedin.com/in/joeljuca
3. 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)
4. 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
5. IRL SQLite Data types (Data types In SQLite) NULL INTEGER & REAL TEXT BLOB
6. 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' }
7. Awesome for Read-heavy Sequential writes Medium-sized datasets (up to 1TB) Segmented datasets (eg.: multi-tenancy)
8. Not very awesome for Parallel writes Large datasets (multi-TBs of data) Access control GIS (SpatiaLite is not super simple)
9. Bets on SQLite Canonical is working on dqlite for quite some time Fly is working on LiteFS Cloud Cloudflare is working on D1 Me
10. 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
11. Case: Expensify 4M queries/s (~345B/day) Lower costs, yet super scalable Uses Bedrock (SQLite fork) Scaling SQLite to 4M QPS “ “
12. 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 “ “
13. 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
14. 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
15. 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!)
16. 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!
17. SQLite Multi-Tenancy Easy to use, cheap to maintain Availability benefits Reduced downtimes Reduced unavailability
18. 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)
19. Contributions SQLFluff (SQL linter written in Python) SQLite dialect does not understand partial indexes (#4830) COLLATE NOCASE causes SQLFluff to fail SQL parsing (#4829)
20. 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
21. Questions? Don't be shy! :)
22. That's all, folks! Joel Jucá joeljuca@proton.me