Skip to content

Instantly share code, notes, and snippets.

@rsudip90
Last active September 5, 2024 15:29
Show Gist options
  • Save rsudip90/45fad7d8959c58bcc91d464873b50013 to your computer and use it in GitHub Desktop.
Save rsudip90/45fad7d8959c58bcc91d464873b50013 to your computer and use it in GitHub Desktop.
How I handled the null possible value in a sql database row in golang?
package main
import (
"database/sql"
"encoding/json"
"fmt"
"reflect"
"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"`
}
// CUSTOM NULL Handling structures
// NullInt64 is an alias for sql.NullInt64 data type
type NullInt64 sql.NullInt64
// Scan implements the Scanner interface for NullInt64
func (ni *NullInt64) Scan(value interface{}) error {
var i sql.NullInt64
if err := i.Scan(value); err != nil {
return err
}
// if nil then make Valid false
if reflect.TypeOf(value) == nil {
*ni = NullInt64{i.Int64, false}
} else {
*ni = NullInt64{i.Int64, true}
}
return nil
}
// NullBool is an alias for sql.NullBool data type
type NullBool sql.NullBool
// Scan implements the Scanner interface for NullBool
func (nb *NullBool) Scan(value interface{}) error {
var b sql.NullBool
if err := b.Scan(value); err != nil {
return err
}
// if nil then make Valid false
if reflect.TypeOf(value) == nil {
*nb = NullBool{b.Bool, false}
} else {
*nb = NullBool{b.Bool, true}
}
return nil
}
// NullFloat64 is an alias for sql.NullFloat64 data type
type NullFloat64 sql.NullFloat64
// Scan implements the Scanner interface for NullFloat64
func (nf *NullFloat64) Scan(value interface{}) error {
var f sql.NullFloat64
if err := f.Scan(value); err != nil {
return err
}
// if nil then make Valid false
if reflect.TypeOf(value) == nil {
*nf = NullFloat64{f.Float64, false}
} else {
*nf = NullFloat64{f.Float64, true}
}
return nil
}
// NullString is an alias for sql.NullString data type
type NullString sql.NullString
// Scan implements the Scanner interface for NullString
func (ns *NullString) Scan(value interface{}) error {
var s sql.NullString
if err := s.Scan(value); err != nil {
return err
}
// if nil then make Valid false
if reflect.TypeOf(value) == nil {
*ns = NullString{s.String, false}
} else {
*ns = NullString{s.String, true}
}
return nil
}
// NullTime is an alias for mysql.NullTime data type
type NullTime mysql.NullTime
// Scan implements the Scanner interface for NullTime
func (nt *NullTime) Scan(value interface{}) error {
var t mysql.NullTime
if err := t.Scan(value); err != nil {
return err
}
// if nil then make Valid false
if reflect.TypeOf(value) == nil {
*nt = NullTime{t.Time, false}
} else {
*nt = NullTime{t.Time, true}
}
return nil
}
// 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
}
// 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
}
// 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
}
// 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
}
// 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 {
s := string(b)
// s = Stripchars(s, "\"")
x, err := time.Parse(time.RFC3339, s)
if err != nil {
nt.Valid = false
return err
}
nt.Time = x
nt.Valid = true
return nil
}
// MAIN program starts here
func main() {
db, err := sql.Open("mysql", "user:pass@/test?charset=utf8")
if err != nil {
fmt.Println("database could not opened!!!!")
fmt.Println(err.Error())
return
}
// read articles
rows, err := db.Query("SELECT * FROM Article")
if err != nil {
fmt.Println("Query failed.....")
fmt.Println(err.Error())
return
}
for rows.Next() {
var a Article
if err = rows.Scan(&a.ID, &a.Title, &a.PubDate, &a.Body, &a.User); err != nil {
fmt.Println("Scanning failed.....")
fmt.Println(err.Error())
return
}
fmt.Printf("Article Instance := %#v\n", a)
articleJSON, err := json.Marshal(&a)
if err != nil {
fmt.Errorf("Error while marshalling json: %s", err.Error())
fmt.Println(err.Error())
return
} else {
fmt.Printf("JSON Marshal := %s\n\n", articleJSON)
}
}
db.Close()
}
use test;
CREATE TABLE Article(
`id` int NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`pub_date` datetime DEFAULT NULL,
`body` text,
`user_id` int DEFAULT NULL,
PRIMARY KEY(id)
);
INSERT INTO Article(`title`) VALUES("first article");
@inix
Copy link

inix commented Feb 25, 2020

Great code,it help a lot.I have change from pointer to this method.

@rsudip90
Copy link
Author

I'm glad that it helped you a lot. Thought, I would recommend to follow this gist instead of the above one.

@lanewebchina
Copy link

Great code,thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment