Wes E

Posted on Sep 30

I built a native DuckDB IDE in Rust, and the client is DuckDB too

#ide #duckdb #database #rust

Most SQL GUIs treat DuckDB as one more driver on a list. You get the SQL every
database shares, and DuckDB's best parts — FROM-first queries, DESCRIBE, table
functions, its own types and admin functions — come second.

I wanted a client that only speaks DuckDB, so I built one. DuckPlus is a free,
MIT-licensed Mac app written in Rust on gpui, the UI framework Zed is built with.
This post covers the parts that were interesting to build.

The client is DuckDB too

DuckDB now has its own client/server protocol, Quack. A server takes one line:

CALL quack_serve('quack:localhost', token := 'super_secret');

DuckPlus embeds an in-memory DuckDB whose only job is to speak Quack. When you
press Cmd+Enter, this is what goes over the wire:

SELECT * FROM quack_query(
  'quack:db.internal:9494',
  '<your sql>',
  token := '...'
);

The server's own parser handles your statement, DDL and multi-statement scripts
included. Results come back in DuckDB's vector format, not through a generic
driver. I use quack_query instead of ATTACH 'quack:...' because attaching still
has catalog gaps.

Local .duckdb files are opened by the same embedded engine, so a file and a
server behave the same way in the app.

Three more choices keep the UI responsive:

Schema lookups run on their own connection, so the tree never waits behind a long query.
Every query runs on a fresh clone of the connection, so a cancelled one can't block the next.
Each new query window gets its own connections to the same server.

A cell becomes text only when it's on screen

Results stay as the Arrow batches DuckDB returned. The grid is virtualized: it
builds only the rows you can see, and formats only the cells in them. Rows you
never scroll to are never turned into strings.

The grid stops pulling batches at 10,000 rows by default, and the status bar
tells you when it hits the limit. Sorting knows what it has: if every row is
loaded it sorts in place, and if not, it asks the server again with ORDER BY.

Editing without surprises

You can double-click a cell and type. Edits are staged until Cmd+S, and then
they all go in one transaction. The hard part is making sure an UPDATE changes
exactly the row you meant. So every UPDATE is paired with a check:

BEGIN TRANSACTION;
SELECT CASE WHEN count(*) <> 1
  THEN error('Expected 1 row where "id" = ...') END
FROM "analytics"."main"."users" WHERE "id" = '10437';
UPDATE "analytics"."main"."users"
SET "plan" = 'team', "mrr" = '49'
WHERE "id" = '10437';
COMMIT;

If the key isn't really unique, or someone deleted the row in the meantime, the
check raises an error inside the transaction and nothing is saved.

Rows are found by the primary key, then a UNIQUE constraint, then a column you
pick. DuckLake tables declare no keys, so there you pick one. A plain
SELECT ... FROM one_table is editable; joins, GROUP BY and CTEs make the result
read-only.

Small guardrails

DROP, DELETE and TRUNCATE need a second Cmd+Enter. The check matches whole words, so a column called dropped_at doesn't trigger it.
Every saved connection has a colour, shown in the title bar. Red means production.
Tokens go to the macOS Keychain, not to a file.

Admin screens are just queries

The sidebar has seven server views: databases, storage, extensions, settings,
memory, secrets and running Quack servers. Clicking one puts its SQL in the
editor and runs it. For example, Extensions is:

SELECT extension_name, loaded, installed, extension_version, install_mode, description
FROM duckdb_extensions()
ORDER BY loaded DESC, installed DESC, extension_name;

You can see it, change it and keep it.

What it doesn't do (yet)

Quack is beta until DuckDB 2.0 and has no remote cancel. Cmd+. frees the window, but the server finishes the statement.
It's DuckDB only, on purpose.
It's Mac only for now (macOS 12+, Apple silicon and Intel). gpui runs on Windows and Linux, but the installer and bundle don't yet.

Try it

Download: https://duckhouse.co/duckplus
Source: https://github.com/wes/duckplus

If you want a hosted server to point it at, every DuckHouse database is a Quack
server. That's what I work on day to day. But DuckPlus works with any
quack_serve, including one on your laptop.

Issues and PRs are welcome. I'd especially like to hear from people using
DuckLake or very large local files.

DEV Community