# Using Sqlite > Use when working with SQLite databases in DataPeeker analysis sessions - querying data, importing CSVs, exploring schemas, formatting output, or optimizing performance. Provides task-oriented guidance for effective SQLite CLI usage in data analysis workflows. - Skill: `tomevault-io/using-sqlite` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add tomevault-io/using-sqlite` - Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/using-sqlite/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Productivity - Author: tomevault-io (https://skillmd.com/u/tomevault-io) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/tomevault-io/using-sqlite --- # Using SQLite ## Overview SQLite is the primary database for DataPeeker analysis sessions. This skill provides task-oriented guidance for common SQLite operations during data analysis. **Core principle:** Explore schema first, format output for readability, write efficient queries, verify all operations. ## When to Use Use this skill when you need to: - Explore an unfamiliar database schema - Query data for analysis or hypothesis testing - Import CSV files into SQLite tables - Format query output for readability - Diagnose slow queries or optimize performance - Understand which CLI invocation pattern to use **When NOT to use:** - Database-agnostic data profiling (see `understanding-data` skill for patterns that work across all SQL databases) - Complex data cleaning logic (delegate to `cleaning-data` skill + sub-agents) - Statistical analysis (use Python/pandas for advanced statistics) - Large-scale transformations (use Python sqlite3 module) ## DataPeeker Conventions ``` Database Path: data/analytics.db (relative from project root) Table Naming: raw_* for imported data, clean_* for cleaned data Single Database: All tables in one file per analysis session ``` **Example workflow:** ``` CSV file → raw_sales → clean_sales → Analysis queries ``` ## Quick Reference | Task | Guidance File | |------|---------------| | Understand what tables/columns exist | @./exploring-schema.md | | Make query results readable | @./formatting-output.md | | Write analytical queries | @./writing-queries.md | | Load CSV files | @./importing-data.md | | Fix slow queries | @./optimizing-performance.md | | Choose CLI invocation method | @./invoking-cli.md | ## Task-Oriented Guidance ### Exploring Schema **Before writing queries, understand the database structure.** See @./exploring-schema.md for: - Listing all tables (.tables) - Viewing table structure (.schema, PRAGMA table_info) - Understanding column types and constraints - Checking for indexes **When:** Starting analysis, unfamiliar database, before writing joins --- ### Formatting Output **Make query results readable for analysis.** See @./formatting-output.md for: - Output modes (column, csv, json, markdown) - Showing/hiding headers (.headers on/off) - Setting column widths for readability - Redirecting output to files **When:** Query results hard to read, need specific format for export, preparing reports --- ### Writing Queries **SQLite-specific query patterns and conventions.** See @./writing-queries.md for: - SQLite idioms used in DataPeeker (COUNT(*) - COUNT(col) for NULLs) - Date handling with STRFTIME - DataPeeker percentage calculation conventions - Common verification queries **See also:** `writing-queries` and `understanding-data` skills for database-agnostic SQL patterns and data profiling approaches. This guidance focuses on SQLite-specific syntax, CLI usage, and optimizations. **When:** Need SQLite-specific syntax, DataPeeker query conventions, date formatting with STRFTIME --- ### Importing Data **Load CSV files and verify import success.** See @./importing-data.md for: - Using .import command - Verification queries (row counts, sample data) - When to use CLI vs Python sqlite3 - Transaction handling **When:** Loading new data, Phase 4 of importing-data skill, verifying data loaded correctly --- ### Optimizing Performance **Diagnose and fix slow queries.** See @./optimizing-performance.md for: - EXPLAIN QUERY PLAN analysis - Creating indexes for common queries - Using transactions for bulk operations - PRAGMA optimization **When:** Query takes >1 second, loading large datasets, repeated similar queries --- ### Invoking CLI **Choose the right method to run sqlite3 commands.** See @./invoking-cli.md for: - Interactive mode (for exploration) - Heredoc pattern (for multi-command scripts) - File redirect (for SQL files) - One-liner mode (for quick checks) **When:** Starting any SQLite operation, unsure which invocation to use ## Common Mistakes | Mistake | Fix | |---------|-----| | Writing queries without exploring schema | Always run .tables and .schema first | | Poor output formatting (hard to read results) | Use .mode column and .headers on for readability | | Ignoring NULL values in calculations | Use COUNT(*) - COUNT(col) for NULL counting | | Integer division losing decimals | Use 100.0 (not 100) for percentage calculations | | Slow queries without diagnosis | Run EXPLAIN QUERY PLAN before optimizing | | Assuming import succeeded | Always verify with SELECT COUNT(*) after import | | Using wrong CLI invocation pattern | Interactive for exploration, heredoc for scripts | ## Verification Before Proceeding **After any import operation:** ```sql -- 1. Verify row count matches expectation SELECT COUNT(*) FROM raw_table; -- 2. Check sample data looks correct SELECT * FROM raw_table LIMIT 5; -- 3. Verify no unexpected NULLs SELECT COUNT(*) - COUNT(critical_column) FROM raw_table; ``` **After writing a complex query:** ```sql -- 1. Check query plan EXPLAIN QUERY PLAN SELECT ...; -- 2. Time the query .timer on SELECT ...; ``` ## Real-World Impact **Systematic approach prevents:** - Writing queries against wrong tables (explore schema first) - Unreadable output (format before analyzing) - Slow queries (diagnose with EXPLAIN QUERY PLAN) - Silent data loss (verify imports) - Incorrect percentages (use float division) **Following this skill:** - Reduces query debugging time by 50%+ - Catches import issues immediately - Produces readable analysis artifacts - Ensures reproducible workflows --- > Converted and distributed by [TomeVault](https://tomevault.io/claim/tilmon-engineering) — claim your Tome and manage your conversions.