feat: WAL Mode Support

[mrdevrobot]

Feature Request

Description

Currently, capacitor-fast-sql opens SQLite databases in the default journaling mode (Rollback Journal). In high-performance mobile applications, especially those with concurrent read/write operations or background synchronization, the default mode can lead to "Database is locked" errors and UI stutters.

I am requesting the ability to enable WAL (Write-Ahead Logging) mode and configure other performance-related SQLite PRAGMAs to ensure smoother database operations.

Platform(s)

• iOS

• Android

Preferred Solution

I would prefer an optional configuration object in the open method (or a specific initialization method) to toggle WAL mode and other performance optimizations.

Example:

await FastSQL.open({
  name: "my_db",
  walMode: true, // Executes 'PRAGMA journal_mode = WAL;'
  performancePresets: true // Enables recommended settings (Synchronous NORMAL, etc.)
});

Alternatives

• Manual Execution: Users could manually call db.execute({ statements: ["PRAGMA journal_mode = WAL;"] }) after opening, but this is error-prone as WAL mode should ideally be set as early as possible and handled by the driver to manage the .db-wal and .db-shm files correctly.

• Defaulting to WAL: Many modern mobile database wrappers (like Room or Realm) use WAL by default due to its superior performance on flash storage.

Additional Context

Beyond just WAL mode, I suggest implementing or allowing configuration for the following improvements:

1. PRAGMA synchronous = NORMAL;: When using WAL, "NORMAL" is often the best balance. it is significantly faster than "FULL" and still safe for most mobile use cases.

2. PRAGMA busy_timeout = 5000;: To prevent immediate crashes during concurrent writes, a default busy timeout (e.g., 5 seconds) should be set so SQLite waits for a lock instead of throwing an error immediately.

3. PRAGMA cache_size = -2000;: Explicitly defining the page cache size (the negative value represents ~2MB) ensures consistent memory usage across different device hardwares.

4. PRAGMA foreign_keys = ON;: Often requested alongside performance updates to ensure data integrity is enforced by default.

Benefits of WAL in Capacitor:

• Improved Concurrency: Readers do not block writers and vice versa.

• Better Performance: Fewer disk I/O operations for most transactions.

• UI Responsiveness: Less chance of blocking the main thread during heavy database operations.

Reference: SQLite WAL Documentation