Export & Import

Code

# Export entire database to SQL text
sqlite3 app.db .dump > backup.sql
sqlite3 app.db ".schema" > schema.sql

# Export a single table to CSV
sqlite3 -header -csv app.db "SELECT * FROM users;" > users.csv

# Export query results as JSON
sqlite3 -json app.db "SELECT * FROM users WHERE published = 1;" > users.json

# Restore from a SQL dump
sqlite3 new.db < backup.sql

# Import CSV into a table
sqlite3 app.db <<SQL
.mode csv
.import data.csv new_table
SQL

# Backup with the backup API (online, consistent)
sqlite3 app.db ".backup 'backup.db'"

# Inspect a database
sqlite3 app.db ".tables"
sqlite3 app.db ".schema users"

Explanation

The sqlite3 CLI provides .dump for SQL text backups and -csv/-json flags for format-specific exports. Restoring is as simple as piping a SQL file into a new database. The .backup command performs an online consistent copy using the SQLite backup API, safe even while the source is being written.

More SQLite Snippets