The SQLite command line shell has a .mode dot command, which enables you to change the way SQLite formats its query results.

The default output for queries is as a pipe-separated list, however, you can use the .mode dot command to change this to another format, such as an HTML table.

Example

Here’s an example to demonstrate.

.mode html
SELECT * FROM Products;

Result:

<TR><TD>1</TD>
<TD>Widget Holder</TD>
<TD>139.5</TD>
</TR>
<TR><TD>2</TD>
<TD>Widget Opener</TD>
<TD>89.7</TD>
</TR>
<TR><TD>3</TD>
<TD>Widgets - 6 Pack</TD>
<TD>374.2</TD>
</TR>
<TR><TD>4</TD>
<TD>Blue Widget</TD>
<TD>63.0</TD>
</TR>

Note that the table doesn’t include the opening and closing <TABLE> tags, so you would need to add these yourself.

Add Headers

You can also use the .headers dot command to add table headers that contain the names of the columns.

.headers on
SELECT * FROM Products;

Result:

<TR><TH>ProductId</TH>
<TH>ProductName</TH>
<TH>Price</TH>
</TR>
<TR><TD>1</TD>
<TD>Widget Holder</TD>
<TD>139.5</TD>
</TR>
<TR><TD>2</TD>
<TD>Widget Opener</TD>
<TD>89.7</TD>
</TR>
<TR><TD>3</TD>
<TD>Widgets - 6 Pack</TD>
<TD>374.2</TD>
</TR>
<TR><TD>4</TD>
<TD>Blue Widget</TD>
<TD>63.0</TD>
</TR>

Note that the column headers are nested inside <TH> elements, as opposed to <TD> elements for the data.

Saving these Settings

The above steps only work within the current session. If you open a new terminal window, it will use the SQLite default settings again. That is, unless you’ve chosen to store your settings in a .sqliterc file.

You can store the above settings in a .sqliterc file so that all future queries are automatically output as an HTML table, unless you explicitly specify otherwise.

To do this, enter the following into a blank text file:

.mode html
.headers on

Now whenever you open SQLite in a new terminal window and run a query, your results will be formatted as an HTML table, with table headers containing the column names.