main_test.go

type DBJSONMap map[string]interface{}

// scanning a null column value is problematic how do we know that it was null?
// for structs, this is easy, we can just slap an IsNull field on it and set it
// in the Scanner, but for map[string]interface{} ...
func NewDBJSONMap() DBJSONMap {
	return make(map[string]interface{})
}

func (d DBJSONMap) IsNull() bool {
	if v, ok := d["__jsondbmap.isnull"]; ok {
		return v.(bool)
	}
	return false
}

func (d DBJSONMap) Value() (driver.Value, error) {
	b, err := json.Marshal(d)
	if err != nil {
		return nil, err
	}
	return driver.Value(b), nil
}

func (d *DBJSONMap) Scan(src interface{}) error {
	var source []byte
	switch src.(type) {
	case []byte:
		(*d)["__jsondbmap.isnull"] = false
		source = src.([]byte)
	case nil:
		(*d)["__jsondbmap.isnull"] = true
		return nil
	default:
		return errors.New("can't scan: not []byte")
	}
	return json.Unmarshal(source, d)
}

func TestJSONMap(t *testing.T) {
	err := godotenv.Load()
	if err != nil {
		t.Fatal(err)
	}
	db, err := sqlx.Connect("postgres", "")
	if err != nil {
		t.Fatal(err)
	}
	defer func() {
		db.Exec("DROP TABLE documents")
	}()

	schemaSQL := `CREATE TABLE documents (
		id SERIAL PRIMARY KEY NOT NULL,
		created_at timestamptz DEFAULT CURRENT_TIMESTAMP,
		doc jsonb)`

	_, err = db.Exec(schemaSQL)
	if err != nil {
		t.Fatal(err)
	}
	now := time.Now()
	d := NewDBJSONMap()
	d["name"] = "Art Gillespie"
	d["count"] = 12345
	d["ts"] = now
	d["ok"] = true
	_, err = db.Exec("INSERT INTO documents (doc) VALUES ($1)", d)
	if err != nil {
		t.Fatal(err)
	}
	rData := NewDBJSONMap()
	err = db.QueryRow("SELECT doc FROM documents LIMIT 1").Scan(&rData)
	if err != nil {
		t.Fatal(err)
	}
	pretty.Print(rData)
	/*
		if now.Truncate(time.Microsecond).String() != rData["ts"] {
			t.Errorf("timestamp unexpected %s != %s", now.Truncate(time.Microsecond).String(), rData["ts"])
		}
	*/
	_, err = db.Exec("INSERT INTO documents (doc) VALUES ($1)", nil)
	if err != nil {
		t.Fatal(err)
	}
	err = db.QueryRow("SELECT doc FROM documents WHERE doc IS NULL").Scan(&rData)
	if err != nil {
		t.Fatal(err)
	}
	if !rData.IsNull() {
		t.Errorf("expected rData.IsNull == true")
	}
}