pg_execute_sql
Execute SQL statements on PostgreSQL databases with parameter support, transaction handling, and timeout control for data operations and schema management.
Instructions
Execute arbitrary SQL statements - sql="ANY_VALID_SQL" with optional parameters and transaction support. Examples: sql="CREATE INDEX ...", sql="WITH complex_cte AS (...) SELECT ...", transactional=true
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| connectionString | No | PostgreSQL connection string (optional) | |
| sql | Yes | SQL statement to execute (can be any valid PostgreSQL SQL) | |
| parameters | No | Parameter values for prepared statement placeholders ($1, $2, etc.) | |
| expectRows | No | Whether to expect rows back (false for statements like CREATE, DROP, etc.) | |
| timeout | No | Query timeout in milliseconds | |
| transactional | No | Whether to wrap in a transaction |
Implementation Reference
- src/tools/data.ts:429-485 (handler)Full tool definition including the handler execute function for pg_execute_sql. This is the core implementation that handles tool calls, validates input, invokes the SQL execution helper, and formats the output.export const executeSqlTool: PostgresTool = { name: 'pg_execute_sql', description: 'Execute arbitrary SQL statements - sql="ANY_VALID_SQL" with optional parameters and transaction support. Examples: sql="CREATE INDEX ...", sql="WITH complex_cte AS (...) SELECT ...", transactional=true', inputSchema: ExecuteSqlInputSchema, execute: async (args: unknown, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => { const { connectionString: connStringArg, sql, parameters, expectRows, timeout, transactional } = args as { connectionString?: string; sql: string; parameters?: unknown[]; expectRows?: boolean; timeout?: number; transactional?: boolean; }; const resolvedConnString = getConnectionStringVal(connStringArg); try { // Input validation if (!sql?.trim()) { return { content: [{ type: 'text', text: 'Error: sql is required' }], isError: true }; } const result = await executeSql({ connectionString: resolvedConnString, sql, parameters: parameters ?? [], expectRows: expectRows ?? true, timeout, transactional: transactional ?? false }, getConnectionStringVal); let responseText = result.message; if (result.rows && result.rows.length > 0) { responseText += `\n\nResults:\n${JSON.stringify(result.rows, null, 2)}`; } return { content: [{ type: 'text', text: responseText }] }; } catch (error) { return { content: [{ type: 'text', text: `Error executing SQL: ${error instanceof Error ? error.message : String(error)}` }], isError: true }; } } };
- src/tools/data.ts:364-371 (schema)Zod schema for input validation of pg_execute_sql tool parameters.const ExecuteSqlInputSchema = z.object({ connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'), sql: z.string().describe('SQL statement to execute (can be any valid PostgreSQL SQL)'), parameters: z.array(z.unknown()).optional().default([]).describe('Parameter values for prepared statement placeholders ($1, $2, etc.)'), expectRows: z.boolean().optional().default(true).describe('Whether to expect rows back (false for statements like CREATE, DROP, etc.)'), timeout: z.number().optional().describe('Query timeout in milliseconds'), transactional: z.boolean().optional().default(false).describe('Whether to wrap in a transaction') });
- src/tools/data.ts:375-427 (helper)Helper function that performs the actual database connection, SQL execution (supporting transactions), result processing, and error handling for pg_execute_sql.async function executeSql( input: ExecuteSqlInput, getConnectionString: GetConnectionStringFn ): Promise<{ sql: string; rowsAffected?: number; rows?: unknown[]; message: string }> { const resolvedConnectionString = getConnectionString(input.connectionString); const db = DatabaseConnection.getInstance(); const { sql, parameters, expectRows, timeout, transactional } = input; try { await db.connect(resolvedConnectionString); const queryOptions = timeout ? { timeout } : {}; if (transactional) { return await db.transaction(async (client) => { const result = await client.query(sql, parameters || []); if (expectRows) { return { sql, rowsAffected: Array.isArray(result.rows) ? result.rows.length : 0, rows: result.rows, message: `SQL executed successfully in transaction. Retrieved ${Array.isArray(result.rows) ? result.rows.length : 0} rows.` }; } return { sql, rowsAffected: result.rowCount || 0, message: `SQL executed successfully in transaction. Rows affected: ${result.rowCount || 0}` }; }); } const result = await db.query(sql, parameters || [], queryOptions); if (expectRows) { return { sql, rowsAffected: Array.isArray(result) ? result.length : 0, rows: result, message: `SQL executed successfully. Retrieved ${Array.isArray(result) ? result.length : 0} rows.` }; } return { sql, rowsAffected: Array.isArray(result) ? result.length : 1, message: 'SQL executed successfully. Operation completed.' }; } catch (error) { throw new McpError(ErrorCode.InternalError, `Failed to execute SQL: ${error instanceof Error ? error.message : String(error)}`); } finally { await db.disconnect(); } }
- src/index.ts:30-30 (registration)Import of the pg_execute_sql tool (as executeSqlTool) from data.ts.import { executeQueryTool, executeMutationTool, executeSqlTool } from './tools/data.js';
- src/index.ts:245-247 (registration)Registration of pg_execute_sql tool (executeSqlTool) in the allTools array passed to the MCP server constructor.executeQueryTool, executeMutationTool, executeSqlTool,