Top DuckDB CLI Commands That You Should Know

Hassan Abedi
6 min readSep 27, 2024

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 CLI (the file is named duckdb on GNU/Linux systems) Started in a Terminal Emulator

After 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;
Using .mode to Change the Output Format of the Queries

2. 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
Using .timer to Measure Query Execution Time

3. 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
Using .schema to Display Table Structure

4. 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;
Using .read to Execute Queries from a File

5. 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;
Using .once to Redirect Output to a File

6. 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
Using .shell to Execute Shell Commands

7. 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;
Using .promp to Customize the Prompt Text

8. 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
Using .changes to Display the Number of Affected Rows

9. 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
Using .indexes to Display the Index Names

10. 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;
Using .nullvalue to Customize How NULL Values are Displayed

11. 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
Using .cd to Change the Working Directory

Conclusion

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.

Sign up to discover human stories that deepen your understanding of the world.

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

No responses yet

Write a response