Feature: Nullability Type Hints

[greg-blackcurrent]

What do you want to change?

Nullability Type Hints

Queries such as the following fail if calling a function which can return a null value.

select foo::text from bar();

Workaround

One workaround, for Postgres at least, is to create a domain type in the database.

create domain text_nullable AS text;

And then add an override for this type in the sqlc config.

overrides:
  - db_type: text_nullable
    go_type:
      type: string
      pointer: true

Then this works:

select foo::text_nullable from bar();

Nullability Hints - Potential Proposals

I wonder if there's a reasonable way to modify sqlc to pass in a nullability hint within a query?

A couple of ideas...

Create a new sqlc macro.

select sql.nullable(foo::text) as foo from bar();

Add comment based hinting.

select
    foo::text -- sqlc:nullable
from bar();

Create an option which recognises specific suffixes (This would conflict if these names do exist in the database).

select foo::text_nullable from call_a_function();

Question mark syntax. Harder to implement, but this may be nicer to read. Would probably require a change to
pg_query_go. Though it may be possible to extract the question marks using a simple scanner pass before passing to pg_query_go.

select foo::text? as foo from call_a_function();

Another option is putting detailed type and parameter information in the header comment. i.e. the @param proposal.
Personally, I think I actually prefer type casts and hints in the queries.

#2800

Related Issues

Many of these can be fixed by better nullability inference, but adding nullability hints, could solve these in the
meantime.

#4121
#3274
#4336
#2284
#4262
#2800

Implementation Notes

In a nutshell, I'm looking for a way to strip something out of the query, in a way which doesn't affect the database,
and then pass it through to compiler.Column.NotNull. See: sqlc/internal/compiler/to_column.go.

func toColumn(n *ast.TypeName) *Column {
	if n == nil {
		panic("can't build column for nil type name")
	}
	typ, err := ParseTypeName(n)
	if err != nil {
		panic("toColumn: " + err.Error())
	}
	arrayDims := arrayDims(n)
	return &Column{
		Type:      typ,
		DataType:  strings.TrimPrefix(astutils.Join(n.Names, "."), "."),
		NotNull:   true, // XXX: How do we know if this should be null?
		IsArray:   arrayDims > 0,
		ArrayDims: arrayDims,
	}
}

Happy to have a crack at this, if others think it has value.

What database engines need to be changed?

No response

What programming language backends need to be changed?

No response