Supercharge the One Person Framework with SQLite: Rails World 2024

NOTE: This is an edited transcript of a talk I gave at Rails World 2024. You can watch the full talk on YouTube.

From its beginning, Rails has been famous for being a kind of a rocket engine that could propel your idea to astronomic heights at supersonic speed. But, at least for me, it has lately felt like I needed to be a rocket scientist to then deploy and run my full-featured application.

And that is because just as, over time, rocket engines have grown larger and more complex,

So too has the “standard” web application architecture only grown more complicated with time.

Let’s consider a typical Heroku application architecture from, say, 2008. It would have had some web dynos to respond to HTTP requests, a database to persist data, and some worker dynos alongside a job queue. Sure, it’s a distributed system, but it is manageable enough.

Well, this is a standard Heroku application today—where the number of servers and services has basically tripled.

And I’m sure many, if not most, of you see this and think that this is simply the cost of running production-grade systems these days. It is easy enough to believe that complexity is the price to pay for progress.

But, not always. Sometimes progress is simplification. Sometimes progress is reducing the number of moving parts, shrinking the surface area of the system, yet still expanding its power and functionality.

My name is Stephen Margheim, or you might know me as @fractaledmind on Twitter, and today …

… I want to show you how Rails 8 and SQLite together supercharge Rails as the “one-person framework”.

I want to offer a vision of building applications that have all of this power,

but are this lean and this simple.

Because, believe it or not, this is now a viable application architecture, and one that can serve tens of thousands of users, if not many, many more.

You see, just like liquid methane and liquid oxygen in a rocket, SQLite and Rails—when mixed—form a potent combination.

This is because Rails, with 2 decades of battle-tested solutions extracted from production applications, provides unparalleled conceptual compression, while SQLite, with its single file database and embedded executable, provides truly unique operational compression.

And together they compress an explosive amount of power into a remarkably lean and simple application. Let me show you how.

To begin, we need to understand how Rails 8 makes the rails new command production ready.

Rails has always been a “batteries included” framework that ships with a wide range of components.

And many require some form of persistent data.

These are the data-bound components of Rails, and a full-featured application will need some data stores to back each of them.

Of course, each component has multiple adapters for various data stores.

With version 7 of Rails, when you run rails new, these are the production defaults you would get for each. Nothing offensive here, but this also isn’t a plug-and-play production setup.

The async adapter for jobs is fast and simple locally, but if you run it in production, you will lose pending jobs on deploys or restarts.

The file store for caching works well enough if you control the file system, but many platform services, like Heroku, only provide ephemeral file systems. And a cache isn’t nearly as helpful when its contents are completely wiped at random.

And the Redis adapter for Action Cable is certainly production-ready software, but it does require you to have a Redis instance configured, running, and connected to your application. So, production-ready, but not plug-and-play.

Rails 8 makes major changes to ensure that the out-of-the-box experience is both production-ready and plug-and-play.

With version 8, when you run rails new, you get a notably different set of defaults. The suite of Solid gems provide production-ready, flexible, and scalable defaults for the central data-bound components of Rails.

These new gems offer database-backed, but database-agnostic adapters for Active Job, Active Support Cache, and Action Cable.

Rosa’s talk outlined what makes Solid Queue production-grade software.

And at last year’s Rails World, Donal introduced Solid Cache, detailing its thoughtful and production-oriented design.

Finally, Nick Pezza has done great work getting Solid Cable off the ground.

But, Rails 8 being production-ready isn’t only about these new defaults for Rails components.

Rails 8 also comes with a new and improved out-of-the-box solution for production deployments…

with Kamal. Kamal offers everything you need to deploy and manage your web app in production with Docker.

You can learn more about the new version of Kamal, version 2.0, in Donal’s Rails World talk.

and Kevin introduced the brand new Kamal Proxy.

I think you should learn what the future of deploying and running Rails applications looks like.

All in all, Rails 8 provides the tools you need to go from the “hello, world” of the rails new command to the “hello, web” of a production application.

These are the features and details that make Rails “the one-person framework”. As David outlined in his keynote last year, Rails aims to be a bridge over complexity that allows even the smallest possible team—just you and your laptop—to build full, rich, valuable web applications. And SQLite aligns perfectly with that vision.

I like to say that SQLite supercharges Rails.

Because SQLite enables you to go from the moderate complexity of a minimally distributed system to the radical simplicity of an application and all of operational dependencies living on a single machine. And this is because of SQLite’s unique architecture…

Most SQL engines run in a separate process from your application, even typically on separate machines.

SQLite runs embedded within your Ruby process and the thread that spawns it.

So, it’s not a database in the way you might be used to.

It is just a file on disk and an executable embedded in your application process. Nonetheless, it is a full-featured SQL engine, with CTEs, window functions, aggregations, and the like.

And this is stable software. SQLite’s current major version, version 3, was first released 2 decades ago in 2004.

And today there are an estimated 1 trillion active SQLite databases around the globe, making SQLite the single most used database in the world.

Now, I’ll forgive you if you presume that an embedded database that stores data in a single file can only handle a tiny fraction of the amount of data something like Postgres or MySQL could handle.

