Input parameters not taking nullability into consideration

[justinrassier]

I have an update statement that sets a nullable text field:

UPDATE my_table
SET my_nullable_field = $1
WHERE id = $2

But the generated function from squirrel doesn't take into consideration the nullable field and requires me to pass in a String instead of an Option(String)

pub fn update_exam_instance_question(db, arg_1, arg_2, arg_3) {
  let decoder = decode.map(decode.dynamic, fn(_) { Nil })

  "UPDATE my_table
SET my_nullable_field = $1 
WHERE id = $2
"
  |> pgo.execute(
    db,
    [pgo.text(arg_1) // <--   expected  pgo.nullable(pgo.text, arg_1)
   , pgo.int(arg_2))]
    decode.from(decoder, _),
  )
}

I'm not sure if there is any way to hint to squirrel that this should be nullable or not

[giacomocavalieri]

Ah that's interesting, I need to look a bit into it! There probably is a way to tell if a field in an insert/update query is a nullable column 🤔

[justinrassier]

Doing a little searching, it seems like the query itself can't tell you anything about the nullability, but you can query the schema and get that info

SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 'my_table'
  AND column_name = 'my_nullable_field';

 column_name      | is_nullable
------------------|-------------
 my_nullable_field| YES

[giacomocavalieri]

Unfortunately I cannot really use that to implement this because squirrel doesn't implement a sql parser. I can only use the info the Postgres server sends me once I ask it to prepare the query and it looks like it's not carrying around any info about the parameter's origin (if it, like in this example, a nullable column): I only get its base non-nullable type, text in your example.

So I'm not sure this can actually be implemented, I'll have to look more into how sqlx does it (if it actually does it at all)

[hfjallemark]

I believe sqlx runs an explain query to get nullability:

After preparing, the Postgres driver will first look up the result columns in their source table and check if they have a NOT NULL constraint. Then, it will execute EXPLAIN (VERBOSE, FORMAT JSON) <your query> to determine which columns come from half-open joins (LEFT and RIGHT joins), which makes a normally NOT NULL column nullable. Since the EXPLAIN VERBOSE format is not stable or completely documented, this inference isn't perfect. However, it does err on the side of producing false-positives (marking a column nullable when it's NOT NULL) to avoid errors at runtime.

https://github.com/launchbadge/sqlx/blob/main/FAQ.md#how-do-the-query-macros-work-under-the-hood