How to write the data received in JSON format to the database?

Question

MySQL

Hello. As part of the training, I received a task to get 5 posts and comments on them from the site https://jsonplaceholder.typicode.com/ . I have already read both the official documentation of sql and json packages, and as soon as I did not dodge, a problem arises: json decodes an array only in []interface{} Whereas db.Exec accepts only interface{}.
My code currently looks like this https://play.golang.org/p/b08NgIPbF_Q . You need to do it with the help of https://pkg.go.dev/github.com/go-sql-driver/mysql#... ", so the same GORM is not an option, although it had about the same problem

Answer 1

Note that you are requesting one post, but the response is an array.
This can be seen from the brackets at the beginning and end of the answer "[" .... "]",

[
  {
    "userId": 1,
    "id": 1,
    "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
    "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
  }
]

Those. you need to decode the response as an array, not a separate post
Fixed bugs and refactored your code.
It is also better not to use global variables.
I hope this will become a little clearer :)
If something is not clear - write, I will try to help.
In general, this is how it will work.

package main

import (
    "database/sql"
    "encoding/json"
    "log"
    "net/http"

    _ "github.com/go-sql-driver/mysql"
)

type Post struct {
    Userid int    `json:"userId"`
    ID     int    `json:"id"`
    Title  string `json:"title"`
    Body   string `json:"body"`
}

func main() {
    posts, err := fetchPosts("https://jsonplaceholder.typicode.com/posts?id=1")
    if err != nil {
        log.Fatal(err)
    }

    db, err := sql.Open("mysql", "root:[email protected]/Posts")
    if err != nil {
        log.Fatal(err)
    }

    err = savePosts(db, posts)
    if err != nil {
        log.Fatal(err)
    }
}

func fetchPosts(url string) ([]Post, error) {
    resp, err := http.Get(url)
    if err != nil {
        return nil, err
    }

    var posts []Post
    err = json.NewDecoder(resp.Body).Decode(&posts)
    return posts, err
}

func savePosts(db *sql.DB, posts []Post) error {
    for _, post := range posts {
        _, err := db.Exec("insert into post (id, user_id, title, body) values(?, ?, ?, ?)",
            post.ID, post.Userid, post.Title, post.Body)
        if err != nil {
            return err
        }
    }

    return nil
}