But you would be completely wrong. SQLite can handle database files up to 281 terabytes. Or, to put it otherwise, you won’t hit its computational limits. I promise.

Pair that power and simplicity with modern hardware, and you are hopefully starting to appreciate how SQLite enhances the vision of Rails as the one-person framework, enabling us to radically simplify our application’s operational needs.

But, as I say this, I know that we have had years and years of people consistently saying…

that SQLite simply doesn’t work for web applications.

Indeed, for the last few years, Rails would log this warning if you ran SQLite in production.

Hell, the last time you read the Rails Guides, they warned you against using SQLite in production. But, this is an antiquated point of view.

The reality is that, today, this sentiment is a myth.

And more and more people are realizing it.

But, this myth does have foundations; because SQLite in the context of web applications is easily misunderstood and misused.

You see, unlike much of modern software, SQLite cares more about backwards-compatibility than it does about enabling newer, better features by default. The maintainers care deeply that a database file created 2 decades ago can be opened by SQLite 2 decades from now. This is a key reason why, among other things, SQLite is the storage format used for digital data by the United States’ Library of Congress.

So, for all intents and purposes, when you use SQLite today without any tweaking you are effectively using SQLite as it was configured when first released in 2004. And in 2004, SQLite was not well suited to being run in a web application.

But, over the last 2 decades, SQLite has added many features that make it suitable for web applications. All that is needed is to fine-tune SQLite’s configuration and usage.

And that is precisely what myself and the Rails Core team have been doing over the course of the last year.

This work culminating in Rails 8.

Now, many of these improvements are inherited from 7.2, but there are two additions coming with Rails 8 that merit closer consideration. Because these changes make Rails 8 the first version of Rails (and, as far as I know, the first version of any web framework) that provides a fully production-ready SQLite experience…

out-of-the-box.

There were two issues that hindered the default experience of SQLite on Rails up ’til now. The first problem was that as your application was put under more and more concurrent load, a growing percentage of requests would error out. Even at just 4 concurrent requests, nearly half of your responses would be errors. Obviously, utterly unacceptable for production.

The second problem related to the application’s tail latency under increasing concurrent load. You would see your p99 or even your p95 latency skyrocket. Again, when some requests are taking 5 plus seconds to respond, this is completely untenable for real applications.

Both issues arise from the nuances of using an embedded database in a multi-threaded web application. Rails spins up multiple threads to process incoming requests, and each thread has its own embedded connection to the SQLite database.

Rails must ensure that those connections don’t conflict with each other.

And that they don’t block, waiting for each other.

Ensuring that the embedded connections don’t conflict was solved with this change to how Rails constructs transactions for SQLite.

And ensuring that they don’t block was fixed with a pair of PRs. One in the sqlite3-ruby gem, which is the lower level interface between Ruby and the SQLite engine.

And then one in Rails to make use of this new feature in the driver.

I don’t have the time today to dig into the technical details, but if you are interested in learning more, I have an in-depth post on my blog that walks through every detail of these changes—the nature of the problems, the reasoning behind the solutions, and the details of the implementation.

But details aside, the results speak for themselves. Not only do we no longer see errored responses…

but our p99 latency is literally improved by an order of magnitude under heavier concurrent load.

In fact, we can see the improvements hold steady even in the very slowest requests in our benchmark.

These configuration and usage changes require nothing of you in your application code. And they unlock the full power and speed of SQLite, which now makes SQLite a fully viable production option.

You can now back 4 out of the 5 data-bound components of Rails with SQLite, without compromise.

And this is precisely what unlocks this architecture as a viable option. A full-featured Rails application, with no compromises on features or performance, all running on a single machine.

Now, I recognize that this is a fairly radical suggestion. I expect many of you are thinking right now that this architecture cannot possibly serve production workloads. So, let’s investigate.

I want to start with using the Campfire app released earlier this year by 37signals. And I want to start here because the team at 37signals not only built a full-featured, production-grade application, but they also shipped it with a realistic load-testing tool built in.

Now, admittedly, Campfire did not go all in on SQLite. Yes, SQLite is the database engine that backs Active Record, but jobs, cache, and web sockets are all driven by Redis.

But, Rails is modular, and so I simply swapped those adapters out. I reconfigured Campfire to use Rails 8, the Solid gems, and back everything with SQLite. And, as a side note, it only took me an hour or so.

Then, I ran the load tests for both the standard build of Campfire and my “Campfire on SQLite” fork.

And the results, once again, speak for themselves. Whether looking at the number of connections spawned…

or the number of messages received, the Campfire on SQLite fork performs just as well as the standard Campfire build.

In addition to this load testing, there are also existing Rails applications built on this stack running today, like Ruby Video by Adrien Poly. This app runs on a single $4 per month Hetzner box, with SQLite backing its IO needs, and it has served millions of requests with an average response time of less than 100 milliseconds and 4 nines of uptime.

There are also the applications I ran in production with nothing but SQLite on a single machine for years at my previous company. Unfortunately, they are proprietary applications connected to NDAs with massive companies, so I can’t share details or screenshots. But I can say that these applications are still running smoothly, have driven millions of dollars in revenue, and have never had a performance complaint.

