faostat-mcp-server

<div align="center"> <h1>@cyanheads/faostat-mcp-server</h1> <p><b>Global food & agriculture statistics from the UN FAOSTAT bulk-download corpus, served from a local SQLite mirror with a DataCanvas SQL surface, via MCP. STDIO or Streamable HTTP.</b> <div>7 Tools • 1 Skill Resource • 0 Prompts</div> </p> </div> <div align="center">

</div> <div align="center">

Public Hosted Server: https://faostat.caseyjhand.com/mcp

</div>

---

Overview

Global food and agriculture statistics from the UN's FAOSTAT: production, trade, food balances, food security, land use, fertilizer, and emissions for 245+ countries and territories since 1961. Resolve commodity and country codes, query the data, and run SQL over large results.

Tools

| Tool | Description | |:---|:---| | faostat_list_domains | Discover FAOSTAT statistical domains with codes, descriptions, last-update date, upstream row count, and local index status. The entry point — every query keys on a domain code. | | faostat_resolve_codes | Resolve human terms to the opaque integer codes a query needs (areas, items, elements), flagging each area as a country or an aggregate region. | | faostat_query_observations | Query a domain's cube by area(s), item(s), element(s), and year range. Inline preview for small results; large sets spill to a DataCanvas table. | | faostat_commodity_profile | Workflow: assemble top producers, the production trend, and trade flows for one commodity from the production and trade domains in a single call. | | faostat_dataframe_query | Run a read-only SQL SELECT against the canvas tables staged by the analytical tools. | | faostat_dataframe_describe | List the canvas tables staged this session, each with provenance, row count, and column schema. | | faostat_dataframe_drop | Drop one staged canvas table before its 2-hour TTL. Opt-in — callable only with FAOSTAT_DATAFRAME_DROP_ENABLED=true. |

Capability reference

faostat_list_domains _tool

• Full FAOSTAT catalog (~69 domains) read live from the bulk manifest, annotated with local mirror status
• code for an exact domain lookup; topic substring filter over code/name/topic; indexed_only to list only domains queryable from the local mirror
• offset + limit (max 200, default 20) page the catalog — response reports totalMatches, truncated, and nextOffset
• Each entry reports indexed / index_ready flags, local row count, and last completed sync

---

faostat_resolve_codes _tool

• FTS5 full-text query, substring name_contains, or exact code lookup within a dimension: area, item, or element
• Item/element matches are scoped to codes present in the given domain's cube; area codes are shared across domains
• Every area match is flagged country or aggregate (codes ≥ 5000, plus curated sub-threshold roll-ups such as China=351)
• limit (max 200, default 50) + offset page the match set
• Typed errors: unknown_domain, index_not_ready, query_timeout

---

faostat_query_observations _tool

• Filters by area_codes / item_codes / element_codes and an inclusive year_start / year_end range
• Aggregate regions excluded by default (include_aggregates: false); explicit area_codes bypass the exclusion
• limit caps the inline page (default 200, max 1000); a match that exceeds it spills in full to a DataCanvas table (50,000-row staging cap) — faostat_dataframe_describe for its columns, then SQL via faostat_dataframe_query
• Every row carries its data-quality flag (A/E/I/B/M/T/X, others per domain) — never dropped
• Typed errors: domain_not_indexed, index_not_ready, canvas_disabled, canvas_not_found, invalid_year_range, query_timeout

---

faostat_commodity_profile _tool

• Selects one QCL item with item_code or item_query (code takes precedence), then ranks top producers/exporters/importers and returns an annual production trend
• Query selection prefers a unique exact label after trimming, collapsing whitespace, and case folding; otherwise requires a unique relevance match. For ambiguous terms such as maize or milk, call faostat_resolve_codes with domain: "QCL", dimension: "item", and the query, then pass the selected item_code (e.g. 56 for maize grain)
• Rankings are per-country sums grouped by unit, each country at its own latest reporting year; countries only (aggregates excluded)
• Returns a partial, production-only profile with a notice — rather than failing — when the trade domain (TCL) isn't indexed or still syncing
• top_n caps each ranked list (max 50); when the merged observation set is too large to inline it spills to a DataCanvas table — faostat_dataframe_describe for its columns, then faostat_dataframe_query for further SQL
• Every staging outcome is stated in the notice: the staged table, a set that fit inline, a staging failure, or a disabled canvas
• Typed errors: missing_item, ambiguous_item, no_match, domain_not_indexed, index_not_ready, invalid_year_range, canvas_not_found, query_timeout

---

faostat_dataframe_query _tool

