Skip to content

Open console query results in Positron's Data Explorer - #595

Open
samclark2015 wants to merge 1 commit into
mainfrom
sclark/ggsql-data-explorer
Open

samclark2015 wants to merge 1 commit into
mainfrom
sclark/ggsql-data-explorer

Conversation

@samclark2015

@samclark2015 samclark2015 commented Oct 20, 2026 •

Copy link
Copy Markdown
Contributor

Addresses posit-dev/positron#16137 ("ggsql: View result of SQL query in data explorer"). No Positron changes are needed.

What it does

  • "Open in Data Explorer" link under console results. In a Positron console, a tabular result is followed by an OSC 8 hyperlink to x-ggsql-view:<session>/<key>. Positron offers clicked links to the console session's openResource, which the extension adds by wrapping the session (ggsql-vscode/src/resultLinks.ts), the same way R handles x-r-help: links. The extension then runs -- @view-result <key> silently, and the kernel opens a positron.dataExplorer comm, which Positron shows as a tab.
  • Results are kept in a private in-memory DuckDB (ggsql-jupyter/src/results.rs). The explorer therefore always uses one SQL dialect, a remote query isn't re-run for every page, and nothing appears in the user's database. The number kept is capped by --max-results (default 16), but a result with an explorer open is never evicted.
  • Explorer features: row filters (an invalid filter is flagged without breaking the others), sorting, column search, export (CSV/TSV/HTML), profiles, and Convert to Code. Convert to Code wraps the original query as a subquery, written for the session's database.
  • Clicking the link again focuses the open tab instead of opening a second explorer. The kernel sends open_editor on the UI comm with the explorer's editor URI, which is what the Variables pane does for a variable that's already open.
  • -- @view[: title] directive: opens a query's result in the Data Explorer as soon as it runs, and still shows the table in the console. A silent execution opens only the tab, so a script can trigger it. Outside Positron it shows the table with a note. VISUALISE queries are rejected.
  • Shorter console tables: like R's tibble, a result of up to 20 rows shows in full and a longer one shows its first 10, so the link stays in view. The number is set by the ggsql.consoleRows setting. The extension passes it to the kernel as the GGSQL_CONSOLE_ROWS environment variable rather than as --console-rows, because an older kernel found on the machine would refuse to start on an unknown flag. Notebooks still show up to 100 rows.

Not in this PR

  • Notebooks still get the static HTML table, not Positron's inline data grid.
  • The explorer ignores format_options, Positron's number-formatting settings.
  • Convert to Code output for a multi-statement cell won't run as it stands.

Testing

  • cargo test -p ggsql-jupyter: 104 unit tests pass.
  • ggsql-jupyter/tests: 43 integration tests pass. They cover the link, -- @view and -- @view-result, focusing an open explorer, filter/sort/Convert to Code over the comm, JSON-RPC errors, and the GGSQL_CONSOLE_ROWS row count.
  • ggsql-vscode: 78 extension tests pass, including link parsing and the consoleRows setting.
  • Checked by hand in a Positron dev build:
    • the link opens a tab, and a second click focuses it;
    • sorting, a "between" filter, and Convert to Code, where the copied SQL reproduces the filtered rows;
    • -- @view: title;
    • the 10-row cap on a 5,000-row result, and ggsql.consoleRows = 3.

Demo

ggsql-data-explorer.mp4

🤖 Generated with Claude Code

@thomasp85

Copy link
Copy Markdown
Collaborator

Most of the logic of this PR is implemented in the Jupyter kernel and I'm not super fond of that if it can be avoided at all. For the kernel to contain a module so closely tied to positron feels like a code smell. Can you take another round at this with the goal of keeping it as confined as possible to the VS code extension?

Also, we plan to spin out the VS Code extension to its own repo that you can have more control over once this is in (just an FYI :-) )

@samclark2015

Copy link
Copy Markdown
Contributor Author

