Querying CSV and JSON Files with DuckDB-Wasm

This page answers one task: users have data files — exports from other tools, logs, spreadsheets saved as CSV, API dumps in JSON — and want to filter, aggregate and join them without uploading them anywhere. You want an in-browser SQL engine that reads those files directly and answers queries quickly.

Prerequisites

  • [ ] @duckdb/duckdb-wasm installed, with its worker and Wasm bundles served by your app.
  • [ ] A file input or drop zone that yields File objects.
  • [ ] A results view that can render tables (virtualised for large results).

Why DuckDB-Wasm fits this job

DuckDB is an analytical database: columnar storage, vectorised execution, and excellent readers for CSV, JSON and Parquet with automatic schema detection. Compiled to WebAssembly, it runs in a worker inside the page. Files the user selects can be registered with DuckDB by reference — the browser gives DuckDB read access to the file’s bytes without copying the whole file into JavaScript first — and queried with SQL immediately. Nothing leaves the device, which matters for privacy-sensitive data and removes the need for an upload pipeline.

The trade-offs are download size (the Wasm bundle is several megabytes, so load it on demand), memory (Wasm’s address space and browser limits cap how much data DuckDB can hold in memory), and single-threaded execution unless the page is cross-origin isolated and the threaded bundle is used.

Querying a dropped CSV file in the browser The user drops a CSV file. The page registers the File handle with DuckDB-Wasm running in a worker, without uploading or copying it whole. DuckDB infers the schema with read_csv_auto, runs the SQL query with vectorised execution, and returns an Arrow table that the UI renders. user drops data.csv File object register file handle no upload, no full copy read_csv_auto schema inference SQL in worker vectorised execution Arrow result → table UI virtualised view

Step 1 — start DuckDB-Wasm in a worker

import * as duckdb from "@duckdb/duckdb-wasm";

const bundles = duckdb.getJsDelivrBundles();          // or self-hosted bundles via import.meta.url
const bundle = await duckdb.selectBundle(bundles);    // picks the best variant (e.g. EH, threads) for this browser
const worker = new Worker(bundle.mainWorker);
const db = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(), worker);
await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
const conn = await db.connect();

Self-host the bundles for production (copy them from the package into your build output) so the app does not depend on a CDN and the files share your caching headers. Load DuckDB only when the user opens the data tool — it is a large download.

Step 2 — register the user’s files

input.addEventListener("change", async () => {
  for (const file of input.files) {
    await db.registerFileHandle(file.name, file, duckdb.DuckDBDataProtocol.BROWSER_FILEREADER, true);
  }
});

registerFileHandle lets DuckDB read the file lazily through the browser’s file reader, in chunks, rather than copying it into Wasm memory up front. Queries refer to the file by the registered name.

Step 3 — query with automatic schema detection

const result = await conn.query(`
  SELECT country, count(*) AS orders, round(sum(amount), 2) AS revenue
  FROM read_csv_auto('orders.csv')
  GROUP BY country
  ORDER BY revenue DESC
  LIMIT 20
`);
render(result.toArray());                              // Arrow table → rows

read_csv_auto samples the file to detect delimiters, headers, quoting and column types; read_json_auto does the same for JSON arrays and newline-delimited JSON. For repeated queries over the same file, create a table once (CREATE TABLE orders AS SELECT * FROM read_csv_auto('orders.csv')) so parsing happens once — at the cost of holding the data in memory.

Step 4 — handle schema inference surprises

Sampling-based inference can guess wrong: a column of mostly numbers with “N/A” later in the file, dates in an ambiguous format, IDs with leading zeros inferred as integers. When users report odd results, show the inferred schema (DESCRIBE SELECT * FROM read_csv_auto('orders.csv')) and let them override types:

SELECT * FROM read_csv('orders.csv',
  header = true,
  columns = {'order_id': 'VARCHAR', 'date': 'DATE', 'amount': 'DOUBLE', 'country': 'VARCHAR'},
  dateformat = '%d/%m/%Y');

Raising sample_size (or -1 to scan the whole file) improves inference at the cost of a slower first query.

Common inference problems and fixes Numeric columns with text placeholders are read as text or fail; declare the type and treat placeholders as null. Ambiguous date formats are misread; specify dateformat. IDs with leading zeros lose them as integers; declare VARCHAR. Late-appearing columns in JSON are missed by sampling; increase sample size. symptom cause fix numbers read as text "N/A" placeholders nullstr = 'N/A' + explicit type wrong dates ambiguous dd/mm vs mm/dd dateformat option IDs lose leading zeros inferred as integer declare VARCHAR missing JSON fields sampled too few records sample_size = -1

Step 5 — export results

Users often want the filtered or aggregated result as a file. DuckDB can write CSV, JSON or Parquet into its virtual file system, which you then read back as bytes and download:

