Cli Sqlite

SQLite — Serverless SQL Database

Repo: https://github.com/sqlite/sqlite

Self-contained, serverless, zero-configuration SQL database engine. The most widely deployed database in the world. Great for local data analysis, embedded apps, prototyping, and file-based data exchange.

When to Activate

Manual triggers:

• "How do I use SQLite?"
• "Query a .db file"
• "Import CSV into a database"
• "Serverless / embedded SQL"

Auto-detect triggers:

• User wants to query or transform structured data without a server
• User wants to import CSV files for SQL-based analysis
• User wants a portable, file-based database for an app
• User wants to use full-text search (FTS5)
• User wants to work with JSON data in SQL

Key CLI Commands (sqlite3)

Opening a Database

sqlite3 mydb.db              # Open (or create) a database file
sqlite3 :memory:             # In-memory database (gone when process exits)
sqlite3                      # Open with no file (temporary in-memory)
sqlite3 mydb.db "SELECT 1"   # Run a single query and exit

Dot-Commands (meta-commands)

.tables                      -- List all tables
.schema                      -- Show CREATE statements for all tables
.schema tablename            -- Show CREATE statement for one table
.mode column                 -- Aligned column output
.mode csv                    -- CSV output
.mode json                   -- JSON output
.mode markdown               -- Markdown table output
.mode box                    -- Box-drawing table output
.headers on                  -- Show column headers
.headers off                 -- Hide column headers
.output results.csv          -- Redirect output to file
.output stdout               -- Reset output to terminal
.import data.csv tablename   -- Import CSV into table
.import --csv data.csv tbl   -- Import with explicit CSV mode
.dump                        -- Dump entire DB as SQL
.dump tablename              -- Dump one table as SQL
.backup backup.db            -- Backup DB to file
.read script.sql             -- Execute a SQL file
.quit / .exit                -- Exit sqlite3
.help                        -- Show all dot-commands

Useful Settings for Analysis

# Add to ~/.sqliterc for persistent settings:
.mode box
.headers on
.timer on        -- Show query execution time
.changes on      -- Show rows affected
.nullvalue NULL  -- Display NULLs explicitly

SQL Patterns

DDL & DML

-- Create table
CREATE TABLE users (
  id    INTEGER PRIMARY KEY AUTOINCREMENT,
  name  TEXT NOT NULL,
  email TEXT UNIQUE,
  ts    TEXT DEFAULT (datetime('now'))
);

-- Insert
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Upsert (INSERT OR REPLACE / ON CONFLICT)
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@new.com')
ON CONFLICT(id) DO UPDATE SET email = excluded.email;

-- Update / Delete
UPDATE users SET name = 'Bob' WHERE id = 2;
DELETE FROM users WHERE email IS NULL;

JOINs

SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 100
ORDER BY o.total DESC;

CTEs (Common Table Expressions)

WITH monthly AS (
  SELECT strftime('%Y-%m', ts) AS month, SUM(total) AS revenue
  FROM orders
  GROUP BY 1
),
ranked AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rn
  FROM monthly
)
SELECT * FROM ranked WHERE rn ', '') FROM docs_fts WHERE docs_fts MATCH 'query';

Advanced Patterns

CSV Import for Data Analysis

# Import CSV (auto-creates table from headers)
sqlite3 analysis.db  1000;

-- Generate series
SELECT value FROM generate_series(1, 100) WHERE value % 7 = 0;

Indexes and Query Planning

CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_ts   ON orders(ts DESC);

-- Inspect query plan
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 5 ORDER BY ts DESC;

Practical Examples

# Quick schema dump of an existing DB:
sqlite3 app.db ".schema"

# Count rows in every table:
sqlite3 app.db "SELECT name, (SELECT COUNT(*) FROM pragma_table_info(name)) cols FROM sqlite_master WHERE type='table'"

# Export a table to CSV:
sqlite3 -csv -header app.db "SELECT * FROM users" > users.csv

# Run a SQL file:
sqlite3 app.db < migrations/001_add_index.sql

# Diff two databases (schema):
diff <(sqlite3 db1.db .schema) <(sqlite3 db2.db .schema)

Chaining with Other Skills

• jq: Export JSON from SQLite with json_object()/json_group_array(), pipe to jq for further transformation; or preprocess JSON with jq then import to SQLite
• duckdb (cli-duckdb): Use DuckDB for heavy analytical queries on Parquet/CSV, export results to SQLite for app consumption; or attach SQLite files in DuckDB with ATTACH 'app.db' AS sqlite (TYPE sqlite)
• fd (cli-fd): Use fd to find all .db files in a directory tree before running batch schema inspections or migrations
• bat (cli-bat): Use bat -l sql to view SQL migration files with syntax highlighting before running them

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: ryankolean
• Source: ryankolean/summit-claude-skills
• License: MIT

Install and usage instructions live in the source repository linked above.