magento-sql-mcp-server

v2.4.0

Published

a month ago

Professional MCP server for read-only Magento 2 / Adobe Commerce MySQL queries. Supports DDEV auto-detect, SSH tunnels, direct remote DB, schema exploration, config inspection, and canned debug prompts for Cursor, Claude, VS Code, and LibreChat.

Magento SQL MCP Server

An MCP (Model Context Protocol) server that provides AI assistants with read-only access to a Magento 2 / Adobe Commerce MySQL database. It auto-detects local DDEV environments, connects to Adobe Commerce Cloud via CLI tunnels, and ships 50+ tools for orders, catalog, customers, CMS, config, indexers, and diagnostics.

Complementary MCP: Documentation MCPs (see Related MCPs) cover official Adobe Commerce docs. This package covers your live database.

Features

50+ read-only tools for orders, products, customers, CMS, config, EAV, MSI, B2B, staging, cron, and indexers
Read-only by design — blocks INSERT/UPDATE/DELETE/DDL; masks password, token, and credit-card columns
Multi-environment profiles — local DDEV, Adobe Commerce Cloud (staging/production), direct remote DB, SSH tunnel
Zero-config local dev — reads app/etc/env.php, auto-detects DDEV MySQL port (cached 120s)
Commerce-aware — detects staging (updated_in), MSI, B2B; EAV joins use row_id on Commerce
CMS helpers — get_cms_page, audit_cms_page_blocks
Parameter aliases — query to sql, path to pathPattern, entity_type to entity_type_code
Per-call profile override — pass profile: "staging" on any tool without restarting MCP
MCP standards — Zod schemas, structured output, tool annotations, server instructions
Resources and prompts — table reference, EAV cheatsheet, order-debug and MSI-troubleshoot workflows
Dual transport — stdio (default) and optional HTTP for LibreChat / remote hosts

Quick setup for Cursor

Prerequisites

| Requirement | Notes | |-------------|-------| | Node.js 18+ | node --version | | Magento project | Must contain app/etc/env.php | | DDEV | Optional; auto-detected for local profiles | | Adobe Commerce Cloud CLI | Required for Cloud staging/production tunnels |

Option A: npx (recommended)

Open Cursor → Settings → MCP → Add new MCP server
Configure:

| Field | Value | |-------|-------| | Name | magento-sql | | Type | command | | Command | npx -y magento-sql-mcp-server |

Set environment variables:

| Variable | Required | Example | |----------|----------|---------| | MAGENTO_ROOT | Yes | /absolute/path/to/magento | | MAGENTO_SQL_PROFILE | No | local (defaults to auto-detect) |

Restart Cursor. Verify with: Call get_connection_status

Option B: Project config (`.cursor/mcp.json`)

Create in your Magento project root:

{
  "mcpServers": {
    "magento-sql": {
      "command": "npx",
      "args": ["-y", "magento-sql-mcp-server"],
      "env": {
        "MAGENTO_ROOT": "/absolute/path/to/magento",
        "MAGENTO_SQL_PROFILE": "local"
      }
    }
  }
}

Run from source (development):

{
  "mcpServers": {
    "magento-sql": {
      "command": "node",
      "args": ["/absolute/path/to/magento-sql-mcp-server/dist/index.js"],
      "env": {
        "MAGENTO_ROOT": "/absolute/path/to/magento",
        "MAGENTO_SQL_PROFILE": "local"
      }
    }
  }
}

Option C: Init profile scaffold

npx magento-sql-mcp-server --init

Creates .cursor/magento-sql-mcp.json from examples/magento-sql-mcp.example.json.

Profiles named default, local, or dev auto-fallback to DDEV/env.php detection without a config file.

Setup for other tools

Claude Desktop

| OS | Config path | |----|-------------| | macOS | ~/Library/Application Support/Claude/claude_desktop_config.json | | Windows | %APPDATA%\Claude\claude_desktop_config.json | | Linux | ~/.config/Claude/claude_desktop_config.json |

Use the same mcpServers JSON as Cursor.

VS Code / GitHub Copilot

.vscode/mcp.json — same env vars; use "servers" key instead of "mcpServers".

Windsurf

~/.codeium/windsurf/mcp_config.json — same structure as Claude Desktop.

Connection profiles

Profile file

