The SQLite command line interface provides us with several options for outputting our results in tabular format.

These can be set using the .mode command. You simply type .mode followed by the desired output format.

Below are examples of the various tabular modes available in SQLite.

Column Mode

Column output mode automatically expands columns to contain the longest output row and automatically turns “.header” on if it has not been previously set.

Set to column mode:

.mode column

Now run a query:

SELECT * FROM Pets;

Result:

PetId  PetName  TypeId
-----  -------  ------
1      Homer    3     
2      Yelp     1     
3      Fluff    2     
4      Brush    4    

As we can see, column mode presents the results in nice neat columns.

The following output modes are only available in SQLite 3.33.0 (released on 14 August 2020) and later.

Table Mode

Set to table mode:

.mode table

Now run a query:

SELECT * FROM Pets;

Result:

+-------+---------+--------+
| PetId | PetName | TypeId |
+-------+---------+--------+
| 1     | Homer   | 3      |
| 2     | Yelp    | 1      |
| 3     | Fluff   | 2      |
| 4     | Brush   | 4      |
+-------+---------+--------+

Markdown Mode

Set to markdown mode:

.mode markdown

Now run a query:

SELECT * FROM Pets;

Result:

| PetId | PetName | TypeId |
|-------|---------|--------|
| 1     | Homer   | 3      |
| 2     | Yelp    | 1      |
| 3     | Fluff   | 2      |
| 4     | Brush   | 4      |

Box Mode

Set to box mode:

.mode box

Now run a query:

SELECT * FROM Pets;

Copying and pasting the result when in box mode doesn’t work very well. Here’s a screenshot of the result from my system:

Save to a Configuration File

You can save your preferred mode 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 table

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).