execute
Run INSERT, UPDATE, or DELETE queries on a PostgreSQL database using parameterized SQL statements to modify data securely and efficiently.
Instructions
Execute an INSERT, UPDATE, or DELETE query
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| params | No | Query parameters (optional) | |
| sql | Yes | SQL query (INSERT, UPDATE, DELETE) (use $1, $2, etc. for parameters) |
Implementation Reference
- src/index.ts:353-390 (handler)The handler function that implements the core logic for the 'execute' tool. It ensures a database connection, validates the query is not a SELECT, prepares the SQL with parameters, executes it using pg.Client.query, and returns the rowCount and command executed.private async handleExecute(args: any) { await this.ensureConnection(); if (!args.sql) { throw new McpError(ErrorCode.InvalidParams, 'SQL query is required'); } const sql = args.sql.trim().toUpperCase(); if (sql.startsWith('SELECT')) { throw new McpError( ErrorCode.InvalidParams, 'Use query tool for SELECT statements' ); } try { // Convert ? parameters to $1, $2, etc. if needed const preparedSql = args.sql.includes('?') ? convertToNamedParams(args.sql) : args.sql; const result = await this.client!.query(preparedSql, args.params || []); return { content: [ { type: 'text', text: JSON.stringify({ rowCount: result.rowCount, command: result.command, }, null, 2), }, ], }; } catch (error) { throw new McpError( ErrorCode.InternalError, `Query execution failed: ${getErrorMessage(error)}` ); } }
- src/index.ts:193-209 (schema)Input schema definition for the 'execute' tool, specifying required 'sql' string and optional 'params' array of primitive types.inputSchema: { type: 'object', properties: { sql: { type: 'string', description: 'SQL query (INSERT, UPDATE, DELETE) (use $1, $2, etc. for parameters)', }, params: { type: 'array', items: { type: ['string', 'number', 'boolean', 'null'], }, description: 'Query parameters (optional)', }, }, required: ['sql'], },
- src/index.ts:190-210 (registration)Tool registration in the ListTools response, including name, description, and input schema.{ name: 'execute', description: 'Execute an INSERT, UPDATE, or DELETE query', inputSchema: { type: 'object', properties: { sql: { type: 'string', description: 'SQL query (INSERT, UPDATE, DELETE) (use $1, $2, etc. for parameters)', }, params: { type: 'array', items: { type: ['string', 'number', 'boolean', 'null'], }, description: 'Query parameters (optional)', }, }, required: ['sql'], }, },
- src/index.ts:44-47 (helper)Utility function to convert SQL ? placeholders to PostgreSQL positional parameters $1, $2, etc., used in the execute handler.function convertToNamedParams(query: string): string { let paramIndex = 0; return query.replace(/\?/g, () => `$${++paramIndex}`); }
- src/index.ts:261-262 (helper)Switch case in the CallToolRequest handler that dispatches 'execute' tool calls to the handleExecute method.case 'execute': return await this.handleExecute(request.params.arguments);