Use SQLite instead of Postgres?

[dentarg]

There is a lot of chatter about how great SQLite is.

• https://thmsmlr.com/cheap-infra
• https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/
• https://github.com/benbjohnson/litestream
• https://github.com/oldmoe/litestack

[dentarg]

However:

• https://www.sqlite.org/omitted.html
• https://www.sqlite.org/quirks.html

[dentarg]

I ran into jeremyevans/sequel#2176

[dentarg]

Data can be migrated using sequel

$ sequel --copy-databases postgres://localhost/wikimum sqlite://storage/wiki.db
Databases connections successful
Migrations dumped successfully
Tables created
Begin copying data
Begin copying records for table: schema_info
Finished copying 1 records for table: schema_info
Begin copying records for table: users
Finished copying 1 records for table: users
Begin copying records for table: revisions
Finished copying 0 records for table: revisions
Begin copying records for table: pages
Finished copying 2 records for table: pages
Finished copying data
Begin creating indexes
Finished creating indexes
Begin adding foreign key constraints
Finished adding foreign key constraints
Database copy finished in 0.067332 seconds

[dentarg]

No problem with production

$ sequel --copy-databases postgres://localhost/prod-wikimum sqlite://storage/prod-wiki.db
Databases connections successful
Migrations dumped successfully
Tables created
Begin copying data
Begin copying records for table: schema_info
Finished copying 1 records for table: schema_info
Begin copying records for table: users
Finished copying 145 records for table: users
Begin copying records for table: pages
Finished copying 452 records for table: pages
Begin copying records for table: revisions
Finished copying 4186 records for table: revisions
Finished copying data
Begin creating indexes
Finished creating indexes
Begin adding foreign key constraints
Finished adding foreign key constraints
Database copy finished in 0.422092 seconds

Some stats

$ ls -ahl starkast_wiki_backup_2024-06-15_215012.sql
-rw-r--r--@ 1 dentarg  staff    24M Jun 15 21:51 starkast_wiki_backup_2024-06-15_215012.sql

$ ls -ahl storage/prod-wiki.db
-rw-r--r--  1 dentarg  staff    49M Jun 15 21:53 storage/prod-wiki.db