Connecting Pipedream with PostgreSQL Data via CData Connect AI MCP Server

is a cloud-based workflow automation platform that allows developers to connect APIs, automate tasks, and build event-driven workflows using serverless functions. When combined with CData Connect AI Remote MCP, Pipedream can interact with PostgreSQL data in real time using natural language, without the need for data replication to a natively supported database.

CData Connect AI offers a dedicated cloud-to-cloud interface for connecting to PostgreSQL data. The CData Connect AI Remote MCP Server enables secure communication between Pipedream and PostgreSQL, making it possible to ask questions and retrieve data from PostgreSQL using Pipedream workflows, all powered by an LLM that intelligently discovers data sources and generates SQL queries on the fly.

This article covers how to build a simple natural language data query workflow in Pipedream to conversationally explore PostgreSQL data. The connectivity principles apply to any Pipedream workflow. With Connect AI, workflows and agents can be built with access to live PostgreSQL data, plus hundreds of other sources.

Prerequisites

1. A CData Connect AI account with at least one active connection (e.g., PostgreSQL)
2. A Pipedream account
3. An OpenAI account with API Key
4. CData Connect AI credentials:
   • Email (used as username for Basic Auth)
   • Personal Access Token (PAT) generated from the CData Connect AI Settings page

Step 1: Configure PostgreSQL connectivity for Pipedream

Connectivity to PostgreSQL from Pipedream is made possible through CData Connect AI Remote MCP. To interact with PostgreSQL from Pipedream, we start by creating and configuring a PostgreSQL connection in CData Connect AI.

1. Log into Connect AI, click Sources, and then click Add Connection
👁 Adding a Connection

2. Select "PostgreSQL" from the Add Connection panel
👁 Selecting a data source

3. Enter the necessary authentication properties to connect to PostgreSQL.

   To connect to PostgreSQL, set the Server, Port (the default port is 5432), and Database connection properties and set the User and Password you wish to use to authenticate to the server. If the Database property is not specified, the data provider connects to the user's default database.

   SSH Connectivity for PostgreSQL

   You can use SSH (Secure Shell) to authenticate with PostgreSQL, whether the instance is hosted on-premises or in supported cloud environments. SSH authentication ensures that access is encrypted (as compared to direct network connections).

   SSH Connections to PostgreSQL in Password Auth Mode

   To connect to PostgreSQL via SSH in Password Auth mode, set the following connection properties:

   • User: PostgreSQL User name
   • Password: PostgreSQL Password
   • Database: PostgreSQL database name
   • Server: PostgreSQL Server name
   • Port: PostgreSQL port number like 3306
   • UserSSH: "true"
   • SSHAuthMode: "Password"
   • SSHPort: SSH Port number
   • SSHServer: SSH Server name
   • SSHUser: SSH User name
   • SSHPassword: SSH Password

   SSH Connections to PostgreSQL in Public Key Auth Mode

   To connect to PostgreSQL via SSH in Password Auth mode, set the following connection properties:

   • User: PostgreSQL User name
   • Password: PostgreSQL Password
   • Database: PostgreSQL database name
   • Server: PostgreSQL Server name
   • Port: PostgreSQL port number like 3306
   • UserSSH: "true"
   • SSHAuthMode: "Public_Key"
   • SSHPort: SSH Port number
   • SSHServer: SSH Server name
   • SSHUser: SSH User name
   • SSHClientCret: the path for the public key certificate file
   👁 Configuring a connection (Salesforce is shown)
   
4. Click Save & Test
5. Navigate to the Permissions tab in the Add PostgreSQL Connection page and update the User-based permissions. 👁 Updating permissions
   

Add a Personal Access Token

A Personal Access Token (PAT) is used to authenticate the connection to Connect AI from Pipedream. It is best practice to create a separate PAT for each service to maintain granularity of access.

1. Click on the Gear icon () at the top right of the Connect AI app to open the settings page.
2. On the Settings page, go to the Access Tokens section and click Create PAT.
3. Give the PAT a name and click Create.👁 Creating a new PAT
   
4. The personal access token is only visible at creation, so be sure to copy it and store it securely for future use.

With the connection configured and a PAT generated, we are ready to connect to PostgreSQL from Pipedream.

Step 2: Set up environment variables in Pipedream

Store credentials securely as environment variables in Pipedream.

1. In Pipedream, go to Settings, then Environment Variables 👁 Set up environment variables
   
2. Click on New Variable and add the following variables:

Variable name	Value
CDATA_EMAIL	CData Connect AI login email
CDATA_PAT	CData Personal Access Token
OPENAI_API_KEY	OpenAI API Key

👁 Create new variables

Step 3: Create the Pipedream workflow

3.1 Configure the HTTP trigger

1. Create a new workflow in Pipedream
👁 Create new workflow

2. Select HTTP / Webhook as the trigger
3. Set HTTP Response to "Return a custom response from your workflow"
👁 Set HTTP response

3.2 Add the LLM step

Add a Node.js code step named LLM. This step extracts the natural language query from the incoming request.

