Loading…
Topic
SQLite
30 posts about SQLite. Every summary links to the original.
- 30 posts
- 7 companies
The Good Stuff
Paid picks we use ourselves. Links carry a referral code — a subscription through one earns us a commission at no extra cost to you.
Newest first
Posts
Under the hood, off the hot path: How D1 scales to millions of databases
D1, Cloudflare’s serverless SQLite database, has seen 15x growth in live databases over the past year and scales by keeping query execution on the edge in Durable Objects, separate from the control plane’s metadata operations. The control plane uses an Edge API Worker with Hyperdrive to access Postgres clusters for transactional metadata, while Workers KV caches database-to-Durable-Object mappings for fast edge lookups. Database creation completes in about 1.6 seconds by generating a Durable Object ID, persisting metadata to Postgres, and storing the mapping in Workers KV, with the Durable Object lazily initialized on first query. Storage usage enforcement uses atomic counters in Workers KV that are checked locally on each query and updated asynchronously after execution, avoiding any central database round trip during the query. Precomputed aggregations for billing are stored in ClickHouse, and guiding principles such as eventual consistency and idempotency further reduce reliance on centralized components.
Supabase is acquiring Turso
Supabase is acquiring Turso to evolve database infrastructure for the massive scale of AI agent-created databases. Supabase already launches over one million databases per week and expects demand to outpace capacity as agents build more software. Turso rebuilt SQLite in Rust, creating a cloud platform where a single server manages millions of databases, loading them on demand and suspending them when idle, avoiding dedicated machines for small workloads. This architecture suits agent workloads needing cheap, on-demand databases, while Postgres remains the path for scaling to production; Turso will continue operating independently with a clear path into Supabase’s ecosystem, and for existing users nothing changes. Glauber Costa will lead the agentic infrastructure effort, and the combined mission is to store the world’s data as agents create more of it.
How to Track Opt-Outs in PHP
This tutorial demonstrates building a PHP application that records customer opt-out preferences from SMS replies using Twilio’s Incoming Message Webhook and Event Streams, which require PHP 8.5, Composer, and SQLite. When the SMS body is an opt-out keyword such as STOP, START, or HELP, Twilio includes an OptOutType parameter in the webhook request, which the application checks using strcasecmp and array_first. The application, based on the Twilio Slim Base project, stores the opt-out status and account SID in an SQLite database via PHP-DB and laminas-servicemanager, using a TableGateway for the user_optouts table. The handleWebhook() function validates the OptOutType against class constants (OPT_OUT_TYPE_START, OPT_OUT_TYPE_STOP, OPT_OUT_TYPE_HELP) and inserts a record before returning a TwiML response. Twilio already blocks further messages for standard opt-outs, but local recording enables advanced actions like syncing a CRM; the tutorial covers bootstrapping, configuring Twilio messaging services with Advanced Opt-Out, using ngrok, and verifying with an SQL query.
Augment Voice Calls with Twilio Conversation Intelligence Using Node.js
Twilio Conversation Intelligence, Conversation Orchestrator, and Conversation Memory together analyze voice calls for a Node.js voice agent built with Conversation Relay. After a call, Twilio sends a webhook to /intelligence-results containing a call summary, sentiment analysis, and script adherence for categories like introduction with the required phrase “Thanks for calling Owl Air! I’m Hoot.” The developer creates a Memory Store and Conversation Configuration, then an Intelligence rule selecting Sentiment, Summary, and Script-Adherence operators triggered at conversation end. A Node.js handler extracts the data and persists it to a SQLite database using tables for intelligence_results, operators, and operator_script_adherence_categories. Testing involves calling the Twilio number and verifying the summary, sentiment, and script adherence records in the database.
Building a Memory-Driven Agent with NVIDIA NemoClaw
Enterprise agents need more than conversation history to connect decisions, obligations, people, and projects that change over time. Using NVIDIA NemoClaw, the example builds a memory-driven Chief of Staff around a human-readable self model in structured Markdown, a SQLite ledger for obligations and judgments, scheduled maintenance, bounded retrieval, and user correction with append-only auditing. Its architecture separates evidence, knowledge, and governed execution, while NVIDIA OpenShell sandboxes runtime access and keeps managed inference and MCP credentials outside the sandbox; memory can inform an action but cannot authorize it. In the supplied evaluation, the self model reached 90.9% overall accuracy versus 82.8% for an agentic RAG baseline, with larger gains on hard questions, changing facts, point-in-time reasoning, entity disambiguation, and multisource synthesis, but lower scores on corpus-faithful answers and single-hop lookup.
Shared Nothing Architecture
Shared-nothing architecture is presented as a scaling model for Showcase, a Ruby on Rails ballroom-competition application that began on a Mac Mini and grew to cloud VMs serving events in multiple regions. Because each event has few geographically concentrated users, a roughly one-megabyte SQLite database, about one transaction per second, and typical response times of 100ms or less, the design keeps state separate rather than sharing databases and Redis across events. The implementation runs an event’s services on one Fly.io VM, repeats that unit across machines or regions, and can place multiple event instances on one server with Phusion Passenger, NGINX, volumes, and a shared Action Cable process. It specifies 1GB machines with 2G swap after observing about 220MB idle and 450MB active RAM, and recommends WireGuard; the conclusion is that local state and fewer network calls suit this workload and can improve throughput and reliability.
Litestream Writable VFS
Litestream’s VFS is being extended to support Sprites, whose storage stack needs SQLite-backed block maps to serve writes immediately during cold starts, before a full database or block map is available. The VFS indexes each database page by file, offset, and size in LTX files, serves reads from object storage, and now optionally buffers writes locally before syncing them roughly every second or on clean shutdown. Write mode assumes a single writer, disables polling, and offers only the same eventual durability already provided by Sprite storage. A separate hydration mode serves remote reads while reconstructing a complete database file in the background, then switches reads to that file; the temporary file is discarded on exit. These features target Sprite-like workloads, while ordinary Litestream sidecar deployments do not require them.
How we made Notion available offline
Notion's Offline Mode required turning a best-effort SQLite cache into storage that could guarantee a page and all its dependent records were available without a network connection. The system uses a forest of offline page trees backed by offline_page and offline_action tables, allowing independent reasons such as explicit toggles, favorites, automatic downloads, and inheritance to coexist while preserving hierarchy. To keep content current, clients subscribe to push channels emitted by the page version snapshot system, then use lastDownloadedTimestamp and server lastUpdatedTime to fetch only newer pages after reconnecting. When pages or databases change, the client reconciles fresh page and database snapshots with offline_action, adding or removing rows rather than rebuilding entire trees. This makes offline availability precise and eventually consistent, while supporting conflict resolution through the CRDT data model.
Litestream VFS
Litestream VFS lets SQLite query a Litestream-backed database directly from object storage, including historical states selected with SQL pragmas, without first downloading the complete database. It is a loadable SQLite VFS plugin: Litestream v0.5 uses the LTX data-shipping format, whose compaction removes superseded page versions, while index trailers map database pages to remote file offsets. The VFS fetches only requested page ranges through S3, uses an LRU cache, and can poll second-level L0 backups to maintain a near-realtime replica. Writes remain handled by the regular Litestream Unix program, and the existing SQLite library is unchanged. The result is fast querying and instantaneous point-in-time recovery from arbitrary relative or absolute timestamps, while the optional VFS preserves Litestream’s conventional backup and restore workflow.
Corrosion
Corrosion is Fly.io’s open-source service discovery and state synchronization system, built to distribute routing data across globally deployed edge proxies and worker servers. It replaces centralized consensus-oriented designs with a Rust program that propagates a SQLite database through SWIM-based gossip and QUIC, using cr-sqlite’s CRDT tables and logical timestamps to reconcile updates without locking, a central server, or distributed consensus. The system’s design follows link-state routing ideas and exploits Fly.io’s fully connected WireGuard mesh, while worker-owned state keeps conflicts uncommon. The post also documents failures, including a contagious Rust RWLock deadlock, a nullable-column migration that triggered large-scale reconciliation, and the need for backups, whole-dataset republishing, testing, and regionalization to reduce blast radius. Corrosion now converges in seconds across thousands of workers.
Litestream v0.5.0 is Here
Litestream v0.5.0 updates the SQLite backup and restore tool with faster operation and efficient point-in-time recovery (PITR). It replaces WAL segment generations with LTX files, an interchange format for ordered page ranges that supports compaction and monotonically incrementing transaction IDs. Hierarchical compaction combines changes across 30-second, five-minute, and hourly levels, allowing restoration to any point in time using only about a dozen files on average and performance limited by I/O throughput. The release keeps old v0.3.x WAL files intact and preserves configuration compatibility, but permits only one replica destination per database and renames litestream wal to litestream ltx. It also adds per-page compression and indexing, removes CGO through modernc.org/sqlite, supports NATS JetStream replicas, updates cloud-storage clients, and previews a VFS for read replicas that can fetch pages from S3 while hydration continues.
Litestream: Revamped
Litestream’s redesign aims to make SQLite suitable for more deployment scenarios while preserving its embedded, application-oblivious model. The original tool continuously streamed WAL pages to S3-compatible object storage, but restores could be slow because they replayed every change since the last snapshot. The new design adopts LiteFS’s transaction-aware LTX format, sorted changesets, and compaction, enabling faster point-in-time restores with fewer duplicate pages; conditional writes provide time-based leases that prevent overlapping instances from confusing a destination. Planned additions include a VFS-based read-replica layer that fetches and caches pages from object storage, plus replication across hundreds or thousands of database files. The remote read path may be less efficient than local SQLite, but caching and prefetching are expected to provide strong performance for suitable use cases.
Operationalizing Macaroons
Operationalizing Macaroons at Fly.io requires treating them as online-stateful bearer tokens whose nonces must be checked against database state, while keeping root secrets away from the primary API cluster. The team built tkdb, a roughly 5,000-line Go service backed by encrypted SQLite managed with LiteFS and Litestream, deployed on isolated hardware across the US, Europe, and Australia. Its HTTP/Noise interface supports verification and restricted signing, while FlyCast routing, retries, and client-side caching reduce the impact of unreliable global connectivity; verification caching exceeds 98%. Revocation uses blacklist state, and third-party caveats let services such as flyd obtain narrowly scoped secret-reading authority through tkdb rather than holding broad credentials. OpenTelemetry, standard metrics, and a retained OpenSearch audit trail provide visibility, while the post concludes that Macaroons’ internal infrastructure benefits outweigh limited user adoption.
The Exit Interview: JP Phillips
JP Phillips leaves after four years at Fly.io, saying the company’s increasing focus on Managed Postgres and LLMs does not match the direction he hoped to see: a move toward cloud provision and larger established customers. He says his original goal—building Fly Machines to replace HashiCorp Nomad—has been accomplished, with the platform now capable of supporting the next iteration of products. His central engineering contribution was the Fly Machines API and flyd, an independently running orchestrator whose finite-state-machine invocations use deterministic, durable execution and log each step in BoltDB. He also discusses corrosion2, a Rust service that uses SWIM gossip and a CRDT-structured SQLite database to distribute near-real-time machine state. Phillips criticizes GraphQL, Elixir, inconsistent communication and shifting priorities, while defending BoltDB and praising Rust’s productivity.
Picture This: Open Source AI for Image Description
Nolan Darilek presents a self-hosted image-description service intended to help blind screen-reader users understand photos, diagrams, menus, and other visual material. Humans often provide inadequate alt text, and the post contrasts that history with increasingly capable LLM-generated descriptions from apps and browser add-ons. The implementation combines Ollama, running the Apache-licensed LLaVA 34B multimodal model, with a PocketBase API over SQLite and a small Python client; collection hooks send images and follow-up questions, retain chat history, and enforce per-user access rules. In testing, a cold request on an A100-40GB Fly Machine took about 45 seconds, while stopping idle GPU Machines reduced resource use at the cost of startup delay. The example produced useful scene descriptions but could not reliably identify leafless tree species, illustrating both the prototype’s value and its limitations.
JIT WireGuard
Fly.io describes how its gateways provisioned WireGuard peers for flyctl and why the old design became unreliable and slow. NATS delivery losses caused failed provisioning, while CI-generated peers accumulated because gateways never removed them, leaving hundreds of thousands of stale kernel entries that slowed reloads and contributed to kernel panics. The replacement detects handshake-initiation packets with BPF or a WebSockets packet hook, runs enough Noise cryptography to recover the initiator’s public key, then fetches matching peer data over an internal HTTP API and installs it on demand. A rate-limited SQLite cache supports cleanup jobs, and the gateway can use the observed four-tuple to initiate back toward flyctl and accelerate handshakes. After weeks in production, stale peers rounded to none, with faster setup and rebooting that no longer required reloading unused entries.
Skip the API, Ship Your Database
The post describes an unexpected use for LiteFS, a distributed SQLite database: replacing service-to-service APIs with read-only database replicas. Typical APIs require anticipating access patterns; shipping the full database lets clients use SQL joins, aggregation, and windowing locally, while one-way LiteFS replication avoids chatty gossip and is simple to configure with an upstream primary hostname. Moving queries to client hardware removes shared service compute contention and the need for tenant rate limiting and timeouts, but replicas cannot handle mutations and raw schema changes create looser contracts, mitigated by backward-compatible changes and views. Data access is harder to restrict in multi-tenant databases, so per-tenant SQLite files are a suggested workaround. The approach is positioned for specific read-heavy use cases, potentially including local copies of Stripe or GitHub data, with event buses such as Kafka generating per-tenant databases.
Tracking Application-Level Consistency with LiteFS
LiteFS must keep an application’s view consistent when a write reaches a primary and a subsequent read is served by a lagging replica. The project first considered making applications track each database’s replication position—a TXID paired with a post-apply checksum—and wait for replicas to catch up, but rejected that complexity and abandoned language-specific libraries. It instead introduces a thin HTTP proxy in front of the application: method-based request inspection routes writes to the primary, attaches the committed TXID in a cookie, and delays reads until the local replica reaches that position or times out. The proxy also exposes lag through health checks, allowing Fly.io to remove unhealthy replicas from rotation and restore them after reconnection. This design provides read-your-writes guarantees with little application change, but depends on cookies and conventional HTTP request/response traffic, limiting use with some applications and websocket-heavy frameworks.
How to Get Going with the On
This workshop setup guide prepares a minimal Todo SaaS application for a series of enterprise-readiness workshops, while warning that the base app is not production quality and has intentional security shortcomings. It uses a React frontend, an Express API written in TypeScript, SQLite persistence, Prisma for ORM and database visualization, and an Nx workspace for shared project commands. Developers install Node.js v18 or higher, obtain the repository, run npm ci, seed two users with npm run init-db, and start the application with npm start. The frontend runs on localhost:3000, the API on localhost:3333, and Prisma Studio on localhost:5555 for inspecting users and todos. The guide concludes by directing developers to workshops covering OpenID Connect, SCIM, Terraform, Okta Workflows, and cross-application sign-out.
LiteFS Cloud: Distributed SQLite with Managed Backups
LiteFS Cloud, launched in preview, adds managed backups and restores to LiteFS, an open-source distributed SQLite database that replicates data across application-cluster nodes. Existing LiteFS users had to create snapshots, choose retention, and accept restoration points limited by snapshot frequency; the service instead provides an equivalent snapshot every five minutes and restores within the last 30 days at five-minute granularity. Setup requires LiteFS 0.5.1 or later, a dashboard-created cluster, and an auth token, after which backups are automatic and dashboard restores use the latest snapshot before a selected timestamp. Its LTX format stores changed database pages, while multi-level compactions combine daily snapshots, hourly changes, and five-minute intervals so a restore can stream-merge a minimal set of files. To improve async replication’s durability trade-offs, the primary batches changes, LiteFS Cloud persists them in S3, and a high water mark prevents local transaction files from being removed before storage confirmation.
Related topics
All topics →Weekly digest
The best engineering reads, once a week.