SQLite Skill

Use SQLite for local data storage, CSV processing, and data staging. No credentials needed — databases are just files.

Quick Start

# Create a database and query it
sqlite3 data.db "CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT, body TEXT, created_at TEXT DEFAULT (datetime('now')))"
sqlite3 data.db "INSERT INTO notes (title, body) VALUES ('First Note', 'Hello world')"
sqlite3 data.db "SELECT * FROM notes"

Prerequisites

sqlite3 is pre-installed. No credentials or setup needed.

sqlite3 --version

Core Recipes

Create a Database

sqlite3 data.db "CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  created_at TEXT DEFAULT (datetime('now'))
)"

Query Data

sqlite3 data.db "SELECT * FROM users"

Insert Data

sqlite3 data.db "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')"

List Tables

sqlite3 data.db ".tables"

Show Schema

sqlite3 data.db ".schema"

Count Rows

sqlite3 data.db "SELECT count(*) FROM users"

CSV Import & Export

Import CSV to New Table

sqlite3 data.db <<'EOF'
.mode csv
.import data.csv users
EOF

Export Table to CSV

sqlite3 data.db <<'EOF'
.headers on
.mode csv
.output users.csv
SELECT * FROM users;
.output stdout
EOF

Export Query Results to CSV

sqlite3 -header -csv data.db "SELECT id, name, email FROM users WHERE created_at > '2024-01-01'" > recent_users.csv

JSON Operations

Export as JSON

sqlite3 data.db -json "SELECT * FROM users LIMIT 100" > users.json

Query JSON columns (SQLite 3.38+)

sqlite3 data.db "SELECT json_extract(metadata, '$.name') AS name FROM records"

Formatting Options

Column Mode (pretty tables)

sqlite3 -column -header data.db "SELECT * FROM users LIMIT 10"

JSON Output

sqlite3 -json data.db "SELECT * FROM users"

Data Staging Patterns

Use SQLite as a local staging database to process data from other sources.

Stage API Results

sqlite3 staging.db "CREATE TABLE IF NOT EXISTS api_results (id TEXT, data TEXT, fetched_at TEXT DEFAULT (datetime('now')))"

RESULT=$(curl -s https://api.example.com/data)
sqlite3 staging.db "INSERT INTO api_results (id, data) VALUES ('batch-1', '$(echo $RESULT | sed "s/'/''/g")')"

Process Large CSVs

sqlite3 :memory: <<'EOF'
.mode csv
.import large_file.csv data
.headers on
.mode csv
.output filtered.csv
SELECT * FROM data WHERE status = 'active' AND amount > 100;
.output stdout
EOF

In-Memory Processing

sqlite3 :memory: "SELECT 1+1 AS result"

Advanced Recipes

Attach Multiple Databases

sqlite3 main.db <<'EOF'
ATTACH DATABASE 'archive.db' AS archive;
SELECT m.*, a.notes FROM main.users m LEFT JOIN archive.user_notes a ON m.id = a.user_id;
EOF

Full-Text Search

sqlite3 data.db <<'EOF'
CREATE VIRTUAL TABLE IF NOT EXISTS docs_fts USING fts5(title, body);
INSERT INTO docs_fts (title, body) VALUES ('README', 'This is the readme content');
SELECT * FROM docs_fts WHERE docs_fts MATCH 'readme';
EOF

Error Handling

Error	Cause	Solution
no such table	Table doesn't exist	Run .tables to see available tables
database is locked	Another process has a lock	Wait or check for other sqlite3 processes
UNIQUE constraint failed	Duplicate value	Use INSERT OR IGNORE or INSERT OR REPLACE
no such column	Wrong column name	Run .schema tablename to check columns
attempt to write a readonly database	File permissions	Check file ownership and permissions

Tips

• Use :memory: for throwaway data processing — no cleanup needed
• Use sqlite3 -json for easy integration with other tools
• Import CSVs first — SQLite is great for filtering/joining CSV data
• Use as staging — pull data from APIs/databases into SQLite, process locally, export results
• WAL mode — use PRAGMA journal_mode=WAL for better concurrent read performance
• File location — databases are just files in the Space, easily shareable and portable
• Backup — just copy the .db file