Query (SQL)

SQL queries let you explore your Terraform infrastructure across all states.

Basic Syntax

Stategraph SQL follows standard SQL SELECT syntax:

SELECT columns
FROM table
WHERE conditions
ORDER BY column [ASC|DESC]
LIMIT n

Tables

instances

All resource instances across all states:

Note: To query by resource type or provider, query the resources table directly.

states

All Terraform states:

resources

Resource definitions (use this to query by type/provider):

providers

Provider information:

outputs

State outputs:

transactions

Transaction history:

tenants

Tenant information:

users

User information:

transaction_logs

Transaction log entries:

check_results

Terraform check results:

check_entries

Individual check entries:

More tables: Beyond those above, these are also queryable: cost_snapshots and
cost_snapshot_resources (see Cost Analysis), plus hcl,
hcl_refs, files, tfvars, transaction_subgraphs, revision_hashes, and revision_tx_hashes.
Run stategraph sql schema for the authoritative list of tables and columns.

Query Examples

List All Resources

SELECT * FROM instances

Filter by Resource Type

SELECT * FROM resources
WHERE type = 'aws_instance'

Count Resources by Type

SELECT type, COUNT(*) as type_count
FROM resources
GROUP BY type
ORDER BY COUNT(*) DESC

Find Resources with Specific Attribute

SELECT address, attributes->>'instance_type' as instance_type
FROM instances
WHERE attributes->>'instance_type' = 't3.micro'

Search by Address

SELECT * FROM instances
WHERE address = 'module.database.aws_rds_cluster.main'

Cross-State Query

To analyze resources across states, query each table separately. For example, list all states and then query resources within a specific state:

SELECT id, name FROM states

SELECT * FROM resources
WHERE type = 'aws_rds_cluster'

Note: To correlate rows across tables in one query, use a JOIN (see Cross-Table Queries) on a shared column such as state_id.

Module Analysis

SELECT module, COUNT(*) as resources
FROM resources
WHERE module IS NOT NULL
GROUP BY module
ORDER BY resources DESC

Operators

Comparison

Logical

Set Membership

Pattern Matching

Use % to match any sequence of characters and _ to match a single character.

NULL Handling

JSON Operators

Access JSON attributes using PostgreSQL-style operators:

Arrow Operators

JSON Path Examples

-- Get instance type
SELECT address, attributes->>'instance_type' as instance_type
FROM instances
WHERE attributes->>'instance_type' IS NOT NULL

-- Get nested tag value
SELECT address, attributes#>>'{tags,Environment}' as env
FROM instances

-- Filter by tag
SELECT * FROM instances
WHERE attributes->'tags'->>'Environment' = 'production'

JSON Contains

-- Find instances with specific tags
SELECT * FROM instances
WHERE attributes->'tags' @> '{"Environment": "production"}'::jsonb

Aggregations

COUNT

SELECT count(*) FROM instances
SELECT type, count(*) FROM resources GROUP BY type ORDER BY type

With HAVING

SELECT type, count(*) as type_count
FROM resources
GROUP BY type
HAVING count(*) > 10
ORDER BY type

Cross-Table Queries

Stategraph SQL supports INNER, LEFT, and RIGHT joins, so you can correlate data across tables in a single query. Match rows with an ON clause — most tables share a state_id, and instances.resource_address matches resources.address.

-- Join instances to their resource definitions to filter by type
SELECT i.address, r.type, r.provider
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'

-- Count resources by type within each state
SELECT s.name AS state, r.type, COUNT(*) AS type_count
FROM resources AS r
INNER JOIN states AS s ON r.state_id = s.id
GROUP BY s.name, r.type
ORDER BY COUNT(*) DESC

Ordering and Limits

ORDER BY

SELECT * FROM instances
ORDER BY address ASC, status DESC

LIMIT

SELECT * FROM instances
LIMIT 100

Common Patterns

AWS Resources by Type

SELECT type, COUNT(*) as type_count
FROM resources
WHERE type IN ('aws_instance', 'aws_s3_bucket', 'aws_lambda_function', 'aws_rds_cluster')
GROUP BY type
ORDER BY COUNT(*) DESC

Tip: Use IN for a set of exact types, or LIKE for prefix matches such as type LIKE 'aws_%'.

Find Untagged Resources

SELECT address, resource_address
FROM instances
WHERE attributes->'tags' IS NULL
   OR attributes->>'tags' = '{}'

Resources by State

SELECT state_id, COUNT(*) as resources
FROM instances
GROUP BY state_id

Security Groups

SELECT address, attributes->>'name' as name
FROM instances
WHERE resource_address = 'aws_security_group.main'

Note: To filter instances by resource type, join instances to resources on resource_address (see Cross-Table Queries).

Tips

1. Use LIMIT - Always add LIMIT when exploring large datasets
2. Specific columns - Select only needed columns for faster queries
3. Index usage - Filters on type and address are fast
4. JSON paths - Use #>> for nested JSON access
5. Test incrementally - Build complex queries step by step

Error Messages

Next Steps

• Instances - Browse resource instances
• SQL Syntax Reference - Complete grammar reference
• API Reference - Query via API