Replace the default code in the step with the following:

import OpenAI from "openai";

export default defineComponent({
 async run({ steps }) {
 if (steps.trigger.event.method === "OPTIONS") {
 return { userQuery: null, isOptions: true };
 }

 const body = steps.trigger.event.body;
 const parsed = typeof body === "string" ? JSON.parse(body) : body;
 const userQuery = parsed?.query;

 console.log("USER QUERY:", userQuery);
 if (!userQuery) throw new Error("No query found in request body");

 return { userQuery };
 }
});

3.3 Add the MCP step

Add a Node.js code step named MCP. This step implements the full agentic MCP flow, it automatically discovers all available connections, selects the most relevant one based on the question, discovers the schema and tables dynamically, generates a SQL query using the LLM, and executes it against PostgreSQL data.

The step uses the following CData Connect AI MCP tools in sequence:

Replace the default code in the step with the following:

import fetch from "node-fetch";
import OpenAI from "openai";

export default defineComponent({
 async run({ steps }) {
 const email = process.env.CDATA_EMAIL;
 const pat = process.env.CDATA_PAT;
 const credentials = email + ":" + pat;
 const auth = Buffer.from(credentials).toString("base64");
 const llmOutput = steps.LLM;
 const userQuery = llmOutput.return_value.userQuery; // In Pipedream replace with: steps.LLM.$return_value.userQuery
 const MCP_URL = "https://mcp.cloud.cdata.com/mcp";
 const NL = String.fromCharCode(10);
 const CRNL = String.fromCharCode(13) + String.fromCharCode(10);

 const headers = {
 "Content-Type": "application/json",
 "Accept": "application/json, text/event-stream",
 "Authorization": "Basic " + auth
 };

 function parseSSE(raw) {
 try {
 const lines = raw.split(NL);
 for (let i = 0; i < lines.length; i++) {
 const line = lines.at(i);
 const trimmed = line.trim();
 if (trimmed.indexOf("data:") === 0) {
 const jsonStr = trimmed.slice(5).trim();
 if (jsonStr) {
 const json = JSON.parse(jsonStr);
 const result = json && json.result;
 const content = result && result.content;
 if (Array.isArray(content)) {
 return {
 parsed: content.map(function(c) { return c.text || ""; }).join(NL),
 isError: (result && result.isError) || false,
 full: json
 };
 }
 }
 }
 }
 } catch (e) {
 console.log("SSE parse error:", e.message);
 }
 return { parsed: raw, isError: false, full: null };
 }

 function parseCSV(text) {
 let clean = text || "";
 if (clean.charAt(0) === '"' && clean.charAt(clean.length - 1) === '"') {
 clean = clean.slice(1, -1);
 }
 const ESC_CRNL = String.fromCharCode(92) + "r" + String.fromCharCode(92) + "n";
 const ESC_QUOTE = String.fromCharCode(92) + '"';
 const ESC_SLASH = String.fromCharCode(92) + String.fromCharCode(92);
 const SINGLE_SLASH = String.fromCharCode(92);
 clean = clean.split(ESC_CRNL).join(CRNL).split(ESC_QUOTE).join('"').split(ESC_SLASH).join(SINGLE_SLASH);
 const lines = clean.split(CRNL).filter(function(l) { return l.trim(); });
 return lines.slice(1).map(function(l) { return l.split(",").at(0).trim(); }).filter(Boolean);
 }

 async function initSession() {
 const res = await fetch(MCP_URL, {
 method: "POST",
 headers: headers,
 body: JSON.stringify({
 jsonrpc: "2.0",
 id: 1,
 method: "initialize",
 params: {
 protocolVersion: "2024-11-05",
 capabilities: {},
 clientInfo: { name: "pipedream", version: "1.0" }
 }
 })
 });
 return res.headers.get("mcp-session-id");
 }

 async function callMCP(id, method, args, sessionId) {
 const reqHeaders = Object.assign({}, headers);
 if (sessionId) {
 Object.assign(reqHeaders, { "mcp-session-id": sessionId });
 }
 const res = await fetch(MCP_URL, {
 method: "POST",
 headers: reqHeaders,
 body: JSON.stringify({
 jsonrpc: "2.0",
 id: id,
 method: "tools/call",
 params: { name: method, arguments: args }
 })
 });
 const raw = await res.text();
 const result = parseSSE(raw);
 result.raw = raw;
 return result;
 }

 const client = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });
 const completions = client.chat.completions;

 const session1 = await initSession();
 const catalogsResult = await callMCP(2, "getCatalogs", {}, session1);
 const catalogs = parseCSV(catalogsResult.parsed);

 const systemMsg1 = "You are a data routing expert. Pick the MOST relevant connection name from the list. Return ONLY the connection name. Available connections: " + catalogs.join(", ");
 const connectionResponse = await completions.create({
 model: "gpt-4o-mini",
 messages: new Array(
 { role: "system", content: systemMsg1 },
 { role: "user", content: userQuery }
 )
 });
 const connectionName = connectionResponse.choices.at(0).message.content.trim();

 const session2 = await initSession();
 const schemasResult = await callMCP(2, "getSchemas", {
 connectionName: connectionName,
 catalogName: connectionName
 }, session2);
 const schemas = parseCSV(schemasResult.parsed);
 const schemaName = schemas.at(0) || "REST";

 const session3 = await initSession();
 const tablesResult = await callMCP(2, "getTables", {
 connectionName: connectionName,
 catalogName: connectionName,
 schemaName: schemaName
 }, session3);
 const tableNames = parseCSV(tablesResult.parsed);

 const queryLower = userQuery.toLowerCase();
 const isListTablesQuery =
 queryLower.indexOf("list") !== -1 ||
 queryLower.indexOf("what tables") !== -1 ||
 queryLower.indexOf("show tables") !== -1;

 if (isListTablesQuery) {
 return {
 success: true,
 connection: connectionName,
 message: "Available tables in " + connectionName + "." + schemaName,
 tables: tableNames
 };
 }

 const tableList = tableNames.map(function(t) {
 return connectionName + "." + schemaName + "." + t;
 }).join(", ");

 const systemMsg2 = "You are a SQL expert. Generate SQL for CData. Use format: connectionName.schemaName.TableName. Available tables: " + tableList + ". Return ONLY SQL. No markdown. No brackets.";
 const sqlResponse = await completions.create({
 model: "gpt-4o-mini",
 messages: new Array(
 { role: "system", content: systemMsg2 },
 { role: "user", content: userQuery }
 )
 });
 const sql = sqlResponse.choices.at(0).message.content.trim();

 if (!sql) { return { error: "LLM returned empty SQL" }; }

 const session4 = await initSession();
 const queryResult = await callMCP(2, "queryData", {
 query: sql,
 connectionName: connectionName
 }, session4);

 if (queryResult.full) {
 const content = queryResult.full.result && queryResult.full.result.content;
 if (Array.isArray(content)) {
 try {
 const parsed = JSON.parse(content.at(0).text);
 const results = parsed.results && parsed.results.at(0);
 return {
 sql: sql,
 connection: connectionName,
 data: (results && results.rows) || new Array(),
 schema: (results && results.schema) || new Array(),
 success: true
 };
 } catch (e) {
 return { sql: sql, connection: connectionName, raw: content.at(0).text, success: true };
 }
 }
 }
 return { sql: sql, connection: connectionName, raw: queryResult.raw };
 }
});

