# Cli Sqlite

> >

- **Type:** Skill
- **Install:** `agentstack add skill-ryankolean-summit-claude-skills-cli-sqlite`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [ryankolean](https://agentstack.voostack.com/s/ryankolean)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [ryankolean](https://github.com/ryankolean)
- **Source:** https://github.com/ryankolean/summit-claude-skills/tree/main/skills/cli-sqlite

## Install

```sh
agentstack add skill-ryankolean-summit-claude-skills-cli-sqlite
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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
```bash
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)
```sql
.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
```bash
# 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
```sql
-- 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
```sql
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)
```sql
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
```bash
# 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
```sql
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

```bash
# 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](https://github.com/ryankolean)
- **Source:** [ryankolean/summit-claude-skills](https://github.com/ryankolean/summit-claude-skills)
- **License:** MIT

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** yes
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-ryankolean-summit-claude-skills-cli-sqlite
- Seller: https://agentstack.voostack.com/s/ryankolean
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.