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

[giacomocavalieri]

So I'm turning this into a discussion since I believe there's no way to tell if a query parameter is bound to a nullable column (not even by looking at the query explanation).
So this needs a bit more care and consideration to make its way into squirrel. I'm really interested in adding a way to tell squirrel if a query parameter should be nullable; some considerations:

• it cannot be a custom extension to the sql syntax (like $1?, ...) because it would go against squirrel's philosophy of "it's just plain SQL you can reuse everywhere"
• so it will probably need to be given in the form of some special comment before the query

Louis pointed out we could first ask the Postrgres folks if such an API to solve our problem could be added, that would for sure be the best possible outcome! I think I'll reach out soon and see how that goes