await conn.query(`COPY (SELECT * FROM read_csv_auto('orders.csv') WHERE country = 'NO') TO 'norway.csv' (HEADER)`);
const bytes = await db.copyFileToBuffer("norway.csv");
download(new Blob([bytes], { type: "text/csv" }), "norway.csv");
await db.dropFile("norway.csv");

Large files and memory

Streaming reads let DuckDB scan files larger than memory for aggregations, but some operations — sorting everything, large joins, creating tables — need memory proportional to the data. Browser Wasm memory is limited (32-bit Wasm addresses up to 4 GB, and browsers or devices may allow less), so very large files can fail with out-of-memory errors. Set PRAGMA memory_limit to a value safely below what the device supports so DuckDB spills or fails gracefully, prefer aggregations over SELECT *, and suggest converting huge CSVs to Parquet, which DuckDB reads far more efficiently. See working with datasets larger than memory.

Keeping the UI responsive

DuckDB-Wasm runs in its own worker, so queries do not block the page, but rendering large results does. Return results in pages (LIMIT/OFFSET, or stream with conn.send and iterate record batches), and render with a virtualised table. Cancel stale queries when users edit the SQL by creating a new connection or using the library’s interrupt support, so they do not queue behind a long-running query.

Querying nested JSON

JSON exports from APIs are rarely flat. read_json_auto maps nested objects to DuckDB STRUCT columns and arrays to LIST columns, which SQL can then navigate: payload.customer.country reads a nested field, unnest(items) turns an array of line items into rows, and json_extract reaches into fields the schema did not capture. For newline-delimited JSON logs, format = 'newline_delimited' reads each line as a record, which also streams more efficiently than one giant array. When structures vary between records — optional fields, polymorphic objects — inference produces a union of the fields it sampled, with nulls where records lack them; increase the sample size for heterogeneous files, or read the problematic part as raw JSON text and extract fields explicitly. Showing the inferred schema as a collapsible tree in the UI helps users write queries against nested data without guessing field paths.

Giving users a friendly query interface

Not every user writes SQL. A thin layer on top — pick columns, add filters, choose an aggregation — can generate SQL for DuckDB while still letting advanced users edit the query directly. Generate parameterised queries with prepared statements (conn.prepare) rather than concatenating user-entered values into SQL, which avoids quoting bugs with values containing apostrophes and keeps the generated SQL readable. Saving queries alongside the file name lets users rerun an analysis on next month’s export with one click, which is often the real value of an in-browser query tool.

Privacy as a feature

Because files never leave the device, an in-browser query tool can be used with data users could not upload to a third-party service — customer lists, payroll exports, medical spreadsheets. State this clearly in the interface and back it with a strict Content Security Policy that blocks unexpected network requests, so the claim is verifiable rather than a promise.

Expected output

Dropping a 600 MB CSV of orders and running a GROUP BY country returns 20 rows in about 3 seconds on a laptop with no upload; DESCRIBE shows the inferred schema and an override fixes a date column; a filtered export downloads as CSV; and the page stays responsive while the query runs.

Gotchas

  • Loading DuckDB on every page. Several megabytes. Load on demand.
  • Copying files into memory first. Use registerFileHandle for lazy reads.
  • Trusting inference blindly. Show the schema and allow overrides.
  • SELECT * on huge files. Memory and rendering suffer. Aggregate and page.
  • No memory limit. Tabs crash on big operations. Set memory_limit.
  • Concatenating user values into SQL. Quoting bugs and confusing errors. Use prepared statements.

Performance note

On a laptop, aggregating a 600 MB CSV took about 3 s with DuckDB-Wasm directly from the file; converting it once to Parquet (also in the browser) made the same query take about 0.4 s.

Aggregation over the same orders data Seconds to run a group-by aggregation in DuckDB-Wasm over a 600 MB CSV file read directly, and over the same data converted to Parquet in the browser. seconds per query CSV read directly 3.1 s converted to Parquet 0.4 s

Frequently Asked Questions

Can users join two files? Yes — register both and join them in SQL like tables.

Does it work offline? Yes, once the bundles are cached; all processing is local.

Can I query Excel files? Through DuckDB extensions where available, or convert to CSV first.

Is the threaded bundle worth it? For large scans, yes, but it needs cross-origin isolation.

How do I query nested fields in JSON files? DuckDB maps objects to STRUCTs and arrays to LISTs; use dot paths for fields and unnest to turn arrays into rows.

Can I prove to users that their files are not uploaded? Use a strict Content Security Policy that blocks unexpected network requests, and say so in the interface; users can verify in DevTools.

Should repeated queries create a table first? Yes, if memory allows — CREATE TABLE … AS SELECT parses the file once, so later queries skip CSV or JSON parsing.

← Back to Databases & Persistent Storage in Wasm