GoLang : Using sqlx for record mapping into Structs

Posted on Updated on

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>
}