You can set an output mode for SQLite

Via a toot from Ian Jackson, I learnt that SQLite supports different output formats:

The default output mode is list, where values are pipe-separated:

sqlite> .mode list
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>

Ian’s toot shows off the box mode, which renders some ASCII boxes:

sqlite> .mode box
sqlite> select * from tbl1;
┌─────────┬─────┐
│   one   │ two │
├─────────┼─────┤
│ hello   │ 10  │
│ goodbye │ 20  │
└─────────┴─────┘
sqlite>

(They look better in my terminal, where the lines are all joined up.)

Perhaps the one I’m most likely to use is line, which prints one column per line – very useful for tables which have lots of columns:

sqlite> .mode line
sqlite> select * from tbl1;
  one = hello
  two = 10

  one = goodbye
  two = 20
sqlite>

Currently there are 14 different output formats. The others that look interesting are csv, json, html and markdown, which print the data in markup that you can use immediately – I can imagine there might be cases where that might allow me to use the raw output of a SQLite query.