tugql

TugQL YAML and TugQL text authoring

The existing TugQL Serialize and Deserialize APIs provide a 1:1, human-readable YAML serialization of dalgo's dal.StructuredQuery for the core relational read-only subset. TugQL YAML remains plain YAML over the existing dal query model and uses the standard gopkg.in/yaml.v3 library.

The TugQL text form is an additive text-authoring adapter with a versioned TugQLDocument/TugQLTree model. ParseTugQL parses source, ResolveTugQL validates it against caller-authorized schemas and lowers supported constructs into the same dal.StructuredQuery, and FormatTugQL formats the source. It does not replace or change the TugQL YAML Serialize/Deserialize APIs. The TugQL grammar, supported resolution profile, and approval record are in spec/features/tugql/README.md.

package main

import (
	"fmt"

	"github.com/dal-go/dalgo/tugql"
)

func main() {
	doc, diagnostics := tugql.ParseTugQL("from Invoice as i\nselect i.Id\n")
	if len(diagnostics) != 0 {
		fmt.Println(diagnostics)
		return
	}
	context := tugql.TugQLResolveContext{
		AuthorizedSchemas: []tugql.TugQLAuthorizedSchema{{
			Version: "schema-r1",
			Tables: []tugql.TugQLTable{{
				Name: "Invoice",
				Fields: []tugql.TugQLField{{Name: "Id", Type: "integer", Authorized: true}},
			}},
		}},
	}
	resolved, diagnostics := tugql.ResolveTugQL(doc, context)
	if len(diagnostics) != 0 {
		fmt.Println(diagnostics)
		return
	}
	fmt.Printf("resolved %d output column(s)\n", len(resolved.Columns))
}

TugQL syntax

TugQL puts parameters first and the optional projection last. This example combines a named CTE with a correlated scalar subquery:

parameters (
  @CustomerId integer required
)
with Invoices as (
  from Invoice as i -- billing records
  where i.CustomerId = @CustomerId
  select (
    i.InvoiceId
    i.CustomerId
  )
)
from Invoices as i
select (
  i.InvoiceId as ID
  CustomerName as (
    from Customer as c
    where c.CustomerId = i.CustomerId
    select c.LastName
  )
)

