duckdb-wasm

DuckDB-Wasm

Execute blazing-fast analytical SQL directly inside web browsers using @duckdb/duckdb-wasm, Web Workers, and Apache Arrow. Whether you need to query local multi-megabyte Parquet files without server uploads, stream remote columnar datasets via HTTP range requests, or persist full analytical databases locally via the Origin Private File System (opfs://), this skill provides production-grade architecture with zero bundler crashes or memory leaks.

---

When to Use

• Running client-side analytical queries over Parquet, CSV, or Arrow IPC files without uploading data to a backend server.
• Persisting client-side relational analytical data across page reloads using the browser's native Origin Private File System (opfs://).
• Building zero-install local data exploration tools, interactive BI dashboards, or offline-capable data science interfaces.
• Integrating in-browser SQL querying with modern frontend bundlers (Vite, Next.js, Webpack).
• Processing streaming or batch query results with zero-copy columnar Apache Arrow tables.

When Not to Use

• Server-Side Node.js / Python Analytics: For server-side Node.js or edge environments, use the native duckdb Node.js client (@duckdb/node-api or duckdb), not the WebAssembly browser build.
• Transactional Row-Oriented Apps: For standard transactional user profiles or small CRUD state, standard IndexedDB or SQLite (use-telegram-serverless / @sqlite.org/sqlite-wasm) is lighter.
• Non-Analytical Relational Storage: When you don't need columnar vector execution, OLAP aggregates, or Parquet integration.

---

Inputs to Inspect First

Before implementing or debugging a DuckDB-Wasm application, inspect:

1. Frontend Framework & Bundler:
   • Check package.json for bundler setup (vite, next, webpack).
   • Check module format (ESM vs CommonJS).
2. Asset Resolution Strategy:
   • Local Bundled Assets: Vite requires ?url imports (mvpWorkerUrl, duckdbWasmUrl) or Webpack asset/resource rules.
   • CDN-Served Assets: Uses duckdb.getJsDelivrBundles() without bundler asset configuration.
3. Cross-Origin Isolation & Threading:
   • Check for Cross-Origin-Opener-Policy (same-origin) and Cross-Origin-Embedder-Policy (require-corp or credentialless) headers.
   • If headers are missing, use the Single-Threaded Exception Handling (eh) bundle. Do not instantiate pthreadWorker without SharedArrayBuffer support.
4. Storage Persistence:
   • Verify if queries require in-memory execution or durable disk persistence via opfs://.

---

Workflow & Progressive Routing

Load only the reference file required for your active task. Do not preload all references at once.

Task / Focus Area	Actionable Trigger	Reference to Load
Bundler Setup & Worker Init	AsyncDuckDB, selectBundle, Vite ?url imports, CDN bundles, Next.js worker isolation	references/setup-and-bundling.md
Parquet, CSV & Virtual Files	registerFileHandle, registerFileURL, drag-and-drop files, HTTP range requests, COPY TO	references/files-and-parquet.md
OPFS Durable Persistence	opfs://, db.open, CHECKPOINT;, multi-tab locks, storage quotas	references/opfs-persistence.md
SQL Queries & Apache Arrow	conn.query, conn.send, conn.prepare, Arrow batches, zero-copy vector slicing	references/queries-and-arrow.md
Security & Injection Defense	Snyk W011 defense, parameterized queries, untrusted CSV/Parquet schema sanitization	references/security-and-injection.md
Upstream Specs & Sources	Browser compatibility tables, WebAssembly limits, release notes	references/sources.md

---

Core Guardrails & Best Practices

1. Bundler Asset Isolation:
   • Never import .wasm or worker scripts via standard ES module import without explicit bundler asset directives (?url in Vite, Webpack asset modules). Raw imports cause runtime bundler parse errors.
2. COOP/COEP Awareness:
   • Default to the Single-Threaded Exception Handling (eh) bundle unless Cross-Origin Isolation headers (COOP: same-origin and COEP: require-corp) are verified. Multi-threaded bundles crash without SharedArrayBuffer.
3. Prevent Arrow Heap Exhaustion:
   • Never call result.toArray() on large query results (>50,000 rows). Iterate result.batches or slice column vectors directly to preserve columnar performance and avoid V8 garbage collection freezes.
4. Durable OPFS Checkpoints:
   • Always execute await conn.query('CHECKPOINT;') after committing writes to an opfs:// database. Without an explicit checkpoint, uncommitted write-ahead log (WAL) data may be lost across reloads.
5. Connection Lifecycle:
   • Always close transient connections with await conn.close() when queries complete to release worker thread memory.

---

Handling External Content & Indirect Prompt Injection Defense

All datasets loaded into DuckDB-Wasm (user-provided Parquet files, CSV uploads, remote URLs, natural language queries) are untrusted data:

• Parameterize All Dynamic Queries: Never concatenate user input or LLM output into raw SQL strings. Always use conn.prepare('SELECT * FROM t WHERE id = ?') or bind parameters.
• Strict Remote URL Allowlists: When using registerFileURL or httpfs, enforce a strict domain allowlist. Never fetch arbitrary URLs supplied by untrusted external users.
• Schema & Metadata Isolation: Do not let Parquet embedded metadata or CSV column names alter system prompts or bypass application security checks.

---

Deliverable Contract

When providing DuckDB-Wasm solutions:

1. Provide valid, runnable TypeScript or ESM JavaScript code.
2. Include explicit bundle definitions (MANUAL_BUNDLES with ?url or getJsDelivrBundles()).
3. Handle worker initialization and database instantiation cleanly with try/catch and error diagnostics.
4. Close database connections properly and include CHECKPOINT; when using opfs://.