DuckDB Terminal User Guide

Getting Started

DuckDB Terminal is a browser-based SQL interface that lets you run queries directly in your web browser. It uses DuckDB WASM for query execution and Ghostty for terminal emulation.

Key Features

• Full SQL Support - Execute any DuckDB-compatible SQL query
• Command History - Navigate previous commands with arrow keys (persisted across sessions)
• Auto-Complete - Tab completion for SQL keywords, table names, and functions
• Multiple Output Formats - Table, CSV, TSV, or JSON output
• Interactive Charts - Visualize query results with auto-detected chart types
• Query Sharing - Share SQL queries via URL for collaboration
• File Loading - Load CSV, Parquet, and JSON files via drag-and-drop
• Syntax Highlighting - Color-coded SQL for better readability

Your First Query

When you open the terminal, you'll see the duck prompt (🦆). Simply type a SQL query and press Enter to execute:

SELECT 'Hello, DuckDB!' AS greeting;

The result will be displayed in a formatted table below your query.

Multi-line Input

For complex queries, you can write SQL across multiple lines. The terminal detects incomplete statements and shows a continuation prompt ( >) until you complete the query with a semicolon:

SELECT
    id,
    name,
    email
FROM users
WHERE active = true
ORDER BY name;

Terminal Commands

Terminal commands start with a dot (.) and provide various utilities. Type .help to see all available commands.

Information Commands

Display Commands

Data Commands

System Commands

Visualization Commands

Keyboard Shortcuts

Navigation & Editing

Pagination Mode

When viewing paginated results, these shortcuts are available:

Chart Mode

Output Modes

Use .mode to switch between output formats. The format affects both display and clipboard/download content.

Table Format (Default)

Human-readable ASCII table with borders:

+----+-------+-------------------+
| id | name  | email             |
+----+-------+-------------------+
| 1  | Alice | [email protected] |
| 2  | Bob   | [email protected]   |
+----+-------+-------------------+

CSV Format

Comma-separated values, ideal for spreadsheets:

id,name,email
1,Alice,[email protected]
2,Bob,[email protected]

TSV Format

Tab-separated values for data import:

id	name	email
1	Alice	[email protected]
2	Bob	[email protected]

JSON Format

Array of objects for APIs and JSON files:

[
  {"id": 1, "name": "Alice", "email": "[email protected]"},
  {"id": 2, "name": "Bob", "email": "[email protected]"}
]

Copying & Downloading

After running a query:

• .copy - Copy results to clipboard in current format
• .download - Download as file (extension matches format)
• .download mydata - Download with custom filename

Pagination

For large result sets, enable pagination:

.pagesize 50  -- Show 50 rows per page
.pagesize 0   -- Disable pagination

Working with Files

DuckDB Terminal supports loading data files directly in your browser.

Supported Formats

• CSV - Comma-separated values
• Parquet - Columnar storage format
• JSON - JSON files and JSON Lines

Loading Files

There are two ways to load files:

Drag and Drop

Simply drag a file from your computer and drop it onto the terminal. The file will be registered and you can query it immediately.

File Picker

Use the .open command to open a file picker dialog.

Querying Loaded Files

Once loaded, files are accessible by their filename:

SELECT * FROM 'sales.csv' LIMIT 10;

SELECT category, SUM(amount)
FROM 'transactions.parquet'
GROUP BY category;

Managing Files

.files list    -- Show all loaded files
.files add     -- Open file picker
.files remove  -- Remove a loaded file

Charts & Visualization

Visualize query results with interactive charts powered by uPlot.

Basic Usage

Run a query, then use .chart to visualize:

SELECT date, revenue, cost FROM sales;
.chart

Auto-Detected Chart Types

Chart Options

.chart type=bar        -- Force bar chart
.chart x=date          -- Specify X-axis column
.chart y=revenue,cost  -- Specify Y-axis columns
.chart export          -- Export as PNG

Example: Time Series

SELECT
    DATE '2024-01-01' + INTERVAL (i) DAY AS date,
    100 + random() * 50 AS revenue,
    80 + random() * 30 AS cost
FROM generate_series(0, 30) AS t(i);
.chart

Example: Bar Chart

SELECT category, SUM(amount) as total
FROM (VALUES
    ('Electronics', 1500),
    ('Clothing', 800),
    ('Food', 1200),
    ('Books', 400)
) AS t(category, amount)
GROUP BY category;
.chart

Example: Histogram

SELECT (random() * 100)::INTEGER AS value
FROM generate_series(1, 200);
.chart

Query Sharing

Share SQL queries via URL to collaborate with others. When someone opens a shared link, the queries execute automatically in the terminal.

Opening the Share Modal

There are two ways to open the sharing modal:

• Press Ctrl+Shift+S (or Cmd+Shift+S on Mac)
• Click the share button in the sidebar (desktop) or mobile action bar

