14. MCP Toolbox for Databases - PostgreSQL

-- Connect to PostgreSQL
psql -U postgres

-- Create database
CREATE DATABASE ecommerce_db;
\c ecommerce_db

-- Create tables
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    price DECIMAL(10,2),
    stock_quantity INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    join_date DATE
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2),
    status VARCHAR(50)
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    unit_price DECIMAL(10,2)
);

-- Insert sample data
INSERT INTO products (name, category, price, stock_quantity) VALUES
    ('Laptop Pro', 'Electronics', 1299.99, 50),
    ('Wireless Mouse', 'Electronics', 29.99, 200),
    ('Office Chair', 'Furniture', 399.99, 30),
    ('USB-C Hub', 'Electronics', 49.99, 150),
    ('Monitor 4K', 'Electronics', 599.99, 40);

INSERT INTO customers (name, email, join_date) VALUES
    ('Alice Johnson', '[email protected]', '2024-01-15'),
    ('Bob Smith', '[email protected]', '2024-02-20'),
    ('Carol Davis', '[email protected]', '2024-03-10');

INSERT INTO orders (customer_id, total_amount, status) VALUES
    (1, 1329.98, 'completed'),
    (2, 429.99, 'pending'),
    (1, 649.98, 'completed'),
    (3, 399.99, 'completed');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 1, 1299.99),
    (1, 2, 1, 29.99),
    (2, 3, 1, 399.99),
    (3, 4, 1, 49.99),
    (3, 2, 2, 29.99),
    (4, 3, 1, 399.99);

-- Create user with limited privileges
CREATE USER adk_agent WITH PASSWORD 'secure_password';

-- Grant read access to our tables
GRANT CONNECT ON DATABASE ecommerce_db TO adk_agent;
GRANT USAGE ON SCHEMA public TO adk_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO adk_agent;

-- For write operations (optional, uncomment if needed)
-- GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO adk_agent;

pip install toolbox-core

# Option A: Homebrew (macOS/Linux)
brew install mcp-toolbox

# Option B: Binary download (Linux AMD64)
export VERSION=0.21.0  # Check releases page for latest
curl -L -o toolbox https://storage.googleapis.com/genai-toolbox/v$VERSION/linux/amd64/toolbox
chmod +x toolbox

# Option C: Binary download (macOS Apple Silicon)
export VERSION=0.21.0
curl -L -o toolbox https://storage.googleapis.com/genai-toolbox/v$VERSION/darwin/arm64/toolbox
chmod +x toolbox

# Option D: Binary download (macOS Intel)
export VERSION=0.21.0
curl -L -o toolbox https://storage.googleapis.com/genai-toolbox/v$VERSION/darwin/amd64/toolbox
chmod +x toolbox

# MCP Toolbox for Databases - PostgreSQL Configuration
# See: https://googleapis.github.io/genai-toolbox/getting-started/configure/

sources:
  ecommerce_db:
    kind: postgres
    host: ${DB_HOST:localhost}
    port: ${DB_PORT:5432}
    database: ${DB_NAME:ecommerce_db}
    user: ${DB_USER:postgres}
    password: ${DB_PASSWORD:postgres}

tools:
  get_products:
    kind: postgres-sql
    source: ecommerce_db
    description: "Retrieve all products from the catalog with pricing and inventory"
    statement: SELECT * FROM products ORDER BY name

  search_products:
    kind: postgres-sql
    source: ecommerce_db
    description: "Search products by category"
    parameters:
      - name: category
        type: string
        description: "Product category to filter by (e.g., 'Electronics', 'Furniture')"
    statement: SELECT * FROM products WHERE category ILIKE '%' || $1 || '%'

  get_customers:
    kind: postgres-sql
    source: ecommerce_db
    description: "Get all customers"
    statement: SELECT id, name, email FROM customers ORDER BY name

  get_inventory_value:
    kind: postgres-sql
    source: ecommerce_db
    description: "Calculate total inventory value"
    statement: |
      SELECT 
        COUNT(*) as total_products,
        SUM(stock_quantity) as total_items,
        SUM(price * stock_quantity) as total_value
      FROM products

# Toolsets group related tools together
toolsets:
  ecommerce:
    - get_products
    - search_products
    - get_customers
    - get_inventory_value

# Run the Toolbox server with our configuration
./toolbox --tools-file "tools.yaml" --port 5050

