DBHub

--- title: "Custom Tools" --- Custom tools allow you to define reusable, parameterized SQL operations that are automatically registered as MCP tools. They provide type-safe interfaces for common database queries without writing repetitive code. Custom tools are ideal for: - **Frequently used queries**: Define once, use everywhere without rewriting SQL - **Standardized data access**: Ensure consistent query patterns across your team - **Controlled database access**: Expose specific operations without granting broad SQL access - **AI-friendly interfaces**: Give AI models well-defined tools with clear parameters instead of open-ended SQL - **Complex queries**: Encapsulate JOINs, aggregations, or multi-step operations into simple tool calls - **Parameter validation**: Enforce type checking and allowed values before queries execute ## Configuration Custom tools are defined in your `dbhub.toml` configuration file. See the [TOML Configuration](/config/toml) documentation for complete configuration reference. **Required fields:** - `name` - Unique tool identifier - `description` - What the tool does (helps AI models) - `source` - Database source ID to use - `statement` - SQL query with parameter placeholders - `parameters` - Parameter definitions (optional) See [TOML Configuration - Tool Options](/config/toml#tool-options) for detailed field descriptions, parameter types, and validation rules. ## Examples ### Basic Query Simple SELECT query with a single parameter: ```toml [[tools]] name = "get_user_by_id" description = "Retrieve user details by their unique ID" source = "production" statement = "SELECT id, name, email, created_at FROM users WHERE id = $1" [[tools.parameters]] name = "user_id" type = "integer" description = "The unique user ID" ``` ### Search with Limit Search query with optional limit parameter: ```toml [[tools]] name = "search_employees" description = "Search employees by name with configurable result limit" source = "prod_pg" statement = "SELECT emp_no, first_name, last_name FROM employee WHERE first_name ILIKE '%' || $1 || '%' LIMIT $2" [[tools.parameters]] name = "search_term" type = "string" description = "Name to search for (case-insensitive partial match)" [[tools.parameters]] name = "limit" type = "integer" description = "Maximum number of results" default = 10 ``` ### Optional Filter with Enum Query with optional status filter using enum validation: ```toml [[tools]] name = "list_orders" description = "List orders with optional status filter" source = "prod_pg" statement = "SELECT * FROM orders WHERE customer_id = $1 AND ($2::text IS NULL OR status = $2)" [[tools.parameters]] name = "customer_id" type = "integer" description = "Customer ID" [[tools.parameters]] name = "status" type = "string" description = "Optional status filter" required = false allowed_values = ["pending", "processing", "shipped", "delivered"] ``` ### Multiple Database Types The same tool pattern works across different databases, just adjust the parameter placeholder syntax: <Tabs> <Tab title="PostgreSQL"> ```toml [[tools]] name = "get_product" description = "Get product by ID" source = "postgres_db" statement = "SELECT * FROM products WHERE id = $1" [[tools.parameters]] name = "product_id" type = "integer" description = "Product ID" ``` </Tab> <Tab title="MySQL/MariaDB/SQLite"> ```toml [[tools]] name = "get_product" description = "Get product by ID" source = "mysql_db" statement = "SELECT * FROM products WHERE id = ?" [[tools.parameters]] name = "product_id" type = "integer" description = "Product ID" ``` </Tab> <Tab title="SQL Server"> ```toml [[tools]] name = "get_product" description = "Get product by ID" source = "sqlserver_db" statement = "SELECT * FROM products WHERE id = @p1" [[tools.parameters]] name = "product_id" type = "integer" description = "Product ID" ``` </Tab> </Tabs> See [Parameter Placeholders](/config/toml#parameters) for syntax reference. ## Common Patterns ### Flexible Filtering Use `COALESCE` or NULL checks to make parameters optional: ```toml [[tools]] name = "search_users" description = "Search users with optional department filter" source = "production" # PostgreSQL: Use NULL check for optional parameters statement = "SELECT * FROM users WHERE ($1::text IS NULL OR department = $1) LIMIT $2" [[tools.parameters]] name = "department" type = "string" description = "Optional department filter" required = false [[tools.parameters]] name = "limit" type = "integer" description = "Maximum results" default = 50 ``` ### Date Range Queries ```toml [[tools]] name = "get_orders_by_date_range" description = "Retrieve orders within a date range" source = "production" statement = "SELECT * FROM orders WHERE created_at BETWEEN $1 AND $2 ORDER BY created_at DESC" [[tools.parameters]] name = "start_date" type = "string" description = "Start date (ISO 8601 format: YYYY-MM-DD)" [[tools.parameters]] name = "end_date" type = "string" description = "End date (ISO 8601 format: YYYY-MM-DD)" ``` ### Aggregation Queries ```toml [[tools]] name = "get_sales_summary" description = "Get sales summary by product category" source = "production" statement = """ SELECT category, COUNT(*) as total_orders, SUM(amount) as total_revenue, AVG(amount) as avg_order_value FROM orders WHERE status = $1 GROUP BY category ORDER BY total_revenue DESC """ [[tools.parameters]] name = "status" type = "string" description = "Order status" allowed_values = ["completed", "pending", "cancelled"] ``` ## Security & Validation ### SQL Injection Protection Custom tools use parameterized queries, which provide automatic protection against SQL injection attacks. Parameter values are never interpolated directly into SQL strings. <Warning> Always use parameter placeholders. Never concatenate user input into SQL statements. </Warning> ### Readonly Mode The `readonly` setting on `execute_sql` only affects that tool. Custom tools are controlled by their SQL statement - DBHub analyzes the statement to determine if it's read-only. ```toml # Configure execute_sql as read-only [[tools]] name = "execute_sql" source = "prod_pg" readonly = true # Custom tools are unaffected by readonly setting [[tools]] name = "get_user_by_id" source = "prod_pg" description = "Get user details" statement = "SELECT * FROM users WHERE id = $1" [[tools.parameters]] name = "user_id" type = "integer" description = "User ID" ``` ### Max Rows Enforcement For custom tools with parameterized LIMIT clauses, the connector's `max_rows` setting still applies: ```toml [[tools]] name = "execute_sql" source = "prod_pg" max_rows = 1000 # Hard limit on SELECT results [[tools]] name = "search_users" source = "prod_pg" description = "Search users with limit" statement = "SELECT * FROM users WHERE active = $1 LIMIT $2" # Even if $2 is 5000, max_rows=1000 will cap the results ``` ### Startup Validation Tools are validated when the server starts: - All required fields must be present - The specified source must exist - Tool names must be unique and cannot conflict with built-in tools (`execute_sql`, `search_objects`) - Parameter count must match SQL placeholders - Parameter types must be valid If validation fails, the server will not start and will display detailed error messages. ## Tool Response Format Custom tools return the same response format as `execute_sql`: **Success:** ```json { "success": true, "rows": [ { "id": 12345, "name": "Alice Johnson", "email": "alice@example.com", "created_at": "2024-01-15T10:30:00Z" } ], "count": 1, "source_id": "prod_pg" } ``` **Error:** ```json { "success": false, "error": "Parameter validation failed: user_id: Required", "code": "EXECUTION_ERROR" } ``` ## Best Practices - **Use descriptive names**: Tool names should clearly indicate their purpose (e.g., `get_active_users_by_department` rather than `query1`) - **Write detailed descriptions**: Help AI models understand when to use the tool by providing clear, complete descriptions - **Document parameter constraints**: Include units, ranges, and format expectations in parameter descriptions - **Leverage enums**: Use `allowed_values` for parameters with a fixed set of valid options - **Provide defaults**: Make tools easier to use by providing sensible defaults for optional parameters - **Keep tools focused**: Each tool should perform a single, well-defined operation - **Test parameter combinations**: Ensure optional parameters work correctly in all combinations - **Use multi-line strings**: For complex SQL, use TOML's `"""` multi-line syntax for readability ## See Also - [TOML Configuration](/config/toml) - Complete configuration reference - [execute_sql](/tools/execute-sql) - Direct SQL execution tool - [search_objects](/tools/search-objects) - Database schema exploration tool