MCP toolbox with postgresql

1.txt

$docker compose up -d
$docker compose ps
NAME                      IMAGE                                                                COMMAND                  SERVICE   CREATED          STATUS                    PORTS
genai-toolbox-db-1        postgres                                                             "docker-entrypoint.s…"   db        27 minutes ago   Up 27 minutes (healthy)   0.0.0.0:5432->5432/tcp
genai-toolbox-toolbox-1   us-central1-docker.pkg.dev/database-toolbox/toolbox/toolbox:latest   "/toolbox toolbox --…"   toolbox   27 minutes ago   Up 27 minutes             0.0.0.0:5000->5000/tcp

2.txt

Query: Count all products
We have a total of 5 products in our database:

1. Product 1 - Price: $19.99
2. Product 2 - Price: $29.99
3. Product 3 - Price: $39.99
4. Product 4 - Price: $49.99
5. Product 5 - Price: $59.99


Query: List all products
Here are all the products in our database:

1. Product 1 - Price: $19.99
2. Product 2 - Price: $29.99
3. Product 3 - Price: $39.99
4. Product 4 - Price: $49.99
5. Product 5 - Price: $59.99


Query: Products with price less than 30
Here are the products with a price less than $30:

1. Product 1 - Price: $19.99
2. Product 2 - Price: $29.99


Query: Products with price more than 30
Here are the products with a price more than $30:

1. Product 3 - Price: $39.99
2. Product 4 - Price: $49.99
3. Product 5 - Price: $59.99

compose.yml

services:
  toolbox:
    # TODO: It is recommended to pin to a specific image version instead of latest.
    image:  us-central1-docker.pkg.dev/database-toolbox/toolbox/toolbox:latest
    ports:
      - "5000:5000"
    volumes:
      - ./config:/config
    command: [ "toolbox", "--tools-file", "/config/tools.yaml", "--address", "0.0.0.0"]
    depends_on:
      db:
        condition: service_healthy
    networks:
      - tool-network
  db:
    # TODO: It is recommended to pin to a specific image version instead of latest.
    image: postgres
    environment:
      POSTGRES_USER: toolbox_user
      POSTGRES_PASSWORD: my-password
      POSTGRES_DB: toolbox_db
    ports:
      - "5432:5432"
    volumes:
      - ./db:/var/lib/postgresql/data
      # This file can be used to bootstrap your schema if needed.
      # See "initialization scripts" on https://hub.docker.com/_/postgres/ for more info
      - ./config/init.sql:/docker-entrypoint-initdb.d/init.sql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U toolbox_user -d toolbox_db"]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - tool-network
networks:
  tool-network:

demo.py

import asyncio
from langgraph.prebuilt import create_react_agent
from langgraph.checkpoint.memory import MemorySaver
from toolbox_langchain import ToolboxClient
from langchain_openai import ChatOpenAI


TOOL_SERVER = "http://127.0.0.1:5000"


prompt = """
  You're a helpful product assistant. You handle product searching. 
  When the user searches for a product, mention it's name, price 
  Don't ask for confirmations from the user.
"""

queries = [
    "Count all products",
    "List all products",
    "Products with price less than 30",
    "Products with price more than 30",
]

async def run_application():
    model = ChatOpenAI(
        model="gpt-4",
        temperature=0,
        streaming=True,
        verbose=True,
    )

    # Load the tools from the Toolbox server
    client = ToolboxClient(TOOL_SERVER)
    tools = await client.aload_toolset()

    # Create the agent with the loaded tools
    agent = create_react_agent(model, tools, checkpointer=MemorySaver())

    # Run the agent with the queries
    config = {"configurable": {"thread_id": "thread-1"}}
    for query in queries:
        print(f"Query: {query}")
        inputs = {"messages": [("user", prompt + query)]}
        response = agent.invoke(inputs, stream_mode="values", config=config)
        print(response["messages"][-1].content)

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

init.sql

-- Create table products
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert initial data
INSERT INTO products (name, description, price) VALUES
('Product 1', 'Description for product 1', 19.99),
('Product 2', 'Description for product 2', 29.99),
('Product 3', 'Description for product 3', 39.99),
('Product 4', 'Description for product 4', 49.99),
('Product 5', 'Description for product 5', 59.99);

tools.yaml

sources:
  my-pg-source:
      kind: postgres
      host: db
      port: 5432
      database: toolbox_db
      user: toolbox_user
      password: my-password

tools:
  search-product:
    kind: postgres-sql
    source: my-pg-source
    description: Search for products in the database.
    statement: SELECT * FROM products;
  filter-product-by-price-by-operation:
    kind: postgres-sql
    source: my-pg-source
    description: Filter products by price using a specific operation.
    statement: SELECT * FROM products WHERE price {{operation}} {{value}};
    parameters:
      - name: operation
        type: string
        description: The operation to use for filtering (e.g., >, <, =).
      - name: value
        type: float
        description: The value to compare against.