Skip to content

query.md

Latest commit

 

History

History
133 lines (91 loc) · 4.04 KB

query.md

File metadata and controls

133 lines (91 loc) · 4.04 KB
Raw

SQL queries

The SQL bar

The SQL input bar at the top of the main panel is always visible. Type a query and press Enter or click ▶ Run to execute.

SELECT * FROM sales WHERE region = 'EU' ORDER BY amount DESC LIMIT 500

Press ⤢ to open the full Monaco SQL editor modal for multi-line queries with syntax highlighting.

DuckDB SQL

TableViewer uses DuckDB as its SQL engine. Every opened file is registered as a DuckDB view with its alias as the table name.

Supported SQL

  • SELECT, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET
  • JOIN (INNER, LEFT, RIGHT, FULL, CROSS, ASOF)
  • CTEs — WITH cte AS (…) SELECT …
  • Window functions — ROW_NUMBER(), RANK(), SUM() OVER (PARTITION BY …), …
  • PIVOT / UNPIVOT
  • UNNEST — explode array columns
  • Aggregate functions: COUNT, SUM, AVG, MIN, MAX, MEDIAN, STDDEV, …
  • String functions: LIKE, ILIKE, regexp_matches, split_part, trim, …
  • Date / time functions: date_trunc, date_diff, strftime, …
  • JSON operators: ->>, json_extract, …

Cross-file JOINs

Every alias is a DuckDB view. Query multiple tables in the same statement:

SELECT
    o.order_id,
    c.name          AS customer,
    p.description   AS product,
    o.quantity * p.unit_price AS total
FROM orders   o
JOIN customers c ON o.customer_id = c.id
JOIN products  p ON o.product_id  = p.id
WHERE o.created_at >= '2024-01-01'

Complex types

DuckDB LIST, STRUCT, MAP, and UNION columns are automatically serialised to JSON strings in the result table. Click a cell to see the full value.

Monaco SQL editor

Press ⤢ in the toolbar to open a full-screen Monaco editor:

  • SQL syntax highlighting
  • Ctrl+Enter to execute (or click Run in the modal header)
  • The result appears in the main table; the modal closes automatically

The editor opens pre-filled with the current SQL bar content and syncs back on execute.

Lazy loading

For large datasets, results load progressively:

  1. First page (pageSize rows) arrives in < 100 ms — you see data immediately.
  2. A thin green progress bar below the shape bar shows load progress (N%).
  3. Background chunks of 10 000 rows stream in until the dataset is fully loaded.
  4. Running a new query cancels any in-progress background load immediately.

The shape bar always shows the total row count from COUNT(*) even before all rows have loaded:

192 000 rows × 14 cols  ·  loading 24% …

Query history

Every executed query is saved to the history (last 10). The History section in the sidebar lists them newest-first.

Reuse

  • Click a history item to populate the SQL bar and execute.
  • Ctrl+Click (or right-click → Open in modal view) to open the query in the SelectionQueryModal without changing the main SQL bar.

Context menu (right-click on a history item)

Action Description
Open in modal view Execute in a separate paginated modal
Use query Copy to SQL bar and execute
Export as CSV Run query and save result as CSV
Export as Parquet Run query and save result as Parquet
Delete from history Remove from history

Pinned queries

Pin any history item by clicking the 📌 icon. Pinned queries:

  • Stay at the top of the history list with a green left border
  • Have an editable name (double-click the name to rename inline)
  • Are included in Export → All queries as (see export-import.md)
  • Are referenced by TVQuery tokens in notes (see notes.md)

Naming conventions

The query name becomes the table name when exporting to DuckDB/SQLite and the SQL comment when exporting as .sql. Use descriptive names:

monthly_revenue
top_customers_eu
churn_by_cohort

Keyboard shortcuts

Shortcut Action
Enter Execute SQL (in SQL bar)
Ctrl+Enter Execute SQL (in Monaco modal)
Ctrl++ / Ctrl+= Zoom in
Ctrl+- Zoom out
Ctrl+0 Reset zoom