// ZDBVIEW — RKYV + SQLITE TERMINAL EDITOR 🗄️

Quickstart

# install (binary + both man pages + zsh completion)
brew tap MenkeTechnologies/menketech
brew install zdbview

# or from crates.io (binary only)
cargo install zdbview

# or build from source
git clone https://github.com/MenkeTechnologies/zdbview
cd zdbview && cargo build --release

# open a database — kind is detected from the header magic
zdbview app.sqlite               # SQLite  → full CRUD
zdbview ~/.cache/zshrs/scripts.db  # rkyv   → key/value records (despite the .db name)

# force a backend
zdbview --sqlite file
zdbview --rkyv   file

# no argument → pick from recently opened files
zdbview

File type detection

The first 16 bytes are compared against the SQLite header magic SQLite format 3\0. When a full header is readable the magic decides alone — its absence means the file opens in the binary inspector, whatever the extension says.

Trusting the magic over the name is the whole point: rkyv cache shards are routinely written under a .db name, and an extension-first reader hands those to SQLite, which then reports a corrupt database for a perfectly valid archive.

SQLite — generic CRUD

The left pane lists tables and views from sqlite_master; the right pane shows one 500-row page of the selection. Every edit and delete addresses its row by rowid, selected alongside the real columns as SELECT rowid, * FROM <table> — which is what makes the CRUD generic: no schema knowledge, no primary-key guessing, no per-database configuration.

Identifiers are double-quoted with internal quotes doubled, so table and column names containing spaces or keywords work. A WITHOUT ROWID table has no generic row handle, so its rows are listed read-only. Values display by type: integers and reals verbatim, text as UTF-8, NULL as NULL, blobs as <blob N bytes>.

Large databases

Nothing the grid does waits on a scan of the whole table. Measured on a 23 GB SQLite file with a 300 MB WAL, 6.5M rows in the table being browsed and a filter matching 270k of them — development build on both sides, so the ratios are what to read:

Pages, exact counts and n/N searches each run on a worker with its own read-only connection, so a scan cannot block the display — and browsing a database another process is writing can never make zdbview checkpoint its WAL. Every request carries a generation, and each connection runs a progress handler that abandons its statement once that generation is stale, so typing a filter costs one query rather than one per keystroke.

The total is never scanned for: a page fetches one row more than it shows, which is all that is needed to know whether another page exists, and the title reads 501+ until the background count lands. Paging steps by cursor rather than by offset — LIMIT n OFFSET k makes SQLite walk and discard k matching rows, 6.2 s at offset 269000 against 26 ms at the start — and the last page is read backwards from the end. The exact count is taken on every core by cutting the table into rowid ranges; the cuts are arithmetic, because finding balanced ones means walking the whole index, which cost 5.8 s of cold reads on that file.

rkyv — recognized key/value, or structural fallback

An rkyv archive is a memory image of a Rust value plus a root pointer. The format stores no field names, no type tags and no embedded schema — the originating Rust type is the schema. zdbview closes that gap with a format registry: for archives it recognizes it carries a faithful, byte-compatible copy of the producer's archive type, detects the format by its magic header, validates with rkyv::check_archived_root, and decodes real key/value records. Anything it does not recognize falls back to the raw structural views.

Recognized formats — every magic the registry knows, with the little-endian u32 as it appears in the header. The magic sits wherever rkyv placed the root, which is near the end of the file, not at offset 0:

Magic-bearing formats are matched by header; the header-less hash-keyed shards are attempted last and gated by rkyv validation, so an unrelated archive falls through to the structural view instead of mis-decoding. Adding a format is a single registry entry: copy the producer's archive type — same rkyv version (0.7) and features (validation, archive_le, size_32), same field order and types — and map its magic, or add a validated try-decode for a header-less one.

The Records view is editable: a add, e edit, r rename, d delete. Each change deserializes the shard, mutates the owned value, re-serializes the whole archive and swaps it in atomically (temp + rename). Because the registry types are byte-compatible with the producer, the re-serialized archive is byte-identical to what the producing host would have written, so it keeps reading the shard normally — verified by round-tripping every real cache. Unrecognized archives stay structural and read-only, because guessing field boundaries in an untyped image would produce plausible, wrong output.

Keys

Search

/ takes a plain substring (not a regex); n and N then walk the matches. What is scanned depends on the mode and, in SQLite, on which pane has focus.

Detail view

Enter on a row or record opens a full-screen detail: every field untruncated at the top, and a scrollable value pane below. v cycles how the value bytes render — auto (text when it looks textual, else hex), hex (xxd style), or text (UTF-8). y copies the value. This is how you read a cell or record blob too wide for the grid.

Export

Interactive x writes the current table (CSV) or all decoded records (JSON) to a file in the working directory. Non-interactively, --export takes the sqlite3 shell's output modes and --table restricts it to one table, which is what makes zdbview scriptable:

zdbview data.db --export json               # { table: [ {col: val, …}, … ] } for every table
zdbview data.db --export csv                # the first table as RFC-4180 CSV
zdbview data.db --export tsv                # .mode tabs — tabs and newlines escaped, never raw
zdbview data.db --export markdown           # .mode markdown — padded GitHub table
zdbview data.db --export line               # .mode line — one column per line
zdbview data.db --export insert             # .mode insert — one INSERT per row
zdbview data.db --export sql                # .dump — schema plus data, replayable
zdbview data.db --export csv --table users  # just that table
zdbview cache.rkyv --export json            # decoded records; each value blob as lowercase hex

CSV and JSON are emitted by a dependency-free writer in export.rs — quoting and escaping are covered by unit tests. --export never starts the TUI, so it is safe in a pipeline.

--export sql is .dump, and it — like insert mode — reads values from the database rather than from the grid, so a blob comes out as x'…' and a real keeps its decimal point. The display string for a blob is a description of it (<blob 2 bytes>), which is useless in SQL, so everything that writes SQL uses the exact form. Virtual tables are the case that makes a naive dump unreplayable: CREATE VIRTUAL TABLE runs the module's constructor and builds the shadow tables, which the dump would then create a second time. zdbview does what the shell does — writes the sqlite_schema row directly under PRAGMA writable_schema=ON and emits the shadow tables with CREATE TABLE IF NOT EXISTS — so an FTS5 index survives the round trip and still answers MATCH. Checked line-for-line against sqlite3 .dump: identical content, differing only in the order sqlite_master is walked.

