Using Go's `database/sql` to query an arbitrary columns of unknown type(s)

June 13, 2024, 7:41 p.m.

As part of some work I'm doing on dependency-management-data to add a pure-Go database browser, I want to be able to accept arbitrary SQL and query it.

However, I found that trying to do this with Go's database/sql, this didn't work super nicely with the anys that get returned by database/sql, so we could follow something like this StackOverflow, but there are better things we can do.

Adapting this article instead, we can write code like so, taking advantage of sql.RawBytes to make the conversion easier:

package main

import (
 "database/sql"
 "fmt"
 "log"
 "strings"

 _ "modernc.org/sqlite"
)

func main() {
 sqlDB, err := sql.Open("sqlite", "dmd.db")
 if err != nil {
 log.Fatal(err)
 }

 res, err := sqlDB.Query(`select * from metadata`)
 if err != nil {
 log.Fatal(err)
 }

 rows, err := toRows(res)
 if err != nil {
 log.Fatal(err)
 }

 for _, row := range rows {
 fmt.Println(strings.Join(row, " "))
 }
 fmt.Println()

 res, err = sqlDB.Query(`select 'hi' as greeting, 1 as num_rows, NULL as owner, true as is_valid`)
 if err != nil {
 log.Fatal(err)
 }

 rows, err = toRows(res)
 if err != nil {
 log.Fatal(err)
 }

 for _, row := range rows {
 fmt.Println(strings.Join(row, " "))
 }
}

func toRows(res *sql.Rows) ([][]string, error) {
 var rows [][]string

 cols, err := res.Columns()
 if err != nil {
 return nil, err
 }

 rows = append(rows, cols)

 for res.Next() {
 items := make([]any, len(cols))
 for i := range items {
 // http://go-database-sql.org/varcols.html
 items[i] = new(sql.RawBytes)
 }
 if err := res.Scan(items...); err != nil {
 return nil, err
 }

 row := make([]string, len(cols))
 for i, v := range items {
 if sb, ok := v.(*sql.RawBytes); ok {
 row[i] = string(*sb)
 }
 }
 rows = append(rows, row)
 }

 return rows, nil
}

This gives the output of the following (which I've slightly modified for readability):

name value
dmd_version v0.94.3-next
compatible_since v0.94.0
greeting num_rows owner is_valid
hi 1 1
hi 1 1

We alternatively could simplify this to use anys, which seems less nice:

package main

import (
 "database/sql"
 "fmt"
 "log"
 "strings"

 _ "modernc.org/sqlite"
)

func main() {
 sqlDB, err := sql.Open("sqlite", ":memory:")
 if err != nil {
 log.Fatal(err)
 }

 res, err := sqlDB.Query(`select 'hi' as greeting, 1 as num_rows, NULL as owner, true as is_valid`)
 if err != nil {
 log.Fatal(err)
 }

 rows, err := toRows(res)
 if err != nil {
 log.Fatal(err)
 }

 for _, row := range rows {
 fmt.Println(strings.Join(row, " "))
 }
}

func toRows(res *sql.Rows) ([][]string, error) {
 var rows [][]string

 cols, err := res.Columns()
 if err != nil {
 return nil, err
 }

 rows = append(rows, cols)

 for res.Next() {
 items := make([]any, len(cols))
 for i := range items {
 // http://go-database-sql.org/varcols.html
 items[i] = new(any)
 }
 if err := res.Scan(items...); err != nil {
 return nil, err
 }

 row := make([]string, len(cols))
 for i, v := range items {
 vv := v.(*any)
 row[i] = fmt.Sprintf("%v", *vv)
 }
 rows = append(rows, row)
 }

 return rows, nil
}

This gives the output of the following (which I've slightly modified for readability):


greeting num_rows owner is_valid
hi 1 <nil> 1