Step DB SQL

Quick Answer

Use :db with :database set to :postgres, :mysql, or :clickhouse.
Provide either :sql or :template (not both), and pass values through :params as a vector.

Canonical Shape

Field	Type	Required	Notes
`:database`	keyword/string	Yes	`:postgres`, `:mysql`, or `:clickhouse`
`:connection`	keyword/string	Yes	Bound database slot or connection id
`:sql`	string	Yes*	Inline SQL
`:template`	keyword	Yes*	`:db-query` template id
`:params`	vector	No	Positional parameters for placeholders
`:timeout`	int	No	Query timeout in seconds
`:max-results`	int	No	Row cap (up to platform max)

* Provide exactly one of :sql or :template.

Canonical Example (Template + Positional Params)

{:templates [{:id :users-by-status
              :type :db-query
              :sql "select id, email from users where status = ? order by id asc limit ?"}]
 :functions [{:id :to-status-input
              :language :clojure
              :code "(fn [input] [(:status input) (or (:limit input) 100)])"}]
 :flow
 '(let [input (flow/input)
        params (flow/step :function :build-params
                 {:ref :to-status-input
                  :input input})
        rows (flow/step :db :fetch-users
               {:database :postgres
                :connection :app-db
                :template :users-by-status
                :params params
                :max-results 1000})]
    {:count (count rows)
     :rows rows})}

Parameterization And Safety

Prefer parameterized SQL with :params
Avoid building query values with string concatenation
Keep SQL in top-level :templates once query complexity grows

Connection And Binding Recipe

{:requires [{:slot :warehouse
             :type :database
             :backends #{:postgres}
             :label "Warehouse SQL DB"}]}

breyta connections list --type database
breyta flows configure <slug> --set warehouse.conn=conn-...

For SQL connections, the connection config's connection value should be the
secret id that stores the JDBC URL. The secret value must start with the JDBC
prefix for the backend, such as jdbc:postgresql:// for Postgres.

breyta connections update conn-... --config '{"connection":"warehouse-jdbc-url"}'
breyta flows configure <slug> --set 'warehouse-jdbc-url.secret=jdbc:postgresql://localhost:5432/postgres?user=postgres&password=postgres'
breyta connections test conn-...
breyta flows configure check <slug>

Use jdbc:postgresql://..., not postgresql://....

Parameter Pitfalls

:params must match placeholder order exactly
prefer ? placeholders for portable JDBC SQL
for dynamic query shaping, build params in a function step and keep SQL static in templates
inline string literals in SQL can be blocked by query security checks, so keep user values in :params

Query Design Checklist

project only needed columns
always pair LIMIT with deterministic ORDER BY
push filtering and aggregation into SQL before returning rows to flow memory
for variable-size result sets, return compact rows and then persist artifacts when needed

Failure Triage

permission/auth failures: verify bound connection credentials and DB grants
connection errors: verify host/network reachability and DB availability
syntax/placeholder failures: check placeholder count and SQL syntax together
timeout/slow queries: add indexes, reduce scanned rows, and tune :timeout only after query shape is improved

Output Shape

SQL DB steps return rows as vectors. Use a function step to map or enrich structure for downstream steps.