Datascope

Datascope is a desktop database client — think of it as your own DataGrip or TablePlus, built as an Electron app. You register connections to your databases, browse what's inside them (schemas, tables, collections, indexes, documents), write and run queries in a Monaco-powered editor (the editor that powers VS Code), page through results in a fast data grid, and export any result set to CSV or JSON.

The twist that makes it interesting: Datascope speaks to three very different engines — PostgreSQL (relational, SQL), MongoDB (document store, BSON queries), and Typesense (search engine, JSON search API). A relational table, a Mongo collection, and a Typesense collection are cousins, not twins — so the heart of the project is a driver abstraction: one interface (connect, introspect, runQuery, streamRows) with three adapters behind it, letting the entire UI — connection tree, editor tabs, results grid, export dialog — stay engine-agnostic. The app's own state (connections, query history, saved queries, UI preferences) lives in a local SQLite database managed with Drizzle ORM, so Datascope is itself a nice little database app about database apps.

The goal of the hackathon project is to build a working slice of this tool: an Electron app where a user adds a connection, explores the database's structure in a sidebar tree, writes a query in a Monaco editor tab, sees results in a paginated grid, and exports them to CSV or JSON — with all three engines working through the same abstraction.

What you are building

At a high level, a user of Datascope should be able to:

1. Manage connections — add, edit, test, and delete connections for PostgreSQL, MongoDB, and Typesense; credentials stored encrypted on the local machine, metadata in the app's SQLite store.
2. Explore structure — a sidebar tree per connection: Postgres schemas → tables → columns and indexes; Mongo databases → collections with inferred field shapes; Typesense collections with their field schemas. Click a table or collection to peek at its data instantly.
3. Write queries — open editor tabs powered by Monaco: SQL for Postgres (with schema-aware autocomplete), JSON-based query documents for MongoDB (filter / aggregation pipeline), and search parameter JSON for Typesense — each tab bound to a connection.
4. Run & read results — execute with Cmd/Ctrl+Enter, get a virtualized results grid with column types, row counts, execution time, pagination for large result sets, and readable rendering of nested documents.
5. Export — send any result set (or a whole table/collection) to a CSV or JSON file on disk, streamed so a million rows doesn't freeze the app or exhaust memory.
6. Keep history — every executed query is recorded (per connection, with timing and row counts) in the local SQLite store; re-run or pin favorites as saved queries.

Architecture overview

Datascope follows Electron's two-world split: the renderer (Chromium) owns the UI and never touches the network or filesystem; the main process (Node.js) owns database drivers, the SQLite app store, credentials, and file exports. A typed IPC bridge connects them, and a driver registry in the main process hides the differences between the three engines behind one interface.

The pieces

1. The renderer (React UI) Everything the user sees: a left sidebar tree of connections and their structure, a tab strip of Monaco editors, a results pane with a virtualized grid, and dialogs for connections and exports. Crucially, the renderer is a pure client — nodeIntegration off, contextIsolation on — that talks only to the preload bridge. It cannot open sockets or files itself, which is both Electron security best practice and what keeps the UI engine-agnostic.

2. The preload bridge (the API boundary) A preload script exposes a small, typed API to the renderer via contextBridge: connections.list(), db.introspect(connId), db.runQuery(connId, query), db.fetchPage(cursorId, page), export.start(cursorId, format, path). Request/response calls use ipcRenderer.invoke; long-lived operations (query progress, export progress) push events back over IPC channels. Treat this bridge like a public API: validate every payload with Zod on the main side.

3. The driver abstraction (the heart of the project) One TypeScript interface, three implementations:

interface DatabaseDriver {
  connect(config: ConnectionConfig): Promise<void>
  testConnection(config: ConnectionConfig): Promise<TestResult>
  introspect(): Promise<StructureTree> // schemas/tables/columns or dbs/collections/fields
  runQuery(query: EngineQuery): Promise<ResultHandle>
  fetchPage(handle: ResultHandle, page: number): Promise<ResultPage>
  streamRows(handle: ResultHandle): AsyncIterable<Row[]> // for exports
  cancel(handle: ResultHandle): Promise<void>
  disconnect(): Promise<void>
}

