Piping SQL from stdin

When stdin is not a TTY, the default invocation reads and executes stdin as a multi-statement script. Ideal for generating SQL on the fly and piping in.

Syntax

<SQL producer> | delta-forge-cli [--format <fmt>]

Description

## Overview When stdin is a pipe (not a TTY), invoking `delta-forge-cli` with no subcommand reads the entire stdin stream, splits it into statements, and executes them in order. This is the preferred pattern for code that synthesises SQL (code generators, templating tools, shell loops) and for ad-hoc heredocs. ## Recipe ```bash # Emit a single scalar via generated SQL and read it back as a typed number count=$( echo 'SELECT count(*) AS n FROM public.orders' \ | delta-forge-cli --profile prod --format json \ | jq -r '.rows[0].n | tonumber' ) echo "orders: $count" ``` The pipe producer on the left generates a SQL statement, `delta-forge-cli` executes it against the `prod` profile, and jq extracts the named column from the first row. Because JSON row values are stringified, the `| tonumber` cast is required to use `$count` in a numeric context. ## Behavior - Stdin detection uses `isatty(0)`. If stdin is redirected (pipe, heredoc, `<file`), the CLI switches into stdin-script mode automatically. - Statement splitting is identical to `run`: `;` terminates, single-quoted literals are preserved, `--` comments are stripped to end of line. - Unlike `run`, stdin mode does not emit `[N/TOTAL]` progress headers; per-statement status lines still go to stderr. - The authenticated client is created once and reused across all statements in the stream. - Exit codes match `run`: 0 on success, 1 on SQL/auth/parse error, 2 on client init error.

Examples

# Scalar extraction with jq (note: rows are objects keyed by column name):
echo 'SELECT count(*) AS n FROM orders' | delta-forge-cli --format json | jq -r '.rows[0].n'

# Apply a generated migration file:
cat migrations.sql | delta-forge-cli --format compact

# Feed a Python generator directly:
python3 generate_sql.py | delta-forge-cli --profile staging

# Heredoc for inline scripts:
delta-forge-cli --format json <<'SQL'
SELECT status, count(*) AS n FROM jobs GROUP BY status;
SQL

Pitfalls

• The stdin script inherits `run`'s no-transaction behaviour. A failure on statement N leaves statements 1 through N-1 committed; recovery is the producer's responsibility.
• Interactive features (REPL, prompts, coloured output auto-detection) are suppressed when stdin is a pipe, even if stdout is still a TTY. If you need a REPL, pass `--classic` explicitly.
• Upstream producers that print progress or banner text to stdout will have that text sent to the engine as SQL. Separate generator logs from the emitted SQL (for example, log to stderr in the producer).
• Statement splitting is textual. SQL that contains `;` inside a dollar-quoted function body or multi-line string literal outside single quotes can be mis-split. Prefer single-quoted string literals where possible.

See Also

• CLI_STDIN_PIPE
• CLI_OUTPUT_JSON
• CLI_AUTOMATION_JQ