hemx/docs/recipes/sqlx-persistence.md

# Recipe: SQLx persistence for the SaaS tutorial

This recipe replaces the tutorial app's in-memory `LocalProjectStore` with an
application-owned SQLx adapter. SQLx is deliberately a recipe dependency, not a
hemx core dependency: hemx still sees ordinary Rust domain values, typed forms,
and generated UI commands. req: laws/002 req: laws/004 req: auth/001

Use this when the `examples/saas` flow is ready to persist projects outside the
process. Keep auth/session and CSRF checks in middleware/extractors or app state,
then call the store from the handler only after those checks pass. req: auth/002 req: auth/004

## Cargo feature in the app, not hemx

Add SQLx to the application crate that owns persistence:

```toml
# examples/saas/Cargo.toml or your app crate
[dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "sqlite", "macros", "migrate"] }
```

Do not add SQLx to `hemx`, `hemx-core`, `hemx-build`, `hemx-derive`, or
`hemx-axum`. Persistence is app/domain policy, not a UI runtime primitive.

## Schema

```sql
-- migrations/0001_projects.sql
CREATE TABLE projects (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  owner_email TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```

## Adapter

The adapter has the same shape as `LocalProjectStore`: insert a domain command,
return a domain record, and let the handler convert that record into the
hemplate view type used by generated helpers. req: examples/001 req: canonical_authoring/002

```rust
use sqlx::{Row, SqlitePool};

#[derive(Clone)]
pub struct SqlxProjectStore {
    pool: SqlitePool,
}

impl SqlxProjectStore {
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }

    pub async fn insert(
        &self,
        name: ProjectName,
        session: &Session,
    ) -> Result<ProjectRecord, AppError> {
        let row = sqlx::query(
            r#"
            INSERT INTO projects (name, owner_email)
            VALUES (?, ?)
            RETURNING id, name, owner_email
            "#,
        )
        .bind(name.as_str())
        .bind(&session.email)
        .fetch_one(&self.pool)
        .await
        .map_err(AppError::from_sqlx)?;

        Ok(ProjectRecord {
            id: ProjectId(row.try_get::<i64, _>("id").map_err(AppError::from_sqlx)? as u64),
            name: row.try_get("name").map_err(AppError::from_sqlx)?,
            owner: row.try_get("owner_email").map_err(AppError::from_sqlx)?,
        })
    }

    pub async fn list(&self) -> Result<Vec<ProjectRecord>, AppError> {
        let rows = sqlx::query(
            r#"
            SELECT id, name, owner_email
            FROM projects
            ORDER BY id
            "#,
        )
        .fetch_all(&self.pool)
        .await
        .map_err(AppError::from_sqlx)?;

        rows.into_iter()
            .map(|row| {
                Ok(ProjectRecord {
                    id: ProjectId(row.try_get::<i64, _>("id").map_err(AppError::from_sqlx)? as u64),
                    name: row.try_get("name").map_err(AppError::from_sqlx)?,
                    owner: row.try_get("owner_email").map_err(AppError::from_sqlx)?,
                })
            })
            .collect()
    }
}
```

Keep SQLx errors in the app error type and map them through the existing
`Result<impl IntoEffect, AppError>` boundary. Expected validation remains a
form UI effect; unexpected persistence failure becomes an app failure effect or
HTTP response. req: failure/004

```rust
impl AppError {
    fn from_sqlx(error: sqlx::Error) -> Self {
        eprintln!("project store failed: {error}");
        AppError::StoreUnavailable
    }
}
```

## Handler boundary

The handler shape does not change. Only the store implementation changes.

```rust
#[hemx::handler]
async fn create_project(
    State(ctx): State<AppContext>,
    Form(form): Form<NewProject>,
) -> Result<impl IntoEffect, AppError> {
    ctx.require_session()?;
    ctx.verify_csrf(&form.csrf)?;

    if form.name.as_str().is_empty() {
        return Err(AppError::Validation("Project name required"));
    }

    let project = ctx.store.insert(form.name, &ctx.session).await?;
    let total = ctx.store.list().await?.len();

    Ok((
        dashboard::project_row.append(ProjectRow::from(project)),
        dashboard::summary.set(project_summary(total)),
        dashboard::new_project.clear(),
        dashboard::flash.set("Project created"),
    ))
}
```

The important invariant is that SQLx never appears in templates, generated
helpers, the JavaScript runtime, or hemx core. It is an application adapter
behind ordinary Rust state. req: invariant/005

## Test shape

Prefer an app-level integration test with an in-memory SQLite pool and migrations:

```rust
let pool = SqlitePool::connect("sqlite::memory:").await?;
sqlx::migrate!("./migrations").run(&pool).await?;
let ctx = AppContext::with_store(Session::demo(), SqlxProjectStore::new(pool));

let response = InteractionRequest::from(form(
    dashboard::create_project,
    &[("csrf", "demo-csrf"), ("name", "Launch checklist")],
))
.dispatch_async(registry(ctx.clone()))
.await?;

let effects = inspect_batch(response.batch);
assert!(effects.inserts_html_containing(dashboard::project_row, "1", "Launch checklist"));
```

This proves the same generated form/slot/keyed-row behavior as the local adapter
while exercising a real provider at the application boundary. req: examples/001 req: test/001