How (and why) to run SQLite in production

You've heard the whispers, sensed the hype, but you're still not sure what all the fuss is about with SQLite these days. Join me as we explore the use-cases and benefits of running SQLite in a production environment. Along the way, we will learn why SQLite makes sense as your next production database and how to ensure that your setup is optimized for end-user performance and developer happiness. We will setup and deploy a full Rails application—with caching, background jobs, websockets, and full-text search all backed by SQLite—in these 40 minutes. Come along for the ride!

Transcript

1. Stephen Margheim · 16 December 2023 SQLite on Rails How (and why) to run SQLite in production
2. 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 🇺🇸 🛫 🇩🇪 💒 🇷🇺
3. $ rails new . --database=sqlite3 --asset-pipeline=propshaft --javascript=esbuild --css=tailwind --skip-jbuilder --skip-spring
4. $ rails new . --database=sqlite3 --asset-pipeline=propshaft --javascript=esbuild --css=tailwind --skip-jbuilder --skip-spring
5. Why?
6. Why not?
8. Simplicity
9. Simplicity Control
10. Simplicity Control Speed
11. How?
12. MVP
13. $ rails new . --database=sqlite3 --asset-pipeline=propshaft --javascript=esbuild --css=tailwind --skip-jbuilder --skip-spring
15. Demo →
16. Deployment
46. Running demo.sqliteonrails.com
47. $ hey -c N -z 10s -m POST / https://demo.sqliteonrails.com/benchmarking/read_heavy $ hey -c N -z 10s -m POST / https://demo.sqliteonrails.com/benchmarking/write_heavy $ hey -c N -z 10s -m POST / https://demo.sqliteonrails.com/benchmarking/balanced ⇒ 16, 24, 22 ⇒ 21 RPS N ⇒ [1, 2, 4, 8, 16, 32, 64, 128, 256] ×2
48. $ hey -c N -z 10s -m POST / https://demo.sqliteonrails.com/benchmarking/balanced ⇒ 14 RPS N ⇒ [1, 2, 4, 8, 16, 32, 64, 128, 256] ×2
49. Average Peak 2 34 3.5 ?? 19.3 29.6 33 116 36.67 66.67 66 110
50. Running SQLite in production with a Rails app wasn’t viable 1 year ago. But today, it is.
51. Rails default PRAGMAs PRAGMA foreign_keys = ON; 
 PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA mmap_size = 134217728; -- 128 megabytes PRAGMA journal_size_limit = 67108864; -- 64 megabytes PRAGMA cache_size = 2000; >= 7.1.0
52. SQLite default compilation SQLITE_DEFAULT_WAL_SYNCHRONOUS=1 SQLITE_ENABLE_FTS5
53. Enhancing
54. Enhancing ActiveRecord $ bundle add activerecord-enhancedsqlite3-adapter
55. Enhancing ActiveRecord • deferred foreign keys, • generated columns, • PRAGMA tuning, • extension loading, and • improved concurrency support How?
56. Enhancing ActiveRecord $ bundle add sqlite-vss # /config/database.yml default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 extensions: - sqlite_vss Extension loading
57. Enhancing ActiveRecord Improved concurrency support Demo →
58. Enhancing ActiveRecord busy_handler do |count| timed_out = false if (count % 100).zero? timed_out = (count * retry_interval) > timeout_seconds end if timed_out false else sleep(retry_interval) true end end Improved concurrency support
59. Fine-tuning compilation $ bundle config set build.sqlite3 \ "--with-sqlite-cflags=' -DSQLITE_DQS=0 -DSQLITE_THREADSAFE=0 -DSQLITE_DEFAULT_MEMSTATUS=0 -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 -DSQLITE_LIKE_DOESNT_MATCH_BLOBS -DSQLITE_MAX_EXPR_DEPTH=0 -DSQLITE_OMIT_PROGRESS_CALLBACK -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_USE_ALLOCA -DSQLITE_ENABLE_FTS5'"
60. Extending
61. Branch-specific databases # /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. Integrating Litestream $ bundle add litestream $ bin/rails generate litestream:install Backups/replication
63. Integrating Litestack $ bundle add litestack $ bin/rails generate litestack:install SQLite all the things!
64. • Database Server (e.g. PostgreSQL, MySQL) • Cache Server (e.g. Redis, Memcached) • Job Processor (e.g. Sidekiq, Goodjob) • Pubsub Server (e.g. Redis, PostgreSQL) • Fulltext Search Server (e.g. Elasticsearch, Mielisearch) What? Integrating Litestack
66. Thank you