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: 10sAnalytics 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: 60sMulti-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
- suggestionsSee also
- SQL resource - Full sql: reference with transactions, batch ops, connection pooling
- Python resource - Post-process SQL results with pandas
- Function calling - Use SQL as an LLM tool
