DuckDB CLI Basics

Learning Focus

Use this lesson to become productive with the DuckDB CLI — your primary tool for interactive SQL work.

Launching the DuckDB CLI

# In-memory session (nothing persisted after exit)
duckdb

# Persistent database file
duckdb mydb.db

# Run a single query and exit
duckdb mydb.db "SELECT COUNT(*) FROM employees;"

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

Essential CLI Commands

Command	Description
.help	Show all dot commands
.tables	List all tables
.schema	Show CREATE statements for all tables
.schema tablename	Show CREATE for a specific table
.mode table	Switch output to table format
.mode csv	Switch output to CSV format
.output file.csv	Redirect output to a file
.quit or .exit	Exit the CLI
.read file.sql	Execute a SQL file

Navigating the CLI

-- List all tables
SHOW TABLES;

-- Describe a table's columns and types
DESCRIBE employees;

-- Get database and schema info
PRAGMA database_list;

-- Show DuckDB version
SELECT version();

-- Show current settings
PRAGMA all_pragmas;

Running Your First Queries

-- Create a table
CREATE TABLE employees (
    id      INTEGER PRIMARY KEY,
    name    VARCHAR,
    dept    VARCHAR,
    salary  DECIMAL(10,2),
    hired   DATE
);

-- Insert rows
INSERT INTO employees VALUES
    (1, 'Alice',   'Engineering', 95000.00, '2021-03-15'),
    (2, 'Bob',     'Marketing',   72000.00, '2019-06-01'),
    (3, 'Charlie', 'Engineering', 88000.00, '2022-01-20'),
    (4, 'Diana',   'HR',          65000.00, '2020-09-10');

-- Query data
SELECT * FROM employees;
SELECT dept, AVG(salary) AS avg_salary FROM employees GROUP BY dept;

Output Formatting

-- Default: table format
SELECT name, salary FROM employees;

-- Pipe-separated
.mode line
SELECT name, salary FROM employees;

-- Export query to CSV
.output results.csv
SELECT * FROM employees;
.output          -- reset to stdout

Reading Files Directly from CLI

-- Read a CSV without importing
SELECT * FROM read_csv_auto('employees.csv') LIMIT 5;

-- Read a Parquet file
SELECT * FROM 'data.parquet' LIMIT 10;

-- Read JSON
SELECT * FROM read_json_auto('records.json') LIMIT 5;

CLI Configuration Tips

# Enable multi-line editing
.prompt '> '

# Set null display
.nullvalue NULL

# Enable timing for each query
.timer on

Common Pitfalls

Pitfall	Consequence	Prevention
Forgetting to use a .db file	All work lost after exit	Always use duckdb mydb.db for persistent work
Using MySQL-style backtick quoting	Syntax error in DuckDB	Use double quotes "column" for identifiers
Mixing ; and dot commands	CLI confusion	Dot commands (.tables) have no semicolon; SQL does

Quick Reference

duckdb          # in-memory
duckdb mydb.db  # persistent

# Inside CLI:
SHOW TABLES;
DESCRIBE tablename;
SELECT version();
.quit

What's Next

• Next: DuckDB Data Types - Learn the full type system available in DuckDB.
• Module Overview - Return to this module index.