Add insert example to docs

[jrstrunk]

Hello! From experience, inserting into SQLite databases has been a common hurdle for people new to Gleam (for the people that are using SQLite of course). What do you think of updating the docs somewhere to show an example of running an insert statement with a sqlight.query call?

Maybe something like this, moving one part of the data into a separate insert statement?

import gleam/dynamic/decode
import sqlight

pub fn main() {
  use conn <- sqlight.with_connection(":memory:")
  let cat_decoder = {
    use name <- decode.field(0, decode.string)
    use age <- decode.field(1, decode.int)
    decode.success(#(name, age))
  }

  let sql = "
  create table cats (name text, age int);

  insert into cats (name, age) values 
  ('Nubi', 4),
  ('Biffy', 10);
  "

  let assert Ok(Nil) = sqlight.exec(sql, conn)

  let sql = "insert into cats (name, age) values (?, ?)"

  let assert Ok([]) =
    sqlight.query(
      sql,
      on: conn,
      with: [sqlight.text("Ginny"), sqlight.int(6)],
      expecting: decode.int,
    )

  let sql = "
  select name, age from cats
  where age < ?
  "

  let assert Ok([#("Nubi", 4), #("Ginny", 6)]) =
    sqlight.query(sql, on: conn, with: [sqlight.int(7)], expecting: cat_decoder)
}

[lpil]

Could be good! What are you aiming to demonstrate?

[jrstrunk]

Mostly just that you don't have to do anything weird like "returning *" to satisfying the expecting: decoder argument in query. The example shows that for inserts the decoder passed can be anything, and the returned result is an empty list.

[lpil]

Maybe we ought to recommend decode.success over decode.int, I was a bit confused as to why that was used.

Alternatively an API like Pog could be adopted

[jrstrunk]

True that's probably less likely to be confused! I wasn't going to say anything since it's a big item, but I do think an API like pog would be nicer overall 😅

[lpil]

Sounds good to me!