MariaDB MCP Server

Instructions

Implementation Reference

• src/connection.ts:49-114 (handler)

  Core handler function that executes the SQL query logic for the 'execute_query' tool, managing MariaDB connections, performing query validation, applying timeouts and row limits, and handling errors.

  export async function executeQuery(
    sql: string,
    params: any[] = [],
    database?: string
  ): Promise<{ rows: any; fields: mariadb.FieldInfo[] }> {
    console.error(`[Query] Executing: ${sql}`);
    // Create connection pool if not already created
    if (!pool) {
      console.error("[Setup] Connection pool not found, creating a new one");
      pool = createConnectionPool();
    }
    try {
      // Get connection from pool
      if (connection) {
        console.error("[Query] Reusing existing connection");
      } else {
        console.error("[Query] Creating new connection");
        connection = await pool.getConnection();
      }
  
      // Use specific database if provided
      if (database) {
        console.error(`[Query] Using database: ${database}`);
        await connection.query(`USE \`${database}\``);
      }
      if (!isAlloowedQuery(sql)) {
        throw new Error("Query not allowed");
      }
      // Execute query with timeout
      const [rows, fields] = await connection.query({
        metaAsArray: true,
        namedPlaceholders: true,
        sql,
        ...params,
        timeout: DEFAULT_TIMEOUT,
      });
  
      // Apply row limit if result is an array
      const limitedRows =
        Array.isArray(rows) && rows.length > DEFAULT_ROW_LIMIT
          ? rows.slice(0, DEFAULT_ROW_LIMIT)
          : rows;
  
      // Log result summary
      console.error(
        `[Query] Success: ${
          Array.isArray(rows) ? rows.length : 1
        } rows returned with ${JSON.stringify(params)}`
      );
  
      return { rows: limitedRows, fields };
    } catch (error) {
      if (connection) {
        connection.release();
        console.error("[Query] Connection released with error");
      }
      console.error("[Error] Query execution failed:", error);
      throw error;
    } finally {
      // Release connection back to pool
      if (connection) {
        connection.release();
        console.error("[Query] Connection released");
      }
    }
  }

• src/index.ts:104-112 (registration)

  Registration of the 'execute_query' tool in the MCP server's ListTools response, specifying name, description, and input schema.

  {
    name: "execute_query",
    description: "Run an arbitrary SQL query",
    inputSchema: {
      type: "object",
      properties: { query: { type: "string" }, database: { type: "string" } },
      required: ["query"],
    },
  },

• src/index.ts:107-111 (schema)

  Input schema for the 'execute_query' tool, requiring a 'query' string parameter and optionally accepting 'database'.

  inputSchema: {
    type: "object",
    properties: { query: { type: "string" }, database: { type: "string" } },
    required: ["query"],
  },

• src/index.ts:147-156 (handler)

  MCP CallTool request handler case that implements the 'execute_query' tool by extracting parameters, calling the query executor, and returning results as formatted JSON text.

  case "execute_query": {
    const qry = args.query as string;
    if (!qry)
      throw new McpError(ErrorCode.InvalidParams, "`query` is required");
    const db = args.database as string | undefined;
    const { rows } = await executeQuery(qry, [], db);
    return {
      content: [{ type: "text", text: JSON.stringify(rows, null, 2) }],
    };
  }

• src/validators.ts:45-85 (helper)

  Helper function for validating SQL queries to ensure only allowed commands (based on env config) are executed, preventing dangerous operations in the execute_query tool.

  export function isAlloowedQuery(query: string): boolean {
    // Normalize query by removing comments and extra whitespace
    const normalizedQuery = query
      .replace(/--.*$/gm, "") // Remove single-line comments
      .replace(/\/\*[\s\S]*?\*\//g, "") // Remove multi-line comments
      .replace(/\s+/g, " ") // Normalize whitespace
      .trim()
      .toUpperCase();
    const ALLOW_INSERT = process.env.MARIADB_ALLOW_INSERT === "true";
    const ALLOW_UPDATE = process.env.MARIADB_ALLOW_UPDATE === "true";
    const ALLOW_DELETE = process.env.MARIADB_ALLOW_DELETE === "true";
  
    // Check if query starts with an allowed command
    const startsWithAllowed = ALLOWED_COMMANDS.some(
      (cmd) => normalizedQuery.startsWith(cmd + " ") || normalizedQuery === cmd
    );
    const startsWithAllowedNoSpace =
      normalizedQuery.startsWith("INSERT") && !ALLOW_INSERT;
    // Check if query contains any disallowed commands
    const containsDisallowed = DISALLOWED_COMMANDS.some((cmd) => {
      if (cmd === "INSERT" && !ALLOW_INSERT) {
        return false; // Skip INSERT if not allowed
      }
      if (cmd === "UPDATE" && !ALLOW_UPDATE) {
        return false; // Skip UPDATE if not allowed
      }
      if (cmd === "DELETE" && !ALLOW_DELETE) {
        return false; // Skip DELETE if not allowed
      }
      const regex = new RegExp(`(^|\\s)${cmd}(\\s|$)`);
      return regex.test(normalizedQuery);
    });
  
    // Check for multiple statements (;)
    const hasMultipleStatements =
      normalizedQuery.includes(";") && !normalizedQuery.endsWith(";");
  
    // Query is read-only if it starts with an allowed command,
    // doesn't contain any disallowed commands, and doesn't have multiple statements
    return startsWithAllowed && !containsDisallowed && !hasMultipleStatements;
  }