Forem: Stephen Margheim

ActiveRecord adapter improvements

Stephen Margheim — Fri, 22 Sep 2023 15:53:31 +0000

Ruby on Rails continues to be a lively and thriving framework. Unfortunately, when it comes to the database adapters, a vast majority of the attention and effort has been focused on the MySQL and PosgreSQL adapters. SQLite supports a large percentage of the database features that Rails has added support for in the recent past. So, today I am starting to do my part to make the developer experience of using Rails with SQLite as seamless and powerful as possible. Maybe you'll join me?

Today I opened my first few pull requests to begin improving Rails' SQLite3Adapter:

And this blog post is my personal declaration of intent—I am going to do my part in bringing as many of the newer ActiveRecord features to the SQLite adapter. From composite foreign keys to virtual columns, with auto-populated columns thrown in, SQLite will no longer lag behind PostgreSQL and MySQL.

But, this is no simple task, and I'm certain there are many more features beyond the ones I have bumped into. So, this post is also a call to action. If you are a SQLite and Rails enthusiast, join me! Let's start leveling up the SQLite3Adapter together. Because one step at a time, we can help surge the tide of SQLite in production usage for Rails applications.

That's it for today. But be on the lookout for a post on how supporting Rails' RETURNING feature opens up the possibility for UUIDs or ULIDs as primary keys.

Local snapshots

Stephen Margheim — Fri, 22 Sep 2023 15:49:45 +0000

Today we consider how SQLite can enhance working with our database in our Ruby on Rails applications. The the database is simply a file, snapshots and clones are both simple and powerful.

When working on a web application, there are various tasks you will have at some point that involve your database. You will want to take a snapshot of your database. You will want to restore your database to a previously saved snapshot. You will want to clone your production database locally. You will want to merge your production data into your existing local database. You will want to merge data from another, branch-specific databases into your current branch's database. You get the idea. When working with database engines that run in a separate process, often on a separate computer, these tasks can be somewhat cumbersome, if not practically impossible. Sure PostgreSQL has pg_dump and pg_restore, but I wouldn't call this straight-forward:¹

pg_dump -U postgres -h localhost -p 5432 -F c -b -v -f /dev/null DB_NAME 2>&1

With SQLite, each of these actions is, in my opinion, quite straight-forward. So, let's walk through them one by one and see for ourselves.

{:.notice}
Note: All code will be in a Rake namespace, as we will eventually be preparing a task file to put in our Rails application's /lib/tasks directory so that we can quickly and easily accomplish these tasks on a day-to-day basis within our apps.

Snapshotting your database

A database snapshot is just a copy of your data at a particular moment in time. When your entire database is simply a file on your filesystem, taking a snapshot is as simple as:

namespace :snap do
  task :create do
    @snapshot_dir = Rails.root.join('storage/snapshots')
    @db_path = ActiveRecord::Base.connection_db_config.database
    @db_name = @db_path.rpartition('/').last.remove('.sqlite3')

    timestamp = DateTime.now.to_formatted_s(:number)
    snap_name = "#{@db_name}-#{timestamp}.backup"
    snap_path = Pathname(@snapshot_dir).join(snap_name)

    FileUtils.copy_file(@db_path, snap_path)
  end
end

There is some boilerplate here, but the core is simply a cp call to copy the database file. We get the current ActiveRecord database path, prepare a timestamped snapshot file name, and just copy the database file over.

Snapshots are useful as they give you the ability to create save points with your schema and data that are easy to jump back to later.

Restoring a snapshot

Once you have a snapshot, you may need to restore your database to that point in time. Typically, you will take a snapshot before you begin an experiment that will require altering your database schema or data or both. You want to be able to revert your changes if needed, so you take a snapshot first and revert later. With simple SQLite files, you can probably guess how snapshot restoring is going to go:

namespace :snap do
  task :restore do
    @snapshot_dir = Rails.root.join('storage/snapshots')
    @db_path = ActiveRecord::Base.connection_db_config.database
    @db_name = @db_path.rpartition('/').last.remove('.sqlite3')
    @snaps = Pathname(@snapshot_dir)
      .children
      .select do |path|
        path.extname == ".backup" &&
        path.basename.to_s.include?(@db_name)
      end
      .sort
      .reverse

    latest_snapshot = @snaps.first

    FileUtils.remove_file(@db_path)
    FileUtils.copy_file(latest_snapshot, @db_path)
  end
end

Restoring a snapshot itself is straight-forward. We take the most recent snapshot of our current database. Then, we delete the current database file and copy the snapshot file into the current database file's place. Again, because we are working with simple files, we are fundamentally just putting some nice boilerplate around cp copy commands.

Because our database is just a file on the file system, working with our production data can also be simplified. I will write about that in a future post. For now, I think that this exploration of how we can snapshot and restore local databases is sufficient for one post. With a bit of cleanup and polish, we can create a /lib/tasks/dbspan.rake file that provides the following usage:

bin/rails db:snap:list
bin/rails db:snap:create
bin/rails db:snap:restore

This will set the foundation that will then allow us to add on the ability to work with our production database as well.

{:.notice}
You can find the full code for the model concern detailed in this Gist.

This command is taken from the pg-snap repository, which provides a simpler CLI utility for working with Postgres snapshots. ↩

Array columns in SQLite

Stephen Margheim — Tue, 12 Sep 2023 14:25:20 +0000

One of the reasons people hesitate to use SQLite in their Ruby on Rails applications, in my opinion, is a fear that they will miss certain features they are accustomed to from PostgeSQL or MySQL. As discussed in an earlier post, we can load SQLite extensions into our Rails applications to enhance the functionality of SQLite. Moreover, today I want to show you that it is possible to build on top of SQLite's primitives to provide matching behavior for one of my favorite features of Postgres—array columns.

When working with relational data, you often come across one particular dilemma: what do I do with this small amount of associated data? Do I build a whole new table with a foreign key, which keeps my schema highly normalized but also means I have to accept a JOIN everytime I need to access this data. Do I simply stuff the data into a JSON column on my primary table, which removes the need for the JOIN but also bloats my primary table and opens up a possibility for stuffing unstructured data into that column.

Postgres offers a nice compromise here with their implementation of array columns. Instead of an amorphous JSON blob, your column is and will always simply be an array of values. This matches the effective behavior of a simple two-column associated table (foreign key plus value column), without the need for the JOIN.

For my favorite example of the utility of this tool, consider Nate Hopkin's implementation of a tagging system, built on top of Postgres' array columns. To save you a click and demonstrate just how elegant this solution is, I will provide the code examples from the README here:

# db/migrate/TIMESTAMP_add_groups_to_user.rb
class AddGroupsToUser < ActiveRecord::Migration[5.0]
  def change
    add_column :users, :groups, :string, array: true, default: [], null: false
    add_index :users, :groups, using: "gin"
  end
end

# app/models/user.rb
class User < ApplicationRecord
  include TagColumns
  tag_columns :groups
end

user = User.find(1)

# assigning tags
user.groups << :reader
user.groups << :writer
user.save

# checking tags
is_writer            = user.has_group?(:writer)
is_reader_or_writer  = user.has_any_groups?(:reader, :writer)
is_reader_and_writer = user.has_all_groups?(:reader, :writer)

# finding tagged records
assigned                = User.with_groups
unassigned              = User.without_groups
writers                 = User.with_any_groups(:writer)
non_writers             = User.without_any_groups(:writer)
readers_or_writers      = User.with_any_groups(:reader, :writer)
readers_and_writers     = User.with_all_groups(:reader, :writer)
non_readers_and_writers = User.without_all_groups(:reader, :writer)

# find unique tags across all users
User.unique_groups

# find unique tags for users with the last name 'Smith'
User.unique_groups(last_name: "Smith")

With one array column on your model, you get a full suite of the core "tagging-style" functionality. I love solutions like this. The entire gem is no more than one file that defines the TagColumns concern, and that file is only 105 lines (89 lines of code). Elegance and simplicity are what SQLite is all about, so, how can we accomplish the same result without an array column primitive?

Let's start with how we can add a column to a table that can only be an array. SQLite does support a wide variety of JSON functionality. It also supports standard column check constraints. This gives us everything we need. We will define a JSON column and then add a constraint to ensure that the column is only an array JSON type. With Rails' migration DSL, if you are creating the column as you create the table it looks like:

create_table :posts, force: true do |t|
  t.json :tags, null: false, default: []
  t.check_constraint "JSON_TYPE(tags) = 'array'", name: 'post_tags_is_array'
