Query (MQL)

MQL (Mapping Query Language) is a SQL-like query language for exploring your Terraform infrastructure across all states.

Basic Syntax

MQL 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:

Column Type Description
address string Full instance address
resource_address string Resource address (without index)
attributes jsonb Resource attributes
dependencies text[] List of dependency addresses
state_id uuid Parent state ID
index_key string Instance index key (for count/for_each)
status string Instance status
schema_version integer Schema version
create_before_destroy boolean Lifecycle setting
deposed string Deposed key if applicable
sensitive_attributes jsonb Sensitive attribute paths
identity jsonb Identity information
identity_schema_version integer Identity schema version
private string Private data

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

states

All Terraform states:

Column Type Description
id uuid State ID
name string State name
group_id uuid Group identifier
workspace string Workspace name
tenant_id uuid Tenant ID
created_at timestamp Creation time
updated_at timestamp Last update time

resources

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

Column Type Description
address string Resource address
type string Resource type (e.g., aws_instance)
name string Resource name
provider string Provider name
module string Module path
mode string Resource mode (managed/data)
state_id uuid Parent state ID

providers

Provider information:

Column Type Description
name string Provider name
state_id uuid Parent state ID

outputs

State outputs:

Column Type Description
name string Output name
value jsonb Output value
type jsonb Output type
sensitive boolean Is sensitive
state_id uuid Parent state ID

transactions

Transaction history:

Column Type Description
id uuid Transaction ID
state string Transaction state
tags jsonb Transaction tags
tenant_id uuid Tenant ID
created_at timestamp Creation time
created_by uuid Creator user ID
completed_at timestamp Completion time
completed_by uuid Completing user ID

tenants

Tenant information:

Column Type Description
id uuid Tenant ID
name string Tenant name
created_at timestamp Creation time

users

User information:

Column Type Description
id uuid User ID
name string User name
type string User type
created_at timestamp Creation time

transaction_logs

Transaction log entries:

Column Type Description
id uuid Log entry ID
tx_id uuid Parent transaction ID
action string Action type (e.g., state_set)
object_type string Type of object affected
state_id uuid Associated state ID
user_id uuid User who performed action
data jsonb Additional action data
created_at timestamp When action occurred

check_results

Terraform check results:

Column Type Description
config_addr string Check configuration address
object_kind string Kind of object checked
state_id uuid Parent state ID
status string Check status

check_entries

Individual check entries:

Column Type Description
config_addr string Check configuration address
object_addr string Object address being checked
state_id uuid Parent state ID
status string Entry status
failure_messages text[] Failure messages if any

Query Examples

List All Resources

SELECT * FROM instances

Filter by Resource Type

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

Count Resources by Type

SELECT r.type, COUNT(*) as count
FROM resources r
GROUP BY r.type
ORDER BY count DESC

Find Resources with Specific Attribute

SELECT i.address, i.attributes->>'instance_type' as instance_type
FROM instances i
JOIN resources r ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type = 'aws_instance'
  AND i.attributes->>'instance_type' = 't3.micro'

Search by Address Pattern

SELECT * FROM instances
WHERE address LIKE '%database%'

Cross-State Query

SELECT s.name as state_name, i.address, r.type
FROM instances i
JOIN resources r ON i.resource_address = r.address AND i.state_id = r.state_id
JOIN states s ON i.state_id = s.id
WHERE r.type = 'aws_rds_cluster'

Module Analysis

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

Operators

Comparison

Operator Description Example
= Equal type = 'aws_instance'
<> Not equal type <> 'null_resource'
< Less than count < 10
> Greater than count > 100
<= Less or equal count <= 50
>= Greater or equal count >= 5

Logical

Operator Description Example
AND Both conditions type = 'aws_instance' AND module IS NOT NULL
OR Either condition type = 'aws_s3_bucket' OR type = 'aws_s3_bucket_policy'
NOT Negate condition NOT type = 'null_resource'

Pattern Matching

Operator Description Example
LIKE Pattern match address LIKE '%prod%'
IN Value in list type IN ('aws_instance', 'aws_ebs_volume')

NULL Handling

Operator Description Example
IS NULL Is null module IS NULL
IS NOT NULL Is not null module IS NOT NULL

JSON Operators

Access JSON attributes using PostgreSQL-style operators:

Arrow Operators

Operator Description Example
-> Get JSON value attributes->'tags'
->> Get JSON as text attributes->>'instance_type'
#>> Get nested path as text attributes#>>'{tags,Name}'

JSON Path Examples

-- Get instance type (join with resources for type filter)
SELECT i.attributes->>'instance_type'
FROM instances i
JOIN resources r ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type = 'aws_instance'

-- Get nested tag value
SELECT i.attributes#>>'{tags,Environment}' as env
FROM instances i
JOIN resources r ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type = 'aws_instance'

-- 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"}'

Aggregations

COUNT

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

With HAVING

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

Joins

Inner Join

SELECT i.address, s.name as state
FROM instances i
INNER JOIN states s ON i.state_id = s.id

Left Join

SELECT s.name, COUNT(i.address) as resources
FROM states s
LEFT JOIN instances i ON s.id = i.state_id
GROUP BY s.name

Ordering and Limits

ORDER BY

SELECT * FROM instances
ORDER BY type ASC, address DESC

LIMIT

SELECT * FROM instances
LIMIT 100

Common Patterns

AWS Resources by Type Prefix

SELECT r.type, COUNT(*) as count
FROM resources r
WHERE r.type LIKE 'aws_%'
GROUP BY r.type
ORDER BY count DESC

Find Untagged Resources

SELECT i.address, r.type
FROM instances i
JOIN resources r ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type LIKE 'aws_%'
  AND (i.attributes->'tags' IS NULL
       OR i.attributes->>'tags' = '{}')

Resources by Workspace

SELECT s.workspace, COUNT(*) as resources
FROM instances i
JOIN states s ON i.state_id = s.id
GROUP BY s.workspace

Security Groups with Open Access

SELECT i.address, i.attributes->>'name' as name
FROM instances i
JOIN resources r ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type = 'aws_security_group'
  AND i.attributes::text LIKE '%0.0.0.0/0%'

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

Error Cause Solution
"Syntax error" Invalid SQL syntax Check query structure
"Unknown column" Column doesn't exist Check column names
"Type mismatch" Wrong data type in comparison Cast values appropriately

Next Steps