MCP Server RDS Data API — HTTP SQL, no VPC, no connection pools needed

Data API vs connection-based access for MCP servers

Enabling the Data API on Aurora Serverless v2

# Enable the Data API on an existing Aurora Serverless cluster
aws rds modify-db-cluster \
  --db-cluster-identifier mcp-aurora-cluster \
  --enable-http-endpoint \
  --apply-immediately

# Verify it is enabled
aws rds describe-db-clusters \
  --db-cluster-identifier mcp-aurora-cluster \
  --query 'DBClusters[0].HttpEndpointEnabled'
# Should return: true

# Store DB credentials in Secrets Manager (required by Data API)
# The secret must contain: username, password, host, port, dbname
aws secretsmanager create-secret \
  --name prod/mcp-server/aurora-data-api \
  --description "Aurora credentials for RDS Data API" \
  --secret-string '{
    "username": "app_user",
    "password": "...",
    "host": "mcp-aurora-cluster.cluster-xxxx.us-east-1.rds.amazonaws.com",
    "port": 5432,
    "dbname": "mcpdb"
  }'

The Data API endpoint is managed by AWS — you do not configure a hostname for it. The SDK resolves the endpoint from the cluster ARN and region automatically.

Executing queries with the AWS SDK

import {
  RDSDataClient,
  ExecuteStatementCommand,
  Field,
} from "@aws-sdk/client-rds-data";

const client = new RDSDataClient({ region: process.env.AWS_REGION });

const CLUSTER_ARN = process.env.AURORA_CLUSTER_ARN!;
const SECRET_ARN = process.env.AURORA_SECRET_ARN!;
const DATABASE = "mcpdb";

// Simple query — no connection setup, no pool management
async function queryRow(sql: string, params: any[] = []): Promise {
  const response = await client.send(
    new ExecuteStatementCommand({
      resourceArn: CLUSTER_ARN,
      secretArn: SECRET_ARN,
      database: DATABASE,
      sql,
      // Parameters must be typed explicitly for the Data API
      parameters: params.map((value, i) => ({
        name: `param${i + 1}`,
        value: typedField(value),
      })),
      // Return column metadata so we can build result objects
      includeResultMetadata: true,
      // Format results as JSON records (cleaner than the default field-value structure)
      formatRecordsAs: "JSON",
    })
  );

  // With formatRecordsAs: "JSON", the response is a JSON string
  return JSON.parse(response.formattedRecords ?? "[]");
}

// Helper to create typed Field values for RDS Data API parameters
function typedField(value: any): Field {
  if (value === null || value === undefined) {
    return { isNull: true };
  }
  if (typeof value === "string") {
    return { stringValue: value };
  }
  if (typeof value === "number") {
    if (Number.isInteger(value)) return { longValue: value };
    return { doubleValue: value };
  }
  if (typeof value === "boolean") {
    return { booleanValue: value };
  }
  // For UUIDs, dates, and other types — pass as string with typeHint
  return { stringValue: String(value) };
}

// Usage in an MCP tool handler:
export async function getSession(sessionId: string) {
  const rows = await queryRow(
    "SELECT * FROM mcp_sessions WHERE session_id = :param1",
    [sessionId]
  );
  return rows[0] ?? null;
}

Note the named parameter syntax: RDS Data API uses :param_name in the SQL and a matching name in the parameters array — not the positional $1, $2 syntax used by node-postgres. This is a common porting mistake when migrating from direct connection code to the Data API.

Transactions across MCP tool calls

Most MCP tools complete a single logical operation (query + response) without needing a multi-statement transaction. But some workflows — like creating a session and logging the first tool call atomically — require a transaction. The Data API handles this with a transactionId token.

import {
  RDSDataClient,
  BeginTransactionCommand,
  ExecuteStatementCommand,
  CommitTransactionCommand,
  RollbackTransactionCommand,
} from "@aws-sdk/client-rds-data";

async function createSessionWithAudit(
  clientId: string,
  toolName: string
): Promise {
  // Begin transaction — returns a transactionId valid for 3 minutes
  const txn = await rdsClient.send(
    new BeginTransactionCommand({
      resourceArn: CLUSTER_ARN,
      secretArn: SECRET_ARN,
      database: DATABASE,
    })
  );
  const transactionId = txn.transactionId!;

  try {
    // Insert session — pass transactionId to join the transaction
    const session = await rdsClient.send(
      new ExecuteStatementCommand({
        resourceArn: CLUSTER_ARN,
        secretArn: SECRET_ARN,
        database: DATABASE,
        transactionId,
        sql: "INSERT INTO mcp_sessions (client_id) VALUES (:param1) RETURNING session_id",
        parameters: [{ name: "param1", value: { stringValue: clientId } }],
        formatRecordsAs: "JSON",
      })
    );
    const sessionId = JSON.parse(session.formattedRecords!)[0].session_id;

    // Insert audit log — same transactionId
    await rdsClient.send(
      new ExecuteStatementCommand({
        resourceArn: CLUSTER_ARN,
        secretArn: SECRET_ARN,
        database: DATABASE,
        transactionId,
        sql: "INSERT INTO mcp_tool_calls (session_id, tool_name) VALUES (:param1, :param2)",
        parameters: [
          { name: "param1", value: { stringValue: sessionId } },
          { name: "param2", value: { stringValue: toolName } },
        ],
      })
    );

    // Commit
    await rdsClient.send(
      new CommitTransactionCommand({
        resourceArn: CLUSTER_ARN,
        secretArn: SECRET_ARN,
        transactionId,
      })
    );

    return sessionId;
  } catch (err) {
    // Rollback on error
    await rdsClient.send(
      new RollbackTransactionCommand({
        resourceArn: CLUSTER_ARN,
        secretArn: SECRET_ARN,
        transactionId,
      })
    ).catch(() => {}); // Ignore rollback errors (transactionId may have expired)
    throw err;
  }
}

The 3-minute transactionId expiry is a hard constraint. If you begin a transaction in one MCP tool call and try to continue it in a later MCP tool call (for example, in a long-running agentic workflow where the user's LLM makes multiple tool calls over 5+ minutes), the transactionId will have expired. Design MCP tools to complete their transactions within a single handler invocation — do not return a transactionId to the MCP client and expect it to be passed back in a later call.

IAM policy for Data API access

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "rds-data:ExecuteStatement",
        "rds-data:BatchExecuteStatement",
        "rds-data:BeginTransaction",
        "rds-data:CommitTransaction",
        "rds-data:RollbackTransaction"
      ],
      "Resource": "arn:aws:rds:us-east-1:123456789012:cluster:mcp-aurora-cluster"
    },
    {
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "arn:aws:secretsmanager:us-east-1:123456789012:secret:prod/mcp-server/aurora-data-api-*"
    }
  ]
}

Both rds-data:* permissions and secretsmanager:GetSecretValue are required. The Data API service uses the Lambda's role to fetch the secret from Secrets Manager on behalf of the query — the Lambda does not need to fetch the secret itself, but it does need the permission because the API call chain uses the caller's credentials.

Common failure modes