The design work is deciding what the common shapes are — StructureTree, ResultPage with typed columns, an EngineQuery that can be SQL text or a JSON document — so the UI renders all three engines without if (engine === ...) branches everywhere. Where engines genuinely differ (Mongo has no fixed columns; Typesense results carry relevance scores and facets), the shapes carry optional, declared capabilities rather than leaking driver details.

4. The three adapters

• PostgreSQL (pg) — introspection via information_schema / pg_catalog (schemas, tables, columns, types, indexes, row estimates); queries are raw SQL; large results read through a cursor (pg-cursor) instead of buffering everything.
• MongoDB (mongodb) — introspection via listDatabases / listCollections plus field-shape inference (sample ~100 documents and merge their key/type structure — Mongo has no schema to read); queries are JSON documents (find filter + projection/sort/limit, or an aggregation pipeline); results stream via async-iterable cursors. Columns for the grid are derived from the result documents themselves.
• Typesense (typesense) — introspection via the collections API (fields are declared, so this one does have a schema); queries are search parameter objects (q, query_by, filter_by, facet_by); results are hits with scores and facet counts, paginated by the engine natively.

5. The app store (SQLite + Drizzle) Datascope's own memory, in a SQLite file under Electron's userData directory, accessed with better-sqlite3 and modeled with Drizzle: connections (name, engine, host, database — everything except secrets), query_history (connection, query text, started/duration, row count, status), saved_queries, and preferences. Drizzle migrations run at app startup, and drizzle-kit gives you generated types across the IPC boundary for free. Passwords and API keys never enter SQLite — they're encrypted with Electron's safeStorage (OS keychain-backed) and referenced by ID.

6. Monaco editor integration Monaco ships SQL syntax highlighting out of the box; the upgrade that makes Datascope feel like DataGrip is a completion provider fed by introspection: table names after FROM/JOIN, column names once a table is in scope, all served from the cached StructureTree. Mongo and Typesense tabs use Monaco's JSON mode with a JSON schema attached, so filters and search parameters get validation and key completion. Each tab persists its content and cursor position to the app store so a restart restores your workspace.

7. The export engine Exports run entirely in the main process: pick a result (or a whole table/collection), a format, and a destination; the engine pulls rows through the driver's streamRows async iterable and pipes them through a CSV serializer or a JSON array/NDJSON writer into a file stream — constant memory at any row count, with progress events streamed to the renderer and cancellation support. Nested documents flatten to JSON strings in CSV cells; JSON export preserves them natively.

How a query flows

Understanding this end-to-end path is the heart of the project:

1. Connect — the user adds a Postgres connection. The renderer sends the form to the main process, which validates it, tests the connection, saves metadata to SQLite via Drizzle, and encrypts the password with safeStorage.
2. Introspect — expanding the connection in the tree triggers db.introspect: the Postgres adapter queries information_schema, returns a StructureTree, and the renderer renders schemas → tables → columns. The tree is cached and feeds Monaco autocomplete.
3. Write — the user opens an editor tab and types SELECT * FROM orders WHERE status = 'pending', with table and column names offered by the completion provider.
4. Run — Cmd+Enter sends the query over the bridge. The main process logs a query_history row, and the adapter executes it with a cursor, returning a ResultHandle plus the first page and column metadata.
5. Render — the grid shows page one (typed columns, execution time, row count); scrolling or paging requests further pages through fetchPage — the renderer never holds more than a window of rows.
6. Export — the user clicks Export → CSV. A save dialog picks the path; the export engine re-runs the query with streamRows, pipes rows through the CSV serializer to disk, and streams progress until done.
7. Repeat, differently — the same user adds a MongoDB connection and runs { "filter": { "status": "pending" } } against a collection in a JSON tab. Different engine, different query language — identical tree, grid, history, and export experience, because everything past the adapter is shared.

Technologies to use

The whole stack is TypeScript / JavaScript end-to-end — pick what your team is fastest in within that ecosystem. The concepts matter more than the exact library.

Concepts to understand

Before writing code, make sure the team is comfortable with these ideas.

Electron's process model & security

• Main vs. renderer vs. preload — Node.js capabilities live in main; the renderer is a browser; preload is the narrow doorway between them.
• Context isolation — why contextBridge + ipcRenderer.invoke is the pattern, and why nodeIntegration: true would be a critical mistake in an app holding production database credentials.
• IPC design — request/response (invoke/handle) vs. push channels for progress events; keeping payloads serializable (BSON types like ObjectId and Date need explicit mapping).
• Where app data lives — app.getPath('userData') for the SQLite file; safeStorage for anything secret.

