Every SQLite output mode explained

SQLite has many viewing modes it can output, and I have difficulty remembering them all and what to use when, so I’m posting this as a public memory for myself and anyone else finding themselves googling “sqlite all modes example” a lot.

Column

Good for eyeballing data interactively - aligns values into readable columns.

.mode column

id  name   email
--  -----  -------------------
1   Alice  alice@example.com
2   Bob    bob@example.com

List

Useful for piping output into shell scripts or other tools that split on a delimiter.

.mode list

1|Alice|alice@example.com
2|Bob|bob@example.com

Line

Handy when you have wide rows and want to read each field on its own line.

.mode line

   id = 1
 name = Alice
email = alice@example.com

   id = 2
 name = Bob
email = bob@example.com

CSV

Use this when you want to export data to a spreadsheet or another tool that accepts CSV.

.mode csv

1,Alice,alice@example.com
2,Bob,bob@example.com

Tabs

Like list mode, but tab-separated - easier to paste into Excel or Numbers directly.

.mode tabs

1	Alice	alice@example.com
2	Bob	bob@example.com

HTML

Outputs table rows ready to drop into an HTML page.

.mode html

<tr><td>1</td><td>Alice</td><td>alice@example.com</td></tr>
<tr><td>2</td><td>Bob</td><td>bob@example.com</td></tr>

Insert

Generates runnable INSERT statements - good for producing quick migration or seed SQL.

.mode insert users

INSERT INTO users VALUES(1,'Alice','alice@example.com');
INSERT INTO users VALUES(2,'Bob','bob@example.com');

JSON

Use when you need to hand the results off to something that consumes JSON (note: some older JSON parsers error or warn on a top-level list).

.mode json

[
  {"id":1,"name":"Alice","email":"alice@example.com"},
  {"id":2,"name":"Bob","email":"bob@example.com"}
]

Markdown

Outputs a GitHub-flavoured markdown table, good for pasting into docs or READMEs.

.mode markdown

| id | name  | email               |
|----|-------|---------------------|
| 1  | Alice | alice@example.com   |
| 2  | Bob   | bob@example.com     |

Box

A nicer-looking version of table mode using Unicode box-drawing characters.

.mode box

┌────┬───────┬────────────────────┐
│ id │ name  │ email              │
├────┼───────┼────────────────────┤
│ 1  │ Alice │ alice@example.com  │
│ 2  │ Bob   │ bob@example.com    │
└────┴───────┴────────────────────┘

Quote

Similar to list mode but strings are single-quoted, matching SQL literal syntax.

.mode quote

1,'Alice','alice@example.com'
2,'Bob','bob@example.com'

ASCII

Uses ASCII control characters (unit separator 0x1F, record separator 0x1E) as delimiters - safe for data that might contain commas or pipes.

.mode ascii

1␟Alice␟alice@example.com␞
2␟Bob␟bob@example.com␞

Table

A plain ASCII bordered table, like box mode but without Unicode - useful in terminals that don’t support it.

.mode table

+----+-------+-------------------+
| id | name  | email             |
+----+-------+-------------------+
| 1  | Alice | alice@example.com |
| 2  | Bob   | bob@example.com   |
+----+-------+-------------------+

TCL

Outputs values as a TCL list - mainly useful if you’re scripting with TCL or tclsh.

.mode tcl

1 Alice alice@example.com
2 Bob bob@example.com