Installation

The DuckDB CLI (Command Line Interface) is a single, dependency-free executable. It is precompiled for Windows, Mac, and Linux for both the stable version and for nightly builds produced by GitHub Actions. Please see the installation page under the CLI tab for download links.

The DuckDB CLI is based on the SQLite command line shell, so CLI-client-specific functionality is similar to what is described in the SQLite documentation (although DuckDB's SQL syntax follows PostgreSQL conventions with a few exceptions).

DuckDB has a tldr page, which summarizes the most common uses of the CLI client. If you have tldr installed, you can display it by running tldr duckdb.

Getting Started

Once the CLI executable has been downloaded, unzip it and save it to any directory. Navigate to that directory in a terminal and enter the command duckdb to run the executable. If in a PowerShell or POSIX shell environment, use the command ./duckdb instead.

Usage

The typical usage of the duckdb command is the following:

duckdb [OPTIONS] [FILENAME]

Options

The [OPTIONS] part encodes arguments for the CLI client. Common options include:

• -csv: sets the output mode to CSV
• -json: sets the output mode to JSON
• -readonly: open the database in read-only mode (see concurrency in DuckDB)

For a full list of options, see the command line arguments page.

In-Memory vs. Persistent Database

When no [FILENAME] argument is provided, the DuckDB CLI will open a temporary in-memory database. You will see DuckDB's version number, the information on the connection and a prompt starting with a D.

duckdb

v1.1.3 19864453f7
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D

To open or create a persistent database, simply include a path as a command line argument:

duckdb my_database.duckdb

Running SQL Statements in the CLI

Once the CLI has been opened, enter a SQL statement followed by a semicolon, then hit enter and it will be executed. Results will be displayed in a table in the terminal. If a semicolon is omitted, hitting enter will allow for multi-line SQL statements to be entered.

SELECT 'quack' AS my_column;

The CLI supports all of DuckDB's rich SQL syntax including SELECT, CREATE, and ALTER statements.

Editor Features

The CLI supports autocompletion, and has sophisticated editor features and syntax highlighting on certain platforms.

Exiting the CLI

To exit the CLI, press Ctrl+D if your platform supports it. Otherwise, press Ctrl+C or use the .exit command. If used a persistent database, DuckDB will automatically checkpoint (save the latest edits to disk) and close. This will remove the .wal file (the write-ahead log) and consolidate all of your data into the single-file database.

Dot Commands

In addition to SQL syntax, special dot commands may be entered into the CLI client. To use one of these commands, begin the line with a period (.) immediately followed by the name of the command you wish to execute. Additional arguments to the command are entered, space separated, after the command. If an argument must contain a space, either single or double quotes may be used to wrap that parameter. Dot commands must be entered on a single line, and no whitespace may occur before the period. No semicolon is required at the end of the line.

Frequently-used configurations can be stored in the file ~/.duckdbrc, which will be loaded when starting the CLI client. See the Configuring the CLI section below for further information on these options.

Below, we summarize a few important dot commands. To see all available commands, see the dot commands page or use the .help command.

Opening Database Files

In addition to connecting to a database when opening the CLI, a new database connection can be made by using the .open command. If no additional parameters are supplied, a new in-memory database connection is created. This database will not be persisted when the CLI connection is closed.

.open

The .open command optionally accepts several options, but the final parameter can be used to indicate a path to a persistent database (or where one should be created). The special string :memory: can also be used to open a temporary in-memory database.

.open persistent.duckdb

Warning .open closes the current database. To keep the current database, while adding a new database, use the ATTACH statement.

One important option accepted by .open is the --readonly flag. This disallows any editing of the database. To open in read only mode, the database must already exist. This also means that a new in-memory database can't be opened in read only mode since in-memory databases are created upon connection.

.open --readonly preexisting.duckdb

Output Formats

The .mode dot command may be used to change the appearance of the tables returned in the terminal output. These include the default duckbox mode, csv and json mode for ingestion by other tools, markdown and latex for documents, and insert mode for generating SQL statements.

Writing Results to a File

By default, the DuckDB CLI sends results to the terminal's standard output. However, this can be modified using either the .output or .once commands. For details, see the documentation for the output dot command.

Reading SQL from a File

The DuckDB CLI can read both SQL commands and dot commands from an external file instead of the terminal using the .read command. This allows for a number of commands to be run in sequence and allows command sequences to be saved and reused.

The .read command requires only one argument: the path to the file containing the SQL and/or commands to execute. After running the commands in the file, control will revert back to the terminal. Output from the execution of that file is governed by the same .output and .once commands that have been discussed previously. This allows the output to be displayed back to the terminal, as in the first example below, or out to another file, as in the second example.

In this example, the file select_example.sql is located in the same directory as duckdb.exe and contains the following SQL statement:

SELECT *
FROM generate_series(5);

To execute it from the CLI, the .read command is used.

.read select_example.sql

The output below is returned to the terminal by default. The formatting of the table can be adjusted using the .output or .once commands.

| generate_series |
|----------------:|
| 0               |
| 1               |
| 2               |
| 3               |
| 4               |
| 5               |

Multiple commands, including both SQL and dot commands, can also be run in a single .read command. In this example, the file write_markdown_to_file.sql is located in the same directory as duckdb.exe and contains the following commands:

.mode markdown
.output series.md
SELECT *
FROM generate_series(5);

To execute it from the CLI, the .read command is used as before.

.read write_markdown_to_file.sql

In this case, no output is returned to the terminal. Instead, the file series.md is created (or replaced if it already existed) with the markdown-formatted results shown here:

| generate_series |
|----------------:|
| 0               |
| 1               |
| 2               |
| 3               |
| 4               |
| 5               |

Configuring the CLI

Several dot commands can be used to configure the CLI. On startup, the CLI reads and executes all commands in the file ~/.duckdbrc, including dot commands and SQL statements. This allows you to store the configuration state of the CLI. You may also point to a different initialization file using the -init.

Setting a Custom Prompt

As an example, a file in the same directory as the DuckDB CLI named prompt.sql will change the DuckDB prompt to be a duck head and run a SQL statement. Note that the duck head is built with Unicode characters and does not work in all terminal environments (e.g., in Windows, unless running with WSL and using the Windows Terminal).

.prompt '⚫◗ '

To invoke that file on initialization, use this command:

duckdb -init prompt.sql

This outputs:

-- Loading resources from prompt.sql
v⟨version⟩ ⟨git hash⟩
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
⚫◗

Non-Interactive Usage

To read/process a file and exit immediately, pipe the file contents in to duckdb:

duckdb < select_example.sql

To execute a command with SQL text passed in directly from the command line, call duckdb with two arguments: the database location (or :memory:), and a string with the SQL statement to execute.

duckdb :memory: "SELECT 42 AS the_answer"

Loading Extensions

To load extensions, use DuckDB's SQL INSTALL and LOAD commands as you would other SQL statements.

INSTALL fts;
LOAD fts;

For details, see the Extension docs.

Reading from stdin and Writing to stdout

When in a Unix environment, it can be useful to pipe data between multiple commands. DuckDB is able to read data from stdin as well as write to stdout using the file location of stdin (/dev/stdin) and stdout (/dev/stdout) within SQL commands, as pipes act very similarly to file handles.

This command will create an example CSV:

COPY (SELECT 42 AS woot UNION ALL SELECT 43 AS woot) TO 'test.csv' (HEADER);

First, read a file and pipe it to the duckdb CLI executable. As arguments to the DuckDB CLI, pass in the location of the database to open, in this case, an in-memory database, and a SQL command that utilizes /dev/stdin as a file location.

cat test.csv | duckdb -c "SELECT * FROM read_csv('/dev/stdin')"

To write back to stdout, the copy command can be used with the /dev/stdout file location.

cat test.csv | \
    duckdb -c "COPY (SELECT * FROM read_csv('/dev/stdin')) TO '/dev/stdout' WITH (FORMAT 'csv', HEADER)"

woot
42
43

Reading Environment Variables

The getenv function can read environment variables.

Examples

To retrieve the home directory's path from the HOME environment variable, use:

SELECT getenv('HOME') AS home;

The output of the getenv function can be used to set configuration options. For example, to set the NULL order based on the environment variable DEFAULT_NULL_ORDER, use:

SET default_null_order = getenv('DEFAULT_NULL_ORDER');

Restrictions for Reading Environment Variables

The getenv function can only be run when the enable_external_access is set to true (the default setting). It is only available in the CLI client and is not supported in other DuckDB clients.

Prepared Statements

The DuckDB CLI supports executing prepared statements in addition to regular SELECT statements. To create and execute a prepared statement in the CLI client, use the PREPARE clause and the EXECUTE statement.