SQLite on Rails 8: Building Production-Ready Apps with Radical Simplicity

Stephen Margheim - Rails World 2024

The vision for Rails, as recently outlined, is ambitious: a framework powerful enough to launch an idea from "Hello World" to IPO. Yet, for those embracing the "no-ops" mindset, deploying and running an application in production has often felt like a task for a rocket scientist.

This perception exists for a reason. The standard architecture for a web application has grown immensely complex over the last 15 years, evolving from a manageable distributed system into a tangled web of services and servers. We've accepted that this complexity is the inevitable price of progress.

But What If Progress Is Actually Simplification?

True progress isn't always about adding more. Sometimes, it's about removing moving parts, shrinking a system's surface area, and yet somehow expanding its power and functionality. This is the potent combination offered by Rails 8 and SQLite.

When mixed, they form a powerful engine for the "one-person framework." On one side is Rails, with two decades of battle-tested solutions extracted from real production systems. On the other is SQLite, with three decades of refinement towards an operationally minimal, embedded database—just a single file and an executable running in your application process.

Rails 8: The New Defaults Are Production-Ready

Rails has always been a full-stack framework. A full-featured application needs a data store for its core, data-bound components: Active Job, Action Cable, and caching.

In Rails 7, the production defaults for these components were not truly "plug-and-play" for a serious production environment:

• The async adapter for jobs would lose jobs on deploys or restarts.
• The file system adapter for caching is unreliable on ephemeral file systems (e.g., Heroku).
• The Redis adapter for Action Cable is production-grade but requires you to configure and manage a separate Redis instance.

Rails 8 changes this entirely. The new out-of-the-box experience is both production-ready and plug-and-play, thanks to a new suite of solid, database-agnostic gems:

• Solid Queue for Active Job
• Solid Cache for Active Support caching
• Solid Cable for Action Cable

These gems provide a solid, scalable, and flexible foundation for the core components of a modern Rails app. Coupled with tools like Kamal for deployment, Rails 8 provides everything you need to go from `rails new` to "hello web."

How SQLite Supercharges the One-Person Framework

SQLite is the perfect match for this vision of simplicity. It enables a radical architectural shift: moving from a minimally distributed system to a single machine running your entire application and its operational dependencies.

This is possible because of SQLite's unique architecture. Unlike client-server databases, SQLite is embedded. It runs within your application's process—it's not a separate process or machine. It's just a file on disk and an executable.

Don't mistake its simplicity for a lack of power. SQLite is a full-featured SQL engine with support for CTEs, window functions, aggregations, and more. It's incredibly stable; the current major version (3) was released two decades ago. Today, it's estimated that there are over 1 trillion SQLite databases in active use, making it the most widely deployed database in the world.

Its limits are also often misunderstood. A SQLite database can grow to 280 terabytes. For the vast majority of applications, you will not hit its computational limits, especially when paired with modern hardware.

Dispelling the Myth: SQLite in Production

For years, the Rails guides explicitly warned against using SQLite in production. This sentiment persists but is now outdated. The myth originated because SQLite, by default, prioritizes backwards compatibility over newer features. Out-of-the-box, it runs in a mode suited for its inception 20 years ago—not for modern web apps.

The truth is, SQLite has evolved. Over the last two decades, it has added numerous features that make it suitable for production web applications. It simply requires fine-tuning its configuration and usage—work that has been the focus of the Rails core team and community for the past year and a half.

Two main issues previously hindered SQLite on Rails:

1. Concurrency Errors: Under load, a growing percentage of requests would simply error out.
2. Skyrocketing Tail Latency: The P99 and P95 latency would become unacceptable under concurrent load.

These issues were rooted in the nuances of using an embedded database in a multi-threaded web server. The solutions required changes to how Rails manages transactions for SQLite and enhancements to the low-level `sqlite3` Ruby gem to prevent connections from blocking each other.

The results are dramatic. With the changes in Rails 8, error rates drop to zero, and P99 latency improves by a factor of 1000, even under heavy concurrent load. These improvements require no changes to application code and finally make SQLite a viable, production-ready option.

Proof in Practice: It Really Works

This isn't just theoretical. Load testing the Campfire application (from 37signals) after swapping its adapters to use the Solid gems and SQLite showed identical performance to the original build, which was capable of handling 50,000 concurrent users.

In the real world, applications are already running successfully on this architecture:

• The Ruby Video app runs on a single $4/month Hetzner server, has served millions of requests with <100ms average response time, and maintains four nines of uptime.
• Peter Levels runs multiple successful applications on a single, beefy VPS backed by SQLite.
• The speaker has run proprietary applications for three years, driving millions in revenue, with no operational complaints.

Considerations and Tradeoffs

This architecture is not a silver bullet. It shines for many use cases but requires consideration in others.

1. Backups and Data Resilience

You must have a solid backup mechanism. The recommended tool is LiteStream, a utility that streams database changes to an S3-compatible bucket. A gem provides a pre-compiled binary and a Puma plugin for easy, plug-and-play setup, including verification jobs to ensure backups are working.

2. Linear Writes

SQLite currently supports only one writer at a time. This is often overblown as a limitation. Most applications are read-heavy (~80% of traffic). Furthermore, the speed of embedded queries (measured in microseconds) compared to network-dependent client-server queries (milliseconds) mostly erases the perceived bottleneck.

However, write-intensive migrations (e.g., adding an index to a massive table) will block the application and likely require scheduled downtime. There are no widely adopted online migration tools for SQLite yet.

3. Scaling Vertically, Not Horizontally

SQLite works best on a single machine. Instead of horizontal scaling, you scale vertically. The ceiling for vertical scaling is much higher than often assumed. For example, Hetzner offers a 48-core, 192GB RAM, 1TB NVMe server for ~$350/month—capable of handling significant traffic for most applications.

Embrace the Power of Simplicity

We've been told for a decade that the only "correct" way to build web apps is with redundant, highly available, auto-scaling infrastructure. These are solutions to problems that most applications will never have, and they come with a massive cost in operational complexity.

A larger system surface area means more potential points of failure. For many projects, especially at the start, a simpler stack that fits in your head is not just adequate—it's advantageous.

Rails 8 and SQLite strip away incidental complexity, leaving you with a lean, simple, and incredibly powerful application stack. It’s an engine that empowers individuals to build production-grade, valuable applications faster, simpler, and cheaper than ever before.