---
title: "SQLite"
description: "30 posts about SQLite, summarised, each linking to the original."
---

# SQLite
> 30 posts about SQLite, summarised, each linking to the original.

## Articles

### [Playing Traffic Cop with Fly-Replay](https://yomu.fyi/post/playing-traffic-cop-with-fly-replay.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: May 15, 2023
- Type: Tutorial

Fly.io’s fly-replay response header lets the Fly Proxy replay an HTTP request on another app, region, or VM within the same organization. The post explains how this can send writes near a leader database, preserve request locality after uploads, wake Machines stopped on exit, or route customer traffic across apps. It then builds a small Go reverse-proxy application that looks up the request hostname in SQLite and returns either app=our-default-app or the registered customer’s app in the header. Local request handling and lookup took about 4 ms, while the real-world proxy-and-replay path added about 100 ms; private customer apps remained reachable because Fly services can be defined without public IP addresses. The post also mentions LiteFS and internal DNS as possible discovery mechanisms.


### [WAL Mode in LiteFS](https://yomu.fyi/post/wal-mode-in-litefs.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Jan 4, 2023
- Type: Announcement

LiteFS v0.3.0 adds support for SQLite’s write-ahead log (WAL) alongside the previously supported rollback journal, allowing its distributed file system to replicate transactions from either journaling mode. The post explains how rollback journals and WAL differ, then shows how LiteFS observes filesystem calls, identifies changed pages, and emits a common LTX change-set format; in WAL mode, the final frame’s commit field marks transaction completion. Replica nodes validate LTX files, acquire SQLite-compatible locks, and apply the changes, while the new litefs import command transactionally replaces a database for bootstrapping or snapshot reversion. Version 0.3.0 also replaces previous-page checksum tracking with a per-page checksum map, costing about 3 MB of RAM per gigabyte and roughly 5 ms of CPU time per gigabyte per commit, and adds a trace log whose enabled use can double disk I/O.


### [Introducing LiteFS](https://yomu.fyi/post/introducing-litefs.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Sep 21, 2022
- Type: Announcement

LiteFS is an open-source distributed file system for SQLite that addresses the durability, replication, availability, and latency limitations of deploying SQLite on a single server. LiteFS interposes a FUSE pass-through filesystem between an application and its database, identifies transactions from SQLite’s journal files, packages page changes into LTX files, and ships them to replicas without requiring application-code changes. A Consul-backed, time-based lease elects a primary writer, while local replicas serve reads; checksum state embedded in each LTX file helps detect out-of-order application and preserve byte-for-byte consistency. Because replication is asynchronous, the design trades some durability guarantees for performance and operational simplicity, while defensive checksum checks address potential split-brain failure modes. The project is described as beta-ready, with WAL-mode support, write forwarding, S3 replication, and encryption listed as planned features.


### [How the SQLite Virtual Machine Works](https://yomu.fyi/post/how-the-sqlite-virtual-machine-works.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Sep 7, 2022
- Type: Explainer

SQLite processes SQL through a parse-optimize-execute pipeline that turns query text into executable virtual-machine instructions. It tokenizes characters, parses them into an AST such as a Select struct, and uses table statistics to choose between a full table scan and index-based access. The source describes sqlite\_stat1's coarse index and duplicate-value counts alongside sqlite\_stat4's distributed samples, then traces bytecode that opens the persons table, compares favorite\_color with "blue", and returns matching columns. Registers hold intermediate values, cursors iterate through tables, and sqlite3\_step() resumes execution after each returned row; the VM has 186 opcodes as of writing, while bind parameters allow prepared statements to skip repeated parsing and optimization.


### [How SQLite Scales Read Concurrency](https://yomu.fyi/post/how-sqlite-scales-read-concurrency.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Aug 24, 2022
- Type: Explainer

SQLite’s write-ahead log (WAL) is presented as the mechanism that overturns the common claim that SQLite is inherently single-user or unable to scale reads. Unlike the rollback journal, which blocks readers and writers from operating at the same time, WAL leaves original database pages in place and appends changed pages to a -wal file, allowing point-in-time snapshots to coexist with ongoing writes. SQLite tracks those entries in a shared-memory (SHM) index: 32KB blocks contain page-number lists and 8,192-slot hash maps, with slot positions calculated as (pgno \* 383)%8192 while transaction position determines visibility. The post concludes that WAL mode benefits most applications, while noting that rollback journaling can remain preferable for edge cases such as multi-database transactions.


