Golang的struct或newbie切片无法建立REST

Question

and need your help. Wanted to build simple api and stuck with some problem. I've choose gin and database/sql with postgres driver

package main

import (
    "database/sql"
    "fmt"

    "github.com/gin-gonic/gin"

    _ "github.com/lib/pq"
)

func main() {

    router := gin.Default()
    router.GET("/search/:text", SearchWord)
    router.Run(":8080")

}

I need to make query to DB and make json out of this request.

func checkErr(err error) {
    if err != nil {
        panic(err)
    }
}

type Message struct {
    ticket_id int    `json:"ticket_id"`
    event     string `json:"event"`
}

func SearchWord(c *gin.Context) {
    word := c.Params.ByName("text")
    db, err := sql.Open("postgres", "host=8.8.8.8 user= password= dbname=sample")
    defer db.Close()
    checkErr(err)
    rows, err2 := db.Query("SELECT ticket_id,event FROM ....$1, word)
    checkErr(err)
    for rows.Next() {
        var ticket_id int
        var event string
        err = rows.Scan(&ticket_id, &event)
        checkErr(err)
        fmt.Printf("%d | %s 

", ticket_id, event)
    }

}

This coda working nice, but when i need to make json. I need to make struct of a row

type Message struct {
    ticket_id int    `json:"ticket_id"`
    event     string `json:"event"`
}

an then i need to create slice , and append every rows.Next() loop an than answer to browser with Json...

c.JSON(200, messages)

But how to do that...don't know :(

Answer 1

I'm going to cheat a little here and fix a few things along the way:

First: open your database connection pool once at program start-up (and not on every request).

Second: we'll use sqlx to make it easier to marshal our database rows into our struct.

package main

var db *sqlx.DB

func main() {
    var err error
    // sqlx.Connect also checks that the connection works.
    // sql.Open only "establishes" a pool, but doesn't ping the DB.
    db, err = sqlx.Connect("postgres", "postgres:///...")
    if err != nil {
        log.Fatal(err)
    }

    router := gin.Default()
    router.GET("/search/:text", SearchWord)
    router.Run(":8080")

}

// in_another_file.go

type Message struct {
    TicketID int    `json:"ticket_id" db:"ticket_id"`
    Event     string `json:"event" db:"event"`
}

func SearchWord(c *gin.Context) {
    word := c.Params.ByName("text")
    // We create a slice of structs to marshal our rows into
    var messages []*Message{}
    // Our DB connection pool is safe to use concurrently from here
    err := db.Select(&messages, "SELECT ticket_id,event FROM ....$1, word)
    if err != nil {
        http.Error(c.Writer, err.Error(), 500)
        return
    }

    // Write it out using gin-gonic's JSON writer.
    c.JSON(200, messages)
}

I hope that's clear. sqlx also takes care of calling rows.Close() for you, which will otherwise leave connections hanging.