This page describes Calíope 1.8, the version I am building right now. 1.7 is on the App Store for the Mac, iPad, iPhone, Apple Watch, Apple TV and Apple Vision Pro. The changelog says which version each feature landed in.

Adds an “Open in Calíope” button to every topic. It only works with the app installed.

DuckDB Editor

DuckDB Editor

Open and query local file-based DuckDB databases, without a server.

Where it is: Workspace › Tools › DuckDB Editor

The DuckDB Editor is a standalone tool for local .duckdb files, with the same layout as the SQLite Editor: Structure, Data, Execute SQL, ER Diagram — only with a file open —, Query Builder and Settings, and it works with no connection to any server.

It can also open an in-memory database — no file — from Query data file…, to work on a CSV, JSON or Parquet file without creating anything on disk.

What Calíope doesn't do with DuckDB. It doesn't download extensions — INSTALL, LOAD, UPDATE EXTENSIONS and extension repositories (CREATE and DROP EXTENSION REPOSITORY) are rejected before they touch the engine — only the four that ship with the app are there. For the same reason, there are no remote-access secrets (CREATE SECRET), and you can't ATTACH a SQLite or Postgres file, which need an extension that isn't linked in. The engine's configuration is closed once it opens: a SET of an engine setting is rejected —threads and memory_limit too, which only change from Settings; SET VARIABLE isn't, because a variable isn't configuration— and, like every error at execution time, it cancels the unsaved changes. The two SET statements that DuckDB 2.0's messages suggest are the exception: Calíope stops them first and they cancel nothing. What does work is moving the whole database: Whole database and Save As… run outside the pending transaction.

Keywords: duckdb, file, local, database, analytics

Open or create a database

Open an existing .duckdb file or create a new one.

Where it is: Workspace › Tools › DuckDB Editor

Use Open… to pick an existing .duckdb file, or New database… to create an empty one. Recent files appear on the initial screen. A Read-only badge appears when a file can't be modified.

If the file is outside Calíope's own folder, the first time you open it you're asked for access to its folder: DuckDB needs to write a .wal file next to the .duckdb one, and the app's sandbox only allows that inside a folder the user has chosen. It's remembered the next time you open a file from the same folder. No DuckDB file at hand? On the empty state, Download the sample database opens the page on the website where the public sample lives —a small shop with 50,000 orders, made with Calíope. The app downloads nothing: it opens the page and you decide.

You can also open it from outside Calíope. On the Mac, from the Finder with Open With › Calíope, or with a double-click if Calíope is the default app for that kind of file. On iPad and iPhone, by sharing it from the Files app and choosing Calíope. A Parquet file opened that way lands here too, in an in-memory database (see Query data file…). If the session in front of you doesn't offer this tool, the file opens in Open offline.

The file format. A new database — the one New database… creates and the one Save As… leaves from an in-memory database — is created in the DuckDB 2.0 format. DuckDB 2.0 and later versions open that format; Calíope 1.7 and any tool with DuckDB 1.5 or earlier can't. A file that already existed keeps its format: Calíope opens it and changes it without converting it, so the tool that created it still opens it. You can't choose the format when creating a database, and an existing file isn't converted. To see the format of the open file, go to Structure: below the toolbar, File format says DuckDB 2.0 or later for a new database and, for example, DuckDB 1.0 or later for one created with DuckDB 1.5.

Keywords: open, create, new, recent, file, folder, wal, format, version

Explore and design the structure

Browse tables, views and indexes, and create them.

The Structure section shows the schema tree with each object's columns and DDL. DuckDB has no triggers, so the tree shows tables, views, indexes, sequences, types, macros and table macros. Sequences, types and macros are created from Execute SQL —CREATE SEQUENCE, CREATE TYPE, CREATE MACRO, CREATE MACRO … AS TABLE—; here you can see and drop them. If the file has more than one schema, each group is prefixed with its schema name, and an object's comment (COMMENT ON) shows on hover. Use New to create tables, indexes or views, Add column or Rename table on the selected table, and Delete to remove an object. Below the toolbar, File format says which versions of DuckDB can open the file: a new database needs DuckDB 2.0 or later, and one that already existed keeps its format.

