feat: adopt DuckDB 2.0 with workspace compatibility guards by ilyabo · Pull Request #959 · sqlrooms/sqlrooms

ilyabo · 2026-10-21T19:14:13Z

DuckDB 2.0 can serialize modifying CTEs beneath a SELECT and creates new database files that older engines cannot read by default. Upgrade the shared SQLRooms/Roomie runtime while preserving an explicit portable-file policy and checking protected query consumers before execution.

Require DuckDB 2.0.x and Python 3.11+ consistently across the Python workspace and Roomie; regenerate the lockfile.
Create runtime workspaces and attached metadata with v1.4.0 storage compatibility. Existing files retain their format; document feature limitations and backup/rollback requirements.
Add a shared recursive AST guard for AI read-only tools, MCP, HTML apps, Python SQL bridge, and table materialization. Preserve writable SQL editor execution and classify modifying CTEs as writes. Check full nested namespace paths.
Reject raw/nested VARIANT Arrow values with explicit cast guidance; cover imports, types, copied files, sync persistence, parser contracts and HTTP mutation rejection.
Add a CI compatibility check and a verification record with exact versions, support limitations and raw runtime benchmarks. Quack remains a later phase.

Validation on macOS arm64 / Python 3.12.9 / Node 24.19:

All 50 workspace packages and both production UIs built.
SQLRooms Python: 339 passed in the full run; subsequent focused launcher/compatibility run: 70 passed; final compatibility suite including sync restart: 9 passed. Roomie Python: 43 passed.
SQLRooms UI: 226 tests and 3 snapshots; Roomie UI: 82 tests; core/connector/AI/HTML/MCP suites passed.
Real 1.5.6 and 2.0.0 parser contracts; older Python 1.4.4/1.5.6, Node 1.4.4 and actual WASM 1.4.3 file readers.
Chromium CSV import, document/table/chart rendering and reload, HTML iframe; two-client sync broadcast and snapshot persistence.
Clean SQLRooms wheel/sdist and paired Roomie wheel installation/startup/persistence checks.
Affected-package ESLint, changed-file Prettier, focused Ruff and diff checks passed.

Local Git hooks force Volta Node 22 despite the repository's Node 24 requirement. Commit/push hooks were bypassed after the explicit checks above; their full repository-wide pre-push suite was not run. Linux and other platform results depend on CI/release checks; optional community extensions and the complete RAG embedding/VSS workflow remain unverified.

Summary by CodeRabbit

New Features
- Added comprehensive read-only SQL validation across query tools, runtime execution, SQL editing, MCP, and Python APIs.
- Data-modifying CTEs and unknown SQL structures are now rejected from read-only workflows.
- Added DuckDB 2.0 compatibility, including safer storage compatibility for new workspaces and improved Arrow/VARIANT handling.
Documentation
- Added DuckDB 2.0 upgrade, compatibility, rollback, and query-safety guidance.
Breaking Changes
- Python support now requires version 3.11 or newer.

chatgpt-codex-connector · 2026-10-21T19:14:27Z

Codex Review Summary

This comment shows the latest Codex review activity on this pull request.

Review	Status	Commit	Review trigger
📝 Code Review	✅ Completed 2026-10-21T19:17:12.890785Z	`8eb7a85`	PR opened

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

Open a pull request for review
Mark a draft as ready
Comment "@codex review" or "@codex security review".

Codex reacts with 👀 while any review is running, comments if it has suggestions, and reacts with 👍 once all reviews finish with no findings.

coderabbitai · 2026-10-21T19:17:01Z

No actionable comments were generated in the recent review. 🎉

ℹ️ Recent review info

⚙️ Run configuration

Configuration used: defaults
Review profile: CHILL
Plan: Advanced
Run ID: f2210c82-09bc-4616-a705-be2dd1b688a7

📥 Commits

Reviewing files that changed from the base of the PR and between 914fd97 and 8eb7a85.

⛔ Files ignored due to path filters: 2

