mysql

Source: builtin/mysql

MySQL and MariaDB data operations.

Provider configuration

Actions

mysql_delete

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

Remove a merchant’s test products.

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

Attributes

Outputs

Type: object({rows_affected=number}).

rows_affected: how many rows were deleted.

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

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

mysql_query

Execute a SQL statement. If it returns rows (a SELECT), 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, then use its AUTO_INCREMENT id in the next statement.

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

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

Read a row back with SELECT; its columns become outputs.

action "mysql_query" "merchant" {
  query = "SELECT id, name FROM merchants WHERE id = ${action.mysql_query.create_merchant.last_insert_id}"
}

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

action "mysql_query" "merchant" {
  query  = "INSERT INTO merchants (name, region) VALUES (?, ?)"
  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, DELETE, or DDL):

Column values are mapped to cty as follows:

mysql_update

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

Attributes

Outputs

Type: object({rows_affected=number}).

rows_affected: how many rows the update changed.

Examples

A complete scenario, with teardown, reading the DSN from the environment.

scenario "mysql_demo" {
  required_providers {
    mysql = { source = "builtin/mysql" }
  }

  variable "dsn" {
    default = env("DATABASE_URL")
  }

  provider "mysql" {
    dsn = var.dsn
  }

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

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

  output "merchant_id" { value = action.mysql_query.create_merchant.last_insert_id }
}

Notes

• Orchard does not manage schema. Tables must exist before the scenario runs.
• mysql_insert, mysql_update and mysql_delete are typed alternatives to mysql_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.
• MySQL has no RETURNING clause. After an INSERT, wire the new row’s id into later steps with last_insert_id (see the examples below). MariaDB 10.5+ does support RETURNING, and its rows come back as outputs like any SELECT.
• Each mysql_query runs on its own, outside any transaction spanning actions. One query can hold several statements, so wrap them in START TRANSACTION; ... COMMIT; if they must apply atomically. With several statements, last_insert_id and rows_affected describe the last one.
• 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.
• Connection opening is lazy — a well-formed DSN configures the provider successfully; the first action call is where connection errors surface.