By default, the SQLite command line shell formats query output as a pipe-separated list, without column headers.

If you need to output the results using commas instead of the pipe-symbol, you can specify this with the .separator dot command.

If you need to format your results as string literals, see How to Enclose Strings in Single Quotes. Or if you need any strings to be enclosed in double quotes, see How to Format Results as CSV.

Example

Here’s an example.

.separator ","

This changes the separator to a comma.

So after entering that command, if I run the following query:

SELECT * FROM Products;

The result looks like this:

1,Widget Holder,139.5
2,Widget Opener,89.7
3,Widgets - 6 Pack,374.2
4,Blue Widget,63.0

You can add a space after the comma if required:

.separator ", "
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

Add Column Headers

You can also add column headers if required. To do this, use the .header dot command.

.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

Save these Settings

Each time you connect to SQLite in a new terminal window, you’ll find that these settings are reset to the SQLite default settings.

To overcome this, you can save your settings to a .sqliterc file, so that you don’t need to keep running the same dot commands each time you connect to SQLite.

To do this, enter your commands into a blank text file:

.mode list
.separator ", "
.headers on

Then save it as .sqliterc in your/(the user’s) home directory.

Now, each time you connect to SQLite in a new terminal window, your query results will be output using commas as separators, and with column headers.