How to insert multiple values to database

Question

In my Go application I have such route:

router.HandleFunc("/api/users_groups_relationship/{user_id:[0-9]+}", controllers.CreateUsersGroupsRelationship).Methods("POST")

I make POST request. In body of that request I send such JSON which looks like this:

{
    "groups": [1, 2, 3] 
}

As you can see groups key has array of ids as value. User can be in several groups. I am trying to insert multiple values to PostgreSQL database.

How to get the value of a specific key in request body?
Is there any other best way to insert multiple values in database by Go?

My code:

var CreateUsersGroupsRelationship  = func(responseWriter http.ResponseWriter, request *http.Request) {
    vars := mux.Vars(request)

    userID := vars["user_id"]

    fmt.Println(userID)

    var groups []int

    groups = request.Body("groups") // ???

    for i := 1; i < len(groups); i++ {
        fmt.Println(groups[i])
        _, _ := database.DBSQL.Exec("INSERT INTO users_groups (user_id, group_id) VALUES ($1, $2);", userID, groups[i])
    }

    utils.ResponseWithSuccess(responseWriter, http.StatusOK, "All new records successfully created.")
}

What's the Content-type of body? is it json? – Saurav Prakash Mar 07 '19 at 07:49 — Saurav Prakash, Mar 07 '19 at 07:49
Yes, your are right. `Content-Type` is `application/json`. – Nurzhan Nogerbek Mar 07 '19 at 07:50 — Nurzhan Nogerbek, Mar 07 '19 at 07:50

Saurav Prakash · Accepted Answer · 2019-03-07T08:02:41.657

2

You can define a struct for Request object and then unmarshal JSON into it.

package main

import (
    "fmt"
    "github.com/gorilla/mux"
    "net/http"
    "encoding/json"
)

//Request is our request body.
type Request struct {
    Groups []int `json:"groups"`
}

//JsonTest1 is the http handler.
func JsonTest1(w http.ResponseWriter, r *http.Request) {
    req := new(Request)
    //decode request to struct.
    if err := json.NewDecoder(r.Body).Decode(&req); err != nil{
        w.WriteHeader(400) //bad request
    }

    w.WriteHeader(200)
    b, _ := json.Marshal(req)
    w.Write(b)
    w.Header().Set("Content-Type", "application/json; charset=utf-8")
}

func main(){
    fmt.Printf("starting backend server\n")

    root := mux.NewRouter()
    root.HandleFunc("/foo", JsonTest1)

    webServer := &http.Server{Addr: ":4000", Handler: root}

    webServer.ListenAndServe()
}

In case your body is quite generic, you can also unmarshal to map[string]interface{}.

Try with

curl -XPOST -d '{"groups": [1]}' http://localhost:4000/foo

edited Mar 07 '19 at 08:02

answered Mar 07 '19 at 07:53

Saurav Prakash

1,880
1
13
24

Thank you for your answer. I tried your code right now. `fmt.Println(req.Groups)` return empty array. Do you have any ideas why it happens? – Nurzhan Nogerbek Mar 07 '19 at 07:59
@NurzhanNogerbek can you paste your request body here? the above code will work for body: {"groups": [1]} – Saurav Prakash Mar 07 '19 at 08:03
Finally I notice that it's my mistake. In JSON body I make typo. Thank you for your help! How do your think is this best idea to insert data one by one to database as I am trying to make? – Nurzhan Nogerbek Mar 07 '19 at 08:22
1

you should batch insert rather if your db provides it. for example in postgres i could do: INSERT INTO user_group (id, group_id) VALUES (1,1), (1,2); – Saurav Prakash Mar 07 '19 at 08:28
I also thought about it but as you can see array in JSON body always contains different number of elements. For thats why SQL statement must be dynamic. So how I need to change my code inside loop to make such dynamic SQL as you adviced? Do you have any ideas? – Nurzhan Nogerbek Mar 07 '19 at 08:37
@NurzhanNogerbek check this one https://stackoverflow.com/questions/12486436/golang-how-do-i-batch-sql-statements-with-package-database-sql/25192138#25192138 – Saurav Prakash Mar 07 '19 at 08:46

How to insert multiple values to database

1 Answers1