SQLite optimisations in Laravel
The winds of opinion have changed on databases in the last ~6 months, and SQLite is popular again. Rails and Laravel have adopted it as the default DB.
Rails is working towards pre-optimising SQLite DBs by default. I'll show you how to do the same in Laravel.
1. SQLite optimisations
Straight to the point:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA journal_size_limit = 67108864; -- 64 megabytes
PRAGMA mmap_size = 134217728; -- 128 megabytes
PRAGMA cache_size = 1000000000;
PRAGMA foreign_keys = true;
PRAGMA busy_timeout = 5000;
PRAGMA temp_store = memory;
The first pragma can be run in a migration as it is persistent, while the others need to be run for each database connection initialised. In Laravel this can be done in a service provider.
The numbers can be tweaked if you know what you're doing. Read this great article for an in-depth explanation. All you need to know is that these will make your SQLite DB more robust and performant.
2. Multiple SQLite databases
This is the second pattern I see as an "easy win" for scaling: Split out connection-heavy functionality to separate SQLite files. This can be done for anything that uses a database driver. Cache, queues, Pulse, Telescope, etc.
This effectively gives you multiple database connections (solving a SQLite limitation) "for free" and lets your SQLite performance scale to the level of your server hardware (SSD).
In Laravel, you just need to define extra SQLite connections for each file, make sure those .sqlite files exist before you run migrations, and there you go.
I'll leave how to set these connections up as an exercise for you, dear reader. Maybe check the Laravel docs as a starting point...
Putting it all together
Now, you have the database optimisations, and you'd like to do this for multiple SQLite database files in your Laravel app. You can have an initialisation migration for WAL mode, whereas the rest can be initialised in a service provider.
That's it! Obviously it's a little bit more config than just spinning up a default MySQL/Postgres database, but these are also such quick wins that it's hard not to recommend them.