pnpm-lock.yaml is excluded by !**/pnpm-lock.yaml
python/uv.lock is excluded by !**/*.lock

📒 Files selected for processing: 43

.github/workflows/pr-checks-python.yml
apps/sqlrooms-cli-ui/src/__tests__/createCliRoomCapabilityHandlers.test.ts
apps/sqlrooms-cli-ui/src/store.ts
dev-docs/contributing/python.md
dev-docs/verification/duckdb-2-benchmarks.json
dev-docs/verification/duckdb-2.md
packages/ai/README.md
packages/ai/__tests__/queryTool.test.ts
packages/ai/src/tools/query/queryTool.ts
packages/app-runtime/README.md
packages/app-runtime/__tests__/html-app.test.ts
packages/app-runtime/package.json
packages/app-runtime/src/html-app.tsx
packages/duckdb-core/README.md
packages/duckdb-core/src/__tests__/sqlSelect.test.ts
packages/duckdb-core/src/index.ts
packages/duckdb-core/src/sqlSelect.ts
packages/duckdb/README.md
packages/duckdb/src/DuckDbSlice.ts
packages/duckdb/src/index.ts
packages/mcp/README.md
packages/mcp/__tests__/localSqlPolicy.duckdb.test.ts
packages/mcp/package.json
packages/mcp/src/localSqlPolicy.ts
packages/sql-editor/README.md
packages/sql-editor/src/SqlEditorSlice.tsx
python/PUBLISHING.md
python/README.md
python/pyproject.toml
python/roomie/README.md
python/roomie/pyproject.toml
python/sqlrooms-rag/README.md
python/sqlrooms-rag/pyproject.toml
python/sqlrooms-rag/tests/test_import.py
python/sqlrooms/DUCKDB_COMPATIBILITY.md
python/sqlrooms/README.md
python/sqlrooms/pyproject.toml
python/sqlrooms/sqlrooms/server/query.py
python/sqlrooms/sqlrooms/server/runtime.py
python/sqlrooms/sqlrooms/web/launcher.py
python/sqlrooms/tests/server/test_duckdb_compatibility.py
python/sqlrooms/tests/test_launcher.py
turbo.json

Included review availability: This review used your included allowance. Your plan provides up to 4 included reviews per hour; 3 remain after this review.

📝 Walkthrough

The changes add a shared validator for serialized SQL query trees and use it across TypeScript and Python query paths. They also update Python runtime compatibility behavior, DuckDB and Python version requirements, tests, and DuckDB 2.0 compatibility documentation.

Changes: DuckDB 2.0 compatibility and read-only SQL validation

Layer / File(s)	Summary
Shared read-only SQL validator `packages/duckdb-core/src/sqlSelect.ts`, `packages/duckdb-core/src/index.ts`, `packages/duckdb-core/src/__tests__/*`, `packages/duckdb-core/README.md`	Adds and exports `isReadOnlySqlSelect`. It validates complete serialized SQL results, checks statement count and root node types, and recursively rejects mutation and unknown query nodes. Tests cover malformed results, nested nodes, and supported AST shapes.
TypeScript query-path validation `packages/ai/`, `packages/app-runtime/`, `packages/duckdb/`, `packages/sql-editor/`, `apps/sqlrooms-cli-ui/src/*`	Uses the shared validator in query tools, the app runtime, DuckDB table creation, the SQL editor, and the Python SQL bridge. Tests verify that data-modifying CTEs are rejected before authorization or execution.
MCP local SQL policy `packages/mcp/src/localSqlPolicy.ts`, `packages/mcp/__tests__/*`, `packages/mcp/package.json`, `packages/mcp/README.md`, `turbo.json`	Applies the validator to MCP SQL inspection and checks qualified paths when classifying internal tables and functions. DuckDB-serialized tests cover local-read approval and fail-closed rejection cases.
Python query and result compatibility `python/sqlrooms/sqlrooms/server/`, `python/sqlrooms/sqlrooms/web/launcher.py`, `python/sqlrooms/tests/`	Adds recursive SQL AST validation before project-query execution, rejects Arrow schemas containing VARIANT fields, and configures storage compatibility as `v1.4.0`. Tests cover query behavior, Arrow conversion, persistence, and older DuckDB readers.
DuckDB 2.0 versioning and verification `python//pyproject.toml`, `python//README.md`, `python/sqlrooms/DUCKDB_COMPATIBILITY.md`, `dev-docs/verification/*`, `.github/workflows/pr-checks-python.yml`	Raises Python minimum requirements to 3.11 and updates DuckDB constraints to 2.0.x. Adds compatibility guidance, verification results, benchmark measurements, and CI checks.

Priority: ➖ Normal

Estimated code review effort: 4 (Complex) | ~60 minutes

Change: Feature

Sequence Diagram(s)

sequenceDiagram
  participant Runtime as executeReadonlyQuery
  participant QueryState as sqlSelectToJson
  participant Validator as isReadOnlySqlSelect
  participant Authorization as authorizeQuery
  participant Database as runQuery
  Runtime->>QueryState: Parse SQL and return serialized statements
  Runtime->>Validator: Validate the parsed query tree
  alt Read-only query
    Runtime->>Authorization: Authorize query
    Runtime->>Database: Execute query
  else Non-read-only query
    Runtime-->>Runtime: Reject before authorization or execution
  end

Merge Risk: ⚪ Minimal · up to 8eb7a

No confirmed issue blocks merging. The reported older-reader compatibility failure remains unverified.

Pre-merge checks: 4 · 1

Failed checks: 1 warning

Check name	Status	Explanation	Resolution
Docstring Coverage		Docstring coverage is 23.68% which is insufficient. The required threshold is 80.00%. Docstring coverage is scoped to functions touched by this diff. Analyzed 38 functions across 20 files. (23 skipped…	Write docstrings for the functions missing them to satisfy the coverage threshold.

Passed checks: 4 passed

Check name	Status	Explanation
Description Check		Check skipped - CodeRabbit’s high-level summary is enabled.
Title check		The title clearly and concisely describes the primary changes: adopting DuckDB 2.0 and adding workspace compatibility guards.
Linked Issues check		Check skipped because no linked issues were found for this pull request.
Out of Scope Changes check		Check skipped because no linked issues were found for this pull request.

Full details: Docstring Coverage

Explanation

Docstring coverage is 23.68% which is insufficient. The required threshold is 80.00%. Docstring coverage is scoped to functions touched by this diff. Analyzed 38 functions across 20 files. (23 skipped: 23 unsupported.)

Resolution

Write docstrings for the functions missing them to satisfy the coverage threshold.

✨ Finishing Touches: 💡 2

📝 Generate docstrings: 💡

Commit to this branch
Create a new PR

🛠️ Fix failing CI checks: 💡

Commit to this branch
Create a new PR

🧪 Generate unit tests (beta)

Commit to this branch
Create a new PR

Autofix · Keep fixing CodeRabbit findings and required CI, and resolving merge conflicts

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

_{Comment @coderabbitai help to get the list of available commands.}

feat: adopt DuckDB 2.0 with workspace compatibility guards

8eb7a85

This was referenced Oct 22, 2026

chore: add Node and Wasm Quack feasibility experiment #960

Draft

feat(roomie): add durable workspace and shared Codex/Claude runs #962

Draft

Navigation Menu

Repository navigation

feat: adopt DuckDB 2.0 with workspace compatibility guards - #959

feat: adopt DuckDB 2.0 with workspace compatibility guards#959
ilyabo wants to merge 1 commit into
mainfrom
feat/duckdb-2-compatibility

ilyabo commented Oct 21, 2026 •

edited by coderabbitai Bot

Loading

Uh oh!

chatgpt-codex-connector Bot commented Oct 21, 2026 •

edited

Loading

Uh oh!

coderabbitai Bot commented Oct 21, 2026 •

edited

Loading

Sequence Diagram(s)

Uh oh!

This branch has not been deployed

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

Conversation

ilyabo commented Oct 21, 2026 • edited by coderabbitai Bot Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary by CodeRabbit

Uh oh!

chatgpt-codex-connector Bot commented Oct 21, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Codex Review Summary

Uh oh!

coderabbitai Bot commented Oct 21, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Sequence Diagram(s)

Uh oh!

This branch has not been deployed

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

ilyabo commented Oct 21, 2026 •

edited by coderabbitai Bot

Loading

chatgpt-codex-connector Bot commented Oct 21, 2026 •

edited

Loading

coderabbitai Bot commented Oct 21, 2026 •

edited

Loading