martinholovsky/claude-skills-generator/sqlite

Name: sqlite
Author: martinholovsky

Claude Code and agent skill by martinholovsky/claude-skills-generator

Trust tier 3

community

Source check pending

Checking that the SKILL.md file is still in the repository.

Expert in SQLite embedded database development for Tauri/desktop applications with focus on SQL injection prevention, migrations, FTS search, and secure data handling

Install

npx vskill@latest install martinholovsky/claude-skills-generator/sqlite

SKILL.md

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

TDD First - Write tests before implementation; use in-memory SQLite for fast test execution
Performance Aware - Optimize with WAL mode, prepared statements, batch operations, and proper indexing
Security First - Always use parameterized queries; never concatenate user input
Transaction Safety - Wrap related operations in transactions for atomicity
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

ALWAYS use parameterized queries - Never concatenate user input into SQL strings
Validate all inputs before database operations
Implement proper error handling without exposing database internals
Use transactions for data integrity
Apply principle of least privilege for database access

2.2 Data Integrity Principles

Schema versioning with migration tracking
Foreign key enforcement with PRAGMA foreign_keys = ON
Constraint validation at database level
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 conn = Connection::open(path)?;
 
        // Enable security and performance features
        conn.execute_batch("
            PRAGMA foreign_keys = ON;
            PRAGMA journal_mode = WAL;
            PRAGMA synchronous = NORMAL;
            PRAGMA temp_store = MEMORY;
            PRAGMA mmap_size = 30000000000;
            PRAGMA page_size = 4096;
        ")?;
 
        Ok(Self { conn })
    }
}

4.2 Parameterized Queries (CRITICAL)

rust

// CORRECT: Parameterized query
pub fn get_user_by_id(&self, user_id: i64) -> Result<Option<User>> {
    let mut stmt = self.conn.prepare(
        "SELECT id, name, email FROM users WHERE id = ?1"
    )?;
 
    let user = stmt.query_row([user_id], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
        })
    }).optional()?;
 
    Ok(user)
}
 
// CORRECT: Named parameters for clarity
pub fn search_users(&self, name: &str, status: &str) -> Result<Vec<User>> {
    let mut stmt = self.conn.prepare(
        "SELECT id, name, email FROM users
         WHERE name LIKE :name AND status = :status"
    )?;
 
    let users = stmt.query_map(
        &[(":name", &format!("%{}%", name)), (":status", &status)],
        |row| Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
        })
    )?.collect::<Result<Vec<_>>>()?;
 
    Ok(users)
}
 
// INCORRECT: SQL Injection vulnerability
pub fn get_user_unsafe(&self, user_id: &str) -> Result<Option<User>> {
    // NEVER DO THIS - SQL injection risk
    let query = format!("SELECT * FROM users WHERE id = {}", user_id);
    // ...
}

4.3 Transaction Management

rust

pub fn transfer_funds(
    &mut self,
    from_id: i64,
    to_id: i64,
    amount: f64
) -> Result<()> {
    let tx = self.conn.transaction()?;
 
    // Debit from source
    tx.execute(
        "UPDATE accounts SET balance = balance - ?1 WHERE id = ?2",
        [amount, from_id as f64],
    )?;
 
    // Credit to destination
    tx.execute(
        "UPDATE accounts SET balance = balance + ?1 WHERE id = ?2",
        [amount, to_id as f64],
    )?;
 
    tx.commit()?;
    Ok(())
}

4.4 Full-Text Search (FTS5)

rust

