sqlite (engine specialist)

The SQLite-specific layer. Cross-engine conventions live in database-conventions - load that hub first and do not restate it. The EF Core side lives in dotnet-data-access. This is only what changes because the engine is SQLite.

When it fits

• Good: embedded / desktop / mobile app storage, single-node edge, a local cache, and test databases. It is a file, not a server.
• Bad: high-write-concurrency multi-client web. SQLite allows one writer at a time for the whole database - reach for postgres there.

Concurrency

• Enable WAL: PRAGMA journal_mode=WAL - readers no longer block the single writer, which is the big throughput win. WAL persists on the file.
• PRAGMA busy_timeout=5000 (ms) so a contended writer waits instead of failing instantly with SQLITE_BUSY.
• PRAGMA synchronous=NORMAL with WAL is the usual durability/speed balance (FULL is safest, slower).

PRAGMAs and typing