MODULE 02

DreamFactory MCP
Server Setup

Configure the Model Context Protocol server to give AI agents structured, secure access to your databases. Transform raw database connections into AI-ready tooling in minutes.

~8 min read
Intermediate
Configuration
Full Step-by-Step Documentation ~20 min

🔌 What is MCP?

The Model Context Protocol (MCP) is a standardized way for AI models to interact with external tools and data sources. It defines how AI agents discover, understand, and call APIs, making your databases accessible to AI in a controlled, secure manner.

DreamFactory's AI Data Gateway includes a built-in MCP server that exposes your database APIs as MCP-compatible tools, running within your own DreamFactory instance. This means AI agents like ChatGPT, Claude, Cursor, or any AI agent can directly query your databases using natural language.

✨ Key Capabilities

🔍

Auto-Discovery

AI agents discover tools like {db}_get_data_model, {db}_get_tables, {db}_get_table_data, list_apis, and all_find_table without manual configuration.

📋

Schema Awareness

Full schema information passed to AI so it understands data types, relationships, and constraints.

🔐

Permission Enforcement

Role-based access controls applied at the MCP layer. AI only sees what users are allowed to access.

📊

Query Logging

Every AI-generated query is logged with full context for debugging and compliance auditing.

📁

File Storage

MCP exposes file operations ({fs}_list_files, {fs}_get_file, {fs}_create_file, {fs}_delete_file) with auto-detection of text/image/audio content. Supports local_file, aws_s3, azure_blob, ftp_file, sftp_file, and webdav_file.

🏗ī¸ Architecture Overview

🤖

AI Agent (ChatGPT, Claude, Cursor, or any AI agent)

Sends natural language requests, receives structured tool definitions via MCP

↓
🔌

DreamFactory MCP Server (Hybrid PHP/Node.js)

PHP Laravel handles OAuth 2.0 auth and routing; persistent Node.js daemon (Express.js + MCP SDK v1.23.0) handles MCP protocol via StreamableHTTP transport on localhost:8006

↓
⚡

DreamFactory AI Data Gateway

Validates requests, applies row-level security, executes stored procedures

↓
🗄ī¸

Database (MySQL, PostgreSQL, SQL Server, Oracle...)

Executes optimized queries, returns results through the secure pipeline

⚙ī¸ Setup Steps

1

Open the AI Tab

Log in as admin and click the AI tab in the DreamFactory admin console.

2

Create an MCP Server Service

Click the purple + button and select MCP Server Service from the menu.

3

Configure the Service

Fill in the API namespace, label, and description for your MCP service. Select which database services and tables to expose.

4

Save and Retrieve Credentials

Save the service. OAuth 2.0 credentials (Client ID and Client Secret) are auto-generated. Authentication uses OAuth 2.0 Authorization Code with PKCE (RFC 7636), with Dynamic Client Registration (RFC 7591) and OAuth discovery (RFC 8414). Token lifetimes: access tokens = 1 hour, refresh tokens = 7 days, authorization codes = 10 minutes.

5

Test with MCP Inspector

Use the built-in MCP Inspector tool to verify tools are discovered correctly and permissions work as expected. The endpoint will be available at POST /mcp/{service-name}.

đŸ’ģ Configuration Example

ENV 
# DreamFactory .env - MCP Node.js Daemon Configuration
MCP_DAEMON_ENABLED=true
MCP_DAEMON_URL=http://127.0.0.1:8006
MCP_DAEMON_HOST=127.0.0.1
MCP_DAEMON_PORT=8006
ℹī¸

Hybrid Architecture

The PHP Laravel side handles OAuth 2.0 authentication and routing. The Node.js daemon (configured above) runs the MCP protocol via StreamableHTTPServerTransport (SSE + JSON-RPC over HTTP). They communicate internally on localhost:8006.

🔗 Client Integration

Python 
from mcp import ClientSession
from mcp.client.streamable_http import streamablehttp_client

# Connect to DreamFactory MCP Server via StreamableHTTP (SSE + JSON-RPC)
# Auth is handled via OAuth 2.0 + PKCE - no API keys needed
async with streamablehttp_client("https://your-dreamfactory.com/mcp/my-ai-service") as (read, write):
    async with ClientSession(read, write) as session:
        await session.initialize()

        # Discover available tools
        tools = (await session.list_tools()).tools
        print(f"Available tools: {[t.name for t in tools]}")

        # Example: session.call_tool("mysql_get_table_data", {"table_name": "customers"})

💡 Best Practices

✓

Start with Read-Only

Begin with read-only access while testing. Enable write operations only after validating AI behavior with your data.

⚠ī¸

Use Stored Procedures for Writes

Instead of exposing raw INSERT/UPDATE, create stored procedures with validation logic. AI should call procedures, not execute raw SQL.

ℹī¸

Monitor Query Patterns

Review MCP logs regularly to understand what queries AI is generating. This helps identify optimization opportunities and security concerns.

⚠ī¸

Pagination Limits for AI Token Management

The MCP server limits results to 1,000 records per request (vs. the platform default of 100,000 via DB_MAX_RECORDS_RETURNED). This is critical for keeping AI token usage manageable. Use {db}_aggregate_data (supports SUM, COUNT, AVG, MIN, MAX with GROUP BY) for analytics instead of fetching large datasets, as it handles pagination internally.

🚀 Next Steps

📖

See Also

How to Connect LLM Chat and AI Agents to Enterprise Data Using Built-In MCP. Step-by-step guide to connecting ChatGPT, Claude, or any MCP client. Most teams finish in under 10 minutes.

Ready to implement? Get the complete step-by-step guide with code examples, screenshots, and troubleshooting tips.

Full Step-by-Step Documentation ~20 min