oxid query

Execute SQL queries directly against the Oxid state database. Works with both SQLite and PostgreSQL backends.

Usage

oxid query "<sql>" [flags]

The query command lets you run arbitrary SQL against your infrastructure state. This is one of Oxid's key differentiators - because state is stored in a real database (SQLite or PostgreSQL), you can use the full power of SQL to analyze, filter, and aggregate your infrastructure.

Flags

Flag	Description	Default
--format <fmt>	Output format: table, json, or csv.	table

Database Tables

The Oxid state database contains the following tables:

resources - All managed resources with their current attributes
outputs - Output values defined in your configuration
runs - History of plan and apply operations
resource_history - Timestamped record of every resource change
dependencies - Resource dependency edges

Example Queries

List all resources

$ oxid query "SELECT address, type, status FROM resources ORDER BY type, address"

+------------------------------------+---------------------+--------+
| address                            | type                | status |
+------------------------------------+---------------------+--------+
| aws_instance.api                   | aws_instance        | active |
| aws_internet_gateway.main          | aws_internet_gateway| active |
| aws_s3_bucket.logs                 | aws_s3_bucket       | active |
| aws_security_group.web             | aws_security_group  | active |
| aws_subnet.public[0]              | aws_subnet          | active |
| aws_subnet.public[1]              | aws_subnet          | active |
| aws_vpc.main                       | aws_vpc             | active |
+------------------------------------+---------------------+--------+

Count resources by type

$ oxid query "SELECT type, COUNT(*) as count FROM resources GROUP BY type ORDER BY count DESC"

+---------------------+-------+
| type                | count |
+---------------------+-------+
| aws_subnet          | 6     |
| aws_security_group  | 4     |
| aws_instance        | 3     |
| aws_s3_bucket       | 2     |
| aws_vpc             | 1     |
+---------------------+-------+

View outputs

$ oxid query "SELECT name, value, sensitive FROM outputs"

+------------------+-------------------------+-----------+
| name             | value                   | sensitive |
+------------------+-------------------------+-----------+
| vpc_id           | vpc-0a1b2c3d4e5f67890  | false     |
| api_endpoint     | https://api.example.com | false     |
| db_password      | <encrypted>            | true      |
+------------------+-------------------------+-----------+

Recent runs

$ oxid query "SELECT id, action, created, resources_changed FROM runs ORDER BY created DESC LIMIT 5"

+------+--------+---------------------+-------------------+
| id   | action | created             | resources_changed |
+------+--------+---------------------+-------------------+
| 42   | apply  | 2025-01-15 14:23:01 | 3                 |
| 41   | plan   | 2025-01-15 14:22:45 | 3                 |
| 40   | apply  | 2025-01-14 09:11:30 | 1                 |
| 39   | plan   | 2025-01-14 09:11:12 | 1                 |
| 38   | apply  | 2025-01-10 16:45:22 | 7                 |
+------+--------+---------------------+-------------------+

Resource change history

$ oxid query "SELECT address, action, created_at FROM resource_history WHERE address LIKE 'aws_instance%' ORDER BY created_at DESC"

JSON output for scripting

$ oxid query "SELECT address, type FROM resources WHERE type = 'aws_s3_bucket'" --format json

[
  {"address": "aws_s3_bucket.logs", "type": "aws_s3_bucket"},
  {"address": "aws_s3_bucket.data", "type": "aws_s3_bucket"}
]

CSV export

$ oxid query "SELECT address, type, status FROM resources" --format csv > resources.csv

PostgreSQL Backend

When using the PostgreSQL backend, you get the full power of PostgreSQL SQL, including joins, window functions, CTEs, and JSON operators:

oxid query "
  SELECT
    type,
    COUNT(*) as total,
    COUNT(*) FILTER (WHERE status = 'active') as active
  FROM resources
  GROUP BY type
  HAVING COUNT(*) > 1
  ORDER BY total DESC
"

TipSQL queries run directly against the state database with no provider calls. They execute in milliseconds regardless of infrastructure size.