Note: When pasting into Pipedream, replace llmOutput.return_value.userQuery with steps.LLM.$return_value.userQuery as indicated in the comment on that line.

3.4 Configure the response step

1. Add a Return HTTP Response step and name it Response
2. Set Response Status Code to 200
3. Set Response Body to {{steps.mcp.$return_value}}
👁 Configure response step

4. Add the following Response Headers by clicking Response Headers then :

Key	Value
Access-Control-Allow-Origin	*
Access-Control-Allow-Methods	POST, OPTIONS
Access-Control-Allow-Headers	Content-Type

👁 Add all the required Response Headers

Step 4: Test the flow and interact with PostgreSQL data

Set a test event on the trigger:

1. Click the trigger step in the workflow
2. Click Generate Test Event
👁 Generate test event

3. Edit the event body and set it to:

{
 "query": "list all tables"
}

👁 Set raw request body

Run the full workflow

1. Click Test workflow at the bottom of the trigger step
👁 Test workflow

2. Pipedream will run all steps in sequence using the test event
3. Watch each step turn green as it completes successfully
👁 Complete workflow

View results in each step

After the test run completes, click each step tab and check the Exports tab to inspect outputs:

The Logs tab inside any step shows detailed outputs including the generated SQL, selected connection, and raw MCP responses.

Note: The Response step's Exports tab only shows a summary like { "success": true } with "status 200"; this confirms the workflow ran successfully but does not show the full data. 👁 Response output snippet

To see the complete output including data rows, SQL, and schema, click the MCP step tab and check the Exports tab. Expand $return_value to see the full response: 👁 MCP output snippet

The workflow automatically:

1. Discovers all CData connections
2. Selects the most relevant connection for the question
3. Discovers the schema and tables dynamically
4. Generates and executes the appropriate SQL query
5. Returns the results

How it works

The integration uses the following CData Connect AI MCP tools in sequence:

The OpenAI LLM acts as the intelligent layer between the natural language question and the CData MCP tools, selecting the right connection, discovering the data structure, and generating accurate SQL queries automatically.

Build real-time, data-aware workflows with Pipedream and CData

Pipedream and CData Connect AI together enable intelligent, AI-driven workflows where natural language queries are automatically translated into live data operations across enterprise systems, without ETL pipelines, data sync jobs, or custom integration logic. This streamlined approach delivers stronger governance, lower operational overhead, and faster, more grounded responses from AI-powered workflows.

Start a free trial today to see how CData Connect AI can empower Pipedream with live, secure access to hundreds of external systems.