Views created by Query data file… carry a different icon — a paperclip — and their tooltip shows the source file's path.

Attach database… opens another DuckDB database next to the current one; its contents show up in the tree prefixed with the database name, and you query them as database.schema.table. It is always read-only —writing to it would mean creating its .wal file alongside, which the sandbox does not allow— and Detach removes it. A SQLite file cannot be attached here: they are different engines, and that is what the SQLite Editor is for.

iPad and Apple Vision Pro: the detail panel shows the source file too.

Keywords: structure, table, index, view, create, ddl, schema

Browse and edit data

View, filter, sort and edit rows.

The Data section shows a table's rows in pages, with the count and the arrows on the right of the bar. How many rows fit on a page is set in Settings › Text Editor › Results › Rows per page — Settings, on iPad. Sort by tapping a column header, narrow it down column by column with Filters, or type in the search field to look across all of them at once.

Editing a cell

macOS: double-click it. iPad: a single tap. Return confirms; Esc cancels.

Adding and deleting rows

Add row opens a form with one line per column. Unlike SQLite, DuckDB doesn't auto-assign the whole primary key: every required column needs its own value, the key included. To delete, select the row by its number and use Delete row.

None of this touches the file until you press Save changes: what you edit stays in a pending transaction that Revert changes undoes entirely.

Keywords: data, rows, edit, filter, sort, search, cell, insert, new row, delete

Writing and running SQL

Syntax highlighting and autocomplete, with a row cap and a Stop button.

The Execute SQL section uses the same text field and results grid as the server SQL Editor, over the open file and with no connection involved.

The text is syntax-highlighted. Press Run, or ⌘↩, to run the whole script; what's drawn is the result of the last statement that returned rows. A large SELECT isn't brought in whole: it's cut at Settings › Text Editor › Results › Row cap — the same cap the SQL Editor uses — and the grid says how many of how many it brought.

While a query is running, a Stop button interrupts it for real: the engine cuts it off mid-way, it doesn't wait for it to finish.

If a statement fails, the script stops there and the error appears where the results would go. A syntax error or a name that doesn't exist leaves what is pending as it was; one that happens at execution time cancels the unsaved changes, and Calíope says so (topic Save and revert changes). When a topic explains the error —the SQL that DuckDB 2.0 no longer accepts is in What changes with DuckDB 2.0—, View solution opens it from the error itself.

INSTALL and LOAD are rejected: Calíope doesn't download DuckDB extensions, so a query that needs one doesn't run, with the reason spelled out in the message.

Whatever you write to the file stays pending, just like an edit in Data: it doesn't touch the file until Save changes.

Visual EXPLAIN

The Visual EXPLAIN button, next to Run, draws the plan DuckDB would follow —EXPLAIN (FORMAT JSON)— without running anything, in the same tab as the SQL Editor, with the rows it estimates. It analyzes the selection and, with nothing selected, the last statement of the text. A table can show a full scan even with an index: DuckDB keeps minimums and maximums per block and often prefers to scan while skipping blocks.

Function entries

Hover the pointer over a function on macOS, or put the text cursor on it on iPad, and its entry appears, the DuckDB one: syntax, return type, parameters and an example. The topic Contextual help for SQL functions explains it.

Apple Vision Pro: the same as on iPad, and the function card hangs above the header, below the window.

Keywords: run, sql, highlighting, autocomplete, grid, results, cap, rows, stop, interrupt

What changes with DuckDB 2.0

The DuckDB 1.5 SQL that 2.0 rejects or answers differently, with its new form.

DuckDB 2.0 answers differently to some SQL that worked with DuckDB 1.5, and Calíope has carried DuckDB 2.0 since version 1.8. An older script may now give an error or, in one case, a different result without warning. When a query fails because of one of these changes, View solution, next to the error, opens this topic.

