Sqlite Database Expert
Implement and harden SQLite in Tauri or desktop apps with migrations, FTS search, and parameterized queries that resist SQL injection.
Overview
SQLite Database Expert is an agent skill for the Build phase that guides secure embedded SQLite—migrations, FTS5, WAL, and parameterized SQL—for Tauri and desktop apps.
Install
npx skills add https://github.com/martinholovsky/claude-skills-generator --skill sqlite-database-expertWhat is this skill?
- Mandatory reference reads for migrations, FTS5, CTEs, WAL, and pooling via advanced-patterns.md
- Security-examples.md gate before any user-input SQL—parameterized queries and sensitive data handling
- Tauri and desktop-focused embedded SQLite expertise
- Full-Text Search (FTS5) setup and complex query patterns
- Risk-aware framing for local user data and migration safety
- Mandatory reference protocol splits advanced-patterns.md vs security-examples.md by trigger conditions
Adoption & trust: 1.8k installs on skills.sh; 38 GitHub stars; 3/3 security scanners passed (skills.sh audits).
What problem does it solve?
You are adding local SQLite to a desktop app but lack a safe migration strategy, FTS setup, or disciplined parameterized queries for user-supplied input.
Who is it for?
Indie builders on Tauri/Electron-style stacks who own the full data layer and need FTS, migrations, and security patterns in one place.
Skip if: Cloud-only SaaS teams standardizing on managed Postgres with no embedded SQLite requirement.
When should I use this skill?
Implementing database migrations, FTS5, complex queries, WAL/pooling, or any SQL involving user input in SQLite desktop/Tauri apps.
What do I get? / Deliverables
You implement database operations with reference-backed migrations, FTS where needed, and SQL injection–resistant patterns suitable for local user data.
- Migration-safe schema changes and query implementations
- Parameterized SQL and validation patterns for user-facing data access
- FTS5 or advanced query designs when search is required
Recommended Skills
Journey fit
Embedded database design and secure query patterns belong in build when you are shipping local persistence—not in launch or grow analytics layers. Backend subphase covers schema, migrations, WAL, pooling, and FTS—the data layer adjacent to your app logic.
How it compares
A depth skill for embedded SQLite on device—not a generic ORM picker or hosted database provisioning guide.
Common Questions / FAQ
Who is sqlite-database-expert for?
Developers building desktop or Tauri applications who implement SQLite themselves and need expert-level migration, search, and security guidance.
When should I use sqlite-database-expert?
During build/backend work when designing schema migrations, enabling FTS5, tuning WAL or pooling, or writing any SQL that accepts user input.
Is sqlite-database-expert safe to install?
The skill emphasizes secure patterns but database work can affect production data; review the Security Audits panel on this page and never skip parameterized-query guidance for user input.
SKILL.md
READMESKILL.md - Sqlite Database Expert
# SQLite Database Expert ## 0. Mandatory Reading Protocol **CRITICAL**: Before implementing ANY database operation, you MUST read the relevant reference files: ### Trigger Conditions for Reference Files **Read `references/advanced-patterns.md` WHEN**: - Implementing database migrations - Setting up Full-Text Search (FTS5) - Designing complex queries with CTEs or window functions - Implementing connection pooling or WAL mode - Performance optimization tasks **Read `references/security-examples.md` WHEN**: - Writing ANY SQL query with user input - Implementing parameterized queries - Setting up database encryption considerations - Handling sensitive data storage - Implementing input validation for database operations --- ## 1. Overview **Risk Level: MEDIUM** **Justification**: SQLite databases in desktop applications handle user data locally, present SQL injection risks if queries aren't properly parameterized, and require careful migration management to prevent data loss. You are an expert in SQLite embedded database development, specializing in: - **Secure SQL patterns** with parameterized queries to prevent SQL injection - **Database migrations** with version control and rollback capabilities - **Full-Text Search (FTS5)** for efficient text searching - **Performance optimization** including indexing, WAL mode, and connection management - **Rust/Tauri integration** using rusqlite and sea-query ### Core Principles 1. **TDD First** - Write tests before implementation; use in-memory SQLite for fast test execution 2. **Performance Aware** - Optimize with WAL mode, prepared statements, batch operations, and proper indexing 3. **Security First** - Always use parameterized queries; never concatenate user input 4. **Transaction Safety** - Wrap related operations in transactions for atomicity 5. **Migration Discipline** - Version all schema changes with rollback capability ### Primary Use Cases - Local data persistence for desktop applications - Offline-first application data storage - Full-text search implementation - Configuration and settings storage - Cache and temporary data management --- ## 2. Core Responsibilities ### 2.1 Security-First Database Operations 1. **ALWAYS use parameterized queries** - Never concatenate user input into SQL strings 2. **Validate all inputs** before database operations 3. **Implement proper error handling** without exposing database internals 4. **Use transactions** for data integrity 5. **Apply principle of least privilege** for database access ### 2.2 Data Integrity Principles 1. **Schema versioning** with migration tracking 2. **Foreign key enforcement** with `PRAGMA foreign_keys = ON` 3. **Constraint validation** at database level 4. **Backup strategies** before destructive operations --- ## 3. Technical Foundation ### 3.1 Version Recommendations | Component | Recommended | Minimum | Notes | |-----------|-------------|---------|-------| | SQLite | 3.45+ | 3.35 | FTS5, JSON functions | | rusqlite | 0.31+ | 0.29 | Bundled SQLite support | | sea-query | 0.30+ | 0.28 | Query builder | | r2d2 | 0.8+ | 0.8 | Connection pooling | ### 3.2 Required Dependencies (Cargo.toml) ```toml [dependencies] rusqlite = { version = "0.31", features = ["bundled", "backup", "functions"] } sea-query = "0.30" sea-query-rusqlite = "0.5" r2d2 = "0.8" r2d2_sqlite = "0.24" ``` --- ## 4. Implementation Patterns ### 4.1 Database Initialization ```rust use rusqlite::{Connection, Result}; use std::path::Path; pub struct Database { conn: Connection, } impl Database { pub fn new(path: &Path) -> Result<Self> { let c