pg_execute_sql
Execute any valid SQL statement on PostgreSQL, including complex queries, with optional parameters, transactional support, and query timeouts for efficient database 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) | |
| expectRows | No | Whether to expect rows back (false for statements like CREATE, DROP, etc.) | |
| parameters | No | Parameter values for prepared statement placeholders ($1, $2, etc.) | |
| sql | Yes | SQL statement to execute (can be any valid PostgreSQL SQL) | |
| timeout | No | Query timeout in milliseconds | |
| transactional | No | Whether to wrap in a transaction |
Implementation Reference
- src/tools/data.ts:429-485 (handler)Main tool handler: defines the 'pg_execute_sql' tool with execute function that validates input, calls helper, formats response.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 input schema for 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, query execution (with optional transaction), and result processing.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:225-257 (registration)Tool registration: pg_execute_sql (as executeSqlTool) is included in the central allTools array passed to the PostgreSQLServer constructor.const allTools: PostgresTool[] = [ // Core Analysis & Debugging analyzeDatabaseTool, debugDatabaseTool, // Schema & Structure Management (Meta-Tools) manageSchemaTools, manageFunctionsTool, manageTriggersTools, manageIndexesTool, manageConstraintsTool, manageRLSTool, // User & Security Management manageUsersTool, // Query & Performance Management manageQueryTool, // Data Operations (Enhancement Tools) executeQueryTool, executeMutationTool, executeSqlTool, // Documentation & Metadata manageCommentsTool, // Data Migration & Monitoring exportTableDataTool, importTableDataTool, copyBetweenDatabasesTool, monitorDatabaseTool ];