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 connection
`: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-...

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.