A Rails initializer to enhance the SQLite adapter to allow for pragmas to be configured in the /config/database.yml file

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).
    #
    # https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L691
    def configure_connection
      if @config[:timeout] && @config[:retries]
        raise ArgumentError, "Cannot specify both timeout and retries arguments"
      elsif @config[:retries]
        # https://www.sqlite.org/c3ref/busy_handler.html
        # https://sqlite.org/forum/info/3fd33f0b9be72353
        # sqliteDefaultBusyCallback
        # https://sqlite.org/src/file?name=src/main.c&ci=trunk
        @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
  end
end

# Enhance the SQLite3 ActiveRecord adapter with optimized defaults and extensions
ActiveSupport.on_load(:active_record_sqlite3adapter) do
  # self refers to `SQLite3Adapter` here,
  # so we can call .prepend
  prepend RailsExt::SQLite3Adapter
end

database.yml

# 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 } %>
  # connection attempts to make immediately before throwing a BUSY exception
  retries: 1000
  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

development:
  <<: *default
  database: storage/<%= `git rev-parse --abbrev-ref HEAD`.chomp || '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