end

If you are simply adding the column to an existing table, the migration would look like:

add_column :posts, :tags, :json, default: [], null: false
add_check_constraint "JSON_TYPE(tags) = 'array'", name: 'post_tags_is_array'

Note: SQLite does not support GIN indexes. In order to provide an index for a JSON column, the recommended pattern in SQLite is to define a generated column and then index that column. This blog post provides a good overview of the approach. Unfortunately for us Rails developers, the ActiveRecord adapter for SQLite doesn't yet support generated attributes, so we would have to drop down to running raw SQL. Support for Postgres generated columns was recently added, and I plan to open a similar pull request for the SQLite adapter in the near future. For the time being, therefore, I will not dig into indexing an "array" column in our SQLite database. Since SQLite doesn't need to eat the network latency cost of a query, even unindexed queries can be sufficiently fast. However, of course, we would prefer to be able to ensure our SQLite implementation of "array columns" can be indexed. Once I have improved Rails' support, I will write a new post detailing how to work with SQLite generated columns and indexing them.

This gives us a JSON column that will only ever be an array of values. Without a schema setup, let's turn to the "taggable" functionality that we want to support. tag_columns supports 11 methods:¹

Model.unique_column_name()
Model.column_name_cloud()
Model.with_column_name()
Model.without_column_name()
Model.with_any_column_name(*items)
Model.with_all_column_name(*items)
Model.without_any_column_name(*items)
Model.without_all_column_name(*items)

model.has_any_column_name(*items)
model.has_all_column_name(*items)
model.has_column_name(*items)

We need SQL queries to back each one, and then the ActiveRecord method calls to generate those queries. As I don't want to derail this post with the process of coming up with each query and ActiveRecord method, I will summarize. At the heart of our implementation is the use of the JSON_EACH function that SQLite provides, which will treat each value in our array as if it were a row in a table. Each virtual row will have a value column that you can select from. So, to get the unique set of values for the tags column in our example posts table, we simply need this SQL query:

SELECT DISTINCT value
FROM "posts",
     JSON_EACH("posts"."tags");

Readable and succinct. Similarly, in order to find those posts that are tagged with draft, we could use this query:

SELECT "posts".*
FROM "posts"
WHERE EXISTS (
  SELECT 1
  FROM JSON_EACH("posts"."tags")
  WHERE value IN ('draft')
  LIMIT 1
)

This query gets more complicated. In order to find all of the posts with the tag, we need to isolate our query for selecting the posts and all of their attributes from the query to check for whether the tag is present or not. This is a perfect use-case for a nested query. Our inner query does a check for whether or not the specific tag is present.² We use SELECT 1 because we only need to return a boolean for the WHERE EXISTS check in the outer query; we use LIMIT 1 to optimize the inner query a bit, as we only need to know if draft is present in the tags array at least once, we don't care about duplicates. This shape of a query will drive all of the any_* methods.

In order to support the all_* methods, we need a query that ensures that returned posts included each of the provided values. For example, Post.with_all_tags('draft', 'sqlite') must only return those posts who are tagged with both the draft tag and the sqlite tag; any post only tagged with draft is ignored. Here is the shape for that query:

SELECT "posts".*
FROM "posts"
WHERE (
  SELECT COUNT(DISTINCT value)
  FROM JSON_EACH("posts"."tags")
  WHERE value IN ('draft', 'sqlite')
) = 2;

Instead of a basic WHERE EXISTS check, our outer query is now checking whether the number of matching tags for the post matches the number of queried tags. Remember that JSON_EACH effectively converts our array column into a virtual table with rows; so, SELECT COUNT(*) FROM JSON_EACH() WHERE ... will count the number of entries in our array column that match the where condition, returning that as an integer. We can use that integer returned from the inner query to ensure that the outer query only returns posts with the total number of tags provided. In order to handle the possibilities of duplicate tags, we ensure that we COUNT only DISTINCT values. Were we to use SELECT COUNT(*) or SELECT COUNT(value), our integer returned from the inner query could be larger than 2 (the size of the array of tags we are querying against). To ensure that the inner query only ever returns an integer as large or smaller than the array of tags, we need to count only distinct values.

However, those three basic queries form the foundation of our entire implementation. with_* scopes use =, while without_* use !=, but the shapes are all the same. So, the final piece to our puzzle is generating these queries in ActiveRecord.

Again, I'm not going to get bogged down in process. We don't want to use raw SQL strings if we can avoid it. Raw SQL strings are brittle in ActiveRecord usage. And we want to provide a model concern, so robustness is of particular value. This means we need to dip down and use Arel. This is the relational algebra library that sits at the foundation of ActiveRecord, with ActiveRecord's query interface built on top of Arel. For a good intro on working with Arel directly, check out this post. I won't review those details in this post.

As we stated earlier, every single query we need uses JSON_EACH at its heart. So, we need to be able to generate this function in Ruby. Arel provides an interface for functions that we can use like so:

# JSON_EACH("{table}"."{column}")
json_each = Arel::Nodes::NamedFunction.new("JSON_EACH", [arel_table[column_name]])

Note: arel_table is available to us as we will be executing this code in the context of an ActiveRecord model concern.

With our json_each expression object ready, we could built the .unique_tags method like so:

# SELECT DISTINCT value FROM "{table}", JSON_EACH("{table}"."{column}")
define_singleton_method :"unique_#{method_name}" do |conditions = "true"|
  select('value')
    .from([arel_table, json_each])
    .distinct
    .pluck('value')
    .sort
end

In order to setup our .with_any_tags scope, we simply need a method builder like this:

# SELECT "{table}".* FROM "{table}" WHERE EXISTS (SELECT 1 FROM JSON_EACH("{table}"."{column}") WHERE value IN ({values}) LIMIT 1)
scope :"with_any_#{method_name}", ->(*items) {
  values = array_columns_sanitize_list(items)
  overlap = Arel::SelectManager.new(json_each)
    .project(1)
    .where(Arel.sql('value').in(values))
    .take(1)
    .exists

  where overlap
}

And the corresponding .with_all_tags scope looks like this:

# SELECT "{table}".* FROM "{table}" WHERE (SELECT COUNT(DISTINCT value) FROM JSON_EACH("{table}"."{column}") WHERE value IN ({values})) = {values.size};
scope :"with_all_#{method_name}", ->(*items) {
  values = array_columns_sanitize_list(items)
  count = Arel::SelectManager.new(json_each)
    .project(Arel.sql('value').count(distinct = true))
    .where(Arel.sql('value').in(values))
  contains = Arel::Nodes::Equality.new(count, values.size)

  where contains
}

I won't paste each method here. You can find them in the Gist I have provided to accompany this post. The idea is to demonstrate how we can map the SQL queries we need to Arel-based Ruby code.

We wrap all of this in an ArrayColumns model concern and we are good to go. With a well-written schema migration and a single model concern, we have the ability to define "array column" types in our SQLite database, as well as query them as if they were an associated table, without the cost of a JOIN.

Hopefully, this demonstrates the power and flexibility available in SQLite. Even without all of the native features and data types provided by Postgres, a little bit of ingenuity can provide equivalent functionality.

You can find the full code for the model concern detailed in this Gist. Check out the full script to see the full set of test cases as well.

To understand what each method does more precisely, consider the test suite that I wrote. ↩
We use IN, even with a single value, to allow the query to accommodate both singular and plural values easily. ↩

Optimizing SQLite compilation

Stephen Margheim — Sun, 10 Sep 2023 10:00:40 +0000

This is the next in a collection of posts on how to enhance SQLite in order to power up our Ruby on Rails applications. In this post, we dig into how to tune SQLite at compile-time to better support production usage in a web application. This is a close companion to a previous post on optimizing the run-time configuration of a SQLite database.

But, before we get to the heart of the issue, a quick story. The fact that I am writing this post is a testament to the power of publishing your work and the amazingness of the Ruby open-source community. When I shared the post on optimizing SQLite configuration, Nate Hopkins replied sharing how he optimizes the compilation of SQLite via a Dockerfile. To be honest, I hadn't even yet considered whether it was even possible to optimize your SQLite database by optimizing your installation of SQLite. I personally don't use Docker for my Rails projects, so I couldn't use Nate's Dockerfile.

I did recall, however, a blog post by Julia Evans on how easy SQLite is to compile, so I thought I might be able to write a Bash script to install and compile a project-specific installation of SQLite. Using Nate's Dockerfile as a guide and the SQLite documentation it actually wasn't too difficult. You can find the script I used in this Gist.

