congiuluc/database-sqlite

SQLite Database Skill

When to Use

• Setting up SQLite for local development or embedded scenarios
• Configuring connection strings and pragmas for optimal performance
• Designing schemas with proper indexing
• Running EF Core or Dapper against SQLite
• Managing migrations and schema changes
• Troubleshooting WAL mode, concurrency, or locking issues

Official Documentation

• SQLite Official Docs
• Microsoft.Data.Sqlite
• EF Core SQLite Provider
• SQLite WAL Mode
• SQLite Query Planner
• SQLite PRAGMA Statements

Procedure

1. Configure connection string and pragmas — see connection setup
2. Design schema with proper types and constraints — see schema design
3. Add indexes for query performance — see performance tuning
4. Run migrations — see migrations
5. Review complete sample

Key Principles

• Enable WAL mode for concurrent read/write performance
• Use parameterized queries exclusively — SQLite is equally vulnerable to injection
• SQLite is single-writer — design for low-concurrency write scenarios
• Use STRICT tables (SQLite 3.37+) for type enforcement
• Always close connections promptly — connection pooling is managed by the provider
• Use DateTimeOffset or ISO 8601 strings for dates — SQLite has no native datetime type
• Test with the same SQLite version used in production