DuckDB is a database management system tailored for analytical tasks. Its command line interface (CLI) provides a convenient way to interact with databases and execute queries. The CLI provides us with the ability to configure the output mode for query results, offering flexibility in how data is displayed or exported.

This article provides a quick overview of how to change the output mode in the DuckDB CLI.

What is Output Mode in DuckDB?

The output mode in DuckDB determines the format in which query results are displayed in the CLI. This can range from a simple tabular view to more structured formats like JSON or CSV. The CLI’s ability to toggle between these modes makes it highly adaptable for different use cases, whether for quick data exploration or exporting results for further analysis.

Available Output Modes

We can check the available output modes with the .help command:

.help mode

Output:

.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns.  (See .width)
     duckbox   Tables with extensive features
     html      HTML <table> code
     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     jsonlines Results in a NDJSON
     latex     LaTeX tabular environment code
     line      One value per line
     list      Values delimited by "|"
     markdown  Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements
     trash     No output

This starts with the syntax for the .mode command, then it’s followed up by a list of supported modes.

Check the Current Output Mode

Before changing the output mode, you may want to check the current setting.

Example:

.mode

Sample output:

current output mode: duckbox

The current output mode is duckbox, which is the default mode.

Change the Output Mode

You can change the output mode in DuckDB CLI using the .mode command followed by the desired mode.

Example:

.mode table

That sets the output mode to table.

Verify the Change

You can verify the new output mode by running a query.

Example:

SELECT * FROM t1;

Output:

+----+---------+
| c1 |   c2    |
+----+---------+
| 1  | Ashbor  |
| 2  | Bromley |
| 3  | Calchum |
+----+---------+

Another Example

Let’s switch to another output mode and re-run the same query.

Change output mode:

.mode json

Re-run the same query:

SELECT * FROM t1;

Output:

[{"c1":1,"c2":"Ashbor"},
{"c1":2,"c2":"Bromley"},
{"c1":3,"c2":"Calchum"}]

This time the query results are output as a JSON document.