Balkan Ruby 2024 — How and why to run SQLite on Rails in production

Learn how to run a Rails application in production with SQLite, covering performance, resilience, enhancements, and deployment.

Transcript

1. Stephen Margheim · 27 April 2024 SQLite on Rails How (and why) to run SQLite in production Balkan 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?
7. Why not?
8. Linear Writes
10. Why?
16. Simplicity
17. Simplicity Control
18. Simplicity Control Speed
19. Why?
21. How?
22. Running SQLite on Rails out-of-the-box isn’t viable today.
23. $ bundle add activerecord-enhancedsqlite3-adapter $ bundle add litestream $ rails generate litestream:install
24. Thank you
25. $ bundle add activerecord-enhancedsqlite3-adapter $ bundle add litestream $ rails generate litestream:install
26. Performance
27. 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
28. $ bundle add activerecord-enhancedsqlite3-adapter https://github.com/fractaledmind/activerecord-enhancedsqlite3-adapter
29. config.enhanced_sqlite3.isolate_connection_pools = true
30. Performance 👍
31. Resilience
32. https://litestream.io
33. $ bundle add litestream $ bin/rails generate litestream:install https://github.com/fractaledmind/litestream-ruby
34. # 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
35. # 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
36. $ bin/rails litestream:replicate
37. $ bin/rails litestream:restore -- \ —database=storage/production.sqlite3
38. $ bin/rails litestream:verify -- \ —database=storage/production.sqlite3 size original 21688320 restored 21688320 delta 0 tables original 9 restored 9 delta 0
40. $ bundle add litestream_rails # config/routes.rb mount LitestreamRails::Engine => "/litestream" https://github.com/fractaledmind/litestream_rails
41. Resilience 👍
42. Enhancements
43. • deferred foreign keys, • generated columns, • returning values from inserts, • PRAGMA tuning, • extension loading, and • improved concurrency support $ bundle add activerecord-enhancedsqlite3-adapter
44. add_reference :person, :alias, foreign_key: { deferrable: :deferred } add_reference :alias, :person, foreign_key: { deferrable: :deferred }
45. 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
46. Post.insert({ title: Faker::Lorem.sentence, description: Faker::Lorem.paragraphs.join("\n"), created_at: Faker::Time.forward(days: 23) }, returning: [ :id, :created_at ])
47. default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 pragmas: temp_store: MEMORY
48. $ bundle add sqlite-vss # /config/database.yml default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 extensions: - sqlite_vss
49. • Active Record (e.g. PostgreSQL, MySQL) • Solid Cache (e.g. Redis, Memcached) • Solid Queue (e.g. Sidekiq, Goodjob) • Solid Cable* (e.g. Redis, PostgreSQL)
50. $ 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'"
51. # /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
52. Enhancements 👍
53. Deployment
54. https://hatchbox.io
55. https://kamal-deploy.org/
56. Deployment 👍
57. ✅ Performance ✅ Resilience ✅ Enhancements ✅ Deployment
59. Thank you