Use one parameter or multiline projection item per line, without commas. Compact projections remain available: select i.InvoiceId, i.CustomerId. Block openers stay on their header line and closers have their own aligned line. Indent each level with two spaces or one tab, consistently within the query. Reserved words must be consistently lowercase or uppercase; identifiers retain their spelling. as is mandatory for aliases and CTEs. Ordinary fields use expression as Alias; scalar subqueries put the result name before as (.

Saved queries use with Saved from './saved.tql', with an indented using ( block for explicit @Parameter = @CallerParameter or scalar literal mappings. The caller supplies project-relative contents pinned to its project revision. An omitted JOIN on, or on CustomerId, resolves only when caller-authorized relationship metadata identifies exactly one complete matching relationship. An omitted select expands authorized fields in order and merges guaranteed equal INNER-join keys while preserving both fields' lineage.

A from or join source is a dotted path (sales.Invoice) or a slash path (spaces/{SpaceID}/ext/datatug/projects); one path may not mix . and /. The last segment is the collection name and the earlier ones are its schema (spaces/{SpaceID}/ext/datatug). A segment is a word, a non-empty quoted identifier without {, } or /, or (in a slash path only) a whole {name} substitution kept literally in the tree. A clause keyword used as a segment must be quoted (spaces/"from"/x). / must touch both neighbouring segments, and a // touching a path segment is an empty_path_segment error rather than a comment (from users // x is still a comment). A schema containing / prints back in slash form. The shared parser vectors are in testdata/slash-paths/cases.json, listed in testdata/slash-paths/manifest.sha256. A tree segment the text form cannot spell fails PrintTugQL with unsupported_print.

WHERE filters source rows before grouping; HAVING filters completed groups after GROUP BY. The clause order is FROM, optional WHERE, optional GROUP BY, optional HAVING, optional ORDER BY and LIMIT, then optional final SELECT:

parameters (
  @MinInvoices integer default 2
)
from Invoice as i
where i.InvoiceId > 1
group by i.CustomerId
having COUNT(*) >= @MinInvoices
order by i.CustomerId asc
select i.CustomerId, COUNT(*) as InvoiceCount

ResolveTugQL binds the parameter default, validates the grouping, and lowers the predicate into the existing dal.StructuredQuery. The generic DALgo executor applies WHERE to invoice rows, computes each customer's COUNT(*), keeps groups whose count is at least the threshold, then orders and projects the result. This count example avoids assumptions about exact money arithmetic.

Cross-database sources

One query may read several databases. An optional root databases ( block, before parameters, declares one alias per line as alias or alias = '<target>'; a from or join source at any level (main query, CTE, scalar subquery) is qualified as alias::path:

databases (
  sales = 'datatug://chinook/sales?env=prod'
  geo
)
parameters (
  @MinSales integer default 1000
)
with BigInvoices as (
  from sales::Invoice as i
  where i.Total > 10
  select i.InvoiceId, i.BillingCountry, i.Total
)
from BigInvoices as b
join geo::Country as c
  on b.BillingCountry = c.Name
group by c.Name
having SUM(b.Total) >= @MinSales
select (
  c.Name as Country
  SUM(b.Total) as TotalSales
)

An alias is an unquoted word that is not a reserved keyword, unique case-insensitively; a qualifier names it exactly, with :: touching both sides. A target is a non-empty single-quoted string, opaque to DALgo (it must be quoted because // starts a comment); an alias without a target is bound by the caller. A target carrying a password fails with database_credentials (the message names only the alias). The check is textual, not URL parsing:

1. at the start of the target and right after every ://, take the text that follows; when it contains @ and the part before its first @ has no /, that part is user information and must not contain : (postgres://u:secret@host/db, u:secret@tcp(host:3306)/db, postgres://u:secret@host/db?redirect=http://x);
2. password, pwd or passwd, in any case, then optional spaces or tabs and =, must not appear at the start of the target or right after ;, &, ?, ,, a space or a tab (Server=h;Password=x, Server=h,Password=x, postgres://h/db?password=x).

postgres://reader@host/db and sqlite:/data/a@b.db are accepted.

An unqualified source reads the caller's default database. When the document declares databases, it matches only authorized schemas whose database is empty; without declarations it matches any database, as before. dal.ExecuteFederatedQuery requires every source to be qualified (an unqualified one fails closed with "has no database"), so a caller that wants a default database in a federated query binds it as an alias. The tree carries databases: [{name, target?}] (TugQLTree.Databases) and each qualified source's database, which resolution matches only against authorized schemas of that database and lowers to dal.NewDatabaseCollectionRef; dal.ExecuteFederatedQuery then routes each source by alias through the caller's DatabaseResolver. A join between sources of different databases must state its on: relationship metadata names no database, so an omitted on there is cross_database_relationship.

Other diagnostics: invalid_databases and unclosed_databases (block shape), invalid_database (declaration), invalid_database_qualifier (spaces around :: or a quoted alias), duplicate_database, undeclared_database (on the qualifier), unused_database (on the declaration), and nested_databases for a block anywhere but the start of the root document, including an imported saved query. A caller-built tree with no declarations keeps its meaning for ResolveTugQL, but PrintTugQL requires every qualified source to be declared. The shared Go/TypeScript vectors are testdata/tugql/v1/cross-database.json.

Comments

In the text form -- and // each start a comment that runs to the end of the line. They are recognised only outside string literals and quoted identifiers, so 'https://example.com/x' and "a//b" contain no comment. A // touching a from/join path segment is an error, not a comment (see above). # is not a comment in the text form. FormatTugQL keeps every comment and the author's marker.

Comments are carried on the parsed tree (TugQLTree.Comments), not on dal.StructuredQuery, and never influence resolution or generated SQL. SQL emitters must not write //; a comment an emitter carries is written as --.

The structured YAML form uses YAML # comments:

data, diagnostics := tugql.TugQLToYAML("-- invoices\nfrom Invoice // main table\nselect Id")
// query:
//     # invoices
//     from: # main table
//         name: Invoice
//     columns:
//         - field: Id
text, diagnostics := tugql.YAMLToTugQL(data)
// -- invoices
// from Invoice -- main table
// select Id

Both markers become #; # becomes --. A // comment therefore comes back as -- after a text, YAML, text round trip. This is the one documented and accepted loss; the comment text and its placement are kept. A comment with no clean anchor (inside a multi-line PARAMETERS, WITH or SELECT block, or on a YAML node that is not a clause) becomes a leading comment of the nearest following clause, and a comment after the last clause becomes a document-end comment. A comment is never dropped. Blank lines between comments of one group are kept; a blank line before the first comment or between the last comment and its clause is not. The text after the marker loses one leading space and trailing whitespace; --x and -- x carry the same text.

The anchors are databases, parameters, definitions[i], query.from, query.from.joins[k], query.from.joins[k].on[j], query.where, query.groupBy, query.having, query.orderBy, query.limit and query.columns.

YAMLToTugQL and PrintTugQL print the canonical layout (lowercase keywords, two-space indentation, one-line SELECT list) and fail closed with a diagnostic for tree features that have no text spelling (for example offset, money, DISTINCT aggregates, IN/EXISTS conditions, negative literals).

The shared Go/TypeScript conformance vectors are testdata/tugql/v1/comments.json, listed in testdata/tugql/v1/manifest.sha256.

Resolution limits

TugQL resolution uses a 128-level structural-depth limit and a 5,000-node semantic budget. The aggregate budget counts the authored root document, each parsed pinned import occurrence once (repeated imports count once per occurrence), and any positive node-count growth from the authored root query to the final expanded root query. Each expanded CTE, import, and scalar-query body is also checked independently against 5,000 canonical nodes and 128 levels before another recursive walk or copy. Version 1 validates all declared definitions eagerly, including unused ones, and caps import expansion at 128 documents.

The sections below document the TugQL YAML mapping and behavior.

data, err := tugql.Serialize(q)   // dal.StructuredQuery -> TugQL YAML
q2, err := tugql.Deserialize(data) // TugQL YAML -> dal.StructuredQuery
tugql.Equal(q, q2)                 // structural equality over the covered surface

This package imports dal but adds no YAML dependency to dal.

Covered subset

TugQL represents a recursive From relation tree over root CollectionRef (Parent() == nil), Where, GroupBy, Having, OrderBy, Limit/Offset, and the final Columns projection. Expressions include fields, constants, arrays, parameters, arithmetic, aggregate functions and COUNT(*). Anything outside this subset — CollectionGroupRef / parented CollectionRef, a cursor (StartFrom), or an operator outside the in-scope set — is rejected by Serialize with a descriptive error rather than silently dropped.

Node → YAML mapping

The optional money policy applies to federated streaming aggregates. Amounts are canonical decimal strings or whole integers; fractional binary floats and amounts with more digits than minorUnitScale are rejected. SUM accumulates integer minor units and returns decimal text. AVG and binary per-capita division return decimal text rounded half-even to divisionScale; division by zero errors. Existing queries keep their numeric behavior. The strongo/decimal cents type cannot serve as this accumulator because its int64 range excludes large sums and its current parser passes through binary floating point. | FieldRef | { field: <name>, source?: <alias> } | | Constant | { value: <scalar> } (inline string, bool, int or float) | | Array | { values: [ <scalar>, ... ] } (inline, for In membership) | | Param | { param: <name> } — a runtime parameter ($name in query text), substituted with a constant or array before execution; names are dotted identifiers such as currentUser or principal.roles | | aggregate | { aggregate: { function: count|sum|avg|min|max|first|last, distinct?: true, args: [<expr>] } } | | arithmetic | { binary: { op: +|-|*|/, left: <expr>, right: <expr> } } | | COUNT(*) argument | { star: true } | | Operator | the dal.Operator string itself: ==, In, >, >=, <, <= | | Limit / Offset | limit: <int> / offset: <int> (omitted when zero) |

An expression node sets exactly one discriminator. Aggregate arguments are expressions, so SUM(quantity * unit_price) needs no later AST redesign. Qualified fields keep the source structural, for example {field: country, source: c}; the source is never flattened into a dotted field name.

Null tests

isNull and isNotNull take one expression (usually a field) and are true when it is, respectively is not, null. They are valid wherever a condition is: in where, in having, inside and / or groups, and in a nested query's conditions; they are not valid in a join's on list, which stays equality only.

from: {name: Chat, alias: c, joins: [{type: left, from: {name: Invoice, alias: i}, on: [{left: {field: id, source: c}, op: '==', right: {field: chat, source: i}}]}]}
where:
  and:
    - isNull: {field: Company, source: c}      # no company, or the column is absent
    - isNotNull: {field: chat, source: i}       # ...and the chat has an invoice
columns: [{field: id, source: c}]

from: {name: sales}
groupBy: [{field: category}]
having:
  isNotNull: {aggregate: {function: max, args: [{field: amount}]}}
columns: [{field: category}]

A field missing from a document counts as null, like an explicit null or a nil pointer. A null test is never unknown, so or over it behaves as two-valued logic. The operand is a field, a literal, arithmetic over those, or a scalar subquery; an aggregate is allowed only in having, and a values list, star and param are rejected at parse (query_shape at where.isNull: ...). A document that sets two condition forms (isNull with isNotNull, and, op, ...) is rejected as mixing forms.

What op: == with {value: null} means, and why it is not enough. It does not mean one thing; it depends on where the condition is evaluated:

So where: Company == null returns the null rows from one table and none once the query is joined, while having treats null as a value. That where versus having inconsistency predates null tests and this change leaves it alone; isNull / isNotNull are the one spelling whose meaning is the same in every row of that table. The serializer never rewrites an existing == null comparison, and != does not exist in TugQL, so isNotNull is the only way to ask for non-null values. Rendered as SQL a null test is x IS NULL / x IS NOT NULL.

Which engines evaluate it. The generic executor (joins, nested queries, aggregation, HAVING) evaluates null tests whatever adapter supplies the rows. An adapter that runs a query itself, a single-source query or one it claims natively (a native join or aggregation), must evaluate or translate them: the in-memory adapter (dalgo2memory) does, and every other adapter currently rejects the query with an "unsupported condition" error until it implements them (an adapter must fail rather than ignore a condition it cannot translate). The TypeScript engine parses a bare single-source query with a null test into its relation model so the same document runs there; see dalgo-js's README.

A misspelt field is not an error in Go unless the executor knows the schema. A record that lacks a field and a field that does not exist look the same, so isNull: {field: compnay} matches every row, and isNotNull none. When the backend reports its fields (dal.JoinFieldsProvider) the executor rejects an unknown field before reading a row (join_field), the way it does for a comparison; the TypeScript parser always has a schema and rejects it at parse. Without field metadata, check field names before running a null test.

A null list element is dropped, not rejected. and: [{isNull: ...}, null] reads as and: [{isNull: ...}], because the YAML decoder skips a null element of a list of mappings (this predates null tests and applies to every condition). The TypeScript parser rejects it.

Not in policies. Access policy predicates (access.DocumentCondition, condeval) keep the comparison and group subset and reject a null test: there a missing field never matches (layered-ACL acceptance V02), and isNull would make it match. The query engine reads a missing field as null, as it already does for every other operator.

Recursive joins

Every relation node may contain ordered joins. A join's from is another relation node, on contains one or more == predicates, and every ON operand is a qualified field reference. inner is the default; left is the other supported type. The serializer emits alias and ==; deserialization also accepts as and eq and normalizes them on output.

from:
  name: Invoice
  alias: i
  joins:
    - from:
        name: Customer
        alias: c
        joins:
          - type: left
            from: {name: Employee, alias: e}
            on:
              - left: {field: SupportRepId, source: c}
                op: '=='
                right: {field: EmployeeId, source: e}
            hints:
              algorithms: [nestedLoop, hash]
      on:
        - left: {field: CustomerId, source: i}
          op: '=='
          right: {field: CustomerId, source: c}
      hints:
        algorithms: [merge, hash]

Each JOIN can independently request an ordered list of hash, merge, lookup, batchedLookup, and nestedLoop. Lists must be nonempty and contain distinct, case-sensitive names. The generic executor currently selects hash for a direct cross-side key and nestedLoop for bounded candidate evaluation. It skips unavailable algorithms and uses its ordinary strategy when no hint applies. A preferred nestedLoop bypasses an available hash index and can hit the candidate bound after up to O(N × M) comparisons for N left and M right rows. Hints never change successful query results or their order.

Validation reports stable JOIN category and zero-based path pairs, such as join_scope at from.joins[1].on[0]. It checks relation shape, inner/left, qualified equality predicates, duplicate aliases, forward references, lexical scope, and cyclic in-memory relation trees. Field existence and key runtime types require schema or execution data and are validated by later layers.

Aggregation

TugQL keeps projection at the end of the YAML pipeline:

from:
  name: orders
where:
  op: ==
  left: {field: status}
  right: {value: paid}
groupBy:
  - field: country
having:
  op: '>'
  left: {field: revenue}
  right: {value: 10000}
orderBy:
  - field: revenue
    desc: true
columns:
  - field: country
  - aggregate:
      function: count
      args: [{star: true}]
    as: orders
  - aggregate:
      function: count
      distinct: true
      args: [{field: customer_id}]
    as: customers
  - aggregate:
      function: sum
      args: [{field: total}]
    as: revenue

With groupBy and no columns, grouping expressions are selected implicitly. Without groupBy, aggregate columns operate on one implicit group. WHERE filters rows; HAVING filters groups; requested ordering, offset and limit apply to aggregate rows.

COUNT(*) counts rows. Other aggregates ignore nulls except FIRST/LAST, where null is a legitimate first/last value. Empty ungrouped input returns one row (COUNT(*) = 0, other aggregates null); empty explicit grouping returns no rows. SUM/AVG use finite float64 accumulation and COUNT returns int64. Arithmetic normalizes numeric operands to float64; non-numeric operands and division by zero evaluate to null. FIRST/LAST require a provider-declared stable input order; aggregate-local ordering is a future extension.

Negative projection

A wildcard column item may exclude one or more unqualified column names. The unqualified form selects every available column except the requested names:

from:
  name: customers
columns:
  - wildcard:
      exclude:
        - email
        - password_hash

When the source has an alias, source scopes the wildcard while the names in exclude remain unqualified:

from:
  name: customers
  alias: c
columns:
  - wildcard:
      source: c
      exclude:
        - email

An excluded name that is absent from the result schema is ignored. Duplicate names are harmless, and removing columns preserves the relative order of every remaining column. This makes defensive requests such as excluding password, password_hash, secret, and api_key useful without prior schema discovery. It is a projection convenience, not a security boundary; access control and sensitive-data policy remain separate concerns. The AST retains the requested exclusion list so future response metadata can distinguish matched and unmatched exclusions without changing query semantics.

Document shape

A TugQL YAML document has this canonical key order (top-level keys are omitted when empty/zero, except from which is required):

from:
  name: users
where:
  and:
    - op: '>='
      left:
        field: age
      right:
        value: 18
    - or:
        - op: In
          left:
            field: status
          right:
            values:
              - active
              - pending
        - op: ==
          left:
            field: country
          right:
            value: US
orderBy:
  - field: name
  - field: age
    desc: true
limit: 10
offset: 20
columns:
  - field: name
  - field: age
    as: years

For a schema-qualified source, schema is a separate optional identifier:

from:
  schema: main
  name: Customer
limit: 50

The same shape represents SQL Server dbo.Customer by setting schema: dbo. Schema values are not special-cased. SQL adapters must quote the schema and relation as separate identifier segments, such as "main"."Customer" for SQLite or [dbo].[Customer] for SQL Server. Non-SQL adapters that cannot represent a schema-qualified source must reject it explicitly; they must not silently flatten the two segments into a dotted collection name.

DALgo access policies likewise do not collapse a qualified source onto the ordinary root collection path. Secured sessions classify it as an opaque query. A policy names such a source with a table rule (access.TableScope); a policy with no table rule needs an explicit OpaqueQueryScope rule for it.

Round-trip guarantees

• Structural — Deserialize(Serialize(q)) reconstructs a StructuredQuery structurally equal to q across From, Where, GroupBy, Having, OrderBy, Limit/Offset, and Columns (see tugql.Equal).
• Canonical — Serialize emits a canonical document (stable key order and 2-space indentation). Serialize(Deserialize(d)) normalizes accepted input aliases (as to alias, eq to ==) and omitted INNER types, then remains byte-identical on later round trips.

Errors

Deserialize returns a descriptive error and no partially-populated query on malformed or schema-invalid input: unknown keys, wrong value types, a missing required from.name, an unknown operator, a comparison missing left/right, an expression that does not select exactly one expression form, or a condition that mixes the comparison and group forms.

Published artifacts

The package also publishes TugQL as a tool-validatable artifact: a JSON Schema (draft 2020-12) generated from the Go types — schema/schema.json (canonical $id https://dal-go.github.io/tugql/schema.json) and schema/schema.yaml — a set of example documents under examples/, and a styled index page, served at https://dal-go.github.io/tugql/.