After compiling a custom SQLite installation, I went researching how to tell the sqlite3-ruby gem to use this SQLite installation over the system one. After trying a few different things, I couldn't get it to work. So, I do as we all do when we hit a wall, I opened a new discussion on the GitHub repo. One of the project's primary maintainers, Mike Dalessio, responded quickly. We went back and forth, and he then offered to hop on a pair-programming call with me to debug on my machine. After a quick call and some further chatting, Mike realized that I had overly-complicated things.

I was trying to bind the sqlite3-ruby gem to a custom installation of SQLite, but all that I really wanted was the ability to set compile-time flags when installing SQLite. After Mike realized the core issue, he quickly opened a new pull request to allow users to set compile-time flags that the sqlite3-ruby gem will use when installing and compiling SQLite. The result is a new release of the sqlite3-ruby gem that enables users to pass compile-time options.

I wanted to tell the whole winding tale because I find the whole thing so remarkable. This is the real power of the Ruby/Rails community. From Nate sharing his Dockerfile to Mike working to understand what I was trying to do, and then doing all of the work to make it possible, we together were able to make something new together. I am genuinely giddy with excitement that we found such a clean and simple way to allow developer's to fine-tune their SQLite with compile-time options for their Rails apps.

So, what does this mean? Well, it means that we now have full control to optimize our SQLite databases for our Rails apps. We can optimize both compile-time and run-time options to truly fine-tune our SQLite databases. And having the ability to tune compile-time options is a massive win for Rails apps, as the default configuration of SQLite is both driven by its commitment to backwards compatibility and its more common usage in embedded systems. In practice, neither is particularly useful for modern web application usage. The SQLite documentation even notes that its default compilation setup is unsuited for most practical usage. They then outline 12 compile-time flags that they recommend setting in order "to minimize the number of CPU cycles and the bytes of memory used by SQLite."

As with our previous post, I will simplify things and show you the flags that I recommend setting up for your Rails application. But first, how to we take advantage of this new feature of the sqlite3-ruby gem?

First and foremost, you need to be using version 1.6.5 or higher. You should put gem "sqlite3", "~> 1.6.5" in your Gemfile. Next, you need to tell Bundler to use the "ruby" platform gem so that Bundler will compile SQLite from source by adding the force_ruby_platform: true option.¹ So, in full, your Gemfile entry for sqlite3 should look like this:

gem "sqlite3", "~> 1.6.5", force_ruby_platform: true

This ensures that you have an appropriate version of the sqlite3-ruby gem and that when the gem compiles SQLite it won't use one of the prebuilt binaries.

Next, you need to set the Bundler config option for the compile-time flags. If you've ever tweaked the compile-time flags for Nokogiri, things should look familiar. You can set the config using the bundler CLI:

bundle config set build.sqlite3 \
"--with-sqlite-cflags='-DSQLITE_DEFAULT_CACHE_SIZE=9999 -DSQLITE_DEFAULT_PAGE_SIZE=4444'"

Note: These values are for demonstration purposes only. Do not copy this and run this in your project. I will provide an appropriate set of CFLAGS shortly.

Running this command will create or update your project's .bundler/config file to include an option like so:

BUNDLE_BUILD__SQLITE3: "--with-sqlite-cflags='-DSQLITE_DEFAULT_CACHE_SIZE=9999 -DSQLITE_DEFAULT_PAGE_SIZE=4444'"

Note: The use of single quotes within the double-quoted string to ensure the space between compiler flags is interpreted correctly.

That's it! You only need those two changes. An update to your Gemfile and your .bundler/config file is all it takes to get a project-specific, fine-tuned SQLite installation. You can find these steps, as well as additional instructions for more advanced usage of the sqlite3-ruby gem, in the gem's installation documentation.

Ok, let's get to the heart of the matter. What compile-time flags should we use? The short answer is: use what SQLite recommends, minus the ones that don't make sense for web application usage. The SQLite docs recommend 12 flags. I won't repeat their explanation of what each one does here; read the docs to learn more.

SQLITE_DQS=0
SQLITE_THREADSAFE=0
SQLITE_DEFAULT_MEMSTATUS=0
SQLITE_DEFAULT_WAL_SYNCHRONOUS=1
SQLITE_LIKE_DOESNT_MATCH_BLOBS
SQLITE_MAX_EXPR_DEPTH=0
SQLITE_OMIT_DECLTYPE
SQLITE_OMIT_DEPRECATED
SQLITE_OMIT_PROGRESS_CALLBACK
SQLITE_OMIT_SHARED_CACHE
SQLITE_USE_ALLOCA
SQLITE_OMIT_AUTOINIT

Two of these options won't work with the sqlite3-ruby gem: SQLITE_OMIT_DEPRECATED and SQLITE_OMIT_DECLTYPE. The gem needs those features of SQLite in order to function, so we must remove them.

Next, we should remove the SQLITE_THREADSAFE=0 option, as this is only usable by applications that never access SQLite from more than one thread at a time. In a web app, we are likely to access the database from multiple threads.

We should also remove the SQLITE_OMIT_AUTOINIT option as it requires applications to correctly call SQLite's initialize method at appropriate times. We can't guarantee that level of control, and if you fail to call initialize properly, you will get a segfault.

You may also want to add the SQLITE_ENABLE_FTS5 option, which adds SQLite's full text search functionality to your build. This one depends on how you plan to use your database, but if you are currently using ElasticSearch or Meilisearch, you could investigate replacing those dependencies with SQLite.

With our removals (and one possible addition), our set of flags now looks like this—9 flags to crank up SQLite's performance:

SQLITE_DQS=0
SQLITE_DEFAULT_MEMSTATUS=0
SQLITE_DEFAULT_WAL_SYNCHRONOUS=1
SQLITE_LIKE_DOESNT_MATCH_BLOBS
SQLITE_MAX_EXPR_DEPTH=0
SQLITE_OMIT_PROGRESS_CALLBACK
SQLITE_OMIT_SHARED_CACHE
SQLITE_USE_ALLOCA
SQLITE_ENABLE_FTS5

We can turn these into the Bundler config we need via the CLI command:

bundle config set build.sqlite3 \
"--with-sqlite-cflags='-DSQLITE_DQS=0 -DSQLITE_DEFAULT_MEMSTATUS=0 -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 -DSQLITE_LIKE_DOESNT_MATCH_BLOBS -DSQLITE_MAX_EXPR_DEPTH=0 -DSQLITE_OMIT_PROGRESS_CALLBACK -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_USE_ALLOCA -DSQLITE_ENABLE_FTS5'"

Or just manually updating your project's .bundler/config file:

BUNDLE_BUILD__SQLITE3: "--with-sqlite-cflags='-DSQLITE_DQS=0 -DSQLITE_DEFAULT_MEMSTATUS=0 -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 -DSQLITE_LIKE_DOESNT_MATCH_BLOBS -DSQLITE_MAX_EXPR_DEPTH=0 -DSQLITE_OMIT_PROGRESS_CALLBACK -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_USE_ALLOCA -DSQLITE_ENABLE_FTS5'"

Now, just run bundle install. That's it.

In a later post, I will talk about how all of our fine-tuning adjustments come together and what the performance profile comparison is. For now, suffice it to say that simply be tweaking these compile-time options along with the run-time settings discussed previously, you will get a noticeably improved SQLite experience for your Rails app.

So, we now have the ability to tweak each of the knobs that SQLite provides to fine-tune its behavior and performance characteristics. And all because the Ruby community is so amazing. I love it.

Note that you can only use the force_ruby_platform: true on Bunder version 2.3.18 or higher. For Bundler version 2.1 or later (up to 2.3.18), you will need to run bundle config set force_ruby_platform true, which has the unfortunate side-effect of setting this option globally for your Gemfile 😕. For version 2.0 or earlier, you'll need to run bundle config force_ruby_platform true, which has the same side-effect. ↩

Setting up Litestream

Stephen Margheim — Sat, 09 Sep 2023 13:34:30 +0000

This is the next in a collection of posts where I want to highlight ways we can use SQLite as the database engine for our Rails applications without giving up key features or power. In this post, I want to discuss one of the most often discussed disadvantages of SQLite—disaster recovery—and how to address it.

Ben Johnson is one of my favorite people in the SQLite ecosystem, and he put the point well:

So why is SQLite considered a “toy” database in the application development world and not a production database?
The biggest problem with using SQLite in production is disaster recovery. If your server dies, so does your data. That’s… not good.

