Is Golang's SQL package incapable of ad hoc / exploratory queries?

Question

Based of the documentation, it seems the only way to get data out of a database in Go is using Rows.Scan(), meaning you must know the count and types of all columns at compile-time.

Am I missing something? How are you supposed to support ad hoc queries? Or even pull all columns out of a table that may change in the future?

Answer 1

The sql.Rows type has a Columns method that will give you a list of the result column names. That can be used to determine the number of columns for unknown queries.

In the docs for the Scan method, it says:

If an argument has type *[]byte, Scan saves in that argument a copy of the corresponding data. The copy is owned by the caller and can be modified and held indefinitely. The copy can be avoided by using an argument of type *RawBytes instead; see the documentation for RawBytes for restrictions on its use.

If an argument has type *interface{}, Scan copies the value provided by the underlying driver without conversion. If the value is of type []byte, a copy is made and the caller owns the result.

So we also have support for scanning column values when we don't know their type: either in their raw form, or as Go types.

Putting these two together, you could do something like the following using the ... syntax to call variadic functions:

columnNames, err := rows.Columns()
if err != nil {
    log.Fatalln(err) // or whatever error handling is appropriate
}
columns := make([]interface{}, len(columnNames))
columnPointers := make([]interface{}, len(columnNames))
for i := 0; i < len(columnNames); i++ {
    columnPointers[i] = &columns[i]
}
if err := rows.Scan(columnPointers...); err != nil {
    log.Fatalln(err)
}

Now the columns slice should contain the decoded versions of all the column values for the current result row.

If you have extra knowledge about the table (e.g. expected types, or know the number of columns ahead of time), you could probably simplify the logic a little.

Answer 2

Found example code for go-mssqldb driver doing exactly this. Ref. https://github.com/denisenkom/go-mssqldb/blob/master/examples/tsql/tsql.go - code extracted below. It works, at least, for this driver. However, it only uses the sql-namespace API so it will probably/possibly work for other drivers too.

Given any(?) SQL select statement, it displays the resulting data rows

func exec(db *sql.DB, cmd string) error {
    rows, err := db.Query(cmd)
    if err != nil {
        return err
    }
    defer rows.Close()
    cols, err := rows.Columns()
    if err != nil {
        return err
    }
    if cols == nil {
        return nil
    }
    vals := make([]interface{}, len(cols))
    for i := 0; i < len(cols); i++ {
        vals[i] = new(interface{})
        if i != 0 {
            fmt.Print("\t")
        }
        fmt.Print(cols[i])
    }
    fmt.Println()
    for rows.Next() {
        err = rows.Scan(vals...)
        if err != nil {
            fmt.Println(err)
            continue
        }
        for i := 0; i < len(vals); i++ {
            if i != 0 {
                fmt.Print("\t")
            }
            printValue(vals[i].(*interface{}))
        }
        fmt.Println()

    }
    if rows.Err() != nil {
        return rows.Err()
    }
    return nil
}

func printValue(pval *interface{}) {
    switch v := (*pval).(type) {
    case nil:
        fmt.Print("NULL")
    case bool:
        if v {
            fmt.Print("1")
        } else {
            fmt.Print("0")
        }
    case []byte:
        fmt.Print(string(v))
    case time.Time:
        fmt.Print(v.Format("2006-01-02 15:04:05.999"))
    default:
        fmt.Print(v)
    }
}