Quarry

A VS Code extension that renders a PostgreSQL database as a navigable 3D scene, indexes every SQL statement written in your source code, and tells you which of them no longer match the schema they run against.

Most schema tools show you either the database or the code. The interesting bugs live between them — a column dropped in a migration six months ago that a reporting query still selects, behind a try/except that has been quietly swallowing the error ever since. Quarry reads both and joins them.

What it does

Walks the database in 3D. Database → schema → table → column, each level a camera move rather than a new window. Tables are rendered as cards showing every column with its type and constraints; foreign keys are arcs that terminate on the exact column they reference, not merely on the target table.

Indexes the SQL in your codebase. Quarry finds SQL in tagged template literals, driver call arguments, heredocs, verbatim strings and .sql files across SQL, TypeScript/JavaScript, Python, Go, Java/Kotlin, C#, Ruby and PHP — then parses each statement and resolves its table and column references against the live catalog.

Reports schema drift as diagnostics. A query that references a table or column the database does not have becomes a squiggle in the editor, with the file and line, exactly where the stale SQL is written.

Only ever reads. Every connection runs with default_transaction_read_only, a statement timeout, a lock timeout, and search_path = pg_catalog. Sample rows are capped, and values in columns that look sensitive are masked in the extension host before they reach the visualiser.

The four levels

Database

Shown above. Schemas orbit a core, each sized by table count. The inspector carries the connection, the capture time, and a structural fingerprint that stays stable while the schema does — which is what lets a layout reproduce itself between sessions.

Schema

Every table laid out by a force simulation, so tables that reference each other end up near each other. Solid arcs are foreign keys within the schema; dashed arcs cross a schema boundary. Tables outside the schema you are in stay visible but recede.

Table

The full column list with types and PK / FK / UQ / IX markers, estimated rows, on-disk size, and a badge counting the queries in your workspace that touch this table. The scene narrows to this table and whatever it joins to.

Column

The selected column highlights and its foreign key arc lands on it. The inspector names the exact column it references, the indexes covering it, redacted sample values, and every query in your code that mentions it.

Performance

Introspection issues a fixed seven catalog queries regardless of database size. The obvious implementation — a query per table — is the difference between milliseconds and minutes on a real schema, so all the stitching happens in TypeScript instead.

Measured on PostgreSQL 14, median of five runs after a warm-up (npm run bench):

Cost tracks the size of the catalog, not the number of tables. A per-table implementation would have issued 500 round-trips for the last row instead of 7.

Workspace indexing over the bundled demo repository: 53 SQL statements across 8 files in 6 languages, 52 parsed, in 27 ms.

Row counts come from pg_class.reltuples rather than COUNT(*), so opening a table never triggers a sequential scan.

SQL in your codebase

Parsing embedded SQL runs into a problem the standalone case never has: the parser speaks PostgreSQL, but the query is written for whatever driver the file uses. JDBC and PDO emit ?, Python's DB-API emits %s or %(name)s, SQLAlchemy emits :name, ADO.NET emits @name — none of which is valid Postgres. On the demo repository that alone accounted for 15 of 51 statements failing to parse.

Quarry rewrites those placeholders to $n, but only after an unmodified parse has already failed. The ordering is load-bearing rather than an optimisation: ? is also the jsonb existence operator, % appears in every LIKE pattern, and : prefixes casts and array slices, so rewriting eagerly would corrupt valid SQL. String literals, quoted identifiers, dollar-quoted bodies and comments are skipped for the same reason.

Against the bundled demo repository, Quarry reports:

The column rule only fires when the column's qualifier resolves to a real relation, so excluded.definition inside an ON CONFLICT DO UPDATE is correctly left alone.

Safety

Sampling row data out of someone's database is the part of a tool like this that deserves suspicion, so the guarantees are enforced in depth and covered by tests that run against a real server (test/safety.integration.test.ts):

• Read-only sessions. default_transaction_read_only, statement_timeout, lock_timeout and idle_in_transaction_session_timeout are set on every pooled connection. Both DDL and INSERT are rejected, and the test asserts the relation really was not created.
• search_path = pg_catalog, so an unqualified identifier in Quarry's own SQL can never resolve to one of your tables.
• Bounded sampling. Five rows by default, hard-capped at 50, always as an explicit quoted column list rather than SELECT *, with the limit validated as an integer.
• Host-side redaction. Columns are classified on a tokenised identifier rather than by substring — which is what keeps updated_at from matching "dat" while password_reset_requested_at still matches "password" — and a value-level classifier with a Luhn check backstops columns whose name looked innocent. High-sensitivity values are replaced entirely; low-sensitivity ones keep their shape but not their content. This happens before the rows leave the extension host, so raw values never reach the webview.
• Sampling off means zero queries, not a query whose results are discarded. Column names, keys and relationships are still fully available.
• Identifier quoting is tested with a table name carrying a quote-and-semicolon payload; the query fails as a missing relation with the target table still standing.
• EXPLAIN never executes. The command runs EXPLAIN (FORMAT JSON) without ANALYZE inside the read-only transaction; planning an UPDATE over 1,500 rows leaves the row count unchanged.

