{ JTR }

Trying Out MCP


MCP
database learning

Trying Out MCP

I recently started exploring MCP (Model Context Protocol) and wanted to share what I learned. To keep things practical, I’ll use a Postgres example from our database: querying inventory data to find items that need restocking.

Quick glance: what MCP returns

Container: Kitchen Pantry
Item: Coffee Beans
Threshold: 200
Current quantity: 150

Container: Kitchen Pantry
Item: Sugar
Threshold: 100
Current quantity: 50

Container: Garage Storage
Item: Motor Oil
Threshold: 2
Current quantity: 1

This is especially useful when you’re in a meeting and need quick answers to keep the discussion moving—ask the AI, get a concise list, and decide next steps without opening the console.

What is MCP?

MCP (Model Context Protocol) is a standard that connects AI systems with external tools and data sources. Think of it as a bridge that lets you query databases, call APIs, and access services in a standardized way.

For this example, I’ll show how MCP’s Postgres integration makes querying data much easier than the traditional Rails console approach.

The Example Scenario

Our database has:

  • Users who own multiple containers
  • Containers that hold various items
  • Items with quantities that need monitoring
  • Thresholds that trigger notifications when items run low

The goal is to query which containers have items below their threshold.

Our Rails Models

Here’s what our existing models look like:

# app/models/user.rb
class User < ApplicationRecord
  has_many :containers
  has_many :items, through: :containers
end

# app/models/container.rb
class Container < ApplicationRecord
  belongs_to :user
  has_many :items
end

# app/models/item.rb
class Item < ApplicationRecord
  belongs_to :container
  has_one :user, through: :container
end

The Query: Finding Low Stock Containers (ActiveRecord)

We want to:

  1. Find items below their threshold
  2. Group them by container
  3. Include user info for notifications

Here’s how to do it in the Rails console with ActiveRecord:

# Find items below threshold with their container and user
low_stock_items = Item
  .joins(container: :user)
  .where('items.quantity < items.threshold')
  .select(
    'users.id AS user_id, users.name AS user_name, users.email AS user_email, '
    'containers.id AS container_id, containers.name AS container_name, containers.location AS container_location, '
    'items.name AS item_name, items.quantity, items.threshold, items.unit'
  )

# Group and format by container
results = {}
low_stock_items.each do |row|
  key = "#{row.user_id}_#{row.container_id}"
  results[key] ||= {
    user_id: row.user_id,
    user_name: row.user_name,
    user_email: row.user_email,
    container_id: row.container_id,
    container_name: row.container_name,
    container_location: row.container_location,
    low_stock_items: []
  }

  results[key][:low_stock_items] << {
    item_name: row.item_name,
    current_quantity: row.quantity,
    threshold: row.threshold,
    unit: row.unit,
    shortage: row.threshold - row.quantity
  }
end

containers_with_low_stock = results.values

Sample Output

Running this query gives us notification-ready data:

[
  {
    "user_id": 1,
    "user_name": "Alice Johnson",
    "user_email": "[email protected]",
    "container_id": 1,
    "container_name": "Kitchen Pantry",
    "container_location": "Home",
    "low_stock_count": 2,
    "low_stock_items": [
      {
        "item_name": "Coffee Beans",
        "current_quantity": 150,
        "threshold": 200,
        "unit": "grams",
        "shortage": 50
      },
      {
        "item_name": "Sugar",
        "current_quantity": 50,
        "threshold": 100,
        "unit": "grams",
        "shortage": 50
      }
    ]
  },
  {
    "user_id": 1,
    "user_name": "Alice Johnson",
    "user_email": "[email protected]",
    "container_id": 2,
    "container_name": "Garage Storage",
    "container_location": "Garage",
    "low_stock_count": 1,
    "low_stock_items": [
      {
        "item_name": "Motor Oil",
        "current_quantity": 1,
        "threshold": 2,
        "unit": "bottles",
        "shortage": 1
      }
    ]
  }
]

The Traditional Way: Rails Console

First, let’s see how you’d typically do this in a Rails console:

# In Rails console
rails console

# First, find all items below threshold
low_stock_items = Item.joins(:container)
                      .where('items.quantity < items.threshold')
                      .includes(container: :user)

# Then manually group and format the data
results = {}
low_stock_items.each do |item|
  container = item.container
  user = container.user
  
  key = "#{user.id}_#{container.id}"
  
  results[key] ||= {
    user_id: user.id,
    user_name: user.name,
    user_email: user.email,
    container_id: container.id,
    container_name: container.name,
    container_location: container.location,
    low_stock_items: []
  }
  
  results[key][:low_stock_items] << {
    item_name: item.name,
    current_quantity: item.quantity,
    threshold: item.threshold,
    unit: item.unit,
    shortage: item.threshold - item.quantity
  }
end

# Convert to array
containers_with_low_stock = results.values

# View the results
puts containers_with_low_stock.inspect

This approach involves:

  • Query the data with ActiveRecord
  • Loop through results to group by container
  • Manually format the data structure
  • Convert to the desired output format

The MCP Way: Direct SQL Query

Here’s the same thing with MCP using a direct SQL query:

// Using MCP Postgres tool
const lowStockContainers = await mcp.postgres.query({
  query: `
    SELECT 
      u.id AS user_id,
      u.name AS user_name,
      u.email AS user_email,
      c.id AS container_id,
      c.name AS container_name,
      json_agg(
        json_build_object(
          'item_name', i.name,
          'current_quantity', i.quantity,
          'threshold', i.threshold,
          'shortage', i.threshold - i.quantity
        )
      ) AS low_stock_items
    FROM users u
    JOIN containers c ON c.user_id = u.id
    JOIN items i ON i.container_id = c.id
    WHERE i.quantity < i.threshold
    GROUP BY u.id, u.name, u.email, c.id, c.name
  `
});

// Results come back formatted and ready to use
console.log(lowStockContainers);

Note: in practice you usually won’t type this SQL yourself. With MCP, you ask your AI assistant in chat (e.g., “show containers with items below threshold”) and it runs the Postgres query for you, returning clean, structured JSON you can use immediately.

Comparing the Two Approaches

Rails Console:

  • Requires multiple steps (query, loop, format)
  • You can use your query to move it to a model method

MCP with Postgres:

  • Just ask the AI to run the query
  • You can also ask it to provide the ActiveRecord version of the query
  • Cost tokens 💰

Next, I’ll explore other MCP tools like Figma MCP for UI/UX design and Browser Tools MCP for browser monitoring and interaction.