Create .cursor/magento-sql-mcp.json in your Magento project (see examples/magento-sql-mcp.example.json):

{
  "defaultProfile": "local",
  "profiles": {
    "local": { "mode": "auto" },
    "staging": {
      "mode": "direct",
      "database": {
        "host": "127.0.0.1",
        "port": 30000,
        "dbname": "your_project_stg",
        "username": "your_project_stg",
        "password": "${MAGENTO_STAGING_DB_PASSWORD}"
      }
    },
    "production": {
      "mode": "direct",
      "database": {
        "host": "127.0.0.1",
        "port": 30000,
        "dbname": "your_project_id",
        "username": "your_project_id",
        "password": "${MAGENTO_PRODUCTION_DB_PASSWORD}"
      }
    }
  }
}

Set passwords in MCP env (never commit credentials):

"env": {
  "MAGENTO_STAGING_DB_PASSWORD": "from-tunnel-info",
  "MAGENTO_PRODUCTION_DB_PASSWORD": "from-tunnel-info",
  "MAGENTO_SQL_PROFILE": "local"
}

Global shared profiles: ~/.config/magento-sql-mcp/config.json

Connection modes

| Mode | Use case | |------|----------| | auto | Local dev: reads env.php, detects DDEV port | | env-php | Same as auto | | direct | Connect to host:port (Cloud tunnel on 127.0.0.1:30000, VPN, allowlisted IP) | | ssh-tunnel | MCP opens SSH port forward (non-Cloud hosts with standard SSH keys) |

Config values support ${ENV_VAR} references for secrets.

Per-tool override: pass profile on any tool call without changing MAGENTO_SQL_PROFILE.

Adobe Commerce Cloud setup

Cloud MySQL runs on database.internal:3306 inside the environment. It is not reachable from the public internet. Use an SSH tunnel (same approach as DBeaver with SSH enabled).

Laptop                    Cloud environment
127.0.0.1:30000  --SSH--> database.internal:3306

Step 1: Install and authenticate Cloud CLI

Documentation: Adobe Commerce Cloud CLI

magento-cloud login
magento-cloud auth:info
magento-cloud project:list
magento-cloud environments -p YOUR_PROJECT_ID

Non-interactive auth: magento-cloud auth:api-token-login or export MAGENTO_CLOUD_CLI_TOKEN=...

Step 2: Open tunnel

# Staging
magento-cloud tunnel:open -p YOUR_PROJECT_ID -e staging

# Production
magento-cloud tunnel:open -p YOUR_PROJECT_ID -e production

Example output:

SSH tunnel opened to database at: mysql://user:[email protected]:30000/dbname?compression=1

Default ports (single environment open):

| Port | Service | |------|---------| | 30000 | MySQL (primary) | | 30001 | MySQL slave / MBI | | 30002+ | OpenSearch, Valkey, etc. |

Note: Staging and production both use port 30000. Close the current tunnel before opening another:

magento-cloud tunnel:close
magento-cloud tunnel:open -p YOUR_PROJECT_ID -e staging

Step 3: Get credentials

magento-cloud tunnel:info -p YOUR_PROJECT_ID -e staging
magento-cloud tunnel:info -P database

On the remote container (SSH):

echo $MAGENTO_CLOUD_RELATIONSHIPS | base64 -d | json_pp

CLI reference: Cloud CLI reference

Step 4: Configure MCP profiles

Cloud profiles use direct mode to 127.0.0.1:30000. The tunnel must remain open while MCP is connected.

Add .cursor/mcp.json and .cursor/magento-sql-mcp.json to .gitignore.

Step 5: Switch environments

| Target | Steps | |--------|-------| | Local | tunnel:close → MAGENTO_SQL_PROFILE=local → reload MCP | | Staging | tunnel:open -e staging → MAGENTO_SQL_PROFILE=staging or profile: "staging" per tool | | Production | tunnel:open -e production → MAGENTO_SQL_PROFILE=production |

Step 6: Verify connection

magento-cloud tunnels

MCP tools:

get_connection_status (with profile: "staging" or "production")
detect_magento_environment

| Check | Staging | Production | |-------|---------|------------| | Database name | Often *_stg suffix | Usually project ID | | Cloud URL | mcstaging.yourdomain.com | mcprod.yourdomain.com | | Order volume (7d) | Typically low | Active traffic |

