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 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.