Golang gin rest api post and get

Question

With golang gin rest api I create title, body, date, titles and contents fields, send to postman and save database. The post operation is running successfully, but when I receive the data, I get an error like this: "error": "failed to import jobs" get doesn't work but post works code parts are as follows

main.go:

type Job struct {
    ID       int       `db:"id" json:"id"`
    Title    string    `db:"title" json:"title"`
    Body     string    `db:"body" json:"body"`
    Date     time.Time `db:"date" json:"date"`
    Titles   [4]string `db:"titles" json:"titles"`
    Contents [4]string `db:"contents" json:"contents"`
}

func main() {
r.POST("/job", func(c *gin.Context) {
    var job Job
    if err := c.ShouldBindJSON(&job); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    // insert job into database
    query := "INSERT INTO table (title, body,  titles, contents ,date) VALUES ($1, $2, $3,  $4, $5) RETURNING id"
    var id int
    err := db.QueryRow(query, job.Title, job.Body, pq.Array(job.Titles), pq.Array(job.Contents), time.Now()).Scan(&id)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to create job"})
        return
    }

    job.ID = id

    c.JSON(http.StatusOK, job)
})

r.GET("/jobs", func(c *gin.Context) {
    // retrieve all jobs from database
    rows, err := db.Query("SELECT * FROM table")
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to retrieve jobs"})
        return
    }
    defer rows.Close()

    // iterate over rows and store in slice of Jobs
    jobs := []Job{}
    for rows.Next() {
        var job Job
        err := rows.Scan(&job.ID, &job.Title, &job.Body, &job.Date, &job.Titles, &job.Contents)
        if err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to retrieve jobs"})
            return
        }
        jobs = append(jobs, job)
    }

    c.JSON(http.StatusOK, jobs)
})`

Answer 1

I've successfully managed your need with the following code. I'm gonna share the complete main.go file so you also can see which packages I used.

The main.go file

package main

import (
    "database/sql"
    "net/http"
    "time"

    "github.com/gin-gonic/gin"
    "github.com/lib/pq"
    _ "github.com/lib/pq"
)

type Job struct {
    ID       int       `db:"id" json:"id"`
    Title    string    `db:"title" json:"title"`
    Body     string    `db:"body" json:"body"`
    Date     time.Time `db:"date" json:"date"`
    Titles   [4]string `db:"titles" json:"titles"`
    Contents [4]string `db:"contents" json:"contents"`
}

func main() {
    dsn := "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable"
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        panic(err)
    }

    r := gin.Default()
    r.POST("/job", func(c *gin.Context) {
        var job Job
        if err := c.ShouldBindJSON(&job); err != nil {
            c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
            return
        }

        // insert job into database
        query := "INSERT INTO jobs (title, body,  titles, contents ,date) VALUES ($1, $2, $3,  $4, $5) RETURNING id"
        var id int
        err := db.QueryRow(query, job.Title, job.Body, pq.Array(job.Titles), pq.Array(job.Contents), time.Now()).Scan(&id)
        if err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to create job"})
            return
        }

        job.ID = id

        c.JSON(http.StatusOK, job)
    })

    r.GET("/jobs", func(c *gin.Context) {
        // retrieve all jobs from database
        rows, err := db.Query("SELECT * FROM jobs")
        if err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to retrieve jobs"})
            return
        }
        defer rows.Close()

        // iterate over rows and store in slice of Jobs
        jobs := []Job{}
        for rows.Next() {
            var job Job
            err := rows.Scan(&job.ID, &job.Title, &job.Body, &job.Date, pq.Array(job.Titles[:]), pq.Array(job.Contents[:]))
            if err != nil {
                c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to retrieve jobs"})
                return
            }
            jobs = append(jobs, job)
        }

        c.JSON(http.StatusOK, jobs)
    })

    r.Run()
}

Let me recap the changes:

• Switch the table name from table to jobs.
• In the Scan section of the GET handler I did these two changes:
  • Used the pq.Array type provided by the pb package to scan the Titles and Content fields
  • Converted the arrays of type [4]string to slices with the operator. [:]. Thanks to this, you don't have to use the address of operator & coz the slice are passed by reference. With this trick, it's able to scan the value and populate the Job struct.

Let me know if this helps you, thanks!