Query DSL

GoFastr ships a small agent-friendly query DSL. Strings can be generated by an LLM, parsed safely, validated against the entity registry, and compiled to a core/query.QueryBuilder, never to raw SQL strings the agent supplied.

Row scopes are NOT applied. dsl.BuildDSLQuery refuses hidden and NoQuery fields and projects only visible columns, but it does not add owner, tenant, or soft-delete predicates. Running its query directly against an owner- or tenant-scoped entity returns every owner's rows and trashed rows alike. Do not wire agent-generated DSL straight to BuildDSLQuery on a scoped entity; go through the CRUD handler (which applies the scopes) or add the scope predicates yourself.

Quickstart

9 lines
qb, err := dsl.BuildDSLQuery(app.Registry,
    `posts.where(status="published", views>=10)
          .include(author)
          .order(created_at DESC)
          .limit(10)`,
)
if err != nil { … }
sqlStr, args := qb.Build() // compile to SQL + bound args
rows, err := db.QueryContext(ctx, sqlStr, args...)

BuildDSLQuery returns a builder you can extend with additional calls. Use dsl.ParseDSL if you only need the parsed AST.

Grammar

6 lines
query   := entity ("." call)*
call    := where(args) | include(args) | order(args) | limit(N) | after(cursor)
where   := field op value (, field op value)*
op      := = | != | > | < | >= | <= | contains | in
include := relation (, relation)*
order   := field [ASC|DESC]
  • Identifiers (entity, field, relation) are unquoted.
  • String values use "…" or '…'. Numeric and boolean values are bare (123, 1.5, true).
  • Whitespace inside calls is permitted. The parser is whitespace- tolerant but the splitter respects quotes and nested parens.
  • The parser is strict about types: every entity, field, and relation is validated against the registry before SQL is produced.

Operators

OperatorSQL produced
=field = $1
!=field != $1
> < >= <=field > $1 etc.
containsLOWER(field) LIKE $1 ESCAPE '\\' with %value% lowered (wildcards escaped)
infield IN ($1, $2, …) over [a, b, c] syntax

contains escapes %, _, and \ in the user input so a query like name contains "50%_off" matches that literal string instead of acting as a wildcard pattern. It ignores case, the same shape ?q= and ?field_like= use: Postgres LIKE is case-sensitive and SQLite's folds ASCII only, so a bare LIKE answered differently per dialect. The case contract is the one ?q= documents: ASCII-case-insensitive everywhere, Unicode folding a Postgres bonus (SQLite's LOWER folds ASCII only, so a stored É is not matched there). contains on a column that is not text (a number, date or Bool) is refused, and a JSON column takes no operator at all.

in accepts either field in ["a","b"] or field in [1,2,3], at most 1000 values (filter.MaxINListEntries, the cap ?field_in=, ?where= and ?filter= apply). Every value, under every operator but contains, must be of the field's declared schema.FieldType and is bound as that type (filter.CoerceValue: an integer for Int, a decimal literal for Float and Decimal, exactly the strconv.ParseBool spellings for Bool, RFC 3339 or a bare date for Timestamp, YYYY-MM-DD for Date). A value of another type is an error naming the field, never a text bind that Postgres refuses at query time while SQLite compares it as text.

Every operator follows filter.OpSuitsType, the rule ?field_<op>= and ?where= follow: > < >= <= on a Bool and any operator on a JSON column are errors, as contains on a non-text column is.

Clauses

where(...)

Comma-separated predicates. Each one splits at its leftmost operator, so an operator word inside a value is part of the value: title="Drive in Cinema" compares title with =. Multiple predicates AND together. There is no DSL-level or; express it as separate calls if your registry supports it, or use the lower-level core/query builder.

include(relation, ...)

Validates each relation exists on the entity. A name may be quoted (include("author")); the bare name is what the query keeps. Validation only; includes do not currently translate into SQL joins. Resolve eager loading through the CRUD list endpoint's ?include= query parameter, or the crud.EagerLoad helper, not through the DSL.

order(field [ASC|DESC])

One ordering per .order() call. Chain multiple .order() calls for multi-column ordering, at most 16, the cap ?sort= applies. Direction defaults to ASC when omitted; asc/desc match in any ASCII case (a look-alike such as deſc is an error, not DESC).

limit(N)

N must be a positive integer. There is no offset(); use cursor pagination via after(cursor) instead.

after(cursor)