### [Building an In-Browser IDE the Hard Way](https://yomu.fyi/post/building-an-in-browser-ide-the-hard-way.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Aug 15, 2022
- Type: Tutorial

This tutorial builds a personal remote development environment: an in-browser IDE for Elixir/Phoenix work, deployed as a Fly Machine from a Dockerfile and managed with command-line tools. The image uses an official Elixir base, adds code-server, flyctl, build dependencies, and the Elixir Language Server extension, while a multi-stage build copies in Tired Proxy. A persistent volume holds working files, and the startup configuration can clone a repository, launch code-server behind HTTPS, and arrange for the machine to sleep after an hour without HTTP requests. From the IDE terminal, the walkthrough then creates and deploys a separate Phoenix hello-world application backed by SQLite, setting its secret and provisioning storage. The resulting setup can wake on browser access, but random HTTP traffic or an active code-server terminal may keep it running, and stopping or destroying the apps limits costs.


### [How SQLite helps you do ACID](https://yomu.fyi/post/how-sqlite-helps-you-do-acid.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Aug 10, 2022
- Type: Explainer

SQLite’s rollback journal provides atomicity, isolation, and durability by preserving database state before a transaction changes the main file. Without journaling, an interrupted 4KB page write could corrupt the database; the journal instead records original pages, then permits recovery if commit steps fail. A journal header stores a nonce, the database’s pre-transaction page count, sector size, and page size, while each page record includes its page number, a copy of prior contents, and a 32-bit checksum. The mode allows concurrent readers but makes writers wait for readers and blocks new readers, using SHARED, RESERVED, and PENDING filesystem locks. Its simplicity brings a concurrency bottleneck; WAL and wal2 provide better support for concurrent readers, which the post says will be covered next.


### [SQLite Internals: Pages & B-trees](https://yomu.fyi/post/sqlite-internals-pages-b-trees.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Jul 27, 2022
- Type: Explainer

SQLite stores table rows as encoded byte arrays, and understanding that representation explains how a simple database preserves compact records and supports efficient growth. Using a sandwich table as an example, the post decodes a row’s varints, type values, UTF-8 text, IEEE-754 floating-point value, and integer count, then examines 4KB pages, cell-pointer indexes, and page headers. SQLite’s b+tree places records in leaf pages and key-range indexes in interior pages; page splits add levels as the database grows, while searches traverse only one page per tree level. The examples estimate that a depth-four tree can hold about 500³ leaf pages, or roughly 476GB, while requiring four page reads for a lookup. The discussion concludes that integer storage is more space-efficient than floating-point storage and that internals such as record encoding and PRAGMAs become less opaque when understood.


### [I'm All-In on Server-Side SQLite](https://yomu.fyi/post/i-m-all-in-on-server-side-sqlite.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: May 9, 2022
- Type: Explainer

Ben Johnson argues that SQLite should be considered as the core data and persistence layer for many production full-stack applications, rather than being relegated to unit tests in an n-tier architecture. He attributes this case to cheaper, faster hardware and the fact that most successful applications will never approach terabyte-scale data, making distributed database infrastructure excessive for some workloads. Litestream works alongside standard SQLite by taking control of WAL-mode journaling, capturing WAL updates, replicating them to storage such as S3, and handling checkpoints itself. This preserves ordinary SQLite libraries and transactions while providing a path to restore copies and improve resilience to storage failure; upcoming direct database-to-database replication is also mentioned. The resulting model places data in the application process, where the post reports 10–20 microsecond per-query latency, but it remains aimed at single-node applications and has restore, deployment, and replication-guarantee limitations.


### [Making Datasets Fly with Datasette and Fly](https://yomu.fyi/post/making-datasets-fly-with-datasette-and-fly.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Mar 26, 2020
- Type: Tutorial

Datasette makes SQLite datasets accessible through a web interface, with SQL queries, JSON and CSV export, and a REST/JSON API. This tutorial imports New York Central Park Squirrel Census CSV data into SQLite, runs it locally with Datasette, and publishes the database to Fly using the datasette-publish-fly plugin. It then renames the database’s X and Y columns to Longitude and Latitude, installs datasette-cluster-map, and redeploys with the plugin to produce an interactive map of squirrel sightings. The deployments expose Datasette online at Fly hostnames, with HTTPS, automatically generated Let’s Encrypt certificates, custom domain support, global deployment options, and edge networking with SSL termination. The mapped deployment lets users inspect individual sightings and view their details.


[Newer posts](https://yomu.fyi/topic/sqlite.md)