Selecting Queries

The modal displays your recent SQL queries from history. Dot commands (like .help) are excluded since they are terminal-specific.

1. Navigate the list using arrow keys or mouse
2. Press Space or click to select/deselect queries
3. Watch the character counter to stay within the 2000 character URL limit
4. Selected queries will execute in order (oldest first) when the link is opened

Copying the Link

Once you've selected queries:

• Press Enter to copy the shareable link to clipboard
• Or click the "Copy shareable link" button

The modal closes automatically after copying.

Receiving Shared Links

When someone opens a shared URL:

1. The terminal loads normally with the welcome message
2. After initialization, the shared queries execute automatically
3. Each query is displayed with its output
4. The URL is cleared to prevent re-execution on page refresh

Keyboard Shortcuts

URL Format

Shared URLs use a hash-based format with Base64-encoded queries:

https://terminal.sql-workbench.com/#$queries=v1,ENCODED_QUERY_1,ENCODED_QUERY_2

• The v1 prefix indicates the URL format version
• Each query is URL-safe Base64 encoded (no special characters)
• Queries are separated by commas

Themes

DuckDB Terminal includes five color themes. Switch using the settings dropdown in the sidebar or the .theme command.

Available Themes

Switching Themes

.theme dark
.theme light
.theme tokyo-night
.theme dracula
.theme solarized-dark

SQL Basics

Here are some common SQL operations to get you started.

Creating Tables

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR,
    email VARCHAR,
    created_at TIMESTAMP DEFAULT current_timestamp
);

Inserting Data

INSERT INTO users (id, name, email) VALUES
    (1, 'Alice', '[email protected]'),
    (2, 'Bob', '[email protected]'),
    (3, 'Charlie', '[email protected]');

Querying Data

-- Select all
SELECT * FROM users;

-- With conditions
SELECT name, email
FROM users
WHERE name LIKE 'A%'
ORDER BY name;

-- With limit
SELECT * FROM users LIMIT 10;

Joins

SELECT
    u.name,
    o.order_date,
    o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

DuckDB Features

DuckDB includes many powerful features beyond standard SQL.

Generate Series

-- Generate numbers 1-10
SELECT * FROM generate_series(1, 10);

-- Generate dates
SELECT generate_series(
    DATE '2024-01-01',
    DATE '2024-12-31',
    INTERVAL '1 month'
);

Range Function

SELECT range(10);  -- Returns [0,1,2,3,4,5,6,7,8,9]
SELECT range(5, 10); -- Returns [5,6,7,8,9]

Date/Time Functions

SELECT
    current_date,
    current_timestamp,
    date_part('year', current_date) AS year,
    date_trunc('month', current_date) AS month_start;

JSON Functions

SELECT
    json_extract('{"name": "Alice", "age": 30}', '$.name') AS name,
    json_extract_string('{"city": "NYC"}', '$.city') AS city;

String Functions

SELECT
    upper('hello') AS upper,
    lower('HELLO') AS lower,
    concat('Hello', ' ', 'World') AS concat,
    substring('Hello World', 1, 5) AS substr;

Window Functions

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Data Analysis Patterns

Aggregation

SELECT
    category,
    COUNT(*) AS count,
    SUM(amount) AS total,
    AVG(amount) AS average,
    MIN(amount) AS min,
    MAX(amount) AS max
FROM transactions
GROUP BY category
ORDER BY total DESC;

Pivot Tables

SELECT
    region,
    SUM(CASE WHEN product = 'A' THEN sales ELSE 0 END) AS product_a,
    SUM(CASE WHEN product = 'B' THEN sales ELSE 0 END) AS product_b,
    SUM(CASE WHEN product = 'C' THEN sales ELSE 0 END) AS product_c
FROM sales
GROUP BY region;

Common Table Expressions (CTEs)

WITH monthly_sales AS (
    SELECT
        date_trunc('month', order_date) AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_sales;

Subqueries

SELECT *
FROM products
WHERE price > (
    SELECT AVG(price) FROM products
);

External Data

DuckDB can query files directly without importing them first.

CSV Files

-- Query local file (after drag-drop or .open)
SELECT * FROM 'sales_data.csv';

-- With options
SELECT * FROM read_csv('data.csv', header=true, delim=',');

Parquet Files

-- Query parquet directly
SELECT * FROM 'analytics.parquet';

-- With column selection (efficient - only reads needed columns)
SELECT date, revenue
FROM 'large_dataset.parquet'
WHERE date >= '2024-01-01';

JSON Files

-- Query JSON array
SELECT * FROM 'users.json';

-- JSON Lines format
SELECT * FROM read_json('logs.jsonl', format='newline_delimited');

HTTP/HTTPS Files

-- Query remote CSV
SELECT * FROM 'https://example.com/data.csv';

-- Query remote Parquet
SELECT * FROM 'https://example.com/dataset.parquet';