The SQLite command line interface allows you to format your query results in TCL mode. Doing this encloses all output in double quotes, including the column headers if you’ve specified those. Any internal double quotes are escaped with a backslash.

Example

.mode tcl
SELECT * FROM Products;

Result:

"1" "Widget Holder" "139.5"
"2" "Widget Opener" "89.7"
"3" "Bob's \"Best\" Widget" "374.2"
"4" "Blue Widget" "63.0"

Notice that everything is enclosed in double quotes, even the numeric values. Also, the third row contains internal double quotes ("Best"). These double quotes have been escaped with backslashes.

Column Headers

When you use TCL mode, even the column headers are enclosed in double quotes. This assumes that you’re outputting the column headers of course.

To output column headers, use .headers on. Here’s the same query again, but this time including the column headers.

.mode tcl
.headers on
SELECT * FROM Products;

Result:

"ProductId" "ProductName" "Price"
"1" "Widget Holder" "139.5"
"2" "Widget Opener" "89.7"
"3" "Bob's \"Best\" Widget" "374.2"
"4" "Blue Widget" "63.0"

Save to a Configuration File

You can save these settings to a configuration file so that you don’t need to keep changing the mode every time you connect to SQLite.

To do this, add your settings to a blank file:

.mode tcl
.headers on

Then save that file as .sqliterc in your home directory.

This assumes that you don’t already have a .sqliterc file. If you do, just edit that instead.

Now when you use the SQLite CLI, it will use the settings in your .sqliterc file instead of the default settings (which formats the results as a pipe-separated list).