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
`database`	String	Required	A connection string used to connect to the database.
`sql`	String	Required	A SQL query string.
`args`	List	Optional	A list of arguments to pass to the query.

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

Postgres Query

Postgres database follows 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      = <<-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
  }
}

SQLite query

The SQLite database is the path to a SQLite database file:

sqlite:path/to/file

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"

pipeline "sqlite_query" {
  step "query" "step_1" {
    database = "sqlite:./my_sqlite_db.db"

    sql = <<EOQ
      select
        *
      from
        my_sqlite_table;
    EOQ
  }

  output "results" {
    value = step.query.step_1.rows
  }
}

DuckDB query

The DuckDB database is the path to a DuckDB database file:

duckdb:path/to/file

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"

pipeline "duckdb_query" {
  step "query" "step_1" {
    database = "duckdb:./my_ducks.db"

    sql = <<EOQ
      select
        *
      from
        my_duckdb_table;
    EOQ
  }

  output "results" {
    value = step.query.step_1.rows
  }
}

MySQL Query

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 = <<EOQ
      select
        host,
        user
      from
        user;
    EOQ
  }

  output "results" {
    value = step.query.step_1.rows
  }
}