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_delete

Delete the rows matching where, checked against the table’s schema before the run starts.

Remove a merchant’s test products.

action "postgres_delete" "test_products" {
  table = "products"
  where = { merchant_id = action.postgres_insert.merchant.id, name = "Test" }
}

Attributes

Outputs

Type: object({rows_affected=number}).

rows_affected: how many rows were deleted.

postgres_insert

Insert one row. Values are sent as bind parameters, never pasted into SQL, and are checked against the table’s schema before the run starts: the table and columns exist, every value fits its column, every required column (not nullable, no default, not generated) is set, and no generated column is. The outputs are the inserted row, including generated ids and defaults.

orchard teardown deletes the row again by its primary key, unless the action has its own lifecycle { teardown { } } block or sets lifecycle { managed_teardown = false }.

Insert a merchant, then a product that references it.

action "postgres_insert" "merchant" {
  table  = "merchants"
  values = { name = "Acme", region = "us" }
}

action "postgres_insert" "product" {
  table  = "products"
  values = {
    merchant_id = action.postgres_insert.merchant.id
    name        = "Widget"
    price       = 9.99
  }
}

Attributes

Outputs

Type: any.

The inserted row, keyed by column name. Columns come back typed by their kind: numbers, bools, strings, decoded JSON.

postgres_query

Execute SQL: one statement, or several separated by ; that run as one transaction. 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"
}

Bind values to placeholders with params instead of interpolating them into the SQL.

action "postgres_query" "merchant" {
  query  = "INSERT INTO merchants (name, region) VALUES ($1, $2) RETURNING id"
  params = [var.merchant_name, "eu"]
}

action "postgres_query" "eu_products" {
  query  = "SELECT count(*) AS n FROM products WHERE merchant_id = ANY($1)"
  params = [[action.postgres_query.merchant.id]]
}

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.

postgres_update

Update the rows matching where, with the same checks and bind parameters as postgres_insert. Generated columns can’t be set.

orchard teardown writes back the values the update overwrote, for rows inserted earlier in the same run. Set lifecycle { managed_teardown = true } to restore other rows too, or false to restore nothing. Updates matching more than 1,000 rows aren’t restored.

Suspend the merchant inserted earlier.

action "postgres_update" "suspend" {
  table = "merchants"
  set   = { status = "suspended" }
  where = { id = action.postgres_insert.merchant.id }
}

Attributes

Outputs

Type: object({rows_affected=number}).

rows_affected: how many rows the update changed.

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.
• postgres_insert, postgres_update and postgres_delete are typed alternatives to postgres_query: values are bound as parameters, and each action is checked against the live schema before the run starts. A table that doesn’t exist yet (because an earlier action creates it) is checked when the action runs instead.
• Each action commits on its own; there is no transaction spanning actions. A postgres_query can hold several statements separated by ;, and Postgres runs them as one transaction: if any fails, none take effect, unless the query issues its own COMMIT or ROLLBACK partway. Wrapping them in BEGIN; ... COMMIT; does the same explicitly; a BEGIN left without its COMMIT is rolled back and fails the action. Statements that refuse to run in a transaction block (VACUUM, CREATE DATABASE, CREATE INDEX CONCURRENTLY) need a postgres_query of their own. Outputs come from the first statement’s result, so are empty when it is BEGIN.
• 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.