SQL Resource

The sql: resource runs SQL queries against a named connection and returns the result set as the resource's output. Use it to read, write, or transact against any supported database.

Where it runs

Both workflow mode and agent mode. In workflow mode it executes as a DAG step. In agent mode, the workflow containing this resource runs as a single callable tool.

Basic Usage

# resources/sql.yaml

actionId: sqlResource
name: Database Query
sql:
  connectionName: main
  query: "SELECT * FROM users WHERE id = $1"
  params:
    - get('user_id')
  timeout: 30s

Supported Databases

Database	Connection String Format
PostgreSQL	postgres://user:pass@host:5432/db
MySQL	mysql://user:pass@host:3306/db
SQLite	sqlite:///path/to/file.db or sqlite:///:memory:
SQL Server	sqlserver://user:pass@host:1433/db
Oracle	oracle://user:pass@host:1521/service

Connection Configuration

Connection strings (DSNs) live in ~/.kdeps/config.yaml - never in workflow.yaml, which is version-controlled. Pool configuration lives in workflow.yaml.

~/.kdeps/config.yaml - credentials:

sql_connections:
  main:
    connection: "postgres://user:pass@localhost:5432/myapp"
  analytics:
    connection: "postgres://user:pass@analytics-db:5432/analytics"

workflow.yaml - pool config:

settings:
  sqlConnections:
    main:
      pool:
        maxConnections: 10
        minConnections: 2
        maxIdleTime: "30s"
        connectionTimeout: "5s"
    analytics:
      pool:
        maxConnections: 5
        minConnections: 1

Use in resources:

# resources/example.yaml
sql:
  connectionName: main  # must match key in sql_connections in ~/.kdeps/config.yaml
  query: "SELECT * FROM users"

Query Types

Simple Query

# resources/example.yaml
sql:
  connectionName: main
  query: "SELECT name, email FROM users WHERE active = true"
  format: json
  maxRows: 100
  timeout: 30s

Parameterized Query

# resources/example.yaml
sql:
  connectionName: main
  query: |
    SELECT * FROM orders
    WHERE customer_id = $1
      AND created_at >= $2
      AND status = $3
    ORDER BY created_at DESC
    LIMIT $4
  params:
    - get('customer_id')
    - get('start_date')
    - get('status', 'active')  # With default
    - get('limit', '100')
  format: json

Insert / Update / Delete

# resources/example.yaml
sql:
  connectionName: main
  query: |
    INSERT INTO users (name, email, created_at)
    VALUES ($1, $2, NOW())
    RETURNING id
  params:
    - get('name')
    - get('email')

Transactions

Execute multiple queries in a transaction:

# resources/example.yaml
sql:
  connectionName: main
  transaction: true
  queries:
    - query: "UPDATE accounts SET balance = balance - $1 WHERE id = $2"
      params:
        - get('amount')
        - get('from_account')

    - query: "UPDATE accounts SET balance = balance + $1 WHERE id = $2"
      params:
        - get('amount')
        - get('to_account')

    - query: |
        INSERT INTO transactions (from_id, to_id, amount, created_at)
        VALUES ($1, $2, $3, NOW())
      params:
        - get('from_account')
        - get('to_account')
        - get('amount')

If any query fails, the entire transaction is rolled back.

Batch Operations

Process multiple records efficiently:

# resources/example.yaml
sql:
  connectionName: main
  transaction: true
  queries:
    - query: |
        INSERT INTO products (name, price, category)
        VALUES ($1, $2, $3)
      paramsBatch: "{{ get('products') }}"

Where products is an array of parameter arrays:

[
  ["Product A", 19.99, "electronics"],
  ["Product B", 29.99, "electronics"],
  ["Product C", 9.99, "accessories"]
]

Result Formats

JSON (Default)

# resources/example.yaml
sql:
  connectionName: main
  query: "SELECT id, name, email FROM users"
  format: json

Output:

[
  {"id": 1, "name": "Alice", "email": "alice@example.com"},
  {"id": 2, "name": "Bob", "email": "bob@example.com"}
]

CSV

# resources/example.yaml
sql:
  connectionName: main
  query: "SELECT id, name, email FROM users"
  format: csv

Output:

id,name,email
1,Alice,alice@example.com
2,Bob,bob@example.com

Table

# resources/example.yaml
sql:
  connectionName: main
  query: "SELECT id, name, email FROM users"
  format: table

Output:

+----+-------+-------------------+
| id | name  | email             |
+----+-------+-------------------+
| 1  | Alice | alice@example.com |
| 2  | Bob   | bob@example.com   |
+----+-------+-------------------+

Connection Pooling

Configure connection pools in workflow settings:

# workflow.yaml
settings:
  sqlConnections:
    main:
      connection: "postgres://user:pass@localhost:5432/myapp"
      pool:
        maxConnections: 20      # Maximum pool size
        minConnections: 5       # Minimum idle connections
        maxIdleTime: "30s"     # Close idle connections after
        connectionTimeout: "5s" # Timeout for acquiring connection

Accessing Results

# In another resource
requires: [sqlResource]
apiResponse:
  response:
    # Full result set
    users: get('sqlResource')

    # First row
    first_user: get('sqlResource')[0]

    # Specific field from first row
    first_name: get('sqlResource')[0].name

Best Practices

1. Use named connections - Easier to manage and configure pooling
2. Always use parameterized queries - Prevent SQL injection
3. Set appropriate maxRows - Prevent memory issues
4. Use transactions for multi-step operations - Ensure data consistency
5. Configure connection pooling - Improve performance under load
6. Use appropriate timeouts - Prevent long-running queries from blocking

Security Notes

• Never interpolate user input directly into queries
• Always use parameterized queries ($1, $2, etc.)
• Store database credentials in environment variables
• Use connection strings from environment in production

# Good -- parameterized; user input never touches the query string
query: "SELECT * FROM users WHERE id = $1"
params:
  - get('user_id')

Bad - SQL injection risk

# resources/example.yaml
query: "SELECT * FROM users WHERE id = {{ get('user_id') }}"

See Also

• SQL Examples - User lookup, analytics, multi-database sync, LLM-enhanced search
• Python Resource -- data processing scripts
• HTTP Client -- external API calls
• Workflow Configuration -- connection settings