SQLite3 Expert Cheat Sheet

Basics

CLI.metaSQL

$ sqlite3 my.db
sqlite> .help                -- show meta-commands
sqlite> .quit

Common meta‑commands (.tables, .schema, headers, modes)

sqlite> .tables               -- list tables
sqlite> .schema open_mics      -- show CREATE TABLE
sqlite> .headers on            -- include column names
sqlite> .mode column           -- pretty tabular
sqlite> .mode csv              -- csv output
sqlite> .separator \t          -- switch delimiter
sqlite> .output result.csv     -- send output to file
sqlite> .output stdout         -- back to screen

Inspect the Database

sqlite> PRAGMA database_list;     -- attached dbs & paths
sqlite> .tables                     -- tables & views
sqlite> PRAGMA table_info(open_mics); -- columns
sqlite> PRAGMA foreign_key_list(t);  -- fkeys on table t

Tip: If a table seems “missing,” verify you’re opening the correct file using PRAGMA database_list; and check the path.

Querying Essentials

SELECT state, city, venue, address, day, time, recurring, description
FROM open_mics;

Filters, Sorting, Limits

SELECT *
FROM open_mics
WHERE state='CA' AND day IN ('sunday','monday')
ORDER BY city, time
LIMIT 50;

Computed Columns & CASE

SELECT venue,
       CASE WHEN recurring IN (1,'1','true','TRUE') THEN 'Yes' ELSE 'No' END AS recurring_text
FROM open_mics;

Joins & Aggregates

SELECT state, COUNT(*) AS shows
FROM open_mics
GROUP BY state
ORDER BY shows DESC;

Window Functions (SQLite ≥ 3.25)

SELECT state, city, venue,
       ROW_NUMBER() OVER (PARTITION BY state ORDER BY city) rn
FROM open_mics;

Import / Export

$ sqlite3 -header -csv open_mics.db \
"SELECT state, city, venue, address, day, time, recurring, description FROM open_mics;" 
> openmics.csv

$ sqlite3 my.db 
sqlite> .mode csv
sqlite> .import data.csv open_mics

TSV & Pretty Table

$ sqlite3 -header -separator $'\t' open_mics.db \
"SELECT * FROM open_mics LIMIT 20;" > sample.tsv

Schema & Migrations

CREATE TABLE open_mics (
  id INTEGER PRIMARY KEY,
  source TEXT NOT NULL,
  state TEXT, city TEXT, venue TEXT,
  address TEXT, day TEXT, time TEXT,
  recurring BOOLEAN DEFAULT 1,
  url TEXT, description TEXT,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Alter Table

ALTER TABLE open_mics ADD COLUMN notes TEXT;

Views & CTEs

CREATE VIEW v_export AS
SELECT state, city, venue, address, day, time,
       CASE WHEN recurring IN (1,'1','true','TRUE') THEN 'Yes' ELSE 'No' END AS recurring,
       description
FROM open_mics;

WITH ca AS (
  SELECT * FROM open_mics WHERE state='CA'
)
SELECT city, COUNT(*) FROM ca GROUP BY city;

Indexes & Performance

CREATE INDEX idx_open_mics_state_city ON open_mics(state, city);
ANALYZE;
EXPLAIN QUERY PLAN SELECT * FROM open_mics WHERE state='CA' AND city='San Diego';

Vacuum & Stats

sqlite> VACUUM;      -- defragment db (can shrink file)
sqlite> ANALYZE;     -- update statistics for planner
sqlite> PRAGMA optimize; -- lightweight maintenance

FTS5 (Full‑Text) & JSON1

FTS5 quickstart

CREATE VIRTUAL TABLE mic_search USING fts5(venue, address, description, content='open_mics', content_rowid='id');
INSERT INTO mic_search(mic_search) VALUES('rebuild');
SELECT rowid, highlight(mic_search,0,'<mark>','</mark>') AS venue
FROM mic_search WHERE mic_search MATCH 'coffee NEAR/3 open';

JSON1 examples

SELECT json_extract('{"a":1,"b":[10,20]}', '$.b[1]');  -- 20
SELECT json_set('{"a":1}', '$.b', 2);             -- {"a":1,"b":2}

Transactions

BEGIN IMMEDIATE;
UPDATE open_mics SET time='8:00pm' WHERE id=1;
COMMIT;  use ROLLBACK to revert

Explain / Analyze

sqlite> EXPLAIN QUERY PLAN SELECT * FROM open_mics WHERE state='CA';
-- look for SEARCH TABLE using index vs SCAN (full scan)

Backups & Safety

$ mkdir -p backup
$ sqlite3 open_mics.db \
".backup backup/open_mics_$(date +%F).db"

Always backup before ALTER TABLE or bulk UPDATE.
Prefer transactions for multi‑step changes.
Use PRAGMA foreign_keys = ON; when using FKs.

Battle‑tested One‑Liners

Task	One‑liner
List tables	`sqlite3 db.db ".tables"`
Show columns	`sqlite3 db.db "PRAGMA table_info(open_mics);"`
CSV export (selected cols)	`sqlite3 -header -csv db.db "SELECT state,city,venue,address,day,time,recurring,description FROM open_mics;" > out.csv`
TSV preview	`sqlite3 -header -separator $'\t' db.db "SELECT * FROM open_mics LIMIT 20;"`
Top states	`sqlite3 db.db "SELECT state,COUNT(*) c FROM open_mics GROUP BY state ORDER BY c DESC LIMIT 10;"`
Check db path	`sqlite3 db.db "PRAGMA database_list;"`

Interactive Builder: Craft your sqlite3 CSV export

Database file

Table

Columns (comma‑separated) WHERE (optional)

ORDER BY

LIMIT

Output file: export.csv

Generated command

Fill the form and click Build Command.