Forward keyset pagination. BuildDSLQuery wires after(value) into a field > value predicate on the entity's cursor column (Pagination.CursorField when set, otherwise the primary key). Unlike the HTTP cursor API (which round-trips an opaque base64 token), the DSL takes the bare keyset value: posts.after(1024) means "rows after id 1024". The value must be of the cursor column's type, the same check a where() value gets: after("abc") on an integer key is an error.

2 lines
posts.where(status="published").order(id).after(1024).limit(20)
→ … WHERE (status = $1) AND (id > $2) ORDER BY id ASC LIMIT $3

after() orders the rows by the cursor column ascending itself, so order() is optional beside it. An order() that names any other column, or the cursor column DESC, is an error: the keyset cut and the sort would disagree, and walking the pages would skip and repeat rows. A key on a column that is not unique skips rows: after(5) on a non-unique CursorField resumes after EVERY row holding 5, so the second of two tied rows is never served. The HTTP cursor API pages on (CursorField, primary key) and has no such gap; page a non-unique column through it.

Composite cursors (EntityConfig.Pagination.CursorFields) have no single-value DSL form, so after() on such an entity returns an error rather than silently paging from the start; use the HTTP cursor API for those.

after() compares the one cursor column and nothing else, so that column must be unique: the entity's sole primary key, or a CursorField declared Unique: true. On any other CursorField (a created_at two rows can share) the rows tied with the value it resumes after would never be served, so after() returns an error (dsl.after_not_unique) naming the field. Mark the field Unique, cursor on the key, or page through the HTTP cursor API, which appends the key as a tiebreak.

Filter text: dsl.ParsePredicate and dsl.ParseSort

Beside the chained agent syntax sits a second, smaller parser for filter TEXT — the form a list view's Where, the admin's filter chips and the ?filter= URL parameter carry. ParsePredicate(text, fields) returns a *filter.Predicate that has already passed filter.ValidatePredicate against the entity's fields; it never builds SQL and never touches BuildDSLQuery, which applies no row scope. Hand the tree to the CRUD handler's in-process list (crud.ListOptions.Where), where the scopes wrap it.

5 lines
expr     := and_expr ("or" and_expr)*
and_expr := term ("and" term)*
term     := "(" expr ")" | field op value
op       := "=" | "!=" | "<" | ">" | "<=" | ">=" | "contains" | "in"
value    := "quoted \" \\ string" | number | true | false | [ value, … ]
4 lines
status = "open"
amount >= 10000
status in ["open", "past_due"] and due_on < "2026-10-01"
(kind = "company" or vat_number != "") and active = true

A quoted value knows two escapes, \" and \\; any other backslash is refused. Code that writes filter text quotes a value with dsl.Quote(v), which escapes the backslash before the quote, so C:\reports is written "C:\\reports" and parses back to itself. A value holding a line break cannot be written as a term, and one that is not valid UTF-8 or holds a NUL byte is refused like any value Postgres cannot bind.

Keywords are case-insensitive and and binds tighter than or. contains is a literal substring match: the %, _ and escape characters in the value are escaped and the clause carries ESCAPE, so contains "50%" matches a literal 50%, not a wildcard. Blank text returns (nil, nil); input over 8 KiB is refused; every error names its byte offset and quotes at most a short, control-byte-scrubbed excerpt. Field names must be plain identifiers and are checked against the entity's fields (unknown, Hidden and NoQuery names are refused, and an operator that does not suit the field's type — contains on an Int, say — is refused with it).

dsl.ParseSort(text, fields) is the sibling for sort text (due_on ASC, amount DESC, number ASC): filter.ParsedSort values under the same field rules as ?sort=, ASC by default, at most 16 keys.

Errors the parser rejects

  • Unknown entity: users.where(...) when users isn't registered.
  • Unknown field, relation, or order direction.
  • Unsupported operator.
  • limit(0) or negative limits.
  • Unclosed parens or unterminated strings.
  • An unquoted [ or ] that does not balance (where(title=a], seq=1)): quote a value that holds a bracket.
  • More than 1000 in values or more than 16 order() calls.
  • An order() beside after() that sorts by another column or DESC.

All errors are returned as plain Go errors with dsl: prefix.

When to use it (and when not to)

Use the DSL when:

  • An agent is generating queries from a natural-language prompt.
  • You want validation that the query references real fields before hitting the database.
  • The query shape is simple (filter + order + limit).

Drop down to core/query directly when:

  • You need joins, subqueries, raw SQL fragments, or window functions.
  • You need OR between predicates.
  • You need projections beyond SELECT * FROM entity_table.

