defuss-shadcn / Application / Scaffolds / scaffold-db-explorer
Database Explorer
A database tool as a working app, in the manner of MongoDB Compass and Robo 3T, over defuss-db 3.1.0. The databases and their collections are a Data Tree; a collection opens with a query bar that reads MongoDB's filter language ({ country: 'DE', amount: { $gt: 500 } }), and every document it finds is an editable Property Tree - or a row of an editable Data Grid. Aggregations, a sampled schema, indexes, the query history and a log of every operation sit around it. Full screen, the data lives in this browser's Origin Private File System and is still there next time. Part of the Application Scaffolds.
On this page (2)
§Database explorer
Pick a collection on the left (shop / orders is the big one), type a filter such as { status: 'shipped', amount: { $gt: 500 } } and press Find. Change a value in a document and Save it; switch to Table to edit cells in place. Ctrl+Space suggests fields, operators and values. Aggregations groups by text, dates and array elements and measures, Export / Import move a collection as JSON, NDJSON or CSV (gzip or Brotli), Schema samples the field types, Indexes creates and drops them - watch the query plan in the result line change. Here in the docs the database runs in memory; full screen it is kept in this browser (OPFS).
§How it works
A page, not a component: it composes the library around defuss-db and keeps its own glue in one script.
- The database. defuss-db 3.1.0 stores collections as DBSONL pages in the browser's Origin Private File System. Its
openOPFSruns only in a dedicated worker (it needs OPFS sync access handles and Web Locks), so the page talks to a worker throughjs/db/db.js:connect()starts it and returns one call per operation - find, aggregate, insert, put, update, delete, create and drop an index, export and import. A docs card is an opaque-origin sandbox without OPFS: there the same operations openopenMemoryon the page, and the badge in the toolbar says which one runs. The worker and its packages are bundled byscripts/build-db.ts(a module worker reads no import map of its page). - Databases and collections. A Data Tree lists them with their document counts; its filter finds a collection by name. The + in its title bar creates a database, a database row's + a collection in it and a collection row's + inserts documents (+ on a focused row does the same). The insert dialog takes the shell's relaxed JSON, coloured as it is typed (a transparent field over its painted twin, so caret, selection and undo stay the browser's), or builds the record from scratch in a Property Tree - its Tree tab; a switch carries the value over, and a text that does not parse stays with the reason under it. A database's page is the table of its collections; Drop database and Drop collection sit at the end of the title bar of the database or collection shown. defuss-db has no drop for a collection or a database: a drop deletes the documents and indexes and records the name in the
admindatabase, which also lists the databases - MongoDB's own place for them. - The query bar. Filter, sort and projection are MongoDB documents as the shell types them - unquoted keys, single quotes,
ISODate(),NumberLong().$eq $ne $gt $gte $lt $lte $inon a top-level field become defuss-dbfilters, which an index can answer;$or $and $nor $not $nin $existsand dot paths ('address.city') become onewhereexpression;$regextakes a prefix (^Al), a suffix (son$) or plain text, with$options: 'i'in any case. Arrays match as in MongoDB: a condition matches an array when one of its elements does, a dot path reaches through arrays of documents, and$all,$sizeand$elemMatchtest the array itself;$type,$modand the$bitsoperators test a value. A condition on a top-level field stays one an index answers only when an index covers that field (an index holds no arrays). The result line names the access path fromexplain()- create an index on Indexes and watch it change from a full scan. Every query field completes as you type (runtime/db/complete.ts, a scan of the half-written text rather than the parser, which only sees an error there): a field where a key goes, the field's operators inside{ $ }(by its type - no$regexon a number), its values from a sample where a value goes; CtrlSpace asks, Tab takes one, CtrlEnter runs. Braces, brackets and quotes close themselves, a syntax error is named when the typing pauses, and the ? beside the filter lists the operators. - Documents. In the List view every document is one row - its id and its first fields - that opens (a native
<details>) into a Property Tree over a copy of it: a change marks the card modified, Save writes the whole document (put), Revert puts the copy back; Duplicate inserts it under a new id and opens the copy under its original, Delete asks first. Dates, BigInts and nested documents keep their types on the way through the worker (structured clone). The Table view is an editable Data Grid - a scalar cell writes one field (update); the JSON view shows the page as Extended JSON, coloured by token, with a Copy button above it. Scrolling to the end of the list loads the next page under it (wheel, touch, keys or the scroll bar - never a scroll a script or a focus makes); the toolbar at the foot keeps the range shown, the query plan and Previous / Next, where Next goes on after everything shown. - Aggregations. Keys and measures are built row by row, and each row offers what its field's type allows: text groups by its value or case-folded, a date by the start of its year, quarter, month, week, day, hour or minute or by a part of it - hour of day, weekday, day of month, month of year, day of year, ISO week - in a time zone (the Time zone field, UTC by default), an array by each element (as
$unwind); text and dates measure the number of distinct values, the distinct values themselves and their first and last, numbers also a sum, an average, the median, the 90th, 95th and 99th percentile (exact, interpolated between the closest ranks) and the standard deviation of the population or of a sample. The keys and these measures are defuss-db 3.1's (q.lower,q.dateTruncwith a time zone, a bin and a week start,q.datePart,q.each,q.countDistinct,q.distinct,q.median,q.percentile,q.stdDevPop,q.stdDevSamp): DataView's aggregate over the raw values, folded into the groups - the query cache and the query workers stay as they are. MongoDB pipeline, beside the filter, goes the other way: a pasted pipeline ($match,$unwind,$groupwith_ida field, a document of fields,$toLower/$toUpper,$dateTruncwithtimezone,binSizeandstartOfWeek(MongoDB's weeks start on Sunday unless it says otherwise, and so do the builder's rows then),$dateToString,$year,$hour,$isoDayOfWeek,$dayOfMonth,$month,$dayOfYearor$isoWeek, accumulators$sum,$avg,$min,$max,$stdDevPop,$stdDevSamp,$median,$percentileof 0.9, 0.95 or 0.99,$addToSetand$sizeof it,$sort,$limit,$count) becomes the builder's rows and runs; a stage the builder has no place for ($lookup, a$matchafter the$group) is named instead of being run differently (runtime/db/pipeline.ts). The dialog starts from the builder's own aggregation written as a pipeline. - Schema, Indexes. Schema samples 500 documents and lists every field path with its types and how often it appears; the completion and the aggregation builder read their types from it. Its JSON Schema view shows defuss-db's
analyzeSchema: a JSON Schema (draft 2020-12) of 1,000 documents read in eight windows spread over the collection, coloured and with a Copy button - optional fields carryx-presence, Dates, BigInts, Maps and Sets keep a JSON type and name theirs inx-dson. As of reads the collection as it was then, which needs a collection that tracks history (the sample articles do; the other samples say they do not). Indexes lists the definitions and creates equality or ordered ones (status, createdAt:-1). - History and timestamps. A collection can keep every version of its documents (defuss-db's
$trackHistory) and stamp every write with_createdAtand_updatedAt($timestamps, defuss-db 3.1) - two switches in New collection, badges in the collection's title bar, and Track history / Add timestamps there for a collection that has neither yet. Both are one way: history starts with the documents as they are, stamps are written into the existing documents, and neither can be turned off again, so the switch asks first. defuss-db reports neither option; the explorer reads them from how the collection answers (audit()refuses without history, opening with$timestamps: falserefuses once they are on). In a collection that tracks history, a document's History lists its versions newest first - first version, written, deleted - each readable as Extended JSON and put back as the next version, a delete says it can be undone, and Restore brings a deleted document back by its id (restore()). The samplecms.articlestracks history and stamps its writes. - Around it. The Query history keeps each collection's queries in this browser (
df$.store.persisted); Keep pins one. The Operations panel logs every call with its time in milliseconds. Both start folded to their title bars (Paneldata-minimized, the Border Layout region folds with them), and the Databases panel folds the same way. Export writes a collection's documents as JSON, NDJSON or CSV - Brotli (defuss-brotli, the default), gzip (the platform's CompressionStream) or plain, all of them or only what the query bar finds - or a whole database as defuss-db's Brotli archive. Import reads either: an archive into a database, documents into a collection (the format from the name, else from the content; gzip from its first bytes), after a preview of what the file holds; a document whose id is there either stops that part of the import or replaces the document. Dates and BigInts survive JSON and NDJSON as Extended JSON; CSV flattens nested fields to dot paths and reads numbers, booleans and ISO dates back as such (runtime/db/transfer.ts). A file dropped on the app opens the import. Routes are#/,#/<db>and#/<db>/<collection>. - The sample data. On the first visit the app writes three databases:
shop(800 customers, 240 products, 12,000 orders with their items),analytics(30,000 events with a BigInt byte count) andcms(120 articles with comments) - deterministic, from a seeded random generator.
Comments, ideas or improvements? Edit this page's source on GitHub