wi12.unix7.org

sql-to-json.go

/*
 * Author, Copyright: Oleg Borodin <onborodin@gmail.com>
 */
 
package main
 
import (
    "encoding/json"
    "fmt"
    "os"
 
    "github.com/jmoiron/sqlx"
    _ "github.com/jackc/pgx/v4/stdlib"
)
 
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)
    }
 
    type DbInfo struct {
        DatName string      `db:"datname"`
        Size int64          `db:"size"`
        Owner string        `db:"owner"`
        NumBackends int     `db:"numbackends"`
    }
 
    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
        LIMIT 3
    `
    rows, err := db.Queryx(query)
    if err != nil {
        fmt.Println(err)
        return
    }
    defer rows.Close()
    cols, _ := rows.Columns()
 
    type M map[string]interface{}
    var out []M
 
 
    for rows.Next() {
        columns := make([]interface{}, len(cols))
        columnPointers := make([]interface{}, len(cols))
 
        for i, _ := range columns {
            columnPointers[i] = &columns[i]
        }
 
        err := rows.Scan(columnPointers...)
        if err != nil {
            fmt.Println(err)
            return
        }
 
        m := make(M)
        for i, colName := range cols {
            val := columnPointers[i].(*interface{})
            m[colName] = *val
        }
        out = append(out, m)
    }
    jsonData, err := json.MarshalIndent(out, " ", "    ")
    fmt.Println(string(jsonData))
}

$ go run sql-to-json.go
[
     {
         "datname": "gorm",
         "numbackends": 0,
         "owner": "gorm",
         "size": 8233475
     },
     {
         "datname": "mattermost",
         "numbackends": 0,
         "owner": "mattermost",
         "size": 10920451
     },
     {
         "datname": "ministore",
         "numbackends": 0,
         "owner": "ministore",
         "size": 9798147
     }
 ]