The fact that SQLite uses a file on the local filesystem is one of the great double-edged sword that makes it so divisive. On the one hand, having a local file completely remove network latency from queries, which is often the primary performance bottleneck for web applications. Additionally, having your database simply be a single file allows for unique possibilities when it comes to managing your application database, like using branch-specific databases. Plus, this makes the operational complexity of your web application noticably simpler, as you don't need to run a separate server for you database. In future posts in this series, I will get into other benefits that come from SQLite's simplicity as a normal file on the filesystem.

However, simply being a file on the filesystem is also SQLite's primary weakness. Persistent data storage needs to be persistent; otherwise, it isn't so useful. And I've personally experienced the danger here. I once, somewhat mindlessly while waiting for another application to deploy, renamed an application in a 3rd-party platform-as-a-service provider's dashboard, just to make the app names more consistent. I didn't know that this action in the web dashboard would lead to the PaaS using rm -rf on the folder that contained my application on their servers to then redeploy under a new folder name. I am using SQLite as my production database for this app, and while I had the database stored in the /storage directory to keep it safe across deployments, that didn't help at all when the entire parent directory was wiped. That one mindless update in a web UI completely wiped away about 1 years worth of production data. I tried a number of recovery techniques, but nothing worked. The data was lost.

I tell this story because I don't want to gloss over this point—without a clear and strong disaster recovery plan, using SQLite as your production database is dangerous and probably foolish. There are many benefits to SQLite, and I still happily use SQLite as my production database for many applications, including the one above. I ensure, however, that I always have a disaster recovery setup.

In what follows, I want to lay out the backup and recovery setup that I use, and provide you the tools to set this up for yourself.

The quote from Ben Johnson above comes from a blog post on why he created the Litestream tool. Litestream is an essential tool for someone interested in unlocking the power of SQLite for their web application to know and use. So, what is Litestream? In a single sentence,

Litestream is a streaming replication tool for SQLite databases.

Let's dig into what that means more concretely. From Ben's introductory blog post, we get this fuller description:

Litestream is a tool that runs in a separate process and continuously replicates a SQLite database to Amazon S3 [or another storage provider]. You can get up and running with a few lines of configuration. Then you can set-it-and-forget-it and get back to writing code.

Put simply, Litestream is your SQLite disaster recovery plan, and it is simple, robust, and resilient. For the miniscule cost of a cloud storage bucket, you can get effectively point-in-time backups of your SQLite database(s).¹ And it is straight-forward to setup.

The documentation provides guides on installing Litestream on macOS, Linux (Debian), or building from source. Personally, I deploy to a Linux (Debian) server, so I will be sharing that approach.

With the package installed on your production server, you then need to get it running. Again, you have multiple options, each laid out well in the documentation. You can either run the process in a Docker container, in a Kubernetes cluster, as a Systemd service, or as a Windows service. In my case, I run Litestream as a Systemd service, so we will follow that path.

Finally, with the package installed and running, you need to configure it to talk to your storage provider. Litestream supports a wide array of providers—Amazon S3, Azure Blob Storage, Backblaze B2, DigitalOcean Spaces, Scaleway Object Storage, Google Cloud Storage, Linode Object Storage, and an SFTP Server. In my case, I use DigitalOcean Spaces.

Hopefully, you can see that Litestream is quite flexible and can be used across a multitude of different deployment situations and storage providers. I, however, only have experience with my setup. So, I will share that, with as much detail as possible, to help you hopefully get everything setup yourself.

I use Hatchbox.io to host my Rails applications. When using SQLite as your production database, you simply can't use Heroku. But, I have fallen out of love with Heroku generally, after a decade under Salesforce's stewardship. I love Hatchbox because it allows me to "deploy to servers that I own", which mitigates the cost overhead that many PaaS providers entails, plus it tailor-made for Rails applications. It is run by Chris Oliver from GoRails and Bilal Budhani, and they offer quick and useful customer support. Salespitch aside (just a joke, I have no affiliate relationship with Hatchbox), Hatchbox is a great service, but since it mostly sits on top of servers you bring it, there is nothing in the setup of Litestream that is Hatchbox-specific.

In my case, I have used both DigitalOcean droplets as well as Hetzner servers through Hatchbox. In either case, I am bringing Linux machines that run Ubuntu—a Debian-based OS. Since Litestream provides Debian package files, it is straight-forward to install Litestream using the dpkg utility.² Moreover, using a Debian-based OS allows us to use systemd to run the Litestream process on our behalf.

Here is the Bash script I use to install and run Litestream on all of my servers:³

#!/usr/bin/env bash
set -e

# Load environment
source /home/deploy/.bashrc

# Determine architecture of current env
arch=$(dpkg --print-architecture)
# Manually set the Litestream version number we are using
version="v0.3.11"

# Download the latest .deb file
wget "https://github.com/benbjohnson/litestream/releases/download/$version/litestream-$version-linux-$arch.deb"

# Install that .deb file using dpkg
sudo dpkg -i "litestream-$version-linux-$arch.deb"

# Verify it is installed
echo "Litestream version:"
litestream version

# Enable Litestream to run continuously as a background service
sudo systemctl enable litestream

# Start Litestream running continuously as a background service
sudo systemctl start litestream

# Verify the service is running
echo "Litestream service logs:"
sudo journalctl -u litestream

As you can see, the setup is straight-forward. I have run this script on both DigitalOcean and Hetzner servers with no problems. It relies on pre-installed system utilities like dpkg, wget, and systemd so no additional setup is required. Plus, it is a small script, so it is easy to share and use and understand.

If you aren't using a Debian-based OS, you will need a different setup script, but Linux and Ubuntu are quite popular, so odds are this script can be useful for you.

Once you have Litestream installed and running, the only thing left is to configure it to start replicating your database(s). Once again, the docs have a page dedicated to configuring Litestream. The summary, though, is that you will need to create an /etc/litestream.yml file and then enter your YAML configuration. The basic structure of the configuration is straight-forward (you may be sensing a trend here, and this is another wonderful thing about Litestream—it does straight-forward things straight-forwardly). You provide an array of dbs, each with an array of replicas. So, you can have your server's Litestream process backing up multiple SQLite database files, plus each database file can be streamed to multiple storage providers.

In my case, I use a single server for a single app, which I backup to a single storage provider. In the simplest case, I use a single SQLite file for the storage backend of my Rails app's ActiveRecord models. Thus, I only have the one database. In more interesting cases (which I will be writing more about in the future), I use Litestack to actually have SQLite back ActionCable, ActiveSupport::Cache, and ActiveJob, such that I have four or more SQLite database files per app to backup.

I won't cover how to create a cloud storage bucket. The Litestream docs do a good job of that on their own. Instead, let's focus on the configuration you would have on your server. Since I am using DigitalOcean Spaces as my storage provider, I followed the instructions from the Litestream docs. You will need an access-key-id and a secret-access-key to allow Litestream to connect to your storage provider. The configuration file supports setting those values globally as well as on a per-replica basis. To make adding replicas easier in the future, I default to setting them on a per-replica basis. Thus, my configuration file looks like this:

# /etc/litestream.yml
dbs:
  - path: /home/deploy/application-name/current/storage/production.sqlite3
    replicas:
      - url: s3://bucket-name.litestream.region.digitaloceanspaces.com/production
        access-key-id: xxxxxxxxxxxxxxxxxxxx
        secret-access-key: xxxxxxxxx/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Again, straight-forward. All you need to do is point Litestream at your database file, then point it at your storage bucket (with access credentials). It handles everything else.

Since our installation script starts the Litestream process immediately after installation, we will likely be creating the configuration file next. Once you have gotten your configuration file setup properly, you will need to restart the Litestream process to pick up your new config file. As we are using systemd, this is as simple as:

sudo systemctl restart litestream

With Litestream installed, configured, and running, you are ready to go. If you write to your database and then visit your storage bucket, you will see that Litestream has already written data there. And since no backup is viable until we have verified that we can recover with it, let us simulate a disaster and run through the recovery steps.

I like to simply rename my database file to mimic a deletion. If we imagine that we moved our /home/deploy/application-name/current/storage/production.sqlite3 database file to /home/deploy/application-name/current/storage/-deleted.sqlite3, how can we recover our production.sqlite3 file with Litestream? From our server's command line, we need to run

litestream restore production.sqlite3

That's it. We don't have the provide the entire file path, we don't need any esoteric command line arguments, just litestream restore. This command will find the database in the configuration file and restore the most recent copy it has from its storage replica. Confirm that the file is present then run

