How to Achieve Dynamic SQL Query With Go

Start by using Rows.Columns or Rows.ColumnTypes to get column information

Photo by Sunder Muthukumaran on Unsplash

For most applications, we will know what data we need from the database, then we can define the appropriate data types to take the query result.

But sometimes we might need the flexibility to take dynamic SQL or unknown column queries at runtime to generate some data. For example, say we are building a reporting, management or GUI tool.

In this post, I am going to talk about how to do this with Go standard library (database/sql) in a simple reporting example.

The full code example can be found here

I will skip the obvious Go code and jump right into the important part. Also, to make the code clear to read, I will ignore the error handling.

Obtaining column length and column names

First, we need to get the column information like column names.

In this example, I am using CockroachDB with users table defined below:

   column_name  |  data_type  |
----------------+-------------+
  id            | UUID        |
  name          | STRING      |
  age           | INT8        |
  married       | BOOL        |
  location      | JSONB       |
  phone_numbers | STRING[]    |
  creation_time | TIMESTAMPTZ |

Let’s run a full table query:

rows, _ := db.Query("select * from users")
defer rows.Close()
cols, _ := rows.Columns()

Variable cols is the column names (slice of string). Assuming we are just printing results to stdout, we can use text/tabwriter with ease:

w := tabwriter.NewWriter(os.Stdout, 0, 2, 1, ' ', 0)
defer w.Flush()
sep := []byte("\t")
newLine := []byte("\n")
w.Write([]byte(strings.Join(cols, "\t") + "\n"))

The header part of the report is completed, let’s tackle the result data.

Handling unknown row data

For dynamic or ad hoc query, since we don’t know the column type, it’s very difficult to determine the variable type we need to use in advance (we can pair switch statement with ColumnTypes to achieve this, and for advanced usage like formatting or further processing, we should. But that will be quite tedious).

In addition, the database column can be NULLABLE . In Go, we need to handle this with special data types like NullString.

For simple reporting purposes, we need a general data type to accommodate results from database query.

Fortunately, byte slice []byte works well for this, including JSON/array data. Also, byte slice treat database NULL as empty by default, which alleviates us from additional handling.

row := make([][]byte, len(cols))

Since the row might contain multiple columns, we need a byte slice.

Now, there’s a tricky part of Go, the Rows.Scan is a variadic function with parameter as type any, and the underlying concrete type should be pointers. There’s no problem if we specify each argument like:

var id int
var name string
err := rows.Scan(&id, &name)

At this point, we might think we can just change row to []*[]byte then unpack it in Scan call:

row := make([]*[]byte, len(cols))
err := rows.Scan(row...)

Unfortunately, we will get compile-time error saying:

cannot use row (variable of type []*[]byte) as []any value in argument to rows.Scan

To address this, we have to follow what the compiler had told us. We need a variable of []any type, then each element will be a pointer to each element of row variable:

rowPtr := make([]any, len(cols))
for i := range row {
  rowPtr[i] = &row[i]
}

That’s quite a lot, now we can print the result.

Printing the query result

This part should be easy, just loop through the rows, scan and write to stdout:

for rows.Next() {
  _ := rows.Scan(rowPtr...)
  w.Write(bytes.Join(row, sep))
  w.Write(newLine)
}

We can execute the program now, the result will like this:

Dynamic query result example

Below is the full minimum runnable code snippet:

(Make sure there’s a database and table available to run the program, or check this for a complete example)

Summary

To achieve dynamic SQL query in Go, we:

1. Used Rows.Columns or Rows.ColumnTypes to get column information
2. Used more general data type (byte slice in this case) to store the query result, with an intermediary type ([]any) to match up Rows.Scan call
3. Loop through the rows as usually

Thanks a lot for reading!