Slide 1
Slide 1 text
Stephen Margheim Β· 13 June 2024
Solid SQLite Apps
on Rails
Baltic Ruby
Slide 2
Slide 2 text
Who am I?
Stephen Margheim Β· @fractaledmind
πΊπΈ π« π©πͺ π π©π° π π―π² π πΆπ
Slide 3
Slide 3 text
Who am I?
β’ Head of Engineering at Test IO
β’ Open source developer
rails/rails
sparklemotion/sqlite3-ruby
oldmoe/litestack
joeldrapper/phlex
joeldrapper/literal
fractaledmind/activerecord-enhancedsqlite3-adapter
fractaledmind/litestream-ruby
fractaledmind/acidic_job
β’ Writer: https://fractaledmind.github.io
Stephen Margheim Β· @fractaledmind
πΊπΈ π« π©πͺ π π©π° π π―π² π πΆπ
Slide 4
Slide 4 text
rails new .
--database=sqlite3
--asset-pipeline=propshaft
--javascript=esbuild
--css=tailwind
--skip-jbuilder
--skip-spring
Slide 5
Slide 5 text
$ rails new .
--database=sqlite3
--asset-pipeline=propshaft
--javascript=esbuild
--css=tailwind
--skip-jbuilder
--skip-spring
Slide 6
Slide 6 text
Why?
Slide 7
Slide 7 text
No content
Slide 8
Slide 8 text
No content
Slide 9
Slide 9 text
No content
Slide 10
Slide 10 text
No content
Slide 11
Slide 11 text
No content
Slide 12
Slide 12 text
No content
Slide 13
Slide 13 text
Simplicity
Slide 14
Slide 14 text
Simplicity
Control
Slide 15
Slide 15 text
Simplicity
Control
Speed
Slide 16
Slide 16 text
No content
Slide 17
Slide 17 text
How?
Slide 18
Slide 18 text
Running SQLite on
Rails out-of-the-box
isnβt viable today.
Slide 19
Slide 19 text
$ bundle add activerecord-enhancedsqlite3-adapter
$ bundle add litestream
$ rails generate litestream:install
Slide 20
Slide 20 text
π Performance
π Solid Gems
π Resilience
π Enhancements
π Deployment
Slide 21
Slide 21 text
Performance
Slide 22
Slide 22 text
1. Write-Ahead-Logging journal mode
2. IMMEDIATE transactions
3. non-GVL-blocking timeout mechanism
4. fair retry interval
5. isolated connection pools for reading and writing
Slide 23
Slide 23 text
$ bundle add activerecord-enhancedsqlite3-adapter
https://github.com/fractaledmind/activerecord-enhancedsqlite3-adapter
Slide 24
Slide 24 text
config.enhanced_sqlite3.isolate_connection_pools = true
Slide 25
Slide 25 text
Performance π
Slide 26
Slide 26 text
Solid Gems
Slide 27
Slide 27 text
$ bundle add solid-queue
$ bundle add mission_control-jobs
$ bundle add solid-cache
$ bundle add solid-errors
Slide 28
Slide 28 text
Solid Queue
Slide 29
Slide 29 text
# config/database.yml
queue: &queue
<<: *default
migrations_paths: db/queue_migrate
database: storage/<%= Rails.env %>-queue.sqlite3
primary: &primary
<<: *default
database: storage/<%= Rails.env %>.sqlite3
development:
primary: *primary
queue: *queue
Slide 30
Slide 30 text
$ DATABASE=queue bin/rails generate solid_queue:install
$ bin/rails db:migrate:queue
Slide 31
Slide 31 text
# config/application.rb
config.active_job.queue_adapter = :solid_queue
config.solid_queue.connects_to = {
database: { writing: :queue }
}
Slide 32
Slide 32 text
# config/solid_queue.yml
default: &default
dispatchers:
- polling_interval: 1
batch_size: 500
recurring_tasks:
periodic_litestream_backup_verfication_job:
class: Litestream::VerificationJob
args: []
schedule: every day at 1am EST
# β¦
Slide 33
Slide 33 text
Solid Cache
Slide 34
Slide 34 text
# config/database.yml
cache: &cache
<<: *default
migrations_paths: db/cache_migrate
database: storage/<%= Rails.env %>-cache.sqlite3
# β¦
development:
primary: *primary
queue: *queue
cache: *cache
Slide 35
Slide 35 text
$ DATABASE=cache bin/rails solid_cache:install
$ bin/rails db:migrate:cache
$ bin/rails dev:cache
Slide 36
Slide 36 text
# config/solid_cache.yml
default: &default
database: cache
store_options:
max_age: <%= 1.week.to_i %>
max_size: <%= 256.megabytes %>
namespace: <%= Rails.env %>
# β¦
Slide 37
Slide 37 text
Solid Errors
Slide 38
Slide 38 text
# config/database.yml
errors: &errors
<<: *default
migrations_paths: db/error_migrate
database: storage/<%= Rails.env %>-errors.sqlite3
# β¦
development:
primary: *primary
queue: *queue
cache: *cache
errors: *errors
Slide 39
Slide 39 text
$ bin/rails generate solid_errors:install --database errors
$ bin/rails db:migrate:errors
Slide 40
Slide 40 text
# config/application.rb
config.solid_errors.connects_to = {
database: { writing: :errors }
}
config.solid_errors.send_emails = true
config.solid_errors.email_from = "[email protected]"
config.solid_errors.email_to = β[email protected]"
Slide 41
Slide 41 text
β’ Active Record (e.g. PostgreSQL, MySQL)
β’ Solid Cache (e.g. Redis, Memcached)
β’ Solid Queue (e.g. Sidekiq, Goodjob)
β’ Solid Errors (e.g. Honeybadger, App Signal)
β’ Solid Cable* (e.g. Redis, PostgreSQL)
Slide 42
Slide 42 text
Solid Gems π
Slide 43
Slide 43 text
Resilience
Slide 44
Slide 44 text
https://litestream.io
Slide 45
Slide 45 text
$ bundle add litestream
$ bin/rails generate litestream:install
https://github.com/fractaledmind/litestream-ruby
Slide 46
Slide 46 text
# config/litestream.yml
dbs:
- path: storage/production.sqlite3
replicas:
- type: s3
bucket: $LITESTREAM_REPLICA_BUCKET
path: storage/production.sqlite3
access-key-id: $LITESTREAM_ACCESS_KEY_ID
secret-access-key: $LITESTREAM_SECRET_ACCESS_KEY
Slide 47
Slide 47 text
# config/initializers/litestream.rb
Litestream.configure do |config|
creds = Rails.application.credentials.litestream
config.replica_bucket = creds.replica_bucket
config.replica_key_id = creds.replica_key_id
config.replica_access_key = creds.replica_access_key
end
Slide 48
Slide 48 text
$ bin/rails litestream:replicate
Slide 49
Slide 49 text
$ bin/rails litestream:restore -- \
βdatabase=storage/production.sqlite3
Slide 50
Slide 50 text
class Litestream::VerificationJob < ApplicationJob
queue_as :default
def perform
Litestream::Commands.databases.each do |db_hash|
Litestream.verify!(db_hash["path"])
end
end
end
Slide 51
Slide 51 text
No content
Slide 52
Slide 52 text
Resilience π
Slide 53
Slide 53 text
Enhancements
Slide 54
Slide 54 text
β’ deferred foreign keys,
β’ generated columns,
β’ returning values from inserts,
β’ PRAGMA tuning,
β’ extension loading, and
β’ improved concurrency support
$ bundle add activerecord-enhancedsqlite3-adapter
Slide 55
Slide 55 text
add_reference :person, :alias,
foreign_key: { deferrable: :deferred }
add_reference :alias, :person,
foreign_key: { deferrable: :deferred }
Slide 56
Slide 56 text
create_table :virtual_columns, force: true do |t|
t.string :name
t.virtual :upper_name, type: :string,
as: "UPPER(name)", stored: true
t.virtual :lower_name, type: :string,
as: "LOWER(name)", stored: false
t.virtual :octet_name, type: :integer,
as: "LENGTH(name)"
end
Slide 57
Slide 57 text
Post.insert({
title: Faker::Lorem.sentence,
description: Faker::Lorem.paragraphs.join("\n"),
created_at: Faker::Time.forward(days: 23)
}, returning: [ :id, :created_at ])
Slide 58
Slide 58 text
default: &default
adapter: sqlite3
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
timeout: 5000
pragmas:
temp_store: MEMORY
Slide 59
Slide 59 text
$ bundle add sqlite-vss
# /config/database.yml
default: &default
adapter: sqlite3
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
timeout: 5000
extensions:
- sqlite_vss
Slide 60
Slide 60 text
$ bundle config set build.sqlite3 \
"--with-sqlite-cflags='
-DSQLITE_DQS=0
-DSQLITE_THREADSAFE=0
-DSQLITE_DEFAULT_MEMSTATUS=0
-DSQLITE_LIKE_DOESNT_MATCH_BLOBS
-DSQLITE_MAX_EXPR_DEPTH=0
-DSQLITE_OMIT_SHARED_CACHE
-DSQLITE_USE_ALLOCA'"
Slide 61
Slide 61 text
# config/database.yml
development:
<<: *default
database: storage/<%= `git branch --show-current`.chomp
|| 'development' %>.sqlite3
# config/environments/development.rb
config.after_initialize do
ActiveRecord::Tasks::DatabaseTasks.prepare_all
end
Slide 62
Slide 62 text
Enhancements π
Slide 63
Slide 63 text
Deployment
Slide 64
Slide 64 text
https://hatchbox.io
Slide 65
Slide 65 text
https://kamal-deploy.org/
Slide 66
Slide 66 text
Deployment π
Slide 67
Slide 67 text
β
Performance
β
Solid Gems
β
Resilience
β
Enhancements
β
Deployment
Slide 68
Slide 68 text
https://fractaledmind.gumroad.com/l/sqlite-on-rails
Slide 69
Slide 69 text
No content
Slide 70
Slide 70 text
Thank you