DuckDB CLI Advanced

Learning Focus

Use this lesson to become a DuckDB CLI power user with scripting, automation, and advanced formatting.

Running SQL Files

# Run a SQL file and exit
duckdb mydb.db < queries.sql

# Run via -c flag
duckdb mydb.db -c "SELECT COUNT(*) FROM employees;"

# Read a .sql file from within CLI
.read queries/analysis.sql

Output Modes

.mode table      -- default aligned table
.mode csv        -- CSV output
.mode json       -- JSON output
.mode line       -- One value per line
.mode markdown   -- Markdown table
.mode latex      -- LaTeX table

Redirecting Output

-- Write query output to a file
.output results.csv
.mode csv
SELECT * FROM employees;
.output  -- reset to stdout

Shell Integration

# DuckDB as part of a pipeline
duckdb mydb.db -csv -c "SELECT name, salary FROM employees" | sort -t',' -k2 -n

# Run DuckDB and pipe result to jq
duckdb mydb.db -json -c "SELECT * FROM employees LIMIT 3" | jq '.[] | .name'

# Use in a cronjob
0 6 * * * duckdb /data/prod.db < /scripts/daily_report.sql > /reports/$(date +%Y%m%d).txt

Variables in DuckDB CLI

-- Set a DuckDB variable
SET variable_name = 'value';
SET min_salary = 80000;

-- Use in a query
SELECT * FROM employees WHERE salary > $min_salary;

-- Unset
RESET min_salary;

Macros (Named SQL Shortcuts)

-- Create a parameterized macro
CREATE MACRO dept_avg_salary(dept_name) AS (
    SELECT AVG(salary) FROM employees WHERE department = dept_name
);

-- Use the macro
SELECT dept_avg_salary('Engineering');

-- Macro as a table function
CREATE MACRO top_earners(n) AS TABLE
    SELECT name, salary FROM employees ORDER BY salary DESC LIMIT n;

SELECT * FROM top_earners(3);

Useful CLI Settings

.timer on              -- show query execution time
.nullvalue NULL        -- show NULL as 'NULL' instead of blank
.headers on            -- show column headers (default: on)
.separator ","         -- set output separator
.width 20 30 10        -- set column widths
PRAGMA enable_progress_bar;  -- show progress for long queries

CLI Startup File

Create ~/.duckdbrc to auto-run commands on every DuckDB CLI start:

-- ~/.duckdbrc
.timer on
.mode table
PRAGMA threads = 8;
PRAGMA memory_limit = '8GB';
LOAD httpfs;

Common Pitfalls

Pitfall	Consequence	Prevention
Forgetting .mode before .output	Wrong file format	Always set mode before redirecting output
No semicolon in -c flag	Syntax error	Always end SQL with ; in -c
Long queries without progress bar	Appears frozen	PRAGMA enable_progress_bar; in startup

Quick Reference

duckdb mydb.db                          # interactive
duckdb mydb.db -c "SELECT 1;"          # one-shot
duckdb mydb.db < file.sql              # run file
duckdb mydb.db -csv -c "SELECT ..." > out.csv

.timer on
.mode csv
.output file.csv
.read file.sql
CREATE MACRO name(param) AS (...);

What's Next

• Next: Run Analytics in DuckDB - Apply everything to real analytical workflows.
• Module Overview - Return to this module index.