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)
})`

This needs more error details like stack trace and other details. Try to log the error details to the standard console. — Mahesh
– Mahesh, Commented Mar 21, 2023 at 14:25
console debugging 2023/03/21 20:10:14 Error scanning job from database: sql: Scan error on column index 3, name "titles": unsupported Scan, storing driver.Value type string into type *time.Time — Coding write
– Coding write, Commented Mar 21, 2023 at 17:13
Since you did select * you need to set the scanning order of struct props to that of order of column of tables. Better way is use named select id, title, ... — Mahesh
– Mahesh, Commented Mar 23, 2023 at 1:43

ossan · Accepted Answer · 2023-03-21 14:40:24Z

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!

Collectives™ on Stack Overflow

Golang gin rest api post and get

1 Answer 1

The `main.go` file

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

The main.go file

Comments

Your Answer

Sign up or log in

Post as a guest

Related

The `main.go` file