> ## Documentation Index
> Fetch the complete documentation index at: https://docs.royaltyport.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

> Query automation databases with structured operations or raw SQL

## Overview

The Database step lets you read, write, and query your [automation databases](/automations/databases). Choose from structured operations (Read, Insert, Update, Upsert, Delete) with a visual builder, or write raw SQL with the Query operation.

## Configuration

| Field         | Description                                    |
| ------------- | ---------------------------------------------- |
| **Database**  | Select a connected automation database         |
| **Operation** | Read, Insert, Update, Upsert, Delete, or Query |
| **Table**     | Target table (for structured operations)       |

### Operations

| Operation  | Description             | Fields                                                                                                                                                        |
| ---------- | ----------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Read**   | Fetch rows from a table | Column toggles to select which columns to return                                                                                                              |
| **Insert** | Add a new row           | Set field values for the new row                                                                                                                              |
| **Update** | Modify existing rows    | Set field values + where clauses to match rows                                                                                                                |
| **Upsert** | Insert or update        | Set field values (inserts if not found, updates if exists)                                                                                                    |
| **Delete** | Remove rows             | Where clauses to match rows to delete                                                                                                                         |
| **Query**  | Run raw SQL             | SQL editor with [template variable](/automations/steps/overview#template-variables) support and [AI generation](/automations/steps/overview#generate-with-ai) |

### Where Clauses

For Update, Delete, and Read operations, use where clauses to filter rows. Each clause specifies a column, operator, and value.

**Available operators:**

| Operator      | Description                        |
| ------------- | ---------------------------------- |
| `=`           | Equals                             |
| `!=`          | Not equals                         |
| `>`           | Greater than                       |
| `<`           | Less than                          |
| `>=`          | Greater than or equal              |
| `<=`          | Less than or equal                 |
| `LIKE`        | Pattern match (case-sensitive)     |
| `ILIKE`       | Pattern match (case-insensitive)   |
| `IN`          | Matches any value in a list        |
| `NOT IN`      | Does not match any value in a list |
| `BETWEEN`     | Within a range                     |
| `IS NULL`     | Value is null                      |
| `IS NOT NULL` | Value is not null                  |

## Output

| Field           | Type    | Description                      |
| --------------- | ------- | -------------------------------- |
| `rows`          | array   | Result rows                      |
| `count`         | number  | Number of affected/returned rows |
| `error`         | boolean | Whether an error occurred        |
| `error_message` | string  | Error description if failed      |

## Throwing Errors from SQL

For the **Query** operation, you can fail the step from inside your SQL by selecting an `_error` column. If any returned row has a non-empty `_error` value, the step fails and the value is surfaced verbatim as the step's error message. The `_error` column is stripped from the rows on the happy path, so downstream steps never see it.

```sql theme={null}
SELECT
  CASE WHEN COUNT(*) = 0 THEN 'No matching records found' END AS _error,
  id,
  name
FROM contracts
WHERE status = {{ trigger.status }}
GROUP BY id, name;
```

<Tip>
  Use this to enforce data preconditions (missing records, validation failures, duplicates) and produce a clear user-facing error message without writing a separate Validator step.
</Tip>