Iwf Guide

Iwf Guide

Markdown source

TypedSQL

TypedSQL lets you write raw SQL while keeping a checked Idris boundary.

In app code, you write the query. During the canonical build, Iwf asks PostgreSQL to describe that query. During Idris compilation, the macro checks that PostgreSQL's parameter and result metadata matches the Idris type you wrote.

Application modules do not write schemaPreparedSql catalogs.

1. Import The Generated Schema

TypedSQL needs generated schema metadata:

import Generated.Schema

Generated.Schema provides schemaMetadata, generated models such as User and Article, typed IDs, enum mappings, JSON/form support, and SQL decoders.

2. Configure The Build

TypedSQL needs PostgreSQL before Idris compilation. Set:

export DATABASE_URL="postgres://..."

or:

export IWF_DATABASE_URL="postgres://..."

When the describe step should load the current schema file into an isolated transaction, set:

export IWF_TYPEDSQL_SCHEMA="$PWD/Application/Schema.sql"

The describe helper loads that schema with a fresh search path, describes the queries, and rolls the transaction back. This keeps query metadata tied to the current schema file instead of a stale developer database.

3. A Query That Returns Rows

Use sqlQuery when the SQL returns rows:

articles : ControllerContext -> ControllerApp (List Article)
articles context =
  sqlQuery "select articles.* from articles"

When PostgreSQL describe metadata shows an exact full-table projection, TypedSQL decodes it as the generated model. This works for select articles.* ..., aliased forms such as select a.* from articles a ..., and explicit full column lists in schema order. Partial and computed multi-column selects still decode as scalar or tuple-shaped results.

4. A Query With Parameters

Use ${name} placeholders:

findArticleRows : String -> ControllerContext -> ControllerApp (List (String, String, String))
findArticleRows slug context =
  sqlQuery "select slug, title, body from articles where slug = ${slug}"

Iwf rewrites placeholders to PostgreSQL positional parameters before describe and execution:

${slug} -> $1

Repeating the same placeholder creates another PostgreSQL parameter, matching normal SQL occurrence behavior.

Placeholders also capture field projections from local values:

articleTags : Article -> ControllerContext -> ControllerApp (List String)
articleTags article context =
  sqlQuery "select name from tags where article_id = ${article.id}"

When PostgreSQL cannot infer a placeholder type, cast it:

select * from articles where id = any(${ids}::int8[])

5. A Statement That Does Not Return Rows

Use sqlExec for statements that must not return rows:

insertArticle :
  Id "users" ->
  String ->
  String ->
  String ->
  ControllerContext ->
  ControllerApp ()
insertArticle authorId slug title body context =
  sqlExec
    "insert into articles (author_id, slug, title, body) values (${authorId}, ${slug}, ${title}, ${body})"

If sqlExec returns columns, the module fails to compile and points you to sqlQuery. If sqlQuery has no result columns, the module fails and points you to sqlExec.

6. Run A Prepared Query

In handlers, execute prepared queries directly. The handler's ControllerContext is used as an auto implicit database source:

rows <-
  sqlQuery "select slug, title, body from articles"

For commands:

sqlExec
  "insert into articles (author_id, slug, title, body) values (${authorId}, ${slug}, ${title}, ${body})"

Checked-out pool slots reuse long-lived PostgreSQL helper workers. Parameterized runtime queries are prepared and cached per worker connection. Parameter values still travel separately from SQL text.

7. Decode Models

Annotate the surrounding handler return type when generated model decoders or DecodeSqlResult instances should decode full rows:

allArticles : ControllerContext -> ControllerApp (List Article)
allArticles context =
  sqlQuery "select articles.* from articles"

TypedSQL maps PostgreSQL integer, text, boolean, UUID, date/time, JSON, bytea, float, numeric, network, vector, geometric, array, and generated enum values. Nullable scalar results become Maybe.

For model inserts, insert into articles values ${article} expands during macro compilation into an explicit schema-order column list and one placeholder per model field. Higher-level insert/update helper APIs are out of scope; keep using sqlQuery and sqlExec as the SQL API.

Advanced code can still use queryPreparedRows when it needs raw SqlValue lists instead of decoded rows.

8. What SQL Is Accepted

TypedSQL accepts statements PostgreSQL can describe, including:

  • explicit SELECT lists.
  • aliases.
  • joins.
  • CTEs.
  • subqueries.
  • UNION.
  • PostgreSQL-described star selects.
  • positional inserts.
  • INSERT, UPDATE, and DELETE.
  • VALUES.
  • MERGE ... RETURNING when PostgreSQL can describe it.

There is no public typedSqlStar escape hatch. Star selections go through the same PostgreSQL describe path as every other typedSql, sqlQuery, or sqlExec call.

9. Source Errors

Build scripts can run a source-aware preflight before Idris compilation:

iwf build

The scanner reports failures as:

path/to/File.idr:line:column

Placeholder scanning ignores SQL comments and dollar-quoted strings.

10. Tests

The TypedSQL contract is covered by framework specs and compile fixtures:

tests/fixtures/typedsql-pass
tests/fixtures/typedsql-fail

Run the PostgreSQL-backed acceptance group:

tests/scripts/acceptance typedsql-postgres

Next

Read JSON API and OpenAPI.