sqlite3 production.sqlite3

to inspect the contents of the database and check that the most recent data from before the "deletion" is present.

Once you have confirmed that your recovery process is up and running, you are good to go. Using Litestream we have now mitigated the primary weakness with using SQLite in production for our web application persistence layer. We can now enjoy the DX benefits that come with using SQLite without the constant worry about disaster recovery.⁴

For those of you interested in how Litestream works, the documentation has a very understandable page. I would heartily recommend just reading that in its entirety. ↩
For an introduction to dpkg, I would recommend this article. Otherwise, the short description from the man page hopefully provides enough of a sense: "dpkg is a tool to install, build, remove and manage Debian packages." ↩
As of the time of this post (September 9^th, 2023), the latest release of Litestream is version 0.3.11. If you run this script at some point in the future and there is a newer release, replace the version variable with that version number. You can always find the most recent release on the project's GitHub Releases page. ↩
One alternative for a disaster recovery plan is to use remote attached storage, like AWS EBS or similar. From your application's point of view, this is still a local filesystem, but if your server dies, the data doesn't. You can then "recover" the data by simply reattaching the storage to another server. The key details with this solution is to ensure that your memory-map is large enough to ensure that reads are basically as fast as with true local storage, plus make sure that you set the synchronous pragma to NORMAL to minimize fsync calls on writes, as these will be much slower with the attached storage. Perhaps most importantly, though, don't even get tempted by the idea of using a remote filesystem like NFS. But, as one of my SQLite guru's has said: "[W]ith attached storage you get durability and availability, and SQLite can be tuned so it gets very close to local storage performance wise. [Plus,] Google & AWS offer auto instance recovery, so if your instance goes down another one is spawned and the storage is reattached, this happens in seconds and delivers a pretty high level of availability for your SQLite powered apps." ↩

Loading extensions

Stephen Margheim — Fri, 08 Sep 2023 09:11:16 +0000

Once again we are enhancing our Ruby on Rails applications to power up SQLite. In this post, we dig into how to load extensions into our SQLite database.

Personally, I find SQLite to be essentially feature complete, but sometimes you have specific needs for your database that SQLite doesn't support. Luckily, SQLite offers a rich extension ecosystem. There is an (unofficial) package manager—sqlpkg, an (unofficial) standard library—sqlean, and a rich collection of Alex Garcia extensions. For a general introduction to installing SQLite extensions, read this post.

We want, however, a simple way to install and load SQLite extensions in a Rails application. Unfortunately, at the moment the sqlpkg and sqlean extension collections do not provide Ruby gem releases. Fortunately though, Alex Garcia does release each of his extensions as a Ruby gem. You can find all of his extensions under his RubyGems' profile. Let's focus on how to make it easy to install and load one of these extensions.

The installation is simple, as these are Ruby gems. We can simply use bundle add {extension-name}. Loading is the tricky part.

Before extensions are loaded, we have to first enable extension loading for the SQLite database. The SQLite3 Ruby adapter provides a #enable_load_extension method for this purpose. Alex Garcia's extensions then provide a .load method on the Ruby extension class that will load the extension. So, in full we would need to do the following to load an extension in Ruby:

@raw_connection.enable_load_extension(true)
SqliteExtension.load(@raw_connection)
@raw_connection.enable_load_extension(false)

We want to enhance Rails, though, to make the developer experience clean. So, how can we expose this functionality more elegantly? Luckily, in our previous post we introduced an enhancement to the SQLite3 adapter which provides a hook for configuring the database from options set in the /config/database.yml file. We can add support for an extensions section, which will accept an array of extension names. We can then add to our configure_connection method to iterate over these extension names and load them:

module RailsExt
  module SQLite3Adapter
    def configure_connection
      # ...

      @raw_connection.enable_load_extension(true)
      @config[:extensions].each do |extension_name|
        require extension_name
        extension_classname = extension_name.camelize
        extension_class = extension_classname.constantize
        extension_class.load(@raw_connection)
      rescue LoadError
        Rails.logger.error("Failed to find the SQLite extension gem: #{extension_name}. Skipping...")
      rescue NameError
        Rails.logger.error("Failed to find the SQLite extension class: #{extension_classname}. Skipping...")
      end
      @raw_connection.enable_load_extension(false)
    end
  end
end

After bundle add {extension-name}, we can simply add the extension to the extensions section in the /config/database.yml file. Our RailsExt::SQLite3Adapter will then handle the rest, dealing with possible errors as well. This means we can have a default section like so to load an extension for supporting ULIDs:

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  # connection attempts to make immediately before throwing a BUSY exception
  retries: 1000
  extensions:
    - sqlite_ulid

What I love about this approach to loading SQLite extensions is that extensions are explicitly installed (in the Gemfile) and loaded (in the database.yml file), plus it naturally builds on top of our existing enhancement to the SQLite adapter. In total, our enhanced adapter now supports pragma configuration as well as extension loading. Plus, our database configuration powers a Git branch-bound database branching approach.

This provides a rich and powerful set of functionality for local development. In the next post, we will dig into how to install and setup Litestream so that our production database will have point-in-time backups and recovery. Exiting things ahead!

You can find the files we have written throughout this post in this Gist

Linked headings in your BridgetownRB site

Stephen Margheim — Thu, 07 Sep 2023 13:11:10 +0000

BridgetownRB is a powerful and flexible "progressive site generator" written in Ruby. I use it to publish this blog. One feature that I wanted to support with my blog is having headings that provide a quick anchor link to that section of the page. In this post, I want to walk you through the simple steps to add this feature to a Bridgetown site.

Before we jump into the code, let's ensure that we are all on the same page about the feature we are building. I want headings to behave like this:

That is, I want headings that reveal a # on hover which is an anchor link to that particular section of the page. This is common interaction, perhaps most commonly seen in GitHub README's.

With its plugin system, Bridgetown is easy to extend. In our case, we want to create a plugin that will inspect and manipulate the generated HTML of a page. Bridgetown provides the Inspector API for precisely this use-case.

To create a local plugin, all we need to do is create a new Ruby file in the /plugins/builders directory. Following the Bridgetown convention, we will name our inspector plugin class Builders::Inspectors:

# /plugins/builders/inspectors.rb
class Builders::Inspectors < SiteBuilder
  def build
    inspect_html do |document|
      # ...
    end
  end
end

Bridgetown will automatically load this plugin when it runs, so this is literally all we need to do in order to get our plugin setup and used. The only thing left is to write the logic for manipulating our headings.

The inspect_html method that Bridgetown provides yields a Nokogiri document object to our block. We can use the #css method to find our page headings. In our case, we only want content headings, which means we only want headings under the <main> tag and only non-<h1> headings. Moreover, we can only link to a heading if it has an id attribute. So, we need a CSS selector like so: main h2[id],h3[id],h4[id],h5[id],h6[id]. This finds precisely the headings we are after. For each heading, we then simply want to append an <a> tag with an href that points to the anchor link for that heading's id. Let's write this up in Ruby:

class Builders::Inspectors < SiteBuilder
  def build
    inspect_html do |document|
      document.css("main h2[id],h3[id],h4[id],h5[id],h6[id]").each do |heading|
        heading << %(
          <a href="##{heading[:id]}" class="anchor" aria-hidden="true">#</a>
        )
      end
    end
  end
end

We add the .anchor class to allow us to style this # as we desire and the aria-hidden="true" attribute to remove this link from the accessibility tree, since this link provides no utility to screen readers.

With our anchor links appended to our headings, we only need to style the interaction. We want the # to be visually hidden by default, only shown when hovering the heading, and to not have the standard link underline text. This can be accomplished with the following CSS:

[aria-hidden="true"] {
  visibility: hidden;
}

.anchor {
  text-decoration: none;
}

h2:hover .anchor,
h3:hover .anchor,
h4:hover .anchor,
h5:hover .anchor,
h6:hover .anchor {
  visibility: visible;
}

Nothing too fancy or complicated, but it gets the job done. Add this CSS to your /frontend/styles/index.css file, or some component file imported into index.css and you are good to go.

These two additions are all you need to setup linked headings in your current or next Bridgetown site. If you enjoyed this tip, please do reach out on Twitter @fractaledmind.

You can find the files we have written throughout this post in this Gist

Fine-tuning your SQLite database

Stephen Margheim — Thu, 07 Sep 2023 13:07:16 +0000

This is the next in a collection of posts where I want to highlight ways we can enhance our Ruby on Rails applications to take advantage of and empower using SQLite as the database engine for our Rails applications. In this post, we dig into how to tune the SQLite configuration to better support production usage in a web application.

