gistfile1.txt

# sqlite migration checklist:

 - [x] main:
  - [x] set the site in read-only mode (config/application.rb on prod)
  - [x] link to this gist from app/views/layouts/application.html.erb
 - [x] lobsters-deploy
 - [x] take mariadb backup to pushcx's desktop
 - [x] scp lib/tasks/migrate.rake l:lobsters/current/lib/tasks
 - [x] be rails dump_db
 - [x] scp db to my local dev
 - [x] disable screensharing a sec
 - [x] edit prod config/database.yml:
             primary:
           #    <<: *trilogy
           #    database: lobsters
               <<: *sqlite3
               database: db/primary.sqlite3
 - [x] scp db/development/primary.sqlite3 l:lobsters/shared/storage/
 - [x] be rails load_db
 - [x] jj edit pmynxpks; jj bookmark set main; jj up
 - [x] lobsters-deploy
 - [ ] ~if prod doesn't immediately melt down, rm read-only mode, notice, and trilogy gem~
 - [ ] ~take a sqlite backup to local dev~
 - [ ] ~run the backup script~
 - [ ] ~confirm the backup worked~
 - [ ] ~tomorrow 2026-02-22: confirm the automated backup script ran~

# revert checklist

 - [x] revert the merge commit
 - [x] stop puma
 - [x] revert DATABASE_URL
 - [x] revert prod config/database.yml
 - [x] lobsters-deploy
 - [x] restart puma
 - [x] restart bg jobs
 - [x] remove the layout note, read-only mode
 - [x] lobsters-deploy
 - [x] prod: mv storage/primary.sqlite3 ~

## perf notes

Lobsters(prod)> ActiveRecord::Base.connection.execute("PRAGMA foreign_keys")
=> [{"foreign_keys" => 1}]
Lobsters(prod)> ActiveRecord::Base.connection.execute("PRAGMA journal_mode")
=> [{"journal_mode" => "wal"}]
Lobsters(prod)> ActiveRecord::Base.connection.execute("PRAGMA synchronous")
=> [{"synchronous" => 1}]
Lobsters(prod)> ActiveRecord::Base.connection.execute("PRAGMA mmap_size")
=> [{"mmap_size" => 134217728}]
Lobsters(prod)> ActiveRecord::Base.connection.execute("PRAGMA journal_size_limit")
=> [{"journal_size_limit" => 67108864}]
Lobsters(prod)> ActiveRecord::Base.connection.execute("PRAGMA cache_size")
=> [{"cache_size" => 2000}]


DATABASE_URL
sqlite3:///home/deploy/lobsters/current/storage/primary.sqlite3

History being re-written (to SQLite)

MariaDB -> SQLite.... seems like a downgrade unless I'm missing something?

Edit: yep i was

further reading here seemingly

And from the resulting PR:

Thanks for all this work, what a big project!

Any readers: we're on a call and going to migrate prod now.

🦀🦀🦀 lobste.rs is gone 🦀🦀🦀

wild to see in 2026, great stuff

inspiring move, let's gooo <3

Godspeed!

is this just fanta sea?

i am a heron. i haev a long neck and i pick fish out of the water w/ my beak. if you dont repost this comment on 10 other pages i will fly into your kitchen tonight and make a mess of your pots and pans

I was here for the SQLite migration!

2/22 never forget

btw can someone hire me, im looking for some k8s work, that'd be great, ty ty

to sqlite 😮

😂

🚀

What was the rationale for migrating to SQLite in the first place?

What was the rationale for migrating to SQLite in the first place?

lobsters/lobsters#1871