• Single-statement read-only SELECT over staged faostat_xxxxxxxx tables — joins, aggregates, window functions, and CTEs all work
• Writes, DDL, DROP, COPY, PRAGMA, ATTACH, external-file functions, and system catalogs (information_schema, sqlite_master, duckdb_*) are rejected
• Engine-setting introspection, query_table, dynamic query() SQL, and file table references are rejected; query() with a single literal read-only SELECT remains supported
• row_limit caps the response (default 1000, max 10000); truncated means more rows exist, with no exact total computed on this path
• Typed errors: canvas_disabled, canvas_not_found, missing_table, system_catalog_access, invalid_sql

---

faostat_dataframe_describe _tool

• Lists staged tables with source tool, query params, row count, column schema, and recorded creation/expiry timestamps
• name describes one table outright; otherwise offset + limit (max 100, default 20) page the listing newest-first
• Typed errors: canvas_disabled, canvas_not_found, missing_table

---

faostat_dataframe_drop _tool

• Opt-in: registered disabled — listed on the landing page with its enable hint, absent from tools/list — unless FAOSTAT_DATAFRAME_DROP_ENABLED=true
• Drops one staged faostat_xxxxxxxx table and its provenance from the session canvas, or from the canvas a canvas_id names
• Idempotent: a name not staged on that canvas returns dropped: false with a notice and changes nothing
• Typed errors: canvas_disabled, canvas_not_found

Features

The faostat-guide covers comparable reporting years, country versus regional totals, units, coverage, and complete SQL analysis across staged results. It ships with plugin, npm, Docker, and MCPB installs and is served through the MCP Skills extension as a skill resource.

Built on @cyanheads/mcp-ts-core: stdio and Streamable HTTP transports, pluggable auth (none / jwt / oauth), swappable storage (in-memory, filesystem, Supabase, Cloudflare KV/R2/D1), structured logging with optional OpenTelemetry tracing.

FAOSTAT-specific:

• Persistent local SQLite mirror of the FAOSTAT bulk corpus via the framework MirrorService, with FTS5 over the dimension labels driving code resolution
• Streaming bulk-ZIP ingester — skips domains whose upstream update date hasn't advanced, and stream-parses the normalized CSV into SQLite without materializing the full file in memory
• Config-driven domain selection (FAOSTAT_DOMAINS) — the indexed set can grow without code changes, and the full catalog stays browsable regardless
• DataCanvas SQL surface (DuckDB) for GROUP BY, ranking, and time-series analysis over spilled result sets

Agent-friendly output:

• Country-vs-aggregate classification on every area, with aggregates excluded from sums by default — guards against double-counting World/continent rows with their member countries
• Data-quality provenance — every observation carries its FAOSTAT flag (A/E/I/B/M/T/X, others per domain), never dropped from output
• Graceful partial results — faostat_commodity_profile returns a production-only profile with a notice, rather than failing, when the trade domain isn't indexed
• Typed error contracts — index_not_ready, domain_not_indexed, canvas_disabled, and others each carry a concrete recovery hint

Getting started

Connect to the hosted instance, no install:

claude mcp add --transport http faostat-mcp-server https://faostat.caseyjhand.com/mcp

Or run it locally after building the mirror:

claude mcp add faostat-mcp-server -- npx -y @cyanheads/faostat-mcp-server

For other clients, add this to the MCP config file:

{
  "mcpServers": {
    "faostat-mcp-server": {
      "command": "npx",
      "args": ["-y", "@cyanheads/faostat-mcp-server"]
    }
  }
}

The bulk-download service is public and keyless. For Docker or a source build, see Running the server.

Configuration

| Variable | Description | Default | |:---------|:------------|:--------| | FAOSTAT_DOMAINS | Comma-separated FAOSTAT domain codes to index into the local mirror. Domains outside this set appear in faostat_list_domains but are not queryable until added and re-synced. | QCL,TCL,FBS,FS,RL,GLE,RFN,QV | | FAOSTAT_MIRROR_PATH | Directory holding the per-domain SQLite stores and the shared dimension database. Created if absent. | ./.faostat-mirror | | FAOSTAT_BULK_BASE_URL | FAOSTAT bulk-download service base URL (manifest + per-domain ZIPs). | https://bulks-faostat.fao.org/production | | FAOSTAT_REFRESH_CRON | Cron for the in-process incremental refresh (HTTP transport only). Omit to disable and run mirror:refresh out-of-band. | — | | CANVAS_PROVIDER_TYPE | DataCanvas engine. duckdb enables the SQL surface; set none to disable analytical staging (the dataframe_* tools then report canvas_disabled and large queries