Service Task — SQL 💾

Executes SQL queries against a configured database.

Node type: serviceTask (subtype: SQL) Category: Integration Actor: serviceTask (2 threads)

---

Description

The SQL Service Task runs SQL queries against a connected database (PostgreSQL, MySQL, MSSQL, or Oracle). It supports two modes:

• Direct mode — you write the SQL queries explicitly
• AI mode — the LLM generates and executes SQL based on a natural language question

Connection credentials are stored in a Database Integration.

---

Properties

Property	Type	Required	Description
databaseConnectionId	string	Yes	ID of the Database integration
mode	select	Yes	direct — write SQL manually; ai — AI generates the SQL
queries	multiQueryEditor	Conditional	Required in direct mode. One or more SQL statements
transactional	checkbox	No	Wrap all queries in a single transaction (default: false)
question	textarea	Conditional	Required in ai mode. Natural language question
outputVariable	text	No	Variable name to store results (default: sqlResult)
openaiApiKey	password	Conditional	Required in ai mode if no AI integration configured

---

Direct Mode

Write explicit SQL queries. Support for multiple queries executed in sequence:

-- Query 1: Get customer
SELECT id, name, email, tier FROM customers WHERE id = '{customerId}'

-- Query 2: Get recent orders
SELECT order_id, total, status FROM orders
WHERE customer_id = '{customerId}'
ORDER BY created_at DESC
LIMIT 5

Variable substitution works inside SQL using {varName} syntax. Each {varName} reference is automatically parameterized to prevent SQL injection.

---

Outputs (Direct Mode)

Variable	Type	Description
{sqlResult}	array/object	Query results. Single query returns rows array; multiple queries return array of arrays
{sqlRowCount}	number	Number of rows affected/returned

For a SELECT query:

{
  "sqlResult": [
    { "id": "cust_123", "name": "Jane Smith", "email": "jane@example.com", "tier": "premium" }
  ],
  "sqlRowCount": 1
}

Access individual fields: {sqlResult[0].name}, {sqlResult[0].email}

For INSERT/UPDATE/DELETE:

{
  "sqlResult": null,
  "sqlRowCount": 3
}

---

AI Mode

Describe what data you need in natural language. The AI generates and executes the SQL:

question: Get the total revenue for the last 30 days grouped by product category

The AI mode:

1. Introspects the database schema
2. Generates appropriate SQL
3. Executes it
4. Returns the results

Output variable: Same as direct mode — {sqlResult} contains the rows.

---

Transactional Mode

When transactional is enabled, all queries run inside a single database transaction:

Query 1: UPDATE account SET balance = balance - {amount} WHERE id = '{fromAccountId}'
Query 2: UPDATE account SET balance = balance + {amount} WHERE id = '{toAccountId}'
Query 3: INSERT INTO transactions (from_id, to_id, amount) VALUES ('{fromAccountId}', '{toAccountId}', {amount})

If any query fails, all queries are rolled back atomically.

---

Connections

Condition	Connection
All queries succeeded	successFlow or sequenceFlow
Any query failed	errorFlow
Timeout exceeded	timeoutFlow

---

Example: Fetch and Process User Data

{
  "nodeId": "fetch-user-1",
  "name": "Fetch User Profile",
  "nodeType": "serviceTask",
  "properties": {
    "databaseConnectionId": "int_postgres_prod",
    "mode": "direct",
    "queries": [
      "SELECT u.id, u.name, u.email, u.plan, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.id = '{userId}' GROUP BY u.id, u.name, u.email, u.plan"
    ],
    "outputVariable": "userProfile"
  },
  "timeout": {
    "duration": 10,
    "durationUom": "SECONDS",
    "action": "FAIL"
  }
}

Downstream access: {userProfile[0].name}, {userProfile[0].order_count}

---

SQL Injection Prevention

Variable substitution uses parameterized queries internally. The engine replaces {varName} with JDBC/driver-level bind parameters, not string concatenation. This prevents SQL injection even if variable values contain special characters.

Never use string concatenation to build SQL — always use {varName} in the query text.

---

Supported Databases

Database	Driver	Notes
PostgreSQL	JDBC	Version 12+ recommended
MySQL	JDBC	Version 8+ recommended
Microsoft SQL Server	JDBC	Any modern version
Oracle	JDBC	Oracle 12c+

---

Best Practices

• Use transactional mode for multi-step operations that must be atomic
• Always set a timeout — queries on large tables can be slow
• For queries returning large result sets, use LIMIT/TOP to avoid out-of-memory issues
• Use the outputVariable property to rename results when chaining multiple SQL nodes
• In AI mode, review generated SQL in development before deploying to production
• Never SELECT * — specify only the columns you need