Skip to content

SQL resource examples ​

Example queries for the sql component. All examples use parameterized queries to prevent SQL injection.

User lookup ​

yaml
# resources/user-lookup.yaml
actionId: userLookup
validations:
  check:
    - get('user_id') != ''
  error:
    code: 400
    message: User ID is required

component:
  name: sql
  with:
    connectionName: main
    query: |
      SELECT id, name, email, created_at
      FROM users
      WHERE id = $1
    params:
      - "{{ get('user_id') }}"
    format: json
    timeout: 10s

Analytics query ​

yaml
# resources/analytics.yaml
actionId: analytics
component:
  name: sql
  with:
    connectionName: analytics
    query: |
      SELECT
        DATE(created_at) as date,
        COUNT(*) as total_orders,
        SUM(amount) as revenue,
        AVG(amount) as avg_order
      FROM orders
      WHERE created_at >= $1
        AND created_at < $2
      GROUP BY DATE(created_at)
      ORDER BY date DESC
    params:
      - "{{ get('start_date', '2024-01-01') }}"
      - "{{ get('end_date', '2024-12-31') }}"
    format: csv
    maxRows: 365
    timeout: 60s

Multi-database sync ​

yaml
# Fetch from source database
actionId: fetchSource
component:
  name: sql
  with:
    connectionName: source_db
    query: "SELECT * FROM products WHERE updated_at > $1"
    params:
      - "{{ get('last_sync') }}"
    format: json

---
# Insert into destination database
actionId: syncProducts
flow:
  requires: [fetchSource]
component:
  name: sql
  with:
    connectionName: dest_db
    transaction: true
    queries:
      - query: |
          INSERT INTO products (id, name, price, updated_at)
          VALUES ($1, $2, $3, $4)
          ON CONFLICT (id) DO UPDATE SET
            name = EXCLUDED.name,
            price = EXCLUDED.price,
            updated_at = EXCLUDED.updated_at
        paramsBatch: "{{ get('fetchSource') }}"

Search with LLM enhancement ​

yaml
# Search database
actionId: searchProducts
component:
  name: sql
  with:
    connectionName: main
    query: |
      SELECT id, name, description, price
      FROM products
      WHERE name ILIKE $1 OR description ILIKE $1
      LIMIT 10
    params:
      - "%{{ get('q') }}%"
    format: json

---
# Enhance with LLM
actionId: enhancedSearch
flow:
  requires: [searchProducts]
chat:
  model:
    name: llama3.2:1b
  context:
    prompt: |
      User searched for: {{ get('q') }}

      Found products:
      {{ get('searchProducts') }}

      Provide a helpful summary and recommendations.
  harness:
    output:
      json: true
      keys:
        - summary
        - recommended
        - suggestions

See also ​

kdeps has the Apache 2.0 License.