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.