Talks
Solid SQLite Apps on Rails
#ruby-on-rails
#sqlite
#database-performance
#activerecord
#background-jobs
#deployment
#developer-experience-dx
Summarized using AI
Solid SQLite Apps on Rails
by Stephen MargheimThe video titled "Solid SQLite Apps on Rails" presented by Stephen Margheim at BalticRuby 2024 focuses on leveraging SQLite as a resilient database solution for Rails applications in production environments. Margheim introduces the advantages of using SQLite beyond mere prototyping or local test environments, emphasizing its potential to enhance performance and reduce operational costs. He details a variety of practical strategies and tools that serve to optimize SQLite’s integration within Rails.
Key points discussed include:
- Performance Considerations: Margheim highlights SQLite's superior speed compared to PostgreSQL, showcasing benchmarks that illustrate performance improvements when using SQLite in production.
- Cost Efficiency: By using SQLite, developers can avoid unexpected cloud costs, as SQLite runs locally as a simple file on disk without usage costs.
- Development Flexibility: The simplicity of SQLite allows developers to create a database for each Git branch, simplifying testing and feature development.
- Production Readiness: Margheim stresses that deploying SQLite out of the box with Rails is currently not feasible and outlines three commands vital to transforming a non-viable Rails app into a production-ready one.
- Solid Libraries: He explains how to utilize Solid gems, such as SolidQ and SolidCache, that will become default adapters in Rails 8, outlining installation and configuration steps.
- Data Resilience: The speaker recommends using Lightstream for backup solutions, ensuring database integrity and point-in-time recovery capabilities.
- Deployment Strategies: Margheim suggests leveraging Hatchbox for deployment as an alternative to Heroku, ensuring ease of use when deploying SQLite applications.
- Community and Resources: The speaker invites his audience to explore further learning through his blog and an eBook that encapsulates the setup and optimization processes discussed during the talk.
In conclusion, Margheim empowers developers to effectively build and deploy solid SQLite applications on Rails, asserting that with proper configuration and attention to detail, SQLite can serve as a competent database solution in production scenarios.
00:00:07
It's exciting to see so many people here. This has been a fun first day of the conference, and hopefully, we will enjoy this last talk.
00:00:19
I want to talk today about building solid production-ready SQLite applications on Rails. Before we jump into the meat of the talk, let me quickly introduce myself.
00:00:33
Hi, my name is Stephen, and you can find me on Twitter at @fractal_mind. I am an American expat who moved to Berlin, Germany, five and a half years ago.
00:00:46
There, I met my wife, and we got married about two months ago. Thank you. It turned into a bit of an international adventure. We had some trouble navigating the bureaucracy of Germany.
00:01:01
So we ended up getting our marriage license in Denmark. However, none of our friends and family could join us in the Town Hall in the small town on the border of Denmark. Therefore, we planned a destination wedding in Jamaica, where we had the actual wedding ceremony.
00:01:28
But we are back in Berlin now with our two dogs, who I think bear a striking resemblance to the two dog emojis. Their names are Tuck and Ruby. The brown one is named after my day job, where I am the head of engineering at a company in Germany called test.io.
00:01:51
I spend a lot of my time doing open-source work in Rails, SQLite, and associated projects. Along the way, I write quite a bit on my blog, fractalmind.github.io.
00:02:18
What I want to talk about today is why I start all of my new Rails applications this way. Part of my responsibilities at work has led me to start and maintain a fairly large number of Rails applications in the past couple of years, many of which have been initialized just like this.
00:02:37
The first question I inevitably get when I tell people this or make this recommendation is, 'Why in the world would you do that? Isn't SQLite just for running your local tests quickly? Isn't it just for toy applications?'
00:02:51
You might use it to do a quick demo while screencasting. Why would you actually run a production application with SQLite? There are actually a number of reasons.
00:03:15
The first reason is hard to appreciate until you try it. I recommend you take some time to use some of the code we'll go through today and experience it. It's hard to appreciate how different it feels until you're actually using an application where your database is 600 times faster than what you might be used to.
00:03:37
These are some benchmarks run by Ben Johnson, the creator of Lightstream, which we will discuss a bit later. These benchmarks compare simple single-column writes against SQLite and PostgreSQL in various configurations.
00:03:53
Running PostgreSQL on the same machine as your application in the same availability zone, the performance differences are incomparable. When working with PostgreSQL, which is fairly common, we aim to run a properly sturdy, resilient Rails application.
00:04:11
If your application has a multi-region deployment, many of your application instances will be talking to PostgreSQL across regions. The performance differences are significant when you're just talking to data that is right next to your application.
00:04:29
But beyond speed, there are many other reasons to consider running SQLite in production. I imagine many of you, like me, have seen a growing number of stories on the internet about people waking up to startling cloud bills.
00:04:49
Living the nightmare of overspending, like waking up to a bill of $100,000, can be a painful experience. SQLite itself is free; you can't get much cheaper than that.
00:05:15
Beyond that, it is incredibly predictable and cheap to run since it's just storage. It's simply a file on disk, and it's quite straightforward to see what you're going to need. You can make predictions, you can over-provision, and it just runs on your own server.
00:05:40
It's not a managed service where you're paying for every single read and write. Oh my goodness, my application just became popular, and now I'm begging on Twitter to relieve my debt.
00:06:01
In general, if you're trying to bootstrap a project, the free tiers of managed database services are disappearing, as the economics have changed. It's difficult for these companies to sustain offering compute to thousands, tens of thousands, or hundreds of thousands of customers who aren't paying.
00:06:21
In fact, one of the last remaining generous free tiers in managed databases is Turo, which is managed SQLite. For bootstrappers, it's hard to have the cash on hand to spend $40 a month on a plan like PlanetScale.
00:06:46
In general, if you've been on the internet or Twitter, you've sensed a growing interest and conversation around running SQLite in production for web applications across the web development world. As more people explore possibilities, they appreciate that running SQLite in production can genuinely offer meaningful performance.
00:07:11
It might require a bit of tuning, but we'll get into that shortly. For Rails in particular, I think it is uniquely well-suited to the DNA of Rails. DHH, in his keynote at Rails World last year, reemphasized that Rails is at its heart a one-person framework.
00:07:28
It is designed to enable the smallest possible team, just you as a sole developer, product manager, marketer, or salesperson, to build feature-rich, economically viable, and valuable applications quickly and effectively. SQLite perfectly pairs with this vision of web development.
00:07:50
There are three key characteristics that make SQLite uniquely well-suited for the one-person framework.
00:08:06
Firstly, it is truly simple—it's literally a file on disk. Your database is a file on disk, and the database engine runs inside your application process.
00:08:19
Everything is contained within your repository, which means all the configurations are within your application, bringing a unique amount of control when everything about the database lives in your repository.
00:08:32
Since the database is a literal file on disk, it opens up unique developer experience possibilities, like having a different database for each Git branch you set up in your application.
00:08:49
Secondly, it's incredibly fast. When you transition from measuring queries in milliseconds to microseconds, there's a significant change—a real state change.
00:09:00
You start thinking differently about problems and possibilities. Many people have been genuinely surprised by the speed and the opportunities it provides for their applications.
00:09:13
Just as a quick sense here, by show of hands, who here is running or has run some application or service with SQLite?
00:09:20
Great! I see a few hands. That's amazing. What I hope to do for the rest of this talk is help the rest of us become more comfortable.
00:09:28
To a place where we feel confident that we could run a production-ready SQLite application on our own.
00:09:39
Now, I want to transition from why we might choose SQLite to the practical aspects: how do you build and maintain a production Rails application backed by SQLite?
00:09:52
I need to make a very clear point, so if you've been drifting in and out, come back. We need to be on the same page; this is important.
00:10:05
Running SQLite on Rails out of the box is not viable today. If you run the command 'rails new database:sqlite' right now, build out your features, and deploy to production, you're going to experience pain.
00:10:21
It won't feel good, and you'll think, 'What an ass is this Stephen guy? There are all these bugs, and this is not working at all!' Now, I will say it is my express goal to make this statement false for Rails 8.
00:10:40
We are close—there are two more pull requests that are almost ready to be merged. I believe we will get there. But as of right now, it is not viable.
00:10:56
So, I need to walk you through what it takes to make our Rails application production-ready, to make it solid. What do you need? What are the absolute must-haves to get your application into a production state?
00:11:09
Luckily, it's pretty simple. If you don't get anything else from this talk except one slide, let it be this slide. Take a picture, whether with your phone or with your mind. Whatever you need to do.
00:11:30
These three commands turn a non-viable Rails application into a viable one when you run SQLite. We'll go into more detail on what they do and why they do it, but the short version is: run these three commands, and things will be much better.
00:11:52
Now, let's talk more about what's involved, and let's go a step beyond what's just necessary. We'll cover five key points about optimizing performance in your Rails application.
00:12:03
We are going to discuss how to integrate the Solid gems, which will become the default adapters in Rails 8, what data resilience looks like and what we need, how we can enhance our application and developer experience, and what the constraints and possibilities are for deploying our application.
00:12:31
For the rest of this talk, there will be a lot of code snippets on the screen, and I won't be going particularly slowly or deeply into the concepts. Don't feel stressed; the slides will all be online, and you can reference them. There's no need to feel rushed.
00:12:54
Let's start with performance. There are five things that can take your Rails application from not very good performance to feeling really nice.
00:13:12
If anyone here happened to attend RAB earlier in the year, I gave a talk there where I went in-depth into these five points. I could not cover everything in this talk because it was an hour-long presentation.
00:13:31
If you want to learn more, we can talk about it later, or you can read the blog post, which features a lot of pretty pictures and covers everything here in detail.
00:13:45
The key point is that the first point is true by default in Rails as of version 7.1. That's nice, right? We want to get the first four of these to be true in Rails 8.
00:13:58
It will be a while until the fifth one is true, but the goal for Rails 8 is to have the first four true by default. Until then, the enhanced adapter will be giving you the ability to enhance the SQLite adapter.
00:14:17
These four performance characteristics and requirements will help you achieve a higher-performing Rails application backed by SQLite. All the details of what precisely it's doing and what code exists to facilitate this are in the blog post I have written.
00:14:39
If you want to have isolated connection pools, here's a brief explanation: in a typical Rails application, we set up our database YAML to talk to the production SQLite.
00:14:53
Rails will spin up a connection pool, usually containing five connections by default. SQLite has a restriction of only allowing one write operation at a time. While you can have functionally infinite read operations occurring concurrently, there's an issue with bottlenecking.
00:15:10
If you have one connection pool to your database, what happens when multiple requests come in, all attempting to write at the same time? Those requests will queue up, leading to a bottleneck and severely affecting performance.
00:15:34
To avoid this issue, we can isolate those connection pools. We can create one connection pool for read operations on the database and another for write operations. This strategy allows read and write operations to occur concurrently, reducing contention.
00:15:54
The isolation of connection pools is somewhat of an experimental feature, which is why the gem requires you to opt into it. It's a single configuration, which is all you need for performance.
00:16:08
It really is as simple as adding the enhanced adapter gem. Your application can go from being not so performant with lots of errors to being very performant with no errors.
00:16:23
There are many fascinating discussion points surrounding this topic, so if you want to have a deeper conversation, please feel free to approach me afterwards or read the blog post.
00:16:35
Now, let's turn to the Solid gems. These are the gems that will become the default adapters in Rails 8. We'll start by adding them and setting them up.
00:16:47
We need to install gems like SolidQ and SolidCache. Mission Control is the web UI, giving you observability into what's happening with your background jobs.
00:17:06
Once we've added the gems, we need to configure and set them up properly, including addressing common pain points when it comes to SolidQ.
00:17:16
The key detail we focus on is SQLite's nature as a simple, lightweight database. Typical documentation assumes you're working with one large database, like MySQL, for your application.
00:17:32
But instead, we want to avoid contention. We can use a separate database for each use case, separate from the application data and job data, which will boost our performance.
00:17:48
This separation allows each piece to work independently and efficiently while benefiting from SQLite’s operational simplicity.
00:18:05
To set this up, we need to declare a new database in our database YAML file, defining the primary database for ActiveRecord model data and a separate queue database.
00:18:17
This new configuration will involve specifying unique paths for each database and ensuring that they're recognized by Rails.
00:18:34
When we set up SolidQ, we need to use the environment variable to specify paths and to run the migrations for the new queue database.
00:18:49
We must ensure ActiveJob is directed to use this new separate queue database, configuring the appropriate settings for it.
00:19:04
SolidQ also comes with its configuration file in YAML, which allows you to set parameters relevant to your job processing.
00:19:18
This is crucial for running background jobs smoothly and verifying the setup, showing all ongoing jobs and their states.
00:19:35
Now, let's talk about SolidCache, which follows a similar setup process as SolidQ.
00:19:47
We also define another database for caching, which has its own migrations and setup processes.
00:20:00
We need to tell Rails that we want to cache during development and correctly configure SolidCache to point to that database.
00:20:14
Next, let's discuss SolidErrors. This gem is not a default feature in Rails yet, but I created it to fulfill a specific need.
00:20:32
It allows you to observe error reports easily by writing data into SQLite instead of sending it to a third-party API.
00:20:47
This is set up around a Rails engine so you can access and manage error reporting data locally.
00:21:00
To implement this, we'll need to create the necessary configurations and migrations to ensure everything runs smoothly.
00:21:12
Once you have this setup, running multiple databases in a Rails application becomes straightforward and allows flexibility in managing data.
00:21:35
The basic premise involves configuring the routes and views to appropriately interface with your error handling mechanism.
00:21:46
Data resilience is a critical part of application management, especially with SQLite. It is essential to have a backup strategy in place.
00:22:02
I recommend using a tool called Lightstream for its simplicity and ease of use, as it can stream rights to your SQLite databases.
00:22:23
Using such tools can help you set up point-in-time backups and manage your database's safety effectively.
00:22:39
Lightstream is installable via Ruby gems, making it easy to integrate into your application setup.
00:22:56
The installer will create configuration files that direct your backup settings. This allows for flexibility based on your operational needs.
00:23:10
The configuration will support all your SQLite databases, making it straightforward to manage your backup processes.
00:23:28
It's crucial to ensure your backup process is not only active but verified regularly to prevent any potential data loss.
00:23:44
To facilitate this, you can implement a method that verifies the integrity of your backups and checks for any disruptions.
00:24:02
Automating this verification process can afford peace of mind and allow you to sleep soundly, knowing your data is safeguarded.
00:24:18
The Lightstream gem interfaces directly with your Rails application, enabling you to execute backup operations and restore as necessary.
00:24:38
This process can be managed through Rake tasks, integrating with your deployment strategy to ensure everything runs in harmony.
00:24:51
Now, let's talk about enhancements. The enhanced adapter gem not only gives you improved performance but also introduces features not fully available in earlier Rails versions.
00:25:06
For example, deferred foreign keys are a critical feature in SQLite that allows for flexible timing in data relationships.
00:25:22
Additionally, virtual columns add powerful capabilities to your database management and querying experience.
00:25:36
SQLite supports a range of pragma statements that can be loaded directly from the database YAML, enhancing its capabilities.
00:25:53
Having comprehensive control over your SQLite setup through bundler allows for added customization for your application depending on your needs.
00:26:08
Database branches can alleviate many cumbersome issues that arise when developing with multiple feature branches.
00:26:22
By creating independent databases for each branch, you can manage migrations and tests without the fear of conflicting states.
00:26:38
Deployment requires careful consideration. Unfortunately, you cannot deploy directly to Heroku with SQLite across the board.
00:26:51
My preferred method of deployment is Hatchbox, which offers a hassle-free process similar to Heroku but without the overhead.
00:27:04
You can rent a VPS from different cloud service providers and manage your application deployment without significant additional complexities.
00:27:19
We’ve covered a lot of ground, and although I jest about your attention, it's evident that many of you are engaged.
00:27:34
If you want to delve deeper into this topic, I have an eBook that walks you through all necessary details to set up a production-ready SQLite application.
00:27:48
This book includes every command you’ll need to execute, making it a comprehensive guide to running SQLite effectively.
00:28:01
If you're interested in supporting my work, that would be greatly appreciated. My wife would especially appreciate that!
00:28:15
I hope you now feel empowered to explore and build solid SQLite applications on Rails in production effectively.
Explore all talks recorded at BalticRuby 2024
+4