Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 38 ยท Concepts and CLI

A Tour of the DuckDB Command Line

Learn the DuckDB command-line shell: dot commands, output modes, reading scripts, timing queries, exporting results, command-line flags and handy shell habits.

The DuckDB shell is more than a place to type SQL. It has a small set of dot commands for shell tasks, several output modes for showing results, and flags that make it a good citizen in shell scripts. If you work with DuckDB daily, a quarter of an hour spent here will save you hours.

Everything about the shell program itself is read-only in this lesson, because the browser cannot run a terminal. The "Stand-in" examples run the SQL part on SQLite.

SQL versus dot commands

Anything that ends in a semicolon is SQL and goes to the database engine. Anything starting with a dot is handled by the shell and needs no semicolon:

TEXT
D SELECT 1 + 1;        -- SQL
D .tables              -- shell command

The most useful dot commands:

Command What it does
.help Lists all dot commands
.tables Lists the tables and views
.schema Shows the CREATE statements
.databases Lists attached databases
.open file.duckdb Opens another database file
.read script.sql Runs the SQL in a file
.mode <name> Changes the output format
.output file.txt Sends results to a file; .output alone returns to the screen
.timer on Prints the time each query took
.maxrows N Limits the rows printed
.shell cmd Runs an operating-system command
.exit or .quit Leaves the shell

Tab completion works for SQL keywords, table names, column names and file names, and the up-arrow recalls earlier statements, which the shell saves to a history file between sessions.

Output modes

The mode decides how results print. The default draws a table with box characters and, for long results, shows the first and last rows with an ellipsis in the middle so that your terminal is not flooded.

TEXT
D .mode markdown
D SELECT 'duck' AS animal, 3 AS legs_missing;
| animal | legs_missing |
|--------|-------------:|
| duck   | 3            |

Common modes:

  • duckbox (default): table with box drawing and truncation.
  • box, table: complete tables, markdown: for pasting into documents.
  • csv, tabs: for piping into other programs.
  • json, jsonlines: for programs that speak JSON.
  • line: one column = value line per field, perfect for wide rows.
  • list, ascii, html, latex, insert: other formats.

You can pick a mode when you start the shell too, using a flag such as -csv, -json or -markdown:

Terminal
duckdb -csv -c "SELECT * FROM 'sales.parquet' LIMIT 3" > sample.csv

Scripts and shell pipelines

Put your SQL in a file and run it three ways:

Terminal
duckdb sales.duckdb < report.sql      # redirect a file
duckdb sales.duckdb -f report.sql     # the -f flag
duckdb sales.duckdb -c ".read report.sql"

-c can be repeated and the commands run in order, and -init file.sql runs a file before anything else. DuckDB also reads a file called .duckdbrc in your home folder at startup, so you can put settings you always want there, such as .timer on or .mode box.

DuckDB can read from standard input and write to standard output, which makes it a tool in a Unix pipeline:

Terminal
cat events.csv | duckdb -c "SELECT count(*) FROM read_csv('/dev/stdin')"
duckdb -csv -c "COPY (SELECT 1 AS a) TO '/dev/stdout' (FORMAT csv)"

Timing and row limits

Turn on .timer on and every statement prints how long it took, split into real, user and system time. That is the quickest way to compare two versions of a query. The long-running example below is a stand-in; try it, then imagine the shell printing a timing line after it:

SQL Stand-in: generate and aggregate many rows

Press Run (or Ctrl + Enter) to see the result here.

In DuckDB the same job is one short line, because range(N) is a built-in table function (read-only):

SQL
-- DuckDB (read-only example)
SELECT count(*) AS rows_made, sum(i % 7) AS checksum, max(i) AS biggest
FROM range(1, 1000001) AS t(i);

DuckDB also prints a progress bar for slow queries in the terminal; you can turn it off with SET enable_progress_bar = false;.

Getting help on the engine's settings

Not everything is a dot command. DuckDB has settings that you change with SET and read back with current_setting() or the duckdb_settings() table:

SQL
-- DuckDB (read-only example)
SET threads = 4;
SET memory_limit = '2GB';
SELECT name, value, description FROM duckdb_settings() WHERE name = 'threads';

A dot command affects the shell (how it looks); a SET statement affects the engine (how it works). You will use the second kind in Extensions and performance.

Listing what exists with SQL

The dot commands .tables and .schema are conveniences; behind them are plain queries you can use anywhere, including from Python. DuckDB has built-in catalog functions like duckdb_tables(), duckdb_columns(), duckdb_views(), duckdb_functions() and duckdb_extensions(). The standard information_schema views work as well. SQLite offers a similar catalog, which this stand-in shows:

SQL Stand-in: list tables and columns with SQL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In DuckDB, the first line becomes SELECT table_name FROM duckdb_tables(); and the second SELECT column_name, data_type FROM duckdb_columns() WHERE table_name = 'products';.

Useful habits

  • Run .mode line when a table is too wide to read.
  • Use .timer on while tuning a query and compare timings.
  • Keep recurring setup in ~/.duckdbrc.
  • Use -csv or -json for scripting rather than parsing the pretty table.
  • Use .read for longer queries and keep them under version control.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How does the shell tell SQL from a shell command?

    Dot commands such as .tables are handled by the shell and need no semicolon; SQL statements end with ;
  2. 2. Which flag makes the CLI print results as comma-separated values?

    Output flags such as -csv, -json and -markdown choose the output mode at startup.
  3. 3. What does .timer on do?

    With the timer on, every statement is followed by real, user and system times.
  4. 4. What is the difference between a dot command and SET?

    The shell handles its own display and file tasks, while SET configures how the engine runs queries.

Summary

  • Dot commands control the shell; SQL statements end with ;.
  • .mode, -csv and -json choose the output format; .output redirects it.
  • Run files with .read, -f, redirection or repeated -c flags.
  • .timer on times statements; SET configures the engine itself.
  • Catalog functions like duckdb_tables() list what exists using ordinary SQL.

Next: Columnar storage and vectorized execution