The webview loads under a strict content security policy with a per-load nonce and connect-src 'none' — the bundle is self-contained and makes no network requests.

Architecture

Two bundles compiled from one source tree:

src/shared/protocol.ts     the contract — compiled into both bundles, dependency-free
src/db/                    introspection, hardened connections, sampling, redaction, DDL
src/scan/                  SQL extraction, parsing, reference resolution, lint rules
src/ui/                    the only code that may import `vscode`
webview/src/scene/         Three.js: layout, card textures, foreign-key arcs, camera
webview/src/components/    the 2D overlay panels

src/scan/ and src/db/ never import vscode, which is what makes them testable as plain functions; file enumeration and editor integration live in src/ui/.

Tables are drawn as a single textured quad each, painted with the 2D canvas API, rather than a mesh per column. A 40-table schema stays near 40 draw calls instead of ~600, the text is rasterised at device resolution, and it sidesteps the CSP — 3D text meshes need a font fetched at runtime. The trade-off is that the card geometry constants become load-bearing in three places at once: the texture painter, the foreign-key anchor math, and the pointer hit-test all derive from them. Clicking a column works by converting the ray hit's UV back into a row index.

Layout is a force simulation with grid-bucketed repulsion — each node tests the nine cells around it rather than every other node, which is O(n) instead of O(n²) — seeded from the snapshot's structural fingerprint so reopening the observer lands every table where you left it. All schemas are laid out in one pass rather than one pass per schema, because cross-schema foreign keys are common and an isolated layout leaves those arrows pointing off-screen.

Colors are not hand-picked. Constraint markers use categorical slots 1–3 of a validated palette in fixed order, checked all-pairs rather than adjacent-only, since in a node-link scene any two marks can end up side by side. "Indexed" deliberately gets no hue — a fourth slot puts yellow beside orange, which fails the separation floor. Every marker carries a glyph and a text label, so color is a redundant channel throughout.

Stack: TypeScript · VS Code Extension API · React 19 · Three.js / react-three-fiber · Tailwind CSS 4 · Vite · esbuild · node-postgres · node-sql-parser · Vitest.

Roughly 20,600 lines across the extension, webview, tests, demo schema and demo repository.

Getting started

git clone https://github.com/alexli8408/quarry.git
cd quarry
npm install
npm run build

Press F5 in VS Code to launch an Extension Development Host, then Quarry: Add Connection from the command palette. Paste a postgresql:// URL into the first prompt and the remaining fields are filled in for you. Quarry: Open 3D Database Observer (Ctrl/Cmd+Alt+Q) opens the scene.

Try it against the demo database

A 39-table e-commerce schema — composite keys, cross-schema and self-referencing foreign keys, enums, views, a materialized view, deliberately unindexed foreign keys, and columns that trip the sensitivity classifier:

node scripts/demo-db.mjs up      # creates quarry_demo on localhost

demo-workspace/ is a small microservice repository whose SQL targets that schema, including migrations-gone-stale that Quarry flags. Open it as a folder and run Quarry: Scan Workspace for SQL.

Developing the visuals without VS Code

The observer runs in a plain browser against a dumped snapshot, which is a much faster loop than reloading an extension host:

node scripts/snapshot.mjs        # quarry_demo -> demo/snapshot.json + demo/workspace.json
npm run harness                  # opens webview/dev.html

The harness posts the same messages the extension host does, so nothing in the app needs a dev-only branch. ?level=schema|table|column and ?theme=light are supported.

Development

npm run dev         # watch both bundles
npm run typecheck   # both tsconfigs
npm test            # 455 tests
npm run bench       # introspection + scan benchmarks
npm run package     # build a .vsix

The test suite covers the scanner, the sensitivity classifier, DDL generation and the introspection stitching as pure functions; loads the built bundle against a stubbed vscode namespace to assert the extension activates and registers exactly the commands the manifest declares; and exercises the safety guarantees against a live PostgreSQL. The database tests skip themselves when no server is reachable.

Reference

docs/FEATURES.md documents every user-facing surface: the four observer levels and how to read the scene, the full SQL-in-code pipeline traced through a real query, all nine lint rules, every command, and every setting.

License

MIT