postgres#

Source: builtin/postgres

PostgreSQL data operations.

Provider configuration#

Actions#

postgres_copy#

Bulk-load rows into a table via PostgreSQL COPY FROM STDIN. Dramatically faster than row-by-row INSERT for fixture data — a 10,000-row CSV loads in a single network round-trip.

Provide data inline via data or reference a file via file. The two are mutually exclusive. file paths are resolved relative to the scenario file by the engine, so file = "./fixtures/merchants.csv" works from any cwd.

Inline CSV for a handful of seed rows.

action "postgres_copy" "seed_merchants" {
  table   = "merchants"
  columns = ["name", "region"]
  format  = "csv"
  data    = <<-CSV
    Acme Corp,us
    Globex,eu
  CSV
}

Load a larger fixture from a checked-in file.

action "postgres_copy" "seed_products" {
  table   = "products"
  columns = ["merchant_id", "name", "price"]
  format  = "csv"
  header  = true
  file    = "./fixtures/products.csv"
}

Attributes

Outputs

Type: object({rows_copied=number}).

Number of rows successfully loaded.

postgres_query#

Execute a SQL statement. The first row returned (typically from a RETURNING clause) is exposed as outputs keyed by column name.

Insert a row and capture its generated ID with RETURNING.

action "postgres_query" "create_merchant" {
  query = "INSERT INTO merchants (name, region) VALUES ('Acme', 'us') RETURNING id, name"
}

action "postgres_query" "create_product" {
  query = "INSERT INTO products (merchant_id, name) VALUES (${action.postgres_query.create_merchant.id}, 'Widget') RETURNING id, name"
}

Attributes

Outputs

Type: any.

The first row of the result set, keyed by column name. Null if no rows were returned.

Column types are mapped to cty as follows:

If the query returns no rows (for example a pure INSERT without RETURNING), outputs are null.

Examples#

Seed a merchant and product, wiring the merchant’s generated ID into the product insert.

scenario "seed_demo" {
  required_providers {
    postgres = { source = "builtin/postgres" }
  }

  variable "dsn" {}

  provider "postgres" {
    dsn = var.dsn
  }

  action "postgres_query" "create_merchant" {
    query = "INSERT INTO merchants (name, region) VALUES ('Acme Corp', 'us') RETURNING id, name"
  }

  action "postgres_query" "create_product" {
    query = "INSERT INTO products (merchant_id, name, price) VALUES (${action.postgres_query.create_merchant.id}, 'Widget', 9.99) RETURNING id"
  }

  output "merchant_id" { value = action.postgres_query.create_merchant.id }
  output "product_id"  { value = action.postgres_query.create_product.id }
}

Notes#

• Orchard does not manage schema. Tables must exist before the scenario runs.
• Each sql action runs in its own transaction. There is no cross-action transactional scope today — if you need atomicity across multiple statements, bundle them into one query with a BEGIN;...COMMIT; block, or use a CTE.
• SQL injection: values interpolated via ${var.name} are HCL-evaluated to strings and substituted as text. Use scenario variables for known-safe values; never pass untrusted input through the query attribute.
• The DSN is passed to pgxpool. Connection opening is lazy — a syntactically valid DSN configures the provider successfully; the first action call is where connection errors surface.