golang db scan comparison

main.go

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"

	_ "github.com/mattn/go-sqlite3"
)

func getDB() *sql.DB {
	db, _ := sql.Open("sqlite3", ":memory:")
	if _, err := db.Exec("CREATE TABLE test (id INT NOT NULL, name VARCHAR(255))"); err != nil {
		panic(err)
	}

	if _, err := db.Exec("INSERT INTO test (id, name) VALUES (123, null),(456,'bob')"); err != nil {
		panic(err)
	}

	return db
}

type user struct {
	Id   int    `json:"id"`
	Name string `json:"name"`
}

type userNPtr struct {
	Id   int     `json:"id"`
	Name *string `json:"name"`
}

type userNEmpty struct {
	Id   int    `json:"id"`
	Name string `json:"name,omitempty"`
}

type userNPtrEmpty struct {
	Id   int     `json:"id"`
	Name *string `json:"name,omitempty"`
}

func main() {
	db := getDB()

	scanners := []struct {
		name string
		f    func(*sql.Rows) (string, []byte)
	}{
		{"scanUser", scanUser},
		{"scanUser_ptr", scanUser_ptr},
		{"scanUserNPtr", scanUserNPtr},
		{"scanUserNEmpty", scanUserNEmpty},
		{"scanUserNEmpty_ptr", scanUserNEmpty_ptr},
		{"scanUserNPtrEmpty", scanUserNPtrEmpty},
	}

	rows, err := db.Query("SELECT id,name FROM test")
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	for rows.Next() {
		for _, scanner := range scanners {
			printfv, jsonb := scanner.f(rows)
			fmt.Printf("%-20s %-30s %s\n", scanner.name, printfv, jsonb)
		}
	}
}

func scanUser(rows *sql.Rows) (string, []byte) {
	var u user
	var name sql.NullString

	rows.Scan(&u.Id, &name)
	if name.Valid {
		u.Name = name.String
	}

	jsonString, _ := json.Marshal(u)
	return fmt.Sprintf("%+v", u), jsonString
}

func scanUser_ptr(rows *sql.Rows) (string, []byte) {
	var u user
	var name *string

	rows.Scan(&u.Id, &name)
	if name != nil {
		u.Name = *name
	}

	jsonString, _ := json.Marshal(u)
	return fmt.Sprintf("%+v", u), jsonString
}

func scanUserNPtr(rows *sql.Rows) (string, []byte) {
	var u userNPtr

	rows.Scan(&u.Id, &u.Name)

	jsonString, _ := json.Marshal(u)
	return fmt.Sprintf("%+v", u), jsonString
}

func scanUserNEmpty(rows *sql.Rows) (string, []byte) {
	var u userNEmpty
	var name sql.NullString

	rows.Scan(&u.Id, &name)
	if name.Valid {
		u.Name = name.String
	}

	jsonString, _ := json.Marshal(u)
	return fmt.Sprintf("%+v", u), jsonString
}

func scanUserNEmpty_ptr(rows *sql.Rows) (string, []byte) {
	var u userNEmpty
	var name *string

	rows.Scan(&u.Id, &name)
	if name != nil {
		u.Name = *name
	}

	jsonString, _ := json.Marshal(u)
	return fmt.Sprintf("%+v", u), jsonString
}

func scanUserNPtrEmpty(rows *sql.Rows) (string, []byte) {
	var u userNPtrEmpty

	rows.Scan(&u.Id, &u.Name)

	jsonString, _ := json.Marshal(u)
	return fmt.Sprintf("%+v", u), jsonString
}

output

# go run main.go
scanUser             {Id:123 Name:}                 {"id":123,"name":""}
scanUser_ptr         {Id:123 Name:}                 {"id":123,"name":""}
scanUserNPtr         {Id:123 Name:<nil>}            {"id":123,"name":null}
scanUserNEmpty       {Id:123 Name:}                 {"id":123}
scanUserNEmpty_ptr   {Id:123 Name:}                 {"id":123}
scanUserNPtrEmpty    {Id:123 Name:<nil>}            {"id":123}
scanUser             {Id:456 Name:bob}              {"id":456,"name":"bob"}
scanUser_ptr         {Id:456 Name:bob}              {"id":456,"name":"bob"}
scanUserNPtr         {Id:456 Name:0xc20800a850}     {"id":456,"name":"bob"}
scanUserNEmpty       {Id:456 Name:bob}              {"id":456,"name":"bob"}
scanUserNEmpty_ptr   {Id:456 Name:bob}              {"id":456,"name":"bob"}
scanUserNPtrEmpty    {Id:456 Name:0xc20800a980}     {"id":456,"name":"bob"}