SQLite / libSQL / Turso Database Expert

Reference Files

Read these when the task matches:

| File | When to read | |------|-------------| | references/typescript-patterns.md | Working with TypeScript, libSQL client, Turso SDK, edge deployments (Cloudflare/Vercel/Bun), Python TDD, or performance optimization |

Also read references/advanced-patterns.md when implementing migrations, FTS5, CTEs, connection pooling, or WAL mode. Read references/security-examples.md when writing any SQL with user input.

---

Overview

| Database | Best For | Key Features | | ---------- | --------------------------------- | ---------------------------------------- | | SQLite | Desktop apps, mobile, embedded | Single file, zero config, bundled | | libSQL | Self-hosted web apps, local-first | Server mode, HTTP API, replication | | Turso | Global web apps, edge computing | Managed, multi-region, embedded replicas |

Core Principles

1. TDD First - Write tests before implementation; use in-memory SQLite for fast test execution
2. Performance Aware - WAL mode, prepared statements, batch operations, proper indexing
3. Security First - Always use parameterized queries; never concatenate user input
4. Transaction Safety - Wrap related operations in transactions
5. Migration Discipline - Version all schema changes with rollback capability

---

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)?;
        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 })
    }
}

Parameterized Queries (CRITICAL)

// 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
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 - NEVER DO THIS
// let query = format!("SELECT * FROM users WHERE id = {}", user_id);

Transaction Management

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

Full-Text Search (FTS5)

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(())
}

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()
}

Security Standards

| 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) |

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)?;
    // ...
}

Testing (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
    }
}

Common Mistakes

| Mistake | Wrong | Correct | | --------------- | ---------------------------------------- | ------------------------------------ | | SQL Injection | format!("...WHERE name = '{}'", input) | "...WHERE name = ?1" with params | | No Transaction | Separate execute calls | Wrap in transaction() + commit() | | No Foreign Keys | Default connection | PRAGMA foreign_keys = ON | | LIKE for Search | LIKE '%term%' | FTS5 MATCH 'term' |

Pre-Implementation Checklist

• [ ] Tests written first (in-memory SQLite)
• [ ] Schema designed with constraints and indexes
• [ ] All user inputs identified and parameterized
• [ ] Transactions wrap related operations
• [ ] Foreign keys enabled, WAL mode configured
• [ ] Batch operations used for multiple inserts
• [ ] Error handling secure (no SQL details in user-facing errors)
• [ ] Migrations tested with rollback
• [ ] VACUUM scheduled for maintenance