Programming

SQLite CLI Mastery

February 18, 2026·2 min read

SQLite is the most-deployed database on Earth — on every phone, in every browser, in countless embedded devices. The sqlite3 command-line shell is also one of the best interactive SQL clients ever written. Most of its power lives behind the . dot commands.

The dot commands

sqlite3 mydata.db
.tables                 # list tables
.schema users           # show CREATE TABLE
.headers on
.mode column            # or csv, json, html, markdown
.timer on               # show timing for each query
.databases              # list attached DBs
.indexes                # list indexes
.dump                   # SQL dump of everything
.quit

Import and export

sqlite3 mydata.db
.mode csv
.import data.csv users          # CSV → table 'users'

.mode csv
.headers on
.output users.csv
SELECT * FROM users WHERE active = 1;
.output stdout

WAL mode: turn it on

The default journal mode is DELETE, which uses an exclusive lock for writes. WAL (Write-Ahead Logging) lets readers and a single writer operate concurrently — a massive throughput improvement for any database with concurrent access.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;     -- safe with WAL; FULL is paranoid
PRAGMA cache_size = -64000;      -- 64 MB cache (negative = KB)
PRAGMA foreign_keys = ON;

Indexes

SQLite doesn't auto-index foreign keys. Every column you join or filter on, in a query that runs more than rarely, deserves an index:

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

Composite indexes match left-to-right. The third index above serves queries that filter by user_id alone OR by user_id AND created_at — but not created_at alone.

EXPLAIN QUERY PLAN

The most useful debugging command in SQL. Shows whether a query is scanning the table or using an index.

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = 42 AND created_at > '2026-01-01';

-- output you want:
-- SEARCH TABLE orders USING INDEX idx_orders_user_created (user_id=? AND created_at>?)

-- output you don't:
-- SCAN TABLE orders

Useful built-ins

-- JSON
SELECT json_extract(data, '$.user.email') FROM events;

-- Date/time
SELECT strftime('%Y-%m', created_at), COUNT(*) FROM orders GROUP BY 1;

-- Window functions
SELECT user_id, amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at)
FROM orders;

-- UPSERT
INSERT INTO config(key, value) VALUES ('theme', 'dark')
ON CONFLICT(key) DO UPDATE SET value = excluded.value;

Backups while live

# Online backup — safe while DB is in use
sqlite3 mydata.db ".backup '/var/backups/mydata-$(date +%F).db'"

# Or copy WAL+SHM along with the main file
cp mydata.db mydata.db-wal mydata.db-shm /var/backups/

When SQLite isn't enough

One writer at a time. No network access (without third-party servers). Single-machine. For everything else SQLite is shockingly capable — sites handle millions of requests per day backed by a single SQLite file.