Solid SQLite Apps on Rails

Join me to learn how to pair the enhancements to Rails’ SQLite adapter with the suite of Solid libraries to create resilient, high-performance production apps.

Transcript

1. Stephen Margheim · 13 June 2024 Solid SQLite Apps on Rails Baltic Ruby
2. Who am I? Stephen Margheim · @fractaledmind 🇺🇸 🛫 🇩🇪 💒 🇩🇰 📍 🇯🇲 💕 🐶🐕
3. 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 🇺🇸 🛫 🇩🇪 💒 🇩🇰 📍 🇯🇲 💕 🐶🐕
4. rails new . --database=sqlite3 --asset-pipeline=propshaft --javascript=esbuild --css=tailwind --skip-jbuilder --skip-spring
5. $ rails new . --database=sqlite3 --asset-pipeline=propshaft --javascript=esbuild --css=tailwind --skip-jbuilder --skip-spring
6. Why?
13. Simplicity
14. Simplicity Control
15. Simplicity Control Speed
17. How?
18. Running SQLite on Rails out-of-the-box isn’t viable today.
19. $ bundle add activerecord-enhancedsqlite3-adapter $ bundle add litestream $ rails generate litestream:install
20. 📝 Performance 📝 Solid Gems 📝 Resilience 📝 Enhancements 📝 Deployment
21. Performance
22. 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
23. $ bundle add activerecord-enhancedsqlite3-adapter https://github.com/fractaledmind/activerecord-enhancedsqlite3-adapter
24. config.enhanced_sqlite3.isolate_connection_pools = true
25. Performance 👍
26. Solid Gems
27. $ bundle add solid-queue $ bundle add mission_control-jobs $ bundle add solid-cache $ bundle add solid-errors
28. Solid Queue
29. # 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
30. $ DATABASE=queue bin/rails generate solid_queue:install $ bin/rails db:migrate:queue
31. # config/application.rb config.active_job.queue_adapter = :solid_queue config.solid_queue.connects_to = { database: { writing: :queue } }
32. # 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 # …
33. Solid Cache
34. # config/database.yml cache: &cache <<: *default migrations_paths: db/cache_migrate database: storage/<%= Rails.env %>-cache.sqlite3 # … development: primary: *primary queue: *queue cache: *cache
35. $ DATABASE=cache bin/rails solid_cache:install $ bin/rails db:migrate:cache $ bin/rails dev:cache
36. # config/solid_cache.yml default: &default database: cache store_options: max_age: <%= 1.week.to_i %> max_size: <%= 256.megabytes %> namespace: <%= Rails.env %> # …
37. Solid Errors
38. # 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
39. $ bin/rails generate solid_errors:install --database errors $ bin/rails db:migrate:errors
40. # config/application.rb config.solid_errors.connects_to = { database: { writing: :errors } } config.solid_errors.send_emails = true config.solid_errors.email_from = "errors@baltic.rb" config.solid_errors.email_to = “devs@baltic.rb"
41. • 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)
42. Solid Gems 👍
43. Resilience
44. https://litestream.io
45. $ bundle add litestream $ bin/rails generate litestream:install https://github.com/fractaledmind/litestream-ruby
46. # 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
47. # 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
48. $ bin/rails litestream:replicate
49. $ bin/rails litestream:restore -- \ —database=storage/production.sqlite3
50. class Litestream::VerificationJob < ApplicationJob queue_as :default def perform Litestream::Commands.databases.each do |db_hash| Litestream.verify!(db_hash["path"]) end end end
52. Resilience 👍
53. Enhancements
54. • deferred foreign keys, • generated columns, • returning values from inserts, • PRAGMA tuning, • extension loading, and • improved concurrency support $ bundle add activerecord-enhancedsqlite3-adapter
55. add_reference :person, :alias, foreign_key: { deferrable: :deferred } add_reference :alias, :person, foreign_key: { deferrable: :deferred }
56. 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
57. Post.insert({ title: Faker::Lorem.sentence, description: Faker::Lorem.paragraphs.join("\n"), created_at: Faker::Time.forward(days: 23) }, returning: [ :id, :created_at ])
58. default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 pragmas: temp_store: MEMORY
59. $ bundle add sqlite-vss # /config/database.yml default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 extensions: - sqlite_vss
60. $ 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'"
61. # 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
62. Enhancements 👍
63. Deployment
64. https://hatchbox.io
65. https://kamal-deploy.org/
66. Deployment 👍
67. ✅ Performance ✅ Solid Gems ✅ Resilience ✅ Enhancements ✅ Deployment
68. https://fractaledmind.gumroad.com/l/sqlite-on-rails
70. Thank you