I had a devil of a time figuring out how to get go’s sql query results into a json result set without having to define a structure around it. Perhaps it can help others.

The thing that confused me the most was the sql.Columns.ScanType() function, which I had convinced myself would be critical to the process. In the end, neither it, nor the reflect package defining its return type, was required.

Instead, I create colvals, a []inteface{} to satisfy rows.Scan(), and populate it with pointers to interfaces I had gotten from new(interface{}). Once rows.Scan(colvals...) returns, the value at each interface pointer is an interface that wraps the correct type for the data.

Storing all the rows as a []map[string]interface{} allows me to return json ofall the results like an API might, but without having to model my schema in golang types. I’m really excited to be able to define APIS without a lot of data odeling!

package main

import (
  "database/sql"
  "encoding/json"
  "fmt"
  _ "github.com/lib/pq"
)

// we need some data to query; I'll create a temp table
// and populate
func CreateTable(tx *sql.Tx) {
  if _, err := tx.Exec(
    `CREATE TEMPORARY TABLE test (
      id    SERIAL PRIMARY KEY,
      name  TEXT,
      date  TIMESTAMP WITH TIME ZONE )`,
  ); err != nil {
    panic(err)
  }
  for _, name := range []string{"Maddie", "Dan"} {
    if _, err := tx.Exec(
      "INSERT INTO test (name, date) VALUES ($1, NOW() )", name,
    ); err != nil {
      panic(err)
    }
  }
}

func main() {
  // docker run --rm -d p localhost:5432:5432 postgres
  conn, err := sql.Open(
    "postgres",
    "sslmode=disable host=localhost port=5432 user=postgres",
  )
  if err != nil {
    panic(err)
  }
  tx, err := conn.Begin()
  if err != nil {
    panic(err)
  }
  CreateTable(tx)
  rows, err := tx.Query("SELECT * FROM test")
  if err != nil {
    panic(err)
  }
  cols, err := rows.Columns()
  if err != nil {
    panic(err)
  }
  // we"ll want to end up with a list of name->value maps, a la JSON
  // surely we know how many rows we got but can"t find it now
  allgeneric := make([]map[string]interface{},0)
  // we"ll need to pass an interface to sql.Row.Scan
  colvals := make([]interface{}, len(cols))
  for rows.Next() {
    colassoc := make(map[string]interface{}, len(cols))
    // values we"ll be passing will be pointers, themselves to interfaces
    for i, _ := range colvals {
      colvals[i] = new(interface{})
    }
    if err := rows.Scan(colvals...); err != nil {
      panic(err)
    }
    for i, col := range cols {
      colassoc[col] = *colvals[i].(*interface{})
      fmt.Printf("%s: %T %s\n", col, colassoc[col], colassoc[col] )
    }
    fmt.Println(colassoc)
    allgeneric = append(allgeneric, colassoc)
  }
  rows.Close()
  if err := tx.Commit(); err != nil {
    panic(err)
  }
  j, err := json.Marshal(allgeneric)
  if err != nil{
    panic(err)
  }
  fmt.Println(string(j))
}