GoLang : Using sqlx for record mapping into Structs
When working with GoLang and retrieving records from a database, this is typically done by retrieving a row and then parsing it to extract the various attributes and then in turn mapping them to variables to to a struct. For example, the following code shows the executing a query and then parsing the rows to process the returned attributes and assigning them to a variable.
import (
"fmt"
"time"
"database/sql"
godror "github.com/godror/godror"
)
func main(){
username := <username>;
password := <password>;
host := <host>:<port>;
database := <database name>;
<code to create the connection - didn't include to save space>
dbQuery := "select table_name, tablespace_name from user_tables where table_name not like 'DM$%' and table_name not like 'ODMR$%'"
rows, err := db.Query(dbQuery)
if err != nil {
fmt.Println(".....Error processing query")
fmt.Println(err)
return
}
defer rows.Close()
fmt.Println("... Parsing query results")
var tableName string
for rows.Next() {
rows.Scan(&tableName)
fmt.Println(tableName)
}
<code to close the connection - didn't include to save space>
}
As you can see this can add additional lines of code and corresponding debugging.
With the sqlx golang package, we can use their functionality to assign the query results to a struct. This simplifies the coding. The above code becomes the following:
import (
"fmt"
"time"
"database/sql"
godror "github.com/godror/godror"
"github.com/jmoiron/sqlx"
)
type TableDetails struct {
Table string 'db:"TABLE_NAME"'
Tablespace string 'db:"TABLESPACE_NAME"'
}
func main(){
username := <username>;
password := <password>;
host := <host>:<port>;
database := <database name>;
<code to create the connection - didn't include to save space - this time connect using sqlx>
// select all the rows and load into the struct, in one step
dbQuery := "select table_name, tablespace_name from user_tables where table_name not like 'DM$%' and table_name not like 'ODMR$%'"
table_rec := []TableDetails{}
db.Select(&tanle_rec, dbQuery)
// load each row separately
table_rec := []TableDetails{}
rows, err := db.Queryx(dbQuery)
for rows.next() {
// loads the current row into the struct
err := rows.StructScan(&table_rec)
fmt.Printf("%+v\n", table_rec)
}
<code to close the connection - didn't include to save space>
}
Ora-lytics 