Top DuckDB CLI Commands That You Should Know

DuckDB is a fast in-memory analytical database system. It is designed to be used as an embedded database, which means it can be easily integrated into applications and run within the same process as the application code. However, a command-line version of DuckDB is also available.
In this post, we will explore a few useful commands (also called dot commands
) of the command-line version of DuckDB, which we refer to as DuckDB CLI, in the remainder of this post.
Getting Started
Before we start, make sure you have already downloaded the DuckDB CLI (for your OS from here) and that it is ready to run.

duckdb
on GNU/Linux systems) Started in a Terminal EmulatorAfter downloading the DuckDB CLI, we need to create a toy table that we will use to demonstrate the commands and their functionalities. The table will be called employees
and will have the following schema:
-- Create the `employees` table
create table employees
(
id integer,
name varchar,
department varchar,
salary decimal
);
-- Insert some records into the table
insert into employees
values (1, 'Alice', 'Engineering', 100000),
(2, 'Bob', 'Engineering', 90000),
(3, 'Charlie', 'Sales', 80000),
(4, 'David', 'Marketing', 70000),
(5, 'Eve', 'Sales', 60000);
1. Using .mode
to Change the Output Format
The .mode
command allows us to change the output format of the query results. By default, the output format is duckbox
, which displays the results in a tabular format with extensive aesthetic formatting. However, we can change the output format to many other formats, including: csv
, json
, latex
, or even markdown
. See DuckDB's documentation on supported output formats for the full list.
Example output in different modes:
-- Show the current output mode
.mode
-- Show the result in LaTeX format
.mode latex
select * from employees;
-- Show the result in CSV format
.mode csv
select * from employees;
-- Show the result in JSON format
.mode json
select * from employees;

.mode
to Change the Output Format of the Queries2. Using .timer
to Measure Query Execution Time
The .timer
command lets us measure the execution time of queries. In other words, when we enable the timer, DuckDB will display the time taken to execute each query.
Example usage of .timer
:
-- Turn the timer on
.timer on
-- Run a query
select * from employees;
-- Turn the timer off
.timer off

.timer
to Measure Query Execution Time3. Using .schema
to Display Table Structure
The .schema
command displays the structure or schema of a table. The displayed information includes column names, column types, and any constraints (like primary keys, foreign keys, or indexes) associated with the table.
Example usage of .schema
:
-- Create an index on the `department` column
create index idx_employees_department on employees (department);
-- Show the schema for the `employees` table
.schema employees

.schema
to Display Table Structure4. Using .read
to Execute Queries from a File
The .read
command lets us execute queries (SQL code) stored in a file. This is a very useful command when we have a large number of queries that we want to run in sequence or when we want to reuse queries across multiple sessions.
Example usage of .read
:
-- Execute queries from the `queries.sql` file
.read queries.sql
Example content of queries.sql
:
select * from employees;

.read
to Execute Queries from a File5. Using .once
to Redirect Output to a File
The .once
command allows us to redirect the output of the next query to a file. This can be useful when we want to save the results of a query to a file for later use.
Example usage of .once
:
-- Redirect the output of the next query to `output.txt`
.once output.txt
-- The (next) query
select * from employees;

.once
to Redirect Output to a File6. Using .shell
to Execute Shell Commands
The .shell
command lets us execute shell commands from the DuckDB CLI.
Example usage of .shell
:
-- List the files in the current directory
.shell ls -l

.shell
to Execute Shell Commands7. Using .promp
to Customize the Prompt Text
The .prompt
command allows us to customize the prompt in the DuckDB CLI. For example, we can change the prompt to display the database name or any other custom text.
Example usage of .prompt
:
-- Change the prompt to `EmployeeDB>` (the database name)
.prompt "EmployeeDB> "
select * from employees;

.promp to
Customize the Prompt Text8. Using .changes
to Display the Number of Affected Rows
The .changes
command allows us to display the number of rows affected by a query with side effects. This is useful when you want to know how many rows were inserted, updated, or deleted by a query.
Example usage of .changes
:
-- Turn on tracking changes
.changes on
-- Insert a new row into the `employees` table
insert into employees values (6, 'Frank', 'Engineering', 95000);
-- Turn off tracking changes
.changes off

.changes
to Display the Number of Affected Rows9. Using .indexes
to Display the Index Names
The .indexes
command lets us display the names of the indexes on a table.
Example usage of .indexes
:
-- Create an index on the `department` column
create index idx_employees_department on employees (department);
-- Get the names of the indexes on the `employees` table
.indexes employees

.indexes
to Display the Index Names10. Using .nullvalue
to Customize How NULL Values are Displayed
The .nullvalue
command allows us to customize how NULL values are displayed in DuckDB CLI. By default, NULL values are displayed as an empty string. However, using .nullvalue
, we can change this to any other string.
Example usage of .nullvalue
:
-- Show NULL values as "N/A"
.nullvalue "N/A"
-- Insert a row with a NULL value in the `department` column
insert into employees (id, name, department, salary) values (7, 'Grace', null, 85000);
-- Show the contents of the `employees` table
select * from employees;

.nullvalue
to Customize How NULL Values are Displayed11. Using .cd
to Change the Working Directory
The .cd
command allows us to change the working directory to another directory.
Example usage of .cd
:
-- Change the working directory to /tmp
.cd /tmp

.cd
to Change the Working DirectoryConclusion
In this post, we explored some useful DuckDB commands that can help us work more efficiently with the command-line version of DuckDB. These commands can help us customize the output format, measure query execution time, display table schemas, execute queries from a file, etc. For more information on the DuckDB CLI and its full feature list, please check out DuckDB’s documentation.