peter-palmer/bigquery-mcp-server

BigQuery MCP Server (Node.js)

A minimal Model Context Protocol (MCP) server for Google Cloud BigQuery, written in TypeScript/Node.js.

Features

• Exposes tools:
  • listDatasets: returns available datasets for the current project
  • listTables(datasetId: string): returns tables in a dataset
  • getSchema(datasetId: string, tableId: string): returns columns and types
  • runQuery(sql: string): executes read-only SELECT queries only
• Uses @google-cloud/bigquery
• Auth via env vars: BQ_SERVICE_ACCOUNT_JSON and GCP_PROJECT_ID
• Defensive validation and clear error messages

Requirements

• Node.js >= 18.18
• IAM roles for the service account:
  • roles/bigquery.dataViewer
  • roles/bigquery.jobUser

Installation

npm install

Running locally

This server communicates over stdio per MCP conventions.

npm start

Ensure the following environment variables are set before running:

• BQ_SERVICE_ACCOUNT_JSON: Stringified JSON of the service account key for BigQuery access. Must include client_email and private_key.
• GCP_PROJECT_ID: Your Google Cloud project ID to target for BigQuery operations.

Example .env snippet (if you manage env separately):

GCP_PROJECT_ID=your-project-id
BQ_SERVICE_ACCOUNT_JSON={"type":"service_account","project_id":"your-project-id","private_key_id":"...","private_key":"-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n","client_email":"svc@your-project-id.iam.gserviceaccount.com","client_id":"...","auth_uri":"https://accounts.google.com/o/oauth2/auth","token_uri":"https://oauth2.googleapis.com/token","auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs","client_x509_cert_url":"..."}

Tools (Commands)

All tool responses return plain JSON objects.

• listDatasets()

  • Input: none
  • Output: { datasets: [{ datasetId, location }] }

• listTables(datasetId: string)

  • Input: { datasetId }
  • Output: { datasetId, tables: [{ tableId, type }] }

• getSchema(datasetId: string, tableId: string)

  • Input: { datasetId, tableId }
  • Output: { datasetId, tableId, columns: [{ name, type, mode, description }] }

• runQuery(sql: string)

  • Input: { sql } (must start with SELECT)
  • Output: { rows: [...] }
  • Validation: If the SQL does not start with SELECT (case-insensitive, after trim), the server rejects with Only SELECT queries are allowed.

Notes

• The server initializes a BigQuery client each call using credentials from env vars.
• For SQL, useLegacySql is set to false.
• Any missing env var or invalid credentials produce clear errors before attempting BigQuery calls.