# Schema browser and history

> Browse databases, tables and columns in DuckLocal's schema sidebar, generate SELECT queries, manage DuckDB extensions and reuse past queries.

Source: https://ducklocal.app/docs/schema-and-history

**[中文](zh/schema-and-history.md)**

The sidebar has three tabs: **Schema**, **History** and **Extensions**
(**表结构**, **查询历史** and **扩展** in Chinese). The button at the left of the
title bar, or **⌘B**, hides the sidebar and brings it back; the button stays in
the same place either way. Whether the sidebar is hidden is remembered.

## The schema tree

From top to bottom, the tree shows **Local files**, **Dashboards**, and **S3** as small grey section headings, followed by your databases as bold rows:

- **Local files** — the data files you have registered, each named by its
  view, with its row count. Its children are the view's columns.
  - The file name appears beside the view name only when the two differ
    (`sales_2` from `sales.csv`).
  - The folder appears when two files share a view name.
  - Hover a row for the full path.
- **Dashboards** — the `.dash` dashboards you have opened recently, sorted
  by name so a row stays put when you click it.
  - Click one to reopen it as a tab, the way you would a file.
  - Two dashboards with the same name show their folder beside it. One file
    reached by two spellings of its path (`E:\Proj` and `E:\proj`, or a
    relative and an absolute path) is one entry, and opens one tab.
  - The × on hover takes it off the list, leaving its files and any open tab
    alone.
  - An entry whose path no longer exists is dropped from the list.
