The Interactive Shell — Dot Commands & REPL Workflow

The shell is the swiss army knife

The sqlite3 CLI is more than a query runner. It is a full interactive shell with its own meta-command language — the dot commands. They start with a leading . and are interpreted by the shell itself, not by SQLite.

The dozen you'll actually use:

.help — list every dot command with a one-line description.
.tables — list tables in the current database.
.schema [TABLE] — print CREATE statements; optional table filter.
.indexes [TABLE] — list indexes; optional table filter.
.databases — show attached databases.
.mode column|box|csv|json|markdown|line — change result formatting.
.headers on — print column names with results.
.timer on — print wall-clock time per query.
.read FILE.sql — execute SQL from a file.
.import FILE TABLE — bulk load a CSV/TSV.
.dump — emit a SQL script that reproduces the database.
.backup TARGET.db — safe online snapshot.
.quit — exit the shell (Ctrl-D works too).

Tip: Type .mode box + .headers on in the very first second of any new session. The default list mode is unreadable past five columns; box mode renders pretty Unicode tables and survives long values.

You can also run the CLI in batch mode to pipe SQL in and out — perfect for scripts, cron jobs, and one-off explorations.

Code

REPL workflow that doesn't make you cry·bash

sqlite3 myapp.db <<'EOF'
.headers on
.mode box
.timer on
.tables
.schema users
SELECT count(*) AS n_users FROM users;
EOF

# Output:
# users notes  conversations
# CREATE TABLE users(...)
# ┌──────────┐
# │ n_users  │
# ├──────────┤
# │ 42       │
# └──────────┘
# Run Time: real 0.001 user 0.000 sys 0.000

Saved REPL config — runs every time you open the shell·bash

# ~/.sqliterc
.headers on
.mode box
.timer on
.changes on
.nullvalue NULL

# Then any time you open the CLI, those settings are already on.
sqlite3 myapp.db

Pipe SQL in, get JSON out — perfect for jq·bash

sqlite3 myapp.db -json 'SELECT id, email FROM users LIMIT 3' | jq
# [
#   { "id": 1, "email": "alice@x.com" },
#   { "id": 2, "email": "bob@x.com" },
#   { "id": 3, "email": "carol@x.com" }
# ]

Exercise

Create a ~/.sqliterc with at least three dot commands turned on by default. Then open one of your real SQLite databases (Pippa's, a browser's, your own). Use .tables, .schema, and .indexes to map out one table you don't already know. Write down what you discovered about how that app models its data.

The Interactive Shell — Dot Commands & REPL Workflow

The shell is the swiss army knife

Code

External links

Exercise

Progress

Comments 0