SQL scan in Go without headache

If this is first time you do sql with Go you probably should read this tutorial first, and comme back here to improve scan.
TLDR: Use sqlx for doing SQL. sqlx sit on top of standard sql library adding very convenient function especialy to scan result to a struct.

SQL scan without sqlx

Sql scan is a little bit tedious in Golang : the scan order must match your SQL query order.
An example to see the problem :

// Imagine a query (a big query) :
const userSelect = `SELECT u.user_user_id,
u.user_user_login,
u.user_user_pass,
u.user_user_first_name,
u.user_user_last_name,
u.user_user_date_created,
u.user_user_is_disabled,
u.user_user_is_deleted,
u.user_user_is_admin,
ug.user_group_id,
ug.user_group_parent_id,
ug.user_group_name,
ug.user_group_date_created,
ug.user_group_is_disabled,
ug.user_group_is_deleted,
ug.user_group_max_tkn,
rg.remote_group_id,
rg.remote_group_level,
rg.remote_group_is_company,
rg.remote_group_can_create_user,
rg.remote_group_can_remote_connect
FROM "user_user" u
LEFT JOIN "user_group" ug
  ON u.user_user_group_id = ug.user_group_id
LEFT JOIN "remote_group" rg
  ON ug.user_group_id = rg.remote_group_user_group_id`

// This query will be stored in this struct :
// Base user struct that match SQL select
type DbUser struct {
  Id                         int64
  Login                      string
  Pass                       string
  F_name                     string
  L_name                     string
  Date_created               string
  Is_disabled                bool
  Is_deleted                 bool
  Is_admin                   bool
  U_group_id                 int64
  U_group_parent_id          NullInt64
  U_group_name               string
  U_group_date_created       string
  U_group_is_disabled        bool
  U_group_is_deleted         bool
  U_group_max_tkn            int64
  R_group_id                 int64
  R_group_level              int64
  R_group_is_company         bool
  R_group_can_create_user    bool
  R_group_can_remote_connect bool
}

// Get1UserByLogin Get user info by providing user login
// login is the user login
func Get1UserByLogin(login string) (*DbUser, error) {
  const where = ` WHERE u.user_user_login = $1 LIMIT 1`
  const query = userSelect + where

  r := db.QueryRow(query, login)
  fecthUser, err := scan1User(r) // See the scan

  return fecthUser, err
}

// scan1User Internal function helper to do the user Scan of a Row
// r is the sql row to scan
func scan1User(r *sql.Row) (*DbUser, error) {
  var fecthUser DbUser

  // This is the very boring part, because scan order need to match the select :
  err := r.Scan(&fecthUser.Id,                 // Here
                &fecthUser.Login,              // all
                &fecthUser.Pass,               // of
                &fecthUser.F_name,             // this
                &fecthUser.L_name,             // must
                &fecthUser.Date_created,       // match
                &fecthUser.Is_disabled,        // sql
                &fecthUser.Is_deleted,         // query
                &fecthUser.Is_god,             // order
                &fecthUser.U_group_id,         // !!!
                &fecthUser.U_group_parent_id,	// ... so error prone
                &fecthUser.U_group_name,
                &fecthUser.U_group_date_created,
                &fecthUser.U_group_is_deleted, // Ooops : U_group_is_deleted
                &fecthUser.U_group_is_disabled,// must be after U_group_is_disabled
                &fecthUser.U_group_max_tkn,
                &fecthUser.R_group_id,
                &fecthUser.R_group_level,
                &fecthUser.R_group_is_company,
                &fecthUser.R_group_can_create_user,
                &fecthUser.R_group_can_remote_connect)
  return &fecthUser, err
}

SQL scan with sqlx

sqlx has structScan() that do the matching with your scan and the sql result + some other handy features.
Just be careful to not use sqlx.Select() on query without LIMIT = 1 or 10 max; this is in the doc. This function seems dangerous to me, I prefer to avoid it completely !
Rewrite the same example (just the DbUser struct and scan1User() function)

