SQL Syntax Reference

Complete grammar reference for Stategraph SQL (Structured Query Language).

Grammar Overview

Stategraph supports a subset of SQL for querying Terraform state data.

query := [cte] SELECT select_list FROM table [join ...] [WHERE expr] [GROUP BY exprs] [HAVING expr] [ORDER BY order_exprs] [LIMIT n] [UNION query]
join  := [INNER | LEFT | RIGHT] JOIN table ON expr

SELECT Statement

Basic Syntax

SELECT columns FROM table

Select All Columns

SELECT * FROM instances

Select Specific Columns

SELECT address, type, provider FROM resources

Column Aliases

SELECT address AS resource_address, type AS resource_type FROM resources

FROM Clause

Single Table

SELECT * FROM instances

Table Aliases

Alias a table with AS, then qualify columns with the alias:

SELECT r.type, r.provider FROM resources AS r

JOINs

Combine tables with INNER JOIN, LEFT JOIN, or RIGHT JOIN. Use an ON clause to match rows:

SELECT i.address, r.type
FROM instances AS i
INNER JOIN resources AS r
  ON i.resource_address = r.address
  AND i.state_id = r.state_id
WHERE r.type = 'aws_instance'

Multiple joins in a single query are supported.

Available Tables

See Query (SQL) for complete column documentation.

WHERE Clause

Basic Filtering

SELECT * FROM resources WHERE type = 'aws_instance'

Multiple Conditions

SELECT * FROM resources
WHERE type = 'aws_instance'
  AND provider = 'registry.terraform.io/hashicorp/aws'

Expressions

Literals

Identifiers

Column and table names:

address
type
instances.address

Operators

Comparison Operators

Logical Operators

Arithmetic Operators

String Operators

NULL Handling

SELECT * FROM resources WHERE module IS NULL
SELECT * FROM resources WHERE module IS NOT NULL

Pattern Matching

IN

SELECT * FROM resources
WHERE type IN ('aws_instance', 'aws_ebs_volume', 'aws_eip')

LIKE / ILIKE

LIKE matches a pattern case-sensitively; ILIKE is case-insensitive. Use % for any sequence of characters and _ for a single character. Both support a negated form (NOT LIKE, NOT ILIKE).

-- Resource types starting with "aws_"
SELECT * FROM resources WHERE type LIKE 'aws_%'

-- Case-insensitive substring match
SELECT * FROM resources WHERE name ILIKE '%prod%'

-- Negated match
SELECT * FROM resources WHERE type NOT LIKE 'null_%'

Type Casting

SELECT count::text FROM summary
SELECT attributes::jsonb FROM instances

Syntax: expression::type

JSON Operators

Access JSON data stored in the attributes column.

Arrow Operators

Examples

-- Get JSON value
SELECT attributes->'tags' FROM instances

-- Get as text
SELECT attributes->>'instance_type' FROM instances

-- Nested path
SELECT attributes#>>'{tags,Name}' FROM instances

JSON Contains

-- Check if JSON contains value
SELECT * FROM instances
WHERE attributes->'tags' @> '{"Environment": "production"}'

Aggregation

COUNT

SELECT COUNT(*) FROM instances
SELECT COUNT(*) FROM resources

GROUP BY

SELECT type, COUNT(*) AS count
FROM resources
GROUP BY type

HAVING

SELECT type, COUNT(*) AS count
FROM resources
GROUP BY type
HAVING COUNT(*) > 10

Ordering

ORDER BY

SELECT * FROM resources ORDER BY type
SELECT * FROM resources ORDER BY type ASC
SELECT * FROM resources ORDER BY type DESC

Multiple Columns

SELECT * FROM resources ORDER BY type ASC, address DESC

Limiting Results

LIMIT

SELECT * FROM instances LIMIT 100

Common Table Expressions (CTEs)

WITH Clause

WITH aws_resources AS (
  SELECT * FROM resources WHERE provider = 'registry.terraform.io/hashicorp/aws'
)
SELECT type, COUNT(*) FROM aws_resources GROUP BY type

Multiple CTEs

WITH
  aws AS (SELECT * FROM resources WHERE provider = 'registry.terraform.io/hashicorp/aws'),
  gcp AS (SELECT * FROM resources WHERE provider = 'registry.terraform.io/hashicorp/google')
SELECT 'aws' AS cloud, COUNT(*) FROM aws
UNION
SELECT 'gcp' AS cloud, COUNT(*) FROM gcp

Functions

Built-in Functions

Note: Function names are case-sensitive and must be lowercase.

Operator Precedence

From highest to lowest:

1. :: (type cast)
2. ->, ->>, #>> (JSON)
3. - (unary negation)
4. *, /
5. +, -
6. \|\| (concatenation)
7. =, <>, <, >, <=, >=
8. IS, IS NOT, IS DISTINCT FROM, IS NOT DISTINCT FROM
9. IN, LIKE, ILIKE
10. NOT
11. AND
12. OR

Use parentheses to override precedence:

SELECT * FROM instances WHERE (a = 1 OR b = 2) AND c = 3

Reserved Keywords

AND, AS, ASC, BY, COUNT, DESC, DISTINCT, EXISTS, FALSE, FROM, GROUP,
HAVING, ILIKE, IN, INNER, IS, JOIN, LEFT, LIKE, LIMIT, MATERIALIZED, NOT,
NULL, ON, OR, ORDER, RIGHT, SELECT, TRUE, UNION, WHERE, WITH

Keywords are case-insensitive.

Comments

Stategraph SQL does not currently support comments in queries.

Query Examples

Basic Queries

-- All instances
SELECT * FROM instances

-- All resources
SELECT * FROM resources

-- Specific type (use resources table)
SELECT * FROM resources WHERE type = 'aws_instance'

-- Multiple conditions
SELECT * FROM resources
WHERE type = 'aws_instance'
  AND module IS NOT NULL

Aggregations

-- Count by type
SELECT type, COUNT(*) AS count
FROM resources
GROUP BY type
ORDER BY count DESC

-- With minimum count
SELECT type, COUNT(*) AS count
FROM resources
GROUP BY type
HAVING COUNT(*) > 5
ORDER BY count DESC

JSON Queries

-- Get specific attribute
SELECT
  address,
  attributes->>'instance_type' AS instance_type
FROM instances

-- Filter by attribute
SELECT address
FROM instances
WHERE attributes->>'instance_type' = 't3.micro'

-- Check tags
SELECT address
FROM instances
WHERE attributes->'tags'->>'Environment' = 'production'

Complex Queries

-- Resources by type with counts
SELECT type, COUNT(*) AS count
FROM resources
WHERE provider = 'registry.terraform.io/hashicorp/aws'
GROUP BY type
ORDER BY count DESC

Error Handling

Syntax Errors

Invalid syntax returns an error with position information:

Error at line 1, column 15: Expected 'FROM'

Type Errors

Type mismatches return descriptive errors:

Error: Cannot compare string with integer

Unknown Columns

References to non-existent columns:

Error: Unknown column 'foo'