--backup FILE copies a database with VACUUM INTO (the shell's .backup), the one consistent way to copy a file that may have writers. An existing target is an error, not an overwrite.

Which table is being written, live

In the write monitor, t swaps the bottom frame for a per-table breakdown of the bytes written since it opened. This is the thing no other SQLite tool shows: not how big a table is, but which one is being written to right now.

It works because a WAL frame header carries the page number it rewrote, and a page can be traced to the b-tree that owns it. Every table's and index's root page is walked straight out of the file to build a page → owner map. The file alone is the past in WAL mode — a table created but not yet checkpointed is not even in its schema — so the log's newest image of each page is applied over the file before the walk; without that, every page reads as unmapped. Each sample reads only the frame headers written since the last one, so the cost does not grow with the log's length, and the map is re-read when a checkpoint restarts the log or a frame names a page it does not cover.

written is the total since the monitor opened; rate is what the last sample attributed, so a table that has stopped reads as idle while its total stays — that pair is what answers "which table is being written fastest right now".

Indexes are named as indexes, and a page the map cannot place is shown as page N (unmapped) rather than being charged to a table it might not belong to. A database in rollback-journal mode gets no breakdown: a journal records the pages it is about to change, not the ones it did.

Walking the log

F in the monitor opens the log as a history. A frame does not merely say that a page changed — it carries the whole page image, so the rows that write put there are decoded and shown beside the frame list, labelled with the table's column names. j/k step one frame (down is back in time), [ and ] jump a whole transaction, g/G reach the newest and oldest frame, and / filters the log by table, page number or commit — stepping then walks only what is listed, which is what makes a log of tens of thousands of frames usable.

A value that continues onto an overflow page says so rather than showing a truncated string — the rest is in a different frame — and a page that is not a table leaf (interior node, index page, overflow) is named as what it is instead of pretending to hold rows. This is the only view that reads frame payloads, so it decodes one frame at a time on a keypress: a 190 MB log holds tens of thousands of frames and each payload is a whole page.

Tabs, files and results in the editor

The rest of DB Browser's Execute SQL tab, on the Alt chords so every printable key stays part of the statement: Alt-t/Alt-w open and close a tab, Alt-[/Alt-] move between them, Alt-o/Alt-s load and save a .sql file, Alt-l runs only the line the cursor is on, Alt-; comments it, Alt-f/Alt-r find and replace inside the statement, Alt-x exports the last result set as CSV or JSON by extension, Alt-v saves the statement that produced it as a view, and Alt-W wraps the transcript. Each tab keeps its own statement, transcript and file, and the tab strip is the pane's title.

A statement runs on the thread that draws, so nothing else can watch the keyboard while it does — SQLite's progress handler is the one place that gets control back periodically, so that is where the stop key is read. It polls without blocking every few thousand VM instructions, and Esc aborts the statement; the transcript then says it was stopped rather than that it failed.

Dot-commands in the editor

A line starting with . is a dot-command, as in sqlite3; Tab completes them and .help lists what is implemented: .tables, .schema, .indexes, .dump, .databases, .attach/.detach, .mode, .headers, .output/.once, .timer, .eqp, .import, .read, .backup, .expert, .vacuum/.analyze/.reindex, .quit. .mode renders a result set through the same writers --export uses; list is the grid the editor draws.

.expert is not sqlite3_expert — rusqlite exposes no binding for it. It reads the plan the planner actually produced plus the columns the statement compares on, and names an index for each table chosen for a full scan. A column is attributed to a table only when no other table in the statement has one by that name, so an ambiguous join reports the scan without guessing.

What the grid shows

The Browse Data settings DB Browser keeps per table, kept the same way: H hides the cursor column and U brings them all back, f freezes the columns up to the cursor at the left edge, # shows the rowid as a leading column, and m/M step the column's display format. A wide table scrolls sideways under the cursor while the frozen columns stay put; a frozen column is marked ▏ in its header and a formatted one ƒ.

The nineteen display formats are DB Browser's: decimal, exponent, hex blob, hex number, octal, round, lower and upper case, dd/mm/yyyy, julian day, unix epoch (UTC and local), Apple NSDate, Java milliseconds, WebKit/Chromium microseconds (UTC and local), the Windows OLE date, and DB Browser's Set encoding pair — text as latin-1 and as windows-1252 — plus a custom expression, where %1 stands for the column.

A format is SQL, not a rendering rule, which is how DB Browser does it and what makes hex blob possible at all: the grid receives cells that are already display strings, so a blob has become <blob 12 bytes> before anything could format it. The column keeps its own name in the result, so sorting, filtering, paging and editing still address the raw value — a hex-formatted integer column sorts 1, 2, a, not the 1, 10, 2 that sorting the displayed strings would give. Two of the formats are DB Browser's Set encoding: they read the stored bytes as ISO-8859-1 or Windows-1252 rather than UTF-8, which SQLite cannot do itself — the value comes back as hex() and is decoded on this side, the only way to read bytes that are not valid UTF-8 at all. SpatiaLite Geometry to SVG is the one DB Browser format not implemented; it needs the SpatiaLite extension loaded to mean anything.

Files, extensions and projects

zdbview --new fresh.db          # create an empty database and open it
zdbview --memory                # a database that never reaches a disk
zdbview data.db --readonly      # a read-only connection: every edit is refused
zdbview data.db --import-sql schema.sql        # run a script in, and exit
zdbview data.db --load-extension ./ext.dylib   # load an extension first
zdbview --project session.zdbp  # open the database a project names, as it was left

--readonly opens a different connection rather than checking a flag at the edit, so no key handler can write through it by accident. --import-sql runs the whole script inside one savepoint, so a script that fails half way leaves the database exactly as it was. --new refuses to overwrite an existing file and forces a page out, so what it made is a database rather than an empty file. In the editor, .load FILE loads an extension; O on the database report runs PRAGMA optimize and lists what SQLite decided was worth doing; Ctrl-p prints the table by piping it to lpr.

.project save FILE and .project open FILE write and read a project — everything about a session that is not in the database: what each grid is set to show, what is filtered and sorted, and the statements left in the editor's tabs. DB Browser writes XML; zdbview writes one tab-separated directive per line, so a project can be read and edited by a person, and a directive this version does not know is skipped rather than refused.

Conditional formats

! manages the rules that paint a column's cells when their value matches — DB Browser's Conditional Formats. a adds a rule, Enter edits its condition, c cycles the colour, b toggles bold, d drops it and J/K reorder; the first rule that matches wins, so the order is part of the meaning.

A condition is DB Browser's filter vocabulary: an operator and a value (> 5, = done, <> 0, like a%), null / not null, or bare text meaning "contains". A comparison is numeric when both sides read as numbers and textual otherwise, so > 9 matches 10 — which comparing the strings would not. DB Browser's font and alignment settings have no meaning in a terminal grid; colour and bold do.

Working on the data

r finds and replaces in the cursor column, asking in two steps because a terminal has one prompt line — and counting between them, so a term that matches nothing never reaches the second question. It works over the rows the active filter leaves, matches substrings, and lands in the edit buffer like every other write, so R takes it back.

i inserts a row value by value — it exists next to a because a column starts NULL and stays there unless something is typed, so NULL and the empty string stay apart; n puts a column back to NULL. V saves the current filter as a view, writing its patterns in as literals since a view cannot carry parameters. z and Z clear the sorting and the filter, and Ctrl-y copies the column's name.

L unlocks a view for editing, and reports what SQLite would do rather than pretending the lock is zdbview's to lift: a view can only be written to through INSTEAD OF triggers, so editing one without them is refused with that reason.

Editable pragmas

D prints the pragmas that describe a file; P edits the other kind — the eighteen settings DB Browser puts in its Edit Pragmas tab, which decide how the database is written rather than what is in it: auto_vacuum, automatic_index, case_sensitive_like, checkpoint_fullfsync, foreign_keys, fullfsync, ignore_check_constraints, journal_mode, journal_size_limit, locking_mode, max_page_count, page_size, recursive_triggers, secure_delete, synchronous, temp_store, user_version and wal_autocheckpoint. j/k select, Space cycles a flag or a named set, Enter types a number.

Every change is read back, because SQLite accepts a pragma it will not apply rather than raising: journal_mode will not change inside a transaction, max_page_count clamps to the pages already in use, page_size and auto_vacuum only take effect on the next VACUUM, and an unrecognised value is ignored outright. What the screen shows is what the database reported afterwards. case_sensitive_like has no query form at all, so it shows a dash until this session sets it.

Writing and reverting

Every edit — a cell, a row, an import, a schema change, a statement typed into the editor — is buffered until it is written: changes belong to the session, not to the file, until W (or Ctrl-s) commits them. R throws them all away and puts the database back to what it was when the first unwritten edit was made. Both are one savepoint, so a schema edit and a hundred cell edits revert together. The status line carries a marker while anything is unwritten, and leaving the file asks first, because closing the connection would roll the savepoint back.

Pages normally come from reader threads on their own connections, and no connection can see another's open transaction — so while anything is pending the page is fetched on the store's own connection instead, or an edited cell would still show its old value. VACUUM, ANALYZE, REINDEX and --backup refuse to run with unwritten changes: they rewrite the whole file and cannot run inside a transaction. The one-shot command-line paths write before exiting, since there is no user there to ask.

Schema editing

S lists every object with its CREATE statement — DB Browser's Database Structure tab — and the cursor selects one: Enter opens the designer for a table or an index, a starts a new table, i a new index on the selected table, d drops the object after asking, y copies its statement, and R counts the rows in every table and view — DB Browser's Row counts — with a second press dropping them again.

Both designers are grids of fields with the SQL they will run shown underneath: arrows move by row and field, Enter edits a field, Space toggles a flag, a/d add and drop a column, J/K reorder, W writes and Esc cancels. The table designer covers type, PRIMARY KEY, AUTOINCREMENT, NOT NULL, UNIQUE, DEFAULT, COLLATE, CHECK and REFERENCES, plus WITHOUT ROWID and STRICT.

The definition is parsed from the object's own statement, never from the pragmas: PRAGMA table_info cannot see COLLATE, CHECK, UNIQUE, REFERENCES or a generated expression, so a rebuild driven by it would silently drop all five.

Only renaming the table, renaming a column, appending one and dropping one are native ALTER TABLE; anything else is the rebuild SQLite documents — copy into a new table, drop the old one, rename the new one into place, put the indexes, triggers and views back. It runs as one transaction with foreign keys off and PRAGMA foreign_key_check before the commit, so an edit that would orphan a child row is refused and changes nothing. Views and triggers are dropped before the swap because the rename validates the whole schema, and one pointing at the dropped table fails it.

Following a foreign key

F on a foreign-key column jumps to the row it references — the navigation a schema gives you for free, and what DB Browser and Datasette both link. The parent column comes from PRAGMA foreign_key_list, resolved to the parent's primary key when the pragma leaves it empty; a dangling key, a NULL key and a non-key column are each reported rather than followed.

Column statistics and frequency

A describes every column of the current table — rows, nulls, distinct values, how many cells SQLite actually stores as numbers, the longest value, the extremes, and the mean of the numeric cells. That numeric count is the only place a column declared INTEGER but holding text shows up, because a declared type in SQLite is an affinity hint rather than a constraint. Enter on a column adds its most common values with bars, so a glance says whether the column is skewed.

Every column costs one pass over the table, and a wide blob column costs more than a second of it on a real database — 1.6s for count(DISTINCT body) across 46,716 rows of a 196 MB cache. The pass therefore runs on its own connection on a background thread: the screen opens saying analyzing … and fills in when the work lands, so the UI never blocks.

Import

--import rows.csv --table people loads a CSV (or TSV, chosen by extension) into an existing table — the shell's .import. The header row names the columns, so a file ordered differently from the table still lands correctly, and a header naming a column the table does not have is an error rather than a silent drop. The whole file is one transaction, so a row with the wrong field count rolls all of it back. The reader is RFC 4180 by hand, like the writers: quoted fields may hold the separator, newlines and doubled quotes, and CRLF or a missing final newline are both accepted.

Recovery

--recover (and .recover in the editor) salvages what a file still holds by reading pages — it never opens the database, so it works on a file SQLite refuses, and it walks every page rather than only those reachable from sqlite_master, which is what brings back rows a corrupt b-tree root has orphaned.

Measured against a 400-row database with its table root zeroed — SQLite answers database disk image is malformed — sqlite3 .recover emits 400 inserts and so does this, and the replayed script holds data identical to the original. Truncated to 20 of 35 pages, both recover the same 226 rows. A 20 KB value spanning overflow pages comes back whole.

Rows on pages that cannot be attributed to a table go to a lost_and_found table carrying the page each came from, and every assumption the pass made is written into the script as a comment. Indexes are not recovered — the script replays their CREATE INDEX statements — and the file is never modified.

A WITHOUT ROWID table keeps its rows in an index b-tree, so three page kinds are read, not one: table leaves, index leaves (where the key record is the row) and index interiors, which unlike table interiors carry key payloads of their own. Index cells also keep far less of their payload on the page than table cells, and the two size formulas are not interchangeable. Measured on a 300-row keyed table with its root zeroed: sqlite3 .recover brings back 298 rows and so does this, replaying to key-0000…key-0299.

Clipboard

y copies through the OSC 52 terminal escape, written straight to /dev/tty. That means no clipboard library, no X/Wayland/pbcopy dependency, and it works over SSH — the terminal emulator on your side sets the clipboard. It is best-effort: with no controlling terminal (output redirected), the copy silently does nothing.

Recent files

Every successful open is recorded to $XDG_CACHE_HOME/zdbview/recent (or ~/.cache/zdbview/recent), one tab-separated line per entry: unix-seconds kind absolute-path. Paths are canonicalized, so the same file reached by different relative paths or symlinks dedupes to a single entry that moves back to the front on re-open. The list is capped at 50 and written via temp-file-plus-rename, so a concurrent reader never sees a half-written list. The recorded kind is the one actually used — a .db file that turned out to be an archive is remembered as rkyv.

CLI reference

Man pages ship in the repo: man/man1/zdbview.1 and the all-in-one man/man1/zdbviewall.1. The zsh completion is completions/_zdbview.

DB Browser for SQLite parity

The comparison below is against the action set taken out of DB Browser for SQLite 3.13.1 itself — the setObjectName strings in its binary — rather than from its documentation, so it is what the program actually has.

Ported: new / in-memory / read-only databases, attach and detach, write and revert changes, compact, export to CSV / JSON / SQL, import from CSV and from a SQL script, projects, recent files; create and modify table, create index, delete object, copy CREATE statement, row counts, refresh; the Browse Data tab — per-column filters, sorting, hide / show / freeze columns, the rowid column, display formats including a custom expression, conditional formats, find and replace, insert values, set to NULL, copy column name, copy with hex/ASCII, open in external application, save filter as view, unlock view editing, clear filters and sorting; Edit Pragmas; the Execute SQL tab — tabs, open and save .sql, execute all, execute current line, stop, toggle comment, find and replace, word wrap, export results, save results as view; integrity check, quick check, foreign-key check, optimize, load extension, print.

Repository & links

• Source — GitHub repo
• Crate — crates.io (cargo install zdbview), API docs on docs.rs
• Engineering report — module map, dependency footprint, verification
• Issues — issue tracker
• Full README — README on GitHub
• Format reference — the rkyv archive format, the reason the rkyv side is read-only