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, query the resources table directly.

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 * FROM resources
WHERE type = 'aws_instance'

Count Resources by Type

SELECT type, COUNT(*) as 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: Cross-table JOINs are not yet supported in MQL CLI. Query each table individually and correlate results using shared columns like state_id.

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'

Set Membership

Operator Description Example
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
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"}'

Aggregations

COUNT

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

With HAVING

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

Cross-Table Queries

JOINs are not yet supported in MQL CLI. To correlate data across tables, query each table separately and use shared columns like state_id to match results.

-- Query states
SELECT id, name FROM states

-- Then query instances for a specific state
SELECT address, resource_address FROM instances
WHERE state_id = 'your-state-id-here'

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

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

Note: The LIKE operator is not supported. Use = for exact matches or IN to match multiple specific types.

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: Filtering by resource type across tables requires JOINs, which are not yet supported. Query the resources table first to find addresses, then query instances by specific address.

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