Designing the driver abstraction

• Common denominator vs. capabilities — model what all three engines share (structure tree, tabular pages, streaming), and expose genuine differences (facets, relevance scores, no-schema collections) as declared capabilities the UI can check — not as leaked driver types.
• Three query paradigms — SQL text vs. Mongo filter/aggregation documents vs. Typesense search parameters; one EngineQuery union type covers them.
• Schema vs. schemaless vs. declared — Postgres tells you its schema, Typesense declares one, Mongo makes you infer one by sampling. The structure tree must represent all three honestly.
• Read-only by default — a hackathon database client pointed at real databases should treat mutating statements with respect: at minimum, surface affected-row counts clearly; ideally, a per-connection read-only flag.

Working with each engine

• Postgres introspection — information_schema.tables/columns, pg_indexes, pg_class.reltuples for fast row estimates; why SELECT count(*) on a big table is a trap.
• Mongo cursors & BSON — async-iterable cursors, ObjectId/Decimal128/Date serialization across IPC, and deriving grid columns from heterogeneous documents.
• Typesense's model — collections with typed fields, query_by restrictions, facets, and per_page limits; a search engine paginates for you but caps depth.

Large data in a desktop UI

• Cursors, not buffers — never SELECT * a million rows into an array; hold a server-side cursor and fetch pages.
• Virtualized rendering — the grid renders only visible rows; the renderer holds only a window of pages.
• Streaming exports — an async-iterable pipeline from driver to file stream with backpressure, progress reporting, and cancellation.
• Query cancellation — killing an in-flight query (Postgres pg_cancel_backend / cursor close, Mongo killCursors) so a bad query doesn't hold a connection hostage.

The local app store

• SQLite as an application database — synchronous better-sqlite3 in the main process, schema owned by Drizzle, migrations applied at startup.
• Modeling with Drizzle — tables for connections, history, saved queries; inferred types shared with the renderer through the IPC contract.
• What never goes in it — credentials. Metadata in SQLite, secrets in safeStorage, joined by ID at connect time.

Suggested milestones

A realistic path for a hackathon, from smallest useful slice to full loop:

1. Shell + store — Electron app (main/preload/renderer wired with context isolation), SQLite + Drizzle initialized with migrations, and a connections CRUD UI: add/test/delete a Postgres connection with the password in safeStorage.
2. Postgres end-to-end — define the DatabaseDriver interface, implement the Postgres adapter (introspect + query + pages), render the sidebar tree, and run a hardcoded-textarea query into a virtualized grid. (The app is already useful here.)
3. Monaco — replace the textarea with Monaco tabs: SQL highlighting, Cmd+Enter to run, schema-aware autocomplete from the introspection cache, and query history recorded and re-runnable.
4. Second engine: MongoDB — the adapter with collection listing, field-shape inference by sampling, JSON query tabs (find + aggregation), and document-aware grid rendering. This milestone stress-tests the abstraction — expect to revise the interface.
5. Third engine: Typesense — the search adapter (collections, search parameter tabs, hits with scores and facets). Should be markedly easier if milestone 4 fixed the abstraction.
6. Export & polish — streaming CSV / JSON / NDJSON export with progress and cancel, table/collection quick-peek from the tree, and workspace restore (open tabs and their content survive a restart).

Stretch goals

• Inline data editing — edit a cell in the grid and write it back (UPDATE / updateOne / Typesense document upsert) with a confirmation diff.
• EXPLAIN visualizer — render Postgres EXPLAIN (FORMAT JSON) as a readable plan tree with cost highlights.
• ERD view — draw Postgres foreign-key relationships as an entity diagram from introspection data.
• SSH tunnels — connect to databases reachable only through a bastion host.
• Import — the reverse pipeline: CSV/JSON file → table or collection, with column mapping.
• Cross-connection query history search — full-text search over everything you've ever run (SQLite FTS5 over the history table).
• AI assistance — natural language → SQL/filter generation using the introspected schema as context, rendered as an editable draft, never auto-executed.
• More engines — the payoff of the abstraction: a Redis or ClickHouse adapter as a demo of "add an engine in an afternoon."