query

A query step runs a database query against a database. Postgres, MySQL, SQLite, and DuckDB databases are currently supported.

pipeline "enabled_regions" {

  step "query" "get_enabled_regions" {
    database = "postgres://steampipe@localhost:9193/steampipe"
    sql      = <<-EOQ
      select
        name,
        account_id,
        opt_in_status
      from
        aws_region
      where
        opt_in_status <> 'not-opted-in'
    EOQ

  }

  output "enabled_regions" {
    value = step.query.get_enabled_regions.rows
  }
}

Arguments

Argument	Type	Optional?	Description
`sql`	String	Required	A SQL query string.
`args`	List	Optional	A list of arguments to pass to the query.
`database`	String	Optional	The database to query. This may be a connection reference (`connection.steampipe.default`), a connection string (`postgres://steampipe@127.0.0.1:9193/steampipe`), or a Pipes workspace (`acme/anvils`). If not set, the default set in the mod `database` will be used.

This step also supports the common step arguments and attributes.

Attributes (Read-Only)

Attribute	Type	Description
`rows`	List of objects	The query results, as an array of row objects

The rows attribute contains the query result as a list of objects with an item for each row, where the column name is the key and the column value is the value.

For example:

pipeline "enabled_regions" {

  step "query" "get_enabled_regions" {
    database = "postgres://steampipe@localhost:9193/steampipe"
    sql      = <<-EOQ
      select
        name,
        account_id,
        opt_in_status
      from
        aws_region
      where
        opt_in_status <> 'not-opted-in'
    EOQ

  }

  output "enabled_regions" {
    value = step.query.get_enabled_regions.rows
  }
}

Results in:

{
  "enabled_regions": [
    {
      "account_id": "123456789012",
      "name": "us-east-2",
      "opt_in_status": "opt-in-not-required"
    },
    {
      "account_id": "123456789012",
      "name": "us-west-1",
      "opt_in_status": "opt-in-not-required"
    },
    {
      "account_id": "123456789012",
      "name": "ap-south-1",
      "opt_in_status": "opt-in-not-required"
    },
    {
      "account_id": "123456789012",
      "name": "us-west-2",
      "opt_in_status": "opt-in-not-required"
    },
    ...
  ]
}

Since rows is a list, you can use standard HCL for expressions:

output "enabled_region_names" {
    value = [ for v in step.query.get_enabled_regions.rows : v.name ]
}

or splats:

output "enabled_region_names" {
    value = step.query.get_enabled_regions.rows[*].name
}

to extract data.

{
  "enabled_region_names": [
    "us-east-2",
    "us-west-1",
    "ap-south-1",
    "us-west-2",
    ...
  ]
}

More Examples

Steampipe Query

If no database is specified, then the default defined in the mod database will be used. If that is not set, the local Steampipe instance will be used by default.

You can also specify a Steampipe connection to connect to a Steampipe database:

pipeline "instances_by_region" {
  step "query" "get_instances_by_region" {
    database = connection.steampipe.default
    sql      = "select region, count(*) from aws_ec2_instance group by region;"
  }
}

Postgres Query

You can use a Postgres connection to connect to a PostgreSQL database:

pipeline "enabled_regions" {
  step "query" "get_enabled_regions" {
    database = connection.postgres.mydb
    sql      = "select name, account_id, opt_in_status from aws_region where opt_in_status <> 'not-opted-in'"
  }
}

Alternatively, you can pass the connection string directly, with the standard URI syntax supported by psql and pgcli:

postgresql://[user[:password]@][host][:port][/dbname][?param1=value1&...]

pipeline "enabled_regions" {
  step "query" "get_enabled_regions" {
    database = "postgres://steampipe@localhost:9193/steampipe"
    sql      = "select name, account_id, opt_in_status from aws_region where opt_in_status <> 'not-opted-in'"
  }
}

SQLite query

You can use a SQLite connection to connect to a SQLite database:

pipeline "sqlite_query" {
  step "query" "step_1" {
    database = connection.sqlite.my_db
    sql      = "select * from my_sqlite_table;"
  }
}

Alternatively, pass the connection string directly, in the format sqlite:path/to/file:

pipeline "sqlite_query" {
  step "query" "step_1" {
    database = "sqlite:./my_sqlite_db.db"
    sql      = "select * from my_sqlite_table;"
  }
}

The path is relative to the mod location, and // is optional after the scheme, thus the following are equivalent:

database = "sqlite:./my_sqlite_db.db"
database = "sqlite://./my_sqlite_db.db"
database = "sqlite://my_sqlite_db.db"

DuckDB query

You can use a DuckDB connection to connect to a DuckDB database:

pipeline "duckdb_query" {
  step "query" "step_1" {
    database = connection.duckdb.my_ducks"
    sql      = "select * from my_duckdb_table;"
  }
}

Or pass a connection string directly in the format duckdb:path/to/file:

pipeline "duckdb_query" {
  step "query" "step_1" {
    database = "duckdb:./my_ducks.db"
    sql      = "select * from my_duckdb_table;"
  }
}

The path is relative to the mod location, and // is optional after the scheme, thus the following are equivalent:

database = "duckdb:./my_ducks.db"
database = "duckdb://./my_ducks.db"
database = "duckdb://my_ducks.db"

MySQL Query

You can use a MySQL connection to connect to a MySQL database:

pipeline "mysql_query" {
  step "query" "step_1" {
    database = connection.mysql.default
    sql      = "select host, user from user;"
  }
}

Or pass a connection string directly. The MySQL connection string supports the syntax of the GO SQL driver for MySQL:

mysql://[user[:password]@]network-location[:port][/dbname][?param1=value1&...]

pipeline "mysql_query" {
  step "query" "step_1" {
    database = "mysql://root:my_pass@tcp(localhost)/mysql"
    sql      = "select host, user from user;"
  }
}