Why SQLite?

• Simplicity: No separate server setup. SQLite runs in-process.
• Portability: Single file database, easy to backup and move.
• Zero-configuration: Works out of the box.
• Reduced complexity: Eliminates network-related issues and configuration.
• Lower resource usage: Ideal for development and testing environments.
• Easier onboarding: New developers can start immediately without MySQL setup.

Async vs Sync?

This project already uses async for MySQL, suggesting a preference for non-blocking I/O. However, SQLite is fundamentally different:

• In-process database: No network latency, reducing async benefits.
• File I/O: Still benefits from async, but less dramatically than network ops.

Given this setup:

• Multiple actors (world, player): Suggests concurrent operations.
• Frequent reads, occasional writes: Read-heavy workloads benefit less from async.
• Periodic world state sync: Could benefit from async to prevent blocking.

Recommendation:

• Stick with regular rusqlite, but use a thread pool:
• Use r2d2 for connection pooling.
• Employ tokio::task::spawn_blocking for DB operations:

let pool = Arc::new(pool);
let result = tokio::task::spawn_blocking(move || {
    let conn = pool.get()?;
    conn.query_row("SELECT ...", [], |row| {
        // Process row
    })
}).await??;

This approach:

• Keeps SQLite operations simple and synchronous.
• Prevents blocking the async runtime.
• Leverages Tokio's work-stealing thread pool for efficient execution.

It's a good balance between simplicity and performance, avoiding the complexity of fully async SQLite while still benefiting from the existing async architecture.

Tasks

• Add rusqlite, r2d2, r2d2_sqlite dependencies
• Run mysql and sqlite side by side
• Add a new attribute sqlite_pool (alongside pool)
• Try and get the create account, and login features to work