Golang & PostgreSQL | sql | sqlx (Transactions) | © | ©

See Labs

• $GOPATH/f06ygo/db/postgres/...
• $DEV/go/labs/4-sql/...

Prepared Statements

Use or Avoid?

Nullable (db) Field Values | SQL Null Types | © | ©

TL;DR

Use pointer field types (*T) for any nullable field. That way the end-to-end interface is NULL to/from JSON null, while at Golang (struct) the value is whatever the type's zero value (and nil at the pointer itself). Optionally, additionally, use the json tag, json:"foo,omitempty", to omit any of zero-value from the JSON result upon json.Marshal().

Guidelines:

• Avoid db NULLable keys.
• Use pointer if db key is NULLable.
• Use pointer if JSON key is optional and data layer requires distinction between null/(no key) and zero-value cases; or client requires such between falsey and null/(no key).

Roundtrip Data-type Flow (Type vs *Type)

JSON <==> Golang <==> DB/SQL

From json.Decode to INSERT, to SELECT, to json.Marshal, per case (no-key vs zero-value).

Database field Foo *string type scans to Golang nil, and so JSON-marshals to missing key if json:"foo,omitempty", else to "foo":null. This is the cleanest scheme; db NULL to/from JSON null (or absent key).

Handling Schemes

From best to worst:

1. Foo *string type scans (sql pkg) and decodes (json pkg) to Golang nil, and so JSON-marshals to missing key if json:"foo,omitempty", else to "foo":null. This is the cleanest scheme; db NULL to/from JSON null (or absent key); no need for Nil UUID values in its database table (which fail versioned UUID validator constraints, e.g., uuid4, though validate as uuid).

2. Foo string type fails on SELECT scan of NULL to string

   • Use Nil UUID (and NOT NULL constraint) as a proxy for NULL.

   • Handle at (un)marshal. E.g.,

     // @ Unmarshal
     if got.ID3 == "" {
         got.ID3 = dbUNIL.String
     }
     // @ Marshall
     if s.ID3 == dbUNIL.String {
         s.ID3 = ""
     }
     
     var (
         // Insert dbUNIL as proxy for NULL (validates as `uuid`, but not `uuid4`)
         // @ Golang: `uuid.Nil`; @ Postgres "uuid-oosp" extension: `uuid_nil()`
         dbUNIL sql.NullString = sql.NullString{String: fmt.Sprintf("%s", uuid.Nil), Valid: true}
     )
     

   • This is a workaround, not a genuine solution, since it prohibits (db) NULL. If no NULL fields, then simplify:

     dbUNIL := fmt.Sprintf("%s", uuid.Nil)
     

3. Foo sql.NullString type is itself a struct, and so JSON-marshals as such:

   • NULL marshals to "id3": {"String": "","Valid": false}
   • Else marshals to "id3": {"String": "1aad24fd-cf8a-4051-8863-0200f8a26616","Valid": true}

• Foo sql.NullString type fails on unmarshal of POST/PUT (from client), unless JSON is of the proper structure (above). I.e., "id3":"1aad24fd-cf8a-4051-8863-0200f8a26616" fails to unmarshal, so would require similar (un)marshal code as "Foo string" case, else client-side (Javascript) modifications.

SQL Null Types :: sql.NullString and sql.NullFloat64

type Book struct {
    isbn  string
    title  string
    author string
    price  float32
}

… we set NOT NULL constraints on the columns … If the table contained nullable fields we would need to use the sql.NullString and sql.NullFloat64 types instead.

type Book struct {
    Isbn  string
    Title  sql.NullString
    Author sql.NullString
    Price  sql.NullFloat64
}

Roundtrip Data-type Flow (Type vs *Type)

From json.Decode to INSERT, to SELECT, to json.Marshal, per case (no-key vs zero-value).

string (NOT NULLable)

• json:"foo"

  JSON	Go	db	JSON
  no foo	""	''	""
  null	""	''	""
  ""	""	''	""

  • No distinction.

• json:"foo,omitempty"

  JSON	Go	db	JSON
  no foo	""	''	no foo
  null	""	''	no foo
  ""	""	''	no foo

  • No distinction.

*string (NULLable)

• json:"foo"

  JSON	Go	db	JSON
  no foo	nil	NULL	null
  null	nil	NULL	null
  ""	""	''	""

  • No mutation (Nearly).

• json:"foo,omitempty"

  JSON	Go	db	JSON
  no foo	nil	NULL	no foo
  null	nil	NULL	no foo
  ""	""	''	""

  • No mutation (Nearly).

… choose the desired API dynamic (per key).

Pairing types across app boundaries

• @ Golang, currency may be float32 or currency.Unit{USD}