Automatically infer nullable columns

[joksas]

I love cornucopia! I used sqlc in Go, and this is exactly the thing I needed in Rust!

Having used sqlc though, I was wondering whether the nullability of columns could be handled automatically. At the moment, when I use cornucopia, this is the only thing that results in panics in my code because I forget to mark certain columns as nullable. In sqlc, this seems to be inferred automatically (pointers are returned for nullable columns). Could this also be done in cornucopia or is there some technical limitation preventing this?

[Virgiel]

We'd love to do that! However, this is not something that postgres natively supports and all other projects rely on simulation or loosy inference. This makes this feature unreliable. In addition, Postgres's dialect and SQl functions are a huge area that is very difficult to simulate and this is why many projects do not support all Postgres features. This is a bigger problem than the cornucopia project as a whole. Our current solution is stupid but supports the craziest queries that Postgres supports.

We could have made all columns nullable by default, which would have made all the panic points explicit. But we chose not to make the columns nullable by default as this is the most common case in our projects and therefore reduce the number of manual fixes (we may reconsider this if many users code panic because of this).

I've thought about making all columns nullable by default and starting to deduce the simplest case, gradually making our system easier to use while supporting the whole of Postgres, but I'm running out of time for this project. If anyone is interested in doing this in Rust, we'd be really interested!

[Virgiel]

I really like the way sqlc supports overriding generated types, and we'll be using this as inspiration for implementing this feature.

[erikschul]

What about checking the table schema?

 select column_name, is_nullable
 from INFORMATION_SCHEMA.COLUMNS where table_name ='mytable';

[LouisGariepy]

@erikschul This is possible for simple table queries, but Postgres doesn't expose nullability for more complex table expressions AFAIK.

I believe this is possible to implement this for simple table queries, but even then we'd need to implement some parsing logic to recognize such queries. This is a non-trivial amount of work. I'm open to suggestions though.

[erikschul]

Right, and inline queries like SELECT null, views, etc.
not to mention inline conditional statements CASE.
Difficult!