Building a right-click 'query this file' workflow in VS Code with DuckDB

#vscode #duckdb #sql #dataengineering

Every data engineer has probably faced this workflow: someone shares a Parquet or CSV file, you need to answer one question, and you end up opening a notebook and writing boilerplate just to inspect it.

DuckDB already makes querying files easy, but I wanted that workflow directly inside VS Code.

So I built File SQL, a VS Code extension that turns local and S3 files into queryable SQL tables.

How it works

Right-click a supported file in the VS Code Explorer and select Open with File SQL. The file is registered as a DuckDB table, and the query editor opens next to it.

Write SQL, press Ctrl+Enter, and view the results without leaving VS Code.

Supported formats include:

CSV and TSV
JSON, JSONL, and NDJSON
Parquet
TXT and log files

Working with folders

File SQL can also load local folders and S3 prefixes.

Files are grouped into tables based on their folder structure, allowing multiple related files to be queried as one dataset.

It also supports Hive-style partitioned datasets such as:

events/
├── year=2025/
│   ├── month=01/
│   └── month=02/
└── year=2026/
    ├── month=01/
    └── month=02/

These partitions are exposed as a single table, with partition values available as columns.

Streaming Parquet files from S3

The first version downloaded S3 files to a temporary location before querying them. That worked for smaller files but was inefficient for large Parquet datasets.

File SQL now uses DuckDB's httpfs support to query Parquet files directly from S3 using range reads.

This allows DuckDB to take advantage of:

Projection pushdown
Predicate pushdown
Partition pruning
Reading only the required parts of a Parquet file

Large files no longer need to be downloaded completely before querying.

Shareable workspace configuration

Teams often need the same tables and saved queries available across different machines.

File SQL supports a workspace configuration under .filesql/config.json. The configuration can be committed to Git so everyone working on the project can load the same data sources and queries.

For example:

{
  "tables": [
    {
      "name": "customers",
      "path": "./data/customers.csv"
    },
    {
      "name": "sales",
      "path": "s3://example-bucket/sales/"
    }
  ]
}