# SQL

## Introduction

The **SQL Action** in DBSync Cloud Workflow enables you to interact with databases directly within your integration flows. Use it to write custom SQL queries that **retrieve**, **insert**, **update**, or **delete** records in supported databases — whether cloud-based or on-premise.

<figure><img src="https://1036205596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fv9avy716UiAsS24zOznZ%2Fuploads%2Fz2gBgQ3Kd0UtcY9wM1Gk%2Fsql.png?alt=media&#x26;token=b434e262-37b1-44fc-84f4-f9a3cd9a1674" alt=""><figcaption></figcaption></figure>

### Use Cases

* Fetch records from a database to feed into the data stream.
* Update records dynamically based on workflow logic.
* Insert new data from integrated apps.
* Delete outdated or unnecessary records.

### Key Features

* Works with any connected database (e.g., MySQL, PostgreSQL, SQL Server, Oracle).
* Supports dynamic queries using **Fx-enabled** variables.
* Real-time query results or update counts.
* Customizable **batch size** for optimized performance.
* Code editor with SQL syntax highlighting.

## How to Configure SQL Action

#### Step 1: Add SQL Action to the Flow

1. Open the **Cloud Workflow Development Studio.**
2. Drag and drop the **SQL Action** into your flow.

#### Step 2: Select a Data Source

3. Choose a database from the **Data Source** dropdown.
4. Only adapters of type `DatabaseAdapter` will be shown.

#### Step 3: Write Your SQL Query

5. Use the SQL editor (with **CodeMirror**) to build your query.
6. You can insert dynamic fields using the **Fx Panel** (right-hand side).

```sql
SELECT * FROM customers WHERE created_at > '{{last_sync_date}}'
```

#### Supported Query Types

| Query Type | Result                         |
| ---------- | ------------------------------ |
| SELECT     | Tabular output in data preview |
| INSERT     | Count of rows inserted         |
| UPDATE     | Count of rows updated          |
| DELETE     | Count of rows deleted          |

{% hint style="success" %}
Use `{{...}}` to reference dynamic variables in your query.
{% endhint %}

### Example: Dynamic Query

```sql
SELECT id, name FROM orders WHERE status = '{{order_status}}'
```

This pulls orders with a status provided by an earlier step in the workflow.

### Output & Execution

7. Click **Run** to preview the results.
8. **SELECT** → Displays data in tabular format.
9. **INSERT/UPDATE/DELETE** → Shows how many records were affected.

{% hint style="info" %}

### Batch Size

You can control processing by setting a custom **Batch Size**. Useful for large data syncs.
{% endhint %}

### Best Practices

* Use **Query Preview** before saving changes.
* Sanitize dynamic inputs using filters/validation.
* Keep queries optimized to avoid long-running transactions.
* Leverage batch control in high-volume flows.

### Troubleshooting

| Problem                    | Likely Cause                  | Solution                              |
| -------------------------- | ----------------------------- | ------------------------------------- |
| No results returned        | Filters too narrow            | Check query logic and variable values |
| Column not found           | Column name mismatch          | Check column names in database        |
| Access denied / auth error | Connector credentials invalid | Re-authenticate the connector         |

The **SQL Action** is a powerful addition to your DBSync Cloud Workflow, enabling direct and dynamic access to your databases within any flow. Use it to automate data syncing, enrichment, and control—all from a single visual interface.