Last active
January 3, 2024 21:46
-
-
Save rsudip90/022c4ef5d98130a224c9239e0a1ab397 to your computer and use it in GitHub Desktop.
database rows null handling in go by extending types
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package main | |
import ( | |
"database/sql" | |
"encoding/json" | |
"fmt" | |
"log" | |
"time" | |
"github.com/go-sql-driver/mysql" | |
) | |
// Article struct | |
type Article struct { | |
ID int `json:"id"` | |
Title string `json:"title"` | |
PubDate NullTime `json:"pub_date"` | |
Body NullString `json:"body"` | |
User NullInt64 `json:"user"` | |
} | |
// NullInt64 is an alias for sql.NullInt64 data type | |
type NullInt64 struct { | |
sql.NullInt64 | |
} | |
// MarshalJSON for NullInt64 | |
func (ni *NullInt64) MarshalJSON() ([]byte, error) { | |
if !ni.Valid { | |
return []byte("null"), nil | |
} | |
return json.Marshal(ni.Int64) | |
} | |
// UnmarshalJSON for NullInt64 | |
// func (ni *NullInt64) UnmarshalJSON(b []byte) error { | |
// err := json.Unmarshal(b, &ni.Int64) | |
// ni.Valid = (err == nil) | |
// return err | |
// } | |
// NullBool is an alias for sql.NullBool data type | |
type NullBool struct { | |
sql.NullBool | |
} | |
// MarshalJSON for NullBool | |
func (nb *NullBool) MarshalJSON() ([]byte, error) { | |
if !nb.Valid { | |
return []byte("null"), nil | |
} | |
return json.Marshal(nb.Bool) | |
} | |
// UnmarshalJSON for NullBool | |
// func (nb *NullBool) UnmarshalJSON(b []byte) error { | |
// err := json.Unmarshal(b, &nb.Bool) | |
// nb.Valid = (err == nil) | |
// return err | |
// } | |
// NullFloat64 is an alias for sql.NullFloat64 data type | |
type NullFloat64 struct { | |
sql.NullFloat64 | |
} | |
// MarshalJSON for NullFloat64 | |
func (nf *NullFloat64) MarshalJSON() ([]byte, error) { | |
if !nf.Valid { | |
return []byte("null"), nil | |
} | |
return json.Marshal(nf.Float64) | |
} | |
// UnmarshalJSON for NullFloat64 | |
// func (nf *NullFloat64) UnmarshalJSON(b []byte) error { | |
// err := json.Unmarshal(b, &nf.Float64) | |
// nf.Valid = (err == nil) | |
// return err | |
// } | |
// NullString is an alias for sql.NullString data type | |
type NullString struct { | |
sql.NullString | |
} | |
// MarshalJSON for NullString | |
func (ns *NullString) MarshalJSON() ([]byte, error) { | |
if !ns.Valid { | |
return []byte("null"), nil | |
} | |
return json.Marshal(ns.String) | |
} | |
// UnmarshalJSON for NullString | |
// func (ns *NullString) UnmarshalJSON(b []byte) error { | |
// err := json.Unmarshal(b, &ns.String) | |
// ns.Valid = (err == nil) | |
// return err | |
// } | |
// NullTime is an alias for mysql.NullTime data type | |
type NullTime struct { | |
mysql.NullTime | |
} | |
// MarshalJSON for NullTime | |
func (nt *NullTime) MarshalJSON() ([]byte, error) { | |
if !nt.Valid { | |
return []byte("null"), nil | |
} | |
val := fmt.Sprintf("\"%s\"", nt.Time.Format(time.RFC3339)) | |
return []byte(val), nil | |
} | |
// UnmarshalJSON for NullTime | |
// func (nt *NullTime) UnmarshalJSON(b []byte) error { | |
// err := json.Unmarshal(b, &nt.Time) | |
// nt.Valid = (err == nil) | |
// return err | |
// } | |
// MAIN program starts here | |
func main() { | |
log.Println("connectiong to database...") | |
db, err := sql.Open("mysql", "user:pass@tcp(127.0.0.1:3306)/test?charset=utf8") | |
if err != nil { | |
log.Fatal(err) | |
} | |
defer db.Close() | |
// read articles | |
rows, err := db.Query("SELECT * FROM Article") | |
if err != nil { | |
log.Fatal(err) | |
} | |
defer rows.Close() | |
// loop over articles | |
for rows.Next() { | |
var a Article | |
if err = rows.Scan(&a.ID, &a.Title, &a.PubDate, &a.Body, &a.User); err != nil { | |
log.Fatal(err) | |
} | |
log.Printf("article instance := %#v\n", a) | |
articleJSON, err := json.Marshal(&a) | |
if err != nil { | |
log.Fatal(err) | |
} else { | |
log.Printf("json marshal := %s\n\n", articleJSON) | |
} | |
} | |
err = rows.Err() | |
if err != nil { | |
log.Fatal(err) | |
} | |
} |
@php-coder, you forgot to mention about defer rows.Close()
! 😉 . But thanks anyways, I updated the gist.
Have to roll back to type NullString sql.NullString
because I couldn't make it work with the validator library
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi, I found your article and I tried to follow it, thank you for your effort!
I also noticed a couple issues that are related to errors/handling and I'd like to share them with you and other readers:
defer db.Close()
instead of executing it manually at the end. In this case we would also close a connection in case of errors where we return from a functionrows.Next()
might returnedfalse
it might also means that there was an error and we can check for it withrows.Err()
functionA deeper explanation and example can be found there: http://go-database-sql.org/retrieving.html
HTH, thank you!