Query commands

The command in sgl.query(Name, command) determines how the generated method executes and what it returns. The dialect must support the SQL and the emitter must support the command. Static methods take ctx followed by their parameters, when present.

With the preset's WrapErrors set, every error a method returns is labelled with the error prefix and the method's name, such as sgl: GetUser: ..., and still matches the error beneath with errors.Is; see labelled errors.

Reading and writing rows

A one method returns sql.ErrNoRows, or pgx.ErrNoRows with pgx, when nothing matches. With optional: true, it returns (<Name>Row, bool, error) instead: a miss is (zero, false, nil). See optional single rows.

A stream method can be called as for row, err := range queries.Name(ctx, params). It scans without buffering the result set; breaking the loop closes the rows. Its return type is iter.Seq2[<Name>Row, error] under the default preset; a preset with ModernIdioms off returns func(func(<Name>Row, error) bool), the same underlying type. See streaming rows.

execrows and execresult are refused on both ClickHouse emitters (Q059), whose driver provides neither affected-row counts nor an insert result. asyncexec is accepted only for asynchronous ClickHouse mutations (Q041); its generated comment explains that acceptance does not mean completion. See asynchronous mutations.

Batch execution

A batch method takes items []<Name>Params and returns only error. An empty slice sends nothing. Result rows, including those from RETURNING, are discarded; Q009 warns when the query returns rows.

For database/sql and native ClickHouse, the statement must be an INSERT into a named table, with exactly one VALUES row containing every parameter. Other shapes are Q060. A trailing clause can reference inserted values through the engine's own form, such as EXCLUDED.name or VALUES(name).

The database/sql emitter sends an upsert once per item because a PostgreSQL statement cannot change the same row twice. Multi-row statements are chunked under the engine's bind-variable limit; ClickHouse uses typed placeholders and at most 1024 bound values per statement. A VALUES row these chunks repeat that binds more variables than one statement allows could not be sent even alone, so it is refused when the code is generated (Q060): insert fewer columns per statement, or move values every row shares into the SQL. Separate statements can commit separately unless the caller supplies a transaction; with WrapErrors, a failed statement's error names the row it starts at, counted from zero. See bulk writes.

Copying rows

A copyfrom method also takes items []<Name>Params. Its signature and restrictions depend on the emitter:

pgx requires an INSERT into a named table with an explicit column list and one VALUES row containing only placeholders. No clause may follow it, including ON CONFLICT or RETURNING (Q060). COPY writes values in binary; types that need per-connection registration, such as ltree, composites and enum arrays, are refused (Q059). Use batch for those cases.

Native ClickHouse requires the same single-row INSERT shape, with only placeholders in its VALUES row (Q060). database/sql uses the multi-row shape of batch, but keeps multi-row chunks even when a clause follows VALUES. An upsert that repeats a key within a chunk can therefore fail on PostgreSQL; use batch to send each item separately. An empty slice sends nothing on any emitter.