SQLite has a number of tabular output modes. One of these is called table mode.

Below is an example of using table mode to output SQLite’s query results as a table.

Example

You can change the output mode any time you’re connected to the SQLite command line interface. The syntax is .mode followed by the desired output mode.

Here’s how to change it to table mode:

.mode table

That’s all that’s required to output your query results in a table.

Now when we run a query, the results will be output as a table:

SELECT * FROM Pets;

Result:

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

Table mode is one of three tabular modes that were introduced in SQLite 3.33.0. The others are box and markdown.

Not Working? Try column Mode

If you get an error when trying to change to table mode, it could be that you’re using an older version of SQLite. Table mode was introduced in SQLite 3.33.0, which was released on 14 August 2020.

If you’re using an older version of SQLite, and you are unable to upgrade to a more recent version, then you can always use column mode. Column mode is available in SQLite versions prior to 3.33.0.

Example:

.mode column

That’s all that’s required to output your query results in a table.

Now when we run a query, the results will be output as a table:

SELECT * FROM Pets;

Result:

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

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