execute_sql
Execute custom SQL queries on a self-hosted Supabase database using direct connections or RPC fallbacks, enabling flexible data management and retrieval.
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 |
|---|---|---|---|
| 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)The main handler function of the 'execute_sql' MCP tool. It extracts the client from context, logs the query, executes it using the fallback helper, and processes the result 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-18 (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)Registration of the execute_sql tool in the availableTools object used by the MCP server.[executeSqlTool.name]: executeSqlTool as AppTool,
- src/tools/utils.ts:20-35 (helper)Helper function that checks for SQL errors in the result and validates the data against the 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}`); } }
- src/tools/utils.ts:64-78 (helper)Key helper that attempts direct PG connection first, falling back to RPC execute_sql function for running the SQL query.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); }