@nqbao/pi-alchemy

Turn raw data into insights — query, transform, and visualize data files from the terminal.

Install

pi install npm:@nqbao/pi-alchemy

Or load locally during development:

pi -e ./index.ts

Usage

You: Load the file sales.csv and tell me total revenue by region

Pi: Loaded "sales.csv" as table "sales" (1200 rows)
    → {region: "North", revenue: 452000}
       {region: "South", revenue: 381000}
       {region: "East",  revenue: 295000}
       {region: "West",  revenue: 528000}

Pi calls necessary tools to load and query data.

Tools

alchemy_load

Load a CSV, Parquet, JSON, or JSONL file into a named table:

alchemy_load(path="/data/sales.parquet", name="sales")
  → creates DuckDB view "sales" for querying

Supported formats: .csv, .tsv, .parquet, .pq, .json, .jsonl, .ndjson

alchemy_query

Run a SQL query against loaded tables. Results are returned as JSONL:

alchemy_query(sql="SELECT city, pop FROM cities ORDER BY pop DESC LIMIT 2")
  → {"city":"Tokyo","pop":13960000}
    {"city":"London","pop":8982000}

Results are capped at maxRows (default: 500). Queries exceeding this return an error.

alchemy_tables

List all loaded tables and their types:

alchemy_tables()
  → {"table_name":"sales","table_type":"VIEW"}
    {"table_name":"products","table_type":"VIEW"}

alchemy_schema

Show column names, types, and nullability for a loaded table:

alchemy_schema(table="sales")
  → {"column_name":"region","data_type":"VARCHAR","is_nullable":"YES"}
    {"column_name":"revenue","data_type":"BIGINT","is_nullable":"YES"}

Configuration

From Pi's settings.json:

{
  "alchemy": {
    "dbPath": null,
    "maxRows": 500
  }
}

Field	Default	Description
dbPath	null (in-memory)	Path to a persistent DuckDB database
maxRows	500	Maximum rows returned by alchemy_query

Flags

Flag	Description
--alchemy-db	Path to a persistent DuckDB database (overrides settings)

Engine

Uses @duckdb/node-api — official DuckDB Node.js binding. In-process OLAP, no server.

Package

{
  "pi": {
    "extensions": ["./index.ts"]
  }
}