Weekly SQLite news, articles and extensions ✨
Ciao! Hope you are enjoying the week ツ Here is what's going on in the SQLite world lately:
Nice trick to avoid recomputing a function in a query.
Transactions are a tricky beast, so it's worth revisiting the differences between deferred and immediate ones.
In this book, Paul Sanderson covers the format of the SQLite database, and associated journal and Write-Ahead Logs (WAL) in great detail.
mvSQLite is the open-source, SQLite-compatible distributed database built on FoundationDB. In this post, Heyang Zhou benchmarks mvSQLite to see how a single database scales when adding more machines to the cluster.
libSQL is an open source, open contribution fork of SQLite. The team behind it aims to evolve it to suit many more use cases than SQLite was originally designed for.
That's all for now. See you next week!
Hola! Hope you are enjoying the week ツ Here is what's going on in the SQLite world lately:
LiteFS is a distributed file system specifically built for replicating SQLite databases. This allows each application node to have a full local copy of the database and respond to requests with minimal latency.
Charles Fisher describes how to use SQLite as an application secrets provider.
mvSQLite is the open-source, SQLite-compatible distributed database made by Heyang Zhou. In contrast to rqlite, dqlite, and Litestream, mvSQLite offers not only read but also write scalability, provides the strictest consistency, and is a drop-in addition to SQLite.
Atuin (made by Ellie Huxtable) replaces the existing shell history with an SQLite database, recording additional commands context. It also provides optional and fully encrypted history synchronisation between machines.
rqlite is a lightweight, distributed relational database, which uses SQLite as its storage engine. Release 7.7.0 adds support for the non-deterministic RANDOM function via statement-rewriting.
A CLI tool to load SQLite3 databases into PostgresSQL. Supports tables and indexes, but not views or triggers.
That's all for now. See you next week!
Salut! Hope you are enjoying the week ツ Here is what's going on in the SQLite world lately:
Redowan Delowar has collected 16 examples of handling SQLite with Python, from executing ordinary statements to dumping the database and using full-text search.
You probably haven't heard of this, but SQLite transactions are deferred by default (transaction does not actually start until the database is first accessed). A deferred transaction can start as 'read' and later upgrade to 'write'.
In contrast to 'deferred' transactions there are also 'immediate' ones. Immediate causes the database to start a new write immediately, without waiting for a write statement.
This forum thread explores a valid use case for immediate transactions, and their limitations.
Triggers in any schema other than temp may only access objects in their own schema. However, triggers in temp may access any object by name, even cross-schema.
This forum thread describes a clever way of utilizing this fact.
Functions created with the define extension used to be quite slow. Frankly, 30x times slower than the built-in ones. Not anymore!
In addition, the define extension now includes the eval() function for executing dynamic SQL.
Gani Georgiev has developed an open source Firebase / Supabase alternative in a single-file binary! Provides realtime subscriptions, user management, admin UI and REST-ish API.
DuckDB is an in-process SQL OLAP database management system ("SQLite for analytics" if you will).
The sqlitescanner extension allows DuckDB to directly read data from the SQLite database file. This is a great chance to try DuckDB on top of your existing databases!
That's all for now. See you next week!
Hello! Hope you are enjoying the week ツ Here is what's going on in the SQLite world lately:
SQLite is unique among embedded databases in that it not only has a transactional, b-tree storage layer but it also includes a robust SQL execution engine. In this article, Ben Johnson dives into how SQLite parses, optimizes, & executes SQL queries.
SQLite does not directly support user-defined functions. Unless you use the define extension, which allows writing functions in regular SQL.
Alex Garcia made an SQLite extension for parsing and generating URLs and query strings.
Distributed SQLite replicator by Zohaib Sibte Hassan. It enables multi-master replication of SQLite databases among multiple nodes. Marmot builds on standards like RAFT and CBOR, and uses triggers to store change logs that are replicated to peers.
A clever trick of using CROSS JOIN to force a hand-picked plan.
UPSERT causes the INSERT to behave as an UPDATE or a no-op if the INSERT violates a unique constraint. Non-standard SQL, but compatible with PostgreSQL and super-handy in everyday work.
A look inside the rigorous SQLite testing process.
That's all for now. See you next week!