Getting Started with DuckDB Command Line Interface

Posted on by Ian

DuckDB is a high-performance in-process analytical database management system, often referred to as the “SQLite for analytics.” Its Command Line Interface (CLI) is a useful tool for executing SQL queries, managing databases, and exploring DuckDB’s features.

This article provides a comprehensive guide to the basics of using DuckDB’s CLI, helping beginners and advanced users alike understand how to leverage it effectively.

Setting Up DuckDB

Installation

To get started with DuckDB, you need to install it. DuckDB supports multiple platforms, including Windows, macOS, and Linux. Installation is straightforward, and in many cases you’ll have the option of installing it via package manager or by a direct link.

Using a package manager like brew on macOS or winget on Windows provides a quick and easy installation. Here’s how to do it with each of these:

  • macOS: brew install duckdb
  • Windows: winget install DuckDB.cli

See the DuckDB installation page for all options.

Verifying the Installation

After installation, verify that DuckDB is successfully installed by typing the following command in your terminal:

duckdb --version

You should see the version number of DuckDB displayed.

Starting the DuckDB CLI

To launch the CLI, open your terminal and type:

duckdb

You will see a prompt that looks like this:

D

This indicates that DuckDB is ready to accept commands.

Basic Commands in the CLI

Creating a Database

To create a new database, provide a filename when starting DuckDB:

duckdb my_database.duckdb

This creates a new SQLite-compatible DuckDB file named my_database.duckdb in the current directory. You can use any extension you like for the database. In this case I used .duckdb, but you could use .db or even .ddb for example.

If you don’t specify a filename, DuckDB creates an in-memory database that is discarded upon exiting.

Connecting to a Database

If you have an existing DuckDB file, you can connect to it by specifying its filename:

duckdb existing_database.duckdb

Attaching a Database

If DuckDB is already running, you can use the ATTACH command to attach an existing database:

ATTACH existing_database.duckdb AS MyDB

If you don’t provide the alias, then it will use the file name (in this case existing_database) as the alias.

Detaching a Database

You can detach the database with the DETACH command:

DETACH MyDB

Here, we use the alias for the database.

Opening a Database with the .open Dot Command

The CLI includes a bunch of dot commands that allow us to do all sorts of things, such as opening a database. Here’s an example of using the .open command to open a database:

.open existing_database.duckdb

You run this from within the DuckDB CLI.

Exploring CLI Features

SQL Query Execution

DuckDB’s CLI supports SQL queries directly. Here’s an example of creating and querying a table:

CREATE TABLE students (
    id INTEGER, 
    name VARCHAR, 
    age INTEGER
    );
    
INSERT INTO students VALUES 
    (1, 'Bresha', 21), 
    (2, 'Antelape', 22);

SELECT * FROM students;

Output:

+----+----------+-----+
| id |   name   | age |
+----+----------+-----+
| 1  | Bresha   | 21  |
| 2  | Antelape | 22  |
+----+----------+-----+

In this case, my terminal used the table output mode. The default mode in the CLI is duckbox, but this can easily be changed with the .mode command.

The .mode Dot Command

You can use the .mode command to change the output of the results, as well as checking your existing settings.

Mode Help

To check the syntax for the .mode command and to see a list of the output modes available, use the following:

.help mode

Output:

.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns.  (See .width)
     duckbox   Tables with extensive features
     html      HTML <table> code
     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     jsonlines Results in a NDJSON
     latex     LaTeX tabular environment code
     line      One value per line
     list      Values delimited by "|"
     markdown  Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements
     trash     No output

Check Existing Output Mode

To check your existing output mode, simply type .mode from within the DuckDB CLI:

.mode

Output:

current output mode: table

In my case I had already changed my mode from duckbox to table, and so the output reflects that.

Change Output Mode

To change the mode, just append .mode with the mode you want:

.mode csv

Now let’s run the previous query again to see how the mode affects the output:

SELECT * FROM students;

Output:

id,name,age
1,Bresha,21
2,Antelape,22

As expected, it’s now output in CSV format.

Viewing Tables and Schema

To list all tables in the current database, use:

.tables

To view the CREATE statement/s of an object matching a given pattern, use the .schema command:

.schema students

Alternatively, you can use the DESCRIBE command (or its aliases DESC and SHOW) to return the table’s schema:

DESCRIBE students;

Scripting and Automation

You can run a series of SQL commands stored in a script file using the .read command:

.read script.sql

This is particularly useful for batch processing.

Show Various Settings

You can use the .show command to output the current values for various settings:

.show

Output:

        echo: off
         eqp: off
     explain: auto
     headers: on
        mode: csv
   nullvalue: ""
      output: stdout
colseparator: ","
rowseparator: "\r\n"
       stats: off
       width: 0 0 0 
    filename: :memory:

Exiting the CLI

To exit the CLI, use the .exit command or press Ctrl+D.

Tips for Efficient CLI Usage

  • Command History: Use the up and down arrow keys to navigate through previously entered commands.
  • Autocomplete: Use the Tab key for command and table name autocompletion.
  • Help Command: Type .help to display a list of available CLI commands and their descriptions.