- **S3** — present once [S3 is configured](s3.md). Loads lazily.
- Each database, then its schemas, then tables and views, then columns. The
  database the window opened comes first; databases you
  [attached beside it](data-sources.md#attached-databases) follow, marked
  `read-only` when attached that way.

Tables show estimated row counts; views do not. Column entries show DuckDB data types. The system database and the `information_schema`, `pg_catalog`, and `system` schemas are hidden.

The search box over the tree (**Search tables and columns**) keeps only the
rows whose names contain what you type, ignoring case, and the rows above them.
A table whose name matches keeps all its columns; a table kept for a matching
column opens to show it. Clear the box to get the whole tree back.

### Databases

The open database comes first, then each
[attached database](data-sources.md#attached-databases) as a group of its own,
with the number of tables beside its name.

- An attached database shows even when it is empty. The open database is left
  out while it has no tables, which is how a fresh in-memory session starts.
- A muted hint beside the name says **read-only**, and the file type when it is
  not DuckDB (**sqlite**).
- Hover the name for the database file's path.

Right-click a database to make it the default or to detach it — see
[Right-click menu](#right-click-menu).

The refresh button beside the tabs (**Refresh schema**) reloads the catalog and
the list of registered files.

## Generating a SELECT

All the clicks below **replace the entire buffer** of the active tab — they
do not insert at the cursor. The same is true of clicking a history entry.
Save anything you care about before clicking around.

These fill the active editor when clicked:

| Click | Generated SQL |
| --- | --- |
| A table's play button | `SELECT * FROM db.schema.table LIMIT 100;` |
| A column's checkbox, or its name | `SELECT col FROM db.schema.table LIMIT 100;` |
| More columns of the same table | `SELECT a, b FROM db.schema.table LIMIT 100;` |
| An S3 object that is a data file | `SELECT * FROM 's3://bucket/key' LIMIT 100;` |

Each column, of a table, a view or a local file, has a checkbox. Ticking
columns, or clicking their names, which ticks them, builds the `SELECT` from
the ticked ones, in the table's column order rather than the order you ticked
them. Unticking the last one gives `SELECT *`. The ticks belong to one table:
ticking a column of another table starts over there.

The generated SQL spans three lines (`SELECT …`, `FROM …`, `LIMIT 100;`).
Table names are written as short as the current default database and schema
allow: `orders` for a table in the default schema, `main.orders` for another
schema of the same database, and `database.schema.table` otherwise. After
**Use as default database** on `snap`, a preview of one of its tables reads
`FROM orders`. Until the first query has run, names are fully qualified.

A name is double-quoted only when it contains characters other than letters,
digits and underscores.

## Right-click menu

Every command a row's hover buttons offer is also on its right-click menu, so
you can find the command without hovering:

| Row | Menu |
| --- | --- |
| Table or view | Generate SELECT query, Column overview, Copy name, Copy table info |
| Column | Generate SELECT query, Copy name, Change data type… (table columns only) |
| Attached database | Use as default database, Detach database |
| S3 bucket list | Refresh buckets |
| Recent dashboard | Remove from list |
| Local file | Generate SELECT query, Column overview, Copy name, Copy table info, Remove from local files |

**Copy name** copies the bare name — the table, column or view name alone,
not qualified with its database and schema, and not quoted.

**Copy table info** copies the whole table as Markdown, ready to paste into a
chat, an issue or an AI agent's prompt — the name a query uses, what it is,
its size, and every column with its type:

```md
memory.main.orders (table, ~224 rows, 3 columns)

| column | type |
|---|---|
| date | DATE |
| channel | VARCHAR |
| revenue | DOUBLE |
```

**Column overview** replaces the active editor's text with `FROM <table>;`,
runs it, and opens the results panel on its **Overview** tab, which summarizes
every column of the whole table: nulls, distinct values, range and a small
chart of its values. See [Column overview](results-and-charts.md#overview).

**Use as default database** runs `USE` on that database, so table names
without a database resolve there, and generated queries get shorter (see
above). It is not offered on the database that is already the default.

**Detach database** detaches an attached database and forgets it, so it is not
attached again next time. If it was the default, the session moves to another
database first. The file is not touched. The open database itself cannot be
detached, so its menu has no such item. More in
[Attached databases](data-sources.md#attached-databases).

## Altering a column type

A column row offers a type action (not available on view columns, so attached
CSV and Parquet columns cannot be retyped). It opens a free-text dialog
pre-filled with the current type and runs:

```sql
ALTER TABLE db.schema.table ALTER COLUMN col SET DATA TYPE <what you typed>
```

The table name is always fully qualified here, and the column name is quoted
only when it needs to be.

There is no picker and no validation list — any DuckDB type string works,
including parameterised ones like `DECIMAL(10,2)`. An invalid type comes back
as an error notification. Success reloads the catalog.

Renaming and dropping columns, and editing column comments, are not available.

## Removing a registered file

The **×** on a file row asks for confirmation, then drops the view and forgets
the registration. **The file on disk is not touched.**

To remove several registrations together, select their rows under **Local files**:

- **⌘-click** on macOS or **Ctrl-click** on Windows/Linux adds or removes one file from the selection.
- **Shift-click** selects a consecutive range.
- Click **×** on a selected file, or use its right-click removal action, to confirm removing the selected registrations. The menu shows how many files will be removed.

This removes the views and registrations, leaving the original files on disk. Selecting files this way does not select database tables or dashboard filters.

## Extensions

The **Extensions** tab lists every DuckDB extension the engine knows of:
loaded ones first, then installed ones, then the rest, each group by name.

Each row shows:

- the extension's name and a status tag: **Loaded**, **Installed** or
  **Not installed**;
- DuckDB's one-line description of it;
- its version and where it came from: **built in**, or the repository it was
  installed from (`core`, `community`, or a URL).

What the statuses mean:

| Status | Meaning |
| --- | --- |
| Loaded | Active in the current connection: its functions, readers and types work in queries now |
| Installed | Downloaded to this machine, but not loaded in this connection |
| Not installed | Known to DuckDB, but not on this machine yet |

**Built in** extensions are compiled into DuckLocal — `parquet` and `json`
among them, both loaded from the start. They are never installed or updated.

A row offers the actions that apply to it:

| Button | Shown when | Runs |
| --- | --- | --- |
| **Install** | Not installed | `INSTALL name` |
| **Load** | Installed, not loaded | `LOAD name` |
| **Update** | Installed from a repository | `UPDATE EXTENSIONS (name)` |

- **Install** and **Update** download from DuckDB's extension repository, so
  they need the network. **Load** reads the installed file and does not.
- Installing does not load. Click **Load** afterwards, or run `LOAD name`.
- An installed extension stays installed: it is a file on disk, in DuckDB's own
  extension folder. Loading lasts only for the current connection.
- That folder is per DuckDB release (`~/.duckdb/extensions/v2.0.0/…`). When an
  update moves DuckLocal to a new DuckDB release, as 0.3.0 did with DuckDB 2.0,
  the extensions you had show as **Not installed** until you install them
  again.
- One action runs at a time; the other buttons are disabled meanwhile. A
  notification says *Installed …*, *Loaded …* or *Updated …*, or why it failed.
- After a load or an update, the schema tree is reloaded, since an extension
  can bring tables and functions with it.

The list is read from `duckdb_extensions()` each time the tab opens, after each
action, when the connection changes while the tab is open, and when you click
the refresh button beside the tabs (**Refresh extensions**). So it also shows
an extension loaded another way — by a `LOAD` typed in a query tab, or by
DuckDB on its own.

Since v0.2.1, neither the window nor the CLI automatically downloads a missing extension when a query needs it. Install it explicitly in **Extensions** or with `INSTALL name`. Extensions already installed on this machine can still load automatically.

### Open the DuckDB UI {#duckdb-ui}

The [DuckDB UI](https://duckdb.org/2025/03/12/duckdb-ui) — DuckDB's own
browser notebook, built by MotherDuck — is an extension like any other, so it
runs inside DuckLocal too:

1. On the **Extensions** tab, click **Install** on the `ui` row, then **Load**.
2. In a query tab, run:

   ```sql
   CALL start_ui();
   ```

   Your browser opens the UI at `http://localhost:4213`. To start the server
   without opening a browser, run `CALL start_ui_server();` instead.

![The Extensions tab with the ui extension loaded](./assets/duckdb-ui.png)

The UI runs on the DuckDB instance it was started from, which here is
DuckLocal's. Some things to know:

- **It needs the network.** Installing `ui` downloads it, like any extension.
  And each time it starts, the extension's local server fetches the UI's own
  files from `https://ui.duckdb.org`. Your queries and data still run locally.
- **It lives as long as DuckLocal does.** Quitting DuckLocal closes its DuckDB
  instance, and the server with it. `CALL stop_ui_server();` stops it sooner.
- **Notebooks are saved by the extension, not by DuckLocal,** in
  `~/.duckdb/extension_data/ui/`. DuckLocal's query history does not include
  what you run in the UI.
- **It cannot run directly on a read-only database.** Attach that database
  from a notebook with `ATTACH '…' (READ_ONLY)` instead.

[DuckDB UI vs DuckLocal](https://ducklocal.app/blog/duckdb-ui-vs-ducklocal/)
compares the two and covers when to use which.

## Query history

Every run is recorded — successes and failures alike — with a local-time
timestamp, duration in milliseconds, row count (or affected-row count for
DML), and the error text if it failed. Failed entries are shown in red with a
*Failed* tag.

The list shows the newest **200** entries. Clicking a row refills the active
editor with that SQL and focuses it, replacing whatever was there.

There is no retention policy and no pruning: the history table grows without
bound, and the UI simply shows the most recent 200. There is also no search,
no filter, no export, and no way to delete an entry from the interface.

History is stored in DuckLocal's own database file, not in the database you
have open, so it survives switching databases and losing your workspace. See
[Settings and app data](settings-and-data.md) for where it lives.