# Query builder reference

Source: https://docs.anetos.dev/reference/query-builder/

Everything available on `db.Q[T]`, the query started by
`db.Query[T](ctx)`. See [Query data](https://docs.anetos.dev/guides/queries/index.md) for a walkthrough.

Each method returns a new query; the original is unchanged.

## Conditions and clauses

| Method | SQL |
|---|---|
| `Where(conds...)` | `WHERE … AND …`; repeated calls add more conditions |
| `WhereRaw(sql, args...)` | A condition in SQL with `?` placeholders |
| `Join(sql, args...)` | A join clause in SQL (`JOIN users ON …`); only `T`'s columns are selected |
| `OrderBy(orders...)` | `ORDER BY`, from `col.Asc()`, `col.Desc()` or `db.OrderRaw(sql)` |
| `Latest()`, `Oldest()` | `ORDER BY created_at DESC` / `ASC` |
| `Limit(n)`, `Offset(n)` | `LIMIT` / `OFFSET` |
| `GroupBy(cols...)`, `Having(conds...)` | `GROUP BY` / `HAVING` (read the groups with `db.Select`) |
| `Distinct()` | `SELECT DISTINCT` |
| `Scope(fns...)` | Applies `func(*db.Q[T]) *db.Q[T]` modifiers |
| `WithTrashed()`, `OnlyTrashed()` | Include / only soft-deleted rows |
| `WhereKeys(ids...)` | `WHERE <primary key> IN (…)`; none matches nothing |
| `Similar(embeddingModel, v)`, `Hybrid(text, embeddingModel, v)` | Vector search: records nearest `v` by their chunks of `embeddingModel`; hybrid also ranks the full-text matches of `text` ([vector search](https://docs.anetos.dev/reference/query-builder/index.md#vector-search)) |
| `Search(text)` | Full-text search: rows matching every word of `text` (as prefixes from three letters, whole below; ten words at most), best first, then `OrderBy`'s order; needs a search index ([search](https://docs.anetos.dev/guides/search/index.md)). Text without words changes nothing; `Distinct` and `GroupBy` queries search without the relevance order; Update, Delete and `CursorPaginate` refuse it. It replaces a `Similar` or `Hybrid`, and they replace it |
| `WhereHas(rel, conds...)`, `WhereDoesntHave(rel, conds...)` | `EXISTS (…)` / `NOT EXISTS (…)` on a relation's rows ([relations](https://docs.anetos.dev/reference/models/index.md#relations)) |
| `With(rels...)` | Loads relations of the rows, one query per relation ([relations](https://docs.anetos.dev/reference/models/index.md#relations)) |
| `ForUpdate()`, `ForShare()` | Row locks until the transaction ends (nothing on SQLite) |

## Conditions

| Expression | SQL |
|---|---|
| `col.Eq(v)`, `Ne`, `Gt`, `Gte`, `Lt`, `Lte` | `=`, `<>`, `>`, `>=`, `<`, `<=`. `Eq(nil)` / `Ne(nil)` become `IS NULL` / `IS NOT NULL` |
| `col.In(vs...)`, `col.NotIn(vs...)` | `IN (…)`; an empty `In` matches nothing, an empty `NotIn` everything |
| `col.Between(lo, hi)` | `BETWEEN lo AND hi` |
| `col.Like(p)`, `col.NotLike(p)` | `LIKE` (case sensitivity depends on the database); `p` is a pattern: `%` and `_` in text from users widen it |
| `col.Contains(s)`, `col.StartsWith(s)` | `LIKE` with `s` taken literally (`%`, `_` and `!` escaped, `ESCAPE '!'`): for search boxes. `db.EscapeLike(s)` escapes for a raw `LIKE ? ESCAPE '!'` |
| `col.IsNull()`, `col.NotNull()` | `IS NULL`, `IS NOT NULL` |
| `db.And(...)`, `db.Or(...)`, `db.Not(c)` | Grouped with parentheses; `And()` is true, `Or()` is false |
| `db.SQL(sql, args...)` | Any SQL, with `?` or `:name` placeholders |

## Columns

`go tool anetos generate` declares `PostCols` with a typed column per field of
model `Post` ([Generate typed columns](https://docs.anetos.dev/guides/code-generation/index.md)). By
hand:

| Function or method | Makes |
|---|---|
| `db.Col[T]("name")` | A typed column; `"table.name"` qualifies it. `T` is the Go field's type (`*time.Time` for a nullable timestamp) |
| `db.JSONCol[T]("name")` | A column stored as JSON: values passed to its methods are encoded as JSON (nil stays `NULL`), and `db.Pluck` decodes them |
| `db.C("name")` | An untyped column (`Column[any]`) |
| `col.Of("posts")` | The same column qualified with a table (`posts.name`); `Of("")` removes the qualifier |
| `col.Name()` | The column name |
| `db.Columns[T]()` | Model `T`'s column names in field order, or an error if `T` isn't a model struct |
| `db.SoftDeleting[T]()` | Whether model `T` embeds `db.SoftDeletes` (a `deleted_at` column alone isn't enough) |

## Reading

| Method or function | Returns |
|---|---|
| `Get()` | `[]T`, empty (not nil) when nothing matches |
| `First()` | First row or `db.ErrNotFound` |
| `Find(id)`, `db.Find[T](ctx, id)` | Row by primary key or `db.ErrNotFound` |
| `All()` | `iter.Seq2[T, error]`, streaming rows |
| `Count()` | `int64` |
| `Exists()` | `bool` |
| `Paginate(page, perPage)` | `db.Page[T]`: `Data`, `CurrentPage`, `PerPage`, `Total`, `LastPage` (JSON: `data`, `current_page`, …); `HasPrev()`, `HasNext()`. A page below 1 is page 1; past the end, empty. `db.MapPage(page, fn)` turns its rows into an API's responses, keeping the counts |
| `CursorPaginate(cursor, perPage)` | `db.CursorPage[T]`: `data`, `per_page`, `next_cursor`, `prev_cursor`; `db.ErrInvalidCursor` (400) for malformed cursors |
| `db.Pluck(q, col)` | `[]V`, one column (decoded from JSON for a `db.JSONCol`) |
| `db.Sum(q, col)`, `db.Min`, `db.Max` | `V`; zero if no rows match. `Limit` and `Offset` are respected, `Distinct` is ignored (write `SUM(DISTINCT x)` with `db.Select`); with `GroupBy`, use `db.Select` |
| `db.Avg(q, col)` | `float64` |
| `db.Select[R](q, terms...)` | `[]R`, for custom SELECT terms (`"COUNT(*) AS posts"`) |

`perPage` below 1 means `db.DefaultPerPage` (15). Cursor pagination orders
by the query's column orderings plus the primary key; it rejects
`OrderRaw`, and the ordering columns shouldn't contain NULLs. Cursors are
encoded, not signed: a client can craft one, which only moves where its
own page starts.

## Vector search

For records with an embeddings table (`migrate.Schema.CreateEmbeddings`),
usually kept by [`ai.Embeddings`](https://docs.anetos.dev/guides/semantic-search/index.md).

| Method or function | Does |
|---|---|
| `Similar(embeddingModel, v)` | Joins the records' chunks of `embeddingModel` (all models' for `""`), keeps the `db.SimilarCandidates` (200) chunks nearest `v` by cosine distance, and orders the records by their nearest chunk, then `OrderBy`'s order, then the primary key (ties never make pages overlap). `v` must have the table's size |
| `Hybrid(text, embeddingModel, v)` | `Similar`'s list and `Search(text)`'s (200 each), merged by reciprocal rank fusion: a record scores `1/(60 + rank)` in each list it's in, so records both find come first. Needs a search index; text without words makes it `Similar` |
| `db.Vector` | `[]float32`; scans a string as pgvector's text (`[1,2,3]`, what the PostgreSQL driver returns), and bytes as little-endian float32s (MariaDB, SQLite) |
| `db.CosineDistance(a, b)` | `1 - cos`: 0 for the same direction, 1 unrelated, 2 opposite; an error for vectors of different sizes |
| `db.Chunks[T](ctx, ids...)` | `[]db.Chunk{RecordID, Position, Content, ContentHash, EmbeddingModel, Embedding}` of the records (all, for none), by record and position |
| `db.ReplaceChunks[T](ctx, id, chunks)` | Makes them the record's chunks, in one transaction that locks the record's row (positions from the slice's order); for a deleted record, removes its chunks. Outside a transaction, retried on deadlocks |
| `db.PruneChunks[T](ctx)` | Deletes the chunks of records that no longer exist, and returns how many (MariaDB: those another table's cascade left) |
| `db.NearestChunks[T](ctx, embeddingModel, v, ids...)` | `[]db.ChunkMatch{Chunk, Distance}` of the records, nearest first |
| `db.RecordID(row)`, `db.TableOf[T]()` | A record's integer primary key; `T`'s table |
| `db.EmbeddingsTable(table)` | `table + "_embeddings"` |
| `d.Supports(ctx, db.VectorSearch)`, `d.CheckCapabilities(ctx, feature, caps...)` | Whether the database has vector search: SQLite (the driver registers `anetos_vec_distance_cosine`), PostgreSQL with the `vector` extension available, MariaDB 11.7+; MySQL Community doesn't. `CheckCapabilities` returns an error naming the feature and what to install |

`Similar` and `Hybrid` combine with `Where`, scopes, soft deletes,
`Limit`, `Count` and `Paginate`; `Distinct` and `GroupBy` queries lose
their order; Update, Delete and `CursorPaginate` refuse them. Vector
indexes find nearest chunks approximately (HNSW on PostgreSQL and
MariaDB): a record outside the candidates isn't returned, and a record
with many near chunks takes more of them. The PostgreSQL driver sets
`hnsw.ef_search` to 200 and `hnsw.iterative_scan` to `strict_order`
(pgvector 0.8+) for each session, so the index returns 200 candidates
after `model`'s filter (by default it stops at 40); they apply to the
app's own pgvector queries too. The MySQL driver sets MariaDB's
`mhnsw_ef_search` to 1000 per session (default 20), for the same reason.
On MariaDB, the candidates are chunks of
existing records (its embeddings tables have no foreign key). Zero vectors differ
by database (SQLite: distance 1; MariaDB: 0; PostgreSQL: NaN): don't
store them. On MariaDB,
vectors travel as text (`VEC_FromText(?)`); raw SQL with a `db.Vector`
argument must write that too.

## Writing

| Method | Does |
|---|---|
| `Update(assignments...)` | `UPDATE … SET` from `col.Set(v)` or `col.SetRaw(sql, args...)`; adds `updated_at` for models with timestamps; returns the rows matched. Columns may be qualified with the model's own table |
| `Delete()` | Soft delete for `SoftDeletes` models (rows already deleted keep their `deleted_at`; with `OnlyTrashed` it does nothing), else `DELETE` |
| `ForceDelete()` | `DELETE` |
| `Restore()` | Clears `deleted_at` on matching trashed rows |

Mass writes don't run model hooks and accept only `Where` conditions:
`Join`, `OrderBy`, `Limit`, `Offset`, `GroupBy`, `Having`, `Distinct` and
locks are refused, because they don't work alike across databases. For
those, write the statement with `db.Exec`.

## Raw SQL

| Function | Does |
|---|---|
| `db.Raw[T](ctx, sql, args...)` | `[]T` from any query; `T` a struct or a single value |
| `db.RawFirst[T](ctx, sql, args...)` | First row or `db.ErrNotFound` |
| `db.Exec(ctx, sql, args...)` | `sql.Result` |

Placeholders: `?` everywhere (`??` for a literal `?` on PostgreSQL), or
`:name` with one `db.Named` argument. SQL without arguments is sent as
written. A whole `Raw` or `Exec` query
without `?` is sent as written, so native `$1` works there; fragments
(`db.SQL`, `WhereRaw`, `SetRaw`, `Join`) must use `?`.

## Transactions

| Function | Does |
|---|---|
| `db.Tx(ctx, fn)` | Runs `fn` in a transaction (a savepoint when nested) |
| `db.TxWithOptions(ctx, opts, fn)` | With `*sql.TxOptions` |
| `db.AfterCommit(ctx, fn)` | Runs `fn` after the commit (or now, outside a transaction) |
| `db.InTx(ctx)` | Whether `ctx` has a transaction |
| `db.WithTx(ctx, tx)` | Queries on the returned context use a `*sql.Tx` you began (and commit) yourself; `AfterCommit` callbacks on it never run |
| `db.WithoutTx(ctx)` | Queries on the returned context leave the transaction: their writes stay after a rollback (SQLite: they wait for it) |
| `q.WithContext(ctx)` | The query with another context, e.g. a base query run inside `Tx` |

## Errors

| Error | Status in handlers | When |
|---|---|---|
| `db.ErrNotFound` | 404 | `First`, `Find`, `RawFirst`, `Delete` found no row |
| `db.ErrInvalidCursor` | 400 | `CursorPaginate` got a cursor it didn't produce |
| `db.ErrNoDB` | 500 | The context has no database |
