duckdb

Start an interactive SQL shell

$ duckdb

$ duckdb [path/to/database.db]

$ duckdb -c "[SELECT * FROM table_name]"

$ duckdb -c "[SELECT * FROM 'data.csv']"

$ duckdb -c "[SELECT * FROM 'data.parquet']"

$ duckdb < [path/to/script.sql]

$ duckdb -c "[COPY (SELECT * FROM table) TO 'output.csv' (HEADER)]"

$ duckdb -readonly [database.db]

duckdb [options] [databasefile] [sqlcommands]

DuckDB is an embedded analytical database designed for fast online analytical processing (OLAP) workloads. It can run entirely in-process without a separate server, making it ideal for data analysis and scripting.
A key feature is the ability to query files directly without importing: CSV, Parquet, JSON, and other formats can be used in SQL queries with automatic type detection. This makes DuckDB excellent for exploratory data analysis and ETL tasks.
The database supports standard SQL with analytical extensions including window functions, CTEs, and complex aggregations. It provides high performance through vectorized execution and columnar storage, optimized for aggregation queries over large datasets.
In interactive mode, DuckDB provides a full-featured SQL shell with tab completion, command history, and dot-commands for settings. Results can be output in various formats including tables, JSON, CSV, and Markdown.
DuckDB can be used as a library in Python, R, Java, Node.js, and other languages, or standalone via the CLI. Database files are portable across platforms and versions.

-c, -cmd command

Execute the specified SQL command and exit.

Execute SQL commands from file before starting shell.

Open database in read-only mode.

Don't read from stdin.

Output results in JSON format.

Output results in CSV format.

Output results as ASCII table (default).

Output results with box-drawing characters.

Output results as Markdown table.

Output results in line mode (one value per line).

Set field separator for CSV mode.

Include column headers in output.

Exclude column headers from output.

Allow loading of unsigned extensions.

Print version and exit.

~/.duckdbrc

Initialization file with SQL commands executed on shell startup, used for setting preferences and defaults.

In-memory databases are lost when the process exits. Write operations lock the database file, limiting concurrent write access. Very large datasets may exceed available memory without proper configuration. Some SQL syntax differs slightly from other databases.

DuckDB was created by Mark Raasveldt and Hannes Mühleisen at CWI Amsterdam (the research institute where PostgreSQL originated). Development started around 2018 with the goal of creating an embeddable analytical database akin to "SQLite for analytics." The project gained significant adoption in the data science community starting in 2020.

sqlite3(1), psql(1), clickhouse-client(1), csvq(1)