Queries

Queries are the heart of Hyperterse. Each query defines a SQL statement that becomes a REST endpoint and MCP tool.

Defining queries

1
name: my-api
2

3
queries:
4
  get-user-by-id:
5
    use: production_db
6
    description: 'Retrieve a user by their unique ID'
7
    statement: |
8
      SELECT id, name, email, created_at
9
      FROM users
10
      WHERE id = {{ inputs.userId }}
11
    inputs:
12
      userId:
13
        type: int
14
        description: 'Unique user identifier'

Query properties

Property	Required	Description
`use`	Yes	Adapter name to execute query against
`description`	Yes	Human-readable description (used in docs)
`statement`	Yes	SQL query with template variables
`inputs`	No	Input parameter definitions
`data`	No	Output schema (for documentation)

How queries become endpoints

Each query automatically generates:

Endpoint Type	Path/Method	Description
REST	`POST /query/{query-name}`	Execute query via HTTP
MCP	`POST /mcp` with `tools/call`	Execute via MCP protocol
OpenAPI	Included in `GET /docs`	API documentation
LLM Docs	Included in `GET /llms.txt`	AI-friendly documentation

For a query named get-user-by-id:

# REST endpoint
curl -X POST http://localhost:8080/query/get-user-by-id \
  -H "Content-Type: application/json" \
  -d '{"userId": 123}'

# MCP endpoint
curl -X POST http://localhost:8080/mcp \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "get-user-by-id",
      "arguments": {"userId": "123"}
    },
    "id": 1
  }'

Writing statements

Based on the adapter used, you can use all the query language features supported by the database.

Basic SELECT

1
queries:
2
  list-products:
3
    use: main_db
4
    description: 'List all products'
5
    statement: |
6
      SELECT id, name, price, stock
7
      FROM products
8
      ORDER BY name ASC

Parameterized queries

Use {{ inputs.fieldName }} to inject validated inputs:

1
queries:
2
  search-products:
3
    use: main_db
4
    description: 'Search products by category and price range'
5
    statement: |
6
      SELECT id, name, price, category
7
      FROM products
8
      WHERE category = {{ inputs.category }}
9
        AND price >= {{ inputs.minPrice }}
10
        AND price <= {{ inputs.maxPrice }}
11
      ORDER BY price ASC
12
    inputs:
13
      category:
14
        type: string
15
        description: 'Product category'
16
      minPrice:
17
        type: float
18
        description: 'Minimum price'
19
      maxPrice:
20
        type: float
21
        description: 'Maximum price'

Pagination

1
queries:
2
  paginated-users:
3
    use: main_db
4
    description: 'Get users with pagination'
5
    statement: |
6
      SELECT id, name, email
7
      FROM users
8
      ORDER BY created_at DESC
9
      LIMIT {{ inputs.limit }}
10
      OFFSET {{ inputs.offset }}
11
    inputs:
12
      limit:
13
        type: int
14
        optional: true
15
        default: '20'
16
      offset:
17
        type: int
18
        optional: true
19
        default: '0'

Aggregations

1
queries:
2
  daily-signups:
3
    use: analytics_db
4
    description: 'Count user signups by day'
5
    statement: |
6
      SELECT
7
        DATE(created_at) as signup_date,
8
        COUNT(*) as total_signups
9
      FROM users
10
      WHERE created_at >= {{ inputs.startDate }}
11
        AND created_at <= {{ inputs.endDate }}
12
      GROUP BY DATE(created_at)
13
      ORDER BY signup_date DESC
14
    inputs:
15
      startDate:
16
        type: datetime
17
      endDate:
18
        type: datetime

Response format

All queries return a consistent JSON response.

Success response

1
{
2
  "success": true,
3
  "error": "",
4
  "results": [
5
    {
6
      "id": 1,
7
      "name": "Alice",
8
      "email": "alice@example.com"
9
    }
10
  ]
11
}

Error response

1
{
2
  "success": false,
3
  "error": "validation error for field 'userId': required input 'userId' is missing",
4
  "results": []
5
}

Query naming conventions

Query names must follow these rules:

Use lower-kebab-case or lower_snake_case
Start with a letter
Contain only letters, numbers, hyphens, and underscores

Valid names: get-user, list_products, user-signups-by-date

Invalid names: GetUser, 123query, get user

Documenting output schema

The optional data property documents the response structure:

1
queries:
2
  get-user:
3
    use: main_db
4
    statement: 'SELECT id, name, email FROM users WHERE id = {{ inputs.userId }}'
5
    inputs:
6
      userId:
7
        type: int

This generates better OpenAPI and LLM documentation. The schema is for documentation only—Hyperterse returns whatever the database returns.