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

# Database

> Query your own PostgreSQL or MySQL from a workflow — parameters bound by the driver, read-only by default, over TLS, with the credentials held in a Secret.

# Database

The **Database** step runs one SQL statement against **your own** PostgreSQL or MySQL
and hands the rows to the rest of the workflow. It is how a workflow enriches a webhook
payload from your users table, looks up an order before calling an API, or writes a
result back where your application can see it.

It is not a database KnoxCall hosts. The server is yours; KnoxCall connects to it, runs
the statement you wrote, and forgets the connection.

## Setting one up

<Steps>
  <Step title="Store the connection as a Secret">
    Create a Secret whose value is a connection URI:

    ```text theme={"dark"}
    postgres://appuser:the-password@db.example.com:5432/appdb
    ```

    `postgres://`, `postgresql://`, `mysql://` and `mariadb://` are all understood — a
    JDBC-style `jdbc:postgresql://…` prefix is not; strip it. The
    Secret is envelope-encrypted like every other Secret and is resolved only while the
    step runs — it is never written into the workflow.
  </Step>

  <Step title="Add a Database step and pick it">
    Choose **PostgreSQL** or **MySQL** to match the Secret, then select the Secret in
    **Connection Secret**.
  </Step>

  <Step title="Write the SQL, and put the values in Parameters">
    ```sql theme={"dark"}
    SELECT id, email, plan FROM users WHERE email = $1 AND active = $2
    ```

    ```json theme={"dark"}
    ["{{trigger.body.email}}", true]
    ```
  </Step>
</Steps>

## Values go in Parameters, never in the SQL

The SQL box is **not** templated. `{{variables}}` in it are refused when you save, and
refused again if the step somehow runs.

That is deliberate, and it is the single most important thing on this page. A value
pasted into a statement stops being a value — `' OR '1'='1` in the middle of a `WHERE`
clause is *syntax*, and it is how databases get emptied. A value in **Parameters** is
sent to your database separately from the statement and can never be parsed as SQL,
whatever it contains.

Write the placeholders your database uses:

| Database        | Placeholder        | Example                             |
| --------------- | ------------------ | ----------------------------------- |
| PostgreSQL      | `$1`, `$2`, `$3` … | `WHERE id = $1 AND created_at > $2` |
| MySQL / MariaDB | `?`                | `WHERE id = ? AND created_at > ?`   |

Parameters is a JSON array, in placeholder order, and **is** templated:

```json theme={"dark"}
["{{trigger.body.id}}", "{{steps.lookup.output.since}}", 25]
```

Values keep their type: a number stays a number, `true` stays a boolean, an object
becomes JSON.

<Warning>
  Never put a `{{secrets.NAME}}` in the SQL or in Parameters. It is refused when you save.
  A credential in a statement is written to your own database server's query log, and a
  credential in a parameter comes back inside any error the database raises about it —
  onto a step row every member of your workspace can read. The connection Secret is the
  only credential this step needs.

  A **pasted connection string** — `postgres://user:password@host/db` typed into the SQL
  box, into Parameters, or into the Connection Secret field instead of picking a Secret —
  is refused the same way, in every step of every kind, not just this one. That includes the
  JDBC spelling (`jdbc:postgresql://user:password@host/db`, `jdbc:mysql://…`) and any other
  scheme prefix, wherever it appears in the value. A workflow
  definition is readable by everyone in your workspace, so a password in one is a password
  disclosed. If a step's configuration is too large for KnoxCall to check, saving is
  refused rather than allowed unchecked.
</Warning>

## One statement per step

A Database step runs exactly one statement. `SELECT 1; DROP TABLE users` is refused when
you save, and your database refuses it again at run time: KnoxCall always uses the
prepared-statement protocol, which cannot carry two commands.

If you need several statements, use several steps — or put them in a function or a view
and call that.

## Read-only by default

**Read-only** is on when you add the step, and it is your *database* that enforces it,
not a check on the text of your SQL. The statement runs inside a read-only transaction
(`BEGIN READ ONLY` on PostgreSQL, `START TRANSACTION READ ONLY` on MySQL), so the server
refuses any write — including one hidden inside a function your statement calls.

Turn **Read-only** off to `INSERT`, `UPDATE` or `DELETE`. When you do:

