CODEWITHSUNDEEP
mysqlsqlgo
EliteRaceElephant
EliteRaceElephant

Reputation: 8162

More concise function to fetch SQL result set in GO Golang

I want to retrieve an array of app IDs from a MySQL database. I used http://go-database-sql.org's example code:

func QueryAppList() *[]int64 {
    var (
        appList []int64
        appid   int64
    )
    qry := "SELECT a.appid FROM app a WHERE a.app_status IN (1, 2);"
    // cfg.GetDb() supplies the database connection already established
    rows, err := cfg.GetDb().Query(qry) 
    if err != nil {
        logg.Error(err)
        return &appList
    }
    defer func(rows *sql.Rows) { 
        // simple defer does not catch every error: https://www.joeshaw.org/dont-defer-close-on-writable-files/
        err := rows.Close()
        if err != nil {
            logg.Error(err)
        }
    }(rows)
    for rows.Next() {
        err := rows.Scan(&appid)
        if err != nil {
            logg.Error(err)
            return &appList
        }
        appidList = append(appList, appid)
    }
    err = rows.Err()
    if err != nil {
        logg.Error(err)
        return &appList
    }
    return &appidList
}

My programm will be littered with queries like this. All the ways of getting the result list and how it to prevent failure make this small query hard to read what is actually going on.

Is there a way to make queries more concise?

These are my thoughts to make the code less verbose:

Using an ORM like gorm is NOT an option.

I just started Go programming so I am lacking experience with the language.

Below is the same query in Node.js with the same result. It has 9 lines compared to Go's 34 i.e. 65% more concise in terms of length. That's where I ideally would like to get to.

import {query} from "../db/pool"; // connection pool query from https://github.com/sidorares/node-mysql2

export const queryAppList = async () => {
  try {
    const qry = "SELECT a.appid FROM app a WHERE a.app_status IN (1, 2);";
    const [appList] = await query(qry);
    return appList; 
  } catch (err) {
    console.error(err)
    return [];
  }
};

Upvotes: 0

Views: 1149

Answers (1)

Fenistil
Fenistil

Reputation: 3801

You can make a Query struct which has reusable methods for do such things.

Something like this:

type Query struct{
    conn *sql.DB
    rows *sql.Rows
    ...
}

func NewQuery(conn *sql.DB) *Query {
    return &Query{
        conn: conn,
        rows: nil,
    }
}

func (q Query) OpenSQL(sql string) error {
    q.rows, err = q.conn.Query(sql)
    if err != nil {
        log.Error("SQL error during query ("+sql+"). "+err.Error())
        return err
    }
    return nil
}

func (q Query)Close() (error) {
    err := q.rows.Close()
    if err != nil {
        log.Error("Error closing rows. "+err.Error())
        return err
    }
    return nil
}

//You can use generic functions to make the code even smaller
func FetchToSlice[T any](q Query) ([]T, error) {
    result := make([]T, 0)
    var value T
    for q.rows.Next() {
        err := q.rows.Scan(&value)
        if err != nil {
           log.Error("Error during fetching. "+err.Error())
           return nil, err
        }
        result = append(result, value)
    }
    return result, nil
}

With this you code will look something like this:

qry := NewQuery(cfg.GetDB())
err := qry.OpenSQL("SELECT a.appid FROM app a WHERE a.app_status IN (1, 2);")
if err != nil {
    return err
}
defer qry.Close()
appidList, err := FetchToSlice[int](qry)
if err != nil {
    return err
}

You can later add more methods to your Query to handle more complex cases, even you can use a sync.Pool to cache your query structs and so on.

Upvotes: 2

Related Questions