# Marmot Context

Marmot is a Gleam build tool that reads `.sql` files, introspects a live SQLite database for type information, and generates type-safe Gleam functions targeting `sqlight`. Heavily inspired by [Squirrel](https://github.com/giacomocavalieri/squirrel) (which does the same for Postgres).

## Quick start

```sh
gleam add marmot --dev
gleam add sqlight
gleam run -m marmot
```

Write SQL in `src/**/sql/*.sql`, one query per file. Filename becomes the function name.

## How it works

1. Reads config: `DATABASE_URL` env > `--database` CLI flag > `gleam.toml [marmot]`
2. Opens the SQLite file, scans `src/` for `sql/` directories
3. For each `.sql` file: reads SQL, introspects via `PRAGMA table_info` + `EXPLAIN`
4. Generates a `sql.gleam` module per `sql/` directory with typed functions + decoders

## Config (`[marmot]` in `gleam.toml`)

| Key | Default | Purpose |
|---|---|---|
| `database` | — | Path to the SQLite file used for introspection (also `DATABASE_URL` env, `--database` CLI) |
| `output` | `src/generated/sql` | Output directory for generated modules (must be under `src/`). Marmot infers the directory structure by finding the longest common path prefix between `output` and each `sql/` directory, then uses the remainder as the namespace (e.g. `src/app/users/sql/` with default output produces `src/generated/sql/app/users_sql.gleam`). |
| `query_function` | `sqlight.query` | Fully-qualified function (`module/path.name`) that generated code calls instead of `sqlight.query`. Wrapper must match `sqlight.query`'s labelled signature (`sql: String, on connection, with parameters, expecting decoder`). Useful for logging, timing, instrumentation. |

## Project structure

```
src/
  marmot.gleam                    -- CLI entry point (main, run_generate)
  marmot/
    internal/
      error.gleam                 -- MarmotError type + to_string (pretty-printed errors)
      query.gleam                 -- ColumnType, Column, Parameter, Query types + helpers
      project.gleam               -- Config parsing, sql/ directory scanning, output paths
      sqlite.gleam                -- PRAGMA + EXPLAIN introspection (QueryInfo)
      codegen.gleam               -- Gleam source generation (functions, row types, decoders)
```

## Key design decisions

- **Live SQLite introspection**: connects to real `.db` file, not static analysis
- **EXPLAIN-based type inference**: traces opcodes to map result columns and `?` params back to source table columns
- **No external tools**: no `sqlc`, no `sqlite3` CLI, just `sqlight` (Gleam NIF)
- **Convention over configuration**: follows Squirrel's conventions (one query per file, `sql/` dirs, filename = function name). `output` and `query_function` are opt-in knobs for larger projects.
- **Generated code calls sqlight directly** (or a user-supplied wrapper via `query_function`): no tuples, no intermediate types

## Generated code patterns

Generated functions use labelled arguments: `pub fn find_user(db db: ..., username username: ...)`. Call sites read as `users_sql.find_user(db: db, username: "alice")`.

**SELECT with decoder:**
```gleam
pub type FindUserRow { FindUserRow(id: Int, name: String) }
pub fn find_user(db db: sqlight.Connection, username username: String) {
  sqlight.query("...", on: db, with: [sqlight.text(username)], expecting: { ... })
}
```

**INSERT/UPDATE/DELETE without RETURNING:**
```gleam
pub fn delete_user(db db: sqlight.Connection, id id: Int) {
  sqlight.query("...", on: db, with: [sqlight.int(id)], expecting: decode.success(Nil))
}
```

**With `query_function = "app/db.query"`:** the `sqlight.query(...)` call becomes `db.query(...)` with `import app/db` added. Everything else is identical.

**Module:** imports are conditional (only include `decode`, `option`, `timestamp`, `calendar` when needed). Date modules get `date_decoder`/`date_to_string` private helpers. Timestamp modules get a `timestamp_to_int` helper.

## Shared return types

Multiple queries in the same `sql/` directory can share a single Row type
and decoder by annotating each with a top-of-file SQL comment:

```sql
-- returns: OrgRow
SELECT id, name, subdomain FROM orgs WHERE id = @id
```

Rules:
- The annotation must appear before the first SQL statement. Blank lines
  and other `--` comments may precede it.
- The type name must end in `Row` and be a valid Gleam PascalCase identifier.
- All queries in the same directory annotated with the same name must
  return the exact same shape: same field names, types, nullability, and
  column order. Marmot reports a generation-time error on mismatch.
- Unannotated queries keep the default per-query Row type.

Scope: per-directory (per generated module). `OrgRow` in `admin/orgs/sql/`
is a distinct type from `OrgRow` in `public/orgs/sql/`.

Generated output for shared types:
```gleam
pub type OrgRow { OrgRow(id: Int, name: String, subdomain: String) }

fn org_row_decoder() -> decode.Decoder(OrgRow) { ... }

pub fn get_org(db db: ..., id id: Int) -> Result(List(OrgRow), sqlight.Error) {
  sqlight.query("...", on: db, with: [...], expecting: org_row_decoder())
}
```

## Type mappings

| SQLite type | Gleam type | Decoder | Encoder |
|---|---|---|---|
| `INTEGER`/`INT` | `Int` | `decode.int` | `sqlight.int` |
| `REAL`/`FLOAT`/`DOUBLE` | `Float` | `decode.float` | `sqlight.float` |
| `TEXT`/`VARCHAR`/`CHAR` | `String` | `decode.string` | `sqlight.text` |
| `BLOB` | `BitArray` | `decode.bit_array` | `sqlight.blob` |
| `BOOLEAN`/`BOOL` | `Bool` | `sqlight.decode_bool()` | `sqlight.bool` |
| `TIMESTAMP`/`DATETIME` | `timestamp.Timestamp` | `decode.int` -> `from_unix_seconds` | `to_unix_seconds` -> `sqlight.int` |
| `DATE` | `calendar.Date` | `decode.string` -> `parse_date` | `date_to_string` -> `sqlight.text` |
| nullable column | `Option(T)` | `decode.optional(...)` | -- |

## Known limitations

**Fixable (contributions welcome):**
- Table names containing SQL keywords (`RETURNING`, `INTO`) confuse the string-based SQL parser. A real tokenizer would fix this.
- `INSERT INTO t VALUES (?, ?)` without explicit column list degrades parameter inference. Could match positionally against schema.
- Complex expressions (subqueries, CTEs, `COALESCE`) may not infer types. Use `CAST` for now; improvable per-kind.

**Design decisions:**
- `TIMESTAMP`/`DATETIME` stored as integer Unix seconds. Sub-second precision not preserved.

**Hard limits (SQLite protocol):**
- Repeated anonymous `?` produce distinct function args. Use named parameters (`@name`). SQLite deduplicates them natively.
- `WHERE id IN (?)` with dynamic list: no array bind type. Workarounds: `json_each(?)`, fixed-size queries, or build the SQL dynamically in app code.

## Dependencies

**Runtime** (for projects using generated code): `sqlight`, `gleam_time` (if timestamps/dates)

**Dev** (marmot itself): `sqlight`, `simplifile`, `tom`, `argv`, `gleam_time`

**Test**: `gleeunit`, `birdie` (snapshots), `glinter` (linting)