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, followed by 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. A single-scalar CSV result prints the bare value.

The inner query label is optional for a printing query. It is required for a bound one because 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 (for example, ? on MySQL). 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.

The break Block​

A break stops the run when its condition is true, so a report with nothing to say ends instead of printing empty sections. It takes either sql, with args, evaluated in the database, or expr, a boolean expression evaluated in-process over the script scope. The two are mutually exclusive, and one of them is required. Set message to emit A break stops the run when its condition is true, so a report with nothing to say can simply end instead of printing empty sections. It takes either sql (with args) evaluated in the database, or expr, a boolean expression evaluated in-process over the script scope. The two are mutually exclusive, and one of them is required. Set message to emit a line in place of the script's output, so a stopped run still reports why:

stale.hcl
script "query" "stale_orders" {
  # Bound: the ids feed both the break and the report below.
  query "stale" {
    sql = <<-SQL
      SELECT id FROM orders
      WHERE status = 'pending' AND updated_at < date('now', '-7 day')
    SQL
    rows {
      id = int
    }
  }

  break "all_fresh" {
    expr    = length(query.stale.rows) == 0
    message = "no stale orders"
  }

  query "detail" {
    sql = <<-SQL
      SELECT o.id, u.email, o.updated_at
      FROM orders o JOIN users u ON u.id = o.user_id
      WHERE o.id IN (SELECT value FROM json_each(?))
      ORDER BY o.updated_at
    SQL
    args   = [jsonencode(query.stale.rows[*].id)]
    format = TABLE
  }

  output {
    message = "${length(query.stale.rows)} stale orders"
  }
}

A query script runs in no transaction, so a break undoes nothing .The sections printed before it stay printed, and only the blocks after it are skipped.

The http Block​

An http block calls an external endpoint and binds its response for the blocks after it, so a report can be filtered, enriched, or annotated by data a service owns rather than the database. url, headers, and body interpolate the script scope, including a preceding bound query as query.<name>.rows[*].<col>.

Declare the response shape with response = object({ ... }) to expose its fields as http.<name>.<field>. Extra keys are dropped, and declared fields missing from the response decode as null. A later query binds those fields through args, and an output line can interpolate them. The name label is optional, but a block that declares a response or a check needs one, since both address it as http.<name>.

expect_status asserts the reply, and a nested check { condition, error_message } asserts the decoded body for the 200 that still reports an application-level failure. Like a failing query, a failing http step aborts the run.

Always set expect_status

expect_status is optional and has no default. Without it Atlas never inspects the status, so a 4xx or 5xx counts as a successful step and the blocks after it still run, reporting on whatever an error body decoded to.

For example, this fetches the ids the moderation service currently flags, then reports only those users. method is POST by default, so a read sets it explicitly:

flagged.hcl
variable "flags_endpoint" {
  type = string
}

script "query" "flagged_users" {
  # Ask the service which users are flagged.
  http "flagged" {
    url           = "${var.flags_endpoint}/flagged"
    method        = GET
    expect_status = 200
    response      = object({ ids = list(number) })
  }

  # Report exactly those users, with the ids bound into the query.
  query "detail" {
    sql = <<-SQL
      SELECT id, email, plan FROM users
      WHERE id IN (SELECT value FROM json_each(?))
      ORDER BY id
    SQL
    args   = [jsonencode(http.flagged.ids)]
    format = TABLE
  }

  output {
    message = "reported ${length(http.flagged.ids)} flagged users"
  }
}

A list cannot be bound as a placeholder value directly, so the ids are encoded with jsonencode(...) and unpacked in SQL, exactly as a bound query's rows are. The call itself writes no section to the output; under the streaming report it appears as a step with its duration, and with --quiet, the script prints only the query sections and the output line.

Reporting the result​

A scheduled check can deliver its result rather than print it to Slack, a webhook, or an incident tool:

script "query" "health_report" {
  # A bound query: its result feeds the message below.
  query "counts" {
    sql = <<-SQL
      SELECT
        (SELECT count(*) FROM orders WHERE user_id NOT IN (SELECT id FROM users)) AS orphan_orders,
        (SELECT count(*) FROM users WHERE plan IS NULL) AS users_without_plan
    SQL
    rows {
      orphan_orders      = int
      users_without_plan = int
    }
  }

  http "report" {
    url    = "https://slack.com/api/chat.postMessage"
    method = POST
    headers = {
      "Content-type"  = "application/json"
      "Authorization" = "Bearer ${var.slack_token}"
    }
    body = jsonencode({
      channel = "ABCDEFGHIJK"
      text    = "orphan orders: ${query.counts.rows[0].orphan_orders}"
    })
  }

  output {
    message = "reported ${query.counts.rows[0].orphan_orders} orphan orders"
  }
}

The message reads query.counts.rows[0].<col> because counts declares rows. A printing query's result is serialized to the output and is not in scope. With every query bound, the output block is what gives the script something to emit, per the note above.

The block mirrors the data "http" block. Wrap it in a retry { } block to retry a transient failure with exponential backoff (attempts, min_delay_ms, max_delay_ms), and configure TLS with ca_cert_pem, client_cert_pem, client_key_pem, and insecure. ca_cert_pem and insecure are mutually exclusive, and client_cert_pem and client_key_pem must be set together. request_timeout_ms is not TLS configuration. It bounds the whole request, on plain http:// too.

A query script is not read-only with http

A query script opens no write transaction, but an http block does perform whatever the endpoint does, and POST is the default method. A call that only fetches data for the report should set method = GET; a call that delivers something, such as the Slack message above, is a real side effect that Atlas cannot roll back. For a side effect that belongs to a mutation, use an exec script or a loop instead.

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.