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,.modeand 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
| Feature | DuckDB v1.4 | DuckDB v1.5 | psql | sqlite3 |
|---|---|---|---|---|
| Tab Auto-Complete | ❌ | ✅ | ✅ | ❌ |
| Dark/Light Auto-Detect | ❌ | ✅ | ❌ | ❌ |
| Syntax Highlighting | ❌ | ✅ | ✅ | ❌ |
| Paging Output | ❌ | ✅ | ✅ | ❌ |
| Last Result Reuse | ❌ | ✅ | ❌ | ❌ |
| Multi-Line Editing | Basic | Enhanced | ✅ | Basic |
| Dynamic Prompt | ❌ | ✅ | ✅ | ❌ |
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.
X. Monetization Suggestions
10.1 Training Services for Developers
The CLI experience improvements mean companies can more confidently promote DuckDB to their internal data teams. You can offer “DuckDB Efficient CLI Workflows” training courses, teaching team members how to use advanced features like auto-complete, the .last command, and dynamic prompts to accelerate daily data analysis. These trainings typically charge ¥5,000-20,000 per session.
10.2 Enterprise Shell Customization Services
Many enterprises have specific compliance requirements and security policies. Based on v1.5’s configurable startup text, color schemes, and command restriction features, you can provide customized DuckDB Shell services for enterprises, including:
- Custom startup banners (branding)
- Restricting execution of dangerous commands
- Unifying team interaction styles
- Integrating enterprise logging systems
10.3 CLI-Embedded Data Products
v1.5’s modular CLI refactoring makes it easier to embed into other applications. You can develop industry-specific analytics tools with a DuckDB kernel, leveraging enhanced rendering and interaction capabilities to deliver superior user experiences compared to traditional tools. For example:
- Real-time reporting analysis tools for finance
- User behavior analysis dashboards for e-commerce
- Data quality monitoring platforms for healthcare
10.4 Automated Operations Script Templates
For DevOps teams, you can sell DuckDB CLI automation script template packages, covering:
- Scheduled data synchronization scripts
- Database health check scripts
- Automated backup and recovery workflows
- Performance monitoring and alerting integrations
10.5 Comparison Summary
| Monetization Direction | Target Customer | Expected Revenue | Investment |
|---|---|---|---|
| Training Courses | Data teams | ¥5K-20K/session | Low |
| Shell Customization | Mid-large enterprises | ¥10K-50K/project | Medium |
| Industry Analytics Tools | Various industries | ¥50K+/set | High |
| Automation Scripts | DevOps | ¥3K-10K/set | Low |
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