Testing Materialize MCP Server

A quick guide for testing the POC MCP implementation in Materialize.

Setup

1. Clone and Checkout PR

git clone https://github.com/MaterializeInc/materialize.git
cd materialize
gh pr checkout 33998  # or: git fetch origin pull/33998/head:mcp-server && git checkout mcp-server

2. Start Environmentd with MCP Feature

Choose one of the following authentication methods:

Option A: No Authentication (Quick Testing)

Best for: Local development and quick testing

bin/environmentd --features=mcp --reset -- --unsafe-mode --listeners-config-path

Option B: Password Authentication (Recommended)

Best for: More realistic testing with proper authentication

# Set the system password
export MZ_EXTERNAL_LOGIN_PASSWORD_MZ_SYSTEM=mz_system

# Start environmentd with password authentication enabled
bin/environmentd --features=mcp --reset -- --unsafe-mode \
  --listeners-config-path ./src/materialized/ci/listener_configs/password.json \
  --system-parameter-default=enable_password_auth=true

Wait for the server to start (you should see log output indicating it's ready).

3. Set Up User Authentication

For Option A (No Auth):

Initialize the anonymous HTTP user with a simple request:

curl -s http://localhost:6876/api/mcp/agents \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "initialize",
    "params": {}
  }' | jq '.'

For Option B (Password Auth):

Set a password for the materialize user:

# Connect as mz_system (password: mz_system as set above)
psql -h localhost -p 6877 -U mz_system materialize

# In psql, set the materialize user password
ALTER ROLE materialize PASSWORD 'materialize' LOGIN;

# Exit psql
\q

Now you can test with Basic Auth:

# Generate base64 encoded credentials
echo -n "materialize:materialize" | base64
# Output: bWF0ZXJpYWxpemU6bWF0ZXJpYWxpemU=

# Test the connection
curl -s http://localhost:6876/api/mcp/agents \
  -H "Content-Type: application/json" \
  -H "Authorization: Basic bWF0ZXJpYWxpemU6bWF0ZXJpYWxpemU=" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "initialize",
    "params": {}
  }' | jq '.'

4. Create Test Data Products

Save the following SQL to init-mcp.sql:

-- Set the cluster to use for this session
SET CLUSTER = quickstart;

-- Create a test table
CREATE TABLE orders (
    id INT,
    customer_id INT,
    total DECIMAL(10,2),
    status TEXT,
    created_at TIMESTAMP
);

-- Create an index with a comment (this is what makes it a "data product")
CREATE INDEX orders_by_customer ON orders (customer_id);

-- Add comments to make it discoverable
COMMENT ON INDEX orders_by_customer IS 'Real-time view of orders grouped by customer. Use this to find all orders for a specific customer_id.';

-- Add comments to columns for better schema discovery
COMMENT ON COLUMN orders.id IS 'Unique order identifier';
COMMENT ON COLUMN orders.customer_id IS 'Customer who placed the order';
COMMENT ON COLUMN orders.total IS 'Order total amount in USD';
COMMENT ON COLUMN orders.status IS 'Order status: pending, completed, cancelled';
COMMENT ON COLUMN orders.created_at IS 'When the order was created';

-- Insert some test data
INSERT INTO orders VALUES 
    (1, 100, 99.99, 'completed', '2025-01-01 10:00:00'),
    (2, 100, 149.50, 'pending', '2025-01-02 11:00:00'),
    (3, 200, 75.00, 'completed', '2025-01-03 12:00:00');

-- Create another data product (materialized view)
CREATE MATERIALIZED VIEW customer_stats AS
SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(total) as total_spent,
    MAX(created_at) as last_order_date
FROM orders
GROUP BY customer_id;

CREATE INDEX customer_stats_idx ON customer_stats (customer_id);

COMMENT ON INDEX customer_stats_idx IS 'Aggregated customer statistics including order count and total spending';
COMMENT ON COLUMN customer_stats.customer_id IS 'Customer identifier';
COMMENT ON COLUMN customer_stats.order_count IS 'Total number of orders placed';
COMMENT ON COLUMN customer_stats.total_spent IS 'Total amount spent by customer';
COMMENT ON COLUMN customer_stats.last_order_date IS 'Timestamp of most recent order';

-- Grant permissions to the appropriate user
-- For Option A (no auth): grant to anonymous_http_user
-- For Option B (password auth): grants to materialize are already in place
GRANT SELECT ON orders TO anonymous_http_user;
GRANT SELECT ON customer_stats TO anonymous_http_user;
GRANT USAGE ON CLUSTER quickstart TO anonymous_http_user;

Import the test data:

# For Option A (no auth)
psql -h localhost -p 6875 -U materialize materialize < init-mcp.sql

# For Option B (password auth) - you'll be prompted for password
psql -h localhost -p 6875 -U materialize materialize < init-mcp.sql
# Password: materialize

Manual Testing

Note: Add the Authorization header to all curl commands if using Option B (Password Auth):

-H "Authorization: Basic bWF0ZXJpYWxpemU6bWF0ZXJpYWxpemU="

Tool 1: get_data_products

Purpose: Discover available data products

# Without auth (Option A)
curl -s http://localhost:6876/api/mcp/agents \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "tools/call",
    "params": {
      "name": "get_data_products",
      "arguments": {}
    }
  }' | jq '.result.content[0].text' -r | jq '.'

