db-query
Execute SQL queries directly on Magento 2 database to retrieve, analyze, or modify data with output in table, JSON, or CSV format.
Instructions
Execute SQL queries directly on Magento 2 database
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| format | No | Output format | table |
| query | Yes | SQL query to execute |
Implementation Reference
- src/index.ts:950-974 (handler)The handler function that implements the core logic of the 'db-query' tool by executing a magerun2 db:query command with the provided SQL query and format.async ({ query, format = "table" }) => { const command = `magerun2 db:query --format=${format} "${query}"`; const result = await executeMagerun2Command(command, format === "json"); if (!result.success) { return { content: [{ type: "text", text: result.error }], isError: true }; } const responseText = format === "json" ? `Query results (${format} format):\n\n${JSON.stringify(result.data, null, 2)}` : `Query results (${format} format):\n\n${result.data}`; return { content: [{ type: "text", text: responseText }] }; }
- src/index.ts:943-948 (schema)The input schema defining the parameters for the 'db-query' tool: 'query' (SQL string) and 'format' (table/json/csv with default 'table').query: z.string() .describe("SQL query to execute"), format: z.enum(["table", "json", "csv"]) .default("table") .describe("Output format") }
- src/index.ts:937-975 (registration)The registration of the 'db-query' tool using server.registerTool, including title, description, input schema, and handler reference.server.registerTool( "db-query", { title: "Database Query", description: "Execute SQL queries directly on Magento 2 database", inputSchema: { query: z.string() .describe("SQL query to execute"), format: z.enum(["table", "json", "csv"]) .default("table") .describe("Output format") } }, async ({ query, format = "table" }) => { const command = `magerun2 db:query --format=${format} "${query}"`; const result = await executeMagerun2Command(command, format === "json"); if (!result.success) { return { content: [{ type: "text", text: result.error }], isError: true }; } const responseText = format === "json" ? `Query results (${format} format):\n\n${JSON.stringify(result.data, null, 2)}` : `Query results (${format} format):\n\n${result.data}`; return { content: [{ type: "text", text: responseText }] }; } );
- src/index.ts:25-88 (helper)The helper function 'executeMagerun2Command' used by the db-query handler (and other tools) to run magerun2 CLI commands safely with JSON parsing, timeouts, and Magento-specific error handling./** * Helper function to execute magerun2 commands with consistent error handling */ async function executeMagerun2Command(command: string, parseJson: boolean = false): Promise<{ success: true; data: any; rawOutput: string; } | { success: false; error: string; isError: true; }> { try { const { stdout, stderr } = await execAsync(command, { cwd: process.cwd(), timeout: 30000 // 30 second timeout }); if (stderr && stderr.trim()) { console.error("magerun2 stderr:", stderr); } if (parseJson) { try { return { success: true, data: JSON.parse(stdout), rawOutput: stdout }; } catch (parseError) { return { success: false, error: `Error parsing magerun2 JSON output: ${parseError}\n\nRaw output:\n${stdout}`, isError: true }; } } return { success: true, data: stdout.trim(), rawOutput: stdout }; } catch (error) { const errorMessage = error instanceof Error ? error.message : String(error); // Check if magerun2 is not found if (errorMessage.includes("command not found") || errorMessage.includes("not recognized")) { return { success: false, error: "Error: magerun2 command not found. Please ensure n98-magerun2 is installed and available in your PATH.\n\nInstallation instructions: https://github.com/netz98/n98-magerun2", isError: true }; } // Check if not in Magento directory if (errorMessage.includes("not a Magento installation") || errorMessage.includes("app/etc/env.php")) { return { success: false, error: "Error: Current directory does not appear to be a Magento 2 installation. Please run this command from your Magento 2 root directory.", isError: true }; } return { success: false, error: `Error executing magerun2 command: ${errorMessage}`, isError: true }; } }