Running dynamic queries

[cemremengu]

I have some queries where I dont know the columns that will be returned in advance since the user specifies them.

Is there way to map a row as map[string]interface{}?

[bokwoon95]

Out of curiosity, how would you use these values if you don't know what name or type they are in advance?

[cemremengu]

Thanks! The problem is I dont know both the column names and their types. Your approach would work but I think something like below (if possible) would be even better:

actors, err := sq.FetchAll(db,
    sq.Postgres.Queryf("SELECT {*} FROM actor WHERE first_name IS NOT NULL"),
    func(row *sq.Row) map[string]interface{} {
        actor := make(map[string]interface{})
        for _, column := range row.columns { // get cols from row directly
            actor[column] = row.Any(column)
        }
        return actor
    },
)

[bokwoon95]

But... doesn't the user specify the columns? How are they specifying the columns? Are you not able to obtain a list of those column names as a slice?

Due to how this library works, those column names have to be known beforehand. You said that the user specifies them, so I am very confused how they are able to specify a list of columns without your application knowing what those columns are. Are you running raw SQL provided by the user?

[cemremengu]

Apologies for not being able to explain clearly. So there are two cases:

1- User specifies the columns to a dataset (which is stored as a query) and (in addition the some default columns) the query is executed and the query result is returned. The column names are known beforehand in this case and your suggested approach should work.

2- User executes a raw query to create a dataset. So I am assuming I cannot use this library in this case.

[bokwoon95]

Hmm I wouldn't say it's very difficult to achieve 2 with pure database/sql though, check out how it's done:

package main

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

    _ "github.com/jackc/pgx/v5/stdlib"
)

func main() {
    db, err := sql.Open("pgx", "postgres://user1:Hunter2!@localhost:5456/sakila?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }

    // Setup database.
    _, err = db.Exec("CREATE TABLE IF NOT EXISTS actor (" +
        "actor_id INT PRIMARY KEY" +
        ", first_name TEXT NOT NULL" +
        ", last_name TEXT NOT NULL" +
        ", last_update TIMESTAMPTZ" +
        ")",
    )
    if err != nil {
        log.Fatal(err)
    }
    _, err = db.Exec("INSERT INTO actor (actor_id, first_name, last_name, last_update) VALUES" +
        " (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33')" +
        ", (2, 'NICK', 'WAHLBERG', '2006-02-15 04:34:33')" +
        ", (3, 'ED', 'CHASE', '2006-02-15 04:34:33')" +
        ", (4, 'JENNIFER', 'DAVIS', '2006-02-15 04:34:33')" +
        ", (5, 'JOHNNY', 'LOLLOBRIGIDA', '2006-02-15 04:34:33')" +
        " ON CONFLICT DO NOTHING",
    )
    if err != nil {
        log.Fatal(err)
    }

    // Run raw query from user and dynamically fetch its results.
    rows, err := db.Query("SELECT * FROM actor")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    var results []map[string]interface{}
    for rows.Next() {
        // Get column names.
        columns, err := rows.Columns()
        if err != nil {
            log.Fatal(err)
        }
        // Get row values.
        values := make([]interface{}, len(columns))
        dest := make([]interface{}, len(columns))
        for i := range dest {
            dest[i] = &values[i]
        }
        err = rows.Scan(dest...)
        if err != nil {
            log.Fatal(err)
        }
        // Construct the result map and append it to results.
        result := make(map[string]interface{})
        for i, column := range columns {
            result[column] = values[i]
        }
        results = append(results, result)
    }
    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }
    for _, result := range results {
        fmt.Printf("%#v\n", result)
    }
}

map[string]interface {}{"actor_id":1, "first_name":"PENELOPE", "last_name":"GUINESS", "last_update":time.Date(2006, time.February, 15, 12, 34, 33, 0, time.Local)}
map[string]interface {}{"actor_id":2, "first_name":"NICK", "last_name":"WAHLBERG", "last_update":time.Date(2006, time.February, 15, 12, 34, 33, 0, time.Local)}
map[string]interface {}{"actor_id":3, "first_name":"ED", "last_name":"CHASE", "last_update":time.Date(2006, time.February, 15, 12, 34, 33, 0, time.Local)}
map[string]interface {}{"actor_id":4, "first_name":"JENNIFER", "last_name":"DAVIS", "last_update":time.Date(2006, time.February, 15, 12, 34, 33, 0, time.Local)}
map[string]interface {}{"actor_id":5, "first_name":"JOHNNY", "last_name":"LOLLOBRIGIDA", "last_update":time.Date(2006, time.February, 15, 12, 34, 33, 0, time.Local)}

[bokwoon95]

If you know the columns though then yeah you can do it like this with sq (once I'm done with the implementation).

package main

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

	"github.com/bokwoon95/sq"
	_ "github.com/jackc/pgx/v5/stdlib"
)

func main() {
	db, err := sql.Open("pgx", "postgres://user1:Hunter2!@localhost:5456/sakila?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	// Setup database.
	_, err = db.Exec("CREATE TABLE IF NOT EXISTS actor (" +
		"actor_id INT PRIMARY KEY" +
		", first_name TEXT NOT NULL" +
		", last_name TEXT NOT NULL" +
		", last_update TIMESTAMPTZ" +
		")",
	)
	if err != nil {
		log.Fatal(err)
	}
	_, err = db.Exec("INSERT INTO actor (actor_id, first_name, last_name, last_update) VALUES" +
		" (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33')" +
		", (2, 'NICK', 'WAHLBERG', '2006-02-15 04:34:33')" +
		", (3, 'ED', 'CHASE', '2006-02-15 04:34:33')" +
		", (4, 'JENNIFER', 'DAVIS', '2006-02-15 04:34:33')" +
		", (5, 'JOHNNY', 'LOLLOBRIGIDA', '2006-02-15 04:34:33')" +
		" ON CONFLICT DO NOTHING",
	)
	if err != nil {
		log.Fatal(err)
	}

	// Run query and dynamically fetch its results (columns are known).
	columns := []string{"actor_id", "first_name", "last_name", "last_update"}
	results, err := sq.FetchAll(db,
		sq.Postgres.Queryf("SELECT {*} FROM actor"),
		func(row *sq.Row) map[string]interface{} {
			result := make(map[string]interface{})
			for _, column := range columns {
				result[column] = row.Any(column)
			}
			return result
		},
	)
	if err != nil {
		log.Fatal(err)
	}
	for _, result := range results {
		fmt.Printf("%#v\n", result)
	}
}

[bokwoon95]

I've been thinking about this use case, I think it can be accommodated after all.

func(row *sq.Row) map[string]interface{} {
    actor := make(map[string]interface{})
    for _, column := range row.columns { // get cols from row directly
        actor[column] = row.Any(column)
    }
    return actor
}

So there are two types of queries: dynamic and non-dynamic. If the user provides a list of columns, the query is dynamic (because the SELECT clause will change depending the columns specified). If the user passes in a raw SQL string, it's non-dynamic (because the query string is static and will not change). I plan to add four new methods:

Dynamic queries

Dynamic queries get one new method. You know the names and number of columns.

func (*Row) Value(format string, values ...interface{}) interface{}

Used like this:

result := make(map[string]interface{})
for _, column := range columns {
    result[column] = row.Value(column)
}

Non-dynamic queries

Non-dynamic queries get three new methods. You don't know the names or number of columns.

func (*Row) Columns() []string
func (*Row) ColumnTypes() []*sql.ColumnType
func (*Row) Values() []interface{}

Used like this:

result := make(map[string]interface{})
columns := row.Columns()
values := row.Values() // guranteed to have same length as row.Columns()
for i, column := range columns {
    result[column] = values[i]
}
// row.ColumnTypes() is a thin wrapper over (*database/sql.Rows).ColumnsTypes(),
// usually not needed but added for completion's sake.

Dynamic vs Non-dynamic queries

• It is an error to call row.Value() for non-dynamic queries (i.e. raw SQL). This extends to every other row method that returns a value e.g. row.Int(), row.String() etc.
• It is an error to call row.Columns(), row.Values() and row.ColumnsTypes() on dynamic queries (i.e. queries where you know the columns ahead of time).

Basically the new methods row.Columns(), row.Values() and row.ColumnTypes() are only valid if you use them on raw SQL queries. They are invalid everywhere else. And if you are using raw SQL queries, you cannot call any other method on the row except row.Columns(), row.Values() and row.ColumnTypes(). It's a little confusing but hopefully the error message returned will be informative enough to inform the user what they are doing wrong.

[cemremengu]

Thanks for the write up! Great analysis for the use case. I think this should work well.

[bokwoon95]

Fixed in v0.3.0 009782c, should be working now.

Works as previously discussed.

[cemremengu]

Thank you for your help and work!