* the step's output records `readOnly: false`, so a run that wrote is visible as one;
* the statement still runs inside a transaction, so a failure rolls back;
* `affectedRows` on the output tells you how many rows changed.

The strongest version of this control is not in KnoxCall at all: give the step a
database user that only has the grants it needs. Read-only steps deserve a read-only
role.

## TLS

TLS is on and fully verified by default.

| Setting                                       | What it checks                                                                  |
| --------------------------------------------- | ------------------------------------------------------------------------------- |
| **Verify certificate and hostname** (default) | The certificate chains to a trusted CA **and** names the host you connected to. |
| **Verify certificate only**                   | The chain, not the name. For a server whose certificate names something else.   |
| **No TLS**                                    | Nothing. The credentials and every row cross the network in clear.              |

A server whose CA is not in the public trust store — Amazon RDS and Google Cloud SQL both
use their own roots — will **fail** to connect on the default rather than quietly
connecting insecurely. That is intentional: an unverified TLS session is not a weaker
guarantee than a verified one, it is a different one, and an attacker on the path can
present any certificate and read your database password.

Whichever you choose is recorded on every run's output as `tls`.

## Where KnoxCall will and will not connect

The Database step opens a raw TCP connection, so it goes through a chokepoint of its own.
Before any socket is opened, KnoxCall resolves your host **once** and refuses to connect
if the address is:

* a private, loopback or link-local address (`10.x`, `192.168.x`, `127.0.0.1`, `::1`);
* a cloud metadata address (`169.254.169.254`);
* a KnoxCall host — the platform never sends your traffic to itself;
* one of KnoxCall's own outbound addresses.

It then connects to **that exact address**, so nothing can change what the name points at
between the check and the connection. The certificate is still checked against the *name*
you gave.

Ports must be **1024–65535**, and a short list of well-known non-database services
(Redis, Elasticsearch, memcached, HTTP proxies, Docker, Vault, and similar) is refused.
Databases do not live on port 22 or port 80, and nothing good comes of a database driver
handshaking with something that is not a database.

<Note>
  Your database must be reachable from the public internet for this step to connect. If it
  is inside a private network, expose a read replica, put it behind a bastion you control,
  or use the HTTP Request step against an API in front of it.
</Note>

The same chokepoint governs the **Send Email** step when it is set to a custom SMTP host:
the address rules above apply unchanged, and the port must be **465**, **587** or **2525**.
Port 25 is refused — that is server-to-server mail delivery, not submission, and KnoxCall
does not relay it. A private or internal mail relay is refused for the same reason a
private database host is.

## Bounds

| Setting       | Range                            | What it does                         |
| ------------- | -------------------------------- | ------------------------------------ |
| **Row limit** | 1–1,000 (default 100)            | How many rows the step keeps.        |
| **Timeout**   | 1,000–30,000 ms (default 10,000) | The whole step's budget — see below. |

**Timeout is one budget for the whole step.** Resolving your database's host, opening
the connection and running the statement all draw on the same number — a step set to
10,000 ms will not spend 10,000 ms connecting and then another 10,000 ms querying. When
the budget runs out the connection is closed and the step fails.

**Row limit bounds the output, not the work.** Your database still produces every row a
statement asks for; the limit decides how many are kept in the step's output. Put a
`LIMIT` in your SQL to bound what the database does. When more rows came back than were
kept, the output says so: `truncated: true`, with `rowCount` reporting the real total.

## Output

```json theme={"dark"}
{
  "rows": [{ "id": 3, "email": "user3@example.com" }],
  "rowCount": 1,
  "fields": [{ "name": "id", "typeId": 23 }, { "name": "email", "typeId": 25 }],
  "truncated": false,
  "affectedRows": null,
  "readOnly": true,
  "tls": "verify-full",
  "driver": "postgres",
  "host": "db.example.com",
  "durationMs": 42
}
```

Reference a value downstream the usual way — `{{steps.lookup.output.rows[0].email}}`.

## Live and Test

Secrets are separate per mode, so the Test copy of your connection Secret can point at a
different database. **A Test run will dial whatever that Secret says** — if you leave it
pointing at production, a Test run reaches production. Point it at a staging database,
or at a read-only replica, before you rely on Test mode for safety.

## What it costs

One operation per query, billed as an action — the same as an HTTP Request step.