Inserting a string that can be parsed as an integer into a string column causes subsequent retrieval to fail for Sqlite3

[kiranandcode]

See title (using Caqti version 1.7.0, Sqlite3 version 3.34.1 2021-01-20 14:10:07)

Minimized example of code triggering the error:

open Containers

let (let+) x f = Result.flat_map f x

let res =
  let+ (module DB) = Caqti_blocking.connect (Uri.make ~scheme:"sqlite3" ~path:":memory:" ()) in

  let+ () = DB.exec (Caqti_request.exec Caqti_type.unit "
CREATE TABLE example (id STRING)
") () in

  let id = "123456" in
  let+ () =
    DB.exec (Caqti_request.exec Caqti_type.string "
INSERT INTO example (id) VALUES (?)
") id in
  let+ id' =
    DB.find (Caqti_request.find Caqti_type.string Caqti_type.string "
SELECT id FROM example WHERE id = ?
") "123456" in
  assert String.(id = id');
  Result.return ()

let () = match res with
  | Error e -> print_endline (Caqti_error.show e)
  | Ok () -> ()

prints out the following:

Cannot decode string from <sqlite3::memory:>: Field type not supported and no fallback provided.

[reynir]

Use TEXT instead of STRING. STRING is not a sqlite3 data type, and sqlite3 is very loose with types. You will get the same behavior if you replace STRING with QUUX.

[reynir]

Note that a declared type of "FLOATING POINT" would give INTEGER affinity, not REAL affinity, due to the "INT" at the end of "POINT". And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

https://www.sqlite.org/datatype3.html

[kiranandcode]

Ah, I see thanks for the prompt response, mea culpa. I should have double checked the SQLite3 page.