execute_sql
Run custom SQL queries directly on self-hosted Supabase databases. Supports read-only hints for optimized execution. Ideal for database introspection and management tasks within development environments.
Instructions
Executes an arbitrary SQL query against the database, primarily using the execute_sql RPC function.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| read_only | No | Hint for the RPC function whether the query is read-only (best effort). | |
| sql | Yes | The SQL query to execute. |
Implementation Reference
- src/tools/execute_sql.ts:36-43 (handler)Main handler logic for the execute_sql tool: logs query preview, calls executeSqlWithFallback helper, processes response with handleSqlResponse.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 input (SQL query and read_only flag) and output (array of rows).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/tools/execute_sql.ts:30-44 (registration)Complete tool definition and export, including name, description, schemas, and handler. Imported and registered in src/index.ts.export const executeSqlTool = { name: 'execute_sql', description: 'Executes an arbitrary SQL query against the database, using direct database connection when available or RPC function as fallback.', inputSchema: ExecuteSqlInputSchema, mcpInputSchema: mcpInputSchema, outputSchema: ExecuteSqlOutputSchema, 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/utils.ts:64-78 (helper)Core helper for SQL execution: prefers direct PostgreSQL connection if available, falls back to RPC method.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)Helper function to process SQL results: checks for errors and validates against provided Zod schema.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}`); } }