# Query Modes Reference

This skill supports 3 modes and shares one session policy.

Prerequisite for all modes:

- `DATABASE_URL` must exist in environment or `.env`
- format: `mysql://<username>:<password>@<host>/<database>`
- supported plans: TiDB Cloud Starter and Essential

If the HTTP host is not found, tell the user this direct endpoint flow is only
for Starter/Essential. Dedicated/Premium use different connectivity patterns.

## Shared session policy

- For any mode, if response includes `TiDB-Session`, script stores and reuses it.
- This update also applies to non-2xx responses (for example, `invalid session`) so retries can continue with the latest server-issued token.
- For `invalid session`, script clears that same session file and retries once automatically (still using the same filename).
- If response omits `TiDB-Session`, script keeps using the current stored value.
- `stateful close` sends the current session, then clears stored session after request.
- Session files are under `.session/` even when `--session-file` is provided with a path.

## 1. Stateless connection

Semantics:

- each query is independent
- no need to explicitly open/close stateful channel

HTTP behavior:

- send SQL with current `TiDB-Session` (can be empty on first call)
- if server returns `TiDB-Session`, next request reuses it

Example:

```bash
bash skills/tidbcloud-http/scripts/tidb_serverless_http.sh stateless --sql "show tables"
```

## 2. Stateful connection

Semantics:

- maintain session state across multiple queries
- must close after use

HTTP behavior:

1. `open`: send empty query with `TiDB-Stateful-Action: open`
2. `query`: run SQL under returned session
3. `close`: send empty query with `TiDB-Stateful-Action: close`

Example:

```bash
bash skills/tidbcloud-http/scripts/tidb_serverless_http.sh stateful open
bash skills/tidbcloud-http/scripts/tidb_serverless_http.sh stateful query --sql "use mysql"
bash skills/tidbcloud-http/scripts/tidb_serverless_http.sh stateful close
```

## 3. Transaction

Semantics:

- interactive transaction over maintained session
- one flow: `BEGIN` -> queries -> `COMMIT` or `ROLLBACK`

HTTP behavior:

- `BEGIN` can carry `TiDB-Isolation-Level`
- same `TiDB-Session` must be reused for all statements in tx

Example:

```bash
bash skills/tidbcloud-http/scripts/tidb_serverless_http.sh tx begin --isolation "REPEATABLE READ"
bash skills/tidbcloud-http/scripts/tidb_serverless_http.sh tx query --sql "insert into t values (1)"
bash skills/tidbcloud-http/scripts/tidb_serverless_http.sh tx rollback
```

## Safety and usage notes

- Do not run parallel SQLs in same session for stateful/tx flows.
- Close stateful sessions to avoid connection limit pressure.
- If idle too long, session may expire; reopen and retry.
- Script enforces max 10,000 returned rows per query; add `LIMIT` for large scans.
- Binary-like types (`BLOB`/`BINARY`/`BIT`) are auto-decoded from hex to byte arrays by default, without an extra SQL request.