sqlite_skill

Overview

This skill is an expert guide for building secure, high-performance SQLite databases in Tauri and other desktop applications. It focuses on preventing SQL injection, managing migrations safely, enabling FTS5 full-text search, and applying best practices for transactions, indexing, and connection handling. The content is practical and centered on TDD-first workflows and secure defaults.

How this skill works

The skill inspects database operations and prescribes patterns: always use parameterized queries, enable PRAGMA settings for integrity and performance, and wrap multi-step changes in transactions. It provides concrete initialization code, migration discipline, FTS5 setup with triggers, and testing patterns using in-memory databases to validate safety and performance. It also enforces reading security and advanced reference notes before implementation.

When to use it

• Building local persistence for desktop or offline-first apps
• Implementing or migrating schemas with rollback capability
• Adding full-text search (FTS5) for fast text queries
• Hardening database access against SQL injection and data leaks
• Optimizing read/write throughput with WAL and connection pooling

Best practices

• Write tests first using in-memory SQLite and validate SQL injection prevention
• Always use positional (?) or named (:name) parameters; never concatenate user input
• Enable PRAGMA foreign_keys = ON and journal_mode = WAL at connection time
• Wrap related operations in transactions and provide rollback paths for migrations
• Whitelist dynamic column/table names and validate all inputs before DB use
• Use batch operations and prepared statements for large inserts; schedule VACUUM/OPTIMIZE during idle windows

Example use cases

• User settings and configuration storage for a Tauri desktop app with secure access
• Offline note-taking app using FTS5 for full-text search and highlighted snippets
• A local cache layer with WAL mode and connection pool for concurrent reads/writes
• Migration scripts that version schema changes, support rollback, and run in CI tests
• Repository pattern tests that assert SQL injection does not drop or corrupt tables