proposal: database/sql: support more nullable types

[mcandre]

I'm not entirely sure why the database/sql package requires using special types to represent nullable fields, as opposed to plain old pointer types. Why don't all of the built-in Go types just feature sql.Scanner implementations, including arbitrarily deep pointers?

In any case, the API here is limited to a paltry set of types, compared to the full set of built-in types actually available to Go. We are missing nullable:

• Unsigned integers
• Non-64 bit width integers
• Timestamps
• Runes
• Structs (esp. for JSON SQL columns)

Sure, we can probably skip over complex numbers due to SQL generally not supporting complex numbers as standard types. But the aforementioned types are very common to use, so it would be better to round out the halfway marshaling and actually support these. As of now, users have to write a lot of custom code to manually marshal and truncate these types, with plenty of risk for error compared to built-in API's.

[andybons]

@bradfitz @kardianos @kevinburke

[kardianos]

• Unsigned integers in SQL databases are rare / often not supported.
• NullTime proposal is accepted I need to implement.
• Runes.... I don't think we need to support this, I'd want a use case.
• Structs... That kinda lands in the land of database specific implementations. Though I'd be happy to know of a specific use case.

I'd like to make a better API for passing values out and I've sketched some ideas out, but nothing final yet.

If you want to make a case for having a NullRune type or if you want to do a survey of databases that support complex structures like JSON, please do so.

I have other strategies I used to avoid these problems in general, so feedback on this area is great. Thanks!

[rsc]

NullTime is on its way. The others seem very specialized. I don't even know how we would handle all structs.

Note that for an arbitrary type T, the way to write sql.NullT is *T (pointer to T).

We can leave this on hold for generics but probably the answer is just use a pointer type. Those are nullable automatically.

[cespare]

@kardianos the main internally-defined sql.Scanner/driver.Value type we use all over the place is NullInt32. This is because we want to use a 32-bit integer in our code for IDs which are 32 bits in the DB. So (in postgres):

integer NOT NULL -> int32
bigint NOT NULL -> int64
integer -> NullInt32
bigint -> NullInt64

(Naturally we prefer NullInt32 to turning a bunch of small integers into pointers.)

[gopherbot]

Change https://golang.org/cl/174178 mentions this issue: database/sql: add NullInt32

[kardianos]

I mailed a CL to support int32. I don't see any other nullable types being added as they are either specific to the database or uncommon. Int32 (INT or INTEGER) is commonly used in many databases and database schemas today.

[kardianos]

I've closed this issue. I really can't see adding any other Nullable types in the Go std lib. If you think differently please open up a new issue showing how it will be useful on a type by type basis.

[mei-rune]

why not add Nullable?

type Nullable struct {
	Value interface{}
	Valid bool
}

func (s *Nullable) Scan(src interface{}) error {
	if src == nil {
	        s.Valid = false
		return nil
	}
	s.Valid = true
	return convert.ConvertAssign(s.Value, src)
}

[nicola-spb]

@kardianos Hello. Why Value() in NullInt32 returns int64????? It's really strange.

[kardianos]

@nicola-spb As far as I can tell, it doesn't. Can you show your code or reproduce the issue? Or even link to the database/sql code that you are talking about?

[nicola-spb]

@kardianos golang 1.13 2177bfb#diff-5b3d7257e10f4efeecb9517a1b6d0c28R260

I use it like this

func ToInt32(number sql.NullInt32) *int32 {
	var res int32
	if n, _ := number.Value(); n != nil {
		res = n.(int32)
		return &res
	} else {
		return nil
	}
}

and wrap results from database

tWrapper := &TWrapper{
	Id:        wrappers.ToInt32(t.Id),
}

[kardianos]

Don't use value method, that is for driver. Use field.

[nicola-spb]

I use sqlx. What is field? Can you give example please?

[kardianos]

Use https://godoc.org/database/sql#NullInt32.Int32

The field Int32 in the NullInt32 struct.

[nicola-spb]

Ok. Thank you.

P.S. It's better of course. I rewrote functions.