[solved] Problem in insert into Postgres db

Getting Help Code Review
1

Hey Guys,
when I want insert into db I got an error.

my method for insert:

func RegisterUser(db *sql.DB, w http.ResponseWriter, r *http.Request) {

user := model.User{}

decoder := json.NewDecoder(r.Body)

if err := decoder.Decode(&user); err != nil {

respondError(w, http.StatusBadRequest, err.Error())

return

}

defer r.Body.Close()

sqlStatement := `

INSERT INTO users (id, name, avatar, mobile, birth_day, identify, cart, credit, type, password)

VALUES ($1, $2, $3, $4, $5, &6, &7, &8, &9)

RETURNING id`

stmt, err := db.Prepare(sqlStatement)

fmt.Println("******************here2", user)

if err != nil {

log.Fatal(err)

}

defer stmt.Close()

var userID int

if err := stmt.QueryRow(sqlStatement, user.ID, user.Name, user.Avatar, user.Mobile, user.BirthDay, user.Identify, user.Cart, user.Credit, user.Type, user.Password); err != nil {

//respondError(w, http.StatusInternalServerError, err.Error())

log.Fatal(err)

return

}

fmt.Println("New record ID is:", userID)

respondJSON(w, http.StatusCreated, user)

}

and this is error log:
http: panic serving [::1]:39878: runtime error: invalid memory address or nil pointer dereference
goroutine 18 [running]:

more explain:
I have User struct with this field:

type User struct {
	Base
	Name        string `json:"name"`
	Avatar      string `json:"avatar"`
	Email       string `db:"unique;not null";json:"email"`
	Mobile      string `json:"mobile"`
	BirthDay    string `json:"birthday"`
	Identify    string `json:"identify"`
	Cart        string `json:"cart"`
	Credit      string `json:"credit"`
	Type        int    `json:"type"`
	Password    string `json:"password"`
	Permissions string
}

and my Base in User struct is:

import (

"github.com/jinzhu/gorm"

uuid "github.com/satori/go.uuid"

)

type Base struct {

ID uuid.UUID `db:"type:uuid;primary_key;not null;" json:"-"`

}

func (base *Base) BeforeCreate(scope *gorm.Scope) error {

uuid, err := uuid.NewV4()

if err != nil {

return err

}

return scope.SetColumn("ID", uuid)

}

and that Base is to make UUID for every User record.
but I got an error in this part:

    stmt, err := db.Prepare(sqlStatement)

	fmt.Println("******************here2", user)

	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()

may please help me to fix this section for insert data into database?

1 Like
2

as I check db is nil. before this, I use gorm and some Init method and App struct. after some change: my code is now like this in app.go:
type App struct {
Router *mux.Router
DB *sql.DB
}
and in Initialize method I have these codes:
// App initialize with predefined configuration
func (a *App) Initialize(config *config.Config) {
dbURI := fmt.Sprintf(“host=%s port=%s user=%s dbname=%s password=%s”,
config.DB.Host,
config.DB.Port,
config.DB.User,
config.DB.DbName,
config.DB.Password)

	db, err := sql.Open(config.DB.Dialect, dbURI)

	if err != nil {
		log.Fatal("Could not connect database")
	}

	defer db.Close()

	err = db.Ping()
	if err != nil {
		panic(err)
	}

	fmt.Println("Successfully connected!")

	//a.DB = model.DBMigrate(db)
	a.Router = mux.NewRouter()
	a.setRouters()

}

as the above code when I use gorm, a.DB code assigned with some value but now I just commented on this line. I must set what value for this? because as I check a.DB is nil and I pass this to register method and get an error.

1 Like
3

I fix this part with this code:
a.DB, err = sql.Open(config.DB.Dialect, dbURI).
now I check a.DB in insert method and is not nil. but when I want Insert Into table I got this error that said database closed
Successfully connected!
&{{host=localhost port=5432 user=postgres dbname=testdb password=1234 0xa3cd40} 0 {0 0} [0xc4201ec280] map[] 0 1 0xc42007e120 0xc4201b4e40 false map[0xc4201ec280:map[0xc4201ec280:true]] map[] 0 0 0 <nil> 0x54ee00}
2019/10/23 11:57:24 http: panic serving [::1]:42438: sql: database is closed

https://play.golang.org/p/szzXIhEdZzk
I make tbluser in database with exact these columns. but I can’t understand why It’s not work

12:01 PM

with this refrence : https://www.calhoun.io/inserting-records-into-a-postgresql-database-with-gos-database-sql-package/

12:01 PM

1 Like
4

in my app.go I close the connection. and this happens to make a crash app. I remove that line and don’t close my connection anymore. every-thing is ok now.

func RegisterUser(db *sql.DB, w http.ResponseWriter, r *http.Request) {

	user := model.User{}
	decoder := json.NewDecoder(r.Body)
	if err := decoder.Decode(&user); err != nil {
		respondError(w, http.StatusBadRequest, err.Error())
		return
	}
	defer r.Body.Close()

	stmt, err := db.Prepare("INSERT INTO users(id,name,avatar,mobile, email,birth_day, identify, cart, credit, password, type) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11);")

	fmt.Println(db)
	if err != nil {
		fmt.Println("not work")
		panic(err)
	}

	uuid, err := uuid.NewV4()

	res, err := stmt.Exec(uuid, user.Name, user.Avatar, user.Mobile, user.Email, user.BirthDay, user.Identify, user.Cart, user.Credit, user.Password, user.Type)

	fmt.Println(res.LastInsertId())

	//respondJSON(w, http.StatusCreated, user)
}

and this is my register method for insert user into DB. I hope this helps someone.

1 Like