Sweet! Thanks for this feedback. I churned on it with the 🤖 and came up with this proposal, which moves Positron-specific code into the extension. Kernel additions are minimal and client-agnostic. Let me know how this sounds, and I'll spin up a revision.

Proposal: the kernel offers a general results API, and the extension serves the Data Explorer

Positron Data Explorer
      │  get_data_values, set_sort_columns, …     (Positron's protocol)
      ▼
ggsql-vscode   positron.dataExplorer.registerRpcHandler('ggsql', …)
      │  translates each request into a general ggsql call
      │  session.callMethod('result_page', view, rows, cols)
      ▼
ggsql-jupyter  results API
      answers with only the requested page; the data stays in the kernel

Kernel: a general results API, with no Positron logic.

  • The kernel keeps recent query results as the Arrow batches it already has, up to a limit.
  • It offers a small set of neutral operations on them:
    • result_open(key) returns a view id, the schema and the shape
    • result_set_sort and result_set_filters
    • result_page(view, rows, cols) returns plain values
    • result_profile, result_export and result_close
  • Sorting and filtering produce a list of row indices over the batch, as R's and Python's Data Explorer backends do. The result is never copied, and scrolling only reads the rows on screen.
  • These operations reach the extension through call_method on the positron.ui comm, which the kernel already serves. It's Positron's existing route for an extension to call into a kernel; session.callMethod, which positron-r uses to call into the R kernel, goes through it. That one dispatch point is the only Positron-specific code involved. The operations themselves don't mention Positron, so other frontends could use them later, for example JupyterLab through a comm of its own, or the CLI.

Extension: everything specific to Positron's Data Explorer.

  • It registers as a Data Explorer backend through Positron's extension API (positron.dataExplorer.registerRpcHandler), the same way Positron's Postgres and SQLite drivers do.
  • It translates each request into the operations above. It also handles everything that follows Positron's protocol: mapping column types, formatting values, the shape of column profiles, Convert to Code, and focusing a tab that's already open.
  • It handles the "Open in Data Explorer" link in the console. The kernel prints that link only when the extension asks for it.
  • All of this moves to the new repo with the extension.

Why this shape

  • Performance on large results. Only the visible page crosses between processes, and the result is never copied. The alternative was to send the whole result to the extension and keep a second copy there. Positron's existing backends avoid that: R and Python keep the data in the kernel, and the database drivers query the database directly. Here the kernel plays the role the database plays for the drivers.
  • Less in the kernel. This PR's kernel-side Data Explorer work goes away: the filter and export modules, the private DuckDB copy of results, the @view-result and @view directives, and the comm and tab-focus handling. The kernel's existing Data Explorer for the Connections pane isn't touched.

Tradeoffs

  • Data Explorer requests reach the kernel through the same request channel as code execution, so they wait while a long query runs. The comm this PR adds has the same limitation, and so does R's Data Explorer.
  • Each request takes one more hop (kernel to extension to Positron) than a comm served by the kernel. The database drivers have the same shape, and I don't expect it to be noticeable.

Scope

Both directives go. The extension calls result_open in place of -- @view-result, and drops -- @view. A result is opened from its console link.

@thomasp85

Copy link
Copy Markdown
Collaborator

I think this is a better shape in general. If possible, it would be nice to reuse the cache mechanism already in place in ggsql, however, care should be taken to not enforce a cached reader on the user (because that change the dialect for them). So, the cache reader should only be used for the results API

Console query results get a link that opens them in Positron's Data
Explorer. The kernel keeps each result in a dedicated in-memory DuckDB
results cache (sharing `CacheTables` with `CachingReader`, with pinning so
open results are not evicted; the user's reader is never wrapped) and
exposes a frontend-agnostic results API over `call_method`. The VS Code
extension registers the Data Explorer RPC handler, adds the console link,
and pages, sorts, filters and profiles results through that API.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
@samclark2015
samclark2015 force-pushed the sclark/ggsql-data-explorer branch from 307d520 to 6711156 Compare November 17, 2026 20:21

This branch has not been deployed

No deployments
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants