Dynamic SQL query with Go (Golang) standard library

Picture by Sunder Muthukumaran on Unsplash

For many purposes, we are going to know what information we’d like from the database, then we will outline the suitable information varieties to take the question consequence.

However typically we’d want the pliability to take dynamic SQL or unknown column queries at runtime to generate some information. For instance, say we’re constructing a reporting, administration or GUI instrument.

On this publish, I’m going to speak about how to do that with Go commonplace library (database/sql) in a easy reporting instance.

The complete code instance may be discovered here

I’ll skip the apparent Go code and soar proper into the vital half. Additionally, to make the code clear to learn, I’ll ignore the error dealing with.

First, we have to get the column info like column names.

On this instance, I’m utilizing CockroachDB with customers desk outlined beneath:

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

Let’s run a full desk question:

rows, _ := db.Question("choose * from customers")
defer rows.Shut()
cols, _ := rows.Columns()

Variable cols is the column names (slice of string). Assuming we’re simply printing outcomes to stdout, we will 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.Be a part of(cols, "t") + "n"))

The header a part of the report is accomplished, let’s sort out the consequence information.

For dynamic or advert hoc question, since we don’t know the column kind, it’s very troublesome to find out the variable kind we have to use upfront (we will pair change assertion with ColumnTypes to realize this, and for superior utilization like formatting or additional processing, we should always. However that might be fairly tedious).

As well as, the database column may be NULLABLE . In Go, we have to deal with this with particular information varieties like NullString.

For easy reporting functions, we’d like a common information kind to accommodate outcomes from database question.

Thankfully, byte slice []byte works properly for this, together with JSON/array information. Additionally, byte slice deal with database NULL as empty by default, which alleviates us from further dealing with.

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

For the reason that row may comprise a number of columns, we’d like a byte slice.

Now, there’s a difficult a part of Go, the Rows.Scan is a variadic perform with parameter as kind any, and the underlying concrete kind must be pointers. There’s no drawback if we specify every argument like:

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

At this level, we’d suppose we will simply change row to []*[]byte then unpack it in Scan name:

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

Sadly, we are going to get compile-time error saying:

can't use row (variable of kind []*[]byte) as []any worth in argument to rows.Scan

To handle this, we have now to comply with what the compiler had informed us. We want a variable of []any kind, then every component might be a pointer to every component of row variable:

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

That’s quite a bit, now we will print the consequence.

This half must be straightforward, simply loop via the rows, scan and write to stdout:

for rows.Subsequent() 
_ := rows.Scan(rowPtr...)
w.Write(bytes.Be a part of(row, sep))
w.Write(newLine)

We are able to execute this system now, the consequence will like this:

Dynamic question consequence instance

Beneath is the total minimal runnable code snippet:

(Be sure there’s a database and desk accessible to run this system, or verify this for an entire instance)

More Posts