Overview

In the data analytics world, command-line tools are used extensively. Whether it’s data engineers running batch processing scripts daily or data scientists quickly exploring datasets, the CLI remains the most efficient working interface. However, while DuckDB’s CLI has always been powerful in terms of functionality, its user experience has remained relatively basic — no auto-complete, no syntax highlighting, no pager, and no convenient way to reuse query history.

This all changed fundamentally in DuckDB v1.5 “Variegata”. The DuckDB team undertook a monumental CLI overhaul, migrating the core from SQLite API wrappers to the C++ API, and introduced a wealth of modern terminal interaction features. This article introduces these exciting new features one by one, with runnable code examples.

I. CLI Refactoring Background: From SQLite API to C++ API

Before v1.5, the DuckDB CLI was essentially a thin wrapper around the SQLite C API. This design was reasonable during early development — it allowed rapid implementation of basic functionality using existing API interfaces. But as DuckDB’s capabilities grew, this architecture brought numerous limitations:

• Inability to directly access DuckDB’s unique advanced features
• Global variables causing thread safety issues
• Code that was difficult to maintain and test
• Poor extensibility, making new feature additions challenging

The refactoring in v1.5 (PR #19536) completely removed SQLite API dependencies. The CLI now directly uses DuckDB’s C++ API. This not only solved the above issues but also laid the foundation for all subsequent user experience improvements.

II. Tab Key Auto-Complete: Smooth as zsh

2.1 Basic Auto-Complete

The most thrilling improvement is Tab key auto-complete. In previous versions, entering long SQL statements required manually typing every character, and a single typo meant starting over. Now, DuckDB CLI supports full auto-complete functionality:

$ duckdb
DuckDB 1.5.0
┌──────────────────────────────────────────────────┐
│ DuckDB 1.5.0 Variegata │
│ [Version: 1.5.0 Branch: main] │
└──────────────────────────────────────────────────┘

SELECT * FROM my_table WHERE na[TAB]

Pressing Tab auto-completes to name or other matching column names. This feature covers:

• SQL keywords: SELECT, FROM, WHERE, JOIN, etc.
• Function names: COUNT(), SUM(), COALESCE(), and other built-in functions
• Table and column names: All tables and columns in the current database
• Command names: .tables, .schema, .mode and other shell commands
• Extension names: Installed extension module names

2.2 zsh-Style Smart Completion

v1.5 further optimized the auto-complete behavior to resemble a zsh experience (PR #19805). This means the completion list is intelligently sorted, with common options at the top, supporting partial matching and fuzzy search.

-- Enter se[TAB] auto-completes to SELECT
-- Enter sel[TAB] also completes to SELECT
-- Enter selc[TAB] precisely matches SELECT

2.3 Mouse Cursor Positioning

An easily overlooked but highly practical improvement is mouse cursor positioning (PR #19869). In multi-line editing mode, pressing Ctrl+Q allows you to click anywhere with the mouse to position the cursor — no more using arrow keys to slowly navigate through long SQL statements.

III. Dark/Light Mode: Adapting to Your Environment

3.1 Automatic Theme Detection

DuckDB CLI now supports dark and light display modes, and can automatically detect the terminal’s environment settings (PR #19985). When you switch to a dark terminal, the CLI automatically adjusts its color scheme; switch to a light terminal, and it switches back.

$ duckdb
# If the terminal has a dark theme, CLI uses bright text + dark background
# If the terminal has a light theme, CLI uses dark text + light background

3.2 Rich Color Customization

With the new .display_colors command, users can view and customize all color configurations (PR #19587):

.duckdb> .display_colors
┌──────────────────────────────────────────────────────┐
│ Available Color Schemes │
├──────────┬───────────┬───────────────────────────────┤
│ Group │ Color │ Usage │
├──────────┼───────────┼───────────────────────────────┤
│ Keywords │ Purple │ SQL keywords (SELECT, FROM) │
│ Functions│ Blue │ Built-in functions │
│ Strings │ Green │ String literals │
│ Numbers │ Orange │ Numeric literals │
│ Errors │ Red │ Error messages │
│ Success │ Green │ Success messages │
└──────────┴───────────┴───────────────────────────────┘

The CLI also supports extended 8-bit colors, providing a finer visual experience on terminals with true color support.

3.3 Comparison with Traditional CLIs

IV. The _ Token: Elegantly Reusing Previous Query Results

4.1 Core Concept

One of the most creative features in v1.5 CLI: the result of the last query can be directly referenced via the _ token (PR #19553).

Imagine this scenario: you’re analyzing user data, first pulling a list of active users, then wanting to perform further aggregation on those users. In the traditional way, you’d need to copy-paste the entire query as a subquery. Now you just use _:

-- First query: Get active users
SELECT user_id, name, signup_date 
FROM users 
WHERE last_active > NOW() - INTERVAL '30 days';

-- Second query: Directly reuse the previous result
SELECT _, COUNT(*) as active_count
FROM _
GROUP BY CASE 
 WHEN signup_date < '2025-01-01' THEN 'Long-term'
 ELSE 'New'
END;

4.2 Real-World Application Scenarios

Scenario 1: Interactive Data Exploration

-- Step 1: Get data overview
SELECT COUNT(*) as total_rows FROM sales;

-- Step 2: Further analysis based on step 1
SELECT *, _ as total FROM sales
WHERE amount > (_ * 0.9); -- Rows exceeding 90% of the total

Scenario 2: Recursive Filtering

-- Step 1: Find anomalies
SELECT product_id, AVG(amount) as avg_amount
FROM orders
GROUP BY product_id
HAVING AVG(amount) > 1000;

-- Step 2: Get details of these anomalous products
SELECT o.*, p.category
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.product_id IN _;

4.3 Complementary .last Command

v1.5 also introduces the .last command (PR #20223), which re-renders the previous query’s result. This is especially useful when results get overwritten by subsequent operations:

.duckdb> SELECT COUNT(*) FROM huge_table;
┌─────────┐
│ COUNT() │
├─────────┤
│ 1000000 │
└─────────┘

-- After doing other operations...
.duckdb> SELECT * FROM metadata LIMIT 5;

-- Re-see the previous result
.duckdb> .last
┌─────────┐
│ COUNT() │
├─────────┤
│ 1000000 │
└─────────┘

V. Paging Output: Handling Massive Results Without Screen Overflow

5.1 Automatic Pager

When query results exceed the terminal’s visible area, v1.5 automatically enables a pager (PR #19676). This means you no longer need to worry about screen overflow from too many results, or wasting space with too few.

$ duckdb
.duckdb> SELECT * FROM massive_table LIMIT 100000;
# Results automatically enter paging mode when exceeding one screen
# Use up/down arrows to page, q to quit

The pager’s behavior can be customized, including toggling between auto and manual modes.

5.2 Result Rendering Optimization

Beyond paging, result rendering itself received a comprehensive overhaul. The new BoxRenderer (PR #19721) supports:

• Auto-wrapping for wide values: Overly long field names and content wrap automatically, never overflowing the screen
• Nested type beautification: Complex types like JSON, VARIANT, and arrays are displayed hierarchically
• Adaptive column width: Column widths adjust automatically based on content, balancing readability and compactness

-- Beautiful rendering of nested JSON
.duckdb> SELECT '{"name": "Alice", "age": 30, "hobbies": ["coding", "reading"]}'::JSON;
┌──────────────────────────────────────────────────┐
│ │
│ { │
│ "name": "Alice", │
│ "age": 30, │
│ "hobbies": [ │
│ "coding", │
│ "reading" │
│ ] │
│ } │
│ │
└──────────────────────────────────────────────────┘

VI. Dynamic Prompts and Startup Text

6.1 Dynamic Prompt Support

Traditional database CLIs typically display a fixed sqlite> or postgres=# prompt. DuckDB v1.5 introduces dynamic prompts (PR #19579) that can change based on the current state:

# When connecting to a specific database
.duckdb> .open my_database.db
# The prompt reflects the current connected database

# During multi-line input
.duckdb> SELECT *
 ...> FROM users
 ...> WHERE age > 18;

6.2 Configurable Startup Text

You can customize the welcome message displayed when DuckDB starts (PR #19584):

.duckdb> .startup_text
 ╔══════════════════════════════════╗
 ║ DuckDB Analytics Engine ║
 ║ Connected to: production_db ║
 ║ Version: 1.5.0 ║
 ╚══════════════════════════════════╝

This is particularly helpful for team collaboration when quickly identifying the currently connected environment.

VII. Metadata Rendering for .tables and DESCRIBE

7.1 Schema-Grouped Table Lists

The new .tables command displays table information grouped by database and schema (PR #19798):

.duckdb> .tables
┌──────────────────────────────────────────────────┐
│ Database: main │
├──────────────────────────────────────────────────┤
│ Schema: public │
│ ├── users │
│ ├── orders │
│ └── products │
│ Schema: analytics │
│ ├── daily_metrics │
│ └── user_segments │
├──────────────────────────────────────────────────┤
│ Database: staging │
│ Schema: public │
│ ├── raw_events │
│ └── staging_users │
└──────────────────────────────────────────────────┘

7.2 Structured DESCRIBE Output

DESCRIBE queries now also use the unified metadata rendering engine, providing clear table structure information:

.duckdb> DESCRIBE orders;
┌──────────────┬──────────────┬────────┬──────────┬───────────┐
│ column_name │ data_type │ nullable │ default │ comment │
├──────────────┼──────────────┼────────┼──────────┼───────────┤
│ order_id │ BIGINT │ FALSE │ │ │
│ user_id │ BIGINT │ FALSE │ │ FK->users │
│ amount │ DECIMAL(10,2)│ FALSE │ │ │
│ status │ VARCHAR │ TRUE │ 'pending'│ │
│ created_at │ TIMESTAMPTZ │ FALSE │ │ │
└──────────────┴──────────────┴────────┴──────────┴───────────┘

VIII. Other Important Improvements

8.1 Ctrl+C No Longer Exits the Shell

In v1.4 and earlier, pressing Ctrl+C would exit the entire DuckDB CLI session. Now (PR #20155), Ctrl+C only cancels the currently executing query without exiting the program. This is very safe when handling long-running queries.

8.2 .import Command Refactored

The .import command now uses built-in CSV/JSON readers (PR #19563), supporting more format options and better error handling.

8.3 .open --sql Option

You can execute SQL directly when opening a database via .open --sql database.db (PR #19445), ideal for scripting and automation scenarios.

8.4 Configurable Progress Bar

For long-running import or export operations, the progress bar is now customizable in its display mode (PR #19650).

IX. Upgrade Guide

If you’re using DuckDB v1.4 or earlier, you can enjoy these improvements immediately after upgrading to v1.5:

# Upgrade via pip
pip install --upgrade duckdb

# Via apt (Linux)
curl -LO https://github.com/duckdb/duckdb/releases/download/v1.5.0/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip -d /usr/local/bin

# Via Homebrew (macOS)
brew install duckdb

After upgrading, opening the DuckDB CLI immediately reveals the new interactive experience. Take a few minutes to try Tab completion, .display_colors, and the _ token — they’ll significantly boost your daily productivity.

Conclusion

The CLI refactoring in DuckDB v1.5 is not just “adding a few features” — it represents a key step for DuckDB from “a great analysis engine” to “a complete analysis platform.” Auto-complete, dark mode, the _ token, paging output, dynamic prompts — these seemingly small improvements, when accumulated, significantly enhance developers’ daily experience.

As the DuckDB team said: “We believe great tools should make you forget the tool itself exists.” This CLI refactoring is the best embodiment of that philosophy.

References: DuckDB v1.5.0 Announcement, DuckDB v1.5.3 Announcement

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。