add new db connection types, update docs for mod database args (WIP)

turbot · vhadianto · Oct 22, 2024 · Sep 18, 2024 · Sep 18, 2024 · Sep 18, 2024
commit 8ce0a1f46a86e54caed12cfbf250c2633e0c34c3
diff --git a/docs/flowpipe-hcl/mod.md b/docs/flowpipe-hcl/mod.md
@@ -41,10 +41,17 @@ mod "aws" {
 ## Example - Composite Mod with Dependency
 
 ```hcl
+variable "database" {
+  type        = connection.steampipe
+  description = "Steampipe database connection string."
+  default     = connection.steampipe.default
+}
+
 mod "deactivate_expired_aws_iam_access_keys" {
   title       = "Deactivate expired AWS IAM keys"
   description = "Deactivates AWS IAM keys that have been active for a certain period of time."
-
+  database    = var.database
+
   require {
     flowpipe {
       min_version = "0.1.0"
@@ -68,7 +75,8 @@ mod "deactivate_expired_aws_iam_access_keys" {
 | Name | Type | Required? | Description
 |-|-|-|-
 | `categories` | List(String) | Optional | A list of labels, used to categorize mods (such as on the Flowpipe Hub).
-| `color` | String |Optional |  A hexadecimal RGB value to use as the color scheme for the mod on hub.flowpipe.io.  
+| `color`      | String | Optional |  A hexadecimal RGB value to use as the color scheme for the mod on hub.flowpipe.io.  
+| `database`    | String | Optional | The database to use as the default for [query steps](/docs/flowpipe-hcl/step/query) and [query triggers](/docs/flowpipe-hcl/trigger/query). The `database` may be a connection reference (`connection.steampipe.default`), a connection string (`postgres://[email protected]:9193/steampipe`), or a Pipes workspace (`acme/anvils`). If not set, the default is the local Steampipe database instance.
 | `description` |  String | Optional | A string containing a short description. 
 | `documentation` | String (Markdown)| Optional | A markdown string containing a long form description, used as documentation for the mod on hub.flowpipe.io. 
 | `icon` |  String | Optional | The URL of an icon to use for the mod on hub.flowpipe.io.

diff --git a/docs/flowpipe-hcl/step/query.md b/docs/flowpipe-hcl/step/query.md
@@ -33,11 +33,11 @@ pipeline "enabled_regions" {
 
 ## 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.             |
+| 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  | A connection string used to connect to the database.  If not set, the default set in the [mod `database`](/docs/flowpipe-hcl/mod) will be used. The `database` may be a connection reference (`connection.steampipe.default`), a connection string (`postgres://[email protected]:9193/steampipe`), or a Pipes workspace (`acme/anvils`).
 
 This step also supports the [common step arguments](/docs/flowpipe-hcl/step#common-step-arguments) and [attributes](/docs/flowpipe-hcl/step#common-step-attributes-read-only).
 
@@ -137,44 +137,77 @@ to extract data.
 
 ## More Examples
 
+
+### Steampipe Query
+
+If no `database` is specified, then the default defined in the [mod `database`](/docs/flowpipe-hcl/mod) will be used.  If that is not set, the local Steampipe instance will be used by default.
+
+You can also specify a [Steampipe connection](/docs/reference/config-files/connection/postgres) to connect to a Steampipe database:
+
+
+```hcl
+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
 
-Postgres `database` follows the standard URI syntax supported by `psql` and `pgcli`:
+You can use a [Postgres connection](/docs/reference/config-files/connection/postgres) to connect to a PostgreSQL database:
+
+
+```hcl
+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`:
 
 ```bash
 postgresql://[user[:password]@][host][:port][/dbname][?param1=value1&...]
 ```
 
+
 ```hcl
 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
+    sql      = "select name, account_id, opt_in_status from aws_region where opt_in_status <> 'not-opted-in'"
   }
 }
 ```
 
 ### SQLite query
 
-The SQLite `database` is the path to a SQLite database file:
+You can use a [SQLite connection](/docs/reference/config-files/connection/sqlite) to connect to a SQLite database:
 
-```bash
-sqlite:path/to/file
+
+```hcl
+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`:
+
+```hcl
+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](/docs/run#mod-location), and `//` is optional after the scheme, thus the following are equivalent:
@@ -185,31 +218,29 @@ database = "sqlite://./my_sqlite_db.db"
 database = "sqlite://my_sqlite_db.db"
 ```
 
-```hcl
-pipeline "sqlite_query" {
-  step "query" "step_1" {
-    database = "sqlite:./my_sqlite_db.db"
 
-    sql = <<EOQ
-      select
-        *
-      from
-        my_sqlite_table;
-    EOQ
-  }
+### DuckDB query
 
-  output "results" {
-    value = step.query.step_1.rows
+You can use a [DuckDB connection](/docs/reference/config-files/connection/duckdb) to connect to a DuckDB database:
+
+```hcl
+pipeline "duckdb_query" {
+  step "query" "step_1" {
+    database = connection.duckdb.my_ducks"
+    sql      = "select * from my_duckdb_table;"
   }
 }
 ```
 
-### DuckDB query
-
-The DuckDB `database` is the path to a DuckDB database file:
+Or pass a connection string directly in the format `duckdb:path/to/file`:
 
-```bash
-duckdb:path/to/file
+```hcl
+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](/docs/run#mod-location), and `//` is optional after the scheme, thus the following are equivalent:
@@ -220,24 +251,7 @@ database = "duckdb://./my_ducks.db"
 database = "duckdb://my_ducks.db"
 ```
 
-```hcl
-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
 

diff --git a/docs/reference/config-files/connection/duckdb.md b/docs/reference/config-files/connection/duckdb.md
@@ -0,0 +1,64 @@
+---
+title:  duckdb
+sidebar_label: duckdb
+---
+
+# duckdb
+
+The `duckdb` connection can be used to access a [DuckDB](https://duckdb.org/) database.
+
+```hcl
+connection "duckdb" "duckdb_connection" {
+   connection_string = "duckdb://my_ducks.db"
+}
+```
+
+## Arguments
+
+| Name                | Type    | Required?| Description
+|---------------------|---------|----------|-------------------
+| `connection_string` |  String | Optional | DuckDB connection string
+
+
+
+### Connection String
+
+The DuckDB connection string is the path to a DuckDB database file:
+
+```bash
+duckdb:path/to/file
+```
+
+The path is relative to the [mod location](/docs/run#mod-location), and `//` is optional after the scheme, thus the following are equivalent relative paths:
+
+```hcl
+duckdb:./my_ducks.db
+duckdb://./my_ducks.db
+duckdb://my_ducks.db
+```
+
+and these are equivalent absolute paths:
+
+```hcl
+duckdb:/var/db/my_ducks.db
+duckdb:///var/db/my_ducks.db
+```
+
+All arguments are optional, and a `duckdb` connection with no arguments will behave the same as the [default connection](#default-connection).
+
+<!--
+## Attributes (Read-Only)
+
+| Attribute       | Type   | Description
+| --------------- | ------ |------------------------------------
+| `???`           | String | blah
+-->
+
+## Default Connection
+
+The `duckdb` connection type includes an implicit, default connection (`connection.duckdb.default`) that will be configured using the environment variables ???
+```hcl
+connection "duckdb" "default" {
+
+}
+```
diff --git a/docs/reference/config-files/connection/postgres.md b/docs/reference/config-files/connection/postgres.md
@@ -0,0 +1,48 @@
+---
+title:  postgres
+sidebar_label: postgres
+---
+
+# postgres
+
+The `postgres` connection can be used to access a PostgreSQL database.
+
+```hcl
+connection "postgres" "postgres_connection" {
+
+}
+```
+
+## Arguments
+
+| Name                | Type    | Required?| Description
+|---------------------|---------|----------|-------------------
+| `connection_string` |  String | Optional |  SQL connection string
+
+All arguments are optional, and a `postgres` connection with no arguments will behave the same as the [default connection](#default-connection).
+
+connection_string
+username
+host
+port
+password
+ssl_mode
+
+
+
+## Attributes (Read-Only)
+
+| Attribute       | Type   | Description
+| --------------- | ------ |------------------------------------
+| `???`           | String | blah
+
+
+## Default Connection
+
+The `postgres` connection type includes an implicit, default connection (`connection.postgres.default`) that will be configured using the environment variables ....
+
+```hcl
+connection "postgres" "default" {
+
+}
+```
diff --git a/docs/reference/config-files/connection/sqlite.md b/docs/reference/config-files/connection/sqlite.md
@@ -0,0 +1,65 @@
+---
+title:  sqlite
+sidebar_label: sqlite
+---
+
+
+# sqlite
+
+The `sqlite` connection can be used to access a [SQLite](https://www.sqlite.org/) database.
+
+```hcl
+connection "sqlite" "sqlite_connection" {
+   connection_string = "sqlite://my_sqlite_db.db"
+}
+```
+
+## Arguments
+
+| Name                | Type    | Required?| Description
+|---------------------|---------|----------|-------------------
+| `connection_string` |  String | Optional | SQLite connection string
+
+
+
+### Connection String
+
+The SQLite connection string is the path to a SQLite database file:
+
+```bash
+sqlite:path/to/file
+```
+
+The path is relative to the [mod location](/docs/run#mod-location), and `//` is optional after the scheme, thus the following are equivalent relative paths:
+
+```hcl
+sqlite:./my_sqlite_db.db
+sqlite://./my_sqlite_db.db
+sqlite://my_sqlite_db.db
+```
+
+and these are equivalent absolute paths:
+
+```hcl
+sqlite:/var/db/my_sqlite_db.db
+sqlite:///var/db/my_sqlite_db.db
+```
+
+All arguments are optional, and a `sqlite` connection with no arguments will behave the same as the [default connection](#default-connection).
+
+<!--
+## Attributes (Read-Only)
+
+| Attribute       | Type   | Description
+| --------------- | ------ |------------------------------------
+| `???`           | String | blah
+-->
+
+## Default Connection
+
+The `sqlite` connection type includes an implicit, default connection (`connection.sqlite.default`) that will be configured using the environment variables...
+```hcl
+connection "sqlite" "default" {
+
+}
+```