Of course, tech Twitter has been chatting incessantly recently about Peter Levels’ setup, because he runs multiple successful applications, all backed by SQLite, with a single beefy VPS.

And, as a reminder, single server production deployments have served Rails applications well since day one.

So, yes, it is actually possible to run full-featured Rails applications in production, with no compromises on features or performance, all on a single machine.

So, I hope you trust me when I say, this idea is a myth. And next year, hopefully some of you here today will have started projects and been running them successfully for a while to add even more evidence.

But, this isn’t some silver bullet. There are use-cases where this architecture shines and those where it doesn’t. And there are areas where you do need to be more considerate.

When running SQLite in production, you need to have a solid backup mechanism setup. And I say this as someone who has accidentally deleted the production SQLite database. Trust me, resilience is something you should have setup from day one.

I think the best tool for the job is Litestream. The Litestream utility allows you to stream every update to your SQLite database (or databases) to any of a number of bucket storage systems, or even an FTS server. So, you get point in time backups and incredibly cheap storage costs.

Since it is only a single Go executable, I have wrapped it up in a Ruby gem to make installation a breeze. The gem also uses a Puma plugin to manage the replication process, so it is truly a plug-and-play solution. It even ships with a verification job that you can schedule to run regularly to ensure that your backup process is continuously running smoothly.

Aside from data resilience, probably the most common worry I hear centers on the fact that SQLite only currently supports linear writes.

The worry is that only having one write operation at a time will prevent your application from “scaling”, whatever that means. But, this worry is overblown. Firstly, most applications are read-heavy, not write-heavy. So likely only around 20% of your traffic is writes. Plus, we forget what a difference using an embedded database makes on performance.

Even if you run Postgres on the same machine as your application, you can execute 10 SQLite queries in the time it takes to run one Postgres query. But, web applications are generally moving away from self-hosting and managing their own database server, and cloud databases are more popular than ever. When using a cloud database, there is a good chance that your database server will be in a different region than you application server. In this case, even if the regions are neighbors, the increased latency means that you can run nearly 600 SQLite queries in the same amount of time as running one Postgres query.¹ When you go from a client/server database architecture to an embedded database, your queries go from being measured in milliseconds to microseconds!

Unless you are ingesting a lot of data, and by “a lot” I mean on the order of 50,000 writes per second, I promise you that this aspect of SQLite’s architecture will not have a meaningful impact on your application.

But, it does mean that you should be thoughtful about migrations.

If you have a long and write-intensive migration—like adding a new index to a table with millions of rows—that migration will impact your application’s performance. There currently are not popular, battle-tested tools to get around this limitation, so be aware that such migrations will require scheduled downtime.

The next detail to be considerate of is that SQLite is built to work best on a single machine. And this means that as you need to scale, vertical scaling is your best bet. Expanding the size of that single machine as needed.

Now, there is a fair chance that many of you have some out of date presumptions about the size of machine that you can rent and run using off-the-shelf providers like Digital Ocean or Hetzner or AWS.

Because you can get a big box.

Like, a really really big box. You can rent a VPS from Hetzner with 48 cores, 192 gigs of RAM, and 1 terabyte of NVME SSD space. All for no more than 350 dollars a month. That is a beast.

So, don’t put an artificial ceiling on how far vertical scaling can take an application.

But, I know that we have all been told for the last decade that the only “correct” way to build web apps is with redundancy,

and high availability,

and automatic failovers,

and zero to infinity auto scaling, and all the rest.

The fact, though, is that these are solutions to problems that a minority of applications on the internet do have or will ever have.

And they come with real trade-offs around operational complexity. Remember, the larger the surface area of your system, the more opportunities for failures.

So ask yourself, do I truly need all of that, especially on day one, or should I start with a stack simple enough to keep in my head, yet powerful enough to serve my customers’ needs.

So, like any tool, SQLite does come with tradeoffs. There is no perfect tool, no perfect stack, no set of decisions that require no additional considerations. You must always learn your tools, their quirks and idiosyncrasies, if you want to make maximal use of them. But, when you choose high-leverage tools, tools that are well-built and well-known, tools that might be considered boring, you unlock the power of simplicity.

You truly can, today, build an application that has the power to take your next idea to Mars. But you don’t need to be a rocket scientist to run it. Rails 8 and SQLite strip away the incidental complexity…

leaving you with the leanest, simplest, most powerful application stack imaginable. And few things are as powerful as those tools that have earned their simplicity through years of evolution and consideration.

These are the kinds of engines that empower individuals to build production-grade, full-featured, valuable applications faster, simpler, and cheaper than ever before. These are the tools that enable a “one person framework”. And whether you are an individual, a small team, or even a large team—these are the tools that provide you the leverage and power to launch.

SQLite and Rails are unique pair, a powerful pair. And I hope that after this exploration you better understand and appreciate how well SQLite pairs with Rails as an engine for creativity and building. And, I hope that you now feel confident that you can, and maybe should, build and run your next Rails application with SQLite.

Thank you.

This is an edited transcript of a talk I gave at Rails World 2024. You can watch the full talk on YouTube below.