Before jumping into the tuned configuration, let's step back and get familiar with how SQLite is configured. SQLite uses a custom SQL statement for configuration—the PRAGMA statement:

The PRAGMA statement is an SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data.

As we can see from this definition, there are two basic kinds of PRAGMA statements:

those that "modify the operation of the SQLite library", and
those that "query the SQLite library for internal data"

For configuring SQLite, we are interested in the former, and not the latter. The SQLite documentation provides a page with an overview of every PRAGMA statement. Filtering out deprecated pragmas, specialized pragmas, and internal data pragmas, we are left with this list of 40 "configuration" pragmas:

analysis_limit
application_id
auto_vacuum
automatic_index
busy_timeout
cache_size
cache_spill
case_sensitive_like
cell_size_check
checkpoint_fullfsync
data_version
defer_foreign_keys
encoding
foreign_keys
freelist_count
fullfsync
hard_heap_limit
ignore_check_constraints
integrity_check
journal_mode
journal_size_limit
legacy_alter_table
locking_mode
max_page_count
mmap_size
page_count
page_size
query_only
quick_check
read_uncommitted
recursive_triggers
reverse_unordered_selects
secure_delete
soft_heap_limit
synchronous
temp_store
threads
trusted_schema
user_version
wal_autocheckpoint

I created a new Rails 7.0.7.2 application and checked the values for each of these pragmas to see how Rails and SQLite are setup by default in a new application:

{"analysis_limit"=>0,
 "application_id"=>0,
 "auto_vacuum"=>0,
 "automatic_index"=>1,
 "timeout"=>5000,
 "cache_size"=>-2000,
 "cache_spill"=>483,
 "cell_size_check"=>0,
 "checkpoint_fullfsync"=>0,
 "data_version"=>1,
 "defer_foreign_keys"=>0,
 "encoding"=>"UTF-8",
 "foreign_keys"=>1,
 "freelist_count"=>0,
 "fullfsync"=>0,
 "hard_heap_limit"=>0,
 "ignore_check_constraints"=>0,
 "integrity_check"=>"ok",
 "journal_mode"=>"delete",
 "journal_size_limit"=>-1,
 "legacy_alter_table"=>0,
 "locking_mode"=>"normal",
 "max_page_count"=>1073741823,
 "mmap_size"=>0,
 "page_count"=>5,
 "page_size"=>4096,
 "query_only"=>0,
 "quick_check"=>"ok",
 "read_uncommitted"=>0,
 "recursive_triggers"=>0,
 "reverse_unordered_selects"=>0,
 "secure_delete"=>0,
 "soft_heap_limit"=>0,
 "synchronous"=>2,
 "temp_store"=>0,
 "threads"=>0,
 "trusted_schema"=>1,
 "user_version"=>0,
 "wal_autocheckpoint"=>1000}

Note: This output was achieved with this command:

pragmas.reduce({}) do |memo, pragma|
  memo.merge!(ActiveRecord::Base.connection.execute("PRAGMA #{pragma}").first)
end

This is interesting, but of course not every pragma is equally important for tuning Rails/ActiveRecord. So, let's focus in on the most impactful pragmas. There are around six pragmas that play a big role in performance, especially in the context of a web application:

journal_mode
synchronous
journal_size_limit
mmap_size
cache_size
busy_timeout/busy_handler

It is important that we understand what each of these pragmas does, and how best to tune them for our usage in Rails and ActiveRecord.

The `journal_mode` pragma

The first and most important pragma to understand and tune is the journal_mode pragma. Since version 3.7.0 (2010-07-21) SQLite has offered two implementations to support the atomic transactions:

the Rollback journal, and
the Write-Ahead log

The rollback journal is the default and original implementation, while the write-ahead log is the newer implementation. The journal_mode pragma has five options to tune how the rollback journal implementation behaves (DELETE, TRUNCATE, PERSIST, MEMORY, and OFF), and one option that tells SQLite to simply use the write-ahead log implementation (WAL). By default, our new Rails app uses the rollback journal with the DELETE journal mode. This means that the rollback journal file will be deleted from disk after each transaction commits.

For web applications, the write-ahead log is the superior option. As the SQLite documentation outlines, the write-ahead logs comes with a few advantages over the rollback journal that are especially important in the context of a web application:

WAL is significantly faster in most scenarios.

WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

This is what we want in our application. We want faster queries and increased concurrency. So, the very first configuration change that we will need to make is to set the journal_mode pragma to WAL (we will get into the technical details of how to do this in Rails later in this post).

The `synchronous` pragma

SQLite supports four different modes for the synchronous pragma, which controls when and how SQLite flushes content to disk. The two common options are FULL and NORMAL, which map to "sync on every write" and "sync every 1000 written pages" respectively. Each mode has an integer value as well, so the "synchronous"=>2 default value we see for our new Rails app maps to the FULL mode. This means that SQLite syncs data with the file on disk after every write. As they say in the documentation:

This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower.

Slow isn't what we want. And, it isn't what we need. As the SQLite documentation says:

The synchronous=NORMAL setting is a good choice for most applications running in WAL mode.

In short, when journal_mode is WAL, simply set synchronous to NORMAL. These two go together like peanut butter and jelly.

But, what precisely is the NORMAL sync mode? It simply means that SQLite will flush to disk less often than after every single write. SQLite has its own algorithm for determining the "most critical moments" to write to disk, where it syncs every wal_autocheckpoint pages (which defaults to 1000). So, if/when the wal_autocheckpoint pragma is changed, NORMAL mode syncs would occur after that many pages are written. So, we trade an aggressive approach to durability for speed. However, SQLite does a lot to mitigate the reduction in durability, and it is honestly an extreme edge-case. In fact, SQLite ensures that any potential data loss could only happen with OS or filesystem failure; any process crash won't affect data durability. So, we are optimizing for the 99% case and not the 1% case, which I think is appropriate for a Rails application.

The `journal_size_limit` pragma

Next up we have the journal_size_limit pragma. This tells SQLite how much of the write-ahead log data (in our case) to keep in the on-disk file. The default of -1 means that there is no limit, so this disk file will grow in size indefinitely. This is not what we want. Anyone who has experienced app downtime because log files filled up your disk space no that unlimited file size is just a massive headache waiting to happen. We need to ensure that the file size is capped at an appropriate size. But, what exactly is an appropriate size?

Well, we don't want it to be too small. The more data is in the journal file, the faster SQLite will be (generally). However, we also don't want it to be too big, as this can start to negatively impact read performance. Based on production usage and experimentation, I have landed on 64 megabytes as a solid default for this setting.

The `mmap_size` pragma

Next up, we have the abbreviated pragma mmap_size. This setting controls the "the maximum number of bytes of the database file that will be accessed using memory-mapped I/O." This is a mouth-full, but the gist is that when we enable memory-mapped I/O, we are allowing SQLite to share data among multiple processes. The memory map plays a similar role to Postgres' buffer pool, so instead of disabling it, we should set it to the same safe as the default Postgres buffer pool—128MB.

The `cache_size` pragma

The cache_size pragma sets the "maximum number of database disk pages that SQLite will hold in memory at once per open database file." The default value of -2000 is a negative number, which SQLite interprets as a byte limit. If we use a positive number, SQLite will interpret this as a page limit. The default limit is ~2MB (2,048,000 bytes) and is independent of the number of pages. We want to ensure that we have a large cache and that it doesn't split across pages, so let's use a positive number to set the cache limit to a page number. I recommend 2,000 pages as the cache_size, which, with the default page size of 4,096 bytes, means that the cache limit is ~8MB (8,192,000 bytes).

It is worth noting, for full understanding, that the page cache is private to each connection (even those in the same process), and it gets invalidated once another connection writes to the database file. It is nonetheless quite useful within the boundaries of a statement or a transaction to maximize concurrency speed.

The `busy_timeout`/`busy_handler` pragma

The final pragma that is important to understand and tune is the busy_timeout pragma. This tells SQLite how long to wait to successfully connect to the database when trying to establish a new connection. When you create a new Rails app with SQLite, Rails will set the timeout option in the /config/database.yml to 5000 milliseconds. SQLite uses an exponential backoff algorithm to retry connection attempts for as long as you specify the timeout (the backoff waits 1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100 milliseconds between each attempt, retrying every 100 milliseconds once 12 retries have been attempted¹). This is a reasonable default, but for more aggressive performance tuning we could manually set a busy_handler instead. The busy_timeout provides a higher level interface for setting the busy_handler that SQLite will use. It is possible, however, to set a custom busy_handler function ourselves tho. A common approach is to eschew exponential backoff and simply retry the connection as quickly as possible to establish a connection as soon as possible. In order to prevent infinite retries, we can simply cap the maximum number of retry attempts. Using the SQLite3 Ruby adapter, we can set a busy_handler by passing a proc, e.g.:

@raw_connection.busy_handler do |count|
  count <= @config[:retries]
end

In the implementation section, we will discuss how to enable our Rails application to set a max retries instead of a max timeout. For now, let's suffice to say that whether you use a busy_timeout or a busy_handler comes down to how optimistic you are about how and when you might experience BUSY exceptions.² For most Rails applications, I would recommend setting the busy_handler so that you can establish connections as quickly as possible.

Pragmas summary

These six pragmas can be configured in SQLite using the following SQL:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA journal_size_limit = 67108864 -- 64 megabytes;
PRAGMA mmap_size = 134217728 -- 128 megabytes;
PRAGMA cache_size = 2000;
PRAGMA busy_timeout = 5000;

This would provide a fine-tuned SQLite database for web application usage. In fact, this default configuration is precisely the default configuration used by Litestack for its Litedb module. So, I need to offer a big shout out to Litestack and @oldmoe for doing the hard work of forging the path to find an ideal default setup for Rails SQLite usage.

So, we have the six pragmas that we want to configure, and we have the values that we want to set. The only thing remaining is actually configuring our Rails application to consistently use these settings. As is the theme for this series, we want to enhance Rails, not override Rails. So, we need a mechanism that builds on top of Rails and provides similar flexibility as Rails.

Fine-tuning your Rails application

There does appear to be a natural hook point for configuring Rails' database adapters; unfortunately, it isn't publicly exposed for extension yet—this is the configure_connection method. As the comment explains, this method will

[p]erform any necessary initialization upon the newly-established @raw_connection -- this is the place to modify the adapter's connection settings, run queries to configure any application-global "session" variables, etc.

This sounds like exactly what we need. We can't hook into it naturally yet, so let's responsibly monkey-patch the SQLite adapter instead. Right now, the SQLite adapter will set the busy_timeout pragma if the timeout option is set and turn on the foreign_keys pragma in its configure_connection method. In order to extend this method, let's create an initializer file to extend the SQLite adapter.

We can use the ActiveSupport.on_load(:active_record_sqlite3adapter) hook to only extend the adapter when it is loaded. This block will be passed the SQLite3Adapter, so we can simply call prepend in the block. This means we can simply define a module that will extend the configure_connection method and then prepend that module into the adapter class. I put Rails extensions under the RailsExt module namespace, so let's create a RailsExt::SQLite3Adapter module:

# /config/initializers/active_record_sqlite3adapter.rb
module RailsExt
  module SQLite3Adapter
    # Perform any necessary initialization upon the newly-established
    # @raw_connection -- this is the place to modify the adapter's
    # connection settings, run queries to configure any application-global
    # "session" variables, etc.
    #
    # Implementations may assume this method will only be called while
    # holding @lock (or from #initialize).
    #
    # extends https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L691
    def configure_connection
      super

      # ...
    end
  end
end

We want to provide an enhancement to the Rails database configuration setup. In my opinion, setting up a pragmas section in the default portion of the database configuration provides a clear and flexible developer experience. We can then iterate over the pragmas hash and make the SQLite calls to set the pragmas in our extension module. This is simple to implement:

def configure_connection
  super

  @config[:pragmas].each do |key, value|
    raw_execute("PRAGMA #{key} = #{value}", "SCHEMA")
  end
end

This allows us to enhance our database configuration like so:

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  # time to wait (in milliseconds) to obtain a write lock before raising an exception
  # https://www.sqlite.org/pragma.html#pragma_busy_timeout
  timeout: 5000
  pragmas:
    # level of database durability, 2 = "FULL" (sync on every write), other values include 1 = "NORMAL" (sync every 1000 written pages) and 0 = "NONE"
    # https://www.sqlite.org/pragma.html#pragma_synchronous
    synchronous: "NORMAL"
    # Journal mode WAL allows for greater concurrency (many readers + one writer)
    # https://www.sqlite.org/pragma.html#pragma_journal_mode
    journal_mode: "WAL"
    # impose a limit on the WAL file to prevent unlimited growth (with a negative impact on read performance as well)
    # https://www.sqlite.org/pragma.html#pragma_journal_size_limit
    journal_size_limit: <%= 64.megabytes %>
    # set the global memory map so all processes can share data
    # https://www.sqlite.org/pragma.html#pragma_mmap_size
    # https://www.sqlite.org/mmap.html
    mmap_size: <%= 128.megabytes %>
    # increase the local connection cache to 2000 pages
    # https://www.sqlite.org/pragma.html#pragma_cache_size
    cache_size: 2000

And just like that, we have tuned our application's SQLite database to be better configured for web application usage, while also providing a clear and simple mechanism for setting additional SQLite pragmas as needed/desired.

As a capstone, let's talk about how to support a retries option as an alternative to the timeout option. There are two key details here. Firstly, it is important that retries and timeout option cannot both be set at the same time, as the busy_handler and busy_timeout are mutually exclusive. Secondly, it is important that this be the very first pragma that is set so that other pragma queries respect our busy handling logic. We can update our configure_connection method like so to support these features:

def configure_connection
  if @config[:timeout] && @config[:retries]
    raise ArgumentError, "Cannot specify both timeout and retries arguments"
  elsif @config[:retries]
    # see: https://www.sqlite.org/c3ref/busy_handler.html
    @raw_connection.busy_handler do |count|
      count <= @config[:retries]
    end
  end

  super

  @config[:pragmas].each do |key, value|
    raw_execute("PRAGMA #{key} = #{value}", "SCHEMA")
  end
end

Now, we can replace the timeout: 5000 setting with a retries: 1000 setting instead, and the appropriate busy_handler will get setup.

You can find the files we have written throughout this post in this Gist

This is mentioned by a maintainer in a forum response and can be seen in the sqliteDefaultBusyCallback method in the main.c file. ↩
If you want to dive deeper into understanding how and when SQLite will throw a BUSY exception, this is an excellent blog post: https://activesphere.com/blog/2018/12/24/understanding-sqlite-busy. ↩

Branch-specific databases with SQLite

Stephen Margheim — Thu, 07 Sep 2023 13:02:58 +0000

This is the first in a collection of posts where I want to highlight ways we can enhance our Ruby on Rails applications. Specifically, in this first series, I want to dig into the ways that we can take advantage of and empower using SQLite as the database engine for our Rails applications. In this inaugural post, let's dig into how using SQLite as our database engine opens up powerful new possibilities for our local development workflow; specifically, allowing us to have and use branch-specific databases.

There has been a surge of interest in using SQLite in production for web applications in the last few years. I readily confess that I am fully onboard as well. SQLite removes network latency, simplifies operations, and makes automated testing against your production stack easy.

Over the course of this series, I want to lay out some of the key tweaks we can make to our Rails applications to make working with SQLite more powerful and pleasurable. To start, I want to focus on a developer experience (DX) improvement for local development.

As anyone who has worked on a Rails application within a team of developers knows, managing your database schema can be tricky. Each developer's branches might include some migrations, which update the schema, but locally you only have the one single development database. Switching between branches becomes a pain, running migrations becomes a pain, and sometimes bugs sneak into production as schema changes are merged that shouldn't have been a part of that release.

One of my favorite features from PlanetScale is their branching feature:

Branches are copies of your database schema that live in a completely separate environment from each other. Making changes in one branch does not affect other branches until you merge them, just like you manage code branches in your projects.

This is lovely, and it solves the pain points laid out above. However, this isn't quite perfect in my opinion. Because we now have two different kinds of "branches" for our app. We have our Git branches, which isolate our code (including migrations), and our database branches, which isolate our schema. Having two separate branches now creates syncing issues. How do Git branches and database branches relate? How do we tie git branch merging to database branch merging to production deployment? etc.

This is a necessary trade-off that PlanetScale needs to make, because their serverless database platform can't be deeply integrated with every single user's codebase. However, as Rails developers using SQLite, we have unique opportunities available to us.

Let's describe our ideal scenario, and then dig into how to implement it. What we want is to have a single branch (a Git branch) which isolates both our code and our schema. We want switching Git branches to automatically switch schema branches. We want production deployment driven by Git branch merging that also automatically ensures a predictable and stable production schema. Sounds nice, doesn't it? Well, luckily for us, Rails and SQLite make such a setup remarkably easy to create.

