Skip to main content

Queries and Reports: script query

A script "query" runs one or more SELECT statements against a database and prints what they return. You control what that output looks like: each query serializes as CSV or an aligned table, an output block composes lines of your own from the results, and a mask block redacts columns before they are printed. It is the read-only counterpart to script "exec": a query script reads and prints, and does not open a write transaction.

Use a query script for reads that should be kept as code: recurring reports, health checks, exports with masked columns, and reads that capture a set of keys for a later query. Because the script is code, the same report is versioned, reviewed, and runs identically in every environment.

A query script wraps one or more inner query blocks. Each block runs in the order it is written and prints its result as a section of the output. A block that declares rows prints nothing and feeds its result to a later query.

Examples​

Common reads as code. Each tab is a complete script, and each Example execution block shows an end-to-end run on SQLite.

This prints an aggregate as a table, then details for the top spenders, feeding one query's result into the next:

report.hcl
script "query" "revenue_report" {
  # Section 1: an aggregate, printed as an aligned table.
  query "by_plan" {
    sql = <<-SQL
      SELECT u.plan, count(DISTINCT u.id) AS users, coalesce(sum(o.total), 0) AS revenue
      FROM users u
      LEFT JOIN orders o ON o.user_id = u.id
      GROUP BY u.plan
      ORDER BY revenue DESC
    SQL
    format = TABLE
  }

  # A bound query: prints nothing, captures the top-spender ids for the next block.
  query "top_ids" {
    sql = "SELECT user_id FROM orders GROUP BY user_id ORDER BY sum(total) DESC LIMIT 2"
    rows {
      user_id = int
    }
  }

  # Section 2: details for exactly those ids, as CSV.
  query "top_detail" {
    sql = <<-SQL
      SELECT u.email, sum(o.total) AS spent
      FROM users u
      JOIN orders o ON o.user_id = u.id
      WHERE u.id IN (SELECT value FROM json_each(?))
      GROUP BY u.id
      ORDER BY spent DESC
    SQL
    args   = [jsonencode(query.top_ids.rows[*].user_id)]
    format = CSV
  }

  output {
    message = "top ${length(query.top_ids.rows)} spenders shown above"
  }
}
Example execution

The example is self-contained on SQLite. Set up a database with sqlite3 report.db < setup.sql:

setup.sql
CREATE TABLE users  (id INTEGER PRIMARY KEY, email TEXT, plan TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total INTEGER);
INSERT INTO users VALUES
  (1,'ada@example.com','pro'),(2,'bob@example.com','free'),
  (3,'cara@example.com','pro'),(4,'dan@example.com','free'),(5,'eve@example.com','pro');
INSERT INTO orders VALUES
  (10,1,50),(11,1,70),(12,3,200),(13,5,30),(14,2,10),(15,3,20);

Run the script:

atlas script query --url "sqlite://report.db" --file "file://report.hcl" --run revenue_report --quiet
 plan | users | revenue
------+-------+---------
 pro  | 3     | 370
 free | 2     | 10

cara@example.com,220
ada@example.com,120

top 2 spenders shown above

Each printing query writes its result as a section of the output, in source order, separated by blank lines. by_plan renders as an aligned table, top_ids is a bound query that prints nothing and captures the two top-spender ids, and top_detail binds those ids (JSON-encoded) and prints their emails and totals as CSV.

Output Format, Arguments, and Sections​

Each printing query serializes its result in its own format. TABLE is the default and renders an aligned, human-readable grid. CSV emits one row per line, comma-separated, with no header, and a single-scalar CSV result prints the bare value. The inner query label is optional for a printing query, and required for a bound one, since later blocks address it as query.<name>.rows.

The args attribute is a positional list bound to the placeholders in sql, in order. Values can be literals or var.<name> references, and the placeholder marker is driver-native, ? on MySQL for example. See SQL placeholders. Always bind through args, and never string-interpolate untrusted values into the SQL:

script.hcl
script "query" "user_email" {
  query "by_id" {
    sql    = "SELECT email FROM users WHERE id = ?"
    args   = [2]
    format = CSV
  }
}

Against the report.db from the revenue report, this prints the matching email:

atlas script query --url "sqlite://report.db" --file "file://script.hcl" --run user_email --quiet
bob@example.com

A script may hold several inner query blocks. Each runs in source order and writes its result as a section of the output, with sections separated by a single blank line.

The commands above pass --quiet to print the result on its own. Without it, Atlas wraps the same result in the streaming report, adding a header per block and a closing summary.

The rows Block​

A rows block turns a query into a bound query: it reads only the declared columns, emits nothing to the output, and binds the result into scope for the blocks after it.

Use it when a query produces a set of keys for a later query to filter on. For example, the script below captures the ids of the pro-plan users, then reports only their orders:

pro_orders.hcl
script "query" "pro_orders" {
  query "pro_ids" {
    sql = "SELECT id FROM users WHERE plan = 'pro'"
    rows {
      id = int
    }
  }
  query "orders" {
    sql = <<-SQL
      SELECT user_id, count(*) AS orders
      FROM orders
      WHERE user_id IN (SELECT value FROM json_each(?))
      GROUP BY user_id
      ORDER BY user_id
    SQL
    args   = [jsonencode(query.pro_ids.rows[*].id)]
    format = CSV
  }
}

Later blocks reference the bound result in three forms:

  • query.<name>.rows[*].<col> - the column as a list
  • query.<name>.rows[N].<col> - one row's column
  • length(query.<name>.rows) - the row count

A list cannot be bound as a placeholder value directly. Encode it with jsonencode(...) and unpack it in SQL, as the example above does. Because a bound query emits nothing, rows is mutually exclusive with format and mask, which only apply to emitted output.

note

A query script where every query declares rows and no output block is defined has nothing to emit and is rejected. A bound query must feed something that produces output.

Masking Result Columns​

A mask {} block on a query (or a script-level default) redacts result columns before the result is serialized, applied to the bytes that actually leave Atlas. Use it when a query returns columns such as email, ssn, phone, or a *_enc column whose values should not appear verbatim in a report or an export. For example, the script below redacts the email column in a CSV report:

script "query" "audit" {
  query "users" {
    sql    = "SELECT id, email FROM users ORDER BY id"
    format = CSV
    mask {
      columns = ["email"]
      method  = REDACT
    }
  }
}

Masking has its own methods (REDACT, PARTIAL, HASH, REPLACE), glob column matching, scopes, and reusable named masks. See Masking Sensitive Output for the full reference.

Masking is post-processing

Masking runs on already-fetched rows, not as a query-time filter: the full value is read from the database before it is redacted. It protects the serialized output, not the data at rest.

Testing​

Query scripts are tested with the Atlas testing framework: a script "query" command inside a test block runs the scripts matching run and compares what they print with output or match. An as block runs the same script under a reduced role or login to test privileges. See Testing Data Scripts for the full reference.