text
.mode csv
.import ./users.csv usersNotes
A reliable shell pattern when loading CSV into an existing table.
SQLite Import, Export, and Backup Cheatsheet
CSV import/export, SQL dumps, VACUUM INTO, backup copies, and repeatable data movement patterns for SQLite databases.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Move data in and out
Import files, export query results, and create SQL dumps.
text
.headers on
.mode csv
.once recent_users.csv
SELECT id, email, created_at FROM users ORDER BY created_at DESC LIMIT 100;Notes
Good for ad hoc reporting and support workflows.
text
.dump usersNotes
Useful when sharing a fixture or moving just one logical dataset.
bash
sqlite3 app.db < seed.sqlNotes
A shell-friendly way to initialize or refresh a local database.
Backup and maintenance
Create safe copies and compact files.
Create a compact backup with VACUUM INTO
Write a fresh copy of the database to a new file.
sqlANYsqlitevacuum intobackup
sql
VACUUM INTO 'backup-2026-03-28.db';Notes
Produces a compact copy and is a strong option for backup-style snapshots.
text
.backup backup.dbNotes
A convenient shell-level backup path for local workflows.
sql
VACUUM;Notes
Can reduce file size after large deletes or schema churn.
Run an integrity check
Verify the database structure and content consistency.
sqlANYsqliteintegrity_checkhealth
sql
PRAGMA integrity_check;Notes
Useful before and after migrations, backup jobs, or corruption investigations.
More in SQLite
SQLite Triggers, Views, and Generated Columns Cheatsheet
CREATE VIEW, CREATE TRIGGER, audit logging, derived values, generated columns, and automation patterns in SQLite.
SQLite Full-Text Search (FTS5) Cheatsheet
FTS5 virtual tables, MATCH queries, prefix search, phrase search, bm25 ranking, snippets, and highlighting patterns for SQLite search features.
SQLite PRAGMA and Administration Cheatsheet
High-signal PRAGMA commands for foreign keys, cache, synchronous settings, page size, temp storage, health checks, and general SQLite administration.
SQLite JSON Functions Cheatsheet
json_extract, json_set, json_each, JSON operators, generated columns, and practical JSON query/update patterns in SQLite.
SQLite Transactions, WAL, and Locking Cheatsheet
BEGIN modes, COMMIT and ROLLBACK, savepoints, WAL mode, busy timeouts, and concurrency patterns for SQLite applications.
SQLite Indexes and Query Planner Cheatsheet
CREATE INDEX patterns, covering and partial indexes, EXPLAIN QUERY PLAN, ANALYZE, and practical tuning tips for SQLite queries.