sqlite3 .dump: Back Up and Restore SQLite
sqlite3 app.db .dump > dump.sql exports a database as SQL you can replay into a fresh file to restore.
SQLite backups are either a SQL text dump (.dump) or a binary copy (.backup). The SQL dump is portable and diffable, which suits committing a seed fixture; the binary backup is faster for large databases.
What it does
.dump walks the database and prints the SQL (CREATE plus INSERT wrapped in a transaction) needed to recreate it. Replaying that SQL into a new file restores it. .backup makes a consistent binary copy of the live database file, safe even while it is in use.
Common usage
# SQL text dump and restore
sqlite3 app.db ".dump" > dump.sql
sqlite3 restored.db < dump.sql
# just the schema, no data
sqlite3 app.db ".schema" > schema.sql
# binary backup (consistent copy)
sqlite3 app.db ".backup backup.db"Options
| Command | What it does |
|---|---|
| .dump [table] | Export the database (or one table) as SQL |
| .schema [table] | Export CREATE statements only, no data |
| .backup <file> | Make a consistent binary copy |
| .restore <file> | Load a binary backup into the current database |
| .read <file> | Run SQL from a file (like SOURCE) |
In CI
Commit a .dump SQL file as a seed fixture and replay it into a fresh database at the start of a test run, so every run starts from a known state. Redirect .dump to a file; it writes to stdout. For a large database used mid-test, .backup avoids a torn copy that a plain cp of the file could produce.
Common errors in CI
"Error: near line N: table users already exists" when replaying a dump into a database that already has objects means you should restore into a fresh file, not an existing one. "Error: no such table" during .dump table means a wrong table name. A restored database that is empty usually means the .dump output went to the terminal instead of a file because the redirect was missing.