Optimizing SQLite for servers

[double-daruma]

Description

I think it would be cool that when using SQLite that the defaults that loco uses work well even for a production deployment. Since many smaller apps with only like 100 users or so can run in production with SQLite. This Blog Post has a good summary of how to use SQLite for server applications: https://kerkour.com/sqlite-for-servers

In particular setting pragmas:

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 1000000000;
PRAGMA foreign_keys = true;
PRAGMA temp_store = memory;

And using BEGIN IMMEDIATE should be possible to implement. The part about using two different connections for read and write could be harder.

Thank you for your consideration.

[Sillyvan]

I agree that we need to change the SQlite defaults in loco. The defaults of sqlite are old and cant be changed by them due to compatibility. Instead of relying on that website i would prefer to just copy the new rails adapter for sqlite because that one has been optimized recently.

https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb

edit: i might be wrong but seaorm seems to generally dont allow pragmas? this is a huge issue imo. like to an extend where sqlite is not a good experience for prod at all.

[Sillyvan]

Im reading a lot of code because i would really like to contribute to this project. But i just really cant figure out where to add them. Im getting the feeling that i would need to implement a bunch of things for the Sqlite options. similar to the additional postgres methods.

Maybe this can help? SeaQL/sea-orm#2347

[Sillyvan]

@double-daruma we merged some optimizations.

BEGIN IMMEDIATE is something i never saw anyone talk about before and it might be more of a thing for SeaORM.
The two connections are also a pain to implement and only every matter with huge loads. Is this enough for now?