By default, when you connect to the SQLite command line shell and run a query, the result is returned as a pipe separated list.
You might have noticed that the results don’t include the column names, which can make your results confusing if you’re trying to read them, especially if the query returned many columns.
Fortunately, there’s an easy way to format the output so that it’s displayed as a column with column headers.
The Default Output
As an example, here’s how the SQLite command line interface outputs query results by default:
SELECT * FROM Products;
Result:
1|Widget Holder|139.5 2|Widget Opener|89.7 3|Widgets - 6 Pack|374.2 4|Blue Widget|63.0
So there’s not column headers and it’s displayed as a pipe-separated list.
Add Column Headers
To add the column names to each column use .headers on
.
.headers on
SELECT * FROM Products;
Result:
ProductId|ProductName|Price 1|Widget Holder|139.5 2|Widget Opener|89.7 3|Widgets - 6 Pack|374.2 4|Blue Widget|63.0
You can disable headers by using .headers off
.
Format Results as a Column
You can also use .mode column
to format the results as a column.
.mode column
SELECT * FROM Products;
Result:
ProductId ProductName Price ---------- ------------- ---------- 1 Widget Holder 139.5 2 Widget Opener 89.7 3 Widgets - 6 P 374.2 4 Blue Widget 63.0
If you need to return it to the default format, you can use .mode list
.
Change Column Width
You might notice that in the previous example, one of the product names has been truncated. This is because it’s wider than the column’s width.
By default, each column is between 1 and 10 characters wide, depending on the column header name and the width of the first column of data. Data that is too wide to fit in a column is truncated.
However, you can use the .width
dot command to set the columns to a specific width.
Here’s how we can fix the previous example so that the third product isn’t truncated.
.width 0 16 0
SELECT * FROM Products;
Result:
ProductId ProductName Price ---------- ---------------- ---------- 1 Widget Holder 139.5 2 Widget Opener 89.7 3 Widgets - 6 Pack 374.2 4 Blue Widget 63.0
You may be wondering why I set the first and third columns to zero?
Using a value of 0
actually makes the column self-adjusting (to a certain point). Specifying 0
sets the column to the greater of three numbers; 10, the width of the header, and the width of the first row of data.
In my case, this works fine for the first and third columns, but not for the second column. Therefore, I set that column to 16
(which is how many characters the third product uses).
Reset to the Default
If you need to briefly reset the output to use the SQLite default (i.e. a pipe-separated list with no column headers), you can simply open a new terminal window and run your commands from there. SQLite will use its default settings in this case.
Or if you want to reset the current terminal window to use the default settings, you can always just use the following:
.headers off
.mode list
.separator "|"
In this case, I added the .separator
dot command, just in case you’d previously changed the separator.
In case you’re wondering, yes, you can use .separator ", "
to output the results as a comma separated list.
Persist the Column Settings
As mentioned, every time you open a new terminal window to connect to SQLite, queries will revert to SQLite’s default format (pipe-separated list).
To save yourself from having to re-enter the above commands every time you connect to SQLite, you can enter them into a .sqliterc
file.
For example, open a blank text file and enter the following:
.mode column
.headers on
Save the file as .sqliterc
to the user’s home directory, and SQLite will use it each time it connects.
For example, if the user’s home directory is /Users/bart, then you would place it inside that directory, so that it’s located at /Users/bart/.sqliterc.
This is a hidden file, so you might get various prompts asking you to confirm, etc depending on your system. Just agree with the prompts until it’s saved in the user’s home directory.