Where ANDs a condition with everything before it and OrWhere ORs it, in call order. The builder wraps each condition in parens, and an AND that follows an OR wraps everything before it too, so a tenant, owner or soft-delete scope the framework appends after your OrWhere covers the whole predicate:

5 lines
qb := query.Select("id").From("posts").
	Where("status = $1", "draft").
	OrWhere("status = $1", "pub").
	Where("tenant_id = $1", tenant)
// SELECT id FROM posts WHERE ((status = $1) OR (status = $2)) AND (tenant_id = $3)

Builds before v0.96.0 wrote that chain flat, as (status = $1) OR (status = $2) AND (tenant_id = $3), which SQL reads as draft OR (pub AND tenant), so every tenant's drafts matched. A chain with no OrWhere before a Where builds the same SQL as before.

A condition may end in a -- comment; the builder ends the line before its closing paren.

core/query builds writes too. query.Update and query.Delete take the same Where conditions, and Returning(cols...) ends either in a RETURNING clause naming the changed rows' columns. Matching(cols...) on either builder returns the SELECT of those columns from the rows the write would reach, with the same conditions and only their arguments, so a caller can check the rows before it writes:

2 lines
del := query.Delete("notes").Where("slug = $1", slug).Where("deleted_at IS NOT NULL")
sel, args := del.Matching("slug") // SELECT slug FROM notes WHERE (slug = $1) AND (deleted_at IS NOT NULL)

Raw SQL that names a declared table or column quotes it with query.QuoteFolded, after query.SafeIdent has checked it. AutoMigrate writes its DDL with names unquoted, so Postgres stores an entity declared as Widgets under widgets. QuoteFolded("Widgets") gives "widgets", which finds it; QuoteIdent("Widgets") gives "Widgets", which does not. A dotted name folds each part: QuoteFolded("app.UserEvents") gives "app"."userevents". An index or trigger named after a table takes query.QuoteIndexName, which keeps only the folded part after the last dot: Postgres puts an index in its table's schema and refuses a qualified name, so QuoteIndexName("app.UserEvents_idx") gives "userevents_idx". SQLite is the other way round: it takes the schema on the index name and refuses it on the indexed table. query.IndexOn(sqlite, name, table) renders the <index> ON <table> part for either engine. For the index "x_idx" on "main"."events" it gives "main"."x_idx" ON "events" on SQLite and "x_idx" ON "main"."events" on Postgres. query.SQLitePragma("table_info", table) puts the schema on the pragma the same way: PRAGMA "main".table_info("events"). The builders name a table or column bare, the way the DDL does, except a keyword (order, group, user, end, desc, limit, …), which they quote and fold as the migrator's DDL does: every slot that holds a name (From, Join's table, Insert, Update, Delete and Count's table, Select, Columns, Set, Returning and Matching columns, Order and Cursor's column) goes through query.QuoteKeyword:

2 lines
query.Insert("posts").Columns("order", "title").Values(1, "t").Build()
// INSERT INTO posts ("order", title) VALUES ($1, $2)

A slot holding anything but a plain or dotted name (*, an expression) is left as written. A condition you pass to Where or Join's ON is raw SQL, so a keyword column in it is yours to quote: query.QuoteKeyword("order") + " = $1" gives "order" = $1, and query.QuoteKeyword("posts.user") gives posts."user". On Postgres a bare user in a condition is not a syntax error: it reads the session user, so user = $1 silently matches nothing. query.IsKeyword(name) reports whether a name is in the set. A name that is not a keyword comes back unchanged, so a query without one builds byte for byte what it did before v0.96.0.

QuoteIdent keeps the name as given and is for a name read back from the database catalog; the quoteident vet check refuses it anywhere else outside core/query.

A store that creates its own table takes a configured name through QuoteFolded too. Builds before v0.96.0 quoted such names whole, so a "Jobs" table kept its case and "app.Jobs" was one table named app.Jobs. On Postgres, call query.RefuseCaseKeptTables(ctx, db, table) before the CREATE TABLE IF NOT EXISTS: when the older table is still there it returns an error that holds the ALTER TABLE ... RENAME statements to move it, instead of starting an empty table beside it. The framework's stores and batteries make that call.

Common mistakes

  • Trusting include() to eager-load. It only validates. Combine it with the include/eager-loading API to actually fetch the relation.
  • Assuming you must cap limit yourself. The parser rejects any limit() above maxDSLLimit (10,000), so limit(1000000) fails to parse rather than executing. Cap it lower than that if your app wants a smaller ceiling, but the unbounded case is already closed.
  • Building once, sharing across requests. The returned *query.QueryBuilder is stateful. Build per request.