SQL Commands

The stategraph sql command group provides access to SQL (Structured Query Language) for querying your infrastructure from the command line.

Commands

Command Description
stategraph sql query Execute a SQL query
stategraph sql schema Get the SQL schema

stategraph sql query

Execute a SQL query against your infrastructure.

stategraph sql query <query>

Arguments

Argument Required Description
<query> Yes SQL query string

Example

stategraph sql query "SELECT type, count(*) FROM resources GROUP BY type"

Output (JSON):

[
  { "type": "aws_instance", "count": 20 },
  { "type": "aws_security_group", "count": 15 },
  { "type": "aws_subnet", "count": 6 }
]

More Queries

# Count total instances
stategraph sql query "SELECT count(*) FROM instances"

# List all resource types with counts
stategraph sql query "SELECT type, count(*) as count FROM resources GROUP BY type ORDER BY count DESC"

# Find resources by type
stategraph sql query "SELECT address FROM instances WHERE type = 'aws_instance'"

SQL Syntax Notes

Stategraph SQL supports a subset of standard SQL:

  • JOINs work — INNER, LEFT, and RIGHT joins with ON conditions
  • Table aliases work — FROM resources AS r lets you write r.type
  • Column aliases work — count(*) as total is supported
  • CTEs work — WITH name AS (...) clauses, including UNION
  • ORDER BY works — ORDER BY type ASC is supported
  • LIKE / ILIKE work — pattern matching with % and _ wildcards, plus NOT LIKE / NOT ILIKE
  • No comments — -- and /* */ are not supported in queries

stategraph sql schema

Get the SQL schema for understanding available tables and columns.

stategraph sql schema

Output (JSON) — shows all tables with column names and their PostgreSQL types:

{
  "tables": {
    "instances": {
      "columns": {
        "address": { "type": "text" },
        "attributes": { "type": "jsonb" },
        "dependencies": { "type": "text[]" },
        "provider": { "type": "text" },
        "resource_address": { "type": "text" },
        "state_id": { "type": "uuid" }
      }
    },
    "resources": {
      "columns": {
        "address": { "type": "text" },
        "module": { "type": "text" },
        "state_id": { "type": "uuid" },
        "type": { "type": "text" }
      }
    },
    "states": {
      "columns": {
        "id": { "type": "uuid" },
        "name": { "type": "text" },
        "group_id": { "type": "text" },
        "workspace": { "type": "text" }
      }
    }
  }
}

Run stategraph sql schema to see the full list of tables and columns — there are additional tables beyond what's shown here.

Scripting Examples

Export query results to CSV

stategraph sql query "SELECT address, type FROM resources" | \
  jq -r '.[] | [.address, .type] | @csv' > resources.csv

Count resources and format output

stategraph sql query "SELECT type, count(*) FROM resources GROUP BY type" | \
  jq -r '.[] | "\(.type): \(.count)"'

Generate report

#!/bin/bash
echo "Infrastructure Report"
echo "===================="
echo ""
echo "Total resources:"
stategraph sql query "SELECT count(*) FROM instances" | jq -r '.[0].count'
echo ""
echo "By type:"
stategraph sql query "SELECT type, count(*) FROM resources GROUP BY type" | \
  jq -r '.[] | "  \(.type): \(.count)"'

Error Handling

Syntax Errors

stategraph sql query "SELEC * FROM instances"
# Exit code: 2
# Output: QUERY_ERR
# Error ((None, "SELEC * FROM instances", "0"))

Unknown Columns

stategraph sql query "SELECT foo FROM instances"
# Exit code: 2
# Output: QUERY_ERR
# column "foo" does not exist

Tips

  1. Quote queries — Always wrap queries in quotes to prevent shell interpretation
  2. Use jq — Pipe output to jq for formatting and filtering
  3. Test incrementally — Build complex queries step by step
  4. Check schema — Use stategraph sql schema to see available columns
  5. Qualify columns when joining — Use a table name or alias (r.type) when querying multiple tables

Next Steps