// One things change : we add identifier `db:"blahblah"` 
// where "blahblah" indicate for each field the sql name corresponding
type DbUser struct {
  Id                         int64       `db:"user_user_id"`
  Login                      string      `db:"user_user_login"`
  Pass                       string      `db:"user_user_pass"`
  F_name                     string      `db:"user_user_first_name"`
  L_name                     string      `db:"user_user_last_name"`
  Date_created               string      `db:"user_user_date_created"`
  Is_disabled                bool        `db:"user_user_is_disabled"`
  Is_deleted                 bool        `db:"user_user_is_deleted"`
  Is_admin                   bool        `db:"user_user_is_admin"`
  U_group_id                 int64       `db:"user_group_id"`
  U_group_parent_id          NullInt64   `db:"user_group_parent_id"`
  U_group_name               string      `db:"user_group_name"`
  U_group_date_created       string      `db:"user_group_date_created"`
  U_group_is_disabled        bool        `db:"user_group_is_disabled"`
  U_group_is_deleted         bool        `db:"user_group_is_deleted"`
  U_group_max_tkn            int64       `db:"user_group_max_tkn"`
  R_group_id                 int64       `db:"remote_group_id"`
  R_group_level              int64       `db:"remote_group_level"`
  R_group_is_company         bool        `db:"remote_group_is_company"`
  R_group_can_create_user    bool        `db:"remote_group_can_create_user"`
  R_group_can_remote_connect bool        `db:"remote_group_can_remote_connect"`
}

// scan1User Internal function helper to do the user Scan of a Row
// r is the sql row to scan
func scan1User(r *sql.Row) (*DbUser, error) {
  var fecthUser DbUser

  // This is the magic part, because it's so easier :
  err := r.StructScan(&fecthUser) // and voilà
  return &fecthUser, err
}

SQL query with variadic input

To do a SQL query with Go you have to use function that take a variadic input.
So adding an unpredictable amount of parameters in your query may seems difficult; don’t fear, just use slice of interface as input like this :

// Imagine a query :
query := `SELECT um."user_mail_id" FROM "user_mail" um
WHERE ug."user_group_left_bound" >= $1
AND  ug."user_group_right_bound" <= $2`

// Build query args
// Slice of interface to store many query params
queryArgs := []interface{}{ 
  // Order must match your “$” query
  group.User_group_left_bound,   // $1
  group.User_group_right_bound,  // $2
}

// Filter by status
if "" != statusFilter {
  // → Dynamic add a query parameter
  query += ` AND um."user_mail_status" = $3`
  queryArgs = append(queryArgs, statusFilter)  // $3
}

// Do query
rows, err := db.Queryx(query, queryArgs...) // ← Here : args slice
if nil != err {
  log.Printf("ListMail ERR : %s \n", err)
}
// Then : iterate over rows, scan result and JSON 
// or what you want transform ...
  • The good: it works ! You can dynamically add many parameters to a query (like with $3)
  • The bad: you still have to deal with parameters order and don’t mess with $1, $2 …etc.

Improve SQL parameters with sqlx

The preceding code could be improved using sqlx named parameters.

// Imagine a query
query := `SELECT um."user_mail_id" FROM "user_mail" um 
WHERE ug."user_group_left_bound" >= :user_group_left_bound  
AND  ug."user_group_right_bound" <= :user_group_right_bound` // ← No more “$”

// Build query args
queryArgs := map[string]interface{}{ // map to store many query named params
  // Order don’t matter anymore, name must match your named params
  "user_group_left_bound": group.User_group_left_bound,
  "user_group_right_bound": group.User_group_right_bound,
}

// Filter by status 
if "" != statusFilter {
  // → Dynamic add a query parameter
  query += ` AND um."user_mail_status" = :statusFilter` // add new sql query parameter
  queryArgs["statusFilter"] = statusFilter              // add named param corresponding
}

// Do query
rows, err := db.NamedQuery(query, queryArgs)
if nil != err {
  log.Printf("ListMail ERR : %s \n", err)
}
// Then : iterate over rows, scan result and JSON 
// or what you want transform ...
  • The good: you can still dynamically add many param to a query
  • The better: parameters write order don’t matter → you don’t have to manage an increment counter to write $n ; on big query it’s easier to read