The fix is always to rewrite the query. The engine's messages suggest a SET to go back to the old behavior; in Calíope it doesn't work: DuckDB's configuration is locked while a file is open (topic Settings and maintenance). Calíope stops that SET before it reaches the engine and says so: it isn't run, and the unsaved changes stay.

Lambdas: lambda x: instead of x ->

A lambda function written with an arrow is no longer accepted.

Before:

SELECT list_transform([1, 2, 3], x -> x + 1);

Now:

SELECT list_transform([1, 2, 3], lambda x: x + 1);

With the old form, the engine answers:

Binder Error: Deprecated lambda arrow (->) detected. Please transition to the new lambda syntax, i.e.., lambda x, i: x + i, before DuckDB's next release.

// and % by zero are errors

Integer division //, the remainder % and the functions divide and mod used to return NULL when the divisor was zero. Now the query fails and, because it is an error at execution time, it cancels the unsaved changes. / hasn't changed: it still gives inf.

Before (it returned NULL):

SELECT 7 // 0;

Now, to keep getting NULL:

SELECT TRY(7 // 0);
SELECT 7 // NULLIF(0, 0);

The engine answers:

Invalid Input Error: Division by zero in expression (7 // 0). Use TRY(...) to return NULL for this expression, or SET error_on_division_by_zero=false to return NULL for all divisions by zero.

~ matches part of the text, not the whole text

This change gives no error: it gives a different result. text ~ pattern used to be true only if the regular expression matched the whole text; now matching a part is enough, as in PostgreSQL. !~ changes the same way.

Before (it gave false; now it gives true):

SELECT 'my_file.txt' ~ 'file.txt';

Now, to require the whole text:

SELECT regexp_full_match('my_file.txt', 'file.txt');
SELECT 'my_file.txt' ~ '^file.txt$';

An untyped NULL is no longer INTEGER

In CREATE TABLE … AS, a column that comes from a bare NULL used to get the type INTEGER. Now it gets the type "NULL" and accepts no other value. Tables that already existed keep their type.

Before:

CREATE TABLE notas AS SELECT 1 AS id, NULL AS nota;
INSERT INTO notas VALUES (2, 5);

Now, with the type written out:

CREATE TABLE notas AS SELECT 1 AS id, NULL::INTEGER AS nota;
INSERT INTO notas VALUES (2, 5);

With the old form, the INSERT fails:

Conversion Error: Unimplemented type for cast (INTEGER -> "NULL")

A table that already ended up like this is fixed by changing the column's type:

ALTER TABLE notas ALTER COLUMN nota TYPE INTEGER;

Which errors cancel the unsaved changes

On its own, DuckDB 2.0 cancels the open transaction on any error, a syntax error included, and the next statement answers:

TransactionContext Error: Current transaction is aborted (please ROLLBACK)

Calíope doesn't use that default: it opens every file with the DuckDB 1.5 rule. A syntax error, or the name of a table, a column or a function that doesn't exist, doesn't touch your unsaved changes. Errors that happen at execution time do cancel them, as before —a constraint that isn't met, a value that can't be converted—, and now division by zero as well, which wasn't an error before. Calíope says so next to the error and does the ROLLBACK for you. The topic Save and revert changes explains it.

Also different

  • EXPLAIN as text is drawn differently: boxes with rounded corners and the operators named in words. Visual EXPLAIN hasn't changed.
  • ALTER TABLE … ADD COLUMN now accepts constraints, such as NOT NULL with a DEFAULT; DuckDB 1.5 rejected them.
  • A new file is created in the DuckDB 2.0 format, which earlier versions can't open. The topic Open or create a database explains it.

Keywords: duckdb 2.0, changes, lambda, arrow, division by zero, remainder, modulo, null, type, regular expression, tilde, transaction, upgrade

ER diagram of the file

The file's tables and their foreign keys, drawn without a server.

The ER Diagram section draws the tables of the open file with their columns and the foreign keys they declare, with no connection to any server. It only appears with a file open: an in-memory database does not offer it, because there is no file to keep the layout in.

The selector at the top lists the file's database — named like the file — and the ones you attached; if a database has tables in more than one schema, each schema is an entry (tienda.main, tienda.ventas). + DB brings a second one alongside.

It works like the ER diagram of a connection: zoom, Auto layout, export as PNG or PDF, print on the Mac, and a table's CREATE TABLE from its menu. After a structure change, an ATTACH or a DETACH, the diagram reads again on its own.

It only reads. It does not offer New FK relation… or the ⌃ drag: DuckDB does not add a foreign key to a table that already exists (it rejects ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY). To relate two tables, the foreign key is declared when the table is created (REFERENCES in Execute SQL).

Save layout keeps where you left each table per file and only on this device: it does not travel through iCloud or go into the settings backup, because the file's path is different on each device. The layouts of the files you used most recently are kept, and older ones are deleted on their own. If the system changes the file's path, its layout is lost, just like its entry in the recent files.

If the file cannot be read, a notice above the diagram says why, with the engine's text.

On Apple Vision Pro, Open in space lays the file's diagram out in the room, also read-only; it closes when you close the file.

Keywords: diagram, er, relationships, foreign keys, duckdb, file, layout, attach

Query Builder on the file

Build a SELECT from the database's tables and take it to Execute SQL.

The Query Builder section is the Query Builder of a connection, on the open database and without a server; it also works with an in-memory database. The list on the left has the tables of the database, with a filter by name; press one, its +, or drag it to the canvas. The JOIN comes from the foreign keys the database declares, and where there is none you say in Relations which columns are related. Conditions, the JOIN type, aliases, Clear canvas and copying the SQL work as they do with a connection.

With attached databases, the menu at the top chooses which one; the file's is named like the file. As soon as there is more than one database or schema, each table is written with its full name ("tienda"."main"."clientes"), because two can have the same name. If a DETACH takes the chosen database away, the canvas moves to the main one.

Insert into editor adds the query at the end of what you have written in Execute SQL, after a blank line, and switches to that section. It never replaces what was there.

The canvas is still there when you change section or tab, and it is discarded when you close the database. On the iPhone, and on the iPad in split view, the list fills the screen and adding a table takes you to the canvas.

Keywords: query builder, builder, query, select, join, relations, duckdb, file, attach

Save and revert changes

Changes stay pending until you save them.

Edits are grouped into a pending transaction, just like in the SQLite Editor. An amber dot marks unsaved changes. Use Save changes to commit them or Revert changes to discard them. Closing the file, or opening or creating another one, with pending changes first asks whether to save them or discard them.

If a statement fails

It depends on what fails. A syntax error, or the name of a table, a column or a function that doesn't exist, doesn't touch what is pending: you fix the query and carry on. Neither does Visual EXPLAIN on a mistyped query.

An error that happens at execution time makes DuckDB cancel the whole transaction, and with it every unsaved change, not just what the failed statement was trying to do: a cell or a new row that breaks a constraint, a value that can't be converted, an integer division by zero, a SET of an engine setting —the configuration is locked; the two that the engine's messages suggest are stopped by Calíope first and cancel nothing— or a query you stop with Stop. Calíope says so next to the error, the amber dot goes away and the grid shows what is saved again. There is nothing to undo by hand: whatever you do next goes into a new transaction. Before you try out queries, save what you don't want to lose.

The same goes for a script: if it fails at execution time, what its earlier statements wrote goes with the transaction; if it fails on syntax or on a name, what they wrote stays pending.

On its own, DuckDB 2.0 would cancel the transaction on any error; Calíope opens every file with the DuckDB 1.5 rule, which is the one above. The topic What changes with DuckDB 2.0 explains it.

DuckDB first writes to a .wal file next to the .duckdb one; flushing it into the main file (CHECKPOINT) is asynchronous, so the .wal can take a few seconds to disappear after saving and closing — the data is already safe, even while that side file is still there for a moment.

Keywords: save, revert, commit, transaction, changes, wal, checkpoint

Settings and maintenance

Give DuckDB threads and memory, and checkpoint or compact the file.

The Settings section is to the DuckDB Editor what the Pragmas section is to the SQLite Editor. Under Editable, Threads and Memory limit (MB) take effect with Apply: DuckDB's configuration is locked while a file is open —a SET threads or SET memory_limit can't change it: the engine rejects it and, like every error at execution time, that cancels the unsaved changes—, so Calíope keeps the value and reopens the file with it. They belong to this device —a Mac and an iPad don't have the same cores or memory— and don't travel with the rest of your preferences. With a database in memory there is no file to reopen, and they don't apply.

Maintenance runs Checkpoint —which folds the .wal into the file—, Force checkpoint, Vacuum and Analyze, and the engine's answer appears below the buttons. Like the settings, the buttons stay off while there are unsaved changes: save or revert first. The engine's other settings are listed under Read-only with their current value.

Keywords: settings, threads, memory, memory limit, maintenance, checkpoint, vacuum, analyze, wal

Import and export

Import CSV and export to CSV, JSON or SQL.

From Import / Export you can import a CSV file into a new table of text columns, export a table to CSV or JSON, or export the whole database as a SQL dump (schema and data).

This is different from Query data file…: here the CSV is copied into the .duckdb file as a new table; there the file stays where it is and is only queried through a view.

With DuckDB the CSV is read by the engine itself (COPY … AUTO_DETECT), so every column comes in with its own type rather than as text. And Whole database dumps the entire database to a folder you pick —as CSV or Parquet, with the schema beside it— or reads into the open database what another export left behind. If the database is in memory, Save As… writes it whole to a .duckdb file and keeps working on that file.

Exporting and importing the whole database require no unsaved changes: Calíope runs them outside the pending transaction, because a failure partway through —a table that already exists, a folder without permission— would cancel it along with everything you had not saved. Save As… does not ask for that: it commits what is pending first, and with a database in memory that touches no file.

Keywords: import, export, csv, json, sql, dump

Query data file…

Query a CSV, JSON or Parquet file as if it were a table, without copying it.

The Query data file… button creates, for each file you pick, a view over it — read_csv_auto, read_json_auto or read_parquet, by extension: the file stays where it is and is read on the fly every time the view is queried, without copying its data into the database.

The view's name comes from the file's name, without its extension; if two files would give the same name, the second one gets _2, the third _3, and so on. In Structure, these views carry a different icon — a paperclip — and their tooltip shows the source file's path.

If no database is open, the button first opens an in-memory database: there's no .duckdb file on disk, so Save As… is the way not to lose the work when closing: it writes it whole to a .duckdb file and the editor carries on with that file.

A Parquet file opened from outside Calíope —from the Finder with Open With › Calíope, or shared from the Files app— lands here too: it opens an in-memory database with the view over that file, and leaves SELECT * FROM read_parquet(…) LIMIT 100; written in Execute SQL, without running it.

iPad and Apple Vision Pro: the detail panel shows the source file too.

Keywords: csv, json, parquet, query, view, attach, in memory

Summary of a table (SUMMARIZE)

The Summary button in Data writes SUMMARIZE over the open table and runs it: one row per column with its type, range, distinct values, nulls and quartiles.

Where it is: Workspace › Tools › DuckDB Editor

In Data, with a table chosen, the Summary button writes SUMMARIZE <schema>.<table>; in the editor, switches to Execute SQL and runs it. The result has one row per column, with its type, minimum, maximum, approximate number of distinct values, percentage of nulls, mean and quartiles.

It is a DuckDB feature: the SQLite Editor does not draw the button.

Keywords: summarize, summary, statistics, columns, DuckDB