$30 off During Our Annual Pro Sale. View Details »

Solid SQLite Apps on Rails

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.

Stephen

June 13, 2024
Tweet

More Decks by Stephen

Other Decks in Programming

Transcript

  1. 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 🇺🇸 🛫 🇩🇪 💒 🇩🇰 📍 🇯🇲 💕 🐶🐕
  2. 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
  3. # 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
  4. # 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 # …
  5. # config/database.yml cache: &cache <<: *default migrations_paths: db/cache_migrate database: storage/<%=

    Rails.env %>-cache.sqlite3 # … development: primary: *primary queue: *queue cache: *cache
  6. # config/solid_cache.yml default: &default database: cache store_options: max_age: <%= 1.week.to_i

    %> max_size: <%= 256.megabytes %> namespace: <%= Rails.env %> # …
  7. # 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
  8. # 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]"
  9. • 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)
  10. # 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
  11. # 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
  12. • deferred foreign keys, • generated columns, • returning values

    from inserts, • PRAGMA tuning, • extension loading, and • improved concurrency support $ bundle add activerecord-enhancedsqlite3-adapter
  13. 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
  14. $ bundle add sqlite-vss # /config/database.yml default: &default adapter: sqlite3

    pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 extensions: - sqlite_vss
  15. $ 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'"
  16. # 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