> ## Documentation Index
> Fetch the complete documentation index at: https://dbhub.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# 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 theme={null}
[[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 theme={null}
[[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 theme={null}
[[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 theme={null}
    [[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 theme={null}
    [[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 theme={null}
    [[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 theme={null}
[[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 theme={null}
[[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 theme={null}
[[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 theme={null}
# 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 theme={null}
[[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 theme={null}
{
  "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 theme={null}
{
  "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