00:00:31.920
As Fety said, my name is Stephen. You can find me on Twitter at @fractalmind. I am an American expat who moved to Berlin, Germany five years ago. About five weeks ago, I got married.
00:00:44.640
Thank you. It turned into a bit of an international adventure. We live in Germany, but getting a marriage license in Germany turned out to be functionally impossible for two non-citizens. So, we ended up taking the train up north to Denmark, and now I have a Danish marriage license. We didn't have any friends or family with us in the Danish Town Hall, so we also planned a destination wedding in Jamaica and took photos in New York. It was a fun couple of months.
00:01:26.360
As she said, for my day job, I am the head of engineering at Test IO. In my free time, I am an open source contributor and maintainer. Along the way, the work that I do involves writing on my blog. Today, I want to talk about why I start all of my new Rails applications with SQLite and have for the last couple of years. I want to specifically focus on why I choose SQLite as my database engine.
00:01:51.240
I want to suggest that for many of your projects, it would actually make sense for you to try to use SQLite as well. Of course, anytime I suggest this to a group of developers, the first question I get is, 'Why? Why should I use SQLite? Isn't that just for toy apps? Isn't that just for running local tests quickly?' I know for a fact that it is not just for toy apps, but I recognize that that's a fair question.
00:02:30.680
One of the things that I like to do as I talk to different groups of developers about this is take the opportunity to actually hear some of the reasons that you aren't using SQLite in production. Let's hear them out. Some common responses include: it doesn't scale, it has 'lite' in the name, how do you do backups, and you can't run it on Heroku. These are all really good points, and I'm going to cover a lot of them today. If I don't cover yours, let's talk afterwards.
00:03:08.519
Indeed, one of the most common responses I get is that I’m not using SQLite because it doesn’t scale, and it doesn't scale because it only supports linear writes. So, you can only have one write operation occurring in the entire database at a time. Logically, this cannot possibly scale. I want to address this point at the outset.
00:03:32.920
The specific thing that I want us to think about is how many linear write operations SQLite can perform in the same amount of time that we can perform one PostgreSQL write operation. Luckily for me, I didn't even need to do the benchmarking. Ben Johnson, the creator of Lightstream, which we will come back to, did this exact benchmarking for a conference talk that he gave at GopherCon in 2021.
00:04:00.599
He compared running a single SQLite write operation to running a single PostgreSQL write operation in three different configurations. What you see is that even when you're running PostgreSQL on the same machine as your application, you can run ten SQLite write operations in the same amount of time that you run one PostgreSQL write operation. The overhead of serializing and deserializing across that process is noticeable.
00:04:27.040
However, it's not common for most of us to build our applications today and self-host and self-manage PostgreSQL ourselves. The most common situation is that you're going to be running a cloud-hosted managed PostgreSQL service, and in those contexts, it’s quite likely that your application is going to be in a different cloud region than your PostgreSQL. If you have a multi-region deployment, definitionally some of those application instances are not in the same region. Even if you're in a neighboring region, the benchmark I'm observing looks at the latency from US East 1 to 2, and you'll see that you can run over 600 SQLite writes in the same amount of time that you have to make one PostgreSQL query.
00:05:14.199
So, absolutely SQLite does scale to a point. I'm not here to say that it's magic and you could build the next GitHub, but many of us don't need to build the next GitHub. So, why do I think it's worth taking the time to suggest using SQLite? I imagine that many of you, like me, have seen the growing number of news stories of people waking up to astronomical cloud bills.
00:05:30.600
While SQLite is free, which is amazing, it's also incredibly predictable what it's going to cost because it's just the cost of storage. You can easily see the size of your database and your usage patterns, which allows you to estimate costs. Vertical scaling is straightforward, which avoids the problem of relying on complex cloud providers. In general, when it comes to cost, these cloud-managed database providers are dropping their free tiers left and right. It’s getting harder and harder to bootstrap an application without having to pay for the database.
00:06:07.759
In fact, one of the few remaining generous free tiers in cloud-managed databases is Turo, which is cloud-managed SQLite. They’ve written extensively on why the unique nature of SQLite allows them to offer such a generous free tier. More and more people across the web development ecosystem are exploring the possibilities of using SQLite as a foundational part of the web application stack. As more people explore this, we are seeing more examples of individuals who can achieve meaningful scale for real business use cases with just a single VPS and SQLite behind their applications.
00:06:56.840
Whether they're writing JavaScript applications, Go applications, Ruby applications, or Python applications, in the Rails community though specifically, I think SQLite aligns really beautifully with the vision of Rails as the one-person framework. This was something that DHH reiterated in his talk just before lunch. This union makes the Rails ecosystem unique to truly take advantage of the full power and flexibility of SQLite.
00:07:31.599
For me, the primary reasons that I choose SQLite are that, firstly, it is simple. It is literally a single file on disk, and the database engine is just a single executable that runs inside your process. This is a mental model that I can fully understand and fully debug. To go back to like DHH's slide about boring tools, being able to know how something is going to fail provides safety, security, and speed in debugging. Simple tools allow for a lot of control.
00:08:01.840
This simplicity provides a unique degree of control when your database is simply a file on disk. It becomes possible to create a backup, SCP that backup to your local machine, and dig into production data anomalies in a fully featured but still fully sandboxed environment. That’s quite nice. You also have a single executable, and the tooling in Ruby with Bundler allows you to fully control all of the compilation flags of SQLite directly. You can have special compilation instances of SQLite for your application without any additional tooling.
00:09:30.760
The general simplicity of SQLite opens up some unique developer experience opportunities that we'll discuss more at the end. Of course, it goes without saying, but it is worth saying: it is fast. It's hard to appreciate until you play with it. Working with data that is right next to your application—same machine, same process—provides an experience free from network latency. Many of us have become accustomed to dealing with network request latency, and the absence of it can be a bit addictive. I do speak from experience.
00:10:32.040
So, that is why I think you should consider using SQLite. But I am curious: how many of us in the room have run or are running some kind of web application using SQLite in production? Show of hands. Yes, a couple. But not many.
00:10:57.160
I want to make sure that more of us feel comfortable and confident to actually run SQLite in production. So, I want to turn from the more theoretical to the pragmatic and let's talk about how we can build, deploy, and maintain a SQLite on Rails application.
00:11:03.240
Unfortunately, I have some bad news: it is not viable to take a 'rails new' application, put it up on a server, and walk away. You are going to experience pain; it is not production-ready. Personally, it’s one of my goals to make that true for Rails 8—that the 'rails new' out of the box experience is production-ready.
00:11:42.560
But as of today, in Rails 7.1, even the main branch of Rails that is not true. We’re going to have to do some tweaking and fine-tuning, but I hope to show you over the course of the next few minutes that it’s not too complicated; it’s not scary. What it gives you in the end is something that is indeed production-grade software with a great developer experience.
00:12:20.639
So, what does it take? Luckily, it's not much. Here’s a slide you want to take a picture of. I've got QR codes; these are the repositories and blog posts talking more about these tools.
00:12:29.840
This is the sum total of the requirements to have a production-ready SQLite on Rails application.
00:12:34.560
Okay, that is, one of these days I want to do a version of this talk and I will just stop there. But I recognize that probably a large number of you aren't yet fully convinced and are not really comfortable to say, 'Yeah, okay, I’ll just go home and spin this up.' So, let’s take a little bit more time and dig into some of the details.
00:12:51.680
What are these gems doing? Why are they doing them? What else is worth talking about? I want to start with performance. How do you make your application performant? How do we take advantage of the speed of SQLite? There are four must-haves and one nice-to-have. I won’t have the opportunity to go in depth on these.
00:13:21.880
But I presented this in detail about two weeks ago, and for anyone who might have been at that presentation, you would have enjoyed about an hour-long deep dive into these details. For everyone else, I extracted that into a blog post with all of the slides. I was honored and humbled to hear Javier say that it was excellent content. So, if it’s good enough for Javier, it’s good enough for you.
00:13:50.639
At a high level, what you need to ensure is that you’re using write-ahead logging mode. As of Rails 7.1, that is the default, so you don't have to do anything for that. The next three are what the enhanced adapter provides: immediate transactions, a timeout mechanism that isn't blocking Ruby's Global VM lock, and a fair retry interval.
00:14:10.920
The last point is a nice to have. This is an experimental feature I’ve been working on to isolate the connection pool, ensuring that write operations don’t saturate your connection pool and block read operations.
00:14:33.680
Unfortunately, I don’t have the time to dive deep into them. There's a lot of depth to explore, and I explain all of the problems and all of the solutions in my blog post. If you want to talk more about it, come and talk to me later. Luckily, it’s simple to get all of these things; the enhanced adapter gem automatically injects the configuration into your Rails application.
00:15:02.800
It’s literally as simple as 'bundle add' and you're done. If you want to experiment with isolated connection pools, that is an opt-in configuration. Just throw this in your application.rb, and you’ll get that. This will give you the full extent of performance as I know how to achieve it with those two lines of code.
00:15:21.960
Now let’s discuss resilience. It’s not just enough to have an application that runs quickly; you need to make sure that your data is backed up. I say this as someone who has accidentally deleted their production SQLite database before.
00:15:47.799
For about two weeks, I kept thinking, 'I need to add backups. I need to add backups.' Then, I accidentally deleted it. It was an unfortunate day, so this is something you should really do on day one. That's why I've tried to invest time in making it as simple and easy as possible. There are several tools, but I genuinely think that the best tool for the job is Lightstream.
00:16:05.680
Ben Johnson, who created Lightstream, is one of the experts on SQLite, and if you’re interested in this topic, he's really worth following and reading. It is a single Go executable that streams your changes to your database to an S3-compatible storage service. You can then use the CLI to restore a backup onto any machine.
00:16:19.960
Since it’s just a single executable, I wrapped it up in a Ruby gem, similar to how the SQLite3 Ruby gem works. So, you can use 'bundle add lightstream,' and it will compile it for your platform. In order to use it, there’s an installation command. You only need one command to install the gem, but still two commands to set it up.
00:16:37.920
As always, I have QR codes for more information. The installer will create two files: the first is the configuration file for the Lightstream utility itself. It uses YAML configuration, and what the installer will do is introspect your database configuration, find all of the SQLite databases in your production environment, and write a Lightstream configuration that backs them up.
00:16:55.560
If you've got a database for Active Record, a database for Sidekiq, and a database for Action Cable, it will back up all of them. It uses environment variables to keep your secrets secure. To wire everything together, it will create an initializer file, allowing you to configure those details in Ruby.
00:17:14.319
The default example in the initializer would typically pull values from credentials, but you can structure it however you want. With those two files, you have everything needed to get Lightstream up and running, but you do have to turn it on.
00:17:33.319
The gem provides you with a Rake task that maps directly to the CLI commands. This is helpful because it binds the Ruby configuration with the YAML configuration. It’s all about making sure everything runs smoothly. This process runs as a separate instance, so by default, the gem expects you'll add this command to your Procfile. If you're not using a Procfile, just throw this command wherever you manage your processes.
00:18:02.360
Replicating your database is a nice start, but we also need to discuss how to restore a database. That’s another simple Rake task where you pass in the database path, which matches something in your configuration. You can run this command on the same machine where you're replicating or even from another machine, including your local laptop.
00:18:40.960
It provides an easy mechanism to pull production data down without having to do an SCP. I was thinking, is this sufficient? Would I want something more? Yes, I would want something more. It’s not just enough to restore; I want to verify that my backups are working.
00:19:01.000
I want to know that I have up-to-date backups, and I can restore them as needed. So, I added a new Rake task. This is not one of the Lightstream commands; this is specific to the gem. You call this task with the database path, and it will pull down the most recent backup and verify it.
00:19:29.600
It will output details such as the size in bytes of the original database and the restored copy. It’ll show the delta and the number of tables. I believe there are additional things to add, and I am currently working on a configuration option so you can define custom logic to verify a backup, too.
00:20:00.680
This is a significant step towards tying this process into your routine. You might even create a recurring Sidekiq job that runs this verification each day or week and sends you reports. This way, you can sleep well at night knowing your backups are not just present but also restorable.
00:20:30.960
However, I don’t think that’s entirely sufficient. I want to quickly and clearly see in my Rails application whether things are running smoothly. I want a little web dashboard.
00:20:47.400
I want to see Lightstream's process status, the PID, and information about all the databases it's backing up. To achieve this, I created a simple web page that aggregates all of the information Lightstream provides and maps it onto a dashboard within Rails.
00:21:08.679
You have the freedom to secure this route however you want, but it’s quite nice to check the status of everything Lightstream knows about at any specific point in time.
00:21:35.760
Resilience is taken care of; now let’s talk about enhancements. There are several ways to enhance the experience of using SQLite with Rails.
00:21:50.000
The enhanced adapter gem, as the name suggests, includes many features. Beyond performance improvements, it also brings many SQL features into the Active Record adapter. Some of these features are already in Rails 7.1 and others are in the main branch.
00:22:13.680
One of the features is deferred foreign keys, which is available for PostgreSQL and MySQL adapters. The SQLite syntax is supported, and the Active Record adapter supports it as well.
00:22:39.320
Another feature I love is virtual columns. SQLite supports both stored and unstored virtual columns. While this feature is in the Rails main branch, it isn't in a point release just yet. For those of us familiar with it from PostgreSQL, we agree it’s a valuable addition, and I can't wait until it's officially available.
00:23:06.720
Moreover, you have the ability to specify custom returning values. This feature drives auto-hydrating virtual columns, so when you run 'create' commands that include virtual columns, the Active Record object will return those values.
00:23:36.760
Additionally, you'll be able to specify any of SQLite's configuration pragmas directly in your YAML file. You can also list any extensions you want to load, using an 'extensions' array provided by the enhanced adapter gem.
00:24:06.160
In addition to these enhancements from the gem, the Rails ecosystem is moving toward handling persistent data needs within the database. This applies to tools like Sidekiq and possibly future developments as well.
00:24:38.960
In applications using PostgreSQL with Active Record, it might make sense to include Sidekiq and back those jobs with a SQLite database. You can mix and match based on your needs; it’s not an all-or-nothing situation.
00:25:06.760
This creates valuable use cases for SQLite. Personally, I decided to go all-in because I'm looking to minimize costs while mastering a small number of tools, but that's not a requirement. The goal is to have a cohesive ecosystem where all components work together.
00:25:46.440
You can control the compilation settings of SQLite, which aligns with its recommendations. A note worth reiterating is that the SQLite team prioritizes backwards compatibility over hot new features. They want to ensure you can still run a database created decades ago.
00:26:31.680
Though this might be annoying at first, it’s reassuring knowing that you can upgrade without breaking existing functionality. Their commitment to thorough testing further enhances reliability, which is a key part of verifying the software's longevity.
00:27:03.200
If you want to read more about the SQLite3 gem and its compilation settings, I've also discussed this in my blog post.
00:27:22.679
One enhancement I find particularly useful is the concept of branch-specific databases. Many of us have worked on long-running feature branches, only to find ourselves in messy situations when colleagues need reviews.
00:27:44.279
It’s much easier to work with entirely isolated databases for each Git branch. I was amazed at how simple it was to set up. By piping the database YAML through ERB, we can dynamically name our database according to the current branch.
00:28:09.200
The Active Record provides programmatic access to tasks, enabling you to ensure that the database is migrated and seeded properly for use. This has been incredibly helpful for teams working on shared applications.
00:28:35.920
It only takes a couple of lines to implement, and I’m sure many more opportunities will arise as we explore this space further, finding creative ways to incorporate SQLite into our applications.
00:28:55.200
Finally, let’s touch on deployment. It's worth clarifying that, as we heard earlier, you can’t run a SQLite application on Heroku due to their ephemeral storage.
00:29:20.960
If you store your database there, you're going to experience significant pain within 10 to 24 hours. Alternatives include newer services like Render or Fly.io. Personally, I use Hatchbox, which I find incredibly powerful and easy to use.
00:30:00.760
I have a detailed blog post explaining how to deploy a SQLite on Rails application on Hatchbox if you want to check that out. Also, as mentioned yesterday, there’s the new Kamal tool that Joseph discussed.
00:30:31.079
I personally haven't used it because I've found my SQLite on Rails applications work seamlessly with cross-platform reproducible builds using Bundler and Ruby gems. I don't require Docker for additional dependencies.
00:30:50.319
For those already in the Docker world, many resources discuss how to deploy SQLite Rails applications using Docker. But that's really all I have to say on deployments.
00:31:06.560
We’ve covered performance, resilience, enhancements, and deployment. As far as I know, those are the four key pillars of running a production application. If I've missed anything, please let me know. I’d love to expand on this topic and make this talk even more informative next time.
00:31:50.440
I hope that those who managed to stick around have better insight into why DHH might emphasize using SQLite for certain use cases. If you don't need truly global scale deployments, and if you only need, say, 500 requests per second, then SQLite on Rails can serve you well. It’s likely to be cheaper, easier to build, maintain, and deploy.
00:32:39.360
While it's not currently viable to take a Rails application and just deploy it into production without some adjustments, it can be simple. It can amount to just a few CLI commands and some additional tweaking to create a fully functional application.
00:33:08.400
You could run such an application on a $15 a month VPS and drive real business value from it, as R explained. If that's not where you are at this point, that’s totally fine as well—everyone's approach varies.
00:33:28.600
Thank you very much once again for your attention!