Back to Notes
Notes

Setting a Default Output Mode in SQLite Using an Alias

How to set a default output mode in SQLite with a simple shell alias.

A wooden drawer divided into neatly organized compartments
  • This probably doesn't need to be a post, but I want to document it so I don't forget and end up searching for a solution again.

  • The .mode dot-command in SQLite sets the output mode so that the query response is formatted in a specified way.

  • To see the list of available output modes, you can execute .mode followed by some random text:

Plain text
sqlite> .mode karate
Error: mode should be one of: ascii box column csv html insert json line list markdown qbox quote table tabs tcl
  • The available output modes in my version of SQLite are: ascii, box, column, csv, html, insert, json, line, list, markdown, qbox, quote, table, tabs, and tcl.
  • Personally, I prefer the box output mode because it's easier to read the contents compared to other modes but I don't want to remember to type .mode box everytime I open a SQLite db.
SQL
sqlite>.mode box
sqlite> select * from shakespear limit 5;
┌──────────┬──────────┬──────────────────┬──────────────┬───────────────┬──────────────────────────────────────────────────────────────┐
│ Dataline │   Play   │ PlayerLinenumber │ ActSceneLine │    Player     │                          PlayerLine                          │
├──────────┼──────────┼──────────────────┼──────────────┼───────────────┼──────────────────────────────────────────────────────────────┤
│ 1        │ Henry IV │                  │              │               │ ACT I                                                        │
├──────────┼──────────┼──────────────────┼──────────────┼───────────────┼──────────────────────────────────────────────────────────────┤
│ 2        │ Henry IV │                  │              │               │ SCENE I. London. The palace.                                 │
├──────────┼──────────┼──────────────────┼──────────────┼───────────────┼──────────────────────────────────────────────────────────────┤
│ 3        │ Henry IV │                  │              │               │ Enter KING HENRY, LORD JOHN OF LANCASTER, the EARL of WESTMO │
│          │          │                  │              │               │ RELAND, SIR WALTER BLUNT, and others                         │
├──────────┼──────────┼──────────────────┼──────────────┼───────────────┼──────────────────────────────────────────────────────────────┤
│ 4        │ Henry IV │ 1                │ 1.1.1        │ KING HENRY IV │ So shaken as we are, so wan with care,                       │
├──────────┼──────────┼──────────────────┼──────────────┼───────────────┼──────────────────────────────────────────────────────────────┤
│ 5        │ Henry IV │ 1                │ 1.1.2        │ KING HENRY IV │ Find we a time for frighted peace to pant,                   │
└──────────┴──────────┴──────────────────┴──────────────┴───────────────┴──────────────────────────────────────────────────────────────┘

Anyways, instead of having to type .mode box every single time I open a SQLite db, I added the following alias to my shell profile:

Plain text
alias sqlite='sqlite3 -cmd ".mode box"'

This alias sets the output mode to box automatically whenever I open a SQLite database.

finito.