// Create FTS5 virtual table with triggers
pub fn setup_fts(&self) -> Result<()> {
    self.conn.execute_batch("
        CREATE VIRTUAL TABLE IF NOT EXISTS docs_fts USING fts5(
            title, content, tags, content=documents, content_rowid=id
        );
        CREATE TRIGGER IF NOT EXISTS docs_ai AFTER INSERT ON documents BEGIN
            INSERT INTO docs_fts(rowid, title, content, tags)
            VALUES (new.id, new.title, new.content, new.tags);
        END;
    ")?;
    Ok(())
}
 
// Search with highlighting
pub fn search_documents(&self, query: &str) -> Result<Vec<Document>> {
    let mut stmt = self.conn.prepare(
        "SELECT d.*, highlight(docs_fts, 1, '<mark>', '</mark>') as snippet
         FROM documents d JOIN docs_fts ON d.id = docs_fts.rowid
         WHERE docs_fts MATCH ?1 ORDER BY rank"
    )?;
    stmt.query_map([query], |row| Ok(Document { /* ... */ }))?.collect()
}

5. Security Standards

5.1 Key Vulnerabilities

Mitigation: Update to SQLite 3.44.0+ and always use parameterized queries.

5.2 OWASP Mapping

OWASP Category	Risk	Key Controls
A03 - Injection	Critical	Parameterized queries, input validation
A04 - Insecure Design	Medium	Schema constraints, foreign keys
A05 - Misconfiguration	Medium	Secure PRAGMAs, file permissions (600)

5.3 SQL Injection Prevention

Critical Rules (see references/security-examples.md):

NEVER use string formatting for SQL queries
ALWAYS use ? positional or :name named parameters
Whitelist column/table names for dynamic queries

rust

// Dynamic column selection - SAFE approach
pub fn get_user_fields(&self, user_id: i64, fields: &[&str]) -> Result<HashMap<String, String>> {
    const ALLOWED: &[&str] = &["id", "name", "email", "created_at"];
    let safe_fields: Vec<&str> = fields.iter()
        .filter(|f| ALLOWED.contains(f)).copied().collect();
    if safe_fields.is_empty() { return Err(rusqlite::Error::InvalidQuery); }
    let query = format!("SELECT {} FROM users WHERE id = ?1", safe_fields.join(", "));
    let mut stmt = self.conn.prepare(&query)?;
    // ...
}

6. Testing Standards

6.1 Rust Testing Pattern

rust

#[cfg(test)]
mod tests {
    use super::*;
    use rusqlite::Connection;
 
    fn setup_test_db() -> Database {
        let conn = Connection::open_in_memory().unwrap();
        let db = Database { conn };
        db.run_migrations().unwrap();
        db
    }
 
    #[test]
    fn test_sql_injection_prevented() {
        let db = setup_test_db();
        let result = db.search_users("'; DROP TABLE users; --", "active");
        assert!(result.is_ok());
        assert!(db.get_user_by_id(1).is_ok()); // Table still exists
    }
}

7. Implementation Workflow (TDD)

Step 1: Write Failing Test First

python

# tests/test_user_repository.py
import pytest
import sqlite3
@pytest.fixture
def db():
    """In-memory SQLite for fast testing."""
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    yield conn
    conn.close()
class TestUserRepository:
    def test_create_user_returns_id(self, db):
        repo = UserRepository(db)
        repo.initialize_schema()
        user_id = repo.create_user("test@example.com", "Test User")
        assert user_id > 0
    def test_sql_injection_prevented(self, db):
        repo = UserRepository(db)
        repo.initialize_schema()
        malicious = "'; DROP TABLE users; --"
        user_id = repo.create_user(malicious, "Hacker")
        assert repo.get_by_id(user_id)["email"] == malicious

Step 2: Implement Minimum Code to Pass

python

# app/repositories/user.py
class UserRepository:
    def __init__(self, conn):
        self.conn = conn
    def initialize_schema(self):
        self.conn.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                email TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL
            )""")
        self.conn.commit()
    def create_user(self, email: str, name: str) -> int:
        cursor = self.conn.execute(
            "INSERT INTO users (email, name) VALUES (?, ?)", (email, name))
        self.conn.commit()
        return cursor.lastrowid
    def get_by_id(self, user_id: int):
        return self.conn.execute(
            "SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()

Step 3: Run Verification

bash

pytest tests/test_*_repository.py -v --cov=app/repositories

7.1 Performance Patterns

Pattern 1: WAL Mode

python

# Good: Enable WAL for concurrent read/write
conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA synchronous = NORMAL")
conn.execute("PRAGMA cache_size = -64000")  # 64MB
# Bad: Default DELETE mode blocks reads during writes

Pattern 2: Batch Inserts

python

# Good: Single transaction for batch
conn.executemany("INSERT INTO items (name) VALUES (?)", records)
conn.commit()
# Bad: Commit per row (100x slower)
for r in records:
    conn.execute("INSERT INTO items (name) VALUES (?)", (r,))
    conn.commit()

Pattern 3: Connection Pooling

python

# Good: Reuse connections
from queue import Queue
class ConnectionPool:
    def __init__(self, db_path, size=5):
        self.pool = Queue(size)
        for _ in range(size):
            conn = sqlite3.connect(db_path, check_same_thread=False)
            conn.execute("PRAGMA journal_mode = WAL")
            self.pool.put(conn)
# Bad: New connection per query
conn = sqlite3.connect(db_path)  # Expensive!

Pattern 4: Index Optimization

python

# Good: Covering and partial indexes
conn.executescript("""
    CREATE INDEX idx_users_email ON users(email, name);
    CREATE INDEX idx_active ON items(created_at) WHERE status='active';
    ANALYZE;
""")
# Bad: Full table scan on unindexed columns

Pattern 5: VACUUM Scheduling

This is the start of the file. Read the full SKILL.md on GitHub.

Security

Security scanPassed, 95 of 100Pattern checks on the skill's files.
Model reviewPassed

See the full security report

Quality evaluation

Task evaluationNot runNo evaluation yet. Run one in Skill Studio.

See evaluation results

Source

SKILL.md in the repositoryCheckingskills/sqlite/SKILL.md

Popularity

Installs: 2
Unique users: 1
Repository stars: 38
Repository forks: 4
7-day trend score: 22

Installs come from the vskill CLI. Stars and forks are for the whole GitHub repository, not this skill alone. Refreshed 30 Apr 2026, 6 months ago.

Works with

Any agent vskill installs to: 56 targets in all. These 39 have a page on this site.

Claude Code
Cursor
GitHub Copilot
Windsurf
Codex
Gemini CLI
Cline
Amp
Roo Code
Goose
Continue
Kilo Code
Junie
Trae
OpenHands
Qwen Code
AI Agent
Tabnine Chat
Sourcegraph Cody
Amazon Q
Firebase Genkit
Sweep
Devin
Phind
Mentat
Claude Desktop
Aider
GPT Pilot
Plandex
bolt.new
v0
Replit Agent
Anse
TypingMind
BetterChatGPT
LibreChat
Warp AI
Pieces
Fig AI

Embed badge

[![vskill VERIFIED](https://verified-skill.com/api/v1/skills/martinholovsky/claude-skills-generator/sqlite/badge)](https://verified-skill.com/skills/martinholovsky/claude-skills-generator/sqlite)

Something wrong with this skill or its page? Tell us.