Enhancing your Rails app with SQLite: Loading extensions

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

All posts in this series