While most developers are familiar with SQLite's .dump command for full database exports or .schema for structure-only exports, there's a lesser-known but incredibly useful command: .mode insert. This command generates INSERT statements from query results, making it perfect for partial data exports or exporting data in a specific order.
But there's an even better trick that makes the output much more readable and portable: combining it with .headers on.
The Basic Approach
You can export data as INSERT statements using .mode insert:
sqlite> .mode insert mytable
sqlite> SELECT * FROM users;
INSERT INTO mytable VALUES(1,'John','john@example.com');
INSERT INTO mytable VALUES(2,'Jane','jane@example.com');
This works, but the output relies on column order, making it fragile when importing to databases with different column arrangements.
The Magic Trick
Add .headers on before your query:
sqlite> .mode insert mytable
sqlite> .headers on
sqlite> SELECT 1 id, 'John' name, 'john@example.com' email;
INSERT INTO mytable(id,name,email) VALUES(1,'John','john@example.com');
Real-World Example
Here's how to export a complete table with proper column names:
sqlite3 mydb.sqlite ".mode insert users" ".headers on" ".output export.sql" "SELECT * FROM users;"
This generates:
INSERT INTO users(id,name,email,created_at) VALUES(1,'John','john@example.com','2023-01-15');
INSERT INTO users(id,name,email,created_at) VALUES(2,'Jane','jane@example.com','2023-01-16');
Bonus: Skip Conflicts
For cases where you only want to add new records without overwriting existing ones, you can easily replace INSERT with INSERT OR IGNORE:
sed 's/INSERT INTO/INSERT OR IGNORE INTO/g' export.sql
Why This Matters
- Portable: Works regardless of column order in target database
- Readable: Column names make the SQL self-documenting
- Safer: Explicit column mapping prevents import errors
- Debuggable: Easy to spot data issues when columns are named
This simple combination of two SQLite dot commands turns basic data dumps into production-ready SQL scripts.
Reference
For more information about SQLite's command-line interface and dot commands, see the official SQLite CLI documentation.