Sqlite: better queries

[bhansconnect]

The better query ideas from https://roc.zulipchat.com/#narrow/channel/383402-API-design/topic/Sqlite.20APIs

main! = \_args ->
    db_path = try Env.var! "DB_PATH"

    query_todos_by_status! = try Sqlite.prepare_query_many! {
        path: db_path,
        query: "SELECT id, task FROM todos WHERE status = :status;",
        bindings: \status -> [{ name: ":status", value: String status }],
        rows: { Sqlite.decode_record <-
            id: Sqlite.i64 "id" |> Sqlite.map_value Num.toStr,
            task: Sqlite.str "task",
        },
    }
    todo = try query_todos_by_status! "todo"

    try Stdout.line! "Todo Tasks:"
    try List.forEachTry! todo \{ id, task } ->
        Stdout.line! "\tid: $(id), task: $(task)"

    completed = try query_todos_by_status! "completed"

    try Stdout.line! "\nCompleted Tasks:"
    try List.forEachTry! completed \{ id, task } ->
        Stdout.line! "\tid: $(id), task: $(task)"

    Ok {}

and

exec_transaction! = try prepare_transaction! { path: "path/to/database.db" }

try exec_transaction! \{} ->
    try Sqlite.execute! {
        path: "path/to/database.db",
        query: "INSERT INTO users (first, last) VALUES (:first, :last);",
        bindings: [
            { name: ":first", value: String "John" },
            { name: ":last", value: String "Smith" },
        ],
    }

    # Oh no, hit an error. Need to rollback.
    # Note: Error could be anything.
    Err NeedToRollback

[gamebox]

Example re-written to work with syntax on latest (not nightly) at the time of writing:

main! = |_args|
    db_path = Env.var!("DB_PATH")?

    query_todos_by_status! = Sqlite.prepare_query_many!({
        path: db_path,
        query: "SELECT id, task FROM todos WHERE status = :status;",
        bindings: |status| [{ name: ":status", value: String status }],
        rows: { Sqlite.decode_record <-
            id: Sqlite.i64 "id" |> Sqlite.map_value Num.toStr,
            task: Sqlite.str "task",
        },
    })?
    todo = query_todos_by_status!("todo")?

    Stdout.line!("Todo Tasks:")?
    List.forEachTry!(todo, |{ id, task }|
        Stdout.line! "\tid: ${id}, task: ${task}"
    )

    completed = query_todos_by_status!("completed")?

    Stdout.line!("\nCompleted Tasks:")?
    List.forEachTry!(completed, |{ id, task }|
        Stdout.line! "\tid: ${id}, task: ${task}"
    )

    Ok({})

and

exec_transaction! = prepare_transaction!({ path: "path/to/database.db" })

exec_transaction!(|{}|
    Sqlite.execute!({
        path: "path/to/database.db",
        query: "INSERT INTO users (first, last) VALUES (:first, :last);",
        bindings: [
            { name: ":first", value: String "John" },
            { name: ":last", value: String "Smith" },
        ],
    })?

    # Oh no, hit an error. Need to rollback.
    # Note: Error could be anything.
    Err(NeedToRollback)
)