magento-cloud url -p YOUR_PROJECT_ID -e staging

Step 7: Close tunnel

magento-cloud tunnel:close

Cloud config URLs vs storefront URLs

core_config_data base URLs (web/unsecure/base_url, web/secure/base_url) on staging often still show the production domain (DB cloned from production). Actual storefront URLs are set by Cloud routes and Fastly.

Timezone-aware order queries

Store timezone: general/locale/timezone in core_config_data. Order created_at is stored in UTC. Convert store-local date ranges to UTC before querying sales_order. Use get_magento_config with pathPattern: "general/locale/%" to read the timezone.

Tools

Call list_available_tools for the full catalog with edition tags (OSS / MSI / B2B / Commerce).

Connection and environment

| Tool | Description | |------|-------------| | list_connection_profiles | List configured DB profiles | | get_connection_status | Test connectivity, host, database, latency | | detect_magento_environment | Detect OSS vs Commerce, MSI, B2B, staging columns | | run_database_health_check | Snapshot: connection, indexers, crons, queue backlog |

SQL and schema

| Tool | Parameters | Description | |------|------------|-------------| | execute_select_query | sql or query, limit?, profile? | Read-only SELECT/SHOW/DESCRIBE/EXPLAIN (auto LIMIT 100) | | validate_select_query | sql or query | Validate SQL safety without executing | | explain_select_query | sql or query | EXPLAIN plan for a SELECT | | list_tables | pattern? | List tables (optional SQL LIKE pattern) | | describe_table | table | Column definitions from INFORMATION_SCHEMA | | search_columns | pattern | Find tables containing a column name | | get_table_indexes | table | Index details | | get_foreign_keys | table | Foreign key relationships | | count_table_rows | table | Row count for one table | | get_largest_tables | limit? | Top tables by storage size |

Catalog and products

| Tool | Tag | Description | |------|-----|-------------| | find_product_by_sku | OSS | Product entity + stock + websites | | get_product_attributes | OSS | Name, price, status, visibility, url_key | | get_configurable_children | OSS | Configurable to simple child SKUs | | get_product_categories | OSS | Category assignments with names | | get_eav_attribute | OSS | Attribute metadata + join hints | | get_catalog_rule_price | OSS | Indexed catalog rule price | | get_msi_stock_status | MSI | Physical qty, reservations, salable qty | | get_staging_upcoming_updates | Commerce | Future staging campaigns for a SKU |

Sales and customers

| Tool | Description | |------|-------------| | find_order_by_increment_id | Order header + line items | | find_customer_by_email | Exact email only + order stats | | find_customers_by_name | Firstname/lastname LIKE search | | find_active_quote_by_email | Most recent active cart | | get_active_quote_items | Quote line items with parent-child nesting | | get_order_shipment_tracks | Shipment tracking numbers | | get_order_tax_breakdown | Tax rates applied to an order | | get_b2b_negotiable_quotes | B2B negotiable quotes (optional company_id) |

Operations, CMS, and config

| Tool | Description | |------|-------------| | get_magento_config | core_config_data with value_status + scope inheritance | | get_cms_page | CMS page by identifier + embedded block IDs | | get_cms_block | CMS block by identifier or block_id | | audit_cms_page_blocks | Active/inactive audit of blocks in a page | | get_indexer_status | indexer_state + mview_state | | get_cron_schedule | Recent cron entries | | get_failed_cron_jobs | Failed/stuck crons (24h) | | get_db_queue_backlog | Queue backlog (sampled on large DBs) | | get_store_hierarchy | Websites, store groups, store views | | get_module_versions | Installed module versions | | get_url_rewrite | URL rewrite lookup | | audit_plaintext_secrets | Flag plaintext secrets in config | | get_heavy_log_tables | Oversized log/visitor tables | | list_available_tools | Meta-tool: categorized tool catalog |

Edition-specific tools return a clear error if required tables are missing.

Resources

| URI | Description | |-----|-------------| | magento://schema/common-tables | Common Magento tables by domain | | magento://schema/groups | Table group index (JSON) | | magento://schema/group/{slug} | Tables in a group (catalog, sales, customer, eav, msi, ...) | | magento://schema/eav-cheatsheet | EAV entity types, attribute codes, value tables | | magento://help/tools | Full tool catalog markdown | | magento://server/info | Server version and capabilities | | magento://connection/status | Live connection status (JSON) |

