Building, Compressing, and Auditing Your Databases

This is our second release of SQLite Fun! 🎉🥳

SQLite is widely used, though it may not have the same development news cycle as many other databases. This makes SQLite Fun a bi-weekly newsletter.

Please submit your links to us -- blog post, video, Github Repo, etc.

Highlights

• Build your own SQLite - This series uses Rust to build SQLite from scratch, offering valuable insights into the design and layout of the SQLite file format. Part 2: Scanning large tables has also been published. ‐ Geoffrey Copin
• Log file compression with Zstandard VFS in SQLite Benchmark - An in-depth series of blog posts exploring different compression mechanisms for SQLite databases, with benchmarks of all the knobs and dials to optimize the experience. ‐ Peter OsinskiView
• Auditing and Versioning Data in SQLite - Proposes a pattern for using triggers to audit changes to a table, helping maintain revisions for inspection. ‐ Phillip Wagner

Code

• sqlx is a Rust library for managing SQL interactions. It works with PostgreSQL, MySQL, MariaDB, and SQLite.
• sqlite3-cmake supports the compilation of SQLite in your CMake file.
• SQLite full-text-search support in Rails migrations ‐ Zacharias Knudsen
• Vectorlite is an extension that adds vector database capabilities to SQLite. Claims to be faster than sqlite-vec and sqlite-vss.

The Lighter Side

• Tweet/X:

  15,000 requests/second! That's what I just got out of that $220/month EPYC 48-core hobby box from Hetzner running a basic Rails 8 scaffold MessagesController#show loading a single record from the DB with no caching of any kind. Hot diggity!

  ‐ DHH

• Pipe Syntax - Proof of concept of Google's proposed functional pipe syntax |> for SELECT queries.

• Invisible Columns - Most databases support having columns that don't get returned in a SELECT * query. SQLite does not have this feature; however, it is beneficial to learn patterns from other database systems. ‐ Trevor Lasn

Thanks for reading! See you in two weeks.