kdeps-templates-sql-agent

version: 1.0.1
files:
    README.md.j2: |
        # {{ name }}

        {{ description }}

        ## Quick Start

        1. Set the `main` database connection (machine-local, never in the workflow):
           ```bash
           export KDEPS_SQL_CONNECTIONS_MAIN_CONNECTION="postgres://user:password@localhost/dbname?sslmode=disable"
           ```

        2. Run in development mode:
           ```bash
           export KDEPS_API_AUTH_TOKEN=dev-token   # the API server will not start without a token
           kdeps run workflow.yaml --dev
           ```

        3. Test the API:
           ```bash
           curl -X POST http://localhost:{{ port }}/api/query \
             -H "Authorization: Bearer $KDEPS_API_AUTH_TOKEN" \
             -H "Content-Type: application/json" \
             -d '{"status": "active", "limit": 10}'
           ```

        ## Configuration

        ### Database Connection

        Resources use the named connection `main`. Its connection string lives in
        `~/.kdeps/config.yaml` (or the variable above), not in `workflow.yaml`:

        ```yaml
        # ~/.kdeps/config.yaml
        sql_connections:
          main:
            connection: "postgres://user:password@localhost/dbname?sslmode=disable"
            # mysql://..., sqlite://./app.db, sqlserver://..., oracle://...
        ```

        Pool settings go on the `sql:` resource: `pool: { maxConnections: 10 }`.

        ## API Endpoints

        ### POST /api/query

        Execute SQL query.

        **Request**:
        ```json
        {
          "status": "active",
          "limit": 10
        }
        ```

        **Response**:
        ```json
        {
          "success": true,
          "data": {
            "results": "[{\"id\": 1, \"name\": \"ann\", \"status\": \"active\"}]"
          }
        }
        ```

        ## License

        MIT
    resources/query.yaml.j2: |
        actionId: executeQuery
        name: Execute SQL Query

        validations:
          methods: [POST]
          routes: [/api/query]
          check:
            - get('status') in ['active', 'inactive', 'pending']
          error:
            code: 400
            message: "status must be active, inactive, or pending"

        component:
          name: sql
          with:
            connectionName: main          # sql_connections.main in ~/.kdeps/config.yaml
            query: |
              SELECT *
              FROM users
              WHERE status = $1
              LIMIT $2
            params:
              - "{% raw %}{{ get('status') }}{% endraw %}"
              - "{% raw %}{{ default(get('limit'), 10) }}{% endraw %}"
            format: json
    resources/response.yaml.j2: |
        actionId: formatResponse
        name: Format Response
        requires:
          - executeQuery

        validations:
          methods: [POST]
          routes: [/api/query]

        apiResponse:
          success: true
          response:
            results: get('executeQuery')
    workflow.yaml.j2: |
        apiVersion: kdeps.io/v1
        kind: Workflow

        metadata:
          name: {{ name }}
          description: {{ description }}
          version: "{{ version }}"
          targetActionId: formatResponse

        settings:
          apiServer:
            hostIp: "0.0.0.0"
            portNum: {{ port }}
            routes:
              - path: /api/query
                methods: [POST]
          # The `main` connection string is machine-local, never in this file:
          # ~/.kdeps/config.yaml sql_connections.main.connection
          # or export KDEPS_SQL_CONNECTIONS_MAIN_CONNECTION="postgres://..."