# With auth (Option B)
curl -s http://localhost:6876/api/mcp/agents \
  -H "Content-Type: application/json" \
  -H "Authorization: Basic bWF0ZXJpYWxpemU6bWF0ZXJpYWxpemU=" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "tools/call",
    "params": {
      "name": "get_data_products",
      "arguments": {}
    }
  }' | jq '.result.content[0].text' -r | jq '.'

Expected output: JSON array with two data products (orders_by_customer and customer_stats_idx)

AI prompt examples:

• "What data products are available?"
• "Show me all the data sources"
• "What can I query in Materialize?"

---

Tool 2: get_data_product_details

Purpose: Get schema details for a specific data product

Test with orders:

curl -s http://localhost:6876/api/mcp/agents \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 2,
    "method": "tools/call",
    "params": {
      "name": "get_data_product_details",
      "arguments": {
        "name": "\"materialize\".\"public\".\"orders\""
      }
    }
  }' | jq '.result.content[0].text' -r | jq '.'

Test with customer_stats:

curl -s http://localhost:6876/api/mcp/agents \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 3,
    "method": "tools/call",
    "params": {
      "name": "get_data_product_details",
      "arguments": {
        "name": "\"materialize\".\"public\".\"customer_stats\""
      }
    }
  }' | jq '.result.content[0].text' -r | jq '.'

Expected output: JSON Schema describing columns and types

AI prompt examples:

• "What's the schema of the orders table?"
• "Show me details about customer_stats"
• "What fields are in the orders data product?"

---

Tool 3: query

Purpose: Execute SQL queries against data products

curl -s http://localhost:6876/api/mcp/agents \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 4,
    "method": "tools/call",
    "params": {
      "name": "query",
      "arguments": {
        "cluster": "quickstart",
        "sql_query": "SELECT * FROM orders WHERE customer_id = 100"
      }
    }
  }' | jq '.result.content[0].text' -r | jq '.'

Expected output: Query results as JSON array

AI prompt examples:

• "Show me all orders for customer 100"
• "What are the customer statistics?"
• "Find pending orders"

Observatory Endpoint Testing

The observatory endpoint (/api/mcp/observatory) only allows queries against mz_* system catalog tables.

curl -s http://localhost:6876/api/mcp/observatory \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 5,
    "method": "tools/call",
    "params": {
      "name": "query_system_catalog",
      "arguments": {
        "sql_query": "SELECT name, type FROM mz_objects WHERE type = '\''table'\'' LIMIT 5"
      }
    }
  }' | jq '.result.content[0].text' -r | jq '.'

VS Code MCP Integration

Configuration

Add to your VS Code MCP settings file (.vscode/mcp.json or global settings):

Option A: No Authentication

{
  "mcpServers": {
    "materialize-agents": {
      "url": "http://localhost:6876/api/mcp/agents",
      "type": "http"
    },
    "materialize-observatory": {
      "url": "http://localhost:6876/api/mcp/observatory",
      "type": "http"
    }
  }
}

Option B: Password Authentication (Recommended)

{
  "mcpServers": {
    "materialize-agents": {
      "url": "http://localhost:6876/api/mcp/agents",
      "type": "http",
      "headers": {
        "Authorization": "Basic ${input:mz-auth}"
      }
    },
    "materialize-observatory": {
      "url": "http://localhost:6876/api/mcp/observatory",
      "type": "http",
      "headers": {
        "Authorization": "Basic ${input:mz-auth}"
      }
    }
  },
  "inputs": [
    {
      "type": "promptString",
      "id": "mz-auth",
      "description": "Materialize Basic Auth (base64 encoded username:password)",
      "password": true
    }
  ]
}

When VS Code prompts for mz-auth, enter:

bWF0ZXJpYWxpemU6bWF0ZXJpYWxpemU=

This is the base64-encoded value of materialize:materialize. To generate your own:

echo -n "username:password" | base64

Using the MCP Server in VS Code

Once configured, you can interact with Materialize through Claude in VS Code:

Example prompts:

• "What data products are available?"
• "Show me all the data sources"
• "What can I query in Materialize?"
• "Show me the schema for the orders table"
• "Query all orders for customer 100"

Current Limitations

⚠️ Known Issues:

1. Some VS Code MCP integrations may have issues with the Authorization header
2. OAuth-only enforcement in certain MCP client implementations

Alternative: Claude Desktop App

If VS Code integration has issues, try the Claude Desktop app with this configuration (~/Library/Application Support/Claude/claude_desktop_config.json on macOS):

{
  "mcpServers": {
    "materialize-agents": {
      "url": "http://localhost:6876/api/mcp/agents",
      "type": "http",
      "headers": {
        "Authorization": "Basic bWF0ZXJpYWxpemU6bWF0ZXJpYWxpemU="
      }
    },
    "materialize-observatory": {
      "url": "http://localhost:6876/api/mcp/observatory",
      "type": "http",
      "headers": {
        "Authorization": "Basic bWF0ZXJpYWxpemU6bWF0ZXJpYWxpemU="
      }
    }
  }
}

Security Notes

• Option A (No Auth): Only suitable for local development. Never use in production.
• Option B (Password Auth): Better for testing authentication flows, but still requires --unsafe-mode
• Production: Would require proper OAuth/OIDC integration and removal of --unsafe-mode

Related Links

• PR: MaterializeInc/materialize#33998
• MCP Specification: https://modelcontextprotocol.io/
• Materialize HTTP API Docs: https://materialize.com/docs/integrations/http-api/