esplora/spire

Easy Tools for Production Environment of Laravel SQLite Database

0.0.3 2024-10-24 15:10 UTC

Requires

• php: ^8.0
• ext-sqlite3: *
• laravel/framework: ^9.2|^10.0|^11.0

Requires (Dev)

• doctrine/dbal: ^3.1
• laravel/pint: ^1.17
• mockery/mockery: ^1.4.2
• orchestra/testbench-core: ^7.0|^8.0|^9.0
• phpunit/phpunit: ^9.0|^10.0|^11.0

MIT

• Alexandr Chernyaev

README

Spire is a Laravel package designed to provide easy tools specifically tailored for optimizing SQLite databases in production environments.

Installation

To install Spire into your Laravel project, you can use Composer:

composer require esplora/spire

Usage

Once installed, you can utilize Spire's commands via the Artisan CLI.

Commands

Enable WAL Journal

To enable WAL journal on SQLite databases for performance optimization:

php artisan sqlite:wal-enable

Enabling WAL journaling can enhance database concurrency, reduce contention, and improve overall database performance, especially in high-traffic production environments.

Optimize Database

To optimize the SQLite database by running PRAGMA optimize:

php artisan sqlite:optimize

This combined optimization process ensures comprehensive optimization of the database file, resulting in improved performance and efficient storage utilization.

Run VACUUM

To optimize the SQLite database by running VACUUM:

php artisan sqlite:vacuum

VACUUM rebuilds the database file, reclaims unused disk space, and can help to improve database performance by optimizing storage utilization.

Schedule

For efficient database maintenance, it's highly recommended to schedule some of these commands as cron jobs. For instance, you can optimize the SQLite database periodically by adding a cron job to your scheduler:

// Optimize SQLite database every minute,
// see https://www.sqlite.org/pragma.html#pragma_optimize
$schedule->command('sqlite:optimize')->everyMinute();