SQL (PostgreSQL / MySQL)

Not a fixture loader. Not a migration tool.

The sql provider is intentionally narrow: it runs arbitrary SQL statements inside a scenario so you can drive state your public API does not expose. It does not manage schema, load fixtures, snapshot tables, or seed datasets. Use a real migration tool (Atlas, Goose, sqlc, Flyway, Liquibase, etc.) for schema and bulk data, then use Tales SQL only as a thin escape hatch.

The sql provider lets a scenario reach into the database to perform the few operations the public HTTP / mobile API does not allow: flip an internal feature flag, bump a counter, mark an account as verified, or read back a server-side value that is never returned over the wire. Once that state is in place, the behaviour itself is asserted through the regular HTTP / UI surface, the same way a real user would experience it.

When to use it

Use it for

• Toggling internal flags the API does not let you mutate (vip = true, feature gates, throttling counters).
• Flipping a single row from one state to another so the next HTTP step can observe a specific scenario.
• Inspecting server state inside a teardown to decide whether a cleanup step is still needed.

Don't use it for

• Loading fixtures, seeding datasets, applying migrations, managing schema, taking snapshots. That is your migration tool’s job, not Tales.
• Asserting that the user-facing behaviour works. Always pair a SQL setup with an HTTP / UI assertion that observes the visible effect.
• Reading user-facing data through SQL “to be fast”. The whole point of Tales is to pass through the same API a user does.

Drivers

Tales V1 embeds two drivers:

Driver alias	Underlying library	Placeholders
postgres (or pgx)	github.com/jackc/pgx/v5/stdlib	$1, $2
mysql	github.com/go-sql-driver/mysql	?

Other drivers are rejected at runtime with unsupported sql driver "<name>". Each step author writes the placeholder syntax their driver expects. Tales does not rewrite SQL between dialects.

Connection config

Connections live under config.sql.connections.<name>. They are opened lazily on first use and closed when the suite ends.

config {
  sql = {
    connections = {
      app = {
        driver = "postgres"
        dsn    = env("DATABASE_URL")
      }
    }
  }
}

env("DATABASE_URL", "") is the recommended pattern so the same suite can run locally (with a Docker container) and in CI (with a managed instance). Pair it with skip_unless { env_set = ["DATABASE_URL"] } if the SQL preconditions are optional. See Conditional execution.

DSNs are never copied into reports

Only the connection name, driver alias, SQL text and arg count appear in the JSONL / HTML / JUnit outputs. The DSN itself is masked through a centralised MaskDSN helper and replaced with *** in any wrapped error message.

Dial timeout

Tales injects a default dial timeout into the DSN when none is set, so a missing or unreachable database fails fast instead of stalling on the OS TCP retry stack (~127s on Linux when SYN packets are dropped):

• MySQL: timeout=10s is appended unless the DSN already carries timeout=....
• Postgres: connect_timeout=10 is appended unless the DSN already carries connect_timeout=... (both URL and key=value flavors).

The user-supplied value always wins. The first time a connection is used, Tales also runs a bounded PingContext (10s, or sooner if --timeout / per-step request.timeout is stricter) and surfaces a clear error if connectivity is broken. The connection is not cached when ping fails.

Exec step

step "sql" "make_org_vip" {
  connection = "app"

  exec {
    sql  = "UPDATE organizations SET vip = $1 WHERE id = $2"
    args = [true, result.create_org.id]
  }

  expect {
    json = {
      rows_affected = 1
    }
  }
}

Exec response shape:

{
  "rows_affected":  1,
  "last_insert_id": null
}

• rows_affected is null when the driver does not support it (so suites using one_of or optional(any()) still pass).
• last_insert_id is null on PostgreSQL by design. Use a RETURNING clause and a query step if you need the new id.

Query step

step "sql" "get_org" {
  connection = "app"

  query {
    sql  = "SELECT id, vip FROM organizations WHERE id = $1"
    args = [result.create_org.id]
  }

  expect {
    json = {
      row_count = 1
      rows = [
        {
          id  = result.create_org.id
          vip = true
        },
      ]
    }
  }

  capture {
    vip = response.json.rows[0].vip
  }
}

Query response shape:

{
  "row_count": 1,
  "columns":   ["id", "vip"],
  "rows": [
    { "id": "org_123", "vip": true }
  ]
}

• rows is a list of objects keyed by column name. Always alias your columns when joining two tables that share a name: duplicate column names fail the step with duplicate SQL column "id"; use aliases in query.
• []byte columns are decoded as UTF-8 strings; non-UTF-8 bytes cause an explicit error rather than silent corruption.
• time.Time columns are returned as RFC3339Nano strings.
• PostgreSQL jsonb columns come back as strings. Use HCL’s jsondecode(response.json.rows[0].metadata).field to descend into them.

Teardown

The SQL provider is teardown-friendly. Combine it with the standard when = can(...) pattern so the cleanup only runs when the prerequisite captured value exists:

teardown {
  step "sql" "reset_org_vip" {
    when       = can(result.create_org.id)
    connection = "app"

    exec {
      sql  = "UPDATE organizations SET vip = $1 WHERE id = $2"
      args = [false, result.create_org.id]
    }

    expect {
      json = {
        rows_affected = one_of([0, 1])
      }
    }
  }
}

Captures

response.json.<path> works exactly as it does for HTTP steps. Common patterns:

capture {
  rows_affected = response.json.rows_affected
  vip           = response.json.rows[0].vip
  first_id      = response.json.rows[0].id
}

The captured values are exposed to downstream steps under result.<step_name>.<key>.

Args, types and conversion

Args are bound through database/sql as positional parameters; Tales never interpolates them into the SQL text.

HCL value	Bound as
null	SQL NULL
true / false	bool
integer	int64 (preserves bigint precision)
non-integer	float64
string	string

Objects and maps are rejected explicitly: unsupported SQL arg type at args[<i>]: <type>. Wrap them in a string representation (JSON) before binding if you need to pass one.

Lists get their own treatment, see below.

List args and IN (...)

No driver protocol binds a list to a single placeholder, so Tales expands the placeholder instead. Write the list as a nested value:

step "sql" "get_orgs" {
  connection = "app"

  query {
    sql  = "SELECT id FROM organizations WHERE id IN ($1) ORDER BY id"
    args = [["org_123", "org_456", "org_789"]]
  }

  expect { json = { row_count = 3 } }
}

The statement actually sent becomes ... WHERE id IN ($1,$2,$3) ... and the three values are bound in order. For MySQL, write IN (?) and it becomes IN (?,?,?).

Mixing scalars and lists renumbers every later placeholder for Postgres:

query {
  sql  = "SELECT id FROM organizations WHERE id IN ($1) AND vip = $2"
  args = [["org_123", "org_456"], true]
}
# sent as: ... WHERE id IN ($1,$2) AND vip = $3

The scanner ignores placeholders that are not executable code, so a ? inside a string literal, a $2 inside a -- or /* */ comment, a $1 inside a $$ ... $$ dollar-quoted body, or a backtick-quoted MySQL identifier never shift the renumbering. Postgres never treats ? as a placeholder, since it is the jsonb operator.

What is rejected, and why

Case	Message
Empty list	args[i] is an empty list; SQL has no valid form for "IN ()"
Nested list	nested list arguments are not supported at index i
A cty set	sets are not supported as SQL args; use a list
List placeholder used twice (Postgres)	list argument $N is used k times; a list placeholder may appear only once
List with no matching placeholder	args[i] is a list but $N does not appear in the statement
$N beyond the arg count	statement references $N but only k argument(s) were provided
Placeholder/arg count mismatch (MySQL)	statement has n placeholder(s) but k argument(s) were provided

An empty list is refused rather than rewritten to IN (NULL): that substitution is right for IN and wrong for NOT IN (the comparison becomes UNKNOWN and matches nothing instead of everything), and the provider cannot know the surrounding syntax. Guard the step with when instead:

step "sql" "purge" {
  when       = length(result.collect_ids.ids) > 0
  connection = "app"

  exec {
    sql  = "DELETE FROM organizations WHERE id IN ($1)"
    args = [result.collect_ids.ids]
  }
}

A set is refused because a cty set has no source order, so the expanded statement would not be reproducible from one run to the next.

Expansion happens before the connection is acquired, so a malformed list fails immediately without opening a connection.

Statements that bind only scalars take a fast path: the SQL text and the arg slice are passed through untouched.

MySQL optimizer hints

/*! ... */ hints are executed by MySQL but are treated as ordinary block comments by the scanner. A placeholder written inside one would not be counted.

Security and masking

• DSNs are masked through a centralised MaskDSN helper. They never appear in reports: only the connection name, driver alias, SQL text and the count of args do.
• Error wrappers replace any embedded DSN substring with ***.
• Args are reported in Output.Request.args exactly as written. If a step binds a secret, mark the value as such on the call site (capture it from a previous step’s response so it is never inlined in the .tales file).

Reports

SQL steps appear with provider: "sql" in the JSONL, JUnit and HTML reports. The request structure carries connection, mode (exec / query), sql and args; the response carries the json payload described above.

sql and args are reported as authored, so the report mirrors the .tales file: a list arg keeps its nested shape rather than appearing flattened. When list expansion rewrote the statement, an extra sql_expanded field carries what was actually sent to the driver. It is absent otherwise.

Limitations (V1)

• Two drivers only: postgres (or alias pgx) and mysql. SQLite was considered and dropped to keep the cross-platform binary lean.
• No automatic placeholder rewriting between dialects. Write $1 for Postgres and ? for MySQL. (List args are expanded, but within the dialect you wrote.)
• No transactions span multiple steps. Each step uses the shared *sql.DB pool.
• Args are scalars (string / number / bool / null) or a flat list of scalars. Objects, maps, nested lists and sets are rejected at the runtime boundary.
• last_insert_id is null on PostgreSQL. Use RETURNING + query.
• JSON columns are returned as strings; use jsondecode(...) if you need to walk them.