How to Transfer SQLite Data Between Databases with Column-Safe Exports

2 min read

Discover how combining SQLite's .mode insert with .headers on creates more readable and portable INSERT statements for data export and migration.

command-linedata-exportdatabasemigrationsqlsqlitetips

How to Transfer SQLite Data Between Databases with Column-Safe Exports

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.