SQLite dot-commands: output formatting using ‘.mode list’

May 20, 2020

Only have a minute? Skip down to the tl;dr;.

The command line shell for SQLite provides a variety of ways to output query results to fit the needs of your project. In all, the latest version of the sqlite3 command shell provides 10 different ways to output query results. In this post we will cover the default output mode, list, and see the different options that are available.

All the examples below can be run using the Repl.it link here: https://repl.it/@log4code/SQLiteOutputFormats.

For this example we have a SQLite database with just one table. This table is named companies. It contains a small list of company records which we will query in different ways to show a variety of output formatting available via the .mode list command for the SQLite shell.

As previously mentioned, the list mode for SQLite is the default mode for output for the shell. There is no activation required. To show this, our first query will select all the rows and columns from the companies table.

select * from companies;

Here we can see the output with the default list mode:

1|Southern Tool Company|AL
2|Ohio Valley Tooling|OH
3|Midwest Machining, LLC|IN
4|Pacific Parts|CA
5|ABC Manufacturing|ME
6|Taylor & Sons Manufacturing|FL
7|Backlot Machining, Inc.|AZ

Each row in the result set is on a single line. Each column is separated by the pipe (|) character. For list mode, the pipe character is the default column separator.

Adding Column Headers

Notice in the output that we do not have column headers. This may or may not be preferred based on your needs. But, what if we wanted column headers? Adding column headers is as simple as performing an additional ‘dot command’ for the shell:

.headers on

The .headers command takes an argument (on or off) to allow for changing the output back and forth. To see the previous output with column headers, we will adjust our statements:

.headers on
select * from companies;

Now we have successfully added column headers to the output. Each column is separated by the same delimiter, in this case the | character.

company_id|company_name|company_state
1|Southern Tool Company|AL
2|Ohio Valley Tooling|OH
3|Midwest Machining, LLC|IN
4|Pacific Parts|CA
5|ABC Manufacturing|ME
6|Taylor & Sons Manufacturing|FL
7|Backlot Machining, Inc.|AZ

Changing the delimiter

While the | character is the default delimiter, it can be changed. What if one of the values returned contained a | character?

For example, what if instead of ‘Pacific Parts’ it was ‘Pacific|Parts’? The output for this row would now look like this:

Oops! Now we have 4 columns for that row instead of 3. Pacific and Parts now look like values in two separate columns instead of one. This is a good reminder of the importance of knowing about the data you are working with and checking the output for any issues you may need to be aware of.

We can solve this problem multiple ways. One way is to specify the output column delimiter to be different than the default |. We do this with the .seperator command

.headers on
.separator '|-|'
select * from companies;

Now we have specified the column separator to be what we believe should be unique: |-|. Let’s check out the output:

company_id|-|company_name|-|company_state  
1|-|Southern Tool Company|-|AL  
2|-|Ohio Valley Tooling|-|OH  
3|-|Midwest Machining, LLC|-|IN  
4|-|Pacific|Parts|-|CA  
5|-|ABC Manufacturing|-|ME  
6|-|Taylor & Sons Manufacturing|-|FL  
7|-|Backlot Machining, Inc.|-|AZ

The column delimiter has indeed been changed from | to |-|. Now the Pacific|Parts value will work just fine with the output.

tl;dr;

The default output for the SQLite shell is .mode list.
This will output each row on a line with the default column delimiter of the | character.
To change the default delimiter character, use the .separator command
By default the .mode list command does not output column headers
To add column headers to the output, use the .headers on command.