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

Featured image for sharing metadata for article

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

Written by Jamie Tanna's profile image Jamie Tanna on , and last updated on .

Content for this article is shared under the terms of the Creative Commons Attribution Non Commercial Share Alike 4.0 International, and code is shared under the Apache License 2.0.

#blogumentation #go.

This post was filed under articles.

Interactions with this post

Interactions with this post

Below you can find the interactions that this page has had using WebMention.

Have you written a response to this post? Let me know the URL:

Do you not have a website set up with WebMention capabilities? You can use Comment Parade.