CLI and Dot-Commands

sqlite3 app.db                  # opens, creating the file only on the first write
sqlite3                         # a transient in-memory database
sqlite3 :memory:                # the same, explicitly
sqlite3 'file:app.db?mode=ro'   # read-only via URI
sqlite3 'file:app.db?immutable=1'  # read-only AND promise nobody is writing
sqlite3 app.db .quit            # run a dot-command and exit
sqlite3 app.db "SELECT count(*) FROM users;"    # one SQL statement and exit

sqlite3 :memory: "SELECT sqlite_version();"     # 3.51.0

sqlite3 app.db ".databases" ".tables" ".schema users" ".indexes users"
# main: /Users/you/app.db r/w
# users  posts
# CREATE TABLE users(id INTEGER PRIMARY KEY, email TEXT UNIQUE);

SELECT name, type FROM sqlite_schema WHERE type IN ('table','view');
SELECT * FROM pragma_table_info('users');
SELECT * FROM pragma_index_list('users');

.mode box            -- unicode box drawing, the nicest for reading
.headers on

sqlite3 :memory: ".mode markdown" ".headers on" "SELECT 1 AS id, 'Ada' AS name;"
# | id | name |
# |----|------|
# | 1  | Ada  |

sqlite3 :memory: ".mode insert people" "SELECT 1 AS id, 'Ada' AS name;"
# INSERT INTO people VALUES(1,'Ada');

sqlite3 :memory: ".mode quote" "SELECT 1, 'a', NULL;"
# 1,'a',NULL

sqlite3 app.db ".timer on" ".changes on" "UPDATE users SET name='Ada' WHERE id=1;"
# changes: 1   total_changes: 1
# Run Time: real 0.001 user 0.000 sys 0.000

# Export one query to CSV
sqlite3 app.db ".mode csv" ".headers on" ".once users.csv" "SELECT * FROM users;"

# Load a CSV, creating the table from the header row
sqlite3 app.db ".import --csv --skip 0 users.csv users"

# Load into an existing table, skipping the header line
sqlite3 app.db ".import --csv --skip 1 users.csv users"

# Run a migration file, stopping on the first error
sqlite3 app.db ".bail on" ".read migrations/001_init.sql"

-- Import to a staging table, then cast into the real one
.import --csv raw.csv staging
INSERT INTO users(id, email) SELECT CAST(id AS INTEGER), email FROM staging;
DROP TABLE staging;

sqlite3 app.db ".dump" > dump.sql               # portable, text, diffable
sqlite3 app.db ".dump users" > users.sql        # one table
sqlite3 new.db ".read dump.sql"                 # restore
sqlite3 app.db ".backup backup.db"              # binary, safe while writers are active
sqlite3 app.db "VACUUM INTO 'backup.db';"       # 3.27+, the modern equivalent
sqlite3 broken.db ".recover" > salvage.sql      # after corruption

sqlite3 app.db ".load ./vector0"       # load a loadable extension, .so/.dylib/.dll

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

sqlite3 -init /dev/null app.db     # ignore ~/.sqliterc, for reproducible scripts

sqlite3 -header -csv app.db "SELECT * FROM users;" > users.csv
sqlite3 -json app.db "SELECT id, email FROM users LIMIT 10;"
sqlite3 -line app.db "SELECT * FROM users WHERE id=1;"
sqlite3 -cmd ".mode box" -cmd ".headers on" app.db "SELECT * FROM users LIMIT 3;"
sqlite3 -readonly app.db "SELECT count(*) FROM users;"

# Pipe SQL in from stdin
echo "SELECT count(*) FROM users;" | sqlite3 app.db

# A scriptable, quiet, fail-fast invocation
sqlite3 -init /dev/null -bail -noheader -list app.db "SELECT id FROM users;"