Dumping MySQL tables to JSON with Golang

Question

Was putting together a quick dumper for MySQL to JSON in Go. However I find that everything that I retrieve from the database is a []byte array. Thus instead of native JSON integers or booleans, I'm getting everything encoded as strings.

Subset of the code:

import (     "encoding/json"     "database/sql"     _ "github.com/go-sql-driver/mysql" )   func dumpTable(w io.Writer, table) {     // ...      rows, err := Query(db, fmt.Sprintf("SELECT * FROM %s", table))     checkError(err)     columns, err := rows.Columns()     checkError(err)      scanArgs := make([]interface{}, len(columns))     values   := make([]interface{}, len(columns))      for i := range values {         scanArgs[i] = &values[i]     }      for rows.Next() {         err = rows.Scan(scanArgs...)         checkError(err)          record := make(map[string]interface{})          for i, col := range values {             if col != nil {                 fmt.Printf("\n%s: type= %s\n", columns[i], reflect.TypeOf(col))                  switch t := col.(type) {                 default:                     fmt.Printf("Unexpected type %T\n", t)                 case bool:                     fmt.Printf("bool\n")                     record[columns[i]] = col.(bool)                 case int:                     fmt.Printf("int\n")                     record[columns[i]] = col.(int)                 case int64:                     fmt.Printf("int64\n")                     record[columns[i]] = col.(int64)                 case float64:                     fmt.Printf("float64\n")                     record[columns[i]] = col.(float64)                 case string:                     fmt.Printf("string\n")                     record[columns[i]] = col.(string)                 case []byte:   // -- all cases go HERE!                     fmt.Printf("[]byte\n")                     record[columns[i]] = string(col.([]byte))                 case time.Time:                     // record[columns[i]] = col.(string)                 }             }         }          s, _ := json.Marshal(record)         w.Write(s)         io.WriteString(w, "\n")     } } 

Answer 1

I also needed to dump database tables to json and here is how I achieved: (different than another answer in this topic, everything is not string, thanks to this answer: https://stackoverflow.com/a/17885636/4124416, I could get integer fields correctly)

func getJSON(sqlString string) (string, error) {     rows, err := db.Query(sqlString)     if err != nil {         return "", err     }     defer rows.Close()     columns, err := rows.Columns()     if err != nil {         return "", err     }     count := len(columns)     tableData := make([]map[string]interface{}, 0)     values := make([]interface{}, count)     valuePtrs := make([]interface{}, count)     for rows.Next() {         for i := 0; i < count; i++ {           valuePtrs[i] = &values[i]         }         rows.Scan(valuePtrs...)         entry := make(map[string]interface{})         for i, col := range columns {             var v interface{}             val := values[i]             b, ok := val.([]byte)             if ok {                 v = string(b)             } else {                 v = val             }             entry[col] = v         }         tableData = append(tableData, entry)     }     jsonData, err := json.Marshal(tableData)     if err != nil {         return "", err     }     fmt.Println(string(jsonData))     return string(jsonData), nil  } 

Here is a sample output:

[{"ID":0,"Text":"Zero"},{"ID":1,"Text":"One"},{"ID":2,"Text":"Two"}] 

Answer 2

It is needed to use prepared statements to get the native types. MySQL has two protocols, one transmits everything as text, the other as the "real" type. And that binary protocol is only used when you use prepared statements. See https://github.com/go-sql-driver/mysql/issues/407

The function getJSON below is correct:

func getJSON(sqlString string) (string, error) {   stmt, err := db.Prepare(sqlString)   if err != nil {     return "", err   }   defer stmt.Close()    rows, err := stmt.Query()   if err != nil {     return "", err   }   defer rows.Close()    columns, err := rows.Columns()   if err != nil {     return "", err   }    tableData := make([]map[string]interface{}, 0)    count := len(columns)   values := make([]interface{}, count)   scanArgs := make([]interface{}, count)   for i := range values {     scanArgs[i] = &values[i]   }    for rows.Next() {     err := rows.Scan(scanArgs...)     if err != nil {       return "", err     }      entry := make(map[string]interface{})     for i, col := range columns {       v := values[i]        b, ok := v.([]byte)       if (ok) {         entry[col] = string(b)       } else {         entry[col] = v       }     }      tableData = append(tableData, entry)   }    jsonData, err := json.Marshal(tableData)   if err != nil {     return "", err   }    return string(jsonData), nil  }