# 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:

```idris
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:

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

or:

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

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

```sh
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:

```idris
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:

```idris
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:

```text
${slug} -> $1
```

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

Placeholders also capture field projections from local values:

```idris
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:

```sql
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:

```idris
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:

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

For commands:

```idris
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:

```idris
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:

```sh
iwf build
```

The scanner reports failures as:

```text
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:

```text
tests/fixtures/typedsql-pass
tests/fixtures/typedsql-fail
```

Run the PostgreSQL-backed acceptance group:

```sh
tests/scripts/acceptance typedsql-postgres
```

## Next

Read [JSON API and OpenAPI](json-api-and-openapi.md).