Meta Commands

Dot commands for controlling the DuckDB CLI

Meta commands (also called dot commands) are special commands that start with a dot (.) and control the behavior of the DuckDB CLI.

Getting Help

.help

Display help information for all commands:

.help

Get help for a specific command:

.help import

Show all commands with extended information:

.help --all

Database Operations

.open

Open a database file:

.open mydata.db

Open in read-only mode:

.open --readonly mydata.db

Create a new database (deleting existing file):

.open --new mydata.db

Open using a SQL query result:

.open --sql "SELECT 'my_' || current_date() || '.db'"

.databases

List all attached databases:

.databases

Output:

main: /path/to/mydata.db

.tables

List all tables:

.tables

List tables matching a pattern:

.tables user%

.schema

Show CREATE statements for all tables:

.schema

Show schema for specific table:

.schema employees

Pretty-print schema:

.schema --indent employees

.indexes

Show all indexes:

.indexes

Show indexes for a specific table:

.indexes employees

Import and Export

.import

Import data from a file into a table:

.import data.csv users

Import CSV with options:

.import --csv data.csv users

Import Parquet:

.import --parquet data.parquet users

Import JSON:

.import --json data.json users

Pass parameters to the reader function:

.import --csv --header true --delimiter ";" data.csv users

Notes:

• If the table doesn’t exist, it will be created automatically
• If no format is specified, it’s inferred from the file extension
• Additional parameters can be passed with --parameter value syntax

.dump

Dump database schema and data as SQL:

.dump

Dump specific tables:

.dump employees departments

Allow unescaped newlines in output:

.dump --newlines

Output Control

.mode

Set output mode:

.mode duckbox

Available modes:

• ascii - Columns/rows delimited by ASCII separators (0x1F and 0x1E)
• box - Tables using Unicode box-drawing characters
• column - Left-aligned columns (see .width)
• csv - Comma-separated values
• duckbox - Tables with extensive formatting features (default)
• html - HTML <table> format
• insert - SQL INSERT statements
• json - Results as JSON array
• jsonlines - Results as newline-delimited JSON
• latex - LaTeX tabular environment
• line - One value per line
• list - Values delimited by |
• markdown - Markdown table format
• quote - Escape values as for SQL
• table - ASCII-art table
• tabs - Tab-separated values
• tcl - TCL list elements
• trash - No output (useful for benchmarking)

For INSERT mode, specify table name:

.mode insert employees

.headers

Toggle display of column headers:

.headers on

.headers off

.separator

Set column separator:

.separator ","

Set both column and row separator:

.separator "," "\n"

.nullvalue

Set the string to display for NULL values:

.nullvalue NULL

.nullvalue "(null)"

.width

Set column widths for column mode:

.width 10 20 15

Negative values right-justify:

.width 10 -20 15

.maxrows

Set maximum number of rows to display (duckbox mode only):

.maxrows 100

Set max rows and max analyze rows:

.maxrows 100 1000

.maxwidth

Set maximum display width in characters (duckbox mode only):

.maxwidth 120

Set to 0 to use terminal width:

.maxwidth 0

File Operations

.read

Execute SQL from a file:

.read script.sql

.output

Redirect output to a file:

.output results.txt
SELECT * FROM users;
.output stdout

Output to a pipe:

.output |less
SELECT * FROM large_table;
.output stdout

Options:

• --bom - Add UTF-8 byte-order mark
• -e - Send to system text editor
• -x - Send as CSV to spreadsheet

.once

Redirect output for next command only:

.once results.txt
SELECT * FROM users;

.excel

Display next query result in spreadsheet:

.excel
SELECT * FROM sales_data;

Display and Formatting

.echo

Toggle command echo:

.echo on

When enabled, commands are printed before execution.

.timer

Toggle SQL timer:

.timer on

Displays execution time after each query:

Run Time (s): real 0.001 user 0.000 sys 0.000

.changes

Show number of rows changed by SQL:

.changes on

.bail

Stop after hitting an error:

.bail on

.print

Print a literal string:

.print "Processing data..."

Rendering and Highlighting

.highlight

Toggle syntax highlighting:

.highlight on

.highlight_mode

Set highlighting mode:

.highlight_mode dark

Modes:

• auto - Automatically detect terminal colors
• dark - Dark mode colors
• light - Light mode colors
• mixed - Mixed mode

.highlight_errors

Toggle error highlighting:

.highlight_errors on

.highlight_results

Toggle result highlighting:

.highlight_results on

.highlight_colors

Configure highlighting colors:

.highlight_colors keyword blue
.highlight_colors constant green
.highlight_colors comment gray

.display_colors

Display all available terminal colors:

.display_colors

With formatting:

.display_colors bold
.display_colors underline

Number Formatting (duckbox mode)

.large_number_rendering

Configure readable rendering of large numbers:

.large_number_rendering all

Modes:

• all - Format all large numbers
• footer - Show formatted numbers in footer only
• off - Disable formatting

.decimal_sep

Set decimal separator:

.decimal_sep ","

.thousand_sep

Set thousands separator:

.thousand_sep "_"

Example with separators:

.thousand_sep "_"
.decimal_sep "."
SELECT 1234567.89;
-- Displays as: 1_234_567.89

Pager Control

.pager

Configure pager for output:

-- Turn pager on
.pager on

-- Turn pager off
.pager off

-- Automatic mode (default)
.pager automatic

Set custom pager command:

.pager less -SRX

Set row threshold for automatic mode:

.pager set_row_threshold 100

Prompt Customization

.prompt

Customize prompts:

.prompt "duckdb> " "   ...> "

Arguments:

1. Main prompt
2. Continuation prompt
3. Selected continuation prompt (optional)
4. Scroll up prompt (optional)
5. Scroll down prompt (optional)

Progress Bar

.progress_bar

Configure progress bar display:

-- Clear all components
.progress_bar --clear

-- Add a component
.progress_bar --add percentage

System Commands

.shell (or .system)

Run system command:

.shell ls -la

.system pwd

Note: Not available in safe mode.

.cd

Change working directory:

.cd /path/to/directory

Note: Not available in safe mode.

Information Display

.show

Show current configuration:

.show

Displays all current settings including output mode, separators, and other options.

.version

Show DuckDB version:

.version

Output:

DuckDB v1.2.0 (dev) abc123

.about

Show information about DuckDB:

.about

.last

Render the last result without truncation:

.last

Useful when a previous query result was truncated.

Special Features

.columns and .rows

Switch between column-wise and row-wise rendering:

.columns  -- Column-wise rendering
.rows     -- Row-wise rendering (default)

.log

Turn logging on or off:

.log output.log
.log off

Log to stderr or stdout:

.log stderr
.log stdout

.edit

Open external text editor to edit a query:

.edit

The editor is determined by environment variables:

1. DUCKDB_EDITOR
2. EDITOR
3. VISUAL
4. Default: vi

Alias: \e

Session Control

.quit or .exit

Exit the DuckDB CLI:

.quit

.exit

Exit with specific return code:

.exit 0

Safe Mode

Safe mode disables potentially dangerous operations:

.safe_mode

In safe mode, the following commands are disabled:

• .import
• .open
• .cd
• .shell / .system
• .log

Start the CLI in safe mode:

duckdb -safe mydata.db