kdeps-components-sql

version: 1.0.2
requires:
    schemas/v1: '>=1.0.4'
    tools/sql_ops: ""
files:
    component.yaml: |
        apiVersion: kdeps.io/v1
        kind: Component
        metadata:
          name: sql
          description: Run SQL against PostgreSQL, MySQL/MariaDB, SQLite, SQL Server or Oracle - one query, or several in a transaction
          version: "1.1.1"
        interface:
          inputs:
            - name: connectionName
              type: string
              required: true
              connection: sql
              description: Name under sql_connections in ~/.kdeps/config.yaml (postgres://, mysql://, sqlite://, sqlserver://, oracle://)
            - name: query
              type: string
              default: ""
              description: The statement; placeholders $1, ?, @p1 or :1 all work with every database
            - name: params
              type: array
              default: []
              description: Values for the placeholders, in order
            - name: transaction
              type: boolean
              default: false
              description: Run queries in one transaction (all or nothing)
            - name: queries
              type: array
              default: []
              description: "Transaction statements: [{query, params}] or [{query, paramsBatch: [[...], [...]]}]"
            - name: format
              type: string
              default: ""
              description: "SELECT result shape: '' or table (rows), json (a JSON string), csv"
            - name: timeout
              type: string
              default: ""
              description: Statement timeout as a duration (default KDEPS_SQL_TIMEOUT, else 30s)
            - name: maxRows
              type: integer
              default: 0
              description: SELECT rows to return at most (default KDEPS_SQL_MAX_ROWS, else 1000)
          # The tool returns the value itself: a row, {rowsAffected, data, columns}, a
          # JSON or CSV string, a DML summary, or a transaction's list of results.
          output: "{{ fromJSON(output('sqlOperation').body) }}"
        resources:
          # One call to the kdeps API's sql_ops tool: the component holds no SQL code
          # of its own. The tool reads the connection string from config.yaml in the
          # kdeps process, so the password never crosses this request.
          - actionId: sqlOperation
            name: SQL
            httpClient:
              method: POST
              url: http://kdeps.api/apis/tools.kdeps.io/v1/tools/sql_ops/call
              headers:
                Content-Type: application/json
              failOnError: true
              timeout: 600s
              data:
                connectionName: "{{ get('sql.connectionName') }}"
                query: "{{ get('sql.query') }}"
                params: "{{ get('sql.params') }}"
                transaction: "{{ get('sql.transaction') }}"
                queries: "{{ get('sql.queries') }}"
                format: "{{ get('sql.format') }}"
                timeout: "{{ get('sql.timeout') }}"
                maxRows: "{{ get('sql.maxRows') }}"