execute_sql
Execute SQL queries directly on your self-hosted Supabase database for data retrieval, updates, and management operations.
Instructions
Executes an arbitrary SQL query against the database, using direct database connection when available or RPC function as fallback.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | The SQL query to execute. | |
| read_only | No | Hint for the RPC function whether the query is read-only (best effort). |
Implementation Reference
- src/tools/execute_sql.ts:36-43 (handler)The main execute handler for the 'execute_sql' tool. It retrieves the database client from context, logs the query preview, executes the SQL using a fallback mechanism, and processes/validates the response.execute: async (input: ExecuteSqlInput, context: ToolContext) => { const client = context.selfhostedClient; console.error(`Executing SQL (readOnly: ${input.read_only}): ${input.sql.substring(0, 100)}...`); const result = await executeSqlWithFallback(client, input.sql, input.read_only); return handleSqlResponse(result, ExecuteSqlOutputSchema); },
- src/tools/execute_sql.ts:8-17 (schema)Zod schemas defining the input (sql query and optional read_only flag) and output (array of rows) for the execute_sql tool.const ExecuteSqlInputSchema = z.object({ sql: z.string().describe('The SQL query to execute.'), read_only: z.boolean().optional().default(false).describe('Hint for the RPC function whether the query is read-only (best effort).'), // Future enhancement: Add option to force direct connection? // use_direct_connection: z.boolean().optional().default(false).describe('Attempt to use direct DB connection instead of RPC.'), }); type ExecuteSqlInput = z.infer<typeof ExecuteSqlInputSchema>; // Output schema - expects an array of results (rows) const ExecuteSqlOutputSchema = z.array(z.unknown()).describe('The array of rows returned by the SQL query.');
- src/index.ts:104-104 (registration)Registers the executeSqlTool in the availableTools object, which is used to populate MCP capabilities and handle tool calls in the server.[executeSqlTool.name]: executeSqlTool as AppTool,
- src/tools/utils.ts:64-78 (helper)Utility function that implements the fallback logic: prefers direct PostgreSQL connection via pg if DATABASE_URL available, otherwise uses the RPC function via Supabase client.export async function executeSqlWithFallback( client: SelfhostedSupabaseClient, sql: string, readOnly: boolean = true ): Promise<SqlExecutionResult> { // Try direct database connection first (bypasses JWT authentication) if (client.isPgAvailable()) { console.info('Using direct database connection (bypassing JWT)...'); return await client.executeSqlWithPg(sql); } // Fallback to RPC if direct connection not available console.info('Falling back to RPC method...'); return await client.executeSqlViaRpc(sql, readOnly); }
- src/tools/utils.ts:20-35 (helper)Utility function to process SQL execution results: checks for errors and validates successful row data against the output schema, throwing descriptive errors if issues occur.export function handleSqlResponse<T>(result: SqlExecutionResult, schema: z.ZodSchema<T>): T { // Check if the result contains an error if ('error' in result) { throw new Error(`SQL Error (${result.error.code}): ${result.error.message}`); } // Validate the result against the schema try { return schema.parse(result); } catch (validationError) { if (validationError instanceof z.ZodError) { throw new Error(`Schema validation failed: ${validationError.errors.map(e => `${e.path.join('.')}: ${e.message}`).join(', ')}`); } throw new Error(`Unexpected validation error: ${validationError}`); } }