Let's start with our last feature, as this is something that Rails gives us, regardless of our database engine. This feature is precisely the value of our /migrations directory and the /db/schema.rb or /db/structure.sql file. By integrating our database schema management into our Rails application codebase, and ensuring that all schema changes are implemented via Rails migrations, we can bind our production schema to simple Git branch merging, while also ensuring that our production schema is predictable and stable.

To be honest, the other two features are also possible with any of Rails' supported database adapters, but SQLite fits most nicely with the approach. And the approach is simple. Fundamentally, all we need to do is tell Rails to use a dynamic database name, tied to the Git branch name, for local development. Rails makes this easy through the /config/database.yml file, which is where we configure the core details of our database. By default, when using SQLite, Rails will generate a /config/database.yml that looks like this:

# SQLite. Versions 3.8.0 and up are supported.
#   gem install sqlite3
#
#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem "sqlite3"
#
default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: storage/development.sqlite3

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: storage/test.sqlite3

production:
  <<: *default
  database: storage/production.sqlite3

Simple and reasonable. Each Rails environment gets its own database file. Every database file is stored in the /storage directory (where most hosting providers ensure that contents are persisted across deployments). And each database file has a fixed file name. What we want is to have our development environment use a dynamic file name for the database, and we want that file name to be based on the current Git branch. A quick Google search leads to a StackOverflow answer that provides the git command for getting the current branch name:

git branch --show-current

Note: If you are using a Git version less than 2.22 (when the --show-current option was added), you can use git rev-parse --abbrev-ref HEAD instead.

So, how do we use this in our /config/database.yml file? Well, Rails makes this easy as it allows for ERB. Adding a tiny bit of resilience to the code, we can replace our development section with this:

development:
  <<: *default
  database: storage/<%= `git branch --show-current`.chomp || 'development' %>.sqlite3

Now, instead of always using storage/development.sqlite3 as our database file name, we provide a dynamic file name that will name the database file whatever our current Git branch name is. With this single line change we have implemented the first of our ideal features—we have a single branch (a Git branch) which isolates both our code and our schema.

Next, how can we configure Rails to automatically switch "schema branches" when our Git branch changes? Well, here we need to articulate with a bit more clarity what exactly we need, given our solution above. The change to the /config/database.yml file already ensures that when we switch Git branches, our Rails app will talk to an isolated development database. However, this change alone doesn't ensure that once we have switched Git branches that our isolated development database is ready for use. Imagine that a colleague has created a branch which adds two new database migrations. You pull down that branch to do some code review and local manual testing. When you switch your local Git repo to checkout your colleague's branch for the first time, our dynamic /config/database.yml configuration will ensure that a new SQLite database file is created in our /storage directory. However, this new SQLite database file doesn't yet have anything in it, and it doesn't have the schema setup either. So, how can we ensure that Rails automatically prepares this new database file whenever we switch database branches?

Well, again, luckily for us Rails makes this pretty easy. Rails provides the ActiveRecord::Tasks::DatabaseTasks utility class, which "encapsulates logic behind common tasks used to manage database and migrations." For our needs, we can turn to the .prepare_all method, which is the programmatic equivalent to the db:prepare Rake command added in Rails 6. Preparing a database means, simply, running migrations if the database already exists or creating the database and loading the schema if not. All we need is to tell Rails to run this command in development every time we boot up the app. This will ensure that our dynamic database is always ready for use by our Rails app (whether running the server or jumping into a console).

In order to have Rails run this command in development when we boot that app, we can simply add this to our /config/environments/development.rb file:

# Ensure that our branch-specific SQLite database is prepared for our application to use
config.after_initialize do
  ActiveRecord::Tasks::DatabaseTasks.prepare_all
end

We simply configure our development environment to run the .prepare_all command after the app has been initialized.

Which this simple configuration added, we now have our ideal setup. Every Git branch has its own, isolated database file. That database is automatically prepared for usage on-demand when we boot our Rails app. And production deploys driven by Git branch merging continues to produce stable and predictable production schemas, by using migrations exclusively to alter our schema.

I can say, I have been using this setup in a few different Rails applications and I absolutely love it! And I love how easy Rails and SQLite make such a feature to setup. This was a grand total of four lines (and could easily be two if we used an inline block for after_initialize) to provide a similar (and in some key ways improved) feature to a fancy platform like PlanetScale.

It is precisely these kinds of enhancements—simple, small, but powerful—that I want to explore in the coming weeks and months. So, stay tuned. And, if you enjoyed this tip, please do reach out on Twitter @fractaledmind.

Why parameterized objects in Rails are so powerful

Stephen Margheim — Tue, 17 Jan 2023 15:40:49 +0000

Matt Swanson wrote an insightful post today on how to level-up your usage of Mailers in your Rails application. In it, he discussed how using "parameterized mailers" unlocked the ability to build mailers that could be sent from either a custom domain or the applications default domain. As he details in his post, a parameterized mailer is initialized with params before calling the mailer method. So instead of:

NotificationMailer
  .comment_reply(user, comment)
  .deliver_later

You would write:

NotificationMailer
  .with(user: user, comment: comment)
  .comment_reply
  .deliver_later

I tweeted a quick response to Matt noting that parameterizing jobs can similarly unlock some powerful and useful functionality. Matt reminded me that a fuller post would be more helpful. So, here we are.

Well, what is "parameterizing" something like a mailer? While the implementation in Rails is somewhat more complicated for mailers, in essence with is simply an alias for new. That is, to parameterize is to initialize with state. Instead of passing all of the necessary information into the instance method, we pass it into the initialization method.

As Matt says in his post, this can feel like a difference without a point:

My first impression was that I didn’t quite understand the point of this. I generally prefer having the explicit method arguments on the mailer method compared to a generic params hash.

But, of course, there is a point. The point is that by moving the required state into a class instance, we make it possible to extend, prepend, and inject behavior that alters how the executing method behaves. In Matt's example, he used a before callback to inject behavior that switches the from field for the address, depending on whether the Account has a custom domain set or not.

Notably, ActiveJob is "parameterized" by default. As you recall, you define the executing method via def perform in your job class; that is, you define an instance method named perform. But, you invoke the job by using class methods—either perform_now or perform_later. Under the hood, ActiveJob's class methods are little more than short-hand for initializing the job class with the passed parameters and invoking the perform method:

def self.perform_now(...)
  job = new(...)
  job.perform(*job.arguments)
end

This isn't exactly what the source code for perform_now looks like, but the essence is the same. And what that means is that before your perform method is invoked, your job has all of the information it needs for this particular execution. This is what allows callbacks to be useful, for example.

In my particular case, this has proven so powerful because it allows something like AcidicJob to exist. AcidicJob is a gem that provides a suite of features that you can use to make your jobs both more coherent and more resilient. And, at its heart, it functions by serializing and deserializing your job execution into a database record, and then leaning on the ACID guarantees provided by database engines for transactions. This is easy to do with ActiveJob, since the job instances are parameterized and thus can be serialized before the job is executed with the full set of information needed to execute that job.

At present, such behavior is not possible with pure Sidekiq because Sidekiq does not initialize a worker/job instance with the parameters needed to execute the worker/job. I started a conversation on why this would be valuable, but we haven't yet found an API that works well in the context of Sidekiq. This isn't to say anything negative about Sidekiq, which is fantastic software, but simply to point out what kind of flexibility is unlocked when parameterizing operations like jobs and mailers.

Matt's example is only one of many, many possible ways to make your mailers or jobs more flexible, coherent, and perhaps even resilient by leaning on the power of parameterized classes.

Forem: Stephen Margheim

ActiveRecord adapter improvements

Local snapshots

Snapshotting your database

Restoring a snapshot

Array columns in SQLite

Optimizing SQLite compilation

Setting up Litestream

Loading extensions

Linked headings in your BridgetownRB site

Fine-tuning your SQLite database

The journal_mode pragma

The synchronous pragma

The journal_size_limit pragma

The mmap_size pragma

The cache_size pragma

The busy_timeout/busy_handler pragma

Pragmas summary

Fine-tuning your Rails application

Branch-specific databases with SQLite

Why parameterized objects in Rails are so powerful

The `journal_mode` pragma

The `synchronous` pragma

The `journal_size_limit` pragma

The `mmap_size` pragma

The `cache_size` pragma

The `busy_timeout`/`busy_handler` pragma