Exporting SQLite Data: JSON and CSV Without Losing Your Mind

Getting data out of SQLite is easy until a comma or a quote in your text silently shifts a column. Here are the clean ways to export, and the traps to avoid.

JSON — the friendly format

The sqlite3 CLI outputs JSON natively:

sqlite3 -json app.db "SELECT id, title, published_at FROM posts WHERE status='published';"

Clean arrays of objects, no escaping surprises. Use this for anything machine-readable.

CSV — with headers and proper quoting

sqlite3 -header -csv app.db "SELECT * FROM posts;" > posts.csv

-header adds the column names; -csv applies standard CSV quoting (RFC 4180), so commas and quotes inside fields stay intact.

In Go

If you export from application code, use encoding/json for JSON and encoding/csv for CSV — both handle escaping correctly. Do not hand-roll a "join with commas" string; that is where commas-in-fields corrupt everything.

The traps

• No -header → you lose column names and every consumer guesses.
• Hand-rolled CSV → a , or " in any text field silently breaks the row.
• Exporting while a write is in flight → use a consistent read or export from a VACUUM INTO snapshot.

The habit

For backups, export from a consistent snapshot. For feeds and integrations, use the CLI's -json/-csv flags or the stdlib encoders — never string concatenation. Sizing the data you are exporting with the estimator below tells you how much is actually moving.