SQLite Database Expert

Use parameterized queries with positional (?1) or named (:name) parameters instead of string formatting. For dynamic column or table names, validate against a whitelist of allowed values before interpolating them into the query string.

Create a schema_migrations tracking table and define migrations with version numbers, up and down SQL statements. Run pending migrations inside transactions and record each applied version, enabling rollback by executing down statements in reverse order.

Yes, SQLite includes FTS5 for full-text search via virtual tables. Create an FTS5 table with content sync triggers, then query with MATCH operators supporting boolean logic, prefix matching, phrase search, and BM25 relevance ranking.

rusqlite provides direct database access with bundled SQLite, while sea-query adds a type-safe query builder on top. Use rusqlite for straightforward queries and sea-query when building dynamic queries programmatically with compile-time checks.

The default DELETE journal mode blocks readers during writes. Enable WAL mode with PRAGMA journal_mode = WAL, set synchronous to NORMAL, and use connection pooling so concurrent reads proceed while a write transaction is active.

SQLite is designed for embedded, single-machine storage and does not suit high-concurrency server workloads with many simultaneous writers. For multi-client network access or distributed systems, use a client-server database instead.