Prompts

| Prompt | Arguments | Description | |--------|-----------|-------------| | order-debug | increment_id | Investigate order, items, addresses, status history | | catalog-product-check | sku | Product entity, EAV, stock, URL rewrite | | customer-lookup | email | Customer account, group, recent orders | | config-inspector | path_pattern | Read store configuration paths | | indexer-status-check | | Review indexer and mview health | | checkout-funnel-debug | email? | Trace quote to order conversion | | msi-troubleshoot | sku | Diagnose MSI salable qty issues | | b2b-company-audit | company_id | Audit B2B company and quotes | | staging-campaign-viewer | sku | View upcoming staging campaigns |

Usage examples

| What you ask | What happens | |--------------|--------------| | "How many orders yesterday and total revenue?" | Timezone-aware query on sales_order | | "Debug order 1000203870" | find_order_by_increment_id + line items | | "What is the store timezone?" | get_magento_config on general/locale/timezone | | "Check Fastly config on staging" | get_magento_config with profile: "staging" | | "Audit CMS blocks on the home page" | audit_cms_page_blocks | | "Is this Commerce with MSI?" | detect_magento_environment | | "Why is salable qty 0 for SKU X?" | msi-troubleshoot + get_msi_stock_status | | "Show indexer and failed cron status" | run_database_health_check |

How it works

+-------------+     +---------------------------+     +-----------------------------+
|  AI Client  |---->|  MCP Server (stdio/HTTP)  |---->|  MySQL (read-only)          |
|  Cursor,    |<----|  50+ Tools                |<----|  Magento / Adobe Commerce   |
|  Claude,    |     |  7 Resources, 9 Prompts   |     +-----------------------------+
|  VS Code    |     +---------------------------+
+-------------+
                      |
                      +-- Profile: MAGENTO_SQL_PROFILE -> config JSON
                      +-- auto mode: env.php + DDEV port (cached 120s)
                      +-- Cloud: magento-cloud tunnel -> 127.0.0.1:30000
                      +-- Query validation: read-only only
                      +-- Auto LIMIT 100 (max 1000)
                      +-- Sensitive column masking
                      +-- Commerce staging: updated_in = 2147483647

MCP host starts the server with MAGENTO_ROOT pointing at your Magento project
resolveConnection() loads profile from .cursor/magento-sql-mcp.json or auto-detects
For local DDEV: reads app/etc/env.php, runs ddev describe -j once (cached)
Tools run validated read-only SQL or canned queries with Magento-aware joins
Results return as structured JSON with Zod schemas

HTTP transport

npx magento-sql-mcp-server --http
# listens on http://localhost:3100 (override with MCP_HTTP_PORT)

LibreChat librechat.yaml:

mcpServers:
  magento-sql:
    type: streamable-http
    url: http://localhost:3100
    initTimeout: 30000

Configuration

Environment variables

| Variable | Description | |----------|-------------| | MAGENTO_ROOT | Magento project root (must contain app/etc/env.php) | | MAGENTO_SQL_PROFILE | Active profile (default, local, staging, production) | | MAGENTO_SQL_MODE | Override mode: auto, direct, ssh-tunnel, env-php | | MAGENTO_SQL_HOST | DB host override | | MAGENTO_SQL_PORT | DB port override | | MAGENTO_SQL_DATABASE | Database name override | | MAGENTO_SQL_USER | DB username override | | MAGENTO_SQL_PASSWORD | DB password override | | MAGENTO_STAGING_DB_PASSWORD | Staging password for profile ${...} refs | | MAGENTO_PRODUCTION_DB_PASSWORD | Production password for profile ${...} refs | | MAGENTO_SQL_SSH_HOST | SSH tunnel host override | | MAGENTO_SQL_SSH_USER | SSH tunnel user override | | MCP_HTTP_PORT | HTTP transport port (default: 3100) |

Per-tool overrides: magentoRoot and profile arguments on most tools.

Troubleshooting

MCP server failed to start

Verify Node.js 18+: node --version
Test manually: npx magento-sql-mcp-server (should print "running on stdio")
Ensure MAGENTO_ROOT points to a directory with app/etc/env.php

Profile `local` not found

v2.4.0+ auto-fallbacks local/default/dev to auto-detect. Upgrade or run:

npx magento-sql-mcp-server --init

Connection refused on Cloud (port 30000)

Tunnel not running: magento-cloud tunnel:open -p PROJECT_ID -e staging
Wrong environment: magento-cloud tunnels then close and reopen
Tunnel dropped after reboot: re-run tunnel:open

Connected to wrong environment

Run get_connection_status and check database. Staging names often end in _stg; production matches project ID.

Missing password environment variable

Copy password from magento-cloud tunnel:info into MCP env. Do not commit it.

DDEV connection refused / wrong port

Ensure DDEV is running: ddev start
DDEV port is cached for 120s after first discovery

Tool parameter errors

| Use | Instead of | |-----|------------| | sql | query | | pathPattern | path | | entity_type_code | entity_type |

Slow queue / health check tools

On large databases, queue backlog uses sampled counts. Check sampled: true in results.

Green dot does not appear in Cursor

Restart Cursor
Refresh MCP server in settings
Check Output panel for errors

Security

All queries validated as read-only before execution
Auto LIMIT (default 100, max 1000) on SELECT without explicit LIMIT
Password, token, and credit-card columns masked in results
Customer/admin password hashes never exposed
Use read-only MySQL users for Cloud profiles when available
Never commit credentials; use MCP env or ${ENV_VAR} in config JSON
Close Cloud tunnels when finished; avoid heavy full-table scans on production

Development

Run from source

git clone https://github.com/jigarkkarangiya/magento-sql-mcp-server.git
cd magento-sql-mcp-server
npm install
npm run build
MAGENTO_ROOT=/path/to/magento npm start

Tests

npm test
MAGENTO_ROOT=/path/to/magento npm run test:live
MAGENTO_ROOT=/path/to/magento npm run test:scenarios

Project structure

magento-sql-mcp-server/
├── src/                 # MCP server source
├── scripts/             # live-tool-smoke.ts, scenario-benchmark.ts
├── tests/               # unit tests
├── examples/            # magento-sql-mcp.example.json
└── dist/                # compiled JS (npm run build)

Requirements

| Requirement | Required for | |-------------|--------------| | Node.js 18+ | All modes | | PHP CLI | auto mode (reads env.php) | | DDEV CLI | Optional; local auto-detect | | Adobe Commerce Cloud CLI | Cloud staging/production tunnels | | OpenSSH client | ssh-tunnel mode | | MySQL read access | All modes |

Find this MCP

| Registry | Link | |----------|------| | npm | npmjs.com/package/magento-sql-mcp-server | | GitHub | github.com/jigarkkarangiya/magento-sql-mcp-server |

Related MCPs

Documentation MCPs for Adobe Commerce and related platforms. Install alongside this server for docs + database coverage.

| Package | npm | Description | |---------|-----|-------------| | adobe-commerce-docs-mcp | npm | Merchant, admin, cloud, operations docs (Experience League) | | adobe-commerce-dev-docs-mcp | npm | Developer docs (developer.adobe.com/commerce) | | adobe-commerce-kb-mcp | npm | Support Knowledge Base, patches, troubleshooting | | adobe-app-builder-docs-mcp | npm | App Builder, I/O Runtime, Commerce extensibility | | adobe-api-mesh-docs-mcp | npm | API Mesh, GraphQL gateway | | adobe-io-events-docs-mcp | npm | I/O Events, webhooks | | aem-live-docs-mcp | npm | AEM / Edge Delivery Services (aem.live) | | odoo-docs-mcp | npm | Odoo documentation |

Combined Cursor config:

{
  "mcpServers": {
    "magento-sql": {
      "command": "npx",
      "args": ["-y", "magento-sql-mcp-server"],
      "env": {
        "MAGENTO_ROOT": "/absolute/path/to/magento",
        "MAGENTO_SQL_PROFILE": "local"
      }
    },
    "adobe-commerce-docs": {
      "command": "npx",
      "args": ["-y", "adobe-commerce-docs-mcp"]
    },
    "adobe-commerce-dev-docs": {
      "command": "npx",
      "args": ["-y", "adobe-commerce-dev-docs-mcp"]
    },
    "adobe-commerce-kb": {
      "command": "npx",
      "args": ["-y", "adobe-commerce-kb-mcp"]
    }
  }
}

All MCP packages: github.com/jigarkkarangiya?tab=repositories&q=mcp