# With the UI enabled (recommended for development):
./toolbox --tools-file "tools.yaml" --port 5050 --ui

# If installed via Homebrew, toolbox is in the PATH:
toolbox --tools-file "tools.yaml" --port 5050 --ui

# The server will start at http://localhost:5050
# With --ui flag, access the web interface at http://localhost:5050/ui

# Note: Use port 5050 or higher on macOS (port 5000 is used by AirPlay)

import os
from google.adk.agents import Agent
from toolbox_core import ToolboxSyncClient
from dotenv import load_dotenv

load_dotenv()

# Connect to the MCP Toolbox server
TOOLBOX_URL = os.getenv("TOOLBOX_URL", "http://localhost:5050")

try:
    toolbox_client = ToolboxSyncClient(TOOLBOX_URL)
    db_tools = toolbox_client.load_toolset("ecommerce")
    print(f"Loaded {len(db_tools)} tools from MCP Toolbox")
except Exception as e:
    print(f"WARNING: Could not connect to MCP Toolbox at {TOOLBOX_URL}")
    print(f"Error: {e}")
    print("Make sure the Toolbox server is running: toolbox --tools-file ../tools.yaml --port 5050")
    db_tools = []


root_agent = Agent(
    model="gemini-2.5-flash",
    name="postgres_data_agent",
    description="A data analysis agent for PostgreSQL e-commerce database",
    instruction="""You are a database analyst assistant with access to an e-commerce PostgreSQL database.

Help users:
- Query product information and inventory
- Look up customer data
- Calculate inventory metrics and values
- Search products by category

Use the database tools to fetch real data. Present results clearly.""",
    tools=db_tools,
)

import asyncio
from google.adk.agents import Agent
from google.adk.runners import Runner
from google.adk.sessions import InMemorySessionService
from google.genai import types
from toolbox_core import ToolboxClient

async def main():
    # Load tools using async context manager
    async with ToolboxClient("http://127.0.0.1:5050") as client:
        db_tools = await client.load_toolset("ecommerce")
        
        # Create the agent with loaded tools
        agent = Agent(
            model="gemini-2.5-flash",
            name="postgres_data_agent",
            instruction="You are a database analyst assistant...",
            tools=db_tools,
        )
        
        # Set up runner
        session_service = InMemorySessionService()
        runner = Runner(
            agent=agent,
            app_name="data_agent_app",
            session_service=session_service,
        )
        
        # Create session and run query
        session = await session_service.create_session(
            app_name="data_agent_app",
            user_id="analyst",
        )
        
        query = "What products do we have in inventory?"
        content = types.Content(role="user", parts=[types.Part(text=query)])
        
        async for event in runner.run_async(
            user_id="analyst",
            session_id=session.id,
            new_message=content
        ):
            if hasattr(event, 'content') and event.content and event.content.parts:
                for part in event.content.parts:
                    if hasattr(part, 'text') and part.text:
                        print(part.text)

if __name__ == "__main__":
    asyncio.run(main())

# Terminal 1: Start the Toolbox server
toolbox --tools-file tools.yaml --port 5050

# Terminal 2: Set API key, activate venv, and start the ADK agent
export GOOGLE_API_KEY=your_api_key_here
source .venv/bin/activate
adk web

User: "List all products in the database"

Agent: Here are all the products in the database:

Product ID: 1
- Name: Laptop Pro
- Category: Electronics
- Price: $1,299.99
- Stock Quantity: 50

Product ID: 2
- Name: Wireless Mouse
- Category: Electronics
- Price: $29.99
- Stock Quantity: 200

Product ID: 3
- Name: Office Chair
- Category: Furniture
- Price: $399.99
- Stock Quantity: 30

---

User: "What's the total value of our inventory?"

Agent: Here are the inventory metrics:

- Total Products: 3
- Total Items in Stock: 280
- Total Inventory Value: $82,999.20

The Electronics category holds the highest inventory value with 
the Laptop Pro contributing significantly at $64,999.50 (50 units × $1,299.99).

-- Create read-only user
CREATE USER agent_readonly WITH PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE ecommerce_db TO agent_readonly;
GRANT USAGE ON SCHEMA public TO agent_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_readonly;

# Enable logging in the agent
def log_query(user_query: str, sql_query: str, results: dict):
    """Log database queries for audit purposes"""
    logging.info(f"User: {user_query} | SQL: {sql_query} | Rows: {results.get('row_count', 0)}")