User Tools

Site Tools


Go, Gin and SQLx

gin-agent.go
/*
 * Author, Copyright: Oleg Borodin <onborodin@gmail.com>
 */
 
package main
 
import (
    "net/http"
    "github.com/gin-gonic/gin"
    "fmt"
    "os"
    "time"
    "log"
    "github.com/jmoiron/sqlx"
    _ "github.com/jackc/pgx/v4/stdlib"
)
 
type DbInfo struct {
    DatName string      `db:"datname" json:"datname"`
    Size int64          `db:"size" json:"size"`
    Owner string        `db:"owner" json:"owner"`
    NumBackends int     `db:"numbackends" json:"numbackends"`
}
 
type Env struct {
    db *sqlx.DB
}
 
func main() {
 
    db, err := sqlx.Open("pgx", "postgres://pgsql@localhost/postgres?sslmode=disable")
    if err != nil {
        fmt.Printf("error: %s\n", err)
        os.Exit(1)
    }
    defer db.Close()
    err = db.Ping()
    if err != nil {
        fmt.Printf("error: %s\n", err)
        os.Exit(1)
    }
 
    go func() {
        for {
            if err := db.Ping(); err != nil {
                log.Printf("db error: %s\n", err)
            }
            time.Sleep(2 * time.Second)
        }
    }()
 
    env := &Env{db: db}
 
    r := gin.Default()
    r.GET("/", env.dbInfo)
    r.Run(":8080")
}
 
func (env *Env) dbInfo(context *gin.Context) {
 
    query := `select d.datname as datname,
            pg_database_size(d.datname) as size,
            u.usename as owner,
            s.numbackends as numbackends
        from pg_database d, pg_user u, pg_stat_database s
        where d.datdba = u.usesysid and d.datname = s.datname
        order by d.datname`
 
    startTime := time.Now()
    var dbis []DbInfo
    err := env.db.Select(&dbis, query)
    elapsed := time.Since(startTime).String()
 
    if err == nil {
        context.JSON(http.StatusOK, gin.H{
            "error": false,
            "message": "success",
            "result": dbis,
            "elapsed": elapsed,
        })
    } else {
        context.JSON(http.StatusOK, gin.H{
            "error": true,
            "message": err.Error(),
            "result": []DbInfo{},
            "elapsed": elapsed,
        })
 
    }
}

result

# curl -v http://127.0.0.1:8080/
*   Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to 127.0.0.1 (127.0.0.1) port 8080 (#0)
> GET / HTTP/1.1
> Host: 127.0.0.1:8080
> User-Agent: curl/7.63.0
> Accept: */*
> 
< HTTP/1.1 200 OK
< Content-Type: application/json; charset=utf-8
< Date: Tue, 03 Dec 2019 12:54:48 GMT
< Content-Length: 627
< 
{"elapsed":"150.262338ms","error":false,"message":"success",
"result":[{"datname":"gorm","size":8233475,"owner":"gorm","numbackends":0},
{"datname":"ministore","size":9798147,"owner":"ministore","numbackends":0},
{"datname":"pgbench","size":25248259,"owner":"pgsql","numbackends":0},
{"datname":"pgsql","size":8102403,"owner":"pgsql","numbackends":0},
{"datname":"postgres","size":24101743,"owner":"pgsql","numbackends":1},
{"datname":"rap2","size":8389123,"owner":"rap2","numbackends":0},
{"datname":"template0","size":8102403,"owner":"pgsql","numbackends":0},
{"datname":"template1","size":8102403,"owner":"pgsql","numbackends":0}]}

* Connection #0 to host 127.0.0.1 left intact

XML varian

result.xml
<map>
    <error>false</error>
    <message>success</message>
    <result>
        <datname>gorm</datname>
        <size>8381295</size>
        <owner>gorm</owner>
        <numbackends>0</numbackends>
    </result>
    <result>
        <datname>ministore</datname>
        <size>9945967</size>
        <owner>ministore</owner>
        <numbackends>0</numbackends>
    </result>
    <result>
        <datname>pgbench</datname>
        <size>25248259</size>
        <owner>pgsql</owner>
        <numbackends>0</numbackends>
    </result>
    <result>
        <datname>pgsql</datname>
        <size>8250223</size>
        <owner>pgsql</owner>
        <numbackends>0</numbackends>
    </result>
    <result>
        <datname>postgres</datname>
        <size>24101743</size>
        <owner>pgsql</owner>
        <numbackends>1</numbackends>
    </result>
    <result>
        <datname>rap2</datname>
        <size>8536943</size>
        <owner>rap2</owner>
        <numbackends>0</numbackends>
    </result>
    <result>
        <datname>template0</datname>
        <size>8102403</size>
        <owner>pgsql</owner>
        <numbackends>0</numbackends>
    </result>
    <result>
        <datname>template1</datname>
        <size>8102403</size>
        <owner>pgsql</owner>
        <numbackends>0</numbackends>
    </result>
    <elapsed>30.549443ms</elapsed>
</map>

YAML variant

result.yml
elapsed: 27.830227ms
error: false
message: success
result:
- datname: gorm
  size: 8381295
  owner: gorm
  numbackends: 0
- datname: ministore
  size: 9945967
  owner: ministore
  numbackends: 0
- datname: pgbench
  size: 25248259
  owner: pgsql
  numbackends: 0
- datname: pgsql
  size: 8250223
  owner: pgsql
  numbackends: 0
- datname: postgres
  size: 24101743
  owner: pgsql
  numbackends: 1
- datname: rap2
  size: 8536943
  owner: rap2
  numbackends: 0
- datname: template0
  size: 8102403
  owner: pgsql
  numbackends: 0
- datname: template1
  size: 8102403
  owner: pgsql
  numbackends: 0

size of result

  • yaml: 598
  • xml: 978
  • json: 626