sqlite

Source: builtin/sqlite

SQLite data operations.

Provider configuration

Actions

sqlite_delete

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

Remove a merchant’s test products.

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

Attributes

Outputs

Type: object({rows_affected=number}).

rows_affected: how many rows were deleted.

sqlite_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 "sqlite_insert" "merchant" {
  table  = "merchants"
  values = { name = "Acme", region = "us" }
}

action "sqlite_insert" "product" {
  table  = "products"
  values = {
    merchant_id = action.sqlite_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.

sqlite_query

Execute a SQL statement. If it returns rows (a SELECT, or a write with RETURNING), the first row is exposed as outputs keyed by column name. If it returns no rows, the outputs are last_insert_id and rows_affected.

Insert a row and capture its generated ID with RETURNING.

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

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

Without RETURNING, wire last_insert_id into the next statement.

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

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

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

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

Attributes

Outputs

Type: any.

For a statement that returns rows: the first row of the result set, keyed by column name, or empty if no rows matched.

For a statement that returns no rows (INSERT, UPDATE or DELETE without RETURNING, or DDL):

Column values are mapped to cty as follows:

sqlite_update

Update the rows matching where, with the same checks and bind parameters as sqlite_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 "sqlite_update" "suspend" {
  table = "merchants"
  set   = { status = "suspended" }
  where = { id = action.sqlite_insert.merchant.id }
}

Attributes

Outputs

Type: object({rows_affected=number}).

rows_affected: how many rows the update changed.

Examples

A complete scenario against a local database file, with teardown.

scenario "sqlite_demo" {
  required_providers {
    sqlite = { source = "builtin/sqlite" }
  }

  provider "sqlite" {
    path = "./dev.db"
  }

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

    lifecycle {
      teardown {
        query = "DELETE FROM merchants WHERE id = ${self.id}"
      }
    }
  }

  output "merchant_id" { value = action.sqlite_query.create_merchant.id }
}

Notes

• Orchard does not manage schema. Tables must exist before the scenario runs, or create them with a CREATE TABLE IF NOT EXISTS action that the rest of the scenario depends on.
• sqlite_insert, sqlite_update and sqlite_delete are typed alternatives to sqlite_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.
• Foreign keys are enforced. SQLite leaves them off by default; Orchard turns them on for every connection so scenarios fail loudly on a dangling reference, the same as on Postgres.
• Each sqlite_query runs on its own, outside any transaction spanning actions. If you need several statements to apply atomically, wrap them in one query with BEGIN; ... COMMIT;.
• Actions run one at a time over a single connection, even with orchard run --parallelism. That keeps a :memory: database shared by every action in the run.
• A :memory: database disappears when the run ends, so orchard teardown has nothing to clean up. Use a file path for state you want to tear down later.
• 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.