SQLite

sqlite3 shell, dot-commands, importing and exporting CSV, backups and pragmas.

Shell

Open a database file

sqlite3 <database>.db

Run a query and exit

sqlite3 <database>.db "SELECT count(*) FROM <table>;"

List tables

.tables

Table schema

.schema <table>

Readable output with headers

.mode box

Older versions: .headers on and .mode column

Exit

.quit

Import a CSV into a table

sqlite3 <database>.db ".import --csv data.csv <table>"

Export a query to CSV

sqlite3 -header -csv <database>.db "SELECT * FROM <table>;" > out.csv

Dump the whole database as SQL

sqlite3 <database>.db .dump > dump.sql

Restore from an SQL dump

sqlite3 <database>.db < dump.sql

Consistent backup of a live databaseadvanced

sqlite3 <database>.db ".backup backup.db"

Check integrityadvanced

PRAGMA integrity_check;

Reclaim free spaceadvanced

VACUUM;

Enable WAL mode (better concurrency)advanced

PRAGMA journal_mode=WAL;

Query planadvanced

EXPLAIN QUERY PLAN SELECT * FROM <table> WHERE id = 1;