Why Everyone Is Talking About SQLite?

As part of challenging my own thinking in these newsletter articles, I wanted to take a closer look at a technology that’s been generating a surprising amount of buzz lately: SQLite.

Even the name itself makes it seem like something you’d use for a pet project—"Lite" is right there. Historically, it was relegated to local development, mobile apps, or quick prototypes. Production use? Not a chance.

But suddenly, everyone’s talking about SQLite as if it’s the future of databases. With the rise of serverless computing, changes in Redis’ licensing model increasing costs, and frameworks like Rails 8 adopting it in new ways, SQLite is making a strong case for broader adoption.

So what’s going on?

Is SQLite the Arduino of databases—fun, accessible, but not something you’d actually deploy at scale? Or is it the little engine that could, quietly reshaping how we think about production databases?

This article explores two key questions:

1. Where does SQLite actually break down in production?
2. How can SQLite be a hidden superpower in your stack?

---

1. The Limits of SQLite: Where Does It Break?

Suggest using SQLite in production, and the first response you’ll hear is, “Won’t that break?”

For years, the answer was a firm yes—at least for high-traffic, multi-user applications. And even today, SQLite has real technical constraints that developers need to understand before adopting it.

Where SQLite Struggles in Production

🔴 Write Contention

• SQLite uses a single-writer model, meaning only one transaction can write to the database at a time, while multiple transactions can read concurrently. In WAL mode (Write-Ahead Logging), concurrent reads and writes are possible, but only one write transaction can be committed at a time, which can still lead to contention under heavy write loads.
• Workarounds:
  • Enabling WAL mode (Write-Ahead Logging) helps by allowing multiple readers to access the database while a write transaction is ongoing. However, there’s still a ceiling because only one write operation can be performed at a time. When concurrent writes pile up, transactions start queuing, leading to slowdowns. This limitation makes SQLite less ideal for high-write workloads where multiple users frequently update data.
  • Some applications batch writes or offload them to a queue.

🔴 Scaling and Network Limitations

• SQLite was designed to be used locally, not over a networked filesystem like NFS or SMB. Doing so can cause locking issues, performance degradation, and even data corruption. Additionally, SQLite does not scale horizontally the way traditional databases do, as it lacks built-in support for distributed nodes and multi-writer replication.

However, various tools have emerged to extend SQLite’s capabilities:

• rqlite: Adds Raft-based distributed replication to SQLite, providing redundancy but still operating with a single-writer model.
• dqlite: Uses Paxos for clustering, allowing multi-node operation, but writes still funnel through a single leader.
• LiteFS: Optimized for distributed use cases while preserving SQLite’s simplicity, replicating SQLite instances while maintaining strong consistency.

These solutions help mitigate SQLite’s traditional limitations by adding replication and distributed capabilities, but they do not eliminate the single-writer model. While they enhance availability and redundancy, SQLite still operates with a single-writer architecture, meaning write contention can still be a concern in high-throughput environments. If high availability and true multi-writer clustering are key, a traditional database like Postgres or MySQL is still preferable.

Where SQLite Thrives

While SQLite has limitations, it excels in certain production scenarios—especially when you play to its strengths rather than forcing it to behave like Postgres.

✅ Read-heavy applications – Dashboards, analytics tools, and internal reports.
✅ Single-user or low-concurrency apps – Personal knowledge management tools (Obsidian, Logseq).
✅ Offline-first applications – Apps that sync periodically rather than requiring real-time transactions.
✅ Embedded and edge computing – IoT devices, serverless apps, or distributed services that benefit from a local, lightweight DB.

If you’re running a high-write, multi-user SaaS app, SQLite probably isn’t your answer out of the box—at least not without careful design and external tooling. However, if you optimize for its strengths, it can be a powerful component of your architecture. But if you’re building an app that can work offline, is read-heavy, or runs at the edge, SQLite starts looking very compelling.

---

2. Rethinking SQLite: From Limitation to Superpower

Here are four ways to start using SQLite today—even if you’re not replacing your main database.

🔹 Feature Flag Store

• Instead of Redis or a remote database, SQLite can store feature flags for fast, local lookups, even in a multi-user setup, as long as write contention is managed effectively.
• Works well for serverless environments where network calls to a centralized DB would add latency.

🔹 On-Disk Cache for Expensive Queries

• Have a query that runs too often but doesn’t change much? Store the result in SQLite instead of hitting Postgres repeatedly.
• Think of it as a structured Redis with persistence, providing a lightweight caching layer with durable storage.

🔹 Job Scheduling for Background Tasks

• SQLite can serve as a lightweight job queue storage for monolithic applications. While SQLite can persist jobs reliably, its single-writer model can introduce contention, limiting its effectiveness for high-throughput workloads.
• Example: Rails 8 supports Active Job with SQLite through Solid Queue, allowing background jobs like email sending or low-frequency tasks to be stored in SQLite instead of Redis or another relational database.
• Why it’s useful:
  • Reduces infrastructure complexity by eliminating the need for an additional queue storage system like Redis.
  • Works well for low-volume tasks that don’t require high concurrency.
  • Keeps everything self-contained within the monolith.

Limitations:

• Solid Queue works best with PostgreSQL or MySQL, which support FOR UPDATE SKIP LOCKED for high-throughput job processing. While it can run on SQLite, SQLite's single-writer model limits job execution concurrency, making it less suitable for high-frequency workloads.
• If job throughput becomes a bottleneck, switching Active Job’s backend to Postgres or MySQL is recommended for better scalability.

Many developers dismiss SQLite based on outdated assumptions, but its evolving ecosystem, new replication tools, and broader adoption suggest it’s worth